public int BuildPaganationStateList(StateList model)
{
int NOP = 0;
ShomaRMEntities db = new ShomaRMEntities();
List <StateList> lstState = new List <StateList>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetStatePaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter paramC = cmd.CreateParameter();
paramC.ParameterName = "Criteria";
paramC.Value = model.Criteria;
cmd.Parameters.Add(paramC);
DbParameter paramPN = cmd.CreateParameter();
paramPN.ParameterName = "PageNumber";
paramPN.Value = model.PageNumber;
cmd.Parameters.Add(paramPN);
DbParameter paramNOR = cmd.CreateParameter();
paramNOR.ParameterName = "NumberOfRows";
paramNOR.Value = model.NumberOfRows;
cmd.Parameters.Add(paramNOR);
DbParameter paramSortBy = cmd.CreateParameter();
paramSortBy.ParameterName = "SortBy";
paramSortBy.Value = model.SortBy;
cmd.Parameters.Add(paramSortBy);
DbParameter paramOrderBy = cmd.CreateParameter();
paramOrderBy.ParameterName = "OrderBy";
paramOrderBy.Value = model.OrderBy;
cmd.Parameters.Add(paramOrderBy);
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;
}
}
public List <StateList> FillStateDropDownList()
{
ShomaRMEntities db = new ShomaRMEntities();
List <StateList> model = new List <StateList>();
var stateData = db.tbl_State.OrderBy(p => p.StateName);
foreach (var state in stateData)
{
StateList sl = new StateList();
sl.StateName = state.StateName;
sl.ID = state.ID;
model.Add(sl);
}
db.Dispose();
return(model.ToList());
}
public List <StateList> FillStateDropDownListByCountryID(long CID)
{
ShomaRMEntities db = new ShomaRMEntities();
List <StateList> lstData = new List <StateList>();
DataTable dtTable = new DataTable();
try
{
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_FillStateDropDownListByCountryID";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter param4 = cmd.CreateParameter();
param4.ParameterName = "CID";
param4.Value = CID;
cmd.Parameters.Add(param4);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (System.Data.DataRow dr in dtTable.Rows)
{
StateList model = new StateList();
model.ID = Convert.ToInt64(dr["ID"].ToString());
model.StateName = dr["StateName"].ToString();
lstData.Add(model);
}
db.Dispose();
return(lstData.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}
public List <StateList> GetStateList(StateList model)
{
ShomaRMEntities db = new ShomaRMEntities();
List <StateList> lstState = new List <StateList>();
try
{
DataTable dtTable = new DataTable();
using (var cmd = db.Database.Connection.CreateCommand())
{
db.Database.Connection.Open();
cmd.CommandText = "usp_GetStatePaginationAndSearchData";
cmd.CommandType = CommandType.StoredProcedure;
DbParameter paramC = cmd.CreateParameter();
paramC.ParameterName = "Criteria";
paramC.Value = model.Criteria;
cmd.Parameters.Add(paramC);
DbParameter paramPN = cmd.CreateParameter();
paramPN.ParameterName = "PageNumber";
paramPN.Value = model.PageNumber;
cmd.Parameters.Add(paramPN);
DbParameter paramNOR = cmd.CreateParameter();
paramNOR.ParameterName = "NumberOfRows";
paramNOR.Value = model.NumberOfRows;
cmd.Parameters.Add(paramNOR);
DbParameter paramSortBy = cmd.CreateParameter();
paramSortBy.ParameterName = "SortBy";
paramSortBy.Value = model.SortBy;
cmd.Parameters.Add(paramSortBy);
DbParameter paramOrderBy = cmd.CreateParameter();
paramOrderBy.ParameterName = "OrderBy";
paramOrderBy.Value = model.OrderBy;
cmd.Parameters.Add(paramOrderBy);
DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dtTable);
db.Database.Connection.Close();
}
foreach (DataRow dr in dtTable.Rows)
{
StateList usm = new StateList();
usm.ID = int.Parse(dr["ID"].ToString());
usm.StateName = dr["StateName"].ToString();
usm.Abbreviation = dr["Abbreviation"].ToString();
usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString());
lstState.Add(usm);
}
db.Dispose();
return(lstState.ToList());
}
catch (Exception ex)
{
db.Database.Connection.Close();
throw ex;
}
}