用于 C# 和 VB.NET 中 SQL Server 的 ADO.NET 数据访问组件

介绍 

这是一个我编写的通用数据访问组件，用于简化直接使用 ADO.NET 时的一些常见任务。该类包含执行非查询（更新、插入、删除和存储过程）的方法，并且包含返回 SqlDataReader 或 DataSet 的方法；我还添加了一个 GetScaler 方法。我还在重写这些方法，以允许使用 SQL 字符串或存储过程。这个类非常容易使用和修改。

我还使该类成为通用的，以支持多个连接。我确信有很多方法可以处理多个连接，但我喜欢 VBDataAccess(Of ISomeConnection) 的可读性。下次使用它时，我可能会更改它的工作方式，转而使用上下文对象。

重要提示：虽然这些是 Shared/static 方法，但 Connection 属性每次调用时都会返回一个新的 SqlConnection。共享 SqlCOnnection 不是一个好主意，所以不要这样做。如果您提取此代码并使用它，请注意每次调用时如何使用 new 关键字创建连接。这主要被认为是常识，或者至少曾经是。现在 EF 和其他 ORM 正在抽象我们在 .NET 2.0 时代编写的所有样板代码。这很棒，但如果您是新手，请务必在直接使用 ADO.NET 时记住这一点。

享受...

使用代码 

这是一个使用该类返回 DataSet 的 C# 示例。 获取 SqlDataReader 的工作方式相同，您只需使用 GetSqlDataReader 方法即可。

var strSQL = "SELECT * FROM tblContacts";

GridView2.DataSource = CSDataAccess<IMyConnection>.GetDataset(strSQL);
GridView2.DataBind();  

这是一个使用该类在 VB.NET 中插入新记录的示例：

Dim strSQL As String = _
  "INSERT INTO tblContacts (FirstName, LastName, PhoneNumber, Email) " & _
  "VALUES (@FirstName, @lastName, @PhoneNumber, @Email)"

Dim Parameters(0 To 3) As SqlParameter
Parameters(0) = New SqlParameter("FirstName", "Testy")
Parameters(1) = New SqlParameter("LastName", "McTester")
Parameters(2) = New SqlParameter("PhoneNumber", "565-552-6565")
Parameters(3) = New SqlParameter("Email", "test@test.com")
VBDataAccess(Of IMyConnection).NonQuery(strSQL, CommandType.Text, Parameters)

这是 VB.NET 中的类本身... C# 版本在底部：

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Configuration

Public Class VBDataAccess(Of TConnection As IConnection)
    
    Shared SqlComm As SqlCommand = Nothing
    Shared SqlDA As SqlDataAdapter = Nothing
    
    Private Shared ReadOnly Property Connection() As SqlConnection
        Get
          Dim GenParamType As Type = GetType(TConnection)
            
          If GenParamType Is GetType(IRemingtonCommonConn) Then
            Return New SqlConnection(ConfigurationSettings.AppSettings("AccountingConnection"))
          ElseIf GenParamType Is GetType(ICampusViewConn) Then
            Return New SqlConnection(ConfigurationSettings.AppSettings("GreatPlainsConnection"))
          Else
            Throw New NotSupportedException("TConnection must be typeof(IConnection)")
          End If
        End Get
    End Property
    
    ' Creats a collection of parameters. 
    Public Shared ReadOnly Property Parameters() As SqlParameterCollection
        Get
            Return SqlComm.Parameters
        End Get
    End Property
    
    ' Execute an insert, update, or delete. 
    Public Shared Function GetScalar(ByVal CommandText As String) As String
        
        Return GetScalar(CommandText, Nothing, CommandType.Text)
    End Function

    ''' <summary> 
    ''' 
    ''' </summary> 
    ''' <param name="CommandText"></param> 
    ''' <param name="ParameterValues"></param> 
    ''' <returns></returns> 
    ''' <remarks></remarks> 
    Public Shared Function GetScalar(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter)) As String
        
        Return GetScalar(CommandText, ParameterValues, CommandType.Text)
    End Function
    
    Public Shared Function GetScalar(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter), _
           ByVal CommandType As CommandType) As String
        
        Dim res As String = Nothing
        Dim SqlConn As SqlConnection = Connection
        SqlConn.Open()
        
        Try
            SqlComm = New SqlCommand(CommandText, SqlConn)
            SqlComm.CommandTimeout = 600
            SqlComm.CommandType = CommandType
            If (ParameterValues IsNot Nothing) Then
                For Each Parameter In ParameterValues
                    SqlComm.Parameters.Add(Parameter)
                Next
            End If
            res = SqlComm.ExecuteScalar().ToString()
        Catch ex As Exception
            Return Nothing
        End Try
        Return res
    End Function
    
    ' Execute an insert, update, or delete. 
    Public Shared Function NonQuery(ByVal CommandText As String) As Integer
        
        Return NonQuery(CommandText, Nothing, CommandType.Text)
    End Function
    
    ''' <summary> 
    ''' 
    ''' </summary> 
    ''' <param name="CommandText"></param> 
    ''' <param name="ParameterValues"></param> 
    ''' <returns></returns> 
    ''' <remarks></remarks> 
    Public Shared Function NonQuery(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter)) As Integer
        
        Return NonQuery(CommandText, ParameterValues, CommandType.Text)
    End Function
    
    Public Shared Function NonQuery(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter), _
           ByVal CommandType As CommandType) As Integer
        
        Dim res As Integer = 0
        Dim SqlConn As SqlConnection = Connection
        SqlConn.Open()
        
        Try
            SqlComm = New SqlCommand(CommandText, SqlConn)
            SqlComm.CommandTimeout = 600
            SqlComm.CommandType = CommandType
            If (ParameterValues IsNot Nothing) Then
                For Each Parameter In ParameterValues
                    SqlComm.Parameters.Add(Parameter)
                Next
            End If
            res = SqlComm.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        End Try
        Return res
    End Function
    
    
    ' Return a SqlDataReader 
    Public Shared Function GetSqlDataReader(ByVal _
           CommandText As String) As SqlDataReader
        
        Return GetSqlDataReader(CommandText, Nothing, CommandType.Text)
    End Function
    
    Public Shared Function GetSqlDataReader(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter)) As SqlDataReader
        
        Return GetSqlDataReader(CommandText, ParameterValues, CommandType.Text)
    End Function
    
    Public Shared Function GetSqlDataReader(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter), _
           ByVal CommandType As CommandType) As SqlDataReader
        
        Dim res As SqlDataReader = Nothing
        Dim SqlConn As SqlConnection = Connection
        SqlConn.Open()
        
        Try
            SqlComm = New SqlCommand(CommandText, SqlConn)
            SqlComm.CommandTimeout = 600
            SqlComm.CommandType = CommandType
            If (ParameterValues IsNot Nothing) Then
                For Each Parameter In ParameterValues
                    SqlComm.Parameters.Add(Parameter)
                Next
            End If
            res = SqlComm.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            SqlConn.Close()
            Throw ex
        End Try
        Return res
    End Function
    
    
    ' Return a DataSet 
    Public Shared Function GetDataset(ByVal CommandText As String) As DataSet
        
        Return GetDataset(CommandText, Nothing, CommandType.Text)
    End Function
    
    Public Shared Function GetDataset(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter)) As DataSet
        
        Return GetDataset(CommandText, ParameterValues, CommandType.Text)
    End Function
    
    Public Shared Function GetDataset(ByVal CommandText As String, _
           ByVal ParameterValues As List(Of SqlParameter), _
           ByVal CommandType As CommandType) As DataSet
        
        Dim res As New DataSet()
        Dim SqlConn As SqlConnection = Connection
        SqlConn.Open()
        
        Try
            SqlComm = New SqlCommand(CommandText, SqlConn)
            SqlComm.CommandTimeout = 600
            SqlComm.CommandType = CommandType
            If (ParameterValues IsNot Nothing) Then
                For Each Parameter In ParameterValues
                    SqlComm.Parameters.Add(Parameter)
                Next
            End If
            SqlDA = New SqlDataAdapter(SqlComm)
            SqlDA.Fill(res)
        Catch ex As Exception
            Throw ex
        End Try
        Return res
    End Function
    
End Class

这是 C# 版本

public class CSDataAccess<TConnection> where TConnection : IConnection
{

    static SqlCommand SqlComm = null;
    static SqlDataAdapter SqlDA = null;

    private static SqlConnection Connection
    {
        get 
        {
            Type GenParamType = typeof(TConnection);

            if (GenParamType == typeof(IRemingtonCommonConn))
            {
                return new SqlConnection(
                  ConfigurationSettings.AppSettings["RemingtonCommon"]); 
            }
            else if (GenParamType == typeof(ICampusViewConn))
            {
                return new SqlConnection(
                  ConfigurationSettings.AppSettings["CampusView"]);
            }
            else 
            {
                throw new NotSupportedException(
                      "TConnection must be typeof(IConnection)");
            }
            
        }
    }

    // Creats a collection of parameters. 
    public static SqlParameterCollection Parameters
    {
        get { return SqlComm.Parameters; }
    }



    // Execute an insert, update, or delete. 
    public static string GetScalar(string CommandText)
    {

        return GetScalar(CommandText, null, CommandType.Text);
    }

    /// <summary> 
    /// 
    /// </summary> 
    /// <param name="CommandText"></param> 
    /// <param name="ParameterValues"></param> 
    /// <returns></returns> 
    /// <remarks></remarks> 
    public static string GetScalar(string CommandText, 
           List<SqlParameter> ParameterValues)
    {

        return GetScalar(CommandText, ParameterValues, CommandType.Text);
    }

    public static string GetScalar(string CommandText, 
           List<SqlParameter> ParameterValues, CommandType CommandType)
    {

        string res = null;
        SqlConnection SqlConn = Connection;
        SqlConn.Open();

        try
        {
            SqlComm = new SqlCommand(CommandText, SqlConn);
            SqlComm.CommandTimeout = 600;
            SqlComm.CommandType = CommandType;
            if ((ParameterValues != null))
            {
                foreach (var Parameter in ParameterValues)
                {
                    SqlComm.Parameters.Add(Parameter);
                }
            }
            res = SqlComm.ExecuteScalar().ToString();
        }
        catch (Exception ex)
        {
            return null;
        }
        return res;
    }

    // Execute an insert, update, or delete. 
    public static int NonQuery(string CommandText)
    {

        return NonQuery(CommandText, null, CommandType.Text);
    }

    /// <summary> 
    /// 
    /// </summary> 
    /// <param name="CommandText"></param> 
    /// <param name="ParameterValues"></param> 
    /// <returns></returns> 
    /// <remarks></remarks> 
    public static int NonQuery(string CommandText, 
           List<SqlParameter> ParameterValues)
    {

        return NonQuery(CommandText, ParameterValues, CommandType.Text);
    }

    public static int NonQuery(string CommandText, 
           List<SqlParameter> ParameterValues, CommandType CommandType)
    {

        int res = 0;
        SqlConnection SqlConn = Connection;
        SqlConn.Open();

        try
        {
            SqlComm = new SqlCommand(CommandText, SqlConn);
            SqlComm.CommandTimeout = 600;
            SqlComm.CommandType = CommandType;
            if ((ParameterValues != null))
            {
                foreach (var Parameter in ParameterValues)
                {
                    SqlComm.Parameters.Add(Parameter);
                }
            }
            res = SqlComm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return res;
    }

    // Return a SqlDataReader 
    public static SqlDataReader GetSqlDataReader(string CommandText)
    {

        return GetSqlDataReader(CommandText, null, CommandType.Text);
    }

    public static SqlDataReader GetSqlDataReader(string CommandText, 
                  List<SqlParameter> ParameterValues)
    {

        return GetSqlDataReader(CommandText, ParameterValues, 
                                CommandType.Text);
    }

    public static SqlDataReader GetSqlDataReader(string CommandText, 
           List<SqlParameter> ParameterValues, CommandType CommandType)
    {

        SqlDataReader res = null;
        SqlConnection SqlConn = Connection;
        SqlConn.Open();

        try
        {
            SqlComm = new SqlCommand(CommandText, SqlConn);
            SqlComm.CommandTimeout = 600;
            SqlComm.CommandType = CommandType;
            if ((ParameterValues != null))
            {
                foreach (var Parameter in ParameterValues)
                {
                    SqlComm.Parameters.Add(Parameter);
                }
            }
            res = SqlComm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            SqlConn.Close();
            throw ex;
        }
        return res;
    }

    // Return a DataSet 
    public static DataSet GetDataset(string CommandText)
    {

        return GetDataset(CommandText, null, CommandType.Text);
    }

    public static DataSet GetDataset(string CommandText, 
           List<SqlParameter> ParameterValues)
    {

        return GetDataset(CommandText, ParameterValues, CommandType.Text);
    }

    public static DataSet GetDataset(string CommandText, 
           List<SqlParameter> ParameterValues, CommandType CommandType)
    {

        DataSet res = new DataSet();
        SqlConnection SqlConn = Connection;
        SqlConn.Open();

        try
        {
            SqlComm = new SqlCommand(CommandText, SqlConn);
            SqlComm.CommandTimeout = 600;
            SqlComm.CommandType = CommandType;
            if ((ParameterValues != null))
            {
                foreach (var Parameter in ParameterValues)
                {
                    SqlComm.Parameters.Add(Parameter);
                }
            }
            SqlDA = new SqlDataAdapter(SqlComm);
            SqlDA.Fill(res);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return res;
    }
}