Creating a Simple Data Access Framework in ASP.NET

Introduction:

This article describes on how to create a data access architecture using sqlclient objects with ADO.NET 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 codes in this article is written in C# language.

STEP1: Creating a Class

First, I added some folders under my App_Code folder to store some classes. These 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 the figure below.

STEP 2: Setting up the Connection string

In this article I am setting the connection string under the ConnectionString tag in the web config file like below.

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 application.

The DBConnection Class

Note: MyDBConnection is the name of the connectionstring that was set up in the webconfig.

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

    }
}

using System.Data.SqlClient;

The following are the names of the DAL methods with its corresponding descriptions.

Note that you should declare the namespace below in your class fro you to use SqlClient libraries.

InsertWithParam()	A method that executes for Inserting/Updating data into the database with a specified paramater
FetchDataWithParam()	A method that executes for Fetching data into the database with a specified paramater
FetchData()	A method that executes for fetching data into the database
DeleteWithParam()	A method that executes for deleting data into the database with a specified paramater

STEP 5: Creating the CommonQueries.cs Class

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

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. I also include a methods here for Fetching,Inserting and deleting data to the database. Take a look at the following code blocks 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;

///
/// A class that contains the connection string
///

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

                 DA Ldal = new DAL();
                 dal.InsertWithParam(sql, param);

             }
             catch (System.Exception ex)
             {
               throw new Exception(ex.Message);
             }

         }
}

The following are the names of the CommonQueries method with its correspondin descriptions.

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.

Here's the sample code blocks below:

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

The 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Default</title>
</head>
<body>
    <form id="form1" runat="server">
        <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">
                <Columns>
                    <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:
here's the output below for that query.

That's it.

Creating a Simple Data Access Framework in ASP.NET

Creating a Simple Data Access Framework in ASP.NET

Author

Comments (no comments yet)

Post a comment

Related articles

Join CodeAsp.Net for FREE Today!

Latest Articles