using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Data;
using System.Collections;
namespace DAL
{
public class DbConnection
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adp;
StringBuilder sb;
SqlDataReader _rdr;
public DbConnection()
{
con = new SqlConnection(ConfigurationSettings.AppSettings["conn"].ToString());
sb = new StringBuilder("");
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public void OpenDbConnection()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
public void CloseDbConnection()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public SqlCommand GetDbCommand()
{
if (cmd != null)
{
return cmd;
}
else
{
cmd = new SqlCommand();
cmd.Connection = con;
return cmd;
}
}
public int ExecuteNonQuery(string _spname, SqlParameter[] sp_param)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _spname;
foreach (SqlParameter _parm in sp_param)
{
cmd.Parameters.Add(_parm);
}
int i = cmd.ExecuteNonQuery();
return i;
}
public ArrayList GetLogininfo(string username, string password)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SUB_SP_AdminLogin";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.Add("@returnVar", SqlDbType.Bit);
cmd.Parameters["@returnVar"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@RegID", SqlDbType.Int);
cmd.Parameters["@RegID"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
ArrayList result = new ArrayList();
result.Add(cmd.Parameters["@returnVar"].Value);
result.Add(cmd.Parameters["@RegID"].Value);
return result;
}
public ArrayList GetClientLogininfo(string Email, string password)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SUB_SP_GetClientLoginInfo";
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.Add("@returnVar", SqlDbType.Bit);
cmd.Parameters["@returnVar"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@ClientID", SqlDbType.Int);
cmd.Parameters["@ClientID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@OrgId", SqlDbType.VarChar, 50);
cmd.Parameters["@OrgId"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
ArrayList result = new ArrayList();
result.Add(cmd.Parameters["@returnVar"].Value);
result.Add(cmd.Parameters["@ClientID"].Value);
result.Add(cmd.Parameters["@OrgID"].Value);
return result;
}
public DataTable ExecuteDataTable(string _spname, SqlParameter[] sp_param)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _spname;
if (sp_param != null)
{
foreach (SqlParameter _parm in sp_param)
{
cmd.Parameters.Add(_parm);
}
}
DataSet dt = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
adp.Dispose();
cmd.Dispose();
CloseDbConnection();
return dt.Tables[0];
}
public int ExecuteScalar(string _sql)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = _sql;
int count = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
CloseDbConnection();
return count;
}
public SqlDataReader ExecuteReader(string _sql)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = _sql;
_rdr = cmd.ExecuteReader();
return _rdr;
}
public DataTable ExecuteDT(string _Sql)
{
cmd = GetDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = _Sql;
DataSet dt = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
adp.Dispose();
cmd.Dispose();
return dt.Tables[0];
}
}
}
Business Logic Layer :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DAL;
namespace BAL
{
public class ClsManageInterest
{
#region "Properties"
public int InterestID { get; set; }
public string Interest { get; set; }
public string InterestType { get; set; }
public int OrgID { get; set; }
public Boolean IsActive { get; set; }
public string Mode { get; set; }
#endregion
#region "LoadAll Interest Details"
public DataTable InterestDetails()
{
string sql = "select InterestID,Interest,InterestType,IsActive,OrgID from SUB_TBL_Interest where OrgID='" + OrgID + "'";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "All Insert,Update,Delete operation are done"
public int SaveData()
{
DbConnection _clsdb = new DbConnection();
SqlParameter[] _sp = new SqlParameter[6];
_sp[0] = new SqlParameter("@Interest", Interest);
_sp[1] = new SqlParameter("@InterestType", InterestType);
_sp[2] = new SqlParameter("@OrgID", OrgID);
_sp[3] = new SqlParameter("@InterestID", InterestID);
_sp[4] = new SqlParameter("@IsActive", IsActive);
_sp[5] = new SqlParameter("@mode", Mode);
int i = _clsdb.ExecuteNonQuery("[SUB_SP_Interest]", _sp);
return i;
}
#endregion
}
}
