public int BuildPaganationStorageList(FOBListModel 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_GetFOBPaginationAndSearchData"; 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 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; } }
public List <FOBListModel> FillStorageSearchGrid(FOBListModel model) { ShomaRMEntities db = new ShomaRMEntities(); List <FOBListModel> lstData = new List <FOBListModel>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetFOBPaginationAndSearchData"; 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 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) { FOBListModel usm = new FOBListModel(); usm.StorageID = int.Parse(dr["StorageID"].ToString()); usm.StorageName = dr["StorageName"].ToString(); usm.PropertyID = long.Parse(dr["PropertyID"].ToString()); usm.Charges = decimal.Parse(dr["Charges"].ToString()); usm.Description = dr["Description"].ToString(); usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString()); lstData.Add(usm); } db.Dispose(); return(lstData.ToList()); } catch (Exception ex) { db.Database.Connection.Close(); throw ex; } }