//version 1 //public static void SaveRecord(Employee emp) //{ // //1 connect to databse // SqlConnection conn = UtilityDB.ConnectDB(); // //2 create and customize a sqlcommand object // SqlCommand cmdInsert = new SqlCommand(); // cmdInsert.CommandText = "INSERT INTO Employees VALUES(" // + emp.EmployeeId + ",'" // + emp.FirstName + "','" // + emp.LastName + "','" // + emp.JobTitle + "');"; // cmdInsert.Connection = conn; // //3 perform the insert operation // cmdInsert.ExecuteNonQuery(); // //4 close the connection // conn.Close(); //} public static void SaveRecord(Employee emp) { // version 2 : using parameterized query : Easy to write, less error, no sql injection //1 connect to databse SqlConnection conn = UtilityDB.ConnectDB(); try { SqlCommand cmdInsert = new SqlCommand(); cmdInsert.CommandText = "INSERT INTO Employees(EmployeeId,FirstName,LastName,JobTitle) VALUES (@EmployeeId,@FirstName,@LastName,@JobTitle)"; //parameterized query cmdInsert.Connection = conn; cmdInsert.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmdInsert.Parameters.AddWithValue("@FirstName", emp.FirstName); cmdInsert.Parameters.AddWithValue("@LastName", emp.LastName); cmdInsert.Parameters.AddWithValue("@JobTitle", emp.JobTitle); cmdInsert.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { //4 close the connection conn.Close(); conn.Dispose(); // //return memory back to system } }
public static void UpdateRecord(Employee emp) { SqlConnection conn = UtilityDB.ConnectDB(); try { SqlCommand cmdUpdate = new SqlCommand(); cmdUpdate.CommandText = "UPDATE Employees SET EmployeeId = @EmployeeId,FirstName = @FirstName, LastName = @LastName , JobTitle = @JobTitle WHERE EmployeeId = @EmployeeId"; cmdUpdate.Connection = conn; cmdUpdate.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmdUpdate.Parameters.AddWithValue("@FirstName", emp.FirstName); cmdUpdate.Parameters.AddWithValue("@LastName", emp.LastName); cmdUpdate.Parameters.AddWithValue("@JobTitle", emp.JobTitle); cmdUpdate.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { conn.Close(); conn.Dispose(); } }
public static bool IsDuplicateId(int id) { int flag = 0; SqlConnection conn = UtilityDB.ConnectDB(); SqlCommand cmdValidate = new SqlCommand(); cmdValidate.CommandText = "SELECT EmployeeId FROM Employees WHERE EmployeeId = @EmployeeId"; cmdValidate.Parameters.AddWithValue("@EmployeeId", id); cmdValidate.Connection = conn; SqlDataReader reader = cmdValidate.ExecuteReader(); while (reader.Read()) { if (Convert.ToInt32(id) == Convert.ToInt32(reader["EmployeeId"])) { flag = 1; } } conn.Close(); conn.Dispose(); if (flag == 1) { return(false); } else { return(true); } }
public static void DeleteRecord(Employee emp) { SqlConnection conn = UtilityDB.ConnectDB(); try { SqlCommand cmdDelete = new SqlCommand(); cmdDelete.CommandText = "DELETE FROM Employees WHERE EmployeeId = @EmployeeId"; cmdDelete.Connection = conn; cmdDelete.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId); cmdDelete.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { conn.Close(); conn.Dispose(); } }
public static List <Employee> GetListRecords() { List <Employee> listEmp = new List <Employee>(); SqlConnection conn = UtilityDB.ConnectDB(); SqlCommand cmdSelect = new SqlCommand(); cmdSelect.Connection = conn; cmdSelect.CommandText = "Select * from Employees"; SqlDataReader reader = cmdSelect.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 List <Employee> SearchRecords(int employeeId) { List <Employee> listEmp = new List <Employee>(); SqlConnection conn = UtilityDB.ConnectDB(); SqlCommand cmdSearch = new SqlCommand(); cmdSearch.CommandText = "SELECT * FROM EMPLOYEES WHERE EmployeeId = @EmployeeId"; cmdSearch.Parameters.AddWithValue("@EmployeeId", employeeId); cmdSearch.Connection = conn; SqlDataReader reader = cmdSearch.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); }