public int BuildPaganationCityList(CityList model) { int NOP = 0; ShomaRMEntities db = new ShomaRMEntities(); List <CityList> lstCity = new List <CityList>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetCityPaginationAndSearchData"; 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 <CityList> GetCityListbyState(int StateID) { ShomaRMEntities db = new ShomaRMEntities(); List <CityList> model = new List <CityList>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetCityListbyState"; cmd.CommandType = CommandType.StoredProcedure; DbParameter paramC = cmd.CreateParameter(); paramC.ParameterName = "StateID"; paramC.Value = StateID; cmd.Parameters.Add(paramC); DbDataAdapter da = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(dtTable); db.Database.Connection.Close(); } foreach (DataRow dr in dtTable.Rows) { CityList usm = new CityList(); usm.ID = int.Parse(dr["ID"].ToString()); usm.CityName = dr["CityName"].ToString(); model.Add(usm); } db.Dispose(); return(model.ToList()); } catch (Exception ex) { db.Database.Connection.Close(); throw ex; } }
public List <CityList> GetCityList(CityList model) { ShomaRMEntities db = new ShomaRMEntities(); List <CityList> lstCity = new List <CityList>(); try { DataTable dtTable = new DataTable(); using (var cmd = db.Database.Connection.CreateCommand()) { db.Database.Connection.Open(); cmd.CommandText = "usp_GetCityPaginationAndSearchData"; 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) { CityList usm = new CityList(); usm.ID = int.Parse(dr["ID"].ToString()); usm.CityName = dr["CityName"].ToString(); usm.StateName = dr["StateName"].ToString(); usm.NumberOfPages = int.Parse(dr["NumberOfPages"].ToString()); lstCity.Add(usm); } db.Dispose(); return(lstCity.ToList()); } catch (Exception ex) { db.Database.Connection.Close(); throw ex; } }