public Customer Select(int id) { SchoolDatabase db = new SchoolDatabase(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT_BY_ID); command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int)); command.Parameters["@p_id"].Value = id; SqlDataReader reader = db.Select(command); try { reader.Read(); Customer cust = new Customer(); cust.id = reader.GetInt32(0); cust.email = reader.GetString(1); cust.fname = reader.GetString(2); cust.lname = reader.GetString(3); cust.phone = reader.GetString(4); cust.adress = reader.GetString(5); cust.city = reader.GetString(6); cust.postaCode = reader.GetInt32(7); reader.Close(); db.Close(); return cust; } catch { return null; } }
//public int Delete(int id) //{ // Database db = new Database(connectionString); // db.Connect(); // SqlCommand command = db.CreateCommand(SQL_DELETE); // command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int)); // command.Parameters["@p_id"].Value = id; // int ret = db.ExecuteNonQuery(command); // db.Close(); // return ret; //} public int Insert(Customer cust) { if (!FindCustomerByEmail(cust.email)) { SchoolDatabase db = new SchoolDatabase(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_INSERT); PrepareCommand(command, cust); int ret = db.ExecuteNonQuery(command); db.Close(); return ret; } else return -2; }
private void PrepareCommand(SqlCommand command, Customer cust) { command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int)); command.Parameters["@p_id"].Value = cust.id; command.Parameters.Add(new SqlParameter("@p_email", SqlDbType.VarChar, 50)); command.Parameters["@p_email"].Value = cust.email; command.Parameters.Add(new SqlParameter("@p_fname", SqlDbType.VarChar, 20)); command.Parameters["@p_fname"].Value = cust.fname; command.Parameters.Add(new SqlParameter("@p_lname", SqlDbType.VarChar, 20)); command.Parameters["@p_lname"].Value = cust.lname; command.Parameters.Add(new SqlParameter("@p_phone", SqlDbType.VarChar, 15)); command.Parameters["@p_phone"].Value = cust.phone; command.Parameters.Add(new SqlParameter("@p_adress", SqlDbType.VarChar, 20)); command.Parameters["@p_adress"].Value = cust.adress; command.Parameters.Add(new SqlParameter("@p_city", SqlDbType.VarChar, 20)); command.Parameters["@p_city"].Value = cust.city; command.Parameters.Add(new SqlParameter("@p_postal", SqlDbType.Int)); command.Parameters["@p_postal"].Value = cust.postaCode; }
private List<Customer> Read(SqlDataReader reader) { List<Customer> custs = new List<Customer>(); while (reader.Read()) { Customer cust = new Customer(); cust.id = reader.GetInt32(0); cust.email = reader.GetString(1); cust.fname = reader.GetString(2); cust.lname = reader.GetString(3); cust.phone = reader.GetString(4); cust.adress = reader.GetString(5); cust.city = reader.GetString(6); cust.postaCode = reader.GetInt32(7); custs.Add(cust); } return custs; }
public int Update(Customer cust) { SchoolDatabase db = new SchoolDatabase(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_UPDATE); PrepareCommand(command, cust); int ret = db.ExecuteNonQuery(command); db.Close(); return ret; }