/*if (hireDate != string.Empty)
* {
* sCommand += " AND EmpNo = @empno";
* }
* if (birthDate != string.Empty)
* {
* sCommand += " AND EmpNo = @empno";
* }*/
public static int AddEmployee(Employee e)
{
using (SqlConnection con = new System.Data.SqlClient.SqlConnection(SqlConnect.GetConString()))
{
string sCommand = "INSERT INTO Website..Employees(BirthDate, FirstName, LastName, Gender, HireDate) VALUES(@birthdate, @firstname, @lastname, @gender, @hiredate)";
try
{
SqlCommand cmd = new SqlCommand(sCommand, con);
cmd.Parameters.Add("@birthdate", SqlDbType.Date).Value = e.BirthDate;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = e.FirstName;
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = e.LastName;
cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = e.Gender;
cmd.Parameters.Add("@hiredate", SqlDbType.Date).Value = e.HireDate;
con.Open();
int result = cmd.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(0);
}
public static int UpdateEmployee(Employee e)
{
using (SqlConnection con = new System.Data.SqlClient.SqlConnection(SqlConnect.GetConString()))
{
string sCommand = "UPDATE Website..Employees SET BirthDate = @birthdate, FirstName = @firstname, LastName = @lastname, Gender = @gender, HireDate = @hiredate WHERE EmpNo = @empno";
try
{
SqlCommand cmd = new SqlCommand(sCommand, con);
cmd.Parameters.Add("@birthdate", SqlDbType.Date).Value = e.BirthDate;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = e.FirstName;
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = e.LastName;
cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = e.Gender;
cmd.Parameters.Add("@hiredate", SqlDbType.Date).Value = e.HireDate;
cmd.Parameters.Add("@empno", SqlDbType.Int).Value = e.EmpNo;
con.Open();
int result = cmd.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(0);
}
public static int DeleteEmployee(Employee e)
{
using (SqlConnection con = new System.Data.SqlClient.SqlConnection(SqlConnect.GetConString()))
{
string sCommand = "DELETE FROM Website..Employees WHERE EmpNo = @empno";
try
{
SqlCommand cmd = new SqlCommand(sCommand, con);
cmd.Parameters.Add("@empno", SqlDbType.Int).Value = e.EmpNo;
con.Open();
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(0);
}
public static List <Employee> GetEmployees(int empNo = 0, string birthDate = "", string firstName = "", string lastName = "", string gender = "", string hireDate = "")
{
List <Employee> employees = new List <Employee> {
};
using (SqlConnection con = new System.Data.SqlClient.SqlConnection(SqlConnect.GetConString()))
{
string sCommand = "SELECT Top 2000 * FROM Website..Employees WHERE 1=1";
if (empNo != 0)
{
sCommand += " AND EmpNo = @empno";
}
if (firstName != string.Empty)
{
sCommand += " AND FirstName LIKE @firstname";
}
if (lastName != string.Empty)
{
sCommand += " AND LastName LIKE @lastname";
}
if (gender != string.Empty)
{
sCommand += " AND Gender = @gender";
}
try
{
SqlCommand cmd = new SqlCommand(sCommand, con);
cmd.Parameters.Add("@empno", SqlDbType.Int).Value = empNo;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = firstName + "%";
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = lastName + "%";
cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = gender;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int colIndex = 0;
Employee e = new Employee();
colIndex = reader.GetOrdinal("EmpNo");
e.EmpNo = reader.GetInt32(colIndex);
colIndex = reader.GetOrdinal("BirthDate");
e.BirthDate = reader.GetDateTime(colIndex);
colIndex = reader.GetOrdinal("FirstName");
e.FirstName = reader.GetString(colIndex);
colIndex = reader.GetOrdinal("LastName");
e.LastName = reader.GetString(colIndex);
colIndex = reader.GetOrdinal("Gender");
e.Gender = reader.GetString(colIndex);
colIndex = reader.GetOrdinal("HireDate");
e.HireDate = reader.GetDateTime(colIndex);
employees.Add(e);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(employees);
}
public int AddUser()
{
int rowsAdded = 0;
using (SqlConnection con = new System.Data.SqlClient.SqlConnection(SqlConnect.GetConString()))
{
SqlCommand comTask = new SqlCommand();
comTask.Connection = con;
String sCommand = String.Empty;
sCommand += "INSERT INTO General..UserAccounts (";
sCommand += "FirstName,";
sCommand += "LastName,";
sCommand += "EmailAddress,";
sCommand += "Password,";
sCommand += "PasswordSalt,";
sCommand += "LastIP,";
sCommand += "DateCreated,";
sCommand += "MobileNumber";
sCommand += " )";
sCommand += " VALUES (";
sCommand += "@FirstName" + ",";
sCommand += "@LastName" + ",";
sCommand += "@EmailAddress" + ",";
sCommand += "@Password" + ",";
sCommand += "@PasswordSalt" + ",";
sCommand += "@LastIP" + ",";
sCommand += "@DateCreated" + ",";
sCommand += "@MobileNumber";
sCommand += " );";
comTask.CommandText = sCommand;
comTask.CommandType = CommandType.Text;
comTask.Parameters.Add("@FirstName", SqlDbType.VarChar);
comTask.Parameters["@FirstName"].Value = FirstName;
comTask.Parameters.Add("@LastName", SqlDbType.VarChar);
comTask.Parameters["@LastName"].Value = LastName;
comTask.Parameters.Add("@EmailAddress", SqlDbType.VarChar);
comTask.Parameters["@EmailAddress"].Value = EmailAddress;
comTask.Parameters.Add("@Password", SqlDbType.VarChar);
comTask.Parameters["@Password"].Value = Password;
comTask.Parameters.Add("@PasswordSalt", SqlDbType.VarChar);
comTask.Parameters["@PasswordSalt"].Value = PasswordSalt;
comTask.Parameters.Add("@LastIP", SqlDbType.VarChar);
comTask.Parameters["@LastIP"].Value = LastIP;
comTask.Parameters.Add("@DateCreated", SqlDbType.DateTime);
comTask.Parameters["@DateCreated"].Value = DateTime.Now;
comTask.Parameters.Add("@MobileNumber", SqlDbType.VarChar);
comTask.Parameters["@MobileNumber"].Value = MobileNumber;
try
{
con.Open();
rowsAdded = comTask.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAdded);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(rowsAdded);
}