Saving and reading textual or numerical data from SQL Server is quite trivial. You can use standard SQL clauses like SELECT and INSERT, use databound controls or LINQ.
However, saving binary data to SQL Server with standard SQL is not so trivial. Basically you need three objects to obtain this, namely connection object, command object and parameter object. Below is a VB.NET sample which inserts a byte array to SQL Server. VB.NET sample below is a template how to do it. You have to provide your own connection string, table name and field name are hard coded. You may want to pass them as parameter rather than use hard coded names.
''' <summary> ''' Save a byte array to database ''' </summary> ''' <param name="ByteArr">Contains bytes to be saved</param> ''' <remarks></remarks> Public Sub SaveByteArray(ByVal ByteArr() As Byte) ' Dim strSQL As String Dim oConn As SqlConnection Dim oCmd As SqlCommand Dim oBLOBParam As SqlParameter Try ' Create and open connection object oConn = New SqlConnection("<Here goes your connection string>") oConn.Open() ' Insert statement ' Notice that @BLOBValue is a placeholder for the actual data strSQL = "INSERT INTO MyImageTable (BlobField) VALUES (@BLOBValue)" ' Create a command object oCmd = oConn.CreateCommand() ' Set SQL statement oCmd.CommandText = strSQL ' Create a command parameter oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _ ByteArr.Length, ParameterDirection.Input.ToString) ' Finally, set the actual data oBLOBParam.Value = ByteArr ' Add this parameter to the command oCmd.Parameters.Add(oBLOBParam) ' Execute SQL statement oCmd.ExecuteNonQuery() ' Close the connection oConn.Close() Catch ex As Exception End Try End Sub
Notice that the "BlobField" in the SQL statement should be declared as Image-type in your database table.
If your table has an Identity field as a primary key, you may want to obtain primary key for this newly inserted image. All you have to do is change the SQL statement and the way the statement is executed.
Declare a variable to hold the new primary key:
Dim NewIdentity As Integer
Change the SQL statement to read new identity value:
strSQL = "INSERT INTO MyImageTable (BlobField) VALUES (@BLOBValue)" strSQL = strSQL & "; SELECT @@IDENTITY AS 'Identity' "
Change "ExecuteNonQuery" as below:
NewIdentity = CInt(oCmd.ExecuteScalar())
Of course you may want to return this value either as a ByRef-parameter or by changing the code to function that returns primary key value.