public int BuildPaganationPromotionList(PromotionListModel 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_GetPromotionPaginationAndSearchData"; cmd.CommandType = CommandType.StoredProcedure; DbParameter paramSD = cmd.CreateParameter(); paramSD.ParameterName = "StartDate"; paramSD.Value = !string.IsNullOrEmpty(model.StartDate) ? model.StartDate : DateTime.Now.ToString("MM/dd/yyyy"); cmd.Parameters.Add(paramSD); DbParameter paramED = cmd.CreateParameter(); paramED.ParameterName = "EndDate"; paramED.Value = !string.IsNullOrEmpty(model.EndDate)?model.EndDate:DateTime.Now.ToString("MM/dd/yyyy"); cmd.Parameters.Add(paramED); 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 == 0?25:model.NumberOfRows; cmd.Parameters.Add(paramNOR); 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 <PromotionListModel> FillPromotionSearchGrid(PromotionListModel model) { ShomaRMEntities db = new ShomaRMEntities(); List <PromotionListModel> lstData = new List <PromotionListModel>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetPromotionPaginationAndSearchData"; cmd.CommandType = CommandType.StoredProcedure; DbParameter paramSD = cmd.CreateParameter(); paramSD.ParameterName = "StartDate"; paramSD.Value = !string.IsNullOrEmpty(model.StartDate) ? model.StartDate : DateTime.Now.ToString("MM/dd/yyyy"); cmd.Parameters.Add(paramSD); DbParameter paramED = cmd.CreateParameter(); paramED.ParameterName = "EndDate"; paramED.Value = !string.IsNullOrEmpty(model.EndDate) ? model.EndDate : DateTime.Now.ToString("MM/dd/yyyy"); cmd.Parameters.Add(paramED); 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); DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(dtTable); db.Database.Connection.Close(); } foreach (DataRow dr in dtTable.Rows) { PromotionListModel searchmodel = new PromotionListModel(); DateTime?startDate = null; try { startDate = Convert.ToDateTime(dr["StartDate"].ToString()); } catch { } DateTime?endDate = null; try { endDate = Convert.ToDateTime(dr["EndDate"].ToString()); } catch { } searchmodel.PromotionID = Convert.ToInt32(dr["PromotionID"].ToString()); searchmodel.PropertyID = long.Parse(dr["PropertyID"].ToString()); searchmodel.PropertyName = dr["PropertyName"].ToString(); searchmodel.PromotionTitle = dr["PromotionTitle"].ToString(); searchmodel.StartDate = startDate == null ? "" : startDate.Value.ToString("MM/dd/yyyy"); searchmodel.EndDate = endDate == null ? "" : endDate.Value.ToString("MM/dd/yyyy"); lstData.Add(searchmodel); } db.Dispose(); return(lstData.ToList()); } catch (Exception ex) { db.Database.Connection.Close(); throw ex; } }