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>

<addkey="ConnString"value="Data Source=WPHVD185022-LT8\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;"/>

</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()	Gets all the customers from the database
GetCustomerDetails()	Gets the customer details from the database based on the customer id
DeleteCustomer()	Delete the records of the customer in the database
AddNewCustomer()	Insert new customer in the database

STEP 6: Accessing the Class in the Page

After creating those classes then we can now test for it. In this article I will be calling the GetAllCustomer() and GetCustomerDetails() methods in the CommonQueries and populate my DropDownList and GridView. Basically the DropDownList will be populated by the CustomersID and the GridView will be populated based on the selected ID from the DropDownList. So here it is below:

Default.aspx.cs

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;

public partial class _Default : System.Web.UI.Page

{

private void PopulateDropDownList()

{

//first we need to instatiate the CommonQueries.cs Class so that we can access all the methods in that class.

CommonQueries getcustomers = new CommonQueries();

//I define a new datatable as my datasource for populating the DropDownList

DataTable dt = getcustomers.GetAllCustomers(); // GetAllCustomers is a method under the CommonQueries.cs class

if (dt.Rows.Count > 0)// validates id dt has a returned value

{

DropDownList1.Items.Add("Select Customer ID"); // adds the word select before the dropdownlist is being populated

for (int i = 0; i < dt.Rows.Count; i++)

{

string id = dt.Rows[i]["CustomerID"].ToString(); //gets the customer id

string customername = dt.Rows[i]["ContactName"].ToString(); //gets the customer name

DropDownList1.Items.Add(id + "-" + customername); // combine the two values and add it in the dropdown lists

}

else

{

Response.Write("Record is empty");

}

private void PopulateGridView()

{

CommonQueries getdetails = new CommonQueries();

DataTable dt = getdetails.GetCustomerDetails(DropDownList1.SelectedItem.Text);

GridView1.DataSource = dt;

GridView1.DataBind();

}

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

PopulateDropDownList();

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

PopulateGridView();

}

Default.aspx source

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<title>Default</title>

</head>

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server" />

<div>

<asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server" Width="216px" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

</asp:DropDownList>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">

<asp:BoundField DataField="CompanyName" HeaderText="Company" />

<asp:BoundField DataField="ContactName" HeaderText="Full Name" />

<asp:BoundField DataField="ContactTitle" HeaderText="Position/Title" />

<asp:BoundField DataField="Address" HeaderText="Address" />

<asp:BoundField DataField="City" HeaderText="City" />

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

OUTPUT:

Take a look at the output below:

See Figure2:

So thats it...I hope you will learn a lot with regards to this article.

Happy Programming!

by: Vincent Maverick Durano

Posted on Friday, February 1, 2008 3:11 PM | Back to top