1. DATA LAYER
--------------------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace ClassLibrary.DataAcess
{
public class Data
{
#region "Constant(s)"
// protected static string connectionString = ConfigurationSettings.AppSettings["rajtestConnectionString"].ToString();
protected static string connectionString = "Data Source=happy1;Initial Catalog=Employee13;Integrated Security=True";
#endregion
#region "Constructor(s)"
public Data()
{
}
~Data()
{
}
#endregion
#region "Attributes/Properties"
protected SqlConnection conn = null;
protected SqlTransaction txn = null;
public SqlTransaction Transaction
{
get
{
return txn;
}
set
{
txn = value;
}
}
#endregion
#region "Public Methods"
public void CloseConnection()
{
try
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
public void BeginTransaction()
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
txn = conn.BeginTransaction();
}
catch (Exception ex)
{
throw ex;
}
}
public void CommitTransaction()
{
try
{
if (txn.Connection.State == ConnectionState.Closed)
txn.Connection.Open();
txn.Commit();
}
catch (System.Exception err)
{
throw err;
}
finally
{
this.CloseConnection();
}
}
public void RollbackTransaction()
{
try
{
if (txn != null)
{
if (txn.Connection.State == ConnectionState.Closed)
txn.Connection.Open();
txn.Rollback();
txn = null;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
public DataTable GetSchema(string tableName)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from " + tableName + " where 1 = 0"; // yet to be created.
cmd.CommandType = CommandType.Text;
if (txn != null)
cmd.Transaction = this.txn;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataSet v_ds = new DataSet();
adapter.Fill(v_ds, tableName);
return v_ds.Tables[0];
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="v_ds"></param>
/// <param name="name"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public void FillDataSet(DataSet v_ds, string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == v_ds)
v_ds = new DataSet();
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
//cmd.ExecuteNonQuery();
adapter.Fill(v_ds, name);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
/// <summary>
/// return DataTable
/// </summary>
/// <param name="name"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public DataTable GetDataByReader(string name, string cmdText, SqlParameter[] cmdParms)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: Connect:: Database Connection successfully. ");
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
//using (SqlCommand cmd = conn.CreateCommand())
//{
// cmd.CommandText = cmdText;
// cmd.CommandType = CommandType.StoredProcedure;
// if (null != cmdParms)
// {
// foreach (SqlParameter parm in cmdParms)
// cmd.Parameters.Add(parm);
// }
// using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
// {
// DataSet v_ds = new DataSet();
// //cmd.ExecuteNonQuery();
// adapter.Fill(v_ds, name);
// return v_ds.Tables[0];
// }
//}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
}
/// <summary>
/// <param name="cmdText"></param>
/// <param name="v_dt"></param>
/// </summary>
public int DataTableUpdate(string cmdText, DataTable v_dt)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Transaction = this.txn;
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
return adapter.Update(v_dt);
}
}
}
}
catch (Exception ex)
{
throw ex;
return -1;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
/// <summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
/// </summary>
public int ExecuteNonQuery(string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val = cmd.ExecuteNonQuery();
return val;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
/// <summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
/// </summary>
public object ExecuteScalar(string cmdText, SqlParameter[] cmdParms)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
if (null != cmdParms)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
object val = cmd.ExecuteScalar();
return val;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
/// <summary>
/// This Method is used to Update Single Field for Provided Table
/// </summary>
/// <param name="TableName">Name of Table for which record is to be Updated</param>
/// <param name="PrimaryKeyField">Name of Field of Primary / Unique Key for Specified Table</param>
/// <param name="PrimaryKeyValue">Value of Primary Key Field</param>
/// <param name="FieldToUpdate">Name of field for which Value is updated</param>
/// <param name="NewValueForFieldToUpdate">New Value of FieldToUpdate</param>
public int ExecuteSingleFieldUpdate(string tableName, string primaryKeyField, string primaryKeyValue, string fieldToUpdate, string newValue)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "OMSingleFieldUpdate"; //Yet to be Created.
cmd.Parameters.AddWithValue("?_TableName", tableName);
cmd.Parameters.AddWithValue("?_PrimaryKeyField", primaryKeyField);
cmd.Parameters.AddWithValue("?_PrimaryKeyValue", primaryKeyValue);
cmd.Parameters.AddWithValue("?_FieldToUpdate", fieldToUpdate);
cmd.Parameters.AddWithValue("?_NewValueForFieldToUpdate", newValue);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = this.txn;
int val = cmd.ExecuteNonQuery();
return val;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
public int ExecuteSingleFieldUpdateByQuery(string tableName, string primaryKeyField, string primaryKeyValue, string fieldToUpdate, string newValue)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
string Query = "UPDATE " + tableName + " SET " + fieldToUpdate + " = '" + newValue + "' WHERE " + primaryKeyField + " = '" + primaryKeyValue + "'";
cmd.CommandText = Query;
cmd.CommandType = CommandType.Text;
cmd.Transaction = this.txn;
int val = cmd.ExecuteNonQuery();
return val;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close(); //Closes connection
conn = null;
}
}
}
}
public DataTable GetSingleRecord(string tableName, string primaryKeyField, string primaryKeyValue)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from " + tableName + " where " + primaryKeyField + " = " + primaryKeyValue;
cmd.CommandType = CommandType.Text;
if (txn != null)
cmd.Transaction = this.txn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: CloseConnection:: Database Connection Closed. ");
}
}
}
}
public int GetMaxID(string tableName, string primaryKeyField)
{
try
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select max(" + primaryKeyField + ") from " + tableName;
cmd.CommandType = CommandType.Text;
if (txn != null)
cmd.Transaction = this.txn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
int maxID = 0;
if (dr.Read())
{
if (dr[0] == System.DBNull.Value)
maxID = 0;
else
maxID = Convert.ToInt32(dr.GetValue(0));
}
return maxID;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
//if (log.IsInfoEnabled)
// log.Info("[" + System.DateTime.Now.ToString() + "] DataAccess: CloseConnection:: Database Connection Closed. ");
}
}
}
}
/// <summary>
/// This method is returns the records of any table without filtering.
/// </summary>
/// <param name="tableName">Provide the table name to return the records.</param>
/// <returns></returns>
public DataTable GetAllRecords(string tableName)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from " + tableName;
cmd.CommandType = CommandType.Text;
if (txn != null)
cmd.Transaction = this.txn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
}
public DataTable GetDataByQuery(string Query)
{
try
{
using (DataTable dt = new DataTable())
{
if (null == conn)
{
conn = new SqlConnection(connectionString);
conn.Open();
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Query;
cmd.CommandType = CommandType.Text;
cmd.Transaction = this.txn;
using (SqlDataReader dr = cmd.ExecuteReader())
{
dt.Load(dr);
return dt;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (txn == null)
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
}
#endregion
}
}
--------------------------------------------------------------------------------------------------------------------------------
2.OBJECT LAYER
--------------------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassLibrary.Business_Logic;
using ClassLibrary.DataAcess;
using ClassLibrary.Object;
namespace ClassLibrary.Object
{
public class Objects
{
public Objects()
{
}
private string TxtName;
private string TxtAddress;
private string TxtAge;
private string TxtGender = null;
private string TxtContactNumber;
private string TxtMailID;
private string Emp_ID;
public string EMP_ID
{
get
{
return Emp_ID;
}
set
{
Emp_ID = value;
}
}
public string TxtNamee
{
get
{
return TxtName;
}
set
{
TxtName = value;
}
}
public string TxtAddresss
{
get
{
return TxtAddress;
}
set
{
TxtAddress = value;
}
}
public string TxtAGE
{
get
{
return TxtAge;
}
set
{
TxtAge = value;
}
}
public string TxtGENDER
{
get
{
return TxtGender;
}
set
{
TxtGender = value;
}
}
public string TxtCONTACT
{
get
{
return TxtContactNumber;
}
set
{
TxtContactNumber = value;
}
}
public string TxtMAILID
{
get
{
return TxtMailID;
}
set
{
TxtMailID = value;
}
}
}
}
-------------------------------------------------------------------------------------------------------
3.BussnessLayer LOGIC
-----------------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ClassLibrary.Business_Logic;
using ClassLibrary.DataAcess;
using ClassLibrary.Object;
namespace ClassLibrary.Business_Logic
{
public class Buisness
{
public Buisness()
{
}
Data objdata = new Data();
public DataSet getdata()
{
DataSet ds = new DataSet();
objdata.FillDataSet(ds, "getdata", "SP_DhavalEmpSelect", null);
return ds;
}
public void UpdateEmployeee(Objects obj)
{
try
{
SqlParameter[] pr =
{
new SqlParameter("@EmployeeName",DbType.String),
new SqlParameter("@EmployeeAddress",DbType.String),
new SqlParameter("@EmployeeAge",DbType.String),
new SqlParameter("@EmployeeGender",DbType.String),
new SqlParameter("@EmployeeContact",DbType.String),
new SqlParameter("@EmployeeMail",DbType.String),
new SqlParameter("@Emp_ID",DbType.String),
new SqlParameter("@mod","Update")
};
pr[0].Value = obj.TxtNamee;
pr[1].Value = obj.TxtAddresss;
pr[2].Value = obj.TxtAGE;
pr[3].Value = obj.TxtGENDER;
pr[4].Value = obj.TxtCONTACT;
pr[5].Value = obj.TxtMAILID;
pr[6].Value = obj.EMP_ID;
objdata.ExecuteNonQuery("SP_DhavalForAllinOne", pr);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertEmployeee(Objects obj)
{
try
{
SqlParameter[] pr=
{
new SqlParameter("@EmployeeName",DbType.String),
new SqlParameter("@EmployeeAddress",DbType.String),
new SqlParameter("@EmployeeAge",DbType.String),
new SqlParameter("@EmployeeGender",DbType.String),
new SqlParameter("@EmployeeContact",DbType.String),
new SqlParameter("@EmployeeMail",DbType.String),
new SqlParameter("@mod","Insert")
};
pr[0].Value = obj.TxtNamee;
pr[1].Value = obj.TxtAddresss;
pr[2].Value = obj.TxtAGE;
pr[3].Value = obj.TxtGENDER;
pr[4].Value = obj.TxtCONTACT;
pr[5].Value = obj.TxtMAILID;
objdata.ExecuteNonQuery("SP_DhavalForAllinOne", pr);
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GridBind2ByID(Objects obj)
{
try
{
DataSet ds = new DataSet();
SqlParameter[] pr =
{
new SqlParameter("@EmployeeGender",DbType.String),
// new SqlParameter("@mod","SelectByID")
};
pr[0].Value = obj.TxtGENDER;
objdata.FillDataSet(ds, "getdata", "SP_DhavalEmpSelectByGender", pr);
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet SelectByID(Objects obj)
{
try
{
DataSet ds = new DataSet();
SqlParameter[] pr =
{
new SqlParameter("@Emp_ID",DbType.Int32),
// new SqlParameter("@mod","SelectByID")
};
pr[0].Value = obj.EMP_ID;
objdata.FillDataSet(ds, "getdata", "SP_DhavalEmpSelectByID", pr);
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public void DeleteEmployee(Objects obj)
{
try
{
// DataSet ds = new DataSet();
SqlParameter[] pr =
{
new SqlParameter("@Emp_ID",DbType.Int32)
//new SqlParameter("@mod","Delete")
};
pr[0].Value = obj.EMP_ID;
// pr[0].Value = obj.
objdata.ExecuteNonQuery("SP_DhavalEmpDelete", pr);
// return ds;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
0 comments: