asked on

ADO.Net help - Most efficient way to insert and update an image field (blog, filebinary) in .NET

Hi Experts,

I am able to insert and update a binary field using a dataset and dataadapter, however, this takes quite some time as I assume the dataset is populated and that process is expensive and time consuming. Is there a way to do this insert and update bypassing the expensive population of the dataset? Is there a flag to avoid the population of the dataset? The code below works, however, I'm looking for code that will get this work done much faster.

Public Class CurriculumContentItem

Public FileName As String
Public FileSize As Integer
Public FileData() As Byte
Public MimeType As String

Private objDB As Database 'standard database access, the Add and Update functions are custom made to insert and update the file data

Public Function Add() As Boolean
Dim dbConn As SqlConnection = New SqlConnection(objDB.ConnectionString)
Dim dbCmd As New SqlCommand
Dim dbAdapt As New SqlDataAdapter
Dim dbCB As SqlCommandBuilder
Dim intIdentity As Integer

Try
dbAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
dbCmd.CommandText = "Select * from Content_Item"
dbCmd.Connection = dbConn
dbAdapt.SelectCommand = dbCmd

dbConn.Open()
Dim dbSet As DataSet = New DataSet
dbAdapt.Fill(dbSet, "Content_Item")

'and the command builder has to be created after the Fill statement has been executed!
dbCB = New SqlCommandBuilder(dbAdapt)
Dim dbTable As DataTable = dbSet.Tables("Content_Item")
Dim dbRow As DataRow = dbTable.NewRow()

dbRow("File_Name") = FileName
dbRow("Mime_Type") = MimeType
dbRow("File_Size") = FileSize
dbRow("File_Data") = FileData

dbTable.Rows.Add(dbRow)
dbAdapt.Update(dbSet, "Content_Item")

If dbRow.IsNull("Content_Item_ID") = False Then
intIdentity = CType(dbRow("Content_Item_ID"), Integer)
If intIdentity = 0 Then
objDB.SQL = "Select @@Identity"
objDB.DataReader()
If objDB.dr.Read = True Then
If IsDBNull(objDB.dr.GetValue(0)) = True Then
Throw New Exception("Database did not return Identity field in Add function in CurriculumContentItem Class.")
Else
intIdentity = objDB.dr.GetValue(0)
End If
Else
Throw New Exception("Database did not return Identity field in Add function in CurriculumContentItem Class.")
End If
End If
End If

Add = True

dbRow = Nothing
dbSet = Nothing
dbTable = Nothing

Catch ex As Exception
Add = False
Throw ex
Finally
dbCB = Nothing
dbConn = Nothing
dbAdapt = Nothing
End Try

End Function

Public Function Update() As Boolean
Dim dbConn As SqlConnection = New SqlConnection(objDB.ConnectionString)
Dim dbAdapt As SqlDataAdapter
Dim dbSet As New DataSet
Dim dbRow As DataRow
Dim dbCB As SqlCommandBuilder

Try
dbConn.Open()

dbAdapt = New SqlDataAdapter("Select * from Content_Item Where Content_Item_ID=" & ContentItemID, dbConn)
dbAdapt.Fill(dbSet)

dbCB = New SqlCommandBuilder(dbAdapt)

dbRow = dbSet.Tables(0).Rows(0)
dbRow.BeginEdit()

dbRow("File_Name") = FileName
dbRow("Mime_Type") = MimeType
dbRow("File_Size") = FileSize
If FileData Is Nothing = False Then
dbRow("File_Data") = FileData
End If

dbRow.EndEdit()

dbAdapt.Update(dbSet)

Update = True

Catch ex As Exception
Update = False
Throw ex
Finally
dbRow = Nothing
dbSet = Nothing
dbConn = Nothing
dbAdapt = Nothing
dbCB = Nothing
End Try
End Function

end class

ASKER CERTIFIED SOLUTION

bchoor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Knight905416

ASKER

Ok, trying that.

Looks good until I get to the one dimensional byte array that represents the file data:

Dim dbConn As SqlConnection = New SqlConnection(objDB.ConnectionString)
Dim dbCmd As New SqlCommand
Dim dbType As System.Data.SqlDbType
Dim strSQL As String

strSQL = "INSERT INTO Content_Item " & _
"(Title, Text_Area, File_Data, File_Name) " & _
"VALUES(@title, @textarea, @filedata, @filename)"

dbCmd.Connection = dbConn
dbCmd.CommandText = strSQL

dbCmd.Parameters.Add(New SqlParameter("@title", dbType.VarChar).Value = Title)
dbCmd.Parameters.Add(New SqlParameter("@textarea", dbType.Text).Value = TextArea)
dbCmd.Parameters.Add(New SqlParameter("@filedata", dbType.Image).Value = FileData)'Error here, one dimensional byte array
dbCmd.Parameters.Add(New SqlParameter("@filename", dbType.VarChar).Value = FileName)

However, the FileData assignment causes a dynamic help message:

"Operator '=' is not defined for types 'System.Object' and '1-dimensional array of Byte'. Use 'Is' operator to compare two reference types."

I will research the proper way to make this assignment, however, if you know how to make this assignment, that would be appreciated.

Knight905416

ASKER

Going to try this next:

https://www.experts-exchange.com/questions/21459987/save-an-image-byte-array-to-sql-server.html?query=SqlParameter+Image&clearTAFilter=true

bchoor

move closing parentheses from end_of_line to before ".value"
to here.....v
dbCmd.Parameters.Add(New SqlParameter("@title", dbType.VarChar)).Value = Title
dbCmd.Parameters.Add(New SqlParameter("@textarea", dbType.Text)).Value = TextArea
dbCmd.Parameters.Add(New SqlParameter("@filedata", dbType.Image)).Value = FileData'Error here, one dimensional byte array
dbCmd.Parameters.Add(New SqlParameter("@filename", dbType.VarChar).Value = FileName

or u could do it in a 2 step process
' step 1: Create a parameter instance and add it to the command parameter collection
dbCmd.Parameters.Add(New SqlParameter("@title", dbType.Varchar, 50))

' step 2: Set the value of the parameter in the command parameter collection
dbCmd.Parameters("@title").Value = Title

Knight905416

ASKER

Yes, thanks for the syntax check. That works perfect and very fast.