/// <summary>
/// 根据主键删除实例
/// </summary>
/// <param name="oldEntity">设置了主键Id的对象</param>
/// <returns></returns>
public bool Delete(SUBCOMPANYMST entity)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
//删除操作
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
/// <summary>
/// 新增对象
/// </summary>
/// <param name="entity">实体</param>
/// <returns>true=成功;false=失败</returns>
public bool Insert(PatrolReportDetail entity)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
context.PatrolReportDetail.AddObject(entity);
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
/// <summary>
/// 根据更新列表更新实体
/// </summary>
/// <param name="entity">实体</param>
/// <param name="updateKeys">更新字段列表 键值对</param>
/// <returns>true=成功,false=失败</returns>
public bool Update(CUSTMST entity, Hashtable updateKeys)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
//更新操作
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
static void Main(string[] args)
{
XmlDocument xml = new XmlDocument();
xml.Load(@"C:\Users\Darko\Documents\Visual Studio 2013\Projects\DbFirstTest\Data\Sample-Vendor-Expenses.xml");
XmlNodeList Vendors = xml.DocumentElement.SelectNodes("/expenses-by-month/vendor");
var dbContext = new SQLEntities();
foreach (XmlNode item in Vendors)
{
string vendorName = item.Attributes["name"].Value;
var addVendor = dbContext.Vendors.Add(new Vendor {
Name = vendorName
});
foreach (XmlNode expens in item.ChildNodes)
{
var vendorId = addVendor.Id;
DateTime expenseMonth = DateTime.Parse(expens.Attributes["month"].Value);
decimal expensePrice = Convert.ToDecimal(expens.InnerText);
Console.WriteLine("Vendor: " + vendorId + "Expense: " + expensePrice + ", " + "Expense Month: " + expenseMonth);
dbContext.Expenses.Add(new Expenses {
ExpenseDate = expenseMonth, VendorId = vendorId, Expense = expensePrice
});
}
dbContext.SaveChanges();
}
}
public JsonResult Create(Test test)
{
using (SQLEntities entities = new SQLEntities())
{
entities.Tests.Add(test);
entities.SaveChanges();
}
return(Json(test));
}
public static bool InsertPatrol(PatrolReportHeader header, List <PatrolReportDetail> detailList)
{
SQLEntities context = new SQLEntities();
bool success = false;
if (header != null && detailList != null && detailList.Count > 0)
{
using (TransactionScope trans = new TransactionScope())
{
try
{
context.PatrolReportHeader.AddObject(header);
foreach (PatrolReportDetail item in detailList)
{
context.PatrolReportDetail.AddObject(item);
}
trans.Complete();
success = true;
}
catch (Exception ex)
{
MyLog.Error("{{" + header.Reporter + "}}新建特巡报告错误" + ex.Message);
Console.WriteLine(ex.Message);
}
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
MyLog.Log("{{" + header.Reporter + "}}保存特巡报告成功");
Console.WriteLine("保存特巡报告成功");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("保存特巡报告异常");
MyLog.Error("{{" + header.Reporter + "}}保存特巡报告错误" + ex.Message);
success = false;
}
context.Dispose();
return(success);
}
public static bool InsertUser(PatrolUserInfo newuser)
{
SQLEntities context = new SQLEntities();
bool success = false;
if (newuser != null)
{
using (TransactionScope trans = new TransactionScope())
{
try
{
context.PatrolUserInfo.AddObject(newuser);
Console.WriteLine("新增对象完毕");
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine("新增对象异常" + ex.Message);
}
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
/// <summary>
/// 根据更新列表更新实体
/// </summary>
/// <param name="entity">实体</param>
/// <param name="updateKeys">更新字段列表 键值对</param>
/// <returns>true=成功,false=失败</returns>
public bool Update(PatrolReportHeader entity, Hashtable updateKeys)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
Console.WriteLine(entity.PatrolNO);
PatrolReportHeader instance = context.PatrolReportHeader.Where("it.PatrolNO=@PatrolNO", new ObjectParameter("PatrolNO", entity.PatrolNO)).First();
//更新数据操作
SetUpdateValue(instance, updateKeys);
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
/// <summary>
/// 根据主键删除实例
/// </summary>
/// <param name="oldEntity">设置了主键Id的对象</param>
/// <returns></returns>
public bool Delete(PatrolReportDetail entity)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
PatrolReportDetail instance = context.PatrolReportDetail.Where("it.PatrolNO=@PatrolNO and it.SubNO =@SubNO", new ObjectParameter("PatrolNO", entity.PatrolNO), new ObjectParameter("SubNO", entity.SubNO)).First();
//标记删除
context.PatrolReportDetail.DeleteObject(instance);
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
/// <summary>
/// 根据主键删除实例
/// </summary>
/// <param name="oldEntity">设置了主键Id的对象</param>
/// <returns></returns>
public bool Delete(PatrolUserInfo entity)
{
SQLEntities context = new SQLEntities();
bool success = false;
using (TransactionScope trans = new TransactionScope())
{
try
{
PatrolUserInfo instance = context.PatrolUserInfo.Where("it.UserCD=@UserCD", new ObjectParameter("UserCD", entity.UserCD)).First();
//标记删除
context.PatrolUserInfo.DeleteObject(instance);
trans.Complete();
success = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
try
{
if (success)
{
//提交保存
context.SaveChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
context.Dispose();
return(success);
}
protected void btnSavePipe_Click(object sender, EventArgs e)
{
this.ShowPipe();
foreach (ListViewDataItem item in lstPipe.Items)
{
Literal litID = (Literal)item.FindControl("litID");
TextBox txtName = (TextBox)item.FindControl("txtName");
TextBox txtPrice = (TextBox)item.FindControl("txtPrice");
int ID = Convert.ToInt32(litID.Text);
tPrice price = _entity.tPrice.Where(w => w.ID == ID).FirstOrDefault();
if (txtPrice.Text.Trim() != price.Price || txtName.Text.Trim() != price.Name)
{
price.Name = txtName.Text.Trim();
price.Price = txtPrice.Text.Trim();
_entity.tPrice.Add(price);
_entity.Entry(price).State = System.Data.Entity.EntityState.Modified;
_entity.SaveChanges();
}
}
btnAddPipe.Visible = true;
}
public static void Main()
{
Console.WriteLine("Please choose a zip file with reports:");
OpenFileDialog ofd = new OpenFileDialog();
ofd.ShowDialog();
var data = new Dictionary <DateTime, ICollection <Dictionary <string, ICollection <string[]> > > >();
using (ZipFile zip = new ZipFile(ofd.FileName))
{
var date = new DateTime();
foreach (var zipEntry in zip)
{
if (zipEntry.IsDirectory)
{
date = GetDateFromZipEntry(zipEntry);
}
else
{
InsertXlsData(zipEntry, data, date);
Console.WriteLine("Excel file to be added: " + zipEntry);
Console.WriteLine();
}
}
}
using (var db = new SQLEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
Console.WriteLine("MSSQL transaction started: ");
Console.WriteLine();
foreach (var dateKey in data.Keys)
{
foreach (var dateFolder in data[dateKey])
{
foreach (var supermarketXls in dateFolder)
{
var supermarketName = supermarketXls.Key;
if (db.Supermarkets.Any(p => p.Name == supermarketName))
{
foreach (var row in supermarketXls.Value)
{
var productName = row[0];
var quantity = int.Parse(row[1]);
var price = decimal.Parse(row[2]);
if (db.Products.Any(q => q.Name == productName && q.Price == price))
{
var newSale = new Sale
{
SoldOn = dateKey,
SupermarketId =
db.Supermarkets.First(p => p.Name == supermarketName).Id,
ProductId = db.Products.First(p => p.Name == productName &&
p.Price == price).Id,
Quantity = quantity
};
if (db.Sales.FirstOrDefault(p => p.SoldOn == newSale.SoldOn &&
p.SupermarketId == newSale.SupermarketId &&
p.ProductId == newSale.ProductId) == null)
{
db.Sales.Add(newSale);
db.SaveChanges();
}
else
{
Console.WriteLine("There is already imported .xls report for product {0} on date {1}, in supermarket {2}!",
db.Products.First(p => p.Id == newSale.ProductId).Name, newSale.SoldOn,
db.Supermarkets.First(p => p.Id == newSale.SupermarketId).Name);
Console.WriteLine();
}
}
else
{
var rnd = new Random();
var newVendor = new Vendor
{
Name = "New Vendor RandGen " + rnd.Next(1, int.MaxValue)
};
db.Vendors.Add(newVendor);
db.SaveChanges();
var newProduct = new Product
{
Name = productName,
Price = price,
VendorId = db.Vendors.First(p => p.Name == newVendor.Name).Id,
MeasurmentUnitId = rnd.Next(1, 4)
};
db.Products.Add(newProduct);
db.SaveChanges();
db.Sales.Add(new Sale
{
SoldOn = dateKey,
SupermarketId =
db.Supermarkets.First(p => p.Name == supermarketName).Id,
ProductId =
db.Products.First(p => p.Name == productName && p.Price == price)
.Id,
Quantity = quantity
});
db.SaveChanges();
}
}
}
else
{
var newSupermarket = new Supermarket
{
Name = supermarketName
};
db.Supermarkets.Add(newSupermarket);
db.SaveChanges();
foreach (var row in supermarketXls.Value)
{
var productName = row[0];
var quantity = int.Parse(row[1]);
var price = decimal.Parse(row[2]);
if (db.Products.Any(q => q.Name == productName && q.Price == price))
{
var newSale = new Sale
{
SoldOn = dateKey,
SupermarketId =
db.Supermarkets.First(p => p.Name == newSupermarket.Name).Id,
ProductId = db.Products.First(p => p.Name == productName &&
p.Price == price).Id,
Quantity = quantity
};
if (db.Sales.FirstOrDefault(p => p.SoldOn == newSale.SoldOn &&
p.SupermarketId == newSale.SupermarketId &&
p.ProductId == newSale.ProductId) == null)
{
db.Sales.Add(newSale);
db.SaveChanges();
}
else
{
Console.WriteLine("There is already imported .xls report for product {0} on date {1}, in supermarket {2}!",
db.Products.First(p => p.Id == newSale.ProductId).Name, newSale.SoldOn,
db.Supermarkets.First(p => p.Id == newSale.SupermarketId).Name);
Console.WriteLine();
}
}
else
{
var rnd = new Random();
var newVendor = new Vendor
{
Name = "New Vendor RandGen " + rnd.Next(1, int.MaxValue).ToString()
};
db.Vendors.Add(newVendor);
db.SaveChanges();
var newProduct = new Product
{
Name = productName,
Price = price,
VendorId = db.Vendors.First(p => p.Name == newVendor.Name).Id,
MeasurmentUnitId = rnd.Next(1, 4)
};
db.Products.Add(newProduct);
db.SaveChanges();
db.Sales.Add(new Sale
{
SoldOn = dateKey,
SupermarketId =
db.Supermarkets.First(p => p.Name == newSupermarket.Name).Id,
ProductId =
db.Products.First(p => p.Name == productName && p.Price == price)
.Id,
Quantity = quantity
});
db.SaveChanges();
}
}
}
}
}
}
Console.WriteLine("End of transaction, result:");
//throw new Exception(); for test purposes - what happens if something went wrong
db.SaveChanges();
dbContextTransaction.Commit();
Console.WriteLine("Data from zip file successfully added to MS SQL DB without duplicate entries");
Console.WriteLine();
}
catch (Exception e)
{
Console.WriteLine("Transaction failed, no changes in MSSQL made!\nThe data from the zip file couldn't be imported in the SQL Database. Exception: {0}", e.Message);
//dbContextTransaction.Rollback(); no need to rollback, because if exception occurs, transaction is not made
}
}
}
}