public static Employee SearchRecord(int searchId) { Employee emp = new Employee(); SqlConnection sqlConn = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlConn; cmd.CommandText = "SELECT * from Employees " + "WHERE EmployeeId = @EmployeeId "; cmd.Parameters.AddWithValue("@EmployeeId", searchId); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]); emp.FirstName = reader["FirstName"].ToString(); emp.LastName = reader["LastName"].ToString(); emp.JobTitle = reader["JobTitle"].ToString(); } else { emp = null; } return(emp); }
public static List <Employee> GetRecordList() { List <Employee> listEmp = new List <Employee>(); SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", connDB); SqlDataReader reader = cmd.ExecuteReader(); Employee emp; if (reader.HasRows) { while (reader.Read()) { emp = new Employee();//create the object here, not outside emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]); emp.FirstName = reader["FirstName"].ToString(); emp.LastName = reader["LastName"].ToString(); emp.JobTitle = reader["JobTitle"].ToString(); listEmp.Add(emp); } } else { listEmp = null; } return(listEmp); }
public static void DeleteRecord(int id) { SqlConnection connDb = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connDb; cmd.CommandText = "delete from Employees where EmployeeId = @EmployeeId"; cmd.Parameters.AddWithValue("@EmployeeId", id); cmd.ExecuteNonQuery(); connDb.Close(); }
//version 1: Not good //public static void SaveRecord(Employee emp) //{ // SqlConnection connDB = UtilityDB.ConnectDB(); // SqlCommand cmd = new SqlCommand(); // cmd.Connection = connDB; // cmd.CommandText = "INSERT INTO Employees " + // "VALUES(" + emp.EmployeeId + "," + // "'" + emp.FirstName + "'," + // "'" + emp.LastName + "'," + // "'" + emp.JobTitle + "')"; // cmd.ExecuteNonQuery(); // connDB.Close(); //} public static void SaveRecord(Employee emp) { SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "INSERT INTO Employees(EmployeeId,FirstName,LastName,JobTitle) " + " VALUES(@EmployeeId,@FirstName,@LastName,@JobTitle) "; cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmd.Parameters.AddWithValue("@FirstName", emp.FirstName); cmd.Parameters.AddWithValue("@LastName", emp.LastName); cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle); cmd.ExecuteNonQuery(); connDB.Close(); }
public static void UpdateRecord(Employee emp) { using (SqlConnection connDB = UtilityDB.ConnectDB()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "UPDATE Employees " + " set FirstName = @FirstName, " + " LastName = @LastName, " + " JobTitle = @JobTitle " + " WHERE EmployeeId = @EmployeeId "; cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmd.Parameters.AddWithValue("@FirstName", emp.FirstName); cmd.Parameters.AddWithValue("@LastName", emp.LastName); cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle); cmd.ExecuteNonQuery(); } }
public static bool IsUniqueId(int tempId) { SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "SELECT * FROM Employees " + " WHERE EmployeeId =" + tempId; int id = Convert.ToInt32(cmd.ExecuteScalar()); if (id != 0) { connDB.Close(); return(false); } connDB.Close(); return(true); }
public static List <Employee> ListRecord() { List <Employee> listEmp = new List <Employee>(); SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", connDB); SqlDataReader reader = cmd.ExecuteReader(); Employee emp; while (reader.Read()) { emp = new Employee(); emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]); emp.FirstName = reader["FirstName"].ToString(); emp.LastName = reader["LastName"].ToString(); emp.JobTitle = reader["JobTitle"].ToString(); listEmp.Add(emp); } return(listEmp); }
//public static bool IsUniqueId(int tempId) //{ // //connect the database // SqlConnection connDB = UtilityDB.ConnectDB(); // //create and customize an object of type SqlCommand // SqlCommand cmd = new SqlCommand(); // cmd.Connection = connDB; // cmd.CommandText = "SELECT * FROM Employees "; // SqlDataReader dReader = cmd.ExecuteReader(); // if (dReader.HasRows) // { // while (dReader.Read()) // { // //Duplicate // if ((Convert.ToInt32(dReader["EmployeeId"])) == tempId) // return false; // } // } // // true if unique // return true; //} public static bool IsUniqueId(int tempId) { //connect the database SqlConnection connDB = UtilityDB.ConnectDB(); //create and customize an object of type SqlCommand SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "SELECT * FROM Employees " + " WHERE EmployeeId= " + tempId; int id = Convert.ToInt32(cmd.ExecuteScalar()); MessageBox.Show(id.ToString()); // debug code if (id != 0) { return(false); } // true if unique return(true); }
public static void UpdateRecord(Employee emp) { //connect the database SqlConnection connDB = UtilityDB.ConnectDB(); //create and customize the object SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "Update Employees " + "SET FirstName = @FirstName, " + "LastName = @LastName, " + "JobTitle = @JobTitle " + "WHERE EmployeeId = @EmployeeId "; // MessageBox.Show(cmd.CommandText); cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmd.Parameters.AddWithValue("@FirstName", emp.FirstName); cmd.Parameters.AddWithValue("@LastName", emp.LastName); cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle); cmd.ExecuteNonQuery(); //close connection connDB.Close(); }
public static List <Employee> SearchRecord(int option, string name) { List <Employee> listEmp = new List <Employee>(); using (SqlConnection connDB = UtilityDB.ConnectDB()) { switch (option) { case 1: // search by FirstName SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "SELECT * FROM Employees " + "WHERE FirstName = @FirstName "; cmd.Parameters.AddWithValue("@FirstName", name); SqlDataReader reader = cmd.ExecuteReader(); Employee emp; if (reader.HasRows) { while (reader.Read()) { emp = new Employee(); emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]); emp.FirstName = reader["FirstName"].ToString(); emp.LastName = reader["LastName"].ToString(); emp.JobTitle = reader["JobTitle"].ToString(); listEmp.Add(emp); } } else { listEmp = null; } break; case 2: // search by LastName SqlCommand cmd2 = new SqlCommand(); cmd2.Connection = connDB; cmd2.CommandText = "SELECT * FROM Employees " + "WHERE LastName = @LastName "; cmd2.Parameters.AddWithValue("@LastName", name); SqlDataReader reader2 = cmd2.ExecuteReader(); Employee emp2; if (reader2.HasRows) { while (reader2.Read()) { emp2 = new Employee(); emp2.EmployeeId = Convert.ToInt32(reader2["EmployeeId"]); emp2.FirstName = reader2["FirstName"].ToString(); emp2.LastName = reader2["LastName"].ToString(); emp2.JobTitle = reader2["JobTitle"].ToString(); listEmp.Add(emp2); } } else { listEmp = null; } break; default: break; } } return(listEmp); }