Saving and retrieving images from a database

[Rob Venable]

Hi,

Does anyone have any recent examples of saving and retrieving images to/from a database? I'm using visual Studio 2008, Sql 2008 and ASP.Net AJAX Q3 2010 NET35 .

I'm using varbinary(MAX) to store the image stream and I store the contenttype and filesize in the database as well.

This is what I'm doing to save the image:

Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Page.IsValid Then
            Dim oProduct As New Product
            Dim iProductID As Integer = CInt(hdnProductID.Value)
            oProduct = ProductBL.GetProductByID(iProductID)
            oProduct.Name = txtName.Text.Trim
            oProduct.Description = txtDescription.Text.Trim
            Dim imgStream As Stream = fleUpload.PostedFile.InputStream
            oProduct.Filename = fleUpload.PostedFile.FileName
            oProduct.ContentType = fleUpload.PostedFile.ContentType
            oProduct.Filesize = fleUpload.PostedFile.ContentLength
            Dim imgData(oProduct.Filesize) As Byte
            oProduct.imgContent = imgData

            Dim oProductBL As New ProductBL
            If oProductBL.Save(oProduct) > 0 Then
                '== Show save message
            Else
                '== Show error message
            End If
        End If

    End Sub

Everything saves to the database without errors...but when I view the page I get an error.
This is my RadGrid:

<telerik:RadGrid ID="gridProducts" runat="server" AllowPaging="True"
        AllowSorting="True" AutoGenerateColumns="False" GridLines="None"
        Skin="Outlook" width="675px">
        <MasterTableView allowcustompaging="True">
        <CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>

        <RowIndicatorColumn>
        <HeaderStyle Width="20px"></HeaderStyle>
        </RowIndicatorColumn>

        <ExpandCollapseColumn>
        <HeaderStyle Width="20px"></HeaderStyle>
        </ExpandCollapseColumn>
          <Columns>
            <telerik:GridTemplateColumn HeaderText="Item" UniqueName="Item">
              <ItemTemplate>
                <telerik:RadBinaryImage ID="rbiItem" runat="server" />
              </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn HeaderText="Item Description" UniqueName="ItemDescription">
              <ItemTemplate>
                <asp:Label ID="lblItem" runat="server" Text=""></asp:Label>
                <br />
                <asp:Label ID="lblDescription" runat="server" Text=""></asp:Label>
              </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn HeaderText="Price" UniqueName="Price">
              <ItemTemplate>
                <asp:Label ID="lblPrice" runat="server" Text=""></asp:Label>
              </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn HeaderText="Add to Cart">
              <ItemTemplate>
                <asp:ImageButton ID="imgCart" OnCommand="imgCart_Command" CommandName="Add" runat="server" />
              </ItemTemplate>
            </telerik:GridTemplateColumn>
          </Columns>
        </MasterTableView>
        <ClientSettings>
          <Selecting AllowRowSelect="True" />
        </ClientSettings>
      </telerik:RadGrid>

I'm trying to retrieve the image into the RadBinaryImage control rbiItem.
This is how I retrieve the images into a gridview:

Private Sub LoadGrid()
gridProducts.DataSource = ProductBL.GetActiveProducts
gridProducts.DataBind()
End Sub

Private Sub gridProducts_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles gridProducts.ItemDataBound
        If (TypeOf (e.Item) Is GridDataItem) Then
            Dim dr As PawnShop.Entities.Product = CType(e.Item.DataItem, PawnShop.Entities.Product)

            Dim lblItem As Label = CType(e.Item.FindControl("lblItem"), Label)
            lblItem.Text = dr.Name

            Dim lblDescription As Label = CType(e.Item.FindControl("lblDescription"), Label)
            lblDescription.Text = dr.Description

            Dim lblPrice As Label = CType(e.Item.FindControl("lblPrice"), Label)
            lblPrice.Text = String.Format(Constants.CURRENCY, dr.Price)

            Dim imgCart As ImageButton = CType(e.Item.FindControl("imgCart"), ImageButton)
            imgCart.ImageUrl = "~/images/shoping_cart_sm.png"
            imgCart.CommandArgument = dr.ProductID.ToString
            imgCart.CommandName = "Add"
                                '==Get the image from the database
            Dim rbiItem As RadBinaryImage = CType(e.Item.FindControl("rbiItem"), RadBinaryImage)
            Response.ContentType = dr.ContentType
            rbiItem.DataValue = CType(dr.imgContent, Byte())
        End If
    End Sub

And this is the error I get:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Parameter is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Parameter is not valid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[ArgumentException: Parameter is not valid.]
   System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData) +1062379
   System.Drawing.Image.FromStream(Stream stream) +8
   Telerik.Web.UI.ImageFilterHelper.CreateImgFromBytes(Byte[] image) +99

[ArgumentException: The provided binary data may not be valid image or may contains unknown header]
   Telerik.Web.UI.ImageFilterHelper.CreateImgFromBytes(Byte[] image) +173
   Telerik.Web.UI.RadBinaryImage.AutoAdjustImageElementSize(Byte[] dataValue) +51
   Telerik.Web.UI.RadBinaryImage.ProcessImageData() +188
   Telerik.Web.UI.RadBinaryImage.OnPreRender(EventArgs e) +41
   System.Web.UI.Control.PreRenderRecursiveInternal() +80
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4955

Does this mean that I'm not storing it correctly or am I not retrieving it correctly?

Can anyone help me on this?
Thanks

[Armand]

There may be other better solutions but this one is working for me.
1. Do not allow automatic updates/insert from Radgrid
2. Do not use generated datasource. Populate datasource and grid with code behind.
3. Update/Insert from code behind. Simplified VB code is:
4. Varbinary(max) is 100%
5. In the code sample below I update/insert directly from the Radgrid. Please adjust accordingly if you insert from outside grid.

Private Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles RadGrid1.ItemCommand
        If e.CommandName = "Update" Or e.CommandName = "PerformInsert" Then
            Dim eeditedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)
            Dim ImageName As String = (TryCast(eeditedItem("ImageName").Controls(0), TextBox)).Text
            Dim ImageDescription As String = (TryCast(eeditedItem("ImageDescription").Controls(0), TextBox)).Text
            Dim ImageID As String = (TryCast(eeditedItem("ImageID").Controls(0), TextBox)).Text



            'get file
            Dim editItem As GridEditableItem = DirectCast(e.Item, GridEditableItem)
            Dim upload As GridBinaryImageColumnEditor = DirectCast(editItem.EditManager.GetColumnEditor("ImageBinary"), GridBinaryImageColumnEditor)

            SqlCommand.Parameters.Add("@ImageBinary", SqlDbType.VarBinary, -1).Value = upload.UploadedFileContent
            Try
                'Open the SqlConnection
                SqlConnection.Open()
                'Update Query to insert into  the database
                SqlCommand.Connection = SqlConnection
                If e.CommandName = "Update" Then
                    SqlCommand.CommandText = "UPDATE Images SET ImageName = @Imagename,ImageDescription=@ImageDescription,ImageBinary=@Imagebinary WHERE ImageID=@ImageID"
                    SqlCommand.Parameters.Add("@ImageID", SqlDbType.Int).Value = ImageID
                End If
                If e.CommandName = "PerformInsert" Then
                    SqlCommand.CommandText = "INSERT INTO  Images (CalltakingLogID,ImageName,ImageDescription,ImageBinary) VALUES (@CalltakingLogid,@Imagename,@ImageDescription,@ImageBinary)"
                    SqlCommand.Parameters.Add("@CallTakingLogID", SqlDbType.Int).Value = calltakinglogid
                End If
                SqlCommand.Parameters.Add("@ImageName", SqlDbType.VarChar).Value = ImageName
                SqlCommand.Parameters.Add("@ImageDescription", SqlDbType.VarChar).Value = ImageDescription
                SqlCommand.ExecuteNonQuery()
                'Close the SqlConnection
                SqlConnection.Close()
                'CLOSE FORM

                'RadGrid1.MasterTableView.IsItemInserted = False
                'RadGrid1.MasterTableView.ClearEditItems()
                'RadGrid1.DataBind()


            Catch ex As Exception
                RadGrid1.Controls.Add(New LiteralControl("Unable to Update " + ex.Message))
                e.Canceled = True
            End Try
        End If

        If e.CommandName = "Delete" Then
            Dim eeditedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)
            Dim ImageID As String = e.Item.OwnerTableView.DataKeyValues(e.Item.ItemIndex)("ImageID")
            SqlCommand.Connection = SqlConnection
            SqlCommand.CommandText = "DELETE FROM Images WHERE ImageID=@ImageID"
            SqlCommand.Parameters.Add("@ImageID", SqlDbType.Int).Value = ImageID
            Try
                SqlConnection.Open()
                SqlCommand.ExecuteNonQuery()
            Catch ex As Exception
                RadGrid1.Controls.Add(New LiteralControl("Unable to Delete " + ex.Message))
                e.Canceled = True
            End Try
            SqlConnection.Close()
        End If
    End Sub

And to get the data for the radgrid

Protected Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource

       RadGrid1.DataSource = GetDataTable()

   End Sub



Public Function GetDataTable() As DataTable

       Dim adapter As SqlDataAdapter = New SqlDataAdapter
       Dim query As String = "Select * from Images Where CallTakingLogID = @Calltakinglogid"
       adapter.SelectCommand = New SqlCommand(Query, conn)
       adapter.SelectCommand.CommandType = CommandType.Text
       adapter.SelectCommand.Parameters.Add("@CallTakingLogid", SqlDbType.Int).Value = calltakinglogid
       Dim table1 As New DataTable
       conn.Open()
       Try
           adapter.Fill(table1)
       Finally
           conn.Close()
       End Try
       Return table1
   End Function

[Rob Venable]

Hi Armand,
I'm afraid my structure is different than yours. I'm not updating from my grid...I have an admin site that does the updating from an upload control. I just need to display the images in the grid. I have a data layer that returns a collection of products which contain the images and I was hoping to be able to use that to display my images. I don't have a connection string in my web.config so I don't really use data adapters or even use any sql data access in my UI layer. I'm not sure how you display your images in the grid...I don't see your markup for the grid so I don't see your dataAdapter code to display the image.
There's gotta be an easier way to display images. I'm sure your way is fine but I'm trying to incorporate my 3 tiered architecture to work with the grid.

Thanks though.

[Tsvetina]

Hello Rob,

First thing that I would recommend to you is to replace the simple data-binding that you use with advanced data-binding, since sorting and paging are not supported with the former.

In regard of the binary image column, you could try using the built-in GridBinaryImage column and see if you get the same error.

Best wishes,
Tsvetina
the Telerik team

[Rob Venable]

Thanks,
It turns out it was an issue with saving it to the database. Although it didn't error out when I saved an image to the database, all of the bytes in my array were zero length. I fixed the method to save and then it worked. I'm using the RadBinaryImage and found it very easy to bind the image to my grid.

Thanks for your help.