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