Click here to Skip to main content
Click here to Skip to main content

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

, 28 Sep 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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. 

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

License

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

Share

About the Author

AdamNThompson
Web Developer
United States United States
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralThoughts PinprofessionalPIEBALDconsult13-Dec-13 7:09 
QuestionWhere should be the config file? (Reporting Services) PinmemberLuis Fernando Forero Guzman7-Feb-13 4:21 
AnswerRe: Where should be the config file? (Reporting Services) PinmemberAdamNThompson7-Feb-13 16:41 
QuestionHow do I create the interface -IConnection- PinmemberLuis Fernando Forero Guzman7-Feb-13 4:03 
AnswerRe: How do I create the interface -IConnection- PinmemberAdamNThompson7-Feb-13 16:45 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 22:41 
GeneralRe: My vote of 5 PinmemberAdamNThompson27-Sep-12 8:50 
QuestionHow to use sqlparameter in vss 2008 [modified] Pinmemberwinki_x28-Jan-10 4:08 
Private Sub LoadData()
Dim EmpDataReader As SqlDataReader
Dim SqlStr As String = "SELECT * FROM T_EmpInfo WHERE Name=@Name"
???
Try
EmpDataReader = SqlLibery.GetSqlDataReader(SqlStr)
If EmpDataReader.HasRows Then
While EmpDataReader.Read()
TxtID.Text = EmpDataReader("ID")
TxtName.Text = EmpDataReader("Name")
TxtLastName.Text = EmpDataReader("LastName")
End While
End If
Catch ex As Exception
 
End Try
End Sub
 
modified on Thursday, January 28, 2010 10:55 AM

AnswerRe: How to use sqlparameter in vss 2008 PinmemberAdamNThompson29-Jan-10 6:52 
GeneralRe: How to use sqlparameter in vss 2008 Pinmemberwinki_x29-Jan-10 18:58 
GeneralProblem with shared Connection. PinmemberRajkumar S7-Jan-10 6:18 
GeneralRe: Problem with shared Connection. PinmemberAdamNThompson10-Jan-10 8:20 
GeneralRe: Problem with shared Connection. PinmemberRajkumar S11-Jan-10 3:58 
GeneralRe: Problem with shared Connection. PinmemberAdamNThompson20-Jan-10 1:34 
GeneralRe: Problem with shared Connection. PinmemberRajkumar S20-Jan-10 12:58 
GeneralRe: Problem with shared Connection. PinmemberAdamNThompson21-Jan-10 4:13 
GeneralRe: Problem with shared Connection. PinmemberAdamNThompson21-Jan-10 4:15 
Generalwhere is example and we need more Pinmemberel3ashe22-Oct-09 12:48 
Generalok article PinmemberDonsw11-May-09 9:02 
GeneralRe: ok article PinmemberAdamNThompson12-May-09 3:46 
GeneralRe: ok article PinmemberDonsw12-May-09 5:19 
Generalrather use 3 layers PinmemberArnie100019-Sep-07 3:32 
GeneralRe: rather use 3 layers PinmemberAdamNThompson19-Sep-07 13:41 
GeneralGridView Options PinmemberHeroner13-Sep-07 4:40 
GeneralRe: GridView Options PinmemberAdamNThompson13-Sep-07 6:53 
GeneralRe: GridView Options PinmemberHeroner14-Sep-07 8:34 
GeneralRe: GridView Options PinmemberAdamNThompson15-Sep-07 16:43 
GeneralRe: GridView Options PinmemberHeroner16-Sep-07 0:56 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 28 Sep 2012
Article Copyright 2007 by AdamNThompson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid