How to Store file in SQL Server database using C#

Download Source from GitHub

This sample code explains you how you can store files in SQL Server database using C#. It uses ADO.Net System.Data.SqlClient namespace. Files can be stored in SQL server using SQL parameters. For storing file, we can use “VARBINARY (MAX)” data type in SQL Server.

Please note that in general it’s not recommended to store files in database. It’s better to store files on a file system and save their paths in a table. This is better for overall database performance and preventing large size of database. But there could be some scenarios where you want to save files in database. For example you want to a small case document with a transaction. As long as files are small in size, they should not increase database size significantly. Also it’s a good idea to keep files in a separate table which is not used frequently in regular queries. This will improve query performance.

How to download and run program

• Clone or Download source from above Github link.
• Open solution and change connection string on frmFilesStore and frmNewFile forms to point to correct path for FileStore.mdf file in project.
• Run project then click Connect button. This should display existing files stored in sql server database.
• Click on “Store New File” button to store a new file. This will pop up a new form where you can select location of file to upload.
• To delete file record from database, select entire row on data grid, i.e. click on very first cell of row. Then click “Delete Selected File”

How to Store File in SQL Server table

To store image in to SQL server database, we need to read image file into a byte array. Once we have image data in byte array, we can easily store this image 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 FileStore (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 in to 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.

        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 FileStore", CN);

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

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

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

It is also possible to save byte data into another local file. Following code explains that.

                 string FileName = saveFileDialog1.FileName;
                 //Get File data from dataset row.
                 byte[] FileData = (byte[])DS.Tables["FileStore"].Rows[SelectedRow]["FileData"];
                 //Write file data to selected file.
                 using (FileStream fs = new FileStream(FileName, FileMode.Create))
                 {
                     fs.Write(FileData, 0, FileData.Length);
                     fs.Close();
                 }