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;
DAL:
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 GetAdminLogininfo(string username, string password)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Pipe_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("@ID", SqlDbType.Int);
cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
ArrayList result = new ArrayList();
result.Add(cmd.Parameters["@returnVar"].Value);
result.Add(cmd.Parameters["@ID"].Value);
return result;
}
public ArrayList GetRepLogininfo(string username, string password)
{
OpenDbConnection();
cmd = GetDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Pipe_SP_RepLogin";
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("@ID", SqlDbType.Int);
cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
ArrayList result = new ArrayList();
result.Add(cmd.Parameters["@returnVar"].Value);
result.Add(cmd.Parameters["@ID"].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];
}
//public SqlDataAdapter GetDataAdaptor()
//{
// if (adp != null)
// {
// return adp;
// }
// else
// {
// adp = new SqlDataAdapter(;
// }
//}
}
}
BAL :
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 ClsAddLeads
{
#region "Properties"
public int ID { get; set; }
public int RepsID { get; set; }
public int LeadsID { get; set; }
public DateTime DateAdded { get; set; }
public string Fname { get; set; }
public string Contact { get; set; }
public string Position { get; set; }
public DateTime LastContactDate { get; set; }
public string Phone { get; set; }
public string LegacyPartner { get; set; }
public string Investment { get; set; }
public string Notes { get; set; }
public string Mode { get; set; }
#endregion
#region "All Insert,Update,Delete operation are done"
public int Leads()
{
DbConnection _clsdb = new DbConnection();
SqlParameter[] _sp = new SqlParameter[11];
_sp[0] = new SqlParameter("@ID", ID);
_sp[1] = new SqlParameter("@RepsID", RepsID);
_sp[2] = new SqlParameter("@Fname", Fname);
_sp[3] = new SqlParameter("@Contact", Contact);
_sp[4] = new SqlParameter("@Position", Position);
_sp[5] = new SqlParameter("@LastContactDate",Convert.ToDateTime(LastContactDate));
_sp[6] = new SqlParameter("@Phone", Phone);
_sp[7] = new SqlParameter("@LegacyPartner", LegacyPartner);
_sp[8] = new SqlParameter("@Investment", Investment);
_sp[9] = new SqlParameter("@Notes", Notes);
_sp[10] = new SqlParameter("@mode", Mode);
int i = _clsdb.ExecuteNonQuery("Pipe_SP_Leads", _sp);
return i;
}
public int AddRepsLeads()
{
DbConnection _clsdb = new DbConnection();
SqlParameter[] _sp = new SqlParameter[5];
_sp[0] = new SqlParameter("@ID", ID);
_sp[1] = new SqlParameter("@RepsID", RepsID);
_sp[2] = new SqlParameter("@LeadsID",LeadsID);
_sp[3] = new SqlParameter("@DateAdded", DateAdded);
_sp[4] = new SqlParameter("@mode", Mode);
int i = _clsdb.ExecuteNonQuery("Pipe_SP_Leads", _sp);
return i;
}
#endregion
#region "LoadAll Leads Details"
public DataTable GetLeads()
{
string sql = "select * from Pipe_TBL_AddLeads where Fname='" + Fname + "'";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
public DataTable GetReps()
{
string sql = "select * from Pipe_TBL_RepsLeads where LeadsID='"+ LeadsID +"' order By ID ";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "All Insert,Update,Delete operation are done"
public int DeleteLeads()
{
DbConnection _clsdb = new DbConnection();
SqlParameter[] _sp = new SqlParameter[11];
_sp[0] = new SqlParameter("@ID", ID);
_sp[1] = new SqlParameter("@Fname", Fname);
_sp[2] = new SqlParameter("@Contact", Contact);
_sp[3] = new SqlParameter("@Position", Position);
_sp[4] = new SqlParameter("@LastContactDate", null);
_sp[5] = new SqlParameter("@Phone", Phone);
_sp[6] = new SqlParameter("@LegacyPartner", LegacyPartner);
_sp[7] = new SqlParameter("@Investment", Investment);
_sp[8] = new SqlParameter("@Notes", Notes);
_sp[9] = new SqlParameter("@mode", Mode);
_sp[10] = new SqlParameter("@RepsID", RepsID);
int i = _clsdb.ExecuteNonQuery("Pipe_SP_Leads", _sp);
return i;
}
#endregion
#region "LoadAll Leads Details"
public DataTable LeadsDetails()
{
string sql = "select * from Pipe_TBL_AddLeads order by ID";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "LoadAll Leads Details"
public DataTable ShowLeads()
{
string sql = "select C.LeadsID,C.RepsID,A.ID,A.fname,A.Position,A.phone from Pipe_TBL_AddLeads as A , Pipe_TBL_RepsLeads as C where C.LeadsID=A.ID and C.RepsID='" + RepsID + "' order by A.ID";
//string sql = "select A.ID,A.RepsID,B.Fullname,A.fname,A.Position,A.phone from Pipe_TBL_AddLeads as A , Pipe_TBL_AddReps as B where A.RepsID=B.ID and A.RepsID='" + RepsID + "' order by A.ID";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "LoadAll Leads details by ID"
public DataTable Leadsdata()
{
string sql = "select * from Pipe_TBL_AddLeads where ID='" + ID + "'";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "LoadAll Leads details by RepsID for Reports"
public DataTable LeadsReport()
{
// string sql = "select ID,RepsID,fname,Position,CONVERT(VARCHAR(12),LastContactDate, 106) as LastContactDate,Phone ,Contact,LegacyPartner,Investment,Notes from Pipe_TBL_AddLeads where RepsID='" + RepsID + "'";
string sql = "select C.LeadsID,C.RepsID,A.ID,A.fname,A.Position,A.phone,CONVERT(VARCHAR(12),A.LastContactDate, 106) as LastContactDate,Contact,LegacyPartner,Investment,Notes from Pipe_TBL_AddLeads as A , Pipe_TBL_RepsLeads as C where C.LeadsID=A.ID and C.RepsID='" + RepsID + "' order by A.ID";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "LoadAll Leads details by RepsID"
public DataTable LeadsdataByRepsID()
{
//string sql = "select A.ID,A.RepsID,B.Fullname,A.fname,A.Position,A.phone from Pipe_TBL_AddLeads as A , Pipe_TBL_AddReps as B where A.RepsID=B.ID and RepsID='" + RepsID + "' order by A.ID";
string sql = "select C.LeadsID,C.RepsID,A.ID,A.fname,A.Position,A.phone from Pipe_TBL_AddLeads as A , Pipe_TBL_RepsLeads as C where C.LeadsID=A.ID and C.RepsID='" + RepsID + "' order by A.ID";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
#region "Load Reps Name for reports"
public DataTable RepsName()
{
string sql = "SELECT DISTINCT A.FullName as FullName FROM Pipe_TBL_AddReps as A,Pipe_TBL_AddLeads as B where B.RepsID=A.ID ORDER BY A.FullName";
DbConnection _clsdb = new DbConnection();
DataTable dt = _clsdb.ExecuteDT(sql);
return dt;
}
#endregion
}
}
PL:
ClsAddLeads oRL = new ClsAddLeads();
oRL.RepsID = Convert.ToInt16(RepsselectedItem);
oRL.LeadsID =Convert.ToInt16(ViewState["ID"]);
oRL.DateAdded = DateTime.Now;
oRL.Mode = "RepsLeads";
int ok = oRL.AddRepsLeads();
if (ok == -1)
{
lblResult.Visible = true;
lblResult.ForeColor = Color.Green;
lblResult.Text = "Save successful ";
}
