Imports System.Data.OleDb
Imports System
Imports co

''' <summary>

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 "取得数据库连接串"

''' <summary>
''' 取得数据库连接串
''' </summary>
''' <returns>取得数据库连接串</returns>
''' <remarks></remarks>
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 "连接数据库"

''' <summary>
''' 连接数据库
''' </summary>
''' <remarks></remarks>
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 "开始事务"

''' <summary>
''' 开始事务
''' </summary>
''' <remarks></remarks>
Public Sub beginTran()
' 如果已经连接到数据库，则开始事务
If connected = True Then
' 开始事务
tran = conn.BeginTransaction()
command.Transaction = tran
End If
End Sub

#End Region

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

''' <summary>
''' 执行数据库查询，返回查询结果数
''' </summary>
''' <param name="strsqlCmd">要执行的sql命令</param>
''' <remarks></remarks>
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） "

''' <summary>
''' 执行数据库查询
''' </summary>
''' <param name="strStoredProcedure">要执行的存储过程</param>
''' <param name="arrlstParams">传入的参数值，如果传入的参数为Nothing则代表没有参数</param>
''' <returns>操作结果数</returns>
''' <remarks>当需要传入输出参数时，请把参数的值设置为OUTPUT</remarks>
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）"

''' <summary>
''' 执行数据库查询，返回一个OleDbDataReader对象
''' </summary>
''' <param name="StrCmd">要执行的sql命令</param>
''' <remarks></remarks>
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） "

''' <summary>
''' 执行数据库查询，返回单一结果集
''' </summary>
''' <param name="strCmd">要执行的sql命令</param>
''' <remarks></remarks>
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