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;
}
