ADO.NET Data Access Component for SQL Server in C# and VB.NET

Introduction 

This is a generic data access component I wrote to simplify some of the common tasks when working directly with ADO.NET. The class contains methods to perform non-queries (Update, Insert, Delete, and Stored Procedures), and it contains methods to return a SqlDataReader or DataSet; I have also added a GetScaler method. I am overriding the methods as well to allow the use of SQL strings or Stored Procedure. This class is extremely easy to use and modify. 

I have also made the class generic to support multiple connections. I'm sure there are lots of ways to handle multiple connections, but I like the readability of VBDataAccess(Of ISomeConnection). I am probably going to change how this works in favor of a context object the next time I use it. 

Important, please make note: while these are Shared/static methods, the Connection property returns a new SqlConnection every time it is called. Sharing SqlCOnnections is not a good idea so don't do it. If you pull this code and use it be mindful of how the connection is being created using the new keyword every time it's called. This is mostly considered common knowledge, or at least it was. now EF, and other ORMs are abstracting all the boiler plate code we used to write in the .NET 2.0 times. This is great but if your new on the scene, be sure to keep this in mind when working directly with ADO.NET.  

Enjoy... 

Using the Code 

Here is a C# example of using the class to return a DataSet. Getting a SqlDataReader works the same, you just use the GetSqlDataReader method instead. 

var strSQL = "SELECT * FROM tblContacts";

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

Here is an example in VB.NET of using the class to Insert a new record: 

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)

Here is the class itself in VB.NET... The C# version is at the bottom: 

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

Here it is in 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;
    }
}