In this example I will review and give examples in C# and VB.NET how to get and save a file stored in an Image datatype in a SQL Server table.
Here is a link to a list of other articles I have written about using .NET with SQL Server.
In general its good practice to store actual images or files in your database rather than just storing references to a physical path on a file-server.
If ever you want to take a look and review these stored files, then you’ll need a way to extract them from your database table and save them to your local hard-drive. I’ve created a small example script below for a C# Windows Application that extracts and saves a single file of your choosing when a button is clicked. This script is fully functional and to get it working in your environment you simply need to change the database connection, SQL query, and file name that you want to save the file as. I have marked these variable fields with orange text.
C# Example
Note that you will need to include System.Runtime.Serialization.Formatters.Binary; in order to be able to convert the Object returned from your database to a Byte array using the BinaryFormatter and MemoryStream methods. Naturally you need System.Data; and System.Data.SqlClient; to get the information from SQL Server, and System.IO; to write out your file to your local computer.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
namespace CSTestFileConvert
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
byte[] byteStream = null;
object objSave = null;
string strSaveName = "myTestDocument.doc";
dynamic strConn = "Data Source=MyServer;Initial Catalog=MyDB;Persist Security Info=True;User ID=myUserId;Password=MyPassword;";
SqlConnection conn = null;
SqlCommand cmd = null;
try {
conn = new SqlConnection(strConn);
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT [FileColumn] FROM [MyDB].[dbo].[MyFileTable] where ID = 1234 ";
cmd.CommandTimeout = 30;
cmd.CommandType = CommandType.Text;
conn.Open();
objSave = cmd.ExecuteScalar();
BinaryFormatter tmpBinF = new BinaryFormatter();
MemoryStream tmpMemStrm = new MemoryStream();
tmpBinF.Serialize(tmpMemStrm,objSave);
byteStream = tmpMemStrm.ToArray();
} catch (Exception ex) {
} finally {
if ((cmd != null))
cmd.Dispose();
if ((conn != null)) {
conn.Close();
conn.Dispose();
}
}
FileStream newFile = File.Create("c:\\" + strSaveName);
newFile.Write(byteStream, 0, byteStream.Length);
newFile.Dispose();
}
}
}
VB.NET Example
Here is the same example code in Visual Basic. Note that in VB.NET you don’t need to use the BinaryFormatter and MemoryStream objects to convert from an object data type to a Byte array. Instead you can do a simple CType cast.
Imports System.Data Imports System.Data.SqlClient Imports System.IO Public Class Form1 Public Sub New() ' This call is required by the designer. InitializeComponent() ' Add any initialization after the InitializeComponent() call. End Sub Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click Dim byteStream As Byte() Dim objSave As Object Dim strSaveName As String = "myTestDocument.doc" Dim strConn = "Data Source=MyServer;Initial Catalog=MyDB;Persist Security Info=True;User ID=myUserId;Password=MyPassword;" Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Try conn = New SqlConnection(strConn) cmd = New SqlCommand() cmd.Connection = conn cmd.CommandText = "SELECT [FileColumn] FROM [MyDB].[dbo].[MyFileTable] where ID = 1234 " cmd.CommandTimeout = 30 cmd.CommandType = CommandType.Text conn.Open() objSave = cmd.ExecuteScalar byteStream = CType(objSave, Byte()) Catch ex As Exception Finally If Not cmd Is Nothing Then cmd.Dispose() If Not conn Is Nothing Then conn.Close() conn.Dispose() End If End Try Dim newFile As FileStream = File.Create("c:\" & strSaveName) newFile.Write(byteStream, 0, byteStream.Length) newFile.Dispose() End Sub End Class
Justin Cooney 