第一步:定义一个属性类,以备其它方法使用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