Source File: Upload File to Server using ADO.Net SqlCommand 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"   2. A stored procedure named "spUploadFile" 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 For more information visit the links below: ADO.NET SqlCommand HtmlInput Control ASP.NET Click the link above to download Visual Basic source code in a Visual Studio 2005 web solution which demonstrates how to use the ADO.NET SqlCommand, an HtmlInput control, and an ASP.NET 2.0 web page to to upload a binary large object (BLOB) and insert it into SQL Server table text type column.

评论