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