mike mcintyre's

This source code demonstrates how to use an ASP.NET web page to upload a binary large object (BLOB) and insert it, using a stored procedure, into a SQL Server table text type column.

Client-side a file is selected and submitted via an ASP.NET web page.

Server-side, the web page processes the request by building an ADO.NET SqlCommand object that passes the file data to the SQL Server table via a stored procedure.

In the attached source code, a SQL Server database is included as a MDF file in the web site's App_Data folder. In it are two database objects:

1. A table named "FileImage"

Code

    Protected Sub uploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadButton.Click

        ' Declare a variable named filePath of type String.

        Dim filePath As String

        ' Assign the pages FileInput control's value to the filePath variable.

        filePath = Me.FileInput.Value

        ' Use the File class' Exits method to verify the path exists on the client's computer.

        If Not File.Exists(filePath) Then

            ' If the path is not valid, message user and exit this Sub

            Me.statusLabel.Text = "File path is invalid. Please try again."

            Return

        End If

        ' Declare a variable named fileType of type String.

        Dim fileType As String

        ' Call the Path class' GetExtention method assigning

        ' the result to the fileType variable.

        fileType = Path.GetExtension(filePath)

        ' Declare a variable named fileName of type String.

        Dim fileName As String

        ' Call the Path class' WithoutExtension method assigning

        ' the result to the fileName variable.

        fileName = Path.GetFileNameWithoutExtension(filePath)

        ' Declare a variable named fileLength of type Integer.

        Dim fileLength As Integer

        ' Assign the value of the FileInput object's ContentLength property

        ' to the fileLength variable.

        fileLength = Me.FileInput.PostedFile.ContentLength

        ' Declare a variable named fileStream of type Stream.

        Dim fileStream As Stream

        ' Declare a variable of type byte array.

        Dim buffer(fileLength) As Byte

        ' Assign the FileInput object's input stream

        ' to the fileStream variable.

        fileStream = Me.FileInput.PostedFile.InputStream

        ' Call the fileStream object's read method.

        fileStream.Read(buffer, 0, fileLength)

        ' Declare a variable named theConnection of type SqlConnection.

        Dim theConnection As SqlConnection

        ' Call the SqlConnection class' New constructor passing in

        ' a connection string from the web.config file.

        ' Assign the resulting SqlConnection object to theConnection variable.

        theConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("FilesConnectionString").ConnectionString)

        ' Use Try..Catch..Finally to handle exceptions.

        ' NOTE: Exception handling underdone to keep example simple.

        Try

            ' Declare a variable named uploadSqlCommand of type SqlCommand.

            Dim uploadSqlCommand As SqlCommand

            ' Call the SqlCommand class' New constructor passing in

            ' the name of a stored procedure in the database and theConnection.

            ' Assign the resulting SqlCommand object to the uploadSqlCommand variable.

            uploadSqlCommand = New SqlCommand("spUploadFile", theConnection)

            ' Set the SqlCommand object's CommandType property to StoredProcedure.

            uploadSqlCommand.CommandType = Data.CommandType.StoredProcedure

            ' /// Add four parameters to the SqlCommand object's Parameters collection,

            ' Add a parameter to pass in the file name.

            uploadSqlCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 50)

            ' Add a parameter to pass in the file bytes.

            uploadSqlCommand.Parameters.Add("@FileImage", SqlDbType.Image)

            ' Add a parameter to pass in the file type.

            uploadSqlCommand.Parameters.Add("@FileType", SqlDbType.VarChar, 5)

            ' Add a parameter to pass in the time the file is uploaded.

            uploadSqlCommand.Parameters.Add("@TimeUploaded", SqlDbType.DateTime)

            ' Assign data to each fo the four parameters.

            uploadSqlCommand.Parameters(0).Value = fileName

            uploadSqlCommand.Parameters(1).Value = buffer

            uploadSqlCommand.Parameters(2).Value = fileType

            uploadSqlCommand.Parameters(3).Value = Date.Now

            ' Open a connection to the Sql Server.

            theConnection.Open()

            ' Call the SqlCommand object's ExecuteNonQuery method

            ' to execute the stored procedure.

            uploadSqlCommand.ExecuteNonQuery()

            ' Closs the connection to the Sql Server.

            theConnection.Close()

            ' Message user.

            Me.statusLabel.Text = "File was successfully submitted to database"

        Catch ex As Exception

            ' Message user.

            Me.statusLabel.Text = "File was NOT successfully sbmitted to database. Exception was: " & ex.Message

        Finally

            If theConnection.State = ConnectionState.Open Then

                theConnection.Close()

            End If

        End Try

    End Sub