当前位置导航:炫浪网>>网络学院>>编程开发>>Visual Basic教程

Visual Basic.NET快速开发MIS系统(1)

  
  
  【摘 要】 本文介绍微软最新技术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
  
相关内容
赞助商链接