Uploading and downloading files to/from a SQL Server database

• Download source - 328 KB

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.

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(@"\"));
    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!).

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