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 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|