Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
3.95/5 (23 votes)
28 Sep 2012CPOL2 min read 98.9K   88   30
A 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 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. 

VB
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: 

VB
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: 

VB
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#:

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;
    }
}

License

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


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
BugDispose? Pin
MaryOB770621-Nov-21 11:48
MaryOB770621-Nov-21 11:48 
QuestionOpened Connections?! Pin
Shahin Khorshidnia22-Aug-20 19:33
professionalShahin Khorshidnia22-Aug-20 19:33 
GeneralThoughts Pin
PIEBALDconsult13-Dec-13 7:09
mvePIEBALDconsult13-Dec-13 7:09 
QuestionWhere should be the config file? (Reporting Services) Pin
Luis Fernando Forero Guzman7-Feb-13 4:21
Luis Fernando Forero Guzman7-Feb-13 4:21 
SQL
If I put this class in a class library project (CSDataAccess) and I reference this project (dll) from a web app I understand that the web.config file would be in that application.

If I reference this project (dll) from a forms app I understand that the app.config file would be in that application

But my problem is that I’m going to use this project that has this class but expose some other methods that will be used by reporting services, so my question is in that case where the web/app config have to go? Any ideas?

AnswerRe: Where should be the config file? (Reporting Services) Pin
AdamNThompson7-Feb-13 16:41
AdamNThompson7-Feb-13 16:41 
QuestionHow do I create the interface -IConnection- Pin
Luis Fernando Forero Guzman7-Feb-13 4:03
Luis Fernando Forero Guzman7-Feb-13 4:03 
AnswerRe: How do I create the interface -IConnection- Pin
AdamNThompson7-Feb-13 16:45
AdamNThompson7-Feb-13 16:45 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:41
professionalKanasz Robert25-Sep-12 22:41 
GeneralRe: My vote of 5 Pin
AdamNThompson27-Sep-12 8:50
AdamNThompson27-Sep-12 8:50 
QuestionHow to use sqlparameter in vss 2008 [modified] Pin
winki_x28-Jan-10 4:08
winki_x28-Jan-10 4:08 
AnswerRe: How to use sqlparameter in vss 2008 Pin
AdamNThompson29-Jan-10 6:52
AdamNThompson29-Jan-10 6:52 
GeneralRe: How to use sqlparameter in vss 2008 Pin
winki_x29-Jan-10 18:58
winki_x29-Jan-10 18:58 
GeneralProblem with shared Connection. Pin
Rajkumar S7-Jan-10 6:18
Rajkumar S7-Jan-10 6:18 
GeneralRe: Problem with shared Connection. Pin
AdamNThompson10-Jan-10 8:20
AdamNThompson10-Jan-10 8:20 
GeneralRe: Problem with shared Connection. Pin
Rajkumar S11-Jan-10 3:58
Rajkumar S11-Jan-10 3:58 
GeneralRe: Problem with shared Connection. Pin
AdamNThompson20-Jan-10 1:34
AdamNThompson20-Jan-10 1:34 
GeneralRe: Problem with shared Connection. Pin
Rajkumar S20-Jan-10 12:58
Rajkumar S20-Jan-10 12:58 
GeneralRe: Problem with shared Connection. Pin
AdamNThompson21-Jan-10 4:13
AdamNThompson21-Jan-10 4:13 
GeneralRe: Problem with shared Connection. Pin
AdamNThompson21-Jan-10 4:15
AdamNThompson21-Jan-10 4:15 
Generalwhere is example and we need more Pin
el3ashe22-Oct-09 12:48
el3ashe22-Oct-09 12:48 
Generalok article Pin
Donsw11-May-09 9:02
Donsw11-May-09 9:02 
GeneralRe: ok article Pin
AdamNThompson12-May-09 3:46
AdamNThompson12-May-09 3:46 
GeneralRe: ok article Pin
Donsw12-May-09 5:19
Donsw12-May-09 5:19 
Generalrather use 3 layers Pin
Arnie100019-Sep-07 3:32
Arnie100019-Sep-07 3:32 
GeneralRe: rather use 3 layers Pin
AdamNThompson19-Sep-07 13:41
AdamNThompson19-Sep-07 13:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.