Saving and Loading Images in MSSQL Database with C# .NET

Microsoft’s MSSQL database can store a variety of data types in its columns including varchar strings, int, double, float, date/time, binary, and image. The data type name “image” may seem mysterious at first, but it is actually another representation of a binary data type. This field in the database is a perfect fit for storing binary files and images. Examples of files that can be literally stored in the database include Microsoft Word documents, JPG, GIF, TIF, BMP, ZIP files, EXE, and any other file type.

Occassionally, in an ASP .NET web application, a task may involve the user uploading a file, which is stored in the database for analysis or retrieval at a later time. By using the “image” data type in the MSSQL database table, we can easily work with the uploaded data.

Binary Image Data in MSSQL

Binary image data in MSSQL is treated as a stream of bytes, or in other words, an array of bytes. We read the stream of bytes from the web browser’s “file” input control, store the bytes in an array, and save the array of bytes to the MSSQL database using a stored procedure or inline SQL. One important note is that you can not save image data with a plain text SQL statement. You must create an SQL command and insert the binary image data using an SQLParameter object. Here is an example:

SqlCommand MyCommand = new SqlCommand("INSERT INTO MYTABLE (IMAGE_DATA) VALUES (@doc_content)", MyConnection);

To assign the SQL variable @doc_content the binary array, you would do the following:

SqlParameter param1 = new SqlParameter("@doc_content", SqlDbType.Image);
param1.Value = fileData;
command.Parameters.Add(param1);

Finally, you can execute the SQL statement to save the picture image in the database.

command.ExecuteNonQuery();

The User Uploads a File in your Web Application

Utilizing the above method to store a picture in the database in an ASP .NET web application is a bit more tricky. We have to create a web form with a file input control, which allows the user to browse for a file to upload, and then access the stream.

Example HTML form to let a user upload a file

<input type="file">

<form name="MyForm" id="MyForm" action="">
Select a file to upload:
<input type="file" id="m_File" runat="server">
</form>

Saving an Image to MSSQL in C# .NET

In the code-behind file within your C# .NET application, you would have the following when the user clicks Submit:

  private bool SaveImageInDatabase()
  {
   byte[] fileData = null;

   // Make sure the user selected a file to upload.
   if (m_File != null)
   {
    // Make sure the file has data.
    if ((m_File.PostedFile != null) && (m_File.PostedFile.ContentLength > 0))
    {
     // Get the filename.
     string fn = System.IO.Path.GetFileName(m_File.PostedFile.FileName);
     try
     {
      // Access the file stream and begin the upload. Store the file in a memory byte array.
      Stream MyStream = m_File.PostedFile.InputStream;
      long iLength = MyStream.Length;
      fileData = new byte[(int)MyStream.Length];
      MyStream.Read(fileData, 0, (int)MyStream.Length);
      MyStream.Close();
     }
     catch (Exception excep)
     {
      return false;
     }
    }
   }

   // Save the file in the mssql database table.
   SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnection"]);

   try
   {
      MyConnection.Open();

      SqlCommand MyCommand = new SqlCommand("INSERT INTO MYTABLE (IMAGE_DATA) VALUES (@doc_content)", MyConnection);

      SqlParameter param1 = new SqlParameter("@doc_content", SqlDbType.Image);
      param1.Value = fileData;
      MyCommand.Parameters.Add(param1);

      MyCommand.ExecuteNonQuery();
}
   catch (Exception excep)
   {
    return false;
   }
   finally
   {
    if (MyConnection != null && MyConnection.State == ConnectionState.Open)
    {
     MyConnection.Close();
    }
   }

   return true;
  }

Loading an Image from MSSQL in C# .NET

If you try to view the column in the MSSQL Enterprise Manager, you will notice the data in the column says ““ and will not let you view the bytes. The only way to view the image bytes is to retrieve the binary stream by reading the byte array from the table. It is easy to do this simply by casting the datareader to (byte[]) as follows:

MyCommand = new SqlCommand("SELECT IMAGE_DATA FROM MYTABLE WHERE ID=1", MyConnection);
MyConnection.Open();
MyReader = MyCommand.ExecuteReader();

if (MyReader.Read())
{
   byte[] m_MyImage = (byte[])MyReader["IMAGE_DATA"];
   Response.BinaryWrite(m_MyImage);
}

In the above example, the last command calls Response.BinaryWrite to display the picture in a web browser. This is the method used in a C# web application to retrieve pictures from a database and display them in the web page. It is actually a bit more complicated than this because of the way web browsers work.

Displaying an Image from MSSQL in a C# .NET Web Application Page

To correctly load an image from a database and display it in a web application, you must have the web application call another page to actually retrieve the image data within an asp:Image tag. Here is an example:

The following tag will display the image on your page:

<asp:Image Width="88" Height="100" Runat="server" ID="m_Image" NAME="m_Image" />

In the code-behind Page_Load function, you set the target image URL along with an ID of the picture to fetch, as follows:

m_Image.ImageUrl = "loadpicture.aspx?" + strID;

You must then create the loadpicture.aspx page, which actually calls the MSSQL database to fetch the image and performs a BinaryWrite to write out the bytes. This way, in the asp:Image tag, the proper image data is written to the web browser inside an IMG SRC tag. The code to read from the database is shown above.

Advantages and Disadvantages to Using Image Data in MSSQL

While storing images in the database may be more complex than storing a relative or absolute path to the image, there are pros and cons to both methods. By storing the image as binary data, the entire database can be backed up without worrying about broken links, missing files, or even filenames. It is also easy to manipulate the image via its byte stream.

On the other hand, storing images as binary data has the possibility of slowing down search queries in the database. It may also cause the table to grow much larger than it would normally. Therefore, if you are working with a .NET web application which deals with hundreds of thousands of records, you may want to consider simply saving the image path in the database, rather than its binary content.

Working with images in a C# .NET web application is a highly useful and powerful feature. it greatly enhances the look and feel of the typical web application and allows you to provide a much richer and dynamic experience.

About the Author

This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.

Sponsor Me