KnowDotNet

Reusability of Data Access Code in ADO.NET Applications

by Les Smith

Reusability is the key to rapid application development in Visual Studio .NET, especially when it relates to data access via ADO.NET.  This article discusses the use of a data access class that I wrote many months ago and use daily in every application I develop.  For the sake of space, I have removed some of the methods and all error handling that relates specifically to an application.

I am providing the code for the class in both C# codeand VB.NET code, but for this article, the code that shows how to use the class is provided in C# only.  I use both languages to develop applications, depending on the application and what it does.  The class discussed in this article is using SQL Server objects, but I normally have methods for using OleDB objects as well as SqlClient objects.   I have simply removed them for sake of space.

To call the class methods, you need to set up some module level variables, whether it is in a form or a class.  Those variables are shown below.  They provide an instantiation of the data access class, CDataAccess, along with a connection string, and connection object.

     private CDataAccessNS.CDataAccess oDA =
               new CDataAccessNS.CDataAccess();
     private string ConnStr =
              "Data Source=MY-SQLSRV;"+
              "Integrated Security=SSPI;" +
           "Initial Catalog=ProductTracking";
     private SqlConnection Connection;

Next, I have coded a test function for testing four different methods of the class.  Two of the calls are to overloaded functions that return a DataTable with the results of the passed SQL statement.  The other two functions provide update capability for processing action SQL statements.  

The difference between the two methods in each set is that one of them accepts a connection string and automatically opens a connection.  I use this call when I am making a single request for data.  If my application is in some kind of loop reading or updating a table, I will call the method that accepts a connection that I have previously opened, prior to calling the read or update method in the loop.  My tests have shown a major performance improvement if I use the same connection in a loop, as opposed to opening and closing the connection for each read or update in the loop.

The four calls are documented in the code, and do not require further discussion here.

  private void TestDataAccess()
      {
      try
        {
          // read from db using connect string
             DataTable dt = new DataTable();
            string Sql = "Select count(*) from product_tracking";
            int iRows = oDA.SendQuery(Sql, ref dt, ConnStr);
             Debug.WriteLine(dt.Rows[0].ItemArray[0]);

          // read from db using connection
          // this type of call would normally be used in a loop
             dt.Reset();
             Connection = new SqlConnection();
            if(oDA.OpenConnection(ConnStr, ref Connection) == false)
            {
                Debug.WriteLine("Open connection failed");
            }
            else
           {
                iRows = oDA.SendQuery(Sql,ref dt, ref Connection);
                Debug.WriteLine(dt.Rows[0].ItemArray[0]);
                Connection.Close();
            }

            // write to db using connect string
            Sql = "update product_tracking ";
             Sql += "set product_class = 'Test' ";
             Sql += "where product_number = 'PR-01-2345' ";
            string ErrMsg = String.Empty;
             iRows = oDA.SendActionSql(Sql, ConnStr, ref ErrMsg);
            if (iRows == 0)
            {
                Debug.WriteLine(ErrMsg);
            }

            // write to db using connection
          // this type of call would normally be used in a loop
          // and use the same connection repeatedly to enhance
          // performance.
           if(oDA.OpenConnection(ConnStr, ref Connection) == false)
            {
                Debug.WriteLine("Open connection failed");
            }
            else
           {
                Sql = "update product_tracking ";
                Sql += "set product_class = 'Test2' ";
                Sql += "where job_number = 'PR-0102345' ";
                ErrMsg = String.Empty;
                iRows = oDA.SendActionSql(Sql, Connection, ref ErrMsg);
               if (iRows == 0)
               {
                   Debug.WriteLine(ErrMsg);
               }
            }
         }
         catch(System.Exception ex)
         {
            Debug.WriteLine(ex.ToString());
        }
      }

The following code is the actual CDataAccess Class.  This class can be enhanced greatly to use other ADO.NET objects, such as DataSets, DataReaders, etc.  My intent in this article is to suggest to you an example of setting up reusable classes to provide the basic building blocks for the backbone of any new application.  Once you have developed, or downloaded from any number of sources on the web, a library of classes that perform the basic back-end functions, application development becomes much less drudgery and a much easier task.

Back to Top


C # Code:

using System;
using System.Data.SqlClient;
using System.Data;
namespace CDataAccessNS
{
   public class CDataAccess
   {
      private SqlCommand dbCmdSS;
      private SqlDataAdapter daSS;
      private SqlConnection ConnSS;

      // Returns number of rows affected
     //  dt will be filled
     //  Returns -2 on any error
     public int SendQuery(string Sql, ref DataTable dt,
           string ConnectString)
      {
         try
        {
             ConnSS = new SqlConnection(ConnectString);
             dbCmdSS = new SqlCommand(Sql, ConnSS);
             daSS = new SqlDataAdapter(dbCmdSS);
             daSS.Fill(dt);
            return dt.Rows.Count;
         }
         catch
        {
            return -2;
         }
      }
    
     // overloaded function that accepts a connection
     // and does not connnect and disconnect
     // used when program is going to do numerous hits
     // to database in a loop
     // Returns number of rows affected
     // dt will be filled
     // returns -2 on any error
     public int SendQuery(string Sql, ref DataTable dt,
           ref SqlConnection Connection)
      {
         try
        {
             dbCmdSS.CommandText = Sql;
             dbCmdSS.Connection= Connection;
             daSS = new SqlDataAdapter(dbCmdSS);
             daSS.Fill(dt);
            return dt.Rows.Count;
         }
         catch
        {
            return -2;
         }
      }

      //  opens a connection to the database
     // and return true if successful
     public bool OpenConnection(string ConnStr,
           ref SqlConnection Conn)
      {
         try
        {
             Conn.ConnectionString = ConnStr;
            if(Conn.State != ConnectionState.Open)
            {
                Conn.Open();
               return true;
            }
            return true;
         }
         catch
        {
            return false;
         }
      }


     // Executes the passed action sql.  returns
     // nbr rows affected if successful
     // otherwise returns -1 and sErr will
     // contain ex.tostring
     public int SendActionSql(string Sql, SqlConnection Conn,
           ref string ErrMsg)
      {
         try
        {
            int RowsAffected = 0;
             dbCmdSS = new SqlCommand();
             dbCmdSS.CommandText=Sql;
             dbCmdSS.Connection=Conn;
             RowsAffected = dbCmdSS.ExecuteNonQuery();
            return RowsAffected;
         }
         catch(System.Exception ex)
         {
             ErrMsg=ex.ToString();
            return -1;
         }
      }

     // Executes the passed action sql.  returns
     // nbr rows affected if successful
     // otherwise returns -1 and sErr will
     // contain ex.tostring
     public int SendActionSql(string Sql, string ConnString,
           ref string ErrMsg)
      {
         try
        {
            if(OpenConnection(ConnString,
                           ref ConnSS)!=true)
            {
                ErrMsg = "Unable to open connection";
                              return -1;
            }
            int RowsAffected = 0;
             dbCmdSS = new SqlCommand();
             dbCmdSS.CommandText=Sql;
             dbCmdSS.Connection=ConnSS;
             RowsAffected = dbCmdSS.ExecuteNonQuery();
             ConnSS.Close();
            return RowsAffected;
         }
         catch(System.Exception ex)
         {
            ErrMsg = ex.ToString();
            return -1;
         }
      }

     public CDataAccess()
      {
     }
   }
}

Back to Top



VB Code:

'****************************************
'* Purpose: Reusable methods for data access
'* Author:  Les Smith
'* Date Created: 09/17/2002 at 02:15:16
'****************************************
Imports System.Data.SqlClient
Imports System.Windows.Forms.Application

Public Class CDataAccess
   Dim dbCmdSS As New SqlCommand()
   Dim daSS As New SqlDataAdapter()
   Dim ConnSS As New SqlConnection()
   Dim mytrans As SqlTransaction

   ' overloaded function that accepts a connection
   ' and does not connnect and disconnect
   ' used when program is going to do numerous hits
   ' to database in a loop
   ' Returns number of rows affected
   ' If error, ErrNum and ErrMsg will have the
   ' respective values
   ' dt will be filled
   ' Returns -1 if connection can't be made
   ' returns -2 on any other error
   Public Overloads Function SendQuery(ByVal Sql As String, _
       ByRef dt As DataTable, _
       ByRef Connection As SqlConnection) As Integer


      Try
         dbCmdSS.CommandText = Sql
         dbCmdSS.Connection = Connection
         daSS = New SqlDataAdapter(dbCmdSS)
         daSS.Fill(dt)
         Return dt.Rows.Count

      Catch ex As System.Exception
      End Try
   End Function

   ' Returns number of rows affected
   ' If error, ErrNum and ErrMsg will have the
   ' respective values
   ' dt will be filled
   ' Returns -1 if connection can't be made
   ' returns -2 on any other error
   ' SSSql = "S" for SqlSrvr or "O" for Sql
   ' This function supports Sql
   Public Overloads Function SendQuery(ByVal Sql As String, _
       ByRef dt As DataTable, _
       ByVal ConnectString As String) As Integer
      Try
         If Not Me.OpenConnection(ConnectString, ConnSS) Then
            Return -1 ' can't connect to db
         End If
         Me.dbCmdSS.CommandText = Sql
         dbCmdSS.Connection = ConnSS
         daSS = New SqlDataAdapter(dbCmdSS)
         daSS.Fill(dt)
         ConnSS.Close()
         Return dt.Rows.Count

      Catch ex As System.Exception
         Return -1
      End Try
   End Function

   '' Executes the passed action sql.  Returns the following:
   '' iRowsAffected if successful
   '' -1 = unique constraint violation
   '' -2 = locked and retries exceeded
   '' -3 = other error (ErrMsg will contain err.description)
   Public Overloads Function SendActionSql(ByVal Sql As String, _
        ByVal ConnectString As String, _
        ByRef ErrMsg As String) _
        As Integer
      Dim iStart As Integer
      Dim iRowsAffected As Integer
      Dim dbCmdSS As New SqlCommand()
      Dim ConnSS As New SqlConnection()
      Dim mytrans As SqlTransaction
      Dim errCnt As Integer
      ErrMsg = ""

      Try
         If Not Me.OpenConnection(ConnectString, ConnSS) Then
            ErrMsg = "Unable to connect to " & ConnectString
            Return -3
         End If
         mytrans = ConnSS.BeginTransaction()
         dbCmdSS.CommandText = Sql
         dbCmdSS.Connection = ConnSS
         dbCmdSS.Transaction = mytrans

         iRowsAffected = dbCmdSS.ExecuteNonQuery
         mytrans.Commit()

         Return iRowsAffected
      Catch ex As System.Exception
         ErrMsg = ex.ToString
         Return -1
      End Try
   End Function

   '' Executes the passed action sql.  Returns the following:
   '' iRowsAffected if successful otherwise -1 with exception in ErrMsg
   Public Overloads Function SendActionSql(ByVal Sql As String, _
      ByRef Connection As SqlConnection, _
      ByRef ErrMsg As String) _
      As Integer
      Dim iStart As Integer
      Dim iRowsAffected As Integer
      Dim dbCmd As New SqlCommand()
      Dim Conn As New SqlConnection()
      Dim errCnt As Integer
      Try
         ErrMsg = String.Empty
         dbCmd.CommandText = Sql
         dbCmd.Connection = Connection
         iRowsAffected = dbCmd.ExecuteNonQuery

         Return iRowsAffected
      Catch ex As System.Exception
         ErrMsg = ex.ToString
         Return -1
      End Try
   End Function

   ' opens a connection to the database and return true if successful
   Public Overloads Function OpenConnection(ByVal ConnStr As String, ByRef Conn As SqlConnection) As Boolean
      Dim i As Integer
      Try
         Do
            Conn.ConnectionString = ConnStr
            Conn.Open()
            Return True
      Catch
         Return False
      End Try
   End Function
End Class

Back to Top