We help IT Professionals succeed at work.

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

Knight905416
Knight905416 asked
on
655 Views
Last Modified: 2011-10-03
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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Commented:
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

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.