VB.NET数据库另一种添加删除操作

前端之家收集整理的这篇文章主要介绍了VB.NET数据库另一种添加删除操作前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
第一步:定义一个属性类,以备其它方法使用
Public Class MTarecls

    '保持属性值的局部变量
    Private mvarID As Integer           'ID
    Private mvarSite As String          '站点
    Private mvarOperator As String      '操作员
    Private mvarMatter As String    '提单号
    Private mvarNowDate As Date         '日期
    Private mvarNotes As String         '备注

    Public Property ID() As Integer     'ID号
        Get
            Return mvarID
        End Get
        Set(ByVal value As Integer)
            mvarID = value
        End Set
    End Property

    Public Property Site() As String
        Get
            Return mvarSite
        End Get
        Set(ByVal Value As String)
            mvarSite = Value
        End Set
    End Property

    Public Property Worker() As String
        Get
            Return mvarOperator
        End Get
        Set(ByVal Value As String)
            mvarOperator = Value
        End Set
    End Property

    Public Property Matter() As String
        Get
            Return mvarMatter
        End Get
        Set(ByVal Value As String)
            mvarMatter = Value
        End Set
    End Property

    Public Property NowDate() As Date
        Get
            Return mvarNowDate
        End Get
        Set(ByVal Value As Date)
            mvarNowDate = Value
        End Set
    End Property

    Property Notes() As String
        Get
            Return mvarNotes

        End Get
        Set(ByVal value As String)
            mvarNotes = value
        End Set
    End Property

End Class



第二步:Form操作方法

Public Class Form1
    Dim mytest As New MTarecls

    Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button1.Click
        With mytest
            .ID = 0
            .Site = "2"
            .Worker = "张三"
            .Matter = ""
            .NowDate = Now
            .Notes = "fffffff"
        End With
        Dim Mobj As New Btarecls
        Mobj.Insert(mytest)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button2.Click
        With mytest
            .ID = 1
            .Site = "2"
            .Worker = "李四"
            .Matter = ""
            .NowDate = Now
            .Notes = "fffffff"
        End With

        Dim Mobj As New Btarecls
        Mobj.Update(mytest)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button3.Click
        With mytest
            .ID = 3
        End With

        Dim Mobj As New Btarecls
        Mobj.Delete(1)
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button4.Click
        Dim mynew As ArrayList
        Dim myT As MTarecls
        Dim Myobj As New Btarecls
        mynew = Myobj.Find("2")
        If mynew Is Nothing Then
            MsgBox("没有记录!")
        Else
            MsgBox(mynew.Count.ToString)
            For i = 0 To mynew.Count - 1
                myT = mynew.Item(i)
                Console.WriteLine(myT.ID.ToString)
            Next
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button5.Click
        Dim Mynew As New MTarecls
        Dim mObj As New Btarecls
        Mynew = mObj.Find(2)
        If Mynew Is Nothing Then
            MsgBox("no recorder")
        Else
            MsgBox(Mynew.Worker.ToString)
        End If

    End Sub
End Class


第三步  定义一个操作方法
Imports System.Data.OleDb

Public Class Btarecls
    Public Const cTableName As String = "tbLog"     'Table
    Public Const cID As String = "ID"                   'ID
    Public Const cSite As String = "站点"               '站点
    Public Const cOperator As String = "操作者"       '操作员
    Public Const cMatter As String = "事件"           'shijian
    Public Const cNowDate As String = "日期"         '日期
    Public Const cNotes As String = "备注"             '备注

    Private CONNSTRING As String = Global.TestTare_ok.My.MySettings.Default.dbConnstring
    Private Const SELECTsql As String = "tbLogSelectCommand"
    Private Const UPDATEsql As String = "tbLogUpdateCommand"
    Private Const DELETEsql As String = "tbLogDeleteCommand"  '"DELETE FROM tbTare WHERE ID=?"
    Private Const INSERTsql As String = "tbLogInsertCommand"
    Private Const FINDsql As String = "select * from tbLog  where  站点=?"
    ' Private Const REPLYsql As String = "ReplyMessage"

    Private cnn As OleDbConnection
    Private InsCmd As OleDbCommand
    Private DelCmd As OleDbCommand
    Private SelCmd As OleDbCommand
    Private UpdCmd As OleDbCommand
    Private FndCmd As OleDbCommand
    Private DAdapter As OleDbDataAdapter
    Private table As DataTable

    Public Sub New()
        createTable()
        Initcmd()
    End Sub

    '在数据中按字段要求建一个表
    Private Sub createTable()
        table = New DataTable(cTableName)
        Dim dt As DataTable = table

        '生成器表的主键列,自动增量
        Dim Column As DataColumn = dt.Columns.Add(cID,GetType(System.Int32))
        Column.AllowDBNull = False
        Column.AutoIncrement = True
        Dim pkeys(0) As DataColumn
        pkeys(0) = Column
        dt.PrimaryKey = pkeys

        dt.Columns.Add(cSite,GetType(System.String)).AllowDBNull = True
        dt.Columns.Add(cOperator,GetType(System.String)).AllowDBNull = True
        dt.Columns.Add(cMatter,GetType(System.String)).AllowDBNull = True
        dt.Columns.Add(cNowDate,GetType(System.DateTime)).AllowDBNull = True
        dt.Columns.Add(cNotes,GetType(System.String)).AllowDBNull = True
    End Sub

    '初始化各命令及适配器
    Private Sub Initcmd()

        Me.cnn = New OleDbConnection(CONNSTRING)
        getInsCmd()
        getUpdCmd()
        getSelCmd()
        getDelCmd()
        getFndCmd()
        'DAdapter
        '
        Me.DAdapter = New OleDbDataAdapter
        Me.DAdapter.DeleteCommand = Me.DelCmd
        Me.DAdapter.InsertCommand = Me.InsCmd
        Me.DAdapter.SelectCommand = Me.SelCmd
        Me.DAdapter.UpdateCommand = Me.UpdCmd

        Me.DAdapter.TableMappings.AddRange( _
           New System.Data.Common.DataTableMapping() { _
               New System.Data.Common.DataTableMapping("Table",cTableName,_
                   New System.Data.Common.DataColumnMapping() { _
                   New System.Data.Common.DataColumnMapping(cID,cID),_
                   New System.Data.Common.DataColumnMapping(cSite,cSite),_
                   New System.Data.Common.DataColumnMapping(cOperator,cOperator),_
                   New System.Data.Common.DataColumnMapping(cMatter,cMatter),_
                   New System.Data.Common.DataColumnMapping(cNowDate,cNowDate),_
                   New System.Data.Common.DataColumnMapping(cNotes,cNotes) _
                   })})
    End Sub

    Private Sub openConnection()
        If cnn Is Nothing Then
            cnn = New OleDbConnection(CONNSTRING)
        End If
        If cnn.State <> ConnectionState.Open Then
            cnn.Open()
        End If
    End Sub

    '建立SelectCommand对象
    Private Sub getSelCmd()
        If SelCmd Is Nothing Then
            SelCmd = New OleDbCommand(SELECTsql,cnn)
            SelCmd.CommandType = CommandType.StoredProcedure
            SelCmd.Parameters.AddRange(New OleDbParameter() { _
            New OleDbParameter(cID,OleDbType.Integer,cID)})
            ''New OleDbParameter(C_SHIP,OleDbType.VarChar,C_SHIP),_
            ''New OleDbParameter(C_LUSHU,OleDbType.Single,C_LUSHU),_
            'New OleDbParameter(C_pkid,C_pkid) _
            '   })
        End If
    End Sub

    '建立InsertCommand对象
    Private Sub getInsCmd()
        If InsCmd Is Nothing Then
            InsCmd = New OleDbCommand(INSERTsql,cnn)
            InsCmd.CommandType = CommandType.StoredProcedure
            Me.InsCmd.Parameters.AddRange(New OleDbParameter() { _
            New OleDbParameter(cID,_
            New OleDbParameter(cSite,_
            New OleDbParameter(cOperator,_
            New OleDbParameter(cMatter,_
            New OleDbParameter(cNowDate,OleDbType.DBTimeStamp,_
            New OleDbParameter(cNotes,cNotes)})
        End If
    End Sub

    '建立UpdateCommand对象
    Private Sub getUpdCmd()
        If UpdCmd Is Nothing Then
            UpdCmd = New OleDbCommand(UPDATEsql,cnn)
            UpdCmd.CommandType = CommandType.StoredProcedure
            Me.UpdCmd.Parameters.AddRange(New OleDbParameter() { _
            New OleDbParameter(cID,cNotes)})
        End If
    End Sub

    '建立DeleteCommand对象
    Private Sub getDelCmd()
        If DelCmd Is Nothing Then
            DelCmd = New OleDbCommand(DELETEsql,cnn)
            DelCmd.CommandType = CommandType.StoredProcedure
            DelCmd.Parameters.AddRange(New OleDbParameter() { _
                        New OleDbParameter(cID,cID)})
        End If
    End Sub

    '建立FindCommand对象
    Private Sub getFndCmd()
        If FndCmd Is Nothing Then
            FndCmd = New OleDbCommand(FINDsql,cnn)
            FndCmd.CommandType = CommandType.Text
            FndCmd.Parameters.AddRange(New OleDbParameter() { _
             New OleDbParameter(cSite,0)})
            'New OleDbParameter(cTravelNo,0),_
            'New OleDbParameter(cLOnes,0)
        End If
    End Sub

    '向数据库增加一个供应商实体对象 
    Function Insert(ByVal s As MTarecls) As Boolean
        '打开连接
        openConnection()
        getInsCmd()         '获取一个Insert的Command对象
        Try
            With InsCmd
                '填写存储过程所需要的参数
                .Parameters(cID).Value = s.ID
                .Parameters(cSite).Value = s.Site              
                .Parameters(cOperator).Value = s.Worker
                .Parameters(cMatter).Value = s.Matter
                .Parameters(cNowDate).Value = s.NowDate
                .Parameters(cNotes).Value = s.Notes
                '执行Insert操作
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    '修改某个供应商实体信息
    Function Update(ByVal s As MTarecls) As Boolean
        '打开连接
        openConnection()
        '生成Update的Command对象
        getUpdCmd()
        Try
            With UpdCmd
                '填写存储过程所需要的参数
                .Parameters(cID).Value = s.ID
                .Parameters(cSite).Value = s.Site
                .Parameters(cOperator).Value = s.Worker
                .Parameters(cMatter).Value = s.Matter
                .Parameters(cNowDate).Value = s.NowDate
                .Parameters(cNotes).Value = s.Notes
                '执行Insert操作
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    '从数据库删除某个供应商实价体
    Function Delete(ByVal s As Integer) As Boolean
        openConnection()
        getDelCmd()
        Try
            With DelCmd
                .Parameters(cID).Value = s
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function


    '从数据库个按条件进行查询某个供应商实价体
    Function Find(ByVal pSite As String) As ArrayList      ' MTarecls
        openConnection()
        getFndCmd()

        With FndCmd
            .Parameters(cSite).Value = pSite
            '.Parameters(cTravelNo).Value = pTravelNo
            '.Parameters(cLOnes).Value = pLOnes
        End With
        Dim dr As OleDbDataReader = FndCmd.ExecuteReader
        Dim al As New ArrayList
        Dim c As MTarecls
        Dim i As Integer
        Try
            While dr.Read
                c = New MTarecls
                c.ID = dr(cID)
                c.Site = dr(cSite)
                c.Worker = dr(cOperator)
                c.Matter = dr(cMatter)
                c.NowDate = dr(cNowDate)
                c.Notes = dr(cNotes)
                i = i + 1
                al.Add(c)
            End While
            If i >= 1 Then
                Return al
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        Finally
            dr.Close()
        End Try
    End Function

    Function Find(ByVal sID As Integer) As MTarecls     '按序号查询只有唯一值
        openConnection()
        getSelCmd()

        With SelCmd
            .Parameters(cID).Value = sID
            '.Parameters(C_SHIP).Value = s.Ship
            '.Parameters(C_LUSHU).Value = s.LuShu
            '.Parameters(C_STATION).Value = s.Station
        End With
        Dim dr As OleDbDataReader = SelCmd.ExecuteReader
        Dim al As New ArrayList
        Dim c As MTarecls
        Dim i As Integer
        Try
            While dr.Read
                c = New MTarecls
                c.ID = dr(cID)
                c.Site = dr(cSite)
                c.Worker = dr(cOperator)
                c.Matter = dr(cMatter)
                c.NowDate = dr(cNowDate)
                c.Notes = dr(cNotes)
                i = i + 1
                al.Add(c)
            End While

            If i >= 1 Then
                Return c
            Else
                Return Nothing
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        Finally
            dr.Close()
        End Try

    End Function

End Class
原文链接:https://www.f2er.com/vb/260856.html

猜你在找的VB相关文章