Creating a Data Access Layer

We'll start with creating a software architecture composed of a Data Access Layer (DAL) using Typed DataSets, a Business Logic Layer (BLL) that enforces custom business rules, and a presentation layer composed of ASP.NET pages that share a common page layout. Once this backend groundwork has been laid, we'll move into reporting, showing how to display, summarize, collect, and validate data from a web application

In this tutorial we'll start from the very beginning and create the Data Access Layer (DAL), followed by creating the Business Logic Layer (BLL) in the second tutorial, and working on page layout and navigation in the third. The tutorials after the third one will build upon the foundation laid in the first three

This layer is also a class which we use to get or set the data to the database back and forth. This layer only interacts with the database. We write the database queries or use stored procedures to access the data from the database or to perform any operation to the database.

Summary

• Application layer is the form where we design using the controls like textbox, labels, command buttons etc.
• Business layer is the class where we write the functions which get the data from the application layer and passes through the data access layer.
• Data layer is also the class which gets the data from the business layer and sends it to the database or gets the data from the database and sends it to the business layer.
• Property layer is the sub layer of the business layer in which we make the properties to sent or get the values from the application layer. These properties help to sustain the value in a object so that we can get these values till the object destroy

Partial Code for Data Access Layer

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;

public class person
{
SqlConnection myconn = new SqlConnection("server=isa;database=CuteChat4;uid=sa;password=sa");

public person()
{
//
// TODO: Add constructor logic here
//
}
///

/// Used to insert records into database
///

public int insert(string title,string description)
{
myconn.Open();
SqlCommand cmd = new SqlCommand("insertnews", myconn);
cmd.CommandType = CommandType.StoredProcedure;

try
{
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@description", description);
return cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
myconn.Close();
myconn.Dispose();

}

}
///

/// Update record into database
///

public int update(int id, string title, string description)
{

myconn.Open();
SqlCommand cmd = new SqlCommand("updatenews", myconn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@description", description);
return cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
myconn.Close();
myconn.Dispose();
}

}
///

/// Shows all records from database
///

public DataTable show()
{

SqlDataAdapter da = new SqlDataAdapter("selectnews", myconn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();

try
{
da.Fill(ds, "news");
return ds.Tables["news"];
}
catch
{
throw;
}

finally
{
ds.Dispose();
da.Dispose();
myconn.Close();
myconn.Dispose();
}
}

public DataTable show1(int id)
{

SqlDataAdapter da = new SqlDataAdapter("select* from news where id=" + id, myconn);
da.SelectCommand.CommandType = CommandType.Text;
DataSet ds = new DataSet();

try
{
da.Fill(ds, "news");
return ds.Tables["news"];
}
catch
{
throw;
}

finally
{
ds.Dispose();
da.Dispose();
myconn.Close();
myconn.Dispose();
}

}

///

/// Delete record from database
///

public int delete(int id)
{
myconn.Open();
SqlCommand cmd = new SqlCommand("deletenews", myconn);
cmd.CommandType = CommandType.StoredProcedure;

try
{
cmd.Parameters.AddWithValue("@id", id);
return cmd.ExecuteNonQuery();
}

catch
{
throw;
}

finally
{
cmd.Dispose();
myconn.Close();
myconn.Dispose();

}

}

}