public List <CodeDetail> GetCodeDetail(QueryBO objQuery) { try { lstObject = new List <CodeDetail>(); using (con = DBconnection.ConnectToDB()) { objcmd = new SqlCommand(); objcmd.Connection = con; objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_SelectCodeDetail"; objcmd.Parameters.Add(new SqlParameter("@PageNumber", objQuery.PageNumber)); objcmd.Parameters.Add(new SqlParameter("@PageSize", objQuery.PageSize)); objcmd.Parameters.Add(new SqlParameter("@Filter", objQuery.Filter)); objcmd.Parameters.Add(new SqlParameter("@Sort", objQuery.Sort)); objcmd.Parameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int)).Direction = ParameterDirection.Output; reader = objcmd.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { obj = new CodeDetail(); obj.CodeDetailId = Convert.ToInt64("0" + reader["CodeDetailId"]); obj.CodeTypeId = Convert.ToInt32("0" + reader["CodeTypeId"]); obj.DetailLongDesc = Convert.ToString("" + reader["DetailLongDesc"]); obj.DetailShortDesc = Convert.ToString("" + reader["DetailShortDesc"]); obj.ParentId = Convert.ToInt64("0" + reader["ParentId"]); obj.Status = Convert.ToBoolean(reader["Status"]); lstObject.Add(obj); } } } objQuery.TotalRecords = Convert.ToInt32(objcmd.Parameters["@TotalRecords"].Value); } catch (Exception ex) { throw ex; } finally { reader.Dispose(); objcmd = null; con = null; } return(lstObject); }
public static List <CityMaster> GetCity(int countryID, int StateID) { string res = ""; SqlDataReader reader = null; SqlConnection con = null; List <CityMaster> lstCity = null; SqlCommand objcmd = null; CityMaster objCity = null; try { lstCity = new List <CityMaster>(); using (con = DBconnection.ConnectToDB()) { objcmd = new SqlCommand(); objcmd.Connection = con; objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_GetCityDtls"; reader = objcmd.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { objCity = new CityMaster(); countryID = Convert.ToInt32(reader["countryID"]); StateID = Convert.ToInt32(reader["StateID"]); objCity.CityID = Convert.ToInt64(reader["CityID"]); objCity.CityName = Convert.ToString("" + reader["StateName"]); lstCity.Add(objCity); } } } } catch (Exception) { throw; } finally { reader.Dispose(); objcmd = null; con = null; } return(lstCity); }
public int Save(User user, string CRUDAction, out string ErrorCode) { int result = 0; ErrorCode = "-1"; using (SqlCommand objcmd = new SqlCommand()) { try { objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_InsertUpdateUserMaster"; objcmd.Parameters.Add(new SqlParameter("@UserId", user.UserId)).Direction = ParameterDirection.InputOutput; objcmd.Parameters.Add(new SqlParameter("@FirstName", user.FirstName)); objcmd.Parameters.Add(new SqlParameter("@LastName", user.LastName)); objcmd.Parameters.Add(new SqlParameter("@Mobile", user.Mobile)); objcmd.Parameters.Add(new SqlParameter("@Email", user.Email)); objcmd.Parameters.Add(new SqlParameter("@Password", user.Password)); objcmd.Parameters.Add(new SqlParameter("@ImageUrl", user.ImageUrl)); objcmd.Parameters.Add(new SqlParameter("@WorkingSince", user.WorkingSince)); objcmd.Parameters.Add(new SqlParameter("@WorkingSinceWithUs", user.WorkingSinceWithUs)); objcmd.Parameters.Add(new SqlParameter("@Status", user.Status)); objcmd.Parameters.Add(new SqlParameter("@IsActiveOnSite", user.IsActiveOnSite)); objcmd.Parameters.Add(new SqlParameter("@UserType", user.UserType)); objcmd.Parameters.Add(new SqlParameter("@CompanyID", user.CompanyId)); objcmd.Parameters.Add(new SqlParameter("@CreatedBy", user.CreatedBy)); objcmd.Parameters.Add(new SqlParameter("@ModifiedBy", user.ModifiedBy)); objcmd.Parameters.Add(new SqlParameter("@CRUDAction", CRUDAction)); objcmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.VarChar, 20)).Direction = ParameterDirection.Output; result = DBconnection.ExecuteNonQuery(objcmd); user.UserId = Convert.ToInt64("0" + objcmd.Parameters["@UserId"].Value); ErrorCode = Convert.ToString(objcmd.Parameters["@ErrorCode"].Value); } catch (Exception ex) { throw ex; } finally { objcmd.Dispose(); } } return(result); }
public List <CityMaster> GetCityList(TableFilter objQuery) { List <CityMaster> lstObject = null; CityMaster obj = null; try { lstObject = new List <CityMaster>(); using (con = DBconnection.ConnectToDB()) { objcmd = new SqlCommand(); objcmd.Connection = con; reader = sdr(objcmd, objQuery); if (reader != null && reader.HasRows) { while (reader.Read()) { obj = new CityMaster(); obj.CityID = Convert.ToInt64("0" + reader["CityID"]); obj.CityName = Convert.ToString("" + reader["CityName"]); lstObject.Add(obj); } } } // objQuery.TotalRecords = Convert.ToInt32(objcmd.Parameters["@TotalRecords"].Value); } catch (Exception ex) { throw ex; } finally { reader.Dispose(); objcmd = null; con = null; } return(lstObject); }
public int Save(AreaMaster area, string CRUDAction, out string ErrorCode) { int result = 0; ErrorCode = "-1"; using (SqlCommand objcmd = new SqlCommand()) { try { objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_InsertUpdateAreaMaster"; objcmd.Parameters.Add(new SqlParameter("@AreaID", area.AreaID)).Direction = ParameterDirection.InputOutput; objcmd.Parameters.Add(new SqlParameter("@AreaName", area.AreaName)); objcmd.Parameters.Add(new SqlParameter("@CityID", area.CityID)); objcmd.Parameters.Add(new SqlParameter("@CountryID", area.CountryID)); objcmd.Parameters.Add(new SqlParameter("@StateID", area.StateID)); objcmd.Parameters.Add(new SqlParameter("@Status", area.Status)); objcmd.Parameters.Add(new SqlParameter("@Remarks", area.Remarks)); objcmd.Parameters.Add(new SqlParameter("@CreatedBy", area.CreatedBy)); objcmd.Parameters.Add(new SqlParameter("@ModifiedBy", area.ModifiedBy)); objcmd.Parameters.Add(new SqlParameter("@CRUDAction", CRUDAction)); objcmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.VarChar, 20)).Direction = ParameterDirection.Output; result = DBconnection.ExecuteNonQuery(objcmd); area.AreaID = Convert.ToInt64("0" + objcmd.Parameters["@AreaID"].Value); ErrorCode = Convert.ToString(objcmd.Parameters["@ErrorCode"].Value); } catch (Exception ex) { throw ex; } finally { objcmd.Dispose(); } } return(result); }
public List <User> Get(QueryBO objQuery) { SqlDataReader reader = null; SqlConnection con = null; List <User> lstUsers = null; SqlCommand objcmd = null; User objUser = null; try { lstUsers = new List <User>(); using (con = DBconnection.ConnectToDB()) { objcmd = new SqlCommand(); objcmd.Connection = con; objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_SelectUserMaster"; objcmd.Parameters.Add(new SqlParameter("@PageNumber", objQuery.PageNumber)); objcmd.Parameters.Add(new SqlParameter("@PageSize", objQuery.PageSize)); objcmd.Parameters.Add(new SqlParameter("@Filter", objQuery.Filter)); objcmd.Parameters.Add(new SqlParameter("@Sort", objQuery.Sort)); objcmd.Parameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int)).Direction = ParameterDirection.Output; reader = objcmd.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { objUser = new User(); objUser.UserId = Convert.ToInt64(reader["UserId"]); objUser.FirstName = Convert.ToString("" + reader["FirstName"]); objUser.LastName = Convert.ToString("" + reader["LastName"]); objUser.FullName = Convert.ToString("" + reader["FullName"]); objUser.Mobile = Convert.ToString("" + reader["Mobile"]); objUser.Email = Convert.ToString("" + reader["Email"]); objUser.Password = Convert.ToString("" + reader["Password"]); objUser.WorkingSince = reader["WorkingSince"] == DBNull.Value ? (DateTime?)null : (DateTime)reader["WorkingSince"]; objUser.WorkingSinceWithUs = reader["WorkingSinceWithUs"] == DBNull.Value ? (DateTime?)null : (DateTime)reader["WorkingSinceWithUs"]; //Convert.ToDateTime(reader["WorkingSince"]); //objUser.WorkingSinceWithUs = Convert.ToDateTime(reader["WorkingSinceWithUs"]); objUser.Status = Convert.ToInt32("0" + reader["Status"]); if (!string.IsNullOrEmpty(Convert.ToString(reader["IsActiveOnSite"]))) { objUser.IsActiveOnSite = Convert.ToBoolean(reader["IsActiveOnSite"]); } objUser.UserType = Convert.ToInt16("0" + reader["UserType"]); objUser.strStatus = Convert.ToString("" + reader["strStatus"]); objUser.strUserType = Convert.ToString("" + reader["strUserType"]); objUser.ImageUrl = Convert.ToString("" + reader["ImageUrl"]); objUser.CompanyId = Convert.ToInt64("0" + reader["CompanyId"]); lstUsers.Add(objUser); } } } objQuery.TotalRecords = Convert.ToInt32(objcmd.Parameters["@TotalRecords"].Value); } catch (Exception ex) { throw ex; } finally { reader.Dispose(); objcmd = null; con = null; } return(lstUsers); }
public List <T> GetTable <T>(TableFilter objFilter) { List <object> lstObject = null; try { lstObject = new List <object>(); using (con = DBconnection.ConnectToDB()) { objcmd = new SqlCommand(); objcmd.Connection = con; objcmd.CommandType = CommandType.StoredProcedure; objcmd.CommandText = "STP_Select_BindDropdown"; objcmd.Parameters.Add(new SqlParameter("@Table", objFilter.TableName)); objcmd.Parameters.Add(new SqlParameter("@IDColumn", objFilter.IdColumn)); objcmd.Parameters.Add(new SqlParameter("@TextColumn", objFilter.TextColumn)); objcmd.Parameters.Add(new SqlParameter("@Condition", objFilter.Condition)); reader = objcmd.ExecuteReader(); if (reader != null && reader.HasRows) { switch (typeof(T).Name.ToLower()) { case "countrymaster": { while (reader.Read()) { CountryMaster obj = new CountryMaster(); obj.CountryID = Convert.ToInt64("0" + reader["CountryID"]); obj.CountryName = Convert.ToString("" + reader["CountryName"]); lstObject.Add(obj); } break; } case "statemaster": { while (reader.Read()) { StateMaster obj = new StateMaster(); //obj.CountryId = Convert.ToInt32("0" + reader["CountryID"]); obj.StateID = Convert.ToInt64("0" + reader["StateId"]); obj.StateName = Convert.ToString("" + reader["StateName"]); lstObject.Add(obj); } break; } case "citymaster": { while (reader.Read()) { CityMaster obj = new CityMaster(); obj.CityID = Convert.ToInt64("0" + reader["CityID"]); obj.CityName = Convert.ToString("" + reader["CityName"]); lstObject.Add(obj); } break; } case "areamaster": { while (reader.Read()) { AreaMaster obj = new AreaMaster(); obj.AreaID = Convert.ToInt64("0" + reader["Area_ID"]); obj.AreaName = Convert.ToString("" + reader["Area_Name"]); lstObject.Add(obj); } break; } } } } } catch (Exception ex) { throw ex; } finally { reader.Dispose(); objcmd = null; con = null; } return(lstObject.OfType <T>().ToList()); }