public int UpdateQryRetRows(string sqlStr, MySqlParameter[] commandParameters)
{
con = new MySqlConnection(strConn);
int intRetRows = 0;
try
{
cmd = new MySqlCommand(sqlStr, con);
con.Open();
foreach (MySqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
intRetRows = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
return(intRetRows);
}
/// <summary>
/// This method calls stored procedure getallservices to get all available services.
/// </summary>
/// <returns></returns>
public List <Service> GetAllServices()
{
var serviceList = new List <Service>();
try
{
using (MySqlCommand cmd = new MySqlCommand("getallservices"))
{
cmd.CommandType = CommandType.StoredProcedure;
Database db = new Database();
using (MySqlDataReader dr = db.SelectQry(cmd))
{
while (dr.Read())
{
serviceList.Add(new Service()
{
serviceName = dr["serviceName"].ToString(),
serviceDesc = dr["servicesDesc"].ToString(),
serviceStatus = Int32.Parse(dr["IsActive"].ToString()),
id = Int32.Parse(dr["id"].ToString()),
});
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(serviceList);
}
public MySqlDataReader SelectQry(MySqlCommand cmdParam)
{
MySqlDataReader dataReader = null;
try
{
using (con = new MySqlConnection(strConn))
{
con = new MySqlConnection(strConn);
cmdParam.Connection = con;
con.Open();
dataReader = cmdParam.ExecuteReader(CommandBehavior.CloseConnection);
cmdParam.Parameters.Clear();
}
}
catch (Exception err)
{
cmdParam.Parameters.Clear();
CloseConnection();
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
return(dataReader);
}
public string ExecuteScalar(string strSql)
{
string val = "";
MySqlConnection con;
MySqlCommand cmd;
string conStr = GetConString();
con = new MySqlConnection(conStr);
try
{
con.Open();
cmd = new MySqlCommand(strSql, con);
val = Convert.ToString(cmd.ExecuteScalar());
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
CloseConnection();
}
return(val);
}
public bool DeleteQry(string strSql)
{
bool result = false;
int intRetRows;
con = new MySqlConnection(strConn);
try
{
cmd = new MySqlCommand(strSql, con);
con.Open();
intRetRows = cmd.ExecuteNonQuery();
if (intRetRows > 0)
{
result = true;
}
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
CloseConnection();
}
return(result);
}
public MySqlDataReader SelectQry(string sqlStr, MySqlParameter[] commandParameters)
{
MySqlDataReader dataReader = null;
con = new MySqlConnection(strConn);
try
{
con.Open();
cmd = new MySqlCommand(sqlStr, con);
foreach (MySqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
cmd.Parameters.Clear();
//CloseConnection();
}
return(dataReader);
}
public bool DeleteQry(MySqlCommand cmdParam)
{
con = new MySqlConnection(strConn);
bool result = false;
try
{
cmdParam.Connection = con;
con.Open();
int retval = cmdParam.ExecuteNonQuery();
if (retval > 0)
{
result = true;
}
else
{
result = false;
}
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
cmdParam.Parameters.Clear();
CloseConnection();
}
return(result);
}
public string GetInClauseValue(string input, string fieldName, out MySqlParameter[] commandParameters)
{
string[] inputArr = input.Split(',');
string[] parameters = new string[inputArr.Length];
commandParameters = null;
try
{
commandParameters = new MySqlParameter[inputArr.Length];
for (int i = 0; i < inputArr.Length; i++)
{
parameters[i] = "@" + fieldName + i;
commandParameters[i] = new MySqlParameter(parameters[i], inputArr[i]);
HttpContext.Current.Trace.Warn(parameters[i].ToString() + " : " + inputArr[i].ToString());
}
HttpContext.Current.Trace.Warn(commandParameters.Length.ToString());
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
return(string.Join(",", parameters));
}
public string ExecuteScalar(string strSql, MySqlParameter[] commandParameters)
{
string val = "";
con = new MySqlConnection(strConn);
try
{
cmd = new MySqlCommand(strSql, con);
con.Open();
foreach (MySqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
val = Convert.ToString(cmd.ExecuteScalar());
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
return(val);
}
public DataSet SelectAdaptQry(string strSql)
{
DataSet dataSet = new DataSet();
con = new MySqlConnection(strConn);
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(strSql, con);
adapter.Fill(dataSet);
}
catch (MySqlException exSql)
{
dataSet = null;
var objErr = new ErrorClass(exSql, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
CloseConnection();
}
return(dataSet);
}
/// <summary>
/// similar to method SelectAdaptQry(MySqlCommand cmdParam) but the parameters of the input MySqlCommand remain intact
/// </summary>
public DataSet SelectAdaptQryParamNC(MySqlCommand cmdParam)
{
con = new MySqlConnection(strConn);
DataSet dataSet = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter();
try
{
cmdParam.Connection = con;
//con.Open();
adapter.SelectCommand = cmdParam;
adapter.Fill(dataSet);
}
catch (MySqlException exSql)
{
dataSet = null;
var objErr = new ErrorClass(exSql, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
catch (Exception err)
{
HttpContext.Current.Trace.Warn(err.Message + err.Source);
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
CloseConnection();
}
return(dataSet);
}
//Added By Ranjeet ||For async || Used for Used car search
public async Task <DataTable> AsyncGetDataTable(MySqlCommand cmd)
{
DataTable dt = new DataTable();
var asyncConnectionString = new MySqlConnectionStringBuilder(ConfigurationManager.AppSettings["connectionString"])
{
//AsynchronousProcessing = true
}.ToString();
using (var conn = new MySqlConnection(asyncConnectionString))
{
try
{
Static_count += 1;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
await cmd.Connection.OpenAsync().ConfigureAwait(false);
using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
//Thread.Sleep(2000);
dt.Load(reader);
}
cmd.Connection.Close();
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "AsyncDb sql: ");
objErr.LogException();
}
}
return(dt);
}
/// <summary>
/// This method calls stored procedure getrequestbyfilters to get all service requests based on filters.
/// </summary>
/// <param name="caregiverId"></param>
/// <param name="requesterId"></param>
/// <param name="status"></param>
/// <returns>List of Service Request</returns>
public List <Request> getRequestsByFilters(int caregiverId, int requesterId, string status)
{
var requestList = new List <Request>();
try
{
using (MySqlCommand cmd = new MySqlCommand("getrequestbyfilters"))
{
string[] statusArr = status.Split(',');
for (int i = 0; i < statusArr.Length; i++)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_CaregiverId", caregiverId);
cmd.Parameters.AddWithValue("_requesterId", requesterId);
cmd.Parameters.AddWithValue("_status", statusArr[i]);
Database db = new Database();
using (MySqlDataReader dr = db.SelectQry(cmd))
{
while (dr.Read())
{
requestList.Add(new Request()
{
id = Int32.Parse(dr["id"].ToString()),
RequesterId = Int32.Parse(dr["RequesterId"].ToString()),
RoleId = Int32.Parse(dr["RoleId"].ToString()),
CaregiverId = Int32.Parse(dr["CaregiverId"].ToString()),
ServiceId = Int32.Parse(dr["ServiceId"].ToString()),
Status = dr["Status"].ToString(),
ScheduleDate = dr["ScheduleDate"].ToString(),
StartTime = dr["StartTime"].ToString(),
EndTime = dr["EndTime"].ToString(),
Comments = dr["Comments"].ToString(),
ModifiedBy = Int32.Parse(dr["ModifiedBy"].ToString()),
ModifiedOn = Convert.ToDateTime(dr["ModifiedOn"].ToString()),
Address = dr["Address"].ToString(),
serviceName = dr["serviceName"].ToString(),
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString(),
RequesterName = dr["FirstName"].ToString() + " " + dr["LastName"].ToString()
});
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(requestList);
}
/// <summary>
/// This method calls stored procedure userlogin to validate user credentials.
/// </summary>
/// <param name="usercr"></param>
/// <returns>User</returns>
public User userLogin(Credential usercr)
{
try
{
User user;
string Username = usercr.UserName;
string LoginPassword = usercr.LoginPassword;
using (MySqlCommand cmd = new MySqlCommand("userlogin"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_username", Username);
cmd.Parameters.AddWithValue("_password", LoginPassword);
Database db = new Database();
using (MySqlDataReader dr = db.SelectQry(cmd))
{
if (dr.Read())
{
user = new User()
{
UserRoleId = Int32.Parse(dr["UserRoleId"].ToString()),
idUser = Int32.Parse(dr["idUser"].ToString()),
UserName = dr["UserName"].ToString(),
UserEmail = dr["UserEmail"].ToString(),
UserMobile = dr["UserMobile"].ToString(),
UserSSN = dr["UserSSN"].ToString(),
CreatedOn = Convert.ToDateTime(dr["CreatedOn"].ToString()),
CreatedBy = Int32.Parse(dr["CreatedBy"].ToString()),
ModifiedOn = Convert.ToDateTime(dr["ModifiedOn"].ToString()),
ModifiedBy = Int32.Parse(dr["ModifiedBy"].ToString()),
IsActive = Int32.Parse(dr["IsActive"].ToString()),
Address = dr["Address"].ToString(),
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString(),
Sex = dr["Sex"].ToString(),
};
return(user);
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(null);
}
public void CloseConnection()
{
try
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
}
/// <summary>
/// This method calls stored procedure insert_update_request to add a new request or update an existiing request.
/// </summary>
/// <param name="requestDetails"></param>
/// <returns>boolean</returns>
public Boolean addRequest(Request requestDetails)
{
try
{
int id = requestDetails.id;
int RequesterId = requestDetails.RequesterId;
int RoleId = requestDetails.RoleId;
int CaregiverId = requestDetails.CaregiverId;
int ServiceId = requestDetails.ServiceId;
string Status = requestDetails.Status;
string ScheduleDate = requestDetails.ScheduleDate;
string StartTime = requestDetails.StartTime;
string EndTime = requestDetails.EndTime;
string Comments = requestDetails.Comments;
int ModifiedBy = requestDetails.ModifiedBy;
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
using (MySqlCommand cmd = new MySqlCommand("insert_update_request"))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_id", id);
cmd.Parameters.AddWithValue("RoleId", RoleId);
cmd.Parameters.AddWithValue("RequesterId", RequesterId);
cmd.Parameters.AddWithValue("CaregiverId", CaregiverId);
cmd.Parameters.AddWithValue("ServiceId", ServiceId);
cmd.Parameters.AddWithValue("Status", Status);
cmd.Parameters.AddWithValue("ScheduleDate", ScheduleDate);
cmd.Parameters.AddWithValue("StartTime", StartTime);
cmd.Parameters.AddWithValue("EndTime", EndTime);
cmd.Parameters.AddWithValue("Comments", Comments);
cmd.Parameters.AddWithValue("ModifiedBy", ModifiedBy);
cmd.ExecuteReader();
con.Close();
return(true);
}
}
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(false);
}
public IHttpActionResult addRequest([FromBody] Entity.Request requestDetails)
{
string response = null;
IUnityContainer container = new UnityContainer();
RequestBL requestBL = new RequestBL(container);
try
{
response = JsonConvert.SerializeObject(requestBL.addRequest(requestDetails));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(Ok(response));
}
public IHttpActionResult PostUserLogin([FromBody] Credential usercr)
{
string response = null;
IUnityContainer container = new UnityContainer();
UserBL userBl = new UserBL(container);
try
{
response = JsonConvert.SerializeObject(userBl.userLogin(usercr));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(Ok(response));
}
public IHttpActionResult GetAllServices()
{
string response = null;
IUnityContainer container = new UnityContainer();
ServiceBL serviceBl = new ServiceBL(container);
try
{
response = JsonConvert.SerializeObject(serviceBl.GetAllServices());
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(Ok(response));
}
public IHttpActionResult addService([FromBody] Entity.Service serviceDetails)
{
string response = null;
IUnityContainer container = new UnityContainer();
ServiceBL serviceBl = new ServiceBL(container);
try
{
response = JsonConvert.SerializeObject(serviceBl.AddService(serviceDetails));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(Ok(response));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public IHttpActionResult PostAddUser([FromBody] User userData)
{
string response = null;
IUnityContainer container = new UnityContainer();
UserBL userBl = new UserBL(container);
try
{
response = JsonConvert.SerializeObject(userBl.SaveUser(userData));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "OfferController.GetAllListings()");
objErr.LogException();
}
return(Ok(response));
}
/// <summary>
/// This method calls stored procedure getalluser to get all users from the database.
/// </summary>
/// <param name="roleId"></param>
/// <returns></returns>
public List <User> GetAllUsers(int roleId)
{
var UserList = new List <User>();
try
{
using (MySqlCommand cmd = new MySqlCommand("getalluser"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_UserRoleId", roleId);
Database db = new Database();
using (MySqlDataReader dr = db.SelectQry(cmd))
{
while (dr.Read())
{
UserList.Add(new User()
{
idUser = Int32.Parse(dr["idUser"].ToString()),
UserName = dr["UserName"].ToString(),
UserEmail = dr["UserEmail"].ToString(),
UserMobile = dr["UserMobile"].ToString(),
UserSSN = dr["UserSSN"].ToString(),
LoginId = dr["LoginId"].ToString(),
LoginPassword = dr["LoginPassword"].ToString(),
CreatedOn = Convert.ToDateTime(dr["CreatedOn"].ToString()),
CreatedBy = Int32.Parse(dr["CreatedBy"].ToString()),
ModifiedOn = Convert.ToDateTime(dr["ModifiedOn"].ToString()),
ModifiedBy = Int32.Parse(dr["ModifiedBy"].ToString()),
IsActive = Int32.Parse(dr["IsActive"].ToString()),
Address = dr["Address"].ToString(),
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString(),
Sex = dr["Sex"].ToString(),
});
}
}
}
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(UserList);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public IHttpActionResult GetUserByUserName()
{
string response = null;
var QueryString = Request.RequestUri.ParseQueryString();
string UserName = (QueryString["username"] != null && !String.IsNullOrEmpty(QueryString["username"].ToString())) ? QueryString["username"] : "";
IUnityContainer container = new UnityContainer();
UserBL userBl = new UserBL(container);
try
{
response = JsonConvert.SerializeObject(userBl.GetUserByUserName(UserName));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "OfferController.GetAllListings()");
objErr.LogException();
}
return(Ok(response));
}
/// <summary>
/// This method dave user's details into the database by calling stored procedure insertorupdate_user.
/// </summary>
/// <param name="UserData"></param>
/// <returns>User</returns>
public User SaveUserDetails(User UserData)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
using (MySqlCommand cmd = new MySqlCommand("insertorupdate_user"))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("_idUser", MySqlDbType.Int32).Value = UserData.idUser;
cmd.Parameters.Add("UserName", MySqlDbType.VarChar).Value = UserData.UserName;
cmd.Parameters.Add("UserEmail", MySqlDbType.VarChar).Value = UserData.UserEmail;
cmd.Parameters.Add("UserMobile", MySqlDbType.VarChar).Value = UserData.UserMobile;
cmd.Parameters.Add("UserRoleId", MySqlDbType.Int32).Value = UserData.UserRoleId;
cmd.Parameters.Add("UserSSN", MySqlDbType.VarChar).Value = UserData.UserSSN;
cmd.Parameters.Add("CreatedBy", MySqlDbType.Int32).Value = UserData.CreatedBy;
cmd.Parameters.Add("ModifiedBy", MySqlDbType.Int32).Value = UserData.ModifiedBy;
cmd.Parameters.Add("LoginId", MySqlDbType.VarChar).Value = UserData.LoginId;
cmd.Parameters.Add("LoginPassword", MySqlDbType.VarChar).Value = UserData.LoginPassword;
cmd.Parameters.Add("IsActive", MySqlDbType.Bit).Value = UserData.IsActive;
cmd.Parameters.Add("Address", MySqlDbType.VarChar).Value = UserData.Address;
cmd.Parameters.Add("FirstName", MySqlDbType.VarChar).Value = UserData.FirstName;
cmd.Parameters.Add("LastName", MySqlDbType.VarChar).Value = UserData.LastName;
cmd.Parameters.Add("Sex", MySqlDbType.VarChar).Value = UserData.Sex;
cmd.Parameters.AddWithValue("ModifiedOn", UserData.ModifiedOn);
cmd.ExecuteReader();
con.Close();
return(GetUserByUserName(UserData.UserName));
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
var objErr = new ErrorClass(ex, "");
objErr.LogException();
return(null);
}
}
//using MySqlCommand
public string GetInClauseValue(string input, string fieldName, MySqlCommand cmd)
{
string[] inputArr = input.Split(',');
string[] parameters = new string[inputArr.Length];
try
{
for (int i = 0; i < inputArr.Length; i++)
{
cmd.Parameters.Add("@" + fieldName + i, MySqlDbType.VarChar, inputArr[i].Length).Value = inputArr[i].ToString();
parameters[i] = "@" + fieldName + i;
}
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
return(string.Join(",", parameters));
}
public bool DeleteQry(string sqlStr, MySqlParameter[] commandParameters)
{
con = new MySqlConnection(strConn);
bool result = false;
try
{
cmd = new MySqlCommand(sqlStr, con);
con.Open();
foreach (MySqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
int retval = cmd.ExecuteNonQuery();
if (retval > 0)
{
result = true;
}
else
{
result = false;
}
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
cmd.Parameters.Clear();
CloseConnection();
}
return(result);
}
public DataSet SelectAdaptQry(string sqlStr, MySqlParameter[] commandParameters)
{
DataSet dataSet = new DataSet();
con = new MySqlConnection(strConn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
try
{
adapter.SelectCommand = new MySqlCommand(sqlStr, con);
foreach (MySqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
adapter.SelectCommand.Parameters.Add(p);
}
adapter.Fill(dataSet);
adapter.SelectCommand.Parameters.Clear();
}
catch (MySqlException exSql)
{
dataSet = null;
var objErr = new ErrorClass(exSql, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
catch (Exception err)
{
var objErr = new ErrorClass(err, HttpContext.Current.Request.ServerVariables["URL"]);
objErr.LogException();
}
finally
{
adapter.SelectCommand.Parameters.Clear();
CloseConnection();
}
return(dataSet);
}
/// <summary>
/// This method calls stored procedure getuserbyusername to get all users based on username.
/// </summary>
/// <param name="UserName"></param>
/// <returns></returns>
public User GetUserByUserName(string UserName)
{
var UserData = new User();
try
{
using (MySqlCommand cmd = new MySqlCommand("getuserbyusername"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_UserName", UserName);
Database db = new Database();
using (MySqlDataReader dr = db.SelectQry(cmd))
{
if (dr.Read())
{
UserData.idUser = Int32.Parse(dr["idUser"].ToString());
UserData.UserName = dr["UserName"].ToString();
UserData.UserEmail = dr["UserEmail"].ToString();
UserData.UserMobile = dr["UserMobile"].ToString();
UserData.UserSSN = dr["UserSSN"].ToString();
UserData.LoginId = dr["LoginId"].ToString();
UserData.LoginPassword = dr["LoginPassword"].ToString();
UserData.CreatedOn = Convert.ToDateTime(dr["CreatedOn"].ToString());
UserData.CreatedBy = Int32.Parse(dr["CreatedBy"].ToString());
UserData.ModifiedOn = Convert.ToDateTime(dr["ModifiedOn"].ToString());
UserData.ModifiedBy = Int32.Parse(dr["ModifiedBy"].ToString());
UserData.Address = dr["Address"].ToString();
UserData.FirstName = dr["FirstName"].ToString();
UserData.LastName = dr["LastName"].ToString();
UserData.Sex = dr["Sex"].ToString();
}
}
}
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(UserData);
}
/// <summary>
/// This method calls stored procedure addservice to add a new service.
/// </summary>
/// <param name="serviceDetails"></param>
/// <returns>boolean</returns>
public Boolean AddService(Service serviceDetails)
{
try
{
int id = serviceDetails.id;
string serviceName = serviceDetails.serviceName;
string serviceDesc = serviceDetails.serviceDesc;
int serviceStatus = serviceDetails.serviceStatus;
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
using (MySqlCommand cmd = new MySqlCommand("addservice"))
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
if (id == 0)
{
cmd.Parameters.AddWithValue("_id", null);
}
else
{
cmd.Parameters.AddWithValue("_id", id);
}
cmd.Parameters.AddWithValue("serviceName", serviceName);
cmd.Parameters.AddWithValue("servicesDesc", serviceDesc);
cmd.Parameters.AddWithValue("IsActive", serviceStatus);
cmd.ExecuteReader();
con.Close();
return(true);
}
}
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(false);
}
public IHttpActionResult getRequest()
{
string response = null;
var QueryString = Request.RequestUri.ParseQueryString();
int requesterid = (QueryString["requesterid"] != null && !String.IsNullOrEmpty(QueryString["requesterid"].ToString())) ? Convert.ToInt32(QueryString["requesterid"]) : -1;
int caregiverid = (QueryString["caregiverid"] != null && !String.IsNullOrEmpty(QueryString["caregiverid"].ToString())) ? Convert.ToInt32(QueryString["caregiverid"]) : -1;
string status = (QueryString["status"] != null && !String.IsNullOrEmpty(QueryString["status"].ToString())) ? QueryString["status"] : "";
IUnityContainer container = new UnityContainer();
RequestBL requestBL = new RequestBL(container);
try
{
response = JsonConvert.SerializeObject(requestBL.getRequestsByFilters(caregiverid, requesterid, status));
}
catch (Exception ex)
{
var objErr = new ErrorClass(ex, "");
objErr.LogException();
}
return(Ok(response));
}