public static bool UpdateEmployee(EmployeeBO employee)
{
bool status = false;
SqlConnection conn = null;
try
{
conn = AppCon.GetConnection();
SqlCommand cmd = new SqlCommand("spUpdateEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@Id";
paramId.Value = employee.Id;
paramId.SqlDbType = SqlDbType.Int;
paramId.Size = 50;
cmd.Parameters.Add(paramId);
SqlParameter paraFirstName = new SqlParameter();
paraFirstName.ParameterName = "@FirstName";
paraFirstName.Value = employee.FirstName;
paraFirstName.SqlDbType = SqlDbType.NVarChar;
paraFirstName.Size = 50;
cmd.Parameters.Add(paraFirstName);
SqlParameter paramLastName = new SqlParameter();
paramLastName.ParameterName = "@LastName";
paramLastName.Value = employee.LastName;
paramLastName.SqlDbType = SqlDbType.NVarChar;
paramLastName.Size = 50;
cmd.Parameters.Add(paramLastName);
SqlParameter paramCompanyName = new SqlParameter();
paramCompanyName.ParameterName = "@CompanyName";
paramCompanyName.Value = employee.CompanyName;
paramCompanyName.SqlDbType = SqlDbType.NVarChar;
paramCompanyName.Size = 50;
cmd.Parameters.Add(paramCompanyName);
conn.Open();
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
public async Task <EmployeeBO> GetEmployeeByID(int empID)
{
EmployeeBO EmpInfo = new EmployeeBO();
var client = new HttpClient();
InitializeHttpClient(ref client);
HttpResponseMessage response = await client.GetAsync("api/Employees/" + empID.ToString());
if (response.IsSuccessStatusCode)
{
var EmpResponse = response.Content.ReadAsStringAsync().Result;
EmpInfo = JsonConvert.DeserializeObject <EmployeeBO>(EmpResponse);
}
return(EmpInfo);
}
public EmployeeBO GetById(int Id)
{
SqlConnection con = null;
EmployeeBO emp = null;
try
{
con = AppCon.GetConnection();
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spGetById";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramId = new SqlParameter();
paramId.Value = Id;
paramId.ParameterName = "@Id";
paramId.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(paramId);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
emp = new EmployeeBO();
emp.Id = Convert.ToInt32(rdr["Id"]);
emp.FirstName = rdr["FirstName"].ToString();
emp.LastName = rdr["LastName"].ToString();
emp.CompanyName = rdr["CompanyName"].ToString();
}
}
catch (NullReferenceException)
{
Console.WriteLine($"Employee with Id ={emp.Id} does on exits in the database");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(emp);
}
public bool DelectEmployee(int Id)
{
EmployeeBO emp = new EmployeeBO();
SqlConnection con = null;
bool status = false;
try
{
con = AppCon.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spDelete";
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlParameter paramId = new SqlParameter();
paramId.Value = Id;
paramId.ParameterName = "@Id";
paramId.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(paramId);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
status = true;
}
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(status);
}
public List <EmployeeBO> GetALL()
{
SqlConnection con = null;
List <EmployeeBO> listemployees = new List <EmployeeBO>();
try
{
con = AppCon.GetConnection();
con.Open();
SqlCommand cmd = new SqlCommand("spGetAll", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EmployeeBO emp = new EmployeeBO();
emp.Id = Convert.ToInt32(rdr["Id"]);
emp.FirstName = rdr["FirstName"].ToString();
emp.LastName = rdr["LastName"].ToString();
emp.CompanyName = rdr["CompanyName"].ToString();
listemployees.Add(emp);
}
//JavaScriptSerializer js = new JavaScriptSerializer();
//js.Serialize(listemployees);
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(listemployees);
}
public int AddEmployee(EmployeeBO employee)
{
bool status = false;
int Empid = 0;
SqlConnection conn = null;
try
{
conn = AppCon.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "spInsertIntoEmployee";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraFirstName = new SqlParameter();
paraFirstName.ParameterName = "@FirstName";
paraFirstName.Value = employee.FirstName;
paraFirstName.SqlDbType = SqlDbType.NVarChar;
paraFirstName.Size = 100;
cmd.Parameters.Add(paraFirstName);
SqlParameter paramLastName = new SqlParameter();
paramLastName.ParameterName = "@LastName";
paramLastName.Value = employee.LastName;
paramLastName.SqlDbType = SqlDbType.NVarChar;
paramLastName.Size = 100;
cmd.Parameters.Add(paramLastName);
SqlParameter paramCompanyName = new SqlParameter();
paramCompanyName.ParameterName = "@CompanyName";
paramCompanyName.Value = employee.CompanyName;
paramCompanyName.SqlDbType = SqlDbType.NVarChar;
paramCompanyName.Size = 100;
cmd.Parameters.Add(paramCompanyName);
SqlParameter paramEmployeeIdOut = new SqlParameter();
paramEmployeeIdOut.ParameterName = "@EmpoyeeId";
paramEmployeeIdOut.SqlDbType = SqlDbType.Int;
paramEmployeeIdOut.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramEmployeeIdOut);
conn.Open();
int result = cmd.ExecuteNonQuery();
employee.Id = Convert.ToInt32(paramEmployeeIdOut.Value);
if (result > 0)
{
status = true;
}
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(employee.Id);
}