public static Client SearchRecord(int cliId) { Client cli = new Client(); //Connect DB SqlConnection connDB = UtilityDB.ConnectDB(); //SqlCommand object SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "SELECT * FROM ClientDB " + "WHERE ClientID = @ClientID "; cmd.Parameters.AddWithValue("@ClientID", cliId); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { cli.CLientID = Convert.ToInt32(reader["CLientID"]); cli.FirstName = reader["FirstName"].ToString(); cli.LastName = reader["LastName"].ToString(); cli.PhoneNumber = reader["PhoneNumber"].ToString(); cli.Email = reader["Email"].ToString(); } else { cli = null; } connDB.Close(); //Close DB return(cli); }
public static void SaveRecord(Client cli) { SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "INSERT INTO ClientDB(ClientID,FirstName,LastName,PhoneNumber,Email) " + " VALUES(@ClientID,@FirstName,@LastName,@PhoneNumber, @Email) "; cmd.Parameters.AddWithValue("@ClientID", cli.CLientID); cmd.Parameters.AddWithValue("@FirstName", cli.FirstName); cmd.Parameters.AddWithValue("@LastName", cli.LastName); cmd.Parameters.AddWithValue("@PhoneNumber", cli.PhoneNumber); cmd.Parameters.AddWithValue("@Email", cli.Email); cmd.ExecuteNonQuery(); connDB.Close(); }
public static List <Client> SearchRecord(int option, string name) { List <Client> listcli = new List <Client>(); using (SqlConnection connDB = UtilityDB.ConnectDB()) { switch (option) { case 1: // search by FirstName SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "SELECT * FROM ClientDB " + "WHERE FirstName = @FirstName "; cmd.Parameters.AddWithValue("@FirstName", name); SqlDataReader reader = cmd.ExecuteReader(); Client cli; if (reader.HasRows) { while (reader.Read()) { cli = new Client(); cli.CLientID = Convert.ToInt32(reader["CLientID"]); cli.FirstName = reader["FirstName"].ToString(); cli.LastName = reader["LastName"].ToString(); cli.PhoneNumber = reader["PhoneNumber"].ToString(); cli.Email = reader["Email"].ToString(); listcli.Add(cli); } } else { listcli = null; } break; } } return(listcli); }
public static void UpdateRecord(Client cli) { using (SqlConnection connDB = UtilityDB.ConnectDB()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = connDB; cmd.CommandText = "UPDATE ClientDB " + " set FirstName = @FirstName, " + " LastName = @LastName, " + " PhoneNumber = @PhoneNumber " + " Email = @Email " + " WHERE CLientID = @CLientID "; cmd.Parameters.AddWithValue("@CLientID", cli.CLientID); cmd.Parameters.AddWithValue("@FirstName", cli.FirstName); cmd.Parameters.AddWithValue("@LastName", cli.LastName); cmd.Parameters.AddWithValue("@PhoneNumber", cli.PhoneNumber); cmd.Parameters.AddWithValue("@Email", cli.Email); cmd.ExecuteNonQuery(); } }
public static bool IsUniqueId(int cliId) { //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 ClientDB " + " WHERE ClientID= " + cliId; int id = Convert.ToInt32(cmd.ExecuteScalar()); MessageBox.Show(id.ToString()); // debug code if (id != 0) { return(false); } // true if unique return(true); }
public static List <Client> ListRecord() { List <Client> listEmp = new List <Client>(); SqlConnection connDB = UtilityDB.ConnectDB(); SqlCommand cmd = new SqlCommand("SELECT * FROM CLientDB", connDB); SqlDataReader reader = cmd.ExecuteReader(); Client cli; while (reader.Read()) { cli = new Client(); cli.CLientID = Convert.ToInt32(reader["CLientID"]); cli.FirstName = reader["FirstName"].ToString(); cli.LastName = reader["LastName"].ToString(); cli.PhoneNumber = reader["PhoneNumber"].ToString(); cli.Email = reader["Email"].ToString(); listEmp.Add(cli); } return(listEmp); }