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