OleDb.OleDbCommand.Update

好久沒看到這樣寫法了,收藏!
引用來源
----
 


Imports System.Data
Imports System.Data.OleDb

Public Class ItemDM
    Public ItemNo As String
    Public ItemName As String
    Public DescriptionCN As String
    Public ItemSize As String
    Public DanWei As String
    Public TypeS As String
    Public ColorS As String
    Public Price As Single
    Public BatchPrice As Single
    Public QTY As Integer
    Public SumMoney As Single
    Public MainProvider As String
    Public MainBuyer As String
    Public InCKDate As Date
    Public InCKAddress As String
    Public InPutDate As Date
    Public Remarks As String
End Class

Public Class ItemInfoClass
    Dim Conn As OleDbConnection
    Dim Cmd As OleDbCommand
    Dim DaAdapter As OleDbDataAdapter

    Public Function GetItemDMByItemNo(ByVal ItemNo As Integer) As ItemDM
        Dim DBCls As New DBOP
        Dim DT As New DataTable("StoreSys")
        Dim ReDM As New ItemDM
        SqlStr = "Select * From StoreSys Where ItmeNo='" & ItemNo & "'"
        DT = DBCls.GetDataTable(SqlStr)
        If DT.Rows.Count > 0 Then
            ReDM.ItemName = DT.Rows(0)(0).ToString
            ReDM.DescriptionCN = DT.Rows(0)(1).ToString
            ReDM.ItemSize = DT.Rows(0)(2).ToString
            ReDM.DanWei = DT.Rows(0)(3).ToString
            ReDM.TypeS = DT.Rows(0)(4).ToString
            ReDM.ColorS = DT.Rows(0)(5).ToString
            ReDM.Price = DT.Rows(0)(6).ToString
            ReDM.BatchPrice = DT.Rows(0)(7).ToString
            ReDM.QTY = DT.Rows(0)(8).ToString
            ReDM.SumMoney = DT.Rows(0)(9).ToString
            ReDM.MainProvider = DT.Rows(0)(10).ToString
            ReDM.MainBuyer = DT.Rows(0)(11).ToString
            ReDM.InCKDate = DT.Rows(0)(12).ToString
            ReDM.InCKAddress = DT.Rows(0)(13).ToString
            ReDM.InPutDate = DT.Rows(0)(14).ToString
            ReDM.Remarks = DT.Rows(0)(15).ToString
        End If
        Return ReDM
        DT = Nothing
        DBCls = Nothing
    End Function

    Public Function Add(ByVal DM As ItemDM) As Boolean
        SqlStr = "Insert Into StoreSys (ItemNo,ItemName,DescriptionCN,ItemSize,DanWei,TypeS,ColorS,Price,BatchPrice,QTY,SumMoney,MainProvider,MainBuyer,InCKDate,InCKAddress,InPutDate,Remarks)"
        SqlStr = SqlStr & " Values (@ItemNo,@ItemName,@DescriptionCN,@ItemSize,@DanWei,@TypeS,@ColorS,@Price,@BatchPrice,@QTY,@SumMoney,@MainProvider,@MainBuyer,@InCKDate,@InCKAddress,@InPutDate,@Remarks) "
        If ExeCmd(DM, SqlStr, "Insert") Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Update(ByVal DM As ItemDM) As Boolean
        SqlStr = "Update StoreSys Set ItemName=@ItemName,DescriptionCN=@DescriptionCN,ItemSize=@ItemSize,DanWei=@DanWei,ColorS=@ColorS,Price=@Price,BatchPrice=@BatchPrice,QTY=@QTY,SumMoney=@SumMoney,MainProvider=@MainProvider,MainBuyer=@MainBuyer,InCKDate=@InCKDate,InCKAddress=@InCKAddress,InPutDate=@InPutDate,Remarks=@Remarks Where (ItemNo=@ItemNo and TypeS=@TypeS)"
        If ExeCmd(DM, SqlStr, "Update") Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Delete(ByVal ItemNo As String) As Boolean
        SqlStr = "Delete From StoreSys Where ItemNo='" & ItemNo & "'"
        Conn = New OleDbConnection(DBOP.ConnStr)
        Conn.Open()
        Cmd = New OleDbCommand(SqlStr, Conn)
        Try
            Cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        End Try
        Cmd = Nothing
        Conn.Close()
        Conn = Nothing
    End Function

    Public Function CheckItemNoIsExist(ByVal ItemNo As String) As Boolean
        SqlStr = "Select Count(*) From StoreSys Where ItemNo='" & ItemNo & "'"
        Dim DBCls As New DBOP
        If DBCls.CheckRSExistByKeyItem(SqlStr) Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function ExeCmd(ByVal DM As ItemDM, ByVal SqlCmdStr As String, ByVal OpType As String) As Boolean
        Dim ItemNo As New OleDbParameter("@ItemNo", OleDbType.VarChar, 30)
        Dim ItemName As New OleDbParameter("@ItemName", OleDbType.VarChar, 50)
        Dim DescriptionCN As New OleDbParameter("@DescriptionCN", OleDbType.VarChar, 255)
        Dim ItemSize As New OleDbParameter("@ItemSize", OleDbType.VarChar, 50)
        Dim DanWei As New OleDbParameter("@DanWei", OleDbType.VarChar, 10)
        Dim TypeS As New OleDbParameter("@TypeS", OleDbType.VarChar, 50)
        Dim ColorS As New OleDbParameter("@ColorS", OleDbType.VarChar, 50)
        Dim Price As New OleDbParameter("@Price", OleDbType.Decimal)
        Dim BatchPrice As New OleDbParameter("@BatchPrice", OleDbType.Decimal)
        Dim QTY As New OleDbParameter("@QTY", OleDbType.Decimal)
        Dim SumMoney As New OleDbParameter("@SumMoney", OleDbType.Decimal)
        Dim MainProvider As New OleDbParameter("@MainProvider", OleDbType.VarChar, 255)
        Dim MainBuyer As New OleDbParameter("@MainBuyer", OleDbType.VarChar, 30)
        Dim InCKDate As New OleDbParameter("@InCKDate", OleDbType.DBDate)
        Dim InCKAddress As New OleDbParameter("@InCKAddress", OleDbType.VarChar, 255)
        Dim InPutDate As New OleDbParameter("@InPutDate", OleDbType.DBDate)
        Dim Remarks As New OleDbParameter("@Remarks", OleDbType.VarChar, 255)
        Conn = New OleDbConnection(DBOP.ConnStr)
        Conn.Open()
        Cmd = New OleDbCommand(SqlCmdStr, Conn)
        If OpType = "Insert" Then
            Cmd.Parameters.Add(ItemNo)
            Cmd.Parameters.Add(ItemName)
            Cmd.Parameters.Add(DescriptionCN)
            Cmd.Parameters.Add(ItemSize)
            Cmd.Parameters.Add(DanWei)
            Cmd.Parameters.Add(TypeS)
            Cmd.Parameters.Add(ColorS)
            Cmd.Parameters.Add(Price)
            Cmd.Parameters.Add(BatchPrice)
            Cmd.Parameters.Add(QTY)
            Cmd.Parameters.Add(SumMoney)
            Cmd.Parameters.Add(MainProvider)
            Cmd.Parameters.Add(MainBuyer)
            Cmd.Parameters.Add(InCKDate)
            Cmd.Parameters.Add(InCKAddress)
            Cmd.Parameters.Add(InPutDate)
            Cmd.Parameters.Add(Remarks)
        ElseIf OpType = "Update" Then
            Cmd.Parameters.Add(ItemName)
            Cmd.Parameters.Add(DescriptionCN)
            Cmd.Parameters.Add(ItemSize)
            Cmd.Parameters.Add(DanWei)
            Cmd.Parameters.Add(ColorS)
            Cmd.Parameters.Add(Price)
            Cmd.Parameters.Add(BatchPrice)
            Cmd.Parameters.Add(QTY)
            Cmd.Parameters.Add(SumMoney)
            Cmd.Parameters.Add(MainProvider)
            Cmd.Parameters.Add(MainBuyer)
            Cmd.Parameters.Add(InCKDate)
            Cmd.Parameters.Add(InCKAddress)
            Cmd.Parameters.Add(InPutDate)
            Cmd.Parameters.Add(Remarks)
            Cmd.Parameters.Add(ItemNo)
            Cmd.Parameters.Add(TypeS)
        End If
        ItemNo.Value = DM.ItemNo
        ItemName.Value = DM.ItemName
        DescriptionCN.Value = DM.DescriptionCN
        ItemSize.Value = DM.ItemSize
        DanWei.Value = DM.DanWei
        TypeS.Value = DM.TypeS
        ColorS.Value = DM.ColorS
        Price.Value = DM.Price
        BatchPrice.Value = DM.BatchPrice
        QTY.Value = DM.QTY
        SumMoney.Value = DM.SumMoney
        MainProvider.Value = DM.MainProvider
        MainBuyer.Value = DM.MainBuyer
        InCKDate.Value = DM.InCKDate
        InCKAddress.Value = DM.InCKAddress
        InPutDate.Value = DM.InPutDate
        Remarks.Value = DM.Remarks
        Try
            Cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        End Try
        Cmd = Nothing
        Conn.Close()
        Conn = Nothing
    End Function

End Class