Click here to Skip to main content
15,868,154 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

 
GeneralRe: My vote of 5 Pin
jchoponis4-Dec-13 14:58
jchoponis4-Dec-13 14:58 
GeneralThis is very good article. Pin
Jayesh Sorathia13-Sep-12 0:49
Jayesh Sorathia13-Sep-12 0:49 
GeneralRe: This is very good article. Pin
jchoponis13-Sep-12 2:28
jchoponis13-Sep-12 2:28 
Questionin vb.net Pin
suganya c9-Jul-12 23:43
suganya c9-Jul-12 23:43 
AnswerRe: in vb.net Pin
jchoponis13-Sep-12 2:30
jchoponis13-Sep-12 2:30 
QuestionUploading and downloading files to/from a SQL Server database Pin
Smith0210-May-12 14:53
Smith0210-May-12 14:53 
AnswerRe: Uploading and downloading files to/from a SQL Server database Pin
jchoponis13-Sep-12 2:35
jchoponis13-Sep-12 2:35 
GeneralMy vote of 5 Pin
astamurik9-May-12 5:48
astamurik9-May-12 5:48 
GeneralRe: My vote of 5 Pin
jchoponis13-Sep-12 2:35
jchoponis13-Sep-12 2:35 
QuestionSqlite Pin
Ram Sam 226-Mar-12 4:16
Ram Sam 226-Mar-12 4:16 
AnswerRe: Sqlite Pin
jchoponis26-Mar-12 7:31
jchoponis26-Mar-12 7:31 
AnswerRe: Sqlite Pin
Ram Sam 211-Apr-12 23:03
Ram Sam 211-Apr-12 23:03 
QuestionUploading and downloading Pin
vyshnavik26-Feb-12 21:37
vyshnavik26-Feb-12 21:37 
AnswerRe: Uploading and downloading Pin
jchoponis26-Mar-12 7:44
jchoponis26-Mar-12 7:44 
SuggestionSuggestions. [modified] Pin
Anton Levshunov13-Jul-11 22:13
Anton Levshunov13-Jul-11 22:13 
GeneralRe: Suggestions. Pin
jchoponis14-Jul-11 4:47
jchoponis14-Jul-11 4:47 
GeneralRe: Suggestions. Pin
khorvat17-Jul-11 2:46
khorvat17-Jul-11 2:46 
GeneralRe: Suggestions. Pin
jchoponis18-Jul-11 10:23
jchoponis18-Jul-11 10:23 
GeneralRe: Suggestions. Pin
jchoponis5-Aug-11 18:06
jchoponis5-Aug-11 18:06 

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.