Data Access Component for SQL Server in VB.NET

Introduction

This is a generic data access component I wrote to simplify some of the common tasks used to work with data using ADO.NET and MS SQL Server. The class contains methods to perform non-queries (Update, Insert, and Delete), 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 or a 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 readablity of VBDataAccess(Of ISomeConnection). I am open to discussion and suggestions though.

Also, please note: while these are Shared/static methods, the Connection property returns a new SqlConnection every time it is called. As a wise man once said: "Sharing SqlCOnnections is not a good idea".

Have fun...

Using the Code

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

Dim strSQL As String = "SELECT * FROM tblContacts"

GridView2.DataSource = VBDataAccess(Of IMyConnection).GetDataset(strSQL)
GridView2.DataBind()

Here is an example 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... I have also added a C# version 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("RemingtonCommon"))
          ElseIf GenParamType Is GetType(ICampusViewConn) Then
            Return New SqlConnection(ConfigurationSettings.AppSettings("CampusView"))
          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;
    }
}

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)