public static void DeleteSupplier(int p_SupplierId)
{
SQLiteConnection con = Connection.GetConnection();
SQLiteCommand command = new SQLiteCommand(con);
command.CommandText = string.Format(
"SELECT {0} FROM {1} " +
"WHERE {2} = @2",
ToolConstants.DB_SUPPLIER_ADRESS_ID,
ToolConstants.DB_SUPPLIER_TABLE,
ToolConstants.DB_SUPPLIER_ID);
command.Parameters.Add("@2", System.Data.DbType.Int32).Value = p_SupplierId;
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
int adressId = reader.GetInt32(0);
AdressMapper.DeleteAdress(adressId);
}
command.CommandText = string.Format(
"DELETE FROM {0} " +
"WHERE {1} = @1",
ToolConstants.DB_SUPPLIER_TABLE,
ToolConstants.DB_SUPPLIER_ID);
command.Parameters.Add("@1", System.Data.DbType.Int32).Value = p_SupplierId;
command.ExecuteNonQuery();
}
public static Supplier GetSupplierById(int p_SupplierId)
{
SQLiteConnection con = Connection.GetConnection();
SQLiteCommand command = new SQLiteCommand(con);
command.CommandText = string.Format(
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} FROM {8}" +
"WHERE {0} = @0",
ToolConstants.DB_SUPPLIER_ID,
ToolConstants.DB_SUPPLIER_LAST_NAME,
ToolConstants.DB_SUPPLIER_FIRST_NAME,
ToolConstants.DB_SUPPLIER_ADRESS_ID,
ToolConstants.DB_SUPPLIER_BIRTHDAY,
ToolConstants.DB_SUPPLIER_PHONE,
ToolConstants.DB_SUPPLIER_COMPANY,
ToolConstants.DB_SUPPLIER_EMAIL,
ToolConstants.DB_SUPPLIER_TABLE);
command.Parameters.Add("@0", System.Data.DbType.Int32).Value = p_SupplierId;
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
Supplier result = new Supplier();
result.Id = Convert.ToInt32(reader.GetValue(0));
result.LastName = Convert.ToString(reader.GetValue(1));
result.FirstName = Convert.ToString(reader.GetValue(2));
result.Adress = AdressMapper.GetAdressById(reader.GetInt32(3));
result.Birthday = Convert.ToDateTime(reader.GetValue(4));
result.Phone = Convert.ToString(reader.GetValue(5));
result.Company = Convert.ToString(reader.GetValue(6));
result.Email = Convert.ToString(reader.GetValue(7));
return(result);
}
else
{
return(null);
}
}
public static ObservableCollection <Producer> GetAllProducers()
{
ObservableCollection <Producer> result =
new ObservableCollection <Producer>();
SQLiteConnection con = Connection.GetConnection();
SQLiteCommand command = new SQLiteCommand(con);
//Build SQLite query
command.CommandText = string.Format(
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} FROM {8}",
ToolConstants.DB_PRODUCER_ID,
ToolConstants.DB_PRODUCER_LAST_NAME,
ToolConstants.DB_PRODUCER_FIRST_NAME,
ToolConstants.DB_PRODUCER_ADRESS_ID,
ToolConstants.DB_PRODUCER_BIRTHDAY,
ToolConstants.DB_PRODUCER_PHONE,
ToolConstants.DB_PRODUCER_COMPANY,
ToolConstants.DB_PRODUCER_EMAIL,
ToolConstants.DB_PRODUCER_TABLE);
SQLiteDataReader reader = command.ExecuteReader();
//Read result
while (reader.Read())
{
Producer producer = new Producer();
producer.Id = Convert.ToInt32(reader.GetValue(0));
producer.LastName = Convert.ToString(reader.GetValue(1));
producer.FirstName = Convert.ToString(reader.GetValue(2));
producer.Adress = AdressMapper.GetAdressById(reader.GetInt32(3));
producer.Birthday = Convert.ToDateTime(reader.GetValue(4));
producer.Phone = Convert.ToString(reader.GetValue(5));
producer.Company = Convert.ToString(reader.GetValue(6));
producer.Email = Convert.ToString(reader.GetValue(7));
result.Add(producer);
}
return(result);
}
/// <summary>
/// Saves an supplier to a database. If the supplier already exists,
/// its values are updated. This function also calls the function
/// for saving the suppliers adress.
/// </summary>
/// <param name="p_Supplier">Supplier to save</param>
public static void SaveSupplier(Supplier p_Supplier)
{
SQLiteConnection con = Connection.GetConnection();
SQLiteCommand command = new SQLiteCommand(con);
bool alreadyExists = false;
command.CommandText = string.Format(
"SELECT {0} FROM {1} " +
"WHERE {0} = @0",
ToolConstants.DB_SUPPLIER_ID,
ToolConstants.DB_SUPPLIER_TABLE);
command.Parameters.Add("@0", System.Data.DbType.Int32).Value = p_Supplier.Id;
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
alreadyExists = true;
}
reader.Close();
if (alreadyExists) //update values
{
command.CommandText = string.Format(
"UPDATE {0} SET " +
"{1} = @1, " +
"{2} = @2, " +
"{3} = @3, " +
"{4} = @4, " +
"{5} = @5, " +
"{6} = @6, " +
"{7} = @7 " +
"WHERE {8} = @8",
ToolConstants.DB_SUPPLIER_TABLE,
ToolConstants.DB_SUPPLIER_LAST_NAME,
ToolConstants.DB_SUPPLIER_FIRST_NAME,
ToolConstants.DB_SUPPLIER_ADRESS_ID,
ToolConstants.DB_SUPPLIER_BIRTHDAY,
ToolConstants.DB_SUPPLIER_PHONE,
ToolConstants.DB_SUPPLIER_COMPANY,
ToolConstants.DB_SUPPLIER_EMAIL,
ToolConstants.DB_SUPPLIER_ID);
command.Parameters.Add("@1", System.Data.DbType.String).Value = p_Supplier.LastName;
command.Parameters.Add("@2", System.Data.DbType.String).Value = p_Supplier.FirstName;
command.Parameters.Add("@3", System.Data.DbType.Int32).Value = p_Supplier.Adress.Id;
command.Parameters.Add("@4", System.Data.DbType.DateTime).Value = p_Supplier.Birthday;
command.Parameters.Add("@5", System.Data.DbType.String).Value = p_Supplier.Phone;
command.Parameters.Add("@6", System.Data.DbType.String).Value = p_Supplier.Company;
command.Parameters.Add("@7", System.Data.DbType.String).Value = p_Supplier.Email;
command.Parameters.Add("@8", System.Data.DbType.Int32).Value = p_Supplier.Id;
command.ExecuteNonQuery();
AdressMapper.SaveAdress(p_Supplier.Adress);
}
else //not exising --> create new one
{
var adressId = AdressMapper.SaveAdress(p_Supplier.Adress);
p_Supplier.Adress.Id = adressId;
command.CommandText = string.Format(
"INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}, {7}) " +
"VALUES (@1, @2, @3, @4, @5, @6, @7)",
ToolConstants.DB_SUPPLIER_TABLE,
ToolConstants.DB_SUPPLIER_LAST_NAME,
ToolConstants.DB_SUPPLIER_FIRST_NAME,
ToolConstants.DB_SUPPLIER_ADRESS_ID,
ToolConstants.DB_SUPPLIER_BIRTHDAY,
ToolConstants.DB_SUPPLIER_PHONE,
ToolConstants.DB_SUPPLIER_COMPANY,
ToolConstants.DB_SUPPLIER_EMAIL);
command.Parameters.Add("@1", System.Data.DbType.String).Value = p_Supplier.LastName;
command.Parameters.Add("@2", System.Data.DbType.String).Value = p_Supplier.FirstName;
command.Parameters.Add("@3", System.Data.DbType.Int32).Value = p_Supplier.Adress.Id;
command.Parameters.Add("@4", System.Data.DbType.DateTime).Value = p_Supplier.Birthday;
command.Parameters.Add("@5", System.Data.DbType.String).Value = p_Supplier.Phone;
command.Parameters.Add("@6", System.Data.DbType.String).Value = p_Supplier.Company;
command.Parameters.Add("@7", System.Data.DbType.String).Value = p_Supplier.Email;
command.ExecuteNonQuery();
}
}
public static ObservableCollection <ProductListData> GetAllProductListData()
{
var result = new ObservableCollection <ProductListData>();
SQLiteConnection con = Connection.GetConnection();
SQLiteCommand command = new SQLiteCommand(con);
command.CommandText = string.Format(
"SELECT {0}.{1}, {0}.{2}, {0}.{3}, {0}.{4}, {0}.{5}, {0}.{6}, {0}.{7}, {0}.{8}, {0}.{9}, {0}.{10}, {0}.{11}, {0}.{12}, " +
"{13}.{14}, {13}.{15}, {13}.{16}, {13}.{17}, {13}.{18}, " +
"{19}.{20}, {19}.{21}, {19}.{22}, {19}.{23}, {19}.{24} FROM {0} " +
"INNER JOIN {13} ON {13}.{14} = {0}.{5} " +
"INNER JOIN {19} ON {19}.{20} = {0}.{6}",
ToolConstants.DB_PRODUCT_TABLE,
ToolConstants.DB_PRODUCT_ID,
ToolConstants.DB_PRODUCT_AMOUNT,
ToolConstants.DB_PRODUCT_SORT,
ToolConstants.DB_PRODUCT_ORIGIN,
ToolConstants.DB_PRODUCT_PRODUCER_ID,
ToolConstants.DB_PRODUCT_SUPPLIER_ID,
ToolConstants.DB_PRODUCT_CATEGORY,
ToolConstants.DB_PRODUCT_PURCHASEDATE,
ToolConstants.DB_PRODUCT_EXPIRATION,
ToolConstants.DB_PRODUCT_MATURE,
ToolConstants.DB_PRODUCT_PURCHASEPRICE,
ToolConstants.DB_PRODUCT_SALESPRICE,
ToolConstants.DB_PRODUCER_TABLE,
ToolConstants.DB_PRODUCER_ID,
ToolConstants.DB_PRODUCER_LAST_NAME,
ToolConstants.DB_PRODUCER_FIRST_NAME,
ToolConstants.DB_PRODUCER_COMPANY,
ToolConstants.DB_PRODUCER_ADRESS_ID,
ToolConstants.DB_SUPPLIER_TABLE,
ToolConstants.DB_SUPPLIER_ID,
ToolConstants.DB_SUPPLIER_LAST_NAME,
ToolConstants.DB_SUPPLIER_FIRST_NAME,
ToolConstants.DB_SUPPLIER_COMPANY,
ToolConstants.DB_SUPPLIER_ADRESS_ID);
var reader = command.ExecuteReader();
while (reader.Read())
{
var data = new ProductListData();
var product = new Product();
var producer = new Producer();
var supplier = new Supplier();
product.Id = reader.GetInt32(0);
product.Amount = reader.GetInt32(1);
product.Sort = reader.GetString(2);
product.Origin = reader.GetString(3);
product.ProducerId = reader.GetInt32(4);
product.SupplierId = reader.GetInt32(5);
product.Category = reader.GetString(6);
product.PurchaseDate = reader.GetDateTime(7);
product.Expiration = reader.GetDateTime(8);
product.Mature = reader.GetInt32(9);
product.PurchasePrice = reader.GetDouble(10);
product.SalesPrice = reader.GetDouble(11);
producer.Id = reader.GetInt32(12);
producer.LastName = reader.GetString(13);
producer.FirstName = reader.GetString(14);
producer.Company = reader.GetString(15);
producer.Adress = AdressMapper.GetAdressById(reader.GetInt32(16));
supplier.Id = reader.GetInt32(17);
supplier.LastName = reader.GetString(18); //hier schmiert der ab
supplier.FirstName = reader.GetString(19);
supplier.Company = reader.GetString(20);
supplier.Adress = AdressMapper.GetAdressById(reader.GetInt32(21));
data.Product = product;
data.Producer = producer;
data.Supplier = supplier;
result.Add(data);
}
return(result);
}