private void btnSave_Click(object sender, System.Windows.RoutedEventArgs e)
{
try
{
if (customerBackup == null)
{
customerBackup = data.NewCustomer(customer.Name, customer.Street, customer.Number,
customer.City, customer.Psc, customer.Phone);
customer.Id = customerBackup.Id;
}
else
{
data.UpdateCustomer(customer, customerBackup);
customerBackup.Name = customer.Name;
customerBackup.Street = customer.Street;
customerBackup.Number = customer.Number;
customerBackup.City = customer.City;
customerBackup.Psc = customer.Psc;
customerBackup.Phone = customer.Phone;
customerBackup.Active = customer.Active;
}
}
catch
{
}
}
public void DeleteCustomer(Customer customer)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
connection.Open();
// delete record from table
DeleteCustomer(connection, customer);
}
}
private List<Order> GetOrders(OracleConnection connection, Customer customer, RecordInfo info)
{
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "select count(*) from tis_zakazka z where z.zakaznik_id = :1 order by z.datum_objednavky";
cmd.Parameters.Add(GetNumber(":1", customer.Id));
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
info.Total = reader.GetInt32(0);
reader.Close();
cmd.Dispose();
List<Order> orders = new List<Order>();
// select record from table
cmd = connection.CreateCommand();
cmd.CommandText = "select * from " +
"(select rownum rn, z.* from tis_zakazka z where z.zakaznik_id = :1 order by z.datum_objednavky)" +
" where rn >= :2 and rn < :3";
cmd.Parameters.Add(GetNumber(":1", customer.Id));
cmd.Parameters.Add(GetNumber(":2", info.Start));
cmd.Parameters.Add(GetNumber(":3", info.Start + info.Count));
reader = cmd.ExecuteReader();
while (reader.Read())
{
Nullable<DateTime> dateShipment = null;
if (!reader.IsDBNull(4))
dateShipment = reader.GetDateTime(4);
Order order = GetOrder(reader.GetInt32(1), reader.GetInt32(2), reader.GetDateTime(3),
dateShipment, reader.GetString(5));
order.Customer = customer;
orders.Add(order);
}
reader.Close();
cmd.Dispose();
foreach (Order order in orders)
order.TotalPrice = GetOrderTotalPrice(connection, order.Id);
return orders;
}
private void DeleteCustomer(OracleConnection connection, Customer customer)
{
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "delete from tis_zakaznik where zakaznik_id = :1";
cmd.Parameters.Add(GetNumber(":1", customer.Id));
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public void UpdateCustomer(Customer newCustomer, Customer oldCustomer)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
connection.Open();
// update record in table
UpdateCustomer(connection, newCustomer, oldCustomer);
}
}
public Order NewOrder(Customer customer)
{
Order order = null;
using (OracleConnection connection = new OracleConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
connection.Open();
// insert record to table
int orderId = NewOrder(connection, customer);
// select record from table
order = GetOrder(connection, orderId);
}
return order;
}
public List<Order> GetOrders(Customer customer, RecordInfo info)
{
List<Order> orderList = new List<Order>();
using (OracleConnection connection = new OracleConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
connection.Open();
// select records from table
orderList = GetOrders(connection, customer, info);
}
return orderList;
}
private void UpdateCustomer(OracleConnection connection, Customer newCustomer, Customer oldCustomer)
{
if (newCustomer.Id != oldCustomer.Id) // probably different customers - wrong input
return;
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "update tis_zakaznik set ";
cmd.CommandType = CommandType.Text;
int paramIdx = 1;
paramIdx = AddUpdateField(cmd, "jmeno", paramIdx, CheckForInjection(newCustomer.Name), oldCustomer.Name);
paramIdx = AddUpdateField(cmd, "ulice", paramIdx, CheckForInjection(newCustomer.Street), oldCustomer.Street);
paramIdx = AddUpdateField(cmd, "cislo", paramIdx, newCustomer.Number, oldCustomer.Number);
paramIdx = AddUpdateField(cmd, "mesto", paramIdx, CheckForInjection(newCustomer.City), oldCustomer.City);
paramIdx = AddUpdateField(cmd, "psc", paramIdx, newCustomer.Psc, oldCustomer.Psc);
paramIdx = AddUpdateField(cmd, "telefon", paramIdx, CheckForInjection(newCustomer.Phone), oldCustomer.Phone);
paramIdx = AddUpdateField(cmd, "aktivni", paramIdx, newCustomer.Active, oldCustomer.Active);
if (paramIdx > 1)
{
cmd.CommandText += " where zakaznik_id = :" + paramIdx.ToString();
cmd.Parameters.Add(GetNumber(":" + paramIdx.ToString(), newCustomer.Id));
cmd.ExecuteNonQuery();
}
cmd.Dispose();
}
private int NewOrder(OracleConnection connection, Customer customer)
{
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "TIS.nova_zakazka";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter res = cmd.Parameters.Add("result", OracleType.Number);
res.Direction = ParameterDirection.ReturnValue;
OracleParameter par = cmd.Parameters.Add("p_zakaznik_id", OracleType.Number);
par.Value = customer.Id;
cmd.ExecuteNonQuery();
int orderId = Convert.ToInt32(res.Value);
cmd.Dispose();
return orderId;
}
private void EditCustomer(Customer editCustomer)
{
customer = new Customer();
if (editCustomer != null)
{
customer.Id = editCustomer.Id;
customer.Name = editCustomer.Name;
customer.Street = editCustomer.Street;
customer.Number = editCustomer.Number;
customer.City = editCustomer.City;
customer.Psc = editCustomer.Psc;
customer.Phone = editCustomer.Phone;
customer.Active = editCustomer.Active;
}
gridEdit.DataContext = customer;
}