access dbHelper

Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration

Namespace DAL
''' <summary>
''' OLEDBHelper 的摘要说明
''' </summary>
Public Class OLEDBHelper
''' <summary>
''' 连接数据库字符串
''' </summary>
Public Shared strCon As String = ConfigurationManager.ConnectionStrings("DemoCon").ConnectionString
''' <summary>
''' 执行得到一行一列
''' </summary>
''' <param name="sqlText">sql语句</param>
''' <returns>object对象</returns>
Public Shared Function ExeOnlyOne(sqlText As String) As Object
Dim oRs As Object = Nothing
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.CommandText = sqlText
cmd.Connection = con
con.Open()
oRs = cmd.ExecuteScalar()
con.Close()
End Using
Return oRs
End Function
''' <summary>
''' 重载ExecuteOnlyOne
''' </summary>
''' <param name="sqlText">命令</param>
''' <param name="pars">参数【个数不定】</param>
''' <returns></returns>
Public Shared Function ExeOnlyOne(sqlText As String, ParamArray pars As OleDbParameter()) As Object
Dim oRs As Object = Nothing
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.CommandText = sqlText
cmd.Connection = con
cmd.Parameters.AddRange(pars)
con.Open()
oRs = cmd.ExecuteScalar()
con.Close()
End Using
Return oRs
End Function

''' <summary>
''' 执行Insert update delete语句
''' </summary>
''' <param name="sqlText">sql语句</param>
''' <returns>受影响行数</returns>
Public Shared Function ExeCUD(sqlText As String) As Integer
Dim iRs As Integer = 0
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.CommandText = sqlText
cmd.Connection = con
con.Open()
iRs = cmd.ExecuteNonQuery()
con.Close()
End Using
Return iRs
End Function
''' <summary>
''' 执行带参数的Insert update delete语句
''' </summary>
''' <param name="sqlText">sql语句</param>
''' <param name="pars">参数</param>
''' <returns>受影响行数</returns>
Public Shared Function ExeCUD(sqlText As String, ParamArray pars As OleDbParameter()) As Integer
Dim iRs As Integer = 0
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.CommandText = sqlText
cmd.Connection = con
cmd.Parameters.AddRange(pars)
con.Open()
iRs = cmd.ExecuteNonQuery()
con.Close()
End Using
Return iRs
End Function

''' <summary>
''' 查询多条结果
''' </summary>
''' <param name="sqlText">sql语句</param>
''' <returns>DataTable</returns>
Public Shared Function ExeAll(sqlText As String) As DataTable
Dim dtRs As DataTable = Nothing
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim sda As New OleDbDataAdapter()
sda.SelectCommand = New OleDbCommand(sqlText, con)
Dim ds As New DataSet()
sda.Fill(ds)
dtRs = ds.Tables(0)
End Using
Return dtRs
End Function
''' <summary>
''' 查询带参的多条结果
''' </summary>
''' <param name="sqlText">sql语句</param>
''' <param name="pars">参数</param>
''' <returns>DataTable</returns>
Public Shared Function ExeAll(sqlText As String, ParamArray pars As OleDbParameter()) As DataTable
Dim dtRs As DataTable = Nothing
Using con As New OleDbConnection()
con.ConnectionString = strCon
Dim sda As New OleDbDataAdapter()
sda.SelectCommand = New OleDbCommand(sqlText, con)
sda.SelectCommand.Parameters.AddRange(pars)
Dim ds As New DataSet()
sda.Fill(ds)
dtRs = ds.Tables(0)
End Using
Return dtRs
End Function

''' <summary>
''' 游标读取数据SqlDataReader
''' </summary>
''' <param name="sqlText">语句</param>
''' <returns>OleDbDataReader</returns>
Public Shared Function ExeReader(sqlText As String) As OleDbDataReader
Dim sdrRs As OleDbDataReader = Nothing
Dim con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.Connection = con
cmd.CommandText = sqlText
con.Open()
sdrRs = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return sdrRs
End Function
''' <summary>
''' 带参reader的sql语句执行
''' </summary>
''' <param name="sqlText">语句</param>
''' <param name="pars">参数</param>
''' <returns>OleDbDataReader</returns>
Public Shared Function ExeReader(sqlText As String, ParamArray pars As OleDbParameter()) As OleDbDataReader
Dim sdrRs As OleDbDataReader = Nothing
Dim con As New OleDbConnection()
con.ConnectionString = strCon
Dim cmd As New OleDbCommand()
cmd.Connection = con
cmd.CommandText = sqlText
cmd.Parameters.AddRange(pars)
con.Open()
sdrRs = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return sdrRs
End Function
End Class
End Namespace