Storing files in a database - Part 1

With Microsoft SQL Server and the Microsoft .NET Framework it's relatively easy to store files in a database. This is something that I've done on several occasions. Until recently I hadn't taken the time to write a formal class library that could be re-used with relative ease. I'm going to share a few code snippets from that class library that will provide enough information to see how it's done. A quick note about the project environment: The code samples are written in C# .NET 2.0. The database is SQL 2000 - although I'm sure that SQL 2005 would work seamlessly with the code samples The Attachment SQL table The SQL table where the files are stored is named "Attachments" and has the following fields: 

 The Attachment class library The Attachment class library is what I wrote to handle the requirements of the last project I completed that required database file storage. It does most of the work for us so we don't have to spend any time in the user interface layer defining how the file gets uploaded to the database. Among others it has the following properties and methods:

public Guid FileId;
public string FileName;
public byte[] FileImage;
public DateTime AttachedDate;
public void Create();

The "FileImage" property is the field that stores the byte stream of the file to be uploaded. The contents of the property will be written to the database. So all we need to do is instantiate the Attachment class and find a way to populate the class's required properties and call the Create() method. We will use the ASP .NET File Upload control to perform this operation. Uploading files to the database using the ASP .NET File Upload control 

 An easy way to provide an interface to upload files to a database is to use the ASP .NET File Upload control. The File Upload control is a control that was introduced in ASP .NET 2.0. Our file upload control is shown below. When the user clicks on the "Upload" button the following code fires.

private void AttachFile()
{
  if (FileUpload1.HasFile)
  {
    //Instantiate our Attachment class.
    Attachment MyAttachment = new Attachment();

    //Set Attachment properties.
    MyAttachment.FileName = FileUpload1.FileName;
    MyAttachment.AttachedDate = DateTime.Now;
    byte[] FileBytes = FileUpload1.FileBytes;
    MyAttachment.FileImage = FileBytes;
    MyAttachment.Create();
  }
}

That's all that has to be done from the ASP .NET user interface. Notice that the File Upload control provides a property named "FileBytes". The FileBytes property holds the byte stream of the file to be uploaded. All we have to do is set the Attachment class property "FileImage" to the value of the FileUpload1.FileBytes property. The real work is done in the Attachment class Create() method. Attachment class Create() method The Create method handles inserting the file into the database. It's very straight forward. The code sample is below. Notice the SQL command parameter @file_image is used to insert the contents of the Attachment.FileImage property into the database.

public void Create()
{
  SqlConnection sqlCon = new SqlConnection(ConnectionString);
  try
  {
    string sqlQuery = @"
    insert into Attachments(
    ,file_id
    ,file_name
    ,file_image
    ,attached_date)
    values (
    ,@file_id
    ,@file_name
    ,@file_image
    ,@attached_date)";

    sqlCmd = new SqlCommand(sqlQuery, sqlCon);
    sqlCmd.CommandType = CommandType.Text;
    Guid NewFileId = new Guid();
    sqlCmd.Parameters.Add("@file_id",
    SqlDbType.UniqueIdentifier).Value = NewFileId;
    sqlCmd.Parameters.Add("@file_name",
     SqlDbType.NVarChar, 256).Value = this.FileName);
    sqlCmd.Parameters.Add("@file_image",
     SqlDbType.Image).SqlValue = new SqlBinary this.FileImage);
    sqlCmd.Parameters.Add("@attached_date",
     SqlDbType.DateTime).Value = this.AttachedDate);
    sqlCon.Open();
    sqlCmd.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
    throw ex;
  }
  finally
  {
    sqlCmd.Dispose();
    sqlCon.Dispose();
  }
}

That's all for part 1 of this posting. Please note that the code samples provided have been simplified for this example. They will work as intended, but if you plan to use this code in a production environment you'll want to be sure to add proper error handling to your routines.