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; } }