How to save image to database using N-tier architecture

See more:

VB

SQL

WinForms

server

N-Tier

Hi All,

Am new to N-tier programming and am trying to save an image to the SQL Server database. The field type for the image is varbinary(MAX). In my first attempt I tried to pass the image as an object to the DAL but I got conversion errors. In the second attempt, I am converting the image at the time of writing to the database but am still not able to save the image to the database. My code is as below and would appreciate help in correcting the errors/code.

Thanks

Business Layer

Private m_ProductCode As String
Private m_ProductPhotoPath As String
Private m_ProductPhoto As Byte


Public Property ProductCode() As String
    Get
        Return m_ProductCode
    End Get
    Set(ByVal value As String)
        m_ProductCode = value
    End Set
End Property

Public Property ProductPhotoPath() As String
    Get
        Return m_ProductPhotoPath
    End Get
    Set(ByVal value As String)
        m_ProductPhotoPath = value
    End Set
End Property

Public Property ProductPhoto() As Byte
    Get
        Return m_ProductPhoto
    End Get
    Set(ByVal value As Byte)
        m_ProductPhoto = value
    End Set
End Property

Presentation Layer

Private Sub InsertNewRecord()
    Dim clsdbo_ProductsMaster As New AppName.BusinessLogic.BusinessLogic.BLL_ProductsMaster
    If VerifyData() = True Then
        AssignDataParameters(clsdbo_ProductsMaster)
        Dim blnSucess As Boolean
        blnSucess = AppName.DataAccess.DAL_ProductsMaster.AddProductMasterRecord(clsdbo_ProductsMaster)
        If blnSucess = True Then
            lblMessage.Text = "*** New Record Added Successfully ***"
            ErrorProvider1.Clear()
        Else
            MsgBox("**** Insert Failed ****", MsgBoxStyle.OkOnly, MsgboxCaption)
        End If
    End If
End Sub


Private Sub AssignDataParameters(ByVal clsdbo_ProductsMaster As AppName.BusinessLogic.BusinessLogic.BLL_ProductsMaster)
    With clsdbo_ProductsMaster
        .ProductCode = System.Convert.ToString(lblProductCode.Text)
        .ProductPhotoPath = If(String.IsNullOrEmpty(lblProductPhotoPath.Text), Nothing, lblProductPhotoPath.Text)
    End With
End Sub

Data Access Layer

Public Shared Function AddProductMasterRecord(ByVal clsdbo_ProductMasterPara As AppName.BusinessLogic.BusinessLogic.BLL_ProductsMaster) As Boolean
    Dim connTemp As SqlConnection = AppName.DataAccess.eSuiteDataClass.GetConnection
    Dim strInsertSQL As String = "INSERT INTO [dbo].[ProductsMaster] ([ProductCode], [ProductPhotoPath], [ProductPhoto]) VALUES (@ProductCode, @ProductPhotoPath, @ProductPhoto)"
    Dim strCommandInsert As New SqlCommand(strInsertSQL, connTemp)
    strCommandInsert.CommandType = CommandType.Text
    strCommandInsert.Parameters.AddWithValue("@ProductCode", clsdbo_ProductMasterPara.ProductCode)
    strCommandInsert.Parameters.AddWithValue("@ProductPhotoPath", IIf(Not IsNothing(clsdbo_ProductMasterPara.ProductPhotoPath), clsdbo_ProductMasterPara.ProductPhotoPath, DBNull.Value))
    strCommandInsert.Parameters.Add("@ProductPhoto", SqlDbType.Image)
    If IsNothing(clsdbo_ProductMasterPara.ProductPhotoPath) = True Then
        strCommandInsert.Parameters("@ProductPhoto").Value = DBNull.Value
    Else
        strCommandInsert.Parameters("@ProductPhoto").Value = IO.File.ReadAllBytes(clsdbo_ProductMasterPara.ProductPhotoPath)
    End If
    Try
        connTemp.Open()
        Dim intCount As Integer = strCommandInsert.ExecuteNonQuery()
        If intCount > 0 Then
            Return True
        Else
            Return False
        End If
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Return False
    Finally
        connTemp.Close()
    End Try
    Return True
End Function