Working with the In Memory FileSaving it to a Database

only for RuBoard

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.VarChar,100)) 38:     param.value = ImageDescription.Text 39:     param = SqlCmdObj.Parameters.Add(new SqlParameter("@ByteSize", SqlDbType.Int)) 40:     param.value = UpFile.ContentLength 41: 42:    sCon.Open() 43:    SqlCmdObj.ExecuteNonQuery 44:    sCon.Close() 45: 46:    sMessage.Append("<p><b>* Your image has been uploaded</b>") 47: 48:   end if 49:    message.Text = sMessage.ToString() 50:  end sub 51: 52: </script> 53: [C#.NET] 01: <%@ Import Namespace="System.IO" %> 02: <%@ Import Namespace="System.Data" %> 03: <%@ Import Namespace="System.Data.SqlClient" %> 04: 05: <script language="c#" runat="server"> 06: 07:  void SubmitImage(Object sender, EventArgs e) { 08: 09:   HttpPostedFile UpFile = UP_FILE.PostedFile; 10:   StringBuilder sMessage = new StringBuilder(); 11: 12:   if (UpFile.ContentLength == 0) { 13: 14:    sMessage.Append("<b>* You must pick a file to upload</b>"); 15: 16:   }  else { 17: 18:    Stream StreamObject; 19:    int FileLength = UpFile.ContentLength; 20:    Byte[] FileByteArray = new Byte[FileLength]; 21:    StreamObject = UpFile.InputStream; 22:    StreamObject.Read(FileByteArray,0,FileLength); 23: 24:    SqlConnection sCon = new  SqlConnection("server=localhost;uid=sa;pwd=;database=northwind"); 25: 26:    StringBuilder SqlCmd = 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:    SqlCommand SqlCmdObj = new SqlCommand(SqlCmd.ToString(),sCon); 32:    SqlParameter param; 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.VarChar,100)); 38:     param.Value = ImageDescription.Text; 39:     param = SqlCmdObj.Parameters.Add(new SqlParameter("@ByteSize", SqlDbType.VarChar,  100)); 40:     param.Value = UpFile.ContentLength; 41: 42:    sCon.Open(); 43:    SqlCmdObj.ExecuteNonQuery(); 44:    sCon.Close(); 45: 46:    sMessage.Append("<p><b>* Your image has been uploaded</b>"); 47: 48:   } 49:    message.Text = sMessage.ToString(); 50:  } 51: 52: </script> [VisualBasic.NET & C#.NET] 54: <html> 55: <body> 56: <form enctype="multipart/form-data" runat="server"> 57: <h1>File Upload Example to a Database</h1> 58: <asp:Table runat="server" width="700" align="left" > 59:  <asp:TableRow> 60:   <asp:TableCell> 61:    <b>Upload New Image</b> 62:   </asp:TableCell> 63:   <asp:TableCell> 64:    <input type="file" id="UP_FILE" runat="server" width="200" accept="image/*" /> 65:   </asp:TableCell> 66:  </asp:TableRow> 67:  <asp:TableRow> 68:   <asp:TableCell> 69:    <b>Give a Description</b> 70:   </asp:TableCell> 71:   <asp:TableCell> 72:    <asp:TextBox runat="server" width="239" id="ImageDescription"  MaintainState="false" /> 73:   </asp:TableCell> 74:  </asp:TableRow> 75:  <asp:TableRow> 76:   <asp:TableCell> 77:    <asp:Label runat="server" id="message" forecolor="red" maintainstate="false" /> 78:   </asp:TableCell> 79:   <asp:TableCell> 80:    <asp:Button runat="server" width="239" OnClick="SubmitImage" text="Upload Image"/> 81:   </asp:TableCell> 82:  </asp:TableRow> 83: </asp:Table> 84: </form> 85: </body> 86: </html> 

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.

only for RuBoard