Completing Your Own SQL Server Based Data Access Helper using COM+ and VB.NET - Executing SQL statements along with BLOB values
(Page 5 of 6 )
The following method shows how to develop a helper method to work with BLOB values.
Public Overloads Sub SQLExecute(ByVal strSQL As String, ByRef BLOBColNames() As String, ByRef ByteArrayObjects() As Object)
Dim Conn As SqlConnection
Dim cmd As SqlCommand
Try
Conn = New SqlConnection(_ConnectionString)
'validations
If BLOBColNames Is Nothing Then
Throw New Exception("No file column names are provided")
End If
If ByteArrayObjects Is Nothing Then
Throw New Exception("No ByteArray(ByteArray objects) are provided to upload the files")
End If
If BLOBColNames.Length <> ByteArrayObjects.Length Then
Throw New Exception("No. of col names doesn't match with no. of Files to be uploaded")
End If
cmd = New SqlCommand(strSQL, Conn)
Dim i As Integer
Dim ColName As String
Dim FileByteArray() As Byte
For i = 0 To BLOBColNames.Length - 1
If BLOBColNames(i) Is Nothing Or ByteArrayObjects(i) Is Nothing Then
Throw New Exception("One of the Column Name or ByteArrayObject contains no information to upload..")
End If
FileByteArray = ByteArrayObjects(i)
ColName = "@" & Trim(BLOBColNames(i))
cmd.Parameters.Add(ColName, System.Data.SqlDbType.Image, FileByteArray.Length).Value = FileByteArray
Next
With cmd
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
Catch ex As Exception
Try
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: " & strSQL)
End Try
End Sub
The following is a simple way to execute the above in DAL:
If Not (m_Customer.arPhoto Is Nothing) Then
If m_Customer.arPhoto.Length > 0 Then
Dim ImageColumns() As String = New String() {"Photo"}
Dim ImageData() As Byte = m_Customer.arPhoto
Dim ByteArrayObjects() As Object = New Object() {ImageData}
db.SQLExecute("update tCustomers set Photo=@Photo where ICNO='" & m_Customer.sICNO & "'", ImageColumns, ByteArrayObjects)
End If
End If
Next: Retrieving a BLOB value from the database >>
More MS SQL Server Articles
More By Jagadish Chaterjee
