Creating a Data Access Framework

<< Welcome | Home | Creating an AJAX Enabled Webparts in Aspnet 2.0 >>

Creating a Data Access Framework

Introduction:

This article describes on how to create a data access architecture using sqlclient objects that will returns a DataTable. It also discusses here on how to use and access a certain method in a particular class for you to manipulate the data into your codes.

Please note that I am using the Northwind database here and all the codes in this article are written in C# language.

STEP1: Adding a Class

First, I added some folders under my App_Code folder to store some classes. This class includes the following below:

CommonQueries.cs - is class that contains all the sql string queries which can be reference in the DAL.cs class.
DAL.cs - is class that executes the request from the user. Its basically executes the Insert, Update, Delete and Fetching of data from the database.
DBConnection.cs - is a class that contains the connection string

See Figure 1:

STEP 2: Setting up the Connection string

In this article I am setting the connection string under <appSettings> in the web config file like below.

<system.webServer>

</system.webServer>

</appSettings>

</configuration>

Note that the attribute “key” will serves as the identifier for the connection string.

STEP 3: Calling the connection string in the class

After setting up the connection string in the web config, the next step is to call that connection string in the DBConnection.cs class that I have been added earlier in the App_Code folder. This is to achieve reusability in accessing the connection string and to reduce writing codes in our project.

The DBConnection Class

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for Connection

/// </summary>

public class Connection

{

public string Connstr()

{

return System.Configuration.ConfigurationManager.AppSettings["ConnString"]; //sets the connection string from your web config file

}

STEP 4: Creating the DAL.cs Class

Data Access Layer (DAL) is basically a class that contains the methods for Insert,Update,Delete and Fetch execution. This will help developers minimize their efforts for manipulating data back and forth because the DAL class in intended to obtain a reusable codes.

The DAL Class

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

/// <summary>

/// Summary description for DAL

/// </summary>

public class DAL

{

// Executes the INSERT/UPDATES

public void InsertWithParam(string statement, SqlParameter[] param)

{

Connection constring = new Connection();

using (SqlConnection conn = new SqlConnection(constring.Connstr()))

{

try

{

conn.Open();

SqlConnection CStr = new SqlConnection(constring.Connstr());

SqlCommand cmd = new SqlCommand(statement, CStr); cmd.CommandType = CommandType.Text;

for (int i = 0; i < param.Length; i++)

{

cmd.Parameters.Add(param[i]);

}

cmd.Connection.Open();

cmd.ExecuteNonQuery();

cmd.Connection.Close();

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Insertion Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

conn.Close();

}

// Executes for FETCHING/SELECTING Data

//Fetches the data from the database with paramters

public DataTable FetchDataWithParam(string statement, SqlParameter[] param)

{

Connection constring = new Connection();

using (SqlConnection conn = new SqlConnection(constring.Connstr()))

{

DataTable dt = new DataTable();

try

{

conn.Open();

SqlConnection CStr = new SqlConnection(constring.Connstr());

SqlCommand cmd = new SqlCommand(statement, CStr);

cmd.CommandType = CommandType.Text;

for (int i = 0; i < param.Length; i++)

{

cmd.Parameters.Add(param[i]);

}

cmd.Connection.Open();

cmd.ExecuteNonQuery();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(dt);

cmd.Connection.Close();

return dt;

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Fetching Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

conn.Close();

}

//Fetches the data from the database WITHOUT paramters

public DataTable FetchData(string statement)

{

Connection constring = new Connection();

using (SqlConnection conn = new SqlConnection(constring.Connstr()))

{

DataTable dt = new DataTable();

try

{

conn.Open();

SqlConnection CStr = new SqlConnection(constring.Connstr());

SqlCommand cmd = new SqlCommand(statement, CStr);

cmd.Connection.Open();

cmd.ExecuteNonQuery();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(dt);

cmd.Connection.Close();

return dt;

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Fetching Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

conn.Close();

}

//Executes for DELETING data

public void DeleteWithParam(string statement, SqlParameter[] param)

{

Connection constring = new Connection();

using (SqlConnection conn = new SqlConnection(constring.Connstr()))

{

try

{

conn.Open();

SqlConnection CStr = new SqlConnection(constring.Connstr());

SqlCommand cmd = new SqlCommand(statement, CStr); cmd.CommandType = CommandType.Text;

for (int i = 0; i < param.Length; i++)

{

cmd.Parameters.Add(param[i]);

}

cmd.Connection.Open();

cmd.ExecuteNonQuery();

cmd.Connection.Close();

}

catch (System.Data.SqlClient.SqlException ex)

{

string msg = "Deletion Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally

{

conn.Close();

}

Note that you should add the namespace below in your class

using System.Data.SqlClient;

DAL Class Methods

Name	Description
InsertWithParam()	Executes for Inserting/Updating data into the database with a specified paramater
FetchDataWithParam()	Executes for Fetching data into the database with a specified paramater
FetchData()	Executes for fetching data into the database
DeleteWithParam()	Executes for deleting data into the database with a specified paramater

STEP 5: Creating the CommonQueries.cs Class

CommonQueries Class is basically contains all the SELECT, UPDATE, INSERT and DELETE sql string queries. What I mean is that all the queries are configured in this class. You can write or add any methods this class.

Basically CommonQueries class calls the DAL class with or without the paramaters. The DAL class will then executes the commands being requested and returns it to the Methods under CommonQueries that calls for it.

In this article, I will just show to you on how are we going to add a simple methods within the CommonQueries class. Basically I write a methods here for Fetching,Inserting and deleting data to the database. Take a look at below

The CommonQueries Class

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

/// <summary>

/// A class that contains the connection string

/// </summary>

public class CommonQueries

{

//A method that Gets all the cutomers from the database

public DataTable GetAllCustomers()

{

DataTable dt = new DataTable();

string sql = string.Empty;

try

{

sql = "SELECT * FROM Customers";

DAL dal = new DAL();

dt = dal.FetchData(sql);

return dt;

}

catch (System.Exception ex)

{

throw new Exception(ex.Message);

}

//A methods that gets all the orders per customer

//GetOrdersPerCustomer is a method with a reqiured parameter

public DataTable GetCustomerDetails(string cusid)

{

DataTable dt = new DataTable();

string sql = string.Empty;

try

{

sql = "SELECT * FROM Customers WHERE CustomerID =@id";

SqlParameter[] param = new SqlParameter[1];

param[0] = new SqlParameter("@id", SqlDbType.NChar, 5);

param[0].Value = cusid;

DAL dal = new DAL();

dt = dal.FetchDataWithParam(sql, param);

return dt;

}

catch (System.Exception ex)

{

throw new Exception(ex.Message);

}

//A methods that Deletes the record per customer in the databae

//DeleteOrdersPerCustomer is a method with a reqiured parameter

public void DeleteCustomer(string cusid)

{

string sql = string.Empty;

try

{

sql = "DELETE FROM Customers WHERE WHERE CustomerID =@id";

SqlParameter[] param = new SqlParameter[2];

param[0] = new SqlParameter("@id", SqlDbType.NChar, 5);

param[0].Value = cusid;

DAL dal = new DAL();

dal.DeleteWithParam(sql, param);

}

catch (System.Exception ex)

{

throw new Exception(ex.Message);

}

//A methods that Inserts new cutomer to the database

//AddNewCustomer is a method with a reqiured parameter

public void AddNewCustomer(int id, string name, string address, string city)

{

string sql = string.Empty;

try

{

sql = " INSERT INTO Customers (CustomerID,ContactName,ContactAddress,City)VALUES (@account,@contact,@nick,@msg)";

SqlParameter[] param = new SqlParameter[4];

param[0] = new SqlParameter("@id", SqlDbType.NChar, 5);

param[1] = new SqlParameter("@name", SqlDbType.NVarChar, 30);

param[2] = new SqlParameter("@address", SqlDbType.NVarChar, 60);

param[3] = new SqlParameter("@city", SqlDbType.NVarChar, 15);

param[0].Value = id;

param[1].Value = name;

param[2].Value = address;

param[3].Value = city;

DAL dal = new DAL();

dal.InsertWithParam(sql, param);

}

catch (System.Exception ex)

{

throw new Exception(ex.Message);

}

CommonQueries Class METHODS

Name	Description
GetAllCustomers()

Vinz d' Code Monkey

Life is like music; it must be composed by ear, feeling, and instinct, not by rule...

Creating a Data Access Framework