N Tier Architecture
DataClass
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace DataLayer
{
#region Data provider
public class Dataprovider
{
public string connection = ConfigurationManager.ConnectionStrings["rvs"].ConnectionString;
private static string errormsg;
public static string Errormsg
{
get { return Dataprovider.errormsg; }
set { Dataprovider.errormsg = value; }
}
SqlDataAdapter SqlDataAdr;
public DataTable oDataTable;
public DataSet oDataSet;
public SqlCommand SqlComm;
public SqlConnection GetConnection()
{
return new SqlConnection(connection);
}
public DataTable ExecuteTable(string strStoredProc, SqlParameter[] sqlParams)
{
try
{
SqlDataAdr = new SqlDataAdapter(strStoredProc, GetConnection());
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;
foreach (SqlParameter sqlParam in sqlParams) { SqlDataAdr.SelectCommand.Parameters.Add(sqlParam); }
oDataTable = new DataTable();
SqlDataAdr.Fill(oDataTable);
}
catch
{
throw;
}
return oDataTable;
}
public void ExecuteVoid(string strStoredProc, SqlParameter[] sqlParams)
{
try
{
SqlConnection conn = new SqlConnection(connection);
SqlComm = new SqlCommand(strStoredProc, conn);
SqlComm.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter sqlParam in sqlParams) { SqlComm.Parameters.Add(sqlParam); }
conn.Open();
SqlComm.ExecuteNonQuery();
conn.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
errormsg = string.Empty;
}
catch (Exception ex)
{
// DL_ErrorReturn oDL_ErrorReturn = new DL_ErrorReturn();
if (ex.Message.StartsWith("The DELETE statement conflicted with the REFERENCE constraint"))
{
errormsg = "You should delete the child record first";
}
else if (ex.Message.StartsWith("Violation of PRIMARY KEY constraint"))
{
errormsg = "Already Exists";
}
else
{
errormsg = ex.Message;
}
//oDL_ErrorReturn.err();
// throw;
}
}
public DataTable ExecuteTable_wop(string strStoredProc)
{
SqlDataAdr = new MySqlDataAdapter(strStoredProc, GetConnection());
SqlDataAdr.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdr.SelectCommand.CommandTimeout = 0;
dt = new DataTable();
SqlDataAdr.Fill(dt);
return dt;
}
}
#endregion
#region Course Table Class
public class DL_Course : Dataprovider
{
public void Put_Data(string department_id, string course_id, string course, string eve)
{
SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("c_id",course_id),
new SqlParameter("department_id",department_id),
new SqlParameter("course",course),
new SqlParameter("eve",eve)
};
ExecuteVoid("dml_course", sqlParams);
}
public DataTable Get_Data(string course_id, string d_id, string eve)
{
SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("c_id",course_id),
new SqlParameter("d_id",d_id),
new SqlParameter("eve",eve)
};
return ExecuteTable("dml_course_select", sqlParams);
}
}
#endregion
Business Class
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DataLayer;
using System.Configuration;
using System.Data.SqlClient;
namespace BusinessLayer
{
#region Course Class
public class BL_course
{
DL_Course dc = new DL_Course();
public void Put_Data(string department_id, string course_id, string course, string eve)
{
dc.Put_Data(department_id, course_id, course, eve);
}
public DataTable Get_Data(string course_id,string d_id, string eve)
{
return dc.Get_Data(course_id, d_id, eve);
}
}
#endregion
}