一个简单的数据操作类

一个简单的数据操作类 今天刚写的一个简单的数据操作类,供大家参考。 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