 | 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 <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 "<binary>" 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. |