Uploading Images to a Database - Part I (转)

Uploading Images to a Database - Part I
by Dave, webmaster of www.123aspx.com  


Intro
One of the popular questions of databases and the web is: "How can I upload an image into a database?" A few weeks ago there was a discussion thread going on at asplists.com about this topic. It turns out with ASP.NET, all of the controls and features are are already built into the framework to achieve this functionality. This article is not here to discuss the advantages or disadvantages of uploading binary data to a database, but merely how to do this.  So lets get started. If you want to skip the article, you can view all the code here.  
   
Building Our Database Table
We start out by building our database table. Our image table is going to have a few columns describing the image data, plus the image itself.  Here is the sql required to build our table in SQL Server.
CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY] GO
I'm a great fan of having a single column primary key, and making that key an Identity column, in our example, that column is img_pk. The next column is img_name,img_name is used to store a friendly name of our image, for example "Mom and Apple Pie". img_data is actually our image data column, and is where we will be storing our binary image data.  img_contenttype will be used to record the content-type of the image, for example "image/gif" or "image/jpeg" so we will know what content-type we need to output back to the client, in our case the browser.
   
Building our Webform
Now that we have a warm, fuzzy place to store our images, lets build a webform to upload our images into the database.
<form enctype="multipart/form-data" runat=server id=form1 name=form1>

    Enter A Friendly Name
    <input type=text id=imgName runat="server" />
    
    <br>Select File To Upload:
<input id="UploadFile" type=file runat=server>
<asp:button Text="Upload Me!" OnClick="UploadBtn_Click" runat=server/>
</form>
The first interesting point about our webform, is the attribute "enctype". Enctype tells the browser and server that we will be uploading some type of binary data.  This binary data needs to be parsed, using a different mechanism from our normal text data.  The next control we of interest is the type=file control.  This control will present  the user with an upload file dialog box.  The user browses for the file they want to upload.  Here is a picture of our form.
   
Working with the Uploaded Image
Once the user posts the data, we have to be able to  parse the binary data and send it to the database.  Along with the main body of the code, we use 2 helper functions to achieve this.  The first function is used to connect to the database and the other function is used to insert the data into the database.  We connect to the database using a function we've posted on aspfree before. This function reads a connection string out of the database. You can find a better explanation of this function here .  I'll list it for completeness.  
    Private Function    sqlConnString() as String
        REM -- Get Connstring from config.web
        Dim Context as HttpContext = HttpContext.Current
        Dim AppSettings as HashTable = Ctype(Context.GetConfig("appsettings"),HashTable)
        Dim myDSN as String = Ctype(AppSettings("DSN"),String)

        Return myDSN
     End Function
   
Inserting the Image
We create another function to insert the image into the database.  This particular function was created using Scott Guthrie's AdHoc Database Code Builder. If you haven't seen it, I suggest you take a look at it, as it can save you a lot of time.  To use the Code Builder, you enter the basic sql syntax you want to use, in our case it was an insert statement (see following code).  And then you define the parameters of your query.  Here is what our function ended up looking like:
Function MyDatabaseMethod(imgName As String, imgbin As Byte(), imgcontenttype as String) As Integer
    Dim connection as New SQLConnection( sqlConnString )
    Dim command as New SQLCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection )

    Dim param0 As New SQLParameter( "@img_name", SQLDataType.VarChar,50 )
    param0.Value = imgName
    command.Parameters.Add( param0 )

    Dim param1 as New SQLParameter( "@img_data", SQLDataType.Image )
    param1.Value = imgbin
    command.Parameters.Add( param1 )

    Dim param2 as New SQLParameter( "@img_contenttype", SQLDataType.VarChar,50 )
    param2.Value = imgcontenttype
    command.Parameters.Add( param2 )

    connection.Open()
    Dim numRowsAffected as Integer = command.ExecuteNonQuery()
    connection.Close()

   Return numRowsAffected

End Function
In this function we are passing in 3 different parameters
imgName - the friendly name we want to give out image data
imgbin -- the binary or Byte array of our data
imgcontenttype - the content type of our image. For example: image/gif or image/jpeg

Scott's Database Code Builder does the rest of the work for us. The Code Builder creates our 3 parameters as SQLParameters and defines the type. Our first SQLParameter is @img_name and is defined as a VarChar with a length of 50.  The 2nd parameter, @img_data, is the binary or Byte() of data and is defined with a data type of Image. The last parameter is @img_contenttype, is defined as a VarChar with a length of 50 characters.  The remainder of the function opens a connection to the database and executes the command by calling command.ExecuteNonQuery().
   
Calling our Functions
Ok, now that we have our worker functions written, let's go ahead and get our image data.  
            imgStream = UploadFile.PostedFile.InputStream
            imgLen = UploadFile.PostedFile.ContentLength
            imgUploadedName = UploadFile.PostedFile.FileName
            Dim imgBinaryData(imgLen) as Byte
            imgContentType = UploadFile.PostedFile.ContentType
            imgName_value = imgName.Value

            Try
                If imgName_value.Length < 1 then
                    imgName_value =     GetLastRightOf("/",imgUploadedName )
                End if
            Catch myEx as Exception
                    imgName_value =     GetLastRightOf("/",imgUploadedName )
            End Try

We need to access three important pieces of data for our example. We need the image:
Name (imgName_value)
Content-Type (imgContentType)
and the Image Data. (imgBindaryData())
First we access to the image stream, which we are able to get by using the property UploadFile.PostedFile.InputStream. (Remember, UploadFile was the name of our upload control on the webform).    We also need to know how long the Byte array we are going to create needs to be.  We can get this number by calling  UploadFile.PostedFile.ContentLength, and storing it's value in imgLen.  Once we have the length of the image, we create a byte array by Dim imgBinaryData(imgLen) as Byte.  We access the content type of the image by accessing the ContentType property of UploadFile.PostedFile. Lastly we need the friendly name we are going to use for the image. In our code we actually capture two names. The original name of the file uploaded, and the friendly name entered by the user. If the user did not enter a friendly name we retrieve the original name of the image from UploadFile.PostedFile.FileName. However, UploadFile.PostedFile.FileName returns more than just the image name, it returns the entire uploaded image file path. An example might be: C:/temp/myimage.jpg. Therefore, the image name we really want, is everything after the last "/". To do this, we write a little utility function:
     Function GetLastRightOf(LookFor as String, myString as String) as String
            Dim StrPos as Integer
            StrPos = myString.LastIndexOf(LookFor)
            Return myString.SubString(StrPos + 1)
     End Function
GetLastRightOf() finds the last positional instance of "/". Once we have that position, stored in the variable StrPos, we call an overloaded SubString() method of the String object and return everything after the last "/". Calling String.SubString() looks like  SubString(StrPos + 1). We have to add 1 to StrPos because the length of "/" needs to be accounted for.
   
The Good Stuff
Ok, we know how to connect to the database, we know how to insert data into the database, and we have access to the uploaded image's properties.  But how do we pass the stream of the image to MyDatabaseMethod(). Again, .NET comes to the rescue. With 1 line of code we are able to access the image stream and convert it to a Byte array.
     Dim n as Integer = imgStream.Read(imgBinaryData, 0, imgLen)
The stream object provides a method called Read(). Read() takes 3 parameters:
buffer - An array of bytes. A maximum of count bytes are read from the current stream and stored in buffer.
offset -The byte offset in buffer at which to begin storing the data read from the current stream.
count - The maximum number of bytes to be read from the current stream.
So we pass in our Byte array, imgBinaryData; the place to start at, 0; and the amount of bytes we want to read.  n number of bytes read into our array is returned.
   
Extending Beyond Images
Because we are able to access the binary stream of data, images are not the only object we can store in the database.  Some other objects might be streaming video, com objects, or sound clips.  As an example I also uploaded a streaming video into my database.  I ran a select query to show the results.
   
Conclusion
So there we have it, ASP.NET provides us some easy functionality for uploading images into a database. In Part II, we will actually look at pulling these images out of a database and sending them to a browser.  The complete code used for this article can be found below.

image.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[image]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[image]
GO

CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY]
GO

imgupload.aspx
<%@ Page EnableSessionState="false" explicit="true" strict="true" MaintainState="false"%>
<%@Import Namespace="System.Data.Sql" %>
<%@Import Namespace="System.IO"%>
<script language="VB" runat=server>

Sub UploadBtn_Click(Sender as Object, E as EventArgs)
            Dim imgStream as Stream
            Dim imgLen as Integer
            Dim imgName_value as string
            Dim imgContentType as String
            Dim imgUploadedName as String
            
            imgStream = UploadFile.PostedFile.InputStream
            imgLen = UploadFile.PostedFile.ContentLength
            imgUploadedName = UploadFile.PostedFile.FileName
            Dim imgBinaryData(imgLen) as Byte
            imgContentType = UploadFile.PostedFile.ContentType
            imgName_value = imgName.Value

            Try
                If imgName_value.Length < 1 then
                    imgName_value =     GetLastRightOf("/",imgUploadedName )
                End if
            Catch myEx as Exception
                    imgName_value =     GetLastRightOf("/",imgUploadedName )
            End Try

     Dim n as Integer = imgStream.Read(imgBinaryData, 0, imgLen)
     Dim NumRowsAffected as Integer = MyDatabaseMethod(imgName_value, imgBinaryData, imgContentType)

    If NumRowsAffected > 0 then
         Response.Write ( "<BR> uploaded image " )
    Else
         Response.Write ( "<BR> an error occurred uploading the image.d " )
    End if
End Sub

Function GetLastRightOf(LookFor as String, myString as String) as String
            Dim StrPos as Integer
            StrPos = myString.LastIndexOf(LookFor)
            Return myString.SubString(StrPos + 1)
End Function


Function MyDatabaseMethod(imgName As String, imgbin As Byte(), imgcontenttype as String) As Integer
    Dim connection as New SQLConnection( sqlConnString() )
    Dim command as New SQLCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection )

    Dim param0 As New SQLParameter( "@img_name", SQLDataType.VarChar,50 )
    param0.Value = imgName
    command.Parameters.Add( param0 )

    Dim param1 as New SQLParameter( "@img_data", SQLDataType.Image )
    param1.Value = imgbin
    command.Parameters.Add( param1 )

    Dim param2 as New SQLParameter( "@img_contenttype", SQLDataType.VarChar,50 )
    param2.Value = imgcontenttype
    command.Parameters.Add( param2 )

    connection.Open()
    Dim numRowsAffected as Integer = command.ExecuteNonQuery()
    connection.Close()

   Return numRowsAffected

End Function


Private Function sqlConnString() as String
    REM -- Get Connstring from config.web
    Dim Context as HttpContext = HttpContext.Current
    Dim AppSettings as HashTable = Ctype(Context.GetConfig("appsettings"),HashTable)
    Dim myDSN as String = Ctype(AppSettings("cnTest"),String)

    Return myDSN
End Function

</script>
<html>
<title>Upload Images to a Database </title>
<body>
<form enctype="multipart/form-data" runat=server id=form1 name=form1>

    Enter A Friendly Name
    <input type=text id=imgName runat="server" />
    
    <br>Select File To Upload:
<input id="UploadFile" type=file runat=server>
<asp:button Text="Upload Me!" OnClick="UploadBtn_Click" runat=server/>
</form>
</body>
</html>