Click here to Skip to main content
13,002,271 members (72,902 online)
Click here to Skip to main content
Add your own
alternative version


70 bookmarked
Posted 13 Jul 2011

Uploading and downloading files to/from a SQL Server database

, 13 Mar 2013
Rate this:
Please Sign up or sign in to vote.
This sample project demonstrates how to save a file to a SQL Server database and also how to pull a file out of the database.


A while ago, I was working to find the right syntax and method for saving and retrieving files (.doc, .txt, .pdf) with regard to SQL Server 2005/2008. While I had found a few good examples showing how to upload files to a binary field type in SQL Server, I found a lack of good samples showing how to retrieve files and reverse the process. To fix this issue for others working on similar projects, I created a small WinForms project using C# and Visual Studio 2010 to demonstrate how to not only save files into a binary field type, but also how to retrieve them.


SQL Server allows you to create a field type known as a "varbinary" type. This field type, when used with a “(max)” length, is useful for storing files right inside a SQL Server database. While you could just store a string value for a file path that would serve as a "link" to the actual file on a file system, you would need a separate file storage area for your application and a way for the front end to connect and manipulate the files. Instead, a SQL Server field type like varbinary(max) will allow you to simply store the file just like any other type of data in a table – right alongside metadata stored in companion rows to make a meaningful record.

Using the code

This example project provides a complete example of how to save and retrieve files of any type to/from a database. To make full use of this code, you will have to create a database like the example in the article or mount the example one provided, as well as update a connection string in the main form code. This project also assumes you have Visual Studio 2010 Express (C#) or better.

High-level code contents

This project is comprised of a single form and class.

  • frmMain.cs - This is the class/form that provides the functionality required. In a production project, this would most likely be broken into one or more classes. The main form has a simple data grid view layout for displaying the uploaded files.
  • db2.mdf - A SQL database - you will have to mount this sample database (or build your own like it - just one table in it). Be sure to update your connection string.
    • Don't worry about the app config file - it's not used here - just modify this line in the frmMain.cs area as appropriate (line 16, I think) => string strSqlConn = @"Data Source=localhost\sqlexpress;Initial Catalog=db2;Integrated Security=True";.
    • The only table, tblAttachments, is built like this:
    • id -> (int, primary key, identity)
      fileName -> (nvarchar(250))
      fileSize -> (int)
      attachment -> (varbinary(max))

The three queries

There are three main queries in use and they are defined as string variables. You will see them used throughout the project.

string strQuery_AllAttachments = 
       "select [id], [fileName], [fileSize] from [tblAttachments] order by [fileName]";
string strQuery_GetAttachmentById = 
       "select * from [tblAttachments] where [id] = @attachId";
string strQuery_AllAttachments_AllFields = "select * from [tblAttachments]";

The data grid

Here we have the main form load event, which sets the connection string and will fill the grid view in the form with all the attachments.

private void frmMain_Load(object sender, EventArgs e)
    objConn.ConnectionString = strSqlConn; //set connection params
    FillDataGrid(gridViewMain, strQuery_AllAttachments);

This function serves to create a SQL data adapter with the table scheme (auto-populated) and the passed-in query string. The data table is then filled using the data adapter. Finally, the passed-in reference to the grid view is used to set the data table as the grid view data source. This function can be used for each data grid refresh.

private void FillDataGrid(DataGridView objGrid, string strQuery)
    DataTable tbl1 = new DataTable();
    SqlDataAdapter adapter1 = new SqlDataAdapter();
    SqlCommand cmd1 = new SqlCommand();
    cmd1.Connection = objConn;  // use connection object
    cmd1.CommandText = strQuery; // set query to use
    adapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;  //grab schema
    adapter1.SelectCommand = cmd1; //
    adapter1.Fill(tbl1);  // fill the data table as specified
    objGrid.DataSource = tbl1;  // set the grid to display data

Adding/uploading a file

Here's the code that begins adding a file - it will create an open file dialog box to allow you to choose a file to upload. If you select a valid file, the upload to the database field is kicked off. Once the CreateAttachment method runs, the grid view is refreshed using FillDataGrid.

private void btnAddFile_Click(object sender, EventArgs e)
    if (ofdMain.ShowDialog() != DialogResult.Cancel)
        CreateAttachment(ofdMain.FileName);  //upload the attachment
    FillDataGrid(gridViewMain, strQuery_AllAttachments);  // refresh grid

Creating an attachment is done in this method. This method does an action similar to the FillDataGrid one. An in-memory data table and adapter is created and populated using a query string. We then create a FileStream object using the passed-in file that was selected when the open file dialog was in use. This will allow the program to open the targeted file with read-only access. A byte array is used to store the read-in file so it can be "pushed" into the varbinary(max) column. intLength is used to create a properly-sized byte array that matches the size of the file. Next, a new row is added to the in-memory data table and a new record is created. Finally, the data adapter commits the data table changes back to the "real" database.

private void CreateAttachment(string strFile)
    SqlDataAdapter objAdapter = 
        new SqlDataAdapter(strQuery_AllAttachments_AllFields, objConn);
    objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
    DataTable objTable = new DataTable();
    FileStream objFileStream = 
        new FileStream(strFile, FileMode.Open, FileAccess.Read);
    int intLength = Convert.ToInt32(objFileStream.Length);
    byte[] objData;
    objData = new byte[intLength];
    DataRow objRow;
    string[] strPath = strFile.Split(Convert.ToChar(@"\"));

    objFileStream.Read(objData, 0, intLength);

    objRow = objTable.NewRow();
    //clip the full path - we just want last part!
    objRow["fileName"] = strPath[strPath.Length - 1];
    objRow["fileSize"] = intLength / 1024; // KB instead of bytes
    objRow["attachment"] = objData;  //our file
    objTable.Rows.Add(objRow); //add our new record

Downloading a file

This code begins the process of retrieving a file from the SQL Server table. We call SaveAttachment, which is explained in the next section. Also note that I call FillDataGrid, which is technically unnecessary, but just for good measure in case I added a delete function (or similar) later (I didn't end up adding that though - that's some homework for you!).

private void btnDownloadFile_Click(object sender, EventArgs e)
    SaveAttachment(sfdMain, gridViewMain);
    FillDataGrid(gridViewMain, strQuery_AllAttachments);  // refresh grid

This is the last main method of this project. First, we grab the row in the grid view that was selected when the method was called (remember, we passed in the grid view object (by ref essentially)). If the cell that represents the id field is not null, then it continues - this saves us from an embarrassing error if the download button is clicked when no row is actually selected. Again, we create an in-memory data adapter and data table, filling the scheme automatically. Note that the query is parameterized this time - @attachid. This query is different from the others in that it returns only a single row. Next we create a byte array and then cast the objRow["attachment"] as a byte array so we can actually grab the file from the attachment field properly. Finally, we show the save file dialog box and have the user select a file name and location for the incoming file; FileStream helps us again by allowing us to create and write to the selected file and path. If all goes well, the file should appear and be usable!

private void SaveAttachment(SaveFileDialog objSfd, DataGridView objGrid)
    string strId = objGrid.SelectedRows[0].Cells["id"].Value.ToString();
    if (!string.IsNullOrEmpty(strId))
        SqlCommand sqlCmd = new SqlCommand(strQuery_GetAttachmentById, objConn);
        sqlCmd.Parameters.AddWithValue("@attachId", strId);
        SqlDataAdapter objAdapter = new SqlDataAdapter(sqlCmd);
        DataTable objTable = new DataTable();
        DataRow objRow;
        objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(objAdapter);
        objRow = objTable.Rows[0];

        byte[] objData;
        objData = (byte[])objRow["attachment"];

        if (objSfd.ShowDialog() != DialogResult.Cancel)
            string strFileToSave = objSfd.FileName;
            FileStream objFileStream = 
               new FileStream(strFileToSave, FileMode.Create, FileAccess.Write);
            objFileStream.Write(objData, 0, objData.Length);

Points of interest

I had fun making this example for you to learn from and use. If you have any comments or questions, feel free to contact me by leaving a reply to this post.


  • 1.0 - 8 July 2011 - Initial post.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Systems Engineer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionLarge Files Pin
Member 130150466-Mar-17 8:44
memberMember 130150466-Mar-17 8:44 
QuestionAutomatic open downloaded file in the appropriate application Pin
Member 107051753-Sep-16 8:21
memberMember 107051753-Sep-16 8:21 
AnswerRe: Automatic open downloaded file in the appropriate application Pin
jchoponis3-Sep-16 11:05
memberjchoponis3-Sep-16 11:05 
Questionexception occur while running code Pin
Member 1254765326-May-16 5:08
memberMember 1254765326-May-16 5:08 
QuestionMessage Automatically Removed Pin
13-Jan-16 23:38
memberMember 1089184813-Jan-16 23:38 
GeneralMy vote of 5 Pin
DrABELL28-Jul-15 10:44
professionalDrABELL28-Jul-15 10:44 
QuestionThis is usually (not to say always) a bad idea... Pin
AlexCode19-Sep-14 1:59
professionalAlexCode19-Sep-14 1:59 
AnswerRe: This is usually (not to say always) a bad idea... Pin
jchoponis30-Sep-14 16:41
memberjchoponis30-Sep-14 16:41 
QuestionHow to use aspx 4.5 FileUpload control to store multiple images in SQL server table at once (C#)? Pin
Skill20128-Jul-14 4:46
memberSkill20128-Jul-14 4:46 
AnswerRe: How to use aspx 4.5 FileUpload control to store multiple images in SQL server table at once (C#)? Pin
jchoponis5-Sep-14 5:48
memberjchoponis5-Sep-14 5:48 
QuestionThx Pin
Member 107058957-Jul-14 10:20
memberMember 107058957-Jul-14 10:20 
QuestionQuestions Pin
ReneS_Sydney5-May-14 17:50
memberReneS_Sydney5-May-14 17:50 
AnswerRe: Questions Pin
jchoponis9-May-14 10:44
memberjchoponis9-May-14 10:44 
Questionfiles stored in sql server db Pin
pfraney9-Dec-13 5:57
memberpfraney9-Dec-13 5:57 
AnswerRe: files stored in sql server db Pin
jchoponis27-Dec-13 6:22
memberjchoponis27-Dec-13 6:22 
QuestionI have a problem to download file. Pin
Member 1038292611-Nov-13 21:17
memberMember 1038292611-Nov-13 21:17 
AnswerRe: I have a problem to download file. Pin
jchoponis4-Dec-13 14:56
memberjchoponis4-Dec-13 14:56 
QuestionSql Download File with Progressbar Pin
Joukuh10-Jun-13 5:16
memberJoukuh10-Jun-13 5:16 
AnswerRe: Sql Download File with Progressbar Pin
jchoponis27-Dec-13 10:50
memberjchoponis27-Dec-13 10:50 
QuestionCan not load binary data (pdf file) to gridveiw Pin
Member 214573611-Mar-13 11:40
memberMember 214573611-Mar-13 11:40 
SuggestionRe: Can not load binary data (pdf file) to gridveiw Pin
jchoponis13-Mar-13 2:05
memberjchoponis13-Mar-13 2:05 
GeneralMy vote of 5 Pin
Lupit29-Jan-13 6:21
memberLupit29-Jan-13 6:21 
GeneralRe: My vote of 5 Pin
jchoponis4-Dec-13 14:58
memberjchoponis4-Dec-13 14:58 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:17
mvpKanasz Robert24-Sep-12 6:17 
GeneralRe: My vote of 5 Pin
jchoponis4-Dec-13 14:58
memberjchoponis4-Dec-13 14:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170624.1 | Last Updated 13 Mar 2013
Article Copyright 2011 by jchoponis
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid