How to Create Data Access Class

As a software developer our lives revolve around working with data. We create databases to store the data, code to retrieve and modify it. and input interfaces to collect and summarize it. Most of the database applications architecture compose of a Data Access Layer (DAL) using typed data-set, Buisness logic layer (BLL) that organizes custom business rules and Presentation layer composed of forms and pages that shares common layout.

This simple post will guide how to create the Data-Access class that rest of BLL will use to get the data from database.

To create the Data Access class add a new C# file and save it to “App_Code” folder, add the name space of SqlClient at top, and class will be just like this.

using System;
using System.Data.SqlClient;

// Summary
//Data Access Class From Farhanjee.com
//summary
namespace MyData
{
public class DataAccess
{

public DataAccess()
{

}
}
}

Now within a class create a SqlConnection object that will be declare at class level, after that add two method OpenConnection() with return type boolean and CloseConnection(), first method will establish the connection with database and for this get the connection string from webconfig. We may use another method to provide the connection string. second method will close the connection. After that over all class will be look like this.

using System;
using System.Data.SqlClient;
//

// Data Access Class From Farhanjee.com
//

 

namespace MyData
{
public class DataAccess
{
protected SqlConnection Cn = null;

public DataAccess()
{
Cn = new SqlConnection();
}
public bool OpenConnection()
{
string strConString;
bool cnopen = false;
strConString = System.Configuration.ConfigurationManager. ConnectionStrings["MyDBConnection"].ConnectionString;

try
{
if (Cn.State == ConnectionState.Closed)
{
Cn = new SqlConnection(strConString);
Cn.Open();
cnopen = true;
}
}
catch
{
cnopen = flase;
}
return cnopen;
}
public void CloseConnection()
{
Cn.Close();
}
}
}

Add a simple method call it ExecuteNonQuery() with a string parameter, that will execute DML (Data manipulation language) statement.

public bool ExecuteNonQuery(string Qry)
{
OpenConnection();
SqlCommand Cmd = new SqlCommand(Qry.Replace(“True”, “1″).Replace(“False”, “0″), Cn);
bool flag = false;

try
{
Cmd.ExecuteNonQuery();
flag = true;
}
catch (Exception ex)
{
flag = false;
}
finally
{
CloseConnection();
}
return flag;

}

30. October 2011 by Farhan Alam
Categories: ASP.NET, Data Access Class | Tags: Data Access Class | 8 comments