private static void MigrateProducts()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT P_ID, " +
"VENDOR_ID," +
"PRODUCT_NAME, " +
"MEASURE_ID, " +
"PRICE, " +
"PRODUCT_TYPE " +
"FROM PRODUCTS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastProduct = data.Products.All().OrderByDescending(p => p.Id).FirstOrDefault();
int dataId = 0;
if (lastProduct != null)
{
dataId = lastProduct.Id;
}
while (reader.Read())
{
int productId = int.Parse(reader["P_ID"].ToString());
if (dataId < productId)
{
Product product = new Product();
// for debugging
product.Id = productId;
product.VendorId = int.Parse(reader["VENDOR_ID"].ToString());
product.ProductName = reader["PRODUCT_NAME"].ToString();
product.MeasureId = int.Parse(reader["MEASURE_ID"].ToString());
product.Price = decimal.Parse(reader["PRICE"].ToString());
product.ProductType = (ProductType)Enum.Parse(typeof(ProductType), reader["PRODUCT_TYPE"].ToString());
data.Products.Add(product);
}
}
data.SaveChanges();
}
Close();
}
private static void MigrateMeasures()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT M_ID, MEASURE_NAME FROM MEASURE_UNITS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastMeasure = data.Measures.All().OrderByDescending(v => v.Id).FirstOrDefault();
int dataId = 0;
if (lastMeasure != null)
{
dataId = lastMeasure.Id;
}
while (reader.Read())
{
int measureId = int.Parse(reader["M_ID"].ToString());
if (dataId < measureId)
{
Measure measure = new Measure();
measure.Id = measureId;
measure.MeasureName = (string)reader["MEASURE_NAME"];
data.Measures.Add(measure);
}
}
data.SaveChanges();
}
Close();
}
private static void MigrateVendors()
{
SupermarketSystemData data = new SupermarketSystemData();
con = new OracleConnection {
ConnectionString = ConnectionString
};
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT V_ID,VENDOR_NAME FROM VENDORS";
using (OracleDataReader reader = cmd.ExecuteReader())
{
var lastVendor = data.Vendors.All().OrderByDescending(v => v.Id).FirstOrDefault();
int dataId = 0;
if (lastVendor != null)
{
dataId = lastVendor.Id;
}
while (reader.Read())
{
int vendorId = int.Parse(reader["V_ID"].ToString());
if (dataId < vendorId)
{
Vendor vendor = new Vendor();
vendor.Id = vendorId;
vendor.VendorName = (string)reader["VENDOR_NAME"];
data.Vendors.Add(vendor);
}
}
data.SaveChanges();
}
Close();
}
private void FillReportsInDatabase()
{
foreach (var connectionString in this.connectionStrings)
{
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
try
{
using (excelConnection)
{
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [Sales$]", excelConnection);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
Sale newSale = new Sale();
string[] conArgs = connectionString.Split('\\');
if (!string.IsNullOrEmpty(conArgs[1]))
{
DateTime date = this.ParseDate(conArgs[1]);
// Date of sale
newSale.Date = date;
}
DataRow headerRow = dataTable.Rows[0];
// Name of the store
string storeName = headerRow.ItemArray[0].ToString();
Store currentStore = data.Stores.Search(s => s.Name == storeName).FirstOrDefault();
if (currentStore == null)
{
var newStore = new Store {
Name = storeName
};
data.Stores.Add(newStore);
data.SaveChanges();
// TODO: To optimized !!!
currentStore = data.Stores.Search(s => s.Name == storeName).FirstOrDefault();
}
newSale.StoreId = currentStore.Id;
int reportsLength = dataTable.Rows.Count - 1;
for (int row = 2; row < reportsLength; row++)
{
DataRow dataRow = dataTable.Rows[row];
// Product name
string productName = dataRow.ItemArray[0].ToString();
if (!string.IsNullOrEmpty(productName))
{
var product = data.Products.Search(p => p.ProductName == productName).FirstOrDefault();
if (product != null)
{
newSale.ProductId = product.Id;
}
else
{
throw new ArgumentNullException("Product", "Not existing product!");
}
}
// Add sale product quantity
string quantityValue = dataRow.ItemArray[1].ToString();
if (!string.IsNullOrEmpty(quantityValue) && char.IsDigit(quantityValue[0]))
{
newSale.Quantity = int.Parse(quantityValue);
}
// Add sale product single price
string unitPriceValue = dataRow.ItemArray[2].ToString();
if (!string.IsNullOrEmpty(unitPriceValue) && char.IsDigit(unitPriceValue[0]))
{
newSale.SinglePrice = decimal.Parse(unitPriceValue);
}
// Add sale sum
string sumValue = dataRow.ItemArray[3].ToString();
if (!string.IsNullOrEmpty(sumValue) && char.IsDigit(sumValue[0]))
{
newSale.Sum = decimal.Parse(sumValue);
}
data.Sales.Add(newSale);
data.SaveChanges();
}
}
excelConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine("Read excel exception! {0}", ex);
}
}
}
