Three Tier Insert and Retrieve Data(Using Singleton for SqlConnection only)

Application layer(PL) :- is the form where we design using the controls like textbox, labels, command buttons etc.

Business layer(BAL) :- is the class where we write the functions which get the data from the application layer and passes through the data access layer.BAL contains business logic, validations or calculations related with the data, if needed.

Data layer(DAL) :- 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(BE) :- 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.

////// SQL HELPER CLASS(Inside the DAL Make sql Helper class) for connection string........

public class sqlHelper
{
private static SqlConnection connection = null;
public static SqlConnection conn
{
get
{
if (connection == null)
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolsConnectionString"].ConnectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}

return connection;
}
}

public static void closeconn(SqlConnection connection)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}

}
}

///////// DAL(Data Access Layer) CLASS LIBRARY..............

Using BE; //ADD REFERENCE OF BE

public class DAL
{
public int InsertDal(BE.BE obBe)
{
try
{
SqlCommand cmd = new SqlCommand("Insert_Student", sqlHelper.conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Name", obBe.Name);
cmd.Parameters.AddWithValue("Roll", obBe.Roll);
return cmd.ExecuteNonQuery();
}
catch
{
sqlHelper.closeconn(sqlHelper.conn);
throw;
}
finally
{
sqlHelper.closeconn(sqlHelper.conn);

}


}

public DataSet BindDal()
{
try
{
SqlCommand cmd = new SqlCommand("Bind", sqlHelper.conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds;
}
catch
{
sqlHelper.closeconn(sqlHelper.conn);
throw;
}
finally
{
sqlHelper.closeconn(sqlHelper.conn);
}
}
}


/////////// BAL(Business Access Layer) CLASS LIBRARY....................
Using DAL;
Using BE;

public class BAL
{
DAL.DAL obDal = new DAL.DAL();
public int InsertBal(BE.BE obBe)
{
try
{
return obDal.InsertDal(obBe);
}
catch { throw; }
}

public DataSet BindBal()
{
try
{
return obDal.BindDal();

}
catch { throw; }

}
}

////// BE(Business Entity) CLASS LIBRARY.............(Property)

public class BE
{
private int _Id;
private string _Name = string.Empty;
private int _roll;

public int Id
{
get { return _Id; }
set { _Id = value; }
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public int Roll
{
get { return _roll; }
set { _roll = value; }
}
}

/////////// PL(Persentation Layer).aspx Page
using BE;
using BAL;

BAL.BAL OBbAL = new BAL.BAL ();
BE.BE obBe = new BE.BE();
//Retrieve the value
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

GridView1.DataSource = OBbAL.BindBal();
GridView1.DataBind();
}
}
//Insert the value
protected void btnInsert_Click(object sender, EventArgs e)
{
obBe.Name =TextBox1.Text.Trim().Replace(",","''");
obBe.Roll =Convert.ToInt32(TextBox2.Text.Trim().Replace(",","''"));
OBbAL.InsertBal(obBe);
TextBox1.Text = "";
TextBox2.Text = "";
}