Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

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.

 

<configuration>

  <system.webServer>

 

  </system.webServer>

  <appSettings>

    <add key="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">

<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:

            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

Technorati Tags: ,,
Posted on Friday, February 1, 2008 9:35 AM ADO.NET , ASP.NET , C# | Back to top


Comments on this post: Creating a Simple Data Access Framework in ASP.NET

# Use <connectionStrings> for connection in config file
Requesting Gravatar...
Nice article Vincent,

one correction

You can use <connectionStrings> inbuilt xml tag in your configuration files, and this is mapped with ConnectionStringsSectionClass in .net 2.0, which gives more type safe access to connection strings.


--thanks
Pradeep
(http://tiwaripradeep.blogspot.com/)
Left by Pradeep Tiwari on Feb 03, 2008 6:49 PM

# re: Creating a Data Access Framework
Requesting Gravatar...
Very Good Example for understanding the Buisness Logic and Data Ascess Layer
Left by Sunil Bailwal on Apr 01, 2008 10:50 PM

# re: Creating a Data Access Framework
Requesting Gravatar...
Looks great. you could also use "using" on other methods that implement Idisposable like SqlCommand
Left by Peter Kellner on Dec 25, 2009 5:22 PM

# re: Creating a Data Access Framework
Requesting Gravatar...
Great article. I have been researching different methods for my own DAL and i really like the dynamic setup you have created for calling CRUD operations.

I was wondering though about other class structures of SQL tables? It appears you have only created the queries for accessing the data but not the properties of each table along with adapters? Do you think this is necessary?

Also, would you consider the commonqueries class to be part of the DAL or more like a BLL?
Left by Brendan on Feb 18, 2010 8:48 AM

# re: Creating a Simple Data Access Framework in ASP.NET
Requesting Gravatar...
What if I want to make this more testable by adding dependency injection? Say I would like to send the SqlConnection as an argument to the DAL constructor. How would you then go about to execute InsertWithParam when the "using" will dispose the SqlConnection? Is it better to just let InsertWithParam do Open and Close on the SqlConnection and then add a Dispose-method which will Dispose the SqlConnection instead?
Left by DCU on Mar 18, 2010 9:15 PM

# re: Creating a Simple Data Access Framework in ASP.NET
Requesting Gravatar...
Great article. Helped with BLL and DAL a lot.
Left by grant on Apr 07, 2010 11:04 AM

# re: Creating a Simple Data Access Framework in ASP.NET
Requesting Gravatar...
Hi Your blog is wonderfull i like it
Left by jignesh on Nov 15, 2010 7:24 PM

# re: Creating a Simple Data Access Framework in ASP.NET
Requesting Gravatar...
@ grant and jignesh,

Glad to hear this post helped you.
Left by Vinz on Nov 16, 2010 3:31 PM

# re: Creating a Simple Data Access Framework in ASP.NET
Requesting Gravatar...
really helpful information according to my question.
thank you so much
Left by Abhay Kumar Singh on Oct 11, 2011 8:11 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net