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();
}
}
/// <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);
}
/// <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);
}
public JsonResult Create(Test test)
{
using (SQLEntities entities = new SQLEntities())
{
entities.Tests.Add(test);
entities.SaveChanges();
}
return(Json(test));
}
public void Report(DateTime startDate, DateTime endDate)
{
Console.WriteLine("Started creating JSON reports...");
if (!Directory.Exists(ReportsPath))
{
Console.WriteLine("Create Sales reports folder.");
Directory.CreateDirectory(ReportsPath);
}
var db = new SQLEntities();
var reports = GetJsonReport(db, startDate, endDate);
SaveReportsToFiles(reports);
SaveReportToMongoDb(reports);
}
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);
}
/// <summary>
/// 返回所有整个表数据
/// </summary>
/// <returns></returns>
public List <SUBCOMPANYMST> SelectAll()
{
List <SUBCOMPANYMST> list = new List <SUBCOMPANYMST>();
try
{
SQLEntities context = new SQLEntities();
//添加所有记录
list.AddRange(context.SUBCOMPANYMST.ToList());
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(list);
}
/// <summary>
/// 返回所有整个表数据
/// </summary>
/// <returns></returns>
public List <PatrolReportDetail> SelectAll()
{
List <PatrolReportDetail> list = new List <PatrolReportDetail>();
try
{
SQLEntities context = new SQLEntities();
//添加所有记录
list.AddRange(context.PatrolReportDetail.ToList());
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(list);
}
/// <summary>
/// 根据查询条件查询数据集合
/// </summary>
/// <param name="searchInfo"></param>
/// <returns></returns>
public List <PatrolReportDetail> SearchByCondition(SearchInfo searchInfo)
{
List <PatrolReportDetail> list = new List <PatrolReportDetail>();
try
{
SQLEntities context = new SQLEntities();
list.AddRange(context.PatrolReportDetail.Where(searchInfo.WhereExpress, searchInfo.Parameters.ToArray()).ToList());
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(list);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolCodeMst Select(PatrolCodeMst searchInfo)
{
PatrolCodeMst instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolCodeMst.Where("it.CodeTypeCD=@CodeTypeCD and it.CodeCD =@CodeCD", new ObjectParameter("CodeTypeCD", searchInfo.CodeTypeCD), new ObjectParameter("CodeCD", searchInfo.CodeCD)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolReportHeader Select(PatrolReportHeader searchInfo)
{
PatrolReportHeader instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolReportHeader.Where("it.PatrolNO=@PatrolNO", new ObjectParameter("PatrolNO", searchInfo.PatrolNO)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(" 查询特巡异常：" + ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolServerList Select(PatrolServerList searchInfo)
{
PatrolServerList instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolServerList.Where("it.Address=@Address", new ObjectParameter("Address", searchInfo.Address)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolSpotParts Select(PatrolSpotParts searchInfo)
{
PatrolSpotParts instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolSpotParts.Where("it.ID=@ID", new ObjectParameter("ID", searchInfo.ID)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolGenerateNO Select(PatrolGenerateNO searchInfo)
{
PatrolGenerateNO instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolGenerateNO.Where("it.PrefixCode=@PrefixCode", new ObjectParameter("PrefixCode", searchInfo.PrefixCode)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public SUBCOMPANYMST Select(SUBCOMPANYMST searchInfo)
{
SUBCOMPANYMST instance = null;
try
{
SQLEntities context = new SQLEntities();
//查询单个用户
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolReportDetail Select(PatrolReportDetail searchInfo)
{
PatrolReportDetail instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolReportDetail.Where("it.PatrolNO=@PatrolNO and it.SubNO =@SubNO", new ObjectParameter("PatrolNO", searchInfo.PatrolNO), new ObjectParameter("SubNO", searchInfo.SubNO)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
/// <summary>
/// 根据查询条件查询单条记录
/// </summary>
/// <param name="searchInfo">查询条件,主键查询</param>
/// <returns></returns>
public PatrolUserInfo Select(PatrolUserInfo searchInfo)
{
PatrolUserInfo instance = null;
try
{
SQLEntities context = new SQLEntities();
instance = context.PatrolUserInfo.Where("it.UserCD=@UserCD", new ObjectParameter("UserCD", searchInfo.UserCD)).First();
context.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(instance);
}
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>
/// Get information from SQL database.
/// </summary>
/// <param name="db">SupermarketsChain database</param>
/// <param name="startDate">Start data</param>
/// <param name="endDate">End date</param>
/// <returns>Json Collection</returns>
private static IQueryable <SalesReport> GetJsonReport(SQLEntities db, DateTime startDate, DateTime endDate)
{
Console.WriteLine("Generating reports from the SQL Server database data...");
var report = db.Sales
.Include(e => e.Product)
.Include(e => e.Product.Vendor)
.Where(s => s.SoldOn >= startDate && s.SoldOn <= endDate)
.Select(x => new SalesReport
{
ProductId = x.ProductId,
ProductName = x.Product.Name,
VendorName = x.Product.Vendor.Name,
TotalQuantitySold = x.Quantity,
TotalIncomes = x.Quantity * x.Product.Price
});
if (report.Any() == false)
{
throw new ArgumentNullException(string.Format("No sales between {0} and {1}", startDate.ToShortDateString(), endDate.ToShortDateString()));
}
return(report);
}
public static DataTable getUserInfo(string usercd)
{
SQLEntities context = new SQLEntities();
DataTable dt = new DataTable();
if (usercd != null && usercd != String.Empty)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connectString))
{
conn.Open();
Console.WriteLine("连接成功...");
String sqlString = getUserSqlString(usercd);
Console.WriteLine(sqlString);
SqlDataAdapter adapter = new SqlDataAdapter(sqlString, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
scope.Complete();
dt = ds.Tables[0];
conn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
context.Dispose();
return(dt);
}
/// <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);
}
public static bool updatePatrol(string headerSql, string detailSql)
{
SQLEntities context = new SQLEntities();
bool success = false;
if (headerSql != null && headerSql != String.Empty && detailSql != null && detailSql != String.Empty)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connectString))
{
Console.WriteLine("连接成功...");
String sqlString = headerSql + detailSql;
Console.WriteLine(sqlString);
SqlCommand command = new SqlCommand(sqlString, conn);
conn.Open();
success = command.ExecuteNonQuery() > 0;
scope.Complete();
conn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
context.Dispose();
return(success);
}
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
}
}
}
}
// GET: Test
public ActionResult Index()
{
SQLEntities entities = new SQLEntities();
return(View(entities.Tests));
}
protected void Page_Init(Object sender, EventArgs e)
{
_entity = new SQLEntities();
}