Working with the In Memory File—Saving it to a Database
The HttpInputFile's InputStream property makes saving binary data to a database very simple because it exposes a stream object pointer to the uploaded file automatically. All you must do is use a Stream to read the bytes from the uploaded file, put them into a local byte array, and save it to the database. A Stream object simply provides a way to read and write bytes to and from a source object, which in this case is an image.
Even though you might have a table that already exists in the Northwind database with a field of the Image type, we are going to create our own table for these examples. The reason is that some older versions of Access and SQL Server don't contain this field in the Employees table. Use the following SQL code in Query Analyzer to make a new table named Images (see Listing 13.3). If you are not using SQL Server you will have to manually design this table in design mode.
Listing 13.3. SQL Code to Create a New Images Table
01: CREATE TABLE Images 02: ( 03: [ImageID] int IDENTITY(1,1) NOT NULL PRIMARY KEY, 04: [Image] Image, 05: [ContentType] VarChar(50), 06: [ImageDescription] VarChar(100), 07: [ByteSize] int 08: ) |
This Images table has 5 fields in it. The first (ImageID) is the Primary Key for the table. It is an Auto Number that will increment by 1 automatically when a new row is added to the table. The second field is Image and it will hold the image uploaded. The third field is ContentType and it will be populated with the value of the HttpPostedFile's ContentType attribute. The fourth field is ImageDescription and it will be populated by the contents of the ImageDescription TextBox. This will be a short description of the image being uploaded as described by the user. The final field is ByteSize and it will be populated with the value of the HttpPostedFile's ContentLength attribute. This information isn't necessary, but will help increase performance when we want to take the image out of the database and display it. This will be explained further in the section following this one. Listing 13.4 contains code to delete this table if you want after you are done with this chapter.
Listing 13.4. SQL Code Used to Delete the Image Table
01: DROP TABLE Images |
Now that you have a table that you can use to insert an image, let's make an image upload page. Listing 13.5 takes an uploaded file that is still in memory and not saved on hard disk and saves it to a database.
Listing 13.5. Saving an Image to Your Database
[VisualBasic.NET]
01: <%@ Import Namespace="System.IO" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04:
05: <script language="vb" runat="server">
06:
07: public sub SubmitImage(sender as Object, e as EventArgs)
08:
09: dim UpFile as HttpPostedFile = UP_FILE.PostedFile
10: dim sMessage as new StringBuilder()
11:
12: if UpFile.ContentLength = nothing then
13:
14: sMessage.Append("<b>* You must pick a file to upload</b>")
15:
16: else
17:
18: dim StreamObject as Stream
19: dim FileLength as Integer = UpFile.ContentLength
20: dim FileByteArray(FileLength) as Byte
21: StreamObject = UpFile.InputStream
22: StreamObject.Read(FileByteArray,0,FileLength)
23:
24: dim sCon as new SqlConnection("server=localhost;uid=sa;pwd=;database=northwind")
25:
26: dim SqlCmd as new StringBuilder()
27: SqlCmd.Append("INSERT INTO Images")
28: SqlCmd.Append("(Image, ContentType, ImageDescription, ByteSize)")
29: SqlCmd.Append("Values(@Image, @ContentType, @ImageDescription, @ByteSize)")
30:
31: dim SqlCmdObj as new SqlCommand(SqlCmd.ToString(),sCon)
32: dim param as SqlParameter
33: param = SqlCmdObj.Parameters.Add(new SqlParameter("@Image", SqlDbType.Image))
34: param.value = FileByteArray
35: param = SqlCmdObj.Parameters.Add(new SqlParameter("@ContentType", SqlDbType
|
.VarChar,50))
36: param.value = UpFile.ContentType
37: param = SqlCmdObj.Parameters.Add(new SqlParameter("@ImageDescription", SqlDbType
Let's walk through the code in Listing 13.5. When the page first loads, you will receive a page with one HtmlInputFile control, one TextBox control, and a Button control. The TextBox control enables the user to give the file they are going to upload a description. After the user selects an image and a gives it a description, the user should click the Upload Image button. The SubmitImage method is then executed on the server. For ease of programming I put the uploaded file into a local variable, UpFile (line 9). Before you do anything you should make sure that a file was indeed uploaded. On line 12, I create an If statement that checks the HttpPostedFile's (UpFile) ContentLength (the size of the file in bytes). If the file is nothing or zero bytes, then no file was uploaded, and a message is given to the user “You must pick a file to upload.” If a file is found, the else block is executed.
In lines 18 through 22, I read the bytes from the uploaded file's InputStream into a local byte array (FileByteArray). This needs to be done to insert the image into the database. In lines 25 through 42, I create the database connection, construct the SQL insert statement, set the values for all parameters, and execute a non-query SQL statement against the database. That's it—the image has been inserted into your database.
Now that you know how to insert images into the database, you might be thinking, “How the heck do I show the images now?” In the next section, I'll demonstrate one way to do this.