Imports System.Data.SqlClient
'数据库类型
Public Enum DBKind
SQL 'SQL Server
ORACLE 'Oracle
DB2
ACCESS
TEST
End Enum
Public Class Database
#Region " 变量 "
'属性变量
Private _databaseKind As String
Private _errorMessage As String
Private _databaseState As String
'连接的数据库种类
Private dbServer As DBKind
'Transaction变量
Private sqlTrans As SqlTransaction
'事务状态
Private useTrans As Boolean
'Connection类变量
Private cnSQL As SqlConnection 'QLDB default use SQL SERVER
Private cnORACLE As OracleClient.OracleConnection 'ORACLEDB default use ORACLE
Private cnDB2 As SqlConnection 'DB2DB default use DB2
Private cnTEST As SqlConnection 'TESTDB default use TEST
#End Region
#Region " 构造函数 "
Sub New()
End Sub
'四种.NET数据提供程序
' 1) SQL Server .NET Framework 数据提供程序;
' 连接字符串必要信息:
' 方案一:Data Source/Server/Address/Addr/Network Address + Initial Catalog/Database +
Integerated Security/Trusted_Connection=SSPI/True/yes
' 方案二:Data Source/Server/Address/Addr/Network Address + Initial Catalog/Database +
Integerated Security/Trusted_Connection=false(默认值)/no + User ID + Password/Pwd
' 例子: 1) "Server=(local);Database=CustomerManagement;Integrated Security=SSPI" 本
机SQL Server
' 2) "Server=xj;database=CustomerManagement;Integrated Security=SSPI" 远程
SQL Server
' 3) "Server=xj;database=CustomerManagement;Integrated Security=fasle;User
Id=book;Password=bookpwd" 远程SQL Server
' 2) Oracle .NET Framework 数据提供程序;
' 连接字符串必要信息:与SQL Server类似
' 例子: 1) "Data source=xj;Integrated Security=yes"
' 2) "Data source=xj;Integrated Security=false;User
ID=myUserID;Password=123456"
' 3) OLE DB .NET Framework 数据提供程序;
' 连接字符串信息:Provider关键字/值对 + 其余部分
' 例子: 1) SQL Server "Provider=SQLOLEDB;Data
source=xj;Database=CustomerManagement;Integrated Security=SSPI"
' 2) Oracle "Provider=MSDAORA;Data source=xj;Persist Security
Info=false;Integrated Security=yes"
' 3) Access "Provider=Microsoft.jet.oledb.4.0;Data
source=c:/bin/localaccess40.mdb"
' 4) ODBC .NET Framework 数据提供程序。
' 方案一:连接字符串(语法要与基础驱动程序所支持的内容完全匹配)与OLE DB类似
' 例子: 1) SQL Server "Driver={SQL Server};server=xj;Integrated
Security=SSPI;Database=CustomerManagement"
' 2) Oracle "Driverr={Microsoft ODBC for Oracle};server=xj;Persist
Security Info=false;Integrated Security=yes"
' 3) Access "Driver={Microsoft Access Driver
(*.mdb);DBQ=c:/bin/localaccess40.mdb"
' 4) Excel "Driver={Microsoft Excel Driver
(*.xls);DBQ=c:/bin/book1.xls"
' 方案二:DSN
Sub New(ByVal userID As String, ByVal userPassword As String, ByVal db As DBKind)
dbServer = db
Select Case dbServer
Case DBKind.SQL 'connect to SQLDB-- SQL SERVER
'1) SQL Server .NET Framework 数据提供程序 方案二
Dim cnString As String
Dim sqlDA As SqlDataAdapter
cnString = "data source=tet;initial catalog=hisdb;Integrated Security=false;user id=" &
userID & ";password=" & userPassword & ""
_databaseKind = "SQL Server"
cnSQL = New SqlConnection(cnString)
sqlDA = New SqlDataAdapter
Case DBKind.ORACLE ' connect to ORACLEDB-- ORACLE
' 相应的处理程序
Case DBKind.DB2 'connect to DB2DB-- DB2
' 相应的处理程序
Case DBKind.ACCESS
'3) OLE DB .NET Framework 数据提供程序;
Dim cnString As String
cnString = "DataSource = 'C:/Documents and Settings/xue/桌面
/hisdb.mdb';Provider='Microsoft.Jet.OLEDB.4.0';persist security info=false;user id=" & userID & ";password="
& userPassword & ""
Dim cnAccess As OleDb.OleDbConnection = New OleDb.OleDbConnection(cnString)
Case DBKind.TEST
'1) SQL Server .NET Framework 数据提供程序 方案二
Dim cnString As String
Dim sqlDA As SqlDataAdapter
cnString = "data source=tet;initial catalog=hisdb;Integrated Security=SSPI"
_databaseKind = "SQL Server"
cnSQL = New SqlConnection(cnString)
End Select
End Sub
#End Region
#Region " 属性 "
'错误信息
Public ReadOnly Property errMsg() As String
Get
errMsg = _errorMessage
End Get
End Property
'获取数据库类型
Public ReadOnly Property getDataBaseKind() As String
Get
getDataBaseKind = _databaseKind
End Get
End Property
'获取数据库状态
Public ReadOnly Property getDbState() As String
Get
getDbState = _databaseState
End Get
End Property
#End Region
#Region " 方法 "
'打开数据库连接
Private Sub openDatabase()
If Not cnSQL.State = ConnectionState.Open Then
cnSQL.Open()
_databaseState = "Open"
End If
End Sub
'关闭数据库连接
Private Sub closeDatabase()
If cnSQL.State = ConnectionState.Open Then
cnSQL.Close()
_databaseState = "Close"
End If
End Sub
'执行sql查询命令取得资料(DATATABLE格式)
Public Overridable Function getData(ByVal sqlcommand As String, Optional ByVal returnTableName As
String = "TableName") As DataTable
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim sqlDA As New SqlDataAdapter
sqlDA.SelectCommand = New SqlCommand(sqlcommand, cnSQL)
getData = New DataTable(returnTableName)
Call openDatabase()
Try
sqlDA.Fill(getData)
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
Call closeDatabase()
End Try
End Select
End Function
'执行sql查询命令取得DataTable的架构(列和约束)
Public Overridable Function getSchema(ByVal sqlcommand As String, Optional ByVal returnTableName
As String = "TableName") As DataTable
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim sqlDA As New SqlDataAdapter
sqlDA.SelectCommand = New SqlCommand(sqlcommand, cnSQL)
getSchema = New DataTable(returnTableName)
Call openDatabase()
Try
sqlDA.FillSchema(getSchema, SchemaType.Mapped)
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
Call closeDatabase()
End Try
End Select
End Function
'执行sql新增更新删除命令
Public Overridable Function ExecuteNonQuery(ByVal sqlcommand As String) As Integer
'execute SQL statement in (INSERTDELETE or UPDATE statement)
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim sqlCmd As SqlCommand = New SqlCommand(sqlcommand, cnSQL)
If useTrans = True Then
sqlCmd.Transaction = sqlTrans
Try
ExecuteNonQuery = sqlCmd.ExecuteNonQuery()
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
End Try
Else
Call openDatabase()
ExecuteNonQuery = sqlCmd.ExecuteNonQuery()
Call closeDatabase()
End If
End Select
End Function
'执行sql查询命令只回传笔数
Public Overridable Function ExecuteScalar(ByVal sqlcommand As String) As Integer
Dim sqlDA As SqlDataAdapter
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim dtTemp As New DataTable
sqlDA.SelectCommand = New SqlCommand(sqlcommand, cnSQL)
Call openDatabase()
Try
sqlDA.Fill(dtTemp)
ExecuteScalar = dtTemp.Rows.Count
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
Call closeDatabase()
End Try
End Select
End Function
'进入交易状态
Public Overridable Sub BeginTransaction()
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Try
Call openDatabase()
' Start a local transaction
sqlTrans = cnSQL.BeginTransaction()
useTrans = True
Catch ex As Exception
Call closeDatabase()
_errorMessage = ex.Message.ToString
Throw New Exception("")
End Try
End Select
End Sub
'交易成功,确认资料变更
Public Overridable Sub Commit()
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Try
' Commit a local transaction
sqlTrans.Commit()
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
useTrans = False
Call closeDatabase()
End Try
End Select
End Sub
'交易失败,取消资料变更
Public Overridable Sub Rollback()
Select Case dbServer
Case DBKind.SQL
' 相应的处理程序
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Try
' Rollback a local transaction
sqlTrans.Rollback()
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
useTrans = False
Call closeDatabase()
End Try
End Select
End Sub
'检查使用者帐号
Public Overridable Function checkID(ByVal par As String) As Boolean
Dim strSQL As String
Select Case dbServer
Case DBKind.SQL
Dim ArrTmp() = par.Split(Microsoft.VisualBasic.ChrW(2))
strSQL = "select LG_LOGIN,LG_PASSWORD,LG_GROUP from LOGIN where " &
"LG_LOGIN= '" & ArrTmp(0) & "' and LG_PASSWORD='" & ArrTmp(1) & "'"
If ExecuteScalar(strSQL) > 0 Then
Return True
Else
Return False
End If
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim ArrTmp() = par.Split(Microsoft.VisualBasic.ChrW(2))
If arrtmp(0) = "test" And arrtmp(1) = "test" Then
Return True
Else
Return False
End If
End Select
End Function
'取得DB时间
Public Overridable Function DBTime() As String
Select Case dbServer
Case DBKind.SQL
DBTime = Now
Case DBKind.ORACLE
' 相应的处理程序
Case DBKind.DB2
' 相应的处理程序
Case DBKind.ACCESS
' 相应的处理程序
Case DBKind.TEST
Dim strSQL As String = "select getdate() from login"
Dim tbTmp As New DataTable
Dim sqlDA As New SqlDataAdapter
sqlDA.SelectCommand = New SqlCommand(strSQL, cnSQL)
Call openDatabase()
Try
sqlDA.Fill(tbTmp)
DBTime = tbTmp.Rows(0).Item(0).ToString
Catch ex As Exception
_errorMessage = ex.Message.ToString
Throw New Exception("")
Finally
Call closeDatabase()
End Try
End Select
End Function
''重载的存储过程
Public Function runStoreProc(ByVal strStoreProcName As String, ByVal strReturnTableName As String)
As DataTable
End Function
Public Function runStoreProc(ByVal strStoreProcName As String, ByVal strParametersName As String, ByVal strParametersValue As String, ByVal strReturnTableName As
String) As DataTable
End Function
Public Function runStoreProc(ByVal strStoreProcName As String, ByVal strParametersName As String, ByVal strParametersValue As String, ByVal strParametersName2 As String, ByVal strParametersValue2 As String, ByVal strReturnTableName As String) As DataTable
End Function
#End Region
End Class