public static void UploadVendorsToSQL(ICollection<Vendor> vendors, SupermarketChainContext context)
{
foreach (var vendor in vendors)
{
var vendorSQL = new SuperMarketChain.Model.Vendor()
{
VendorName = vendor.VendorName
};
if (context.Vendors.Where(v => v.VendorName.Equals(vendorSQL.VendorName)).Count() == 0)
{
context.Vendors.Add(vendorSQL);
Console.WriteLine("Vendor " + vendorSQL.VendorName + " will be uploaded to SQL db." + vendorSQL.VendorName.Count());
}
else
{
Console.WriteLine("Vendor " + vendorSQL.VendorName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
public static void UploadMeasuresToSQL(ICollection<Measure> measures, SupermarketChainContext context)
{
foreach (var measure in measures)
{
var measureSQL = new SuperMarketChain.Model.Measure()
{
ID = measure.Id,
MeasureName = measure.MeasureName
};
if (context.Measures.Where(m => m.MeasureName.Equals(measureSQL.MeasureName)).Count() == 0)
{
context.Measures.Add(measureSQL);
Console.WriteLine("Measure " + measureSQL.MeasureName + " will be uploaded to SQL db.");
}
else
{
Console.WriteLine("Measure " + measureSQL.MeasureName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
public static void UploadProductsToSQL(ICollection<Product> products, SupermarketChainContext context)
{
foreach (var product in products)
{
var vendorName = GetVendorName("EVGENI-PC", "admin", "1111", product.VendorId);
var vendorId = context.Vendors.Where(v => v.VendorName.Equals(vendorName)).Select(v => v.ID).FirstOrDefault();
var productSQL = new SuperMarketChain.Model.Product()
{
VendorId = vendorId,
ProductName = product.ProductName,
MeasureID = product.MeasureId,
Price = product.Price
};
if (context.Products.Where(p => p.ProductName.Equals(productSQL.ProductName)).Count() == 0)
{
context.Products.Add(productSQL);
Console.WriteLine("Product " + productSQL.ProductName + " will be uploaded to SQL db");
}
else
{
Console.WriteLine("Product " + productSQL.ProductName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
private void readExcell(string path)
{
decimal decNumber;
double doubNumber;
var context = new SupermarketChainContext();
string title = "";
bool isTitle = false;
bool isProd = false;
string conString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(conString))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sales$]", connection);
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
using (OleDbDataReader dr = command.ExecuteReader())
{
isTitle = false;
isProd = false;
int counter = 0;
while (dr.Read())
{
var row1Col0 = dr[counter];
var row1Col1 = dr[counter + 1];
var row1Col2 = dr[counter + 2];
var row1Col3 = dr[counter + 3];
if (isTitle == false)
{
title = dr[counter].ToString();
isTitle = true;
if (vendorExist(dr[counter].ToString(), context) == false)
{
context.Vendors.Add(new Vendor
{
VendorName = title
});
context.SaveChanges();
}
continue;
}
else if (dr[counter].ToString() == "Total sum:")
{
continue;
}
if (isProd == false)
{
isProd = true;
continue;
}
Decimal.TryParse(dr[counter + 2].ToString(), out decNumber);
int id = vendorId(title, context);
if (productExist(dr[counter].ToString(), decNumber, id) == false)
{
context.Products.Add(new Product
{
ProductName = dr[counter].ToString(),
MeasureID = 1,
Price = decNumber,
VendorId = id
});
}
Double.TryParse(dr[counter + 1].ToString(), out doubNumber);
if (saleExist(productId(dr[counter].ToString(), context), doubNumber, date, id) == false)
{
context.SaleReports.Add(new SaleReport
{
ProductId = productId(dr[counter].ToString(), context),
Quantity = doubNumber,
SaleTime = date,
VendorId = id
});
}
context.SaveChanges();
}
}
dbContextTransaction.Commit();
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
}
}
}
}