VB同时连接Access和Oracle,Vb.net连接oracle，selserver，access的共通类

Imports System.Data.OleDb

Imports System

Imports co

'''

Public Class DataAccess

#Region "变量/常量"

Private conn As OleDbConnection ' OLEDB连接对象

Private connected As Boolean = False ' 连接标识

Private tran As OleDbTransaction ' 事务对象

Private connectionString As String ' 连接字符串

Private prv_strServerName As String ' 服务器名

Private prv_strUserName As String ' 用户名

Private prv_strPassword As String ' 密码

Private prv_strDbName As String ' 数据库名

Private command As OleDbCommand ' OLEDB Command

Private reader As OleDbDataReader ' OLEDB DataReader

Private WithEvents sqlDtAdp As OleDbDataAdapter ' sqlDataAdapter(一览画面填充用)

Private WithEvents BindDtAdp As OleDbDataAdapter ' sqlDataAdapter(ddlFill用)

Private StrSelCmd As System.Text.StringBuilder ' SELECT 命令

Private sqlSelCmd As OleDbCommand ' sql命令(Select)

Private SpSelCmd As OleDbCommand ' 存储过程命令(Select)

Public Shared StartupPath As String '系统运行的路径

#End Region

#Region "构造方法"

#Region "构造方法(默认)"

Public Sub New()

'从配置文件读取连接字符串

connectionString = GetConnectionString()

' 事务初始化

tran = CType(Nothing,OleDbTransaction)

End Sub

#End Region

#Region "构造方法(指定连接字符串)"

Public Sub New(ByVal connString As String)

'设定连接字符串

connectionString = connString

' 事务初始化

tran = CType(Nothing,OleDbTransaction)

End Sub

#End Region

#Region "构造方法(指定事务)"

Public Sub New(ByVal _tran As OleDbTransaction)

'从配置文件读取连接字符串

connectionString = System.Configuration.ConfigurationManager.AppSettings("conStringOLEsqlServer")

' 设定事务

tran = _tran

End Sub

#End Region

#End Region

#Region "取得数据库连接串"

'''

''' 取得数据库连接串

'''

''' 取得数据库连接串

'''

Private Function GetConnectionString() As String

GetConnectionString = String.Empty

Try

'从配置文件读取数据库服务器名

Me.prv_strServerName = SystemProperty.GetPropertyByKey("ServerName")

'从配置文件读取数据库连接用户名

Me.prv_strUserName = SystemProperty.GetPropertyByKey("UserName")

'从配置文件读取数据库连接用户密码

Me.prv_strPassword = SystemProperty.GetPropertyByKey("Password")

'从配置文件读取数据库名

Me.prv_strDbName = StartupPath + SystemProperty.GetPropertyByKey("DBName")

GetConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + prv_strDbName + ";Jet OLEDB:Database Password=" + prv_strPassword + ";"

Catch ex As Exception

Throw ex

End Try

End Function

#End Region

#Region "连接数据库"

'''

''' 连接数据库

'''

'''

Public Sub connect()

Try

' 判断是否已经连接到数据库

If connected = False Then

' 实例化连接对象

conn = New OleDbConnection(connectionString)

' 打开连接

conn.Open()

' 设为已经连接到数据库

connected = True

' 初始化命令

command = New OleDbCommand

' 设定命令将要使用的连接

command.Connection = conn

' 初始化DataReader对象

reader = CType(Nothing,OleDbDataReader)

End If

Catch oledEx As System.Data.OleDb.OleDbException

Throw oledEx

Catch ex As Exception

Throw ex

End Try

End Sub

#End Region

#Region "开始事务"

'''

''' 开始事务

'''

'''

Public Sub beginTran()

' 如果已经连接到数据库，则开始事务

If connected = True Then

' 开始事务

tran = conn.BeginTransaction()

command.Transaction = tran

End If

End Sub

#End Region

#Region " 执行sql命令(新建、更新、删除：ExecuteNonQuery) "

'''

''' 执行数据库查询，返回查询结果数

'''

''' 要执行的sql命令

'''

Public Function ExecuteNonQuery(ByVal strsqlCmd As String) As Integer

Try

command.Connection = conn

command.CommandText = strsqlCmd

ExecuteNonQuery = command.ExecuteNonQuery()

Catch e As System.InvalidOperationException

'抛出异常

Throw e

Catch oldex As System.Data.OleDb.OleDbException

Throw oldex

Catch ex As Exception

Throw ex

End Try

End Function

#End Region

#Region " 执行存储过程命令(新建、更新、删除：ExecuteNonQuery) "

'''

''' 执行数据库查询

'''

''' 要执行的存储过程

''' 传入的参数值，如果传入的参数为Nothing则代表没有参数

''' 操作结果数

''' 当需要传入输出参数时，请把参数的值设置为OUTPUT

Public Function ExecuteSpNonQuery(ByVal strStoredProcedure As String,ByVal arrlstParams As ArrayList) As Integer

Try

Me.command.Connection = conn

'设定要执行的存储过程名称

Me.command.CommandText = strStoredProcedure

'设定为执行存储过程

Me.command.CommandType = CommandType.StoredProcedure

'当参数不为空时,进行遍历所有的参数,并添加到执行的命令中

If arrlstParams.Count > 0 Then

For icount As Integer = 0 To arrlstParams.Count - 1

'当为输出型参数时,进行输出参数的相关设置

If (CType(arrlstParams.Item(icount),OleDbParameter).Value.ToString.ToLower() = "output") Then

command.Parameters.Add(CType(arrlstParams.Item(icount),OleDbParameter))

command.Parameters(CType(arrlstParams.Item(icount),OleDbParameter).ParameterName).Direction = ParameterDirection.Output

Else

command.Parameters.Add(CType(arrlstParams.Item(icount),OleDbParameter))

End If

Next

End If

'执行存储过程并返回执行结果数

ExecuteSpNonQuery = command.ExecuteNonQuery()

Catch e As System.InvalidOperationException

'抛出异常

Throw e

Catch oldex As System.Data.OleDb.OleDbException

Throw oldex

Catch ex As Exception

Throw ex

End Try

End Function

#End Region

#Region " 取得结果集 (executeReader)"

'''

''' 执行数据库查询，返回一个OleDbDataReader对象

'''

''' 要执行的sql命令

'''

Public Function executeReader(ByVal strCmd As String) As OleDbDataReader

Try

'设定命令的各项属性

command.Connection = conn

command.CommandText = strCmd

reader = command.ExecuteReader()

Return reader

Catch e As Exception

Throw e

End Try

End Function

#End Region

#Region " 取得单一执行结果(executeScalar) "

'''

''' 执行数据库查询，返回单一结果集

'''

''' 要执行的sql命令

'''

Public Function executeScalar(ByVal strCmd As String) As Object

Dim objRes As Object

Dim sqlCmd As OleDbCommand

Try

sqlCmd = New OleDbCommand

sqlCmd.Connection = conn

sqlCmd.CommandText = strCmd

'如果事务已经开始，则设定成默认的事务

If Not (tran Is Nothing) Then

sqlCmd.Transaction = tran

End If

objRes = sqlCmd.ExecuteScalar()

Catch e As Exception

'抛出异常

Throw e

Finally

End Try Return

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。