.NET – Write A File to Disk from your Database

ASP.NET

ASP.NET

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
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s