|
|
Reusability of Data Access Code in ADO.NET Applications |   | 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
|
|
