public int BuildPaganationUtilityList(UtilityList model) { int NOP = 0; ShomaRMEntities db = new ShomaRMEntities(); List <UtilityList> lstUtility = new List <UtilityList>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetUtilityPaginationAndSearchData"; 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 <UtilityList> GetUtilityList(UtilityList model) { ShomaRMEntities db = new ShomaRMEntities(); List <UtilityList> lstUtility = new List <UtilityList>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetUtilityPaginationAndSearchData"; 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) { UtilityList usm = new UtilityList(); usm.UtilityID = int.Parse(dr["UtilityID"].ToString()); usm.UtilityTitle = dr["UtilityTitle"].ToString(); usm.UtilityDetails = dr["UtilityDetails"].ToString(); usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString()); lstUtility.Add(usm); } db.Dispose(); return(lstUtility.ToList()); } catch (Exception ex) { db.Database.Connection.Close(); throw ex; } }