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.

Store Files in SQL Server

New File

Download Source 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.

        private void cmdSave_Click(object sender, EventArgs e)
        {
            try
            {
                //Read File Bytes into a byte array
                byte[] FileData = ReadFile(txtFilePath.Text);
                
                //Initialize SQL Server Connection
                SqlConnection CN = new SqlConnection(txtConnectionString.Text);

                //Set insert query
                string qry = "insert into FilesStore (OriginalPath,FileData) values(@OriginalPath, @FileData)";

                //Initialize SqlCommand object for insert.
                SqlCommand SqlCom = new SqlCommand(qry, CN);

                //We are passing Original File Path and file byte data as sql parameters.
                SqlCom.Parameters.Add(new SqlParameter("@OriginalPath", (object)txtFilePath.Text));
                SqlCom.Parameters.Add(new SqlParameter("@FileData", (object)FileData));

                //Open connection and execute insert query.
                CN.Open();
                SqlCom.ExecuteNonQuery();
                CN.Close();

                //Close form and return to list or Files.
                this.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

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

        //Open file in to a filestream and read data in a byte array.
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            byte[] data = null;

            //Use FileInfo object to get file size.
            FileInfo fInfo = new FileInfo(sPath);
            long numBytes = fInfo.Length;

            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);

            //When you use BinaryReader, you need to supply number of bytes to read from file.
            //In this case we want to read entire file. So supplying total number of bytes.
            data = br.ReadBytes((int)numBytes);

            //Close BinaryReader
            br.Close();

            //Close FileStream
            fStream.Close();

            return data;
        }

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.

        //Get table rows from sql server to be displayed in Datagrid.
        void GetFilesFromDatabase()
        {
            try
            {
                //Initialize SQL Server connection.
                SqlConnection CN = new SqlConnection(txtConnectionString.Text);

                //Initialize SQL adapter.
                SqlDataAdapter ADAP = new SqlDataAdapter("Select FileId,OriginalPath,FileData from FilesStore", CN);

                //Initialize Dataset.
                DS = new DataSet();

                //Fill dataset with FilesStore table.
                ADAP.Fill(DS, "FilesStore");

                //Fill Grid with dataset.
                dataGridView1.DataSource = DS.Tables["FilesStore"];
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

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

         //Get File data from dataset row.
         byte[] FileData = (byte[])DS.Tables["FilesStore"].Rows[SelectedRow]["FileData"];

Finally save byte array data to a file

     //Write file data to selected file.
     using (FileStream fs = new FileStream(FileName, FileMode.Create))
     {
         fs.Write(FileData, 0, FileData.Length);
         fs.Close();
     }

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)

Posted: 2 years 5 months ago by prasaanth07 #553
prasaanth07's Avatar
Hi. Excellent article and post. Very well explained. Only thing is can this be done by using varbinary(max) data type instead of image data type on the SQL table ? If so , could you guide ?
Posted: 3 years 6 months ago by unfragile #439
unfragile's Avatar
Hi, Very good project, Runs fine, But I am having an issue, Whenever I tries to save the file by selecting appropriate row in datagrid, File saves to specified location without extension, We need to rename it and give it extension manually than it is working.... Do you have a solution for it?