Store or save files/documents in SQL Server database using C#

Store or save files/documents in SQL Server database using C#

Introduction

This sample will explain you how you can store files or documents in SQL Server database. In general, it's not a good idea to store files in databases because it can grow database rapidly and can cause slower performance of database and server. Ideally you should use file system to store files. But there may be scenarios where you may want to store files in database.


Download Sample

How to store files in SQL Server database

To store a file in sql server, you need to read file data into a byte array. Once you have file data in byte array, you can easily store this file data in sql server using sql parameters. Following code explains you how to do this.

01.private void cmdSave_Click(object sender, EventArgs e)

02.{

03.try

04.{

05.//Read File Bytes into a byte array

06.byte[] FileData = ReadFile(txtFilePath.Text);

07. 

08.//Initialize SQL Server Connection

09.SqlConnection CN = new SqlConnection(txtConnectionString.Text);

10. 

11.//Set insert query

12.string qry = "insert into FilesStore (OriginalPath,FileData) values(@OriginalPath, @FileData)";

13. 

14.//Initialize SqlCommand object for insert.

15.SqlCommand SqlCom = new SqlCommand(qry, CN);

16. 

17.//We are passing Original File Path and file byte data as sql parameters.

18.SqlCom.Parameters.Add(new SqlParameter("@OriginalPath", (object)txtFilePath.Text));

19.SqlCom.Parameters.Add(new SqlParameter("@FileData", (object)FileData));

20. 

21.//Open connection and execute insert query.

22.CN.Open();

23.SqlCom.ExecuteNonQuery();

24.CN.Close();

25. 

26.//Close form and return to list or Files.

27.this.Close();

28.}

29.catch(Exception ex)

30.{

31.MessageBox.Show(ex.ToString());

32.}

33.}

Following code explains how to read file data in a byte array.

01.//Open file in to a filestream and read data in a byte array.

02.byte[] ReadFile(string sPath)

03.{

04.//Initialize byte array with a null value initially.

05.byte[] data = null;

06. 

07.//Use FileInfo object to get file size.

08.FileInfo fInfo = new FileInfo(sPath);

09.long numBytes = fInfo.Length;

10. 

11.//Open FileStream to read file

12.FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

13. 

14.//Use BinaryReader to read file stream into byte array.

15.BinaryReader br = new BinaryReader(fStream);

16. 

17.//When you use BinaryReader, you need to supply number of bytes to read from file.

18.//In this case we want to read entire file. So supplying total number of bytes.

19.data = br.ReadBytes((int)numBytes);

20. 

21.//Close BinaryReader

22.br.Close();

23. 

24.//Close FileStream

25.fStream.Close();

26. 

27.return data;

28.}

How to read file data bytes from SQL Server table

To read files from SQL Server, prepare a dataset first which will hold data from SQL Server table. Bind this dataset with a gridview control on form.

01.//Get table rows from sql server to be displayed in Datagrid.

02.void GetFilesFromDatabase()

03.{

04.try

05.{

06.//Initialize SQL Server connection.

07.SqlConnection CN = new SqlConnection(txtConnectionString.Text);

08. 

09.//Initialize SQL adapter.

10.SqlDataAdapter ADAP = new SqlDataAdapter("Select FileId,OriginalPath,FileData from FilesStore", CN);

11. 

12.//Initialize Dataset.

13.DS = new DataSet();

14. 

15.//Fill dataset with FilesStore table.

16.ADAP.Fill(DS, "FilesStore");

17. 

18.//Fill Grid with dataset.

19.dataGridView1.DataSource = DS.Tables["FilesStore"];

20.}

21.catch(Exception ex)

22.{

23.MessageBox.Show(ex.ToString());

24.}

25.}

Once you have image data in dataset DS, get file data from in a byte array.

1.//Get File data from dataset row.

2.byte[] FileData = (byte[])DS.Tables["FilesStore"].Rows[SelectedRow]["FileData"];

Finally save byte array data to a file

1.//Write file data to selected file.

2.using (FileStream fs = new FileStream(FileName, FileMode.Create))

3.{

4.fs.Write(FileData, 0, FileData.Length);

5.fs.Close();

6.}

How to download and run program

• Download sample from link on top. Extract in a folder.
• Restore SQL Server database from SQL Database sub folder.
• If somehow you can not restore provided database, you can generate necessary table using script(SQLScript.txt) provided in SQL Database directory.
• Open solution and Run it. Change connection string for your database.
• Click on Connect button. To add new file to database, click on New File button. Click Browse button in New File window to select a file. Then click on Save to save it.

Requirements

• Visual Studio.Net 2008
• .Net Framework 2.0
• MS SQL Server database (any version)