public ACRF_CountryModel GetOneCountry(int Id) { ACRF_CountryModel objList = new ACRF_CountryModel(); try { string sqlstr = "select Id, isnull(country,'') as Country, isnull(CreatedBy,'') as CreatedBy, isnull(createdon,'') as CreatedOn, " + " isnull(updatedby,'') as updatedby, isnull(updatedon,'') as updatedon from ACRF_Country where Id=@Id"; var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = new SqlCommand(sqlstr, connection); cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@Id", Id); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { objList.Id = Convert.ToInt32(sdr["Id"].ToString()); objList.Country = sdr["Country"].ToString(); objList.CreatedBy = sdr["CreatedBy"].ToString(); objList.CreatedOn = Convert.ToDateTime(sdr["CreatedOn"].ToString()); } sdr.Close(); connection.Close(); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(objList); }
public List <ACRF_CountryModel> ListCountry() { List <ACRF_CountryModel> objList = new List <ACRF_CountryModel>(); try { string sqlstr = "select Id, isnull(country,'') as Country, isnull(CreatedBy,'') as CreatedBy, isnull(createdon,'') as CreatedOn, " + " isnull(updatedby,'') as updatedby, isnull(updatedon,'') as updatedon from ACRF_Country order by Id"; var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = new SqlCommand(sqlstr, connection); cmd.CommandType = System.Data.CommandType.Text; SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { ACRF_CountryModel tempobj = new ACRF_CountryModel(); tempobj.Id = Convert.ToInt32(sdr["Id"].ToString()); tempobj.Country = sdr["Country"].ToString(); tempobj.CreatedBy = sdr["CreatedBy"].ToString(); tempobj.CreatedOn = Convert.ToDateTime(sdr["CreatedOn"].ToString()); objList.Add(tempobj); } sdr.Close(); connection.Close(); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(objList); }
public string CheckIfCountryExists(ACRF_CountryModel objModel) { string result = ""; try { string sqlstr = "Select * from ACRF_Country Where ISNULL(Country,'')=@Country and Isnull(Id,0)!=@Id "; var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = new SqlCommand(sqlstr, connection); cmd.Parameters.AddWithValue("@Country", objModel.Country); cmd.Parameters.AddWithValue("@Id", objModel.Id); SqlDataReader sdr = cmd.ExecuteReader(); if (objModel.Country != "") { while (sdr.Read()) { result = "Country already exists!"; } } sdr.Close(); connection.Close(); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(result); }
public string UpdateCountry(ACRF_CountryModel objModel) { string result = "Error on Updating Country!"; try { result = CheckIfCountryExists(objModel); if (result == "") { var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = connection.CreateCommand(); SqlTransaction transaction; transaction = connection.BeginTransaction(); cmd.Transaction = transaction; cmd.Connection = connection; try { string sqlstr = ""; sqlstr = "update ACRF_Country set Country=@Country, UpdatedBy=@UpdatedBy,UpdatedOn=@UpdatedOn where Id=@Id"; cmd.CommandText = sqlstr; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Country", objModel.Country); cmd.Parameters.AddWithValue("@Id", objModel.Id); cmd.Parameters.AddWithValue("@UpdatedBy", objModel.UpdatedBy); cmd.Parameters.AddWithValue("@UpdatedOn", StandardDateTime.GetDateTime()); cmd.ExecuteNonQuery(); transaction.Commit(); connection.Close(); result = "Country Updated Successfully!"; } catch (Exception ex) { transaction.Rollback(); connection.Close(); Global.ErrorHandlerClass.LogError(ex); result = ex.Message; } } else { return(result); } } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(result); }
public IHttpActionResult ViewOneCountry(int Id) { ACRF_CountryModel objList = new ACRF_CountryModel(); try { objList = objCountryVM.GetOneCountry(Id); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(Ok(new { results = objList })); }
public IHttpActionResult AddCountry(ACRF_CountryModel objModel) { string result = ""; if (ModelState.IsValid) { try { objModel.CreatedBy = GlobalFunction.getLoggedInUser(Request.Headers.GetValues("Token").First()); result = objCountryVM.CreateCountry(objModel); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); result = ex.Message; } } else { result = "Enter Valid Mandatory Fields"; } return(Ok(new { results = result })); }
public Paged_CountryModel ListCountry(int max, int page, string search, string sort_col, string sort_dir) { Paged_CountryModel objPaged = new Paged_CountryModel(); List <ACRF_CountryModel> objList = new List <ACRF_CountryModel>(); try { if (search == null) { search = ""; } int startIndex = max * (page - 1); string sqlstr = "ACRF_GetCountryByPage"; var connection = gConnection.Connection(); connection.Open(); SqlCommand cmd = new SqlCommand(sqlstr, connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@startRowIndex", startIndex); cmd.Parameters.AddWithValue("@pageSize", max); cmd.Parameters.AddWithValue("@search", search); cmd.Parameters.AddWithValue("@sort_col", sort_col); cmd.Parameters.AddWithValue("@sort_dir", sort_dir); SqlDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { ACRF_CountryModel tempobj = new ACRF_CountryModel(); tempobj.Id = Convert.ToInt32(sdr["Id"].ToString()); tempobj.Country = sdr["Country"].ToString(); tempobj.CreatedBy = sdr["CreatedBy"].ToString(); tempobj.CreatedOn = Convert.ToDateTime(sdr["CreatedOn"].ToString()); objList.Add(tempobj); } sdr.Close(); objPaged.ACRF_CountryModelList = objList; sqlstr = "select count(*) as cnt from ACRF_Country where country like @search "; cmd.Parameters.Clear(); cmd.CommandText = sqlstr; cmd.Connection = connection; cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@search", '%' + @search + '%'); sdr = cmd.ExecuteReader(); while (sdr.Read()) { objPaged.PageCount = Convert.ToInt32(sdr["cnt"].ToString()); } connection.Close(); } catch (Exception ex) { ErrorHandlerClass.LogError(ex); } return(objPaged); }