public ResponseMessage GetSingleProfile(int id)
{
ResponseMessage result = new ResponseMessage();
//List<TblEmployeeInformation> employeeList = new List<TblEmployeeInformation>();
try
{
TblEmployeeInformation employee = new TblEmployeeInformation();
using (_connection = new OracleConnection(_dbConnectionString))
{
using (_command = new OracleCommand())
{
_command.BindByName = true;
_connection.Open();
_command.Connection = _connection;
_command.CommandText = "spFetchSingleProfile";
_command.Parameters.Add("p_id", id);
_command.Parameters.Add("o_name", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_designation", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_cell_no", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_email", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_address", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_nid", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_password", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.Parameters.Add("o_type_id", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.CommandType = CommandType.StoredProcedure;
int queryResult = _command.ExecuteNonQuery();
//employee.ID = Convert.ToInt32(_command.Parameters["P_ID"].Value.ToString());
employee.ID = id;
employee.NAME = _command.Parameters["o_name"].Value.ToString();
employee.DESIGNATION = _command.Parameters["o_designation"].Value.ToString();
employee.CELL_NO = _command.Parameters["o_cell_no"].Value.ToString();
employee.EMAIL = _command.Parameters["o_email"].Value.ToString();
employee.ADDRESS = _command.Parameters["o_address"].Value.ToString();
employee.NID = _command.Parameters["o_nid"].Value.ToString();
employee.PASSWORD = _command.Parameters["o_password"].Value.ToString();
employee.TYPE_ID = _command.Parameters["o_type_id"].Value.ToString();
_connection.Close();
}
}
return(result = ResponseMapping.GetResponseMessage(employee, (int)StatusCode.Success, ConstantMessage.RetriveSuccess));
}
catch (Exception ex)
{
return(result = ResponseMapping.GetResponseMessage(null, (int)StatusCode.Faild, ex.Message.ToString()));
}
}
public ResponseMessage UpdateEmpolyee()
{
ResponseMessage result = new ResponseMessage();
List <TblEmployeeInformation> employeeList = new List <TblEmployeeInformation>();
try
{
using (_connection = new OracleConnection(_dbConnectionString))
{
using (_command = new OracleCommand())
{
_command.BindByName = true;
//_command.CommandText = "SCOTT.spLogin";
//_command.Parameters.Add("",)
_command.Connection = _connection;
_connection.Open();
_command.CommandText = "update TBLEMPLOYEEINFORMATION set CELL_NO = 01314567 where ID = 3";
_command.CommandType = CommandType.Text;
_reader = _command.ExecuteReader();
while (_reader.Read())
{
TblEmployeeInformation emp = new TblEmployeeInformation
{
ID = Convert.ToInt32(_reader["ID"]),
NAME = _reader["NAME"].ToString(),
DESIGNATION = _reader["DESIGNATION"].ToString(),
CELL_NO = _reader["CELL_NO"].ToString(),
EMAIL = _reader["EMAIL"].ToString(),
ADDRESS = _reader["ADDRESS"].ToString(),
NID = _reader["NID"].ToString(),
PASSWORD = _reader["PASSWORD"].ToString()
};
employeeList.Add(emp);
}
_connection.Close();
}
}
return(result = ResponseMapping.GetResponseMessage(employeeList, (int)StatusCode.Success, ConstantMessage.RetriveSuccess));
}
catch (Exception ex)
{
return(result = ResponseMapping.GetResponseMessage(null, (int)StatusCode.Faild, ex.Message.ToString()));
}
}
public ResponseMessage AddEmployee(TblEmployeeInformation employee)
{
ResponseMessage result = new ResponseMessage();
int sqlResult = 0;
try
{
string password = SimpleCryptService.Factory().Encrypt(employee.PASSWORD);
using (_connection = new OracleConnection(_dbConnectionString))
{
using (_command = new OracleCommand())
{
_command.BindByName = true;
_command.Connection = _connection;
_connection.Open();
_command.CommandText = "spAddProfile";
_command.Parameters.Add("p_name", employee.NAME);
_command.Parameters.Add("p_designation", employee.DESIGNATION);
_command.Parameters.Add("p_cell_no", employee.CELL_NO);
_command.Parameters.Add("p_email", employee.EMAIL);
_command.Parameters.Add("p_address", employee.ADDRESS);
_command.Parameters.Add("p_nid", employee.NID);
_command.Parameters.Add("p_password", password);
_command.Parameters.Add("p_type", employee.TYPE_ID);
// _command.CommandText = $"insert into TBLEMPLOYEEINFORMATION(ID,NAME,DESIGNATION,CELL_NO,EMAIL,ADDRESS,NID,PASSWORD) values ({employee.ID},'{employee.NAME}','{employee.DESIGNATION}','{employee.CELL_NO}','{employee.EMAIL}','{employee.ADDRESS}','{employee.NID}','{employee.PASSWORD}')";
// _command.CommandText = $"insert into TBLEMPLOYEEINFORMATION(ID,NAME,DESIGNATION,CELL_NO,EMAIL,ADDRESS,NID,PASSWORD) values ("+employee.ID+",'"+employee.NAME+"','"+ employee.DESIGNATION + "','" + employee.CELL_NO + "','" + employee.EMAIL + "','" + employee.ADDRESS + "','" + employee.NID + "','" + employee.PASSWORD + "')";
_command.CommandType = CommandType.StoredProcedure;
sqlResult = _command.ExecuteNonQuery();
_connection.Close();
}
}
return(result = ResponseMapping.GetResponseMessage(sqlResult, (int)StatusCode.Success, ConstantMessage.AddProfileSuccess));
}
catch (Exception ex)
{
return(result = ResponseMapping.AddResponseMessage((int)StatusCode.Faild, ex.Message.ToString()));
}
}
public ResponseMessage UpdateEmpolyee(TblEmployeeInformation employee)
{
ResponseMessage result = new ResponseMessage();
int sqlResult = 0;
try
{
using (_connection = new OracleConnection(_dbConnectionString))
{
using (_command = new OracleCommand())
{
_command.BindByName = true;
_command.Connection = _connection;
_connection.Open();
_command.CommandText = "spUpdateInformation";
_command.Parameters.Add("p_id", employee.ID);
_command.Parameters.Add("p_name", employee.NAME);
_command.Parameters.Add("p_designation", employee.DESIGNATION);
_command.Parameters.Add("p_cell_no", employee.CELL_NO);
_command.Parameters.Add("p_email", employee.EMAIL);
_command.Parameters.Add("p_address", employee.ADDRESS);
_command.Parameters.Add("p_nid", employee.NID);
_command.Parameters.Add("p_password", employee.PASSWORD);
_command.Parameters.Add("p_type", employee.TYPE_ID);
_command.CommandType = CommandType.StoredProcedure;
sqlResult = _command.ExecuteNonQuery();
_connection.Close();
}
}
return(result = ResponseMapping.GetResponseMessage(sqlResult, (int)StatusCode.Success, ConstantMessage.UpdateSuccess));
}
catch (Exception ex)
{
return(result = ResponseMapping.AddResponseMessage((int)StatusCode.Faild, ex.Message.ToString()));
}
}
public ResponseMessage GetEmployees()
{
ResponseMessage result = new ResponseMessage();
List <TblEmployeeInformation> employeeList = new List <TblEmployeeInformation>();
//int sqlResult = 0;
try
{
using (_connection = new OracleConnection(_dbConnectionString))
{
using (_command = new OracleCommand())
{
_command.BindByName = true;
_connection.Open();
_command.Connection = _connection;
/*_command.CommandText = "spFetchAllProfile";
* _command.Parameters.Add("p_id", OracleDbType.Int64).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_name", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_designation", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_cell_no", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_email", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_address", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_nid", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_password", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
* _command.Parameters.Add("p_type_id", OracleDbType.Int64).Direction = ParameterDirection.Output;
* _command.Parameters.Add("po_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;*/
_command.CommandText = "sp_FetchAllProfile";
_command.Parameters.Add("employee_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
// _command.CommandText = "spFetchAllProfile";
// _command.Parameters.Add("c1", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
// _command.Parameters.Add("p_type", employee.TYPE);
// _command.Parameters.Add("po_message", OracleDbType.Varchar2, 1000).Direction = ParameterDirection.Output;
_command.CommandType = CommandType.StoredProcedure;
// _command.ExecuteNonQuery();
//_connection.Close();
//_command.CommandText = "select *from TBLEMPLOYEEINFORMATION";
//_command.CommandType = CommandType.Text;
_reader = _command.ExecuteReader();
while (_reader.Read())
{
TblEmployeeInformation employee = new TblEmployeeInformation();
employee.ID = Convert.ToInt32(_reader["ID"]);
employee.NAME = _reader["NAME"].ToString();
employee.DESIGNATION = _reader["DESIGNATION"].ToString();
employee.CELL_NO = _reader["CELL_NO"].ToString();
employee.EMAIL = _reader["EMAIL"].ToString();
employee.ADDRESS = _reader["ADDRESS"].ToString();
employee.NID = _reader["NID"].ToString();
employee.PASSWORD = _reader["PASSWORD"].ToString();
employee.TYPE_ID = _reader["TYPE_ID"].ToString();
//employee.TYPE_ID = Convert.ToInt32(_reader["TYPE_ID"]);
employeeList.Add(employee);
}
_connection.Close();
}
}
return(result = ResponseMapping.GetResponseMessage(employeeList, (int)StatusCode.Success, ConstantMessage.RetriveSuccess));
}
catch (Exception ex)
{
return(result = ResponseMapping.GetResponseMessage(null, (int)StatusCode.Faild, ex.Message.ToString()));
}
}
public JsonResult Update([FromForm] TblEmployeeInformation employee)
{
var resuslt = _employeeServices.UpdateEmpolyee(employee);
return(new JsonResult(resuslt));
}
public JsonResult Add([FromForm] TblEmployeeInformation employee)
{
var resuslt = _employeeServices.AddEmployee(employee);
return(new JsonResult(resuslt));
}
