一个简单的数据操作类
今天刚写的一个简单的数据操作类,供大家参考。
Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace DataAccess
Public Class DataAccess
Public Sub New()
Dim connectString As String = System.Configuration.ConfigurationSettings.AppSettings("strSqlconn")
Me.conn = New SqlConnection(connectString)
'构造函数
End Sub
Public Sub New(ByVal connectString As String)
Me.conn = New SqlConnection(connectString)
'构造函数
End Sub
Private conn As SqlConnection
Public Property Connection() As SqlConnection
Get
Return conn
End Get
Set(ByVal Value As SqlConnection)
conn = Value
End Set
End Property
Public Sub OpenConnection()
'打开连接
If Not (Me.conn Is Nothing) AndAlso Me.conn.State = ConnectionState.Closed Then
Me.conn.Open()
Else
If Me.conn Is Nothing Then
Throw New Exception("Connection 还没有初始化")
End If
End If
End Sub
Public Sub CloseConnection()
'关闭连接
If Not (Me.conn Is Nothing) AndAlso Me.conn.State = ConnectionState.Open Then
Me.conn.Close()
Else
If Me.conn Is Nothing Then
Throw New Exception("Connection 还没有初始化")
End If
End If
End Sub
Public Function ExecuteReader(ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As SqlDataReader
'返回数据阅读器,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
Return command.ExecuteReader
End Function
Public Function ExecuteReader(ByVal sql As String) As SqlDataReader
'返回数据阅读器,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.conn
Me.OpenConnection()
Return command.ExecuteReader
End Function
Public Function ExecuteNonQuery(ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As Integer
'返回受影响的行数,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
Me.OpenConnection()
Try
Return command.ExecuteNonQuery()
Finally
Me.CloseConnection()
End Try
End Function
Public Function ExecuteNonQuery(ByVal sql As String) As Integer
'返回受影响的行数,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.conn
Me.OpenConnection()
Try
Return command.ExecuteNonQuery
Finally
Me.CloseConnection()
End Try
End Function
Public Function FillDataSet(ByVal dataset As DataSet, ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As Integer
'返回数据集,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
command.Connection = Me.conn
Try
Me.OpenConnection()
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Return adapter.Fill(dataset)
Finally
Me.CloseConnection()
End Try
End Function
Public Function FillDataSet(ByVal dataset As DataSet, ByVal sql As String) As Integer
'返回数据集,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.Connection
Try
Me.OpenConnection()
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Return adapter.Fill(dataset)
Finally
Me.CloseConnection()
End Try
End Function
End Class
End Namespace
Imports System.Data
Imports System.Data.SqlClient
Namespace DataAccess
Public Class DataAccess
Public Sub New()
Dim connectString As String = System.Configuration.ConfigurationSettings.AppSettings("strSqlconn")
Me.conn = New SqlConnection(connectString)
'构造函数
End Sub
Public Sub New(ByVal connectString As String)
Me.conn = New SqlConnection(connectString)
'构造函数
End Sub
Private conn As SqlConnection
Public Property Connection() As SqlConnection
Get
Return conn
End Get
Set(ByVal Value As SqlConnection)
conn = Value
End Set
End Property
Public Sub OpenConnection()
'打开连接
If Not (Me.conn Is Nothing) AndAlso Me.conn.State = ConnectionState.Closed Then
Me.conn.Open()
Else
If Me.conn Is Nothing Then
Throw New Exception("Connection 还没有初始化")
End If
End If
End Sub
Public Sub CloseConnection()
'关闭连接
If Not (Me.conn Is Nothing) AndAlso Me.conn.State = ConnectionState.Open Then
Me.conn.Close()
Else
If Me.conn Is Nothing Then
Throw New Exception("Connection 还没有初始化")
End If
End If
End Sub
Public Function ExecuteReader(ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As SqlDataReader
'返回数据阅读器,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
Return command.ExecuteReader
End Function
Public Function ExecuteReader(ByVal sql As String) As SqlDataReader
'返回数据阅读器,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.conn
Me.OpenConnection()
Return command.ExecuteReader
End Function
Public Function ExecuteNonQuery(ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As Integer
'返回受影响的行数,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
Me.OpenConnection()
Try
Return command.ExecuteNonQuery()
Finally
Me.CloseConnection()
End Try
End Function
Public Function ExecuteNonQuery(ByVal sql As String) As Integer
'返回受影响的行数,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.conn
Me.OpenConnection()
Try
Return command.ExecuteNonQuery
Finally
Me.CloseConnection()
End Try
End Function
Public Function FillDataSet(ByVal dataset As DataSet, ByVal NameOfProc As String, ByVal parameters As SqlParameter()) As Integer
'返回数据集,用于执行存储过程
Dim command As SqlCommand = New SqlCommand(NameOfProc)
command.Connection = Me.conn
command.CommandType = CommandType.StoredProcedure
Dim mySqlParameter As SqlParameter
For Each mySqlParameter In parameters
command.Parameters.Add(mySqlParameter)
Next
command.Connection = Me.conn
Try
Me.OpenConnection()
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Return adapter.Fill(dataset)
Finally
Me.CloseConnection()
End Try
End Function
Public Function FillDataSet(ByVal dataset As DataSet, ByVal sql As String) As Integer
'返回数据集,用于执行数据命令
Dim command As SqlCommand = New SqlCommand(sql)
command.Connection = Me.Connection
Try
Me.OpenConnection()
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
Return adapter.Fill(dataset)
Finally
Me.CloseConnection()
End Try
End Function
End Class
End Namespace