Visual Basic.NET快速開發MIS系統

2002-11-29· ·黎宇 ··yesky

　　【摘 要】 本文介紹微軟最新技術Visual Basic.NET在數據庫開發方面的應用。結合數據庫系統開發的知識，介紹了物理表操作的方法，利用Visual Basic.NET的面向對象的特征，利用類的繼承知識，簡化了數據庫系統開發過程。

　　引言

　　以前版本的Visual Basic雖然號稱自己是一種OOP（面向對象）編程語言，但卻不是一個地地道道的OOP編程語言，最多只是半個面向對象的編程語言。但Visual Basic.NET已經是一種完全的面向對象的編程語言。他支持面向對象的所有基本特征：繼承、多態和重載。這使得以前在Visual Basic中很難或根本實現不了的問題，在Visual Basic.NET中可以順利的用簡單的方法實現。

　　自定義數據操作類

　　定義一個數據訪問的基類，并編寫有關數據庫操作的必要方法。

　　定義一個數據訪問類，類名為CData。定義連接Oracle數據庫的方法ConnOracle，獲取數據集的方法GetDataSet, 獲取物理表的方法GetDataTable, 向物理表中插入一行數據的方法Insert, 向物理表中刪除數據的方法Delete, 向物理表中更新數據的方法Update。其實現方法不是本文的重點，在此僅給出代碼，不作詳細分析。代碼如下：

Public Class CDataBase
Dim OleCnnDB As New OleDbConnection()

'連接Oracle數據庫，ServerName:服務器名，UserId:用戶名，UserPwd:用戶密碼
Public Function ConnOracle(ByVal ServerName As String, ByVal UserId As String, ByVal UserPwd As String) As OleDbConnection

　Dim OleCnnDB As New OleDbConnection()
　With OleCnnDB
　　.ConnectionString = "Provider=MSDAORA.1;Password='" & UserPwd & "';User ID='" & UserId & "';Data Source='" & ServerName & "'"
　　Try
　　　.Open()
　　Catch er As Exception
　　　MsgBox(er.ToString)
　　End Try
　End With
　mOleCnnDB = OleCnnDB
　Return OleCnnDB
End Function

'獲取數據集。TableName：表名，strWhere：條件
Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet

　Dim strSql As String
　Dim myDataSet As New DataSet()
　Dim myOleDataAdapter As New OleDbDataAdapter()

　myOleDataAdapter.TableMappings.Add(TableName, TableName)
　strSql = "SELECT * FROM " & TableName & " where " & strWhere

　myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
　Try
　　myOleDataAdapter.Fill(myDataSet)
　Catch er As Exception
　　MsgBox(er.ToString)
　End Try
　Return myDataSet
End Function

'獲取物理表。TableName：表名
Public Overloads Function GetDataTable(ByVal TableName As String) As DataTable
　Dim myDataSet As New DataSet()
　myDataSet = GetDataSet(TableName)
　Return myDataSet.Tables(0)
End Function

'獲取物理表。TableName：表名，strWhere：條件
Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable

　Dim myDataSet As New DataSet()
　myDataSet = GetDataSet(TableName, strWhere)
　Return myDataSet.Tables(0)
End Function

　'向物理表中插入一行數據。TableName：表名，Value：行數據，BeginColumnIndex：開始列
Public Overloads Function Insert(ByVal TableName As String, ByVal Value As Object, Optional ByVal BeginColumnIndex As Int16 = 0) As Boolean

　Dim myDataAdapter As New OleDbDataAdapter()
　Dim strSql As String
　Dim myDataSet As New DataSet()
　Dim dRow As DataRow
　Dim i, len As Int16

　strSql = "SELECT * FROM " & TableName
　myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
　Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
　myDataSet.Tables.Add(TableName)
　myDataAdapter.Fill(myDataSet, TableName)
　dRow = myDataSet.Tables(TableName).NewRow
　len = Value.Length
　For i = BeginColumnIndex To len - 1
　　If Not (IsDBNull(Value(i)) Or IsNothing(Value(i))) Then
　　　dRow.Item(i) = Value(i)
　　End If
　Next
　myDataSet.Tables(TableName).Rows.Add(dRow)
　Try
　　myDataAdapter.Update(myDataSet, TableName)
　Catch er As Exception
　　MsgBox(er.ToString)
　　Return False
　End Try
　myDataSet.Tables.Remove(TableName)
　Return True
End Function

'更新物理表的一個字段的值。strSql：查詢語句，FieldName_Value：字段及與對應的值
Public Overloads Sub Update(ByVal strSql As String, ByVal FieldName_Value As String)

　Dim myDataAdapter As New OleDbDataAdapter()
　Dim myDataSet As New DataSet()
　Dim dRow As DataRow
　Dim TableName, FieldName As String
　Dim Value As Object
　Dim a() As String

　a = strSql.Split(" ")
　TableName = a(3)
　a = FieldName_Value.Split("=")
　FieldName = a(0).Trim
　Value = a(1)
　myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)
　Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
　myDataSet.Tables.Add(TableName)
　myDataAdapter.Fill(myDataSet, TableName)
　dRow = myDataSet.Tables(TableName).Rows(0)
　If Value <> Nothing Then
　　dRow.Item(FieldName) = Value
　End If
　Try
　　myDataAdapter.Update(myDataSet, TableName)
　　myDataSet.Tables.Remove(TableName)
　Catch er As Exception
　　MsgBox(er.ToString)
　End Try
End Sub

'刪除物理表的數據。TableName:表名，strWhere：條件
Public Overloads Sub Delete(ByVal TableName As String, ByVal strWhere As String)
　Dim myReader As OleDbDataReader
　Dim myCommand As New OleDbCommand()
　Dim strSql As String
　strSql = "delete FROM " & TableName & " where " & strWhere
　myCommand.Connection = mOleCnnDB
　myCommand.CommandText = strSql
　Try
　　myReader = myCommand.ExecuteReader()
　　myReader.Close()
　Catch er As Exception
　　MsgBox(er.ToString)
　End Try
End Sub
End Class

　　定義一操作數據庫中物理表的類CData,此類繼承CDataBase,即：

Public Class CData：Inherits CDataBase

　　此類應該由供用戶提供所操作的物理表的表名，指定了表名就可取得該表的所有性質。該表主要完成插入、刪除、更新功能。定義其屬性、方法如下：

　　申明類CData的變量:

'所要操作的表名
Private Shared UpdateTableName As String
'所要操作的表對象
Public Shared UpdateDataTable As New DataTable()
'對應表的一行數據197
Public Shared ObjFields() As Object
'表的字段數
Public Shared FieldCount As Int16
'主關鍵字。我們假設每個物理表都有一個主關鍵字字段fSystemID
Public Shared SystemID As String

　　說明：Shared 關鍵字指示一個或多個被聲明的編程元素將被共享。共享元素不關聯于某類或結構的特定實例。可以通過使用類名或結構名稱或者類或結構的特定實例的變量名稱限定共享元素來訪問它們。

　　申明類CData的屬性UpdateTable,當向UpdateTable賦給了一個已知表的表名，就可確定表的字段數，定義出數據行。這里，先打開表，再重新定義數據行.

Public Property UpdateTable() As String
　Get
　　UpdateTable = UpdateTableName
　End Get
　Set(ByVal Value As String)
　　UpdateTableName = Value.Trim
　　UpdateDataTable = DB.GetDataTable(UpdateTableName)
　　UpdateTableFieldNames = UpdateDataTable.Clone
　　FieldCount = UpdateDataTable.Columns.Count
　　ReDim ObjFields(FieldCount - 1)
　End Set
End Property

'刪除由主關鍵值fSystemID指定的數據行
Public Sub Delete()
　Dim strSQL As String

　strSQL = "Delete from " & UpdateTableName & " where fSystemID=" & SystemID
　DB.Delete(strSQL)
　UpdateDataTable.Rows.Remove(GetRow)
End Sub

'向表UpdateTableName中插入一行數據。數據由ObjFields給出
Public Function Insert() As Boolean
　DB.Insert(UpdateTableName, ObjFields)
End Function

'更新表UpdateTableName所指定的行
Public Shadows Sub Update()
　Dim SetField As String
　Dim i As Int16

　For i = 1 To FieldCount - 1
　　SetField = UpdateTableFieldNames.Columns(i).ColumnName & "=" & ObjFields(i)
　　UpdateField(SetField)
　Next
End Sub

Public Sub UpdateField(ByVal SetField As String)
　Dim StrSQL As String
　StrSQL = "select * from " & UpdateTableName & " where fSystemID= " & SystemID
　DB.Update(StrSQL, SetField)
End Sub

'填充網絡數據
Public Overloads Sub FillGrid(ByVal GridName As DataGrid)
　GridName.DataSource = UpdateDataTable
End Sub

'把數據網格的當前行數據定寫入到輸入控件中
Public Sub DataGridToText(ByVal frm As Form)
　Dim RowIndex, i As Int16
　Dim value
　Dim obj As Control
　Dim DataGrid As New DataGrid()
　If FieldCount = 0 Then Exit Sub
　　For Each obj In frm.Controls
　　　If obj.GetType.Name = "DataGrid" Then
　　　　DataGrid = obj
　　　　Exit For
　　　End If
　　Next
　　RowIndex = DataGrid.CurrentRowIndex
　　For i = 1 To FieldCount - 1
　　　value = DataGrid.Item(RowIndex, i)
　　　If IsDBNull(value) = True Then
　　　　value = ""
　　　End If
　　　For Each obj In frm.Controls '
　　　　If obj.TabIndex = i Then
　　　　　obj.Text = value
　　　　　Exit For
　　　　End If
　　　Next
　　Next
End Sub