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 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:
To assign the SQL variable @doc_content the binary array, you would do the following:
Finally, you can execute the SQL statement to save the picture image in the database.
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
In the code-behind file within your C# .NET application, you would have the following when the user clicks Submit:
If you try to view the column in the MSSQL Enterprise Manager, you will notice the data in the column says “
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.
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:
In the code-behind Page_Load function, you set the target image URL along with an ID of the picture to fetch, as follows:
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.
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.
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.