Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Uploading and downloading files to/from a SQL Server database

Rate me:
Please Sign up or sign in to vote.
4.78/5 (32 votes)
13 Mar 2013CPOL5 min read 303.7K   13.3K   72   44
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.

Introduction

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.

Background

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.

C#
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.

C#
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.

C#
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.

C#
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.

C#
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(@"\"));
    objAdapter.Fill(objTable);

    objFileStream.Read(objData, 0, intLength);
    objFileStream.Close();

    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
    objAdapter.Update(objTable);
}

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!).

C#
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!

C#
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);
        objAdapter.Fill(objTable);
        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);
            objFileStream.Close();
        }
    }
}

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.

History

  • 1.0 - 8 July 2011 - Initial post.

License

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


Written By
Systems Engineer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHelp Save as type Pin
TUANHIS17-Jan-18 2:27
professionalTUANHIS17-Jan-18 2:27 
QuestionLarge Files Pin
JT one6-Mar-17 8:44
JT one6-Mar-17 8:44 
AnswerRe: Large Files Pin
jchoponis20-Aug-17 11:01
jchoponis20-Aug-17 11:01 
QuestionAutomatic open downloaded file in the appropriate application Pin
Member 107051753-Sep-16 8:21
Member 107051753-Sep-16 8:21 
AnswerRe: Automatic open downloaded file in the appropriate application Pin
jchoponis3-Sep-16 11:05
jchoponis3-Sep-16 11:05 
Sure - just add in some code to invoke a Process object to invoke the file you downloaded.

Process.Start(your_file_name>);

This will generally invoke the default associated program on the client machine.

modified 17-Sep-16 16:14pm.

Questionexception occur while running code Pin
Member 1254765326-May-16 5:08
Member 1254765326-May-16 5:08 
GeneralMy vote of 5 Pin
DrABELL28-Jul-15 10:44
DrABELL28-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
jchoponis30-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
Skill20128-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
jchoponis5-Sep-14 5:48 
QuestionThx Pin
Member 107058957-Jul-14 10:20
Member 107058957-Jul-14 10:20 
QuestionQuestions Pin
ReneS_Sydney5-May-14 17:50
ReneS_Sydney5-May-14 17:50 
AnswerRe: Questions Pin
jchoponis9-May-14 10:44
jchoponis9-May-14 10:44 
Questionfiles stored in sql server db Pin
pfraney9-Dec-13 5:57
pfraney9-Dec-13 5:57 
AnswerRe: files stored in sql server db Pin
jchoponis27-Dec-13 6:22
jchoponis27-Dec-13 6:22 
QuestionI have a problem to download file. Pin
Member 1038292611-Nov-13 21:17
Member 1038292611-Nov-13 21:17 
AnswerRe: I have a problem to download file. Pin
jchoponis4-Dec-13 14:56
jchoponis4-Dec-13 14:56 
QuestionSql Download File with Progressbar Pin
Joukuh10-Jun-13 5:16
Joukuh10-Jun-13 5:16 
AnswerRe: Sql Download File with Progressbar Pin
jchoponis27-Dec-13 10:50
jchoponis27-Dec-13 10:50 
QuestionCan not load binary data (pdf file) to gridveiw Pin
Member 214573611-Mar-13 11:40
Member 214573611-Mar-13 11:40 
SuggestionRe: Can not load binary data (pdf file) to gridveiw Pin
jchoponis13-Mar-13 2:05
jchoponis13-Mar-13 2:05 
GeneralMy vote of 5 Pin
Lupit29-Jan-13 6:21
Lupit29-Jan-13 6:21 
GeneralRe: My vote of 5 Pin
jchoponis4-Dec-13 14:58
jchoponis4-Dec-13 14:58 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:17
professionalKanasz Robert24-Sep-12 6:17 

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.