public List <ProspectSearchModel> FillProspectSearchGrid(ProspectSearchModel model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <ProspectSearchModel> lstProspect = new List <ProspectSearchModel>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetProspectPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbParameter param5 = cmd.CreateParameter();
param5.ParameterName = "SortBy";
param5.Value = model.SortBy;
cmd.Parameters.Add(param5);
DbParameter param6 = cmd.CreateParameter();
param6.ParameterName = "OrderBy";
param6.Value = model.OrderBy;
cmd.Parameters.Add(param6);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
ProspectSearchModel psmodel = new ProspectSearchModel();
psmodel.PID = Convert.ToInt64(dr["PID"].ToString());
psmodel.FirstName = dr["FirstName"].ToString();
psmodel.LastName = dr["LastName"].ToString();
psmodel.PhoneNo = dr["PhoneNo"].ToString();
psmodel.EmailId = dr["EmailId"].ToString();
psmodel.StateName = dr["StateName"].ToString();
psmodel.CityName = dr["CityName"].ToString();
psmodel.Address = dr["Address"].ToString();
psmodel.Message = dr["Message"].ToString();
psmodel.HavingPets = dr["HavingPets"].ToString();
//psmodel.UnitID = dr["UnitID"].ToString();
psmodel.CreatedDate = dr["CreatedDate"].ToString();
psmodel.VisitDateTime = dr["VisitDateTime"].ToString();
psmodel.AssignAgentID = Convert.ToInt64(dr["AssignAgentId"].ToString());
psmodel.AppointmentStatusString = dr["AppointmentStatus"].ToString();
var salesAgentName = db.tbl_Login.Where(co => co.UserID == psmodel.AssignAgentID).FirstOrDefault();
if (salesAgentName != null)
{
psmodel.AssignAgentName = salesAgentName.FirstName + " " + salesAgentName.LastName;
}
else
{
psmodel.AssignAgentName = "Agent not yet assign";
}
lstProspect.Add(psmodel);
}
db.Dispose();
return(lstProspect.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public int BuildPaganationProspectList(ProspectSearchModel model)
{
int NOP = 0;
ShomaRMEntities db = new ShomaRMEntities();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetProspectPaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param0 = cmd.CreateParameter();
param0.ParameterName = "FromDate";
param0.Value = model.FromDate;
cmd.Parameters.Add(param0);
DbParameter param1 = cmd.CreateParameter();
param1.ParameterName = "ToDate";
param1.Value = model.ToDate;
cmd.Parameters.Add(param1);
DbParameter param3 = cmd.CreateParameter();
param3.ParameterName = "PageNumber";
param3.Value = model.PageNumber;
cmd.Parameters.Add(param3);
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "NumberOfRows";
param4.Value = model.NumberOfRows;
cmd.Parameters.Add(param4);
DbParameter param5 = cmd.CreateParameter();
param5.ParameterName = "SortBy";
param5.Value = model.SortBy;
cmd.Parameters.Add(param5);
DbParameter param6 = cmd.CreateParameter();
param6.ParameterName = "OrderBy";
param6.Value = model.OrderBy;
cmd.Parameters.Add(param6);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
if (dtTable.Rows.Count > 0)
{
NOP = int.Parse(dtTable.Rows[0]["NumberOfPages"].ToString());
}
db.Dispose();
return(NOP);
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}