// <summary> // Constructs the DbContextTransaction object with the associated connection object // and with the given isolation level // </summary> // <param name="connection">The EntityConnection object owning this transaction </param> // <param name="isolationLevel">The database isolation level with which the underlying store transaction will be created</param> internal DbContextTransaction(EntityConnection connection, IsolationLevel isolationLevel) { DebugCheck.NotNull(connection); _connection = connection; EnsureOpenConnection(); _entityTransaction = _connection.BeginTransaction(isolationLevel); }
static public void Transactions() { //<snippetTransactionsWithEntityClient> using (EntityConnection con = new EntityConnection("name=AdventureWorksEntities")) { con.Open(); EntityTransaction transaction = con.BeginTransaction(); DbCommand cmd = con.CreateCommand(); cmd.Transaction = transaction; cmd.CommandText = @"SELECT VALUE Contact FROM AdventureWorksEntities.Contacts AS Contact WHERE Contact.LastName = @ln"; EntityParameter param = new EntityParameter(); param.ParameterName = "ln"; param.Value = "Adams"; cmd.Parameters.Add(param); using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { // Iterate through the collection of Contact items. while (rdr.Read()) { Console.Write("First Name: " + rdr["FirstName"]); Console.WriteLine("\tLast Name: " + rdr["LastName"]); } } transaction.Commit(); } //</snippetTransactionsWithEntityClient> }
public static void PostIssue(int issueId) { // Open a connection manualy since EF has issues with MSDTC transactions var connection = new EntityConnection(ConfigurationManager.ConnectionStrings[INVENTORY_CONNECTION].ConnectionString); connection.Open(); var context = new InventoryEntities(connection); var issue = context.Issues.Single(i => i.IssueId == issueId); var warehouse = issue.WarehouseId; try { using (EntityTransaction scope = connection.BeginTransaction()) { foreach (var item in issue.IssueItems) { // 1. Check the availablity of stock (ItemId and WarehouseId) var stock = context.StockStatus.Single(s => s.ItemId == item.ItemId && s.WarehouseId == warehouse); // 2. If availalbe < requested then throw an error if (stock.Quantity < item.Quantity) { var msg = "Error posting current record. The requested and available quantity do not match.\n"; msg += string.Format("Item: {0} \nAvailable Quantity: {1}\nRequested Quantity:{2}", item.ItemDetail.ItemNo, stock.Quantity, item.Quantity); throw new ApplicationException(msg); } // 3. If available > requested then update balance and set status of issue to posted. var status = context.StockStatus.Single(s => s.ItemId == item.ItemId && s.WarehouseId == warehouse); status.Quantity -= item.Quantity; // 4. Add transaction log to stock_transaction var transaction = new StockTransaction(); transaction.TransactionTypeId = OWNER_ISSUE; transaction.OwnerId = issue.IssueId; transaction.ItemId = item.ItemId; transaction.Quantity = item.Quantity; transaction.WarehouseId = warehouse; transaction.TransactionDate = DateTime.Now; AddTransactionRecord(transaction); issue.StatusId = STATUS_POSTED; issue.PostedDate = DateTime.Now; //TODO: Consider getting the date from the server instead of the client. var user = SecurityHelper.GetUserDetail(Thread.CurrentPrincipal.Identity.Name); issue.PostedBy = string.Format("{0} ({1})", user.FullName, user.UserName); // SEND ALL CHANGES TO THE DATABASE - MIGHTY SAVE!!!!!! context.SaveChanges(); scope.Commit(); } } } catch (Exception exception) { throw new ApplicationException("Error occured while posting the current issue.", exception); } }
public void example() { using (TransactionScope mainScope = new TransactionScope()) { // ADO.NET using (SqlConnection firstConnection = new SqlConnection("First")) { firstConnection.Open(); using (SqlCommand firstCommand = new SqlCommand("FirstQueryText", firstConnection)) { Int32 recordsAffected = firstCommand.ExecuteNonQuery(); } using (SqlConnection secondConnection = new SqlConnection("Second")) { secondConnection.Open(); using (SqlCommand secondCommand = new SqlCommand("SecondQueryText", secondConnection)) { Int32 secondAffected = secondCommand.ExecuteNonQuery(); } } } mainScope.Complete(); } //Entity Connection using (TestEntities database = new TestEntities()) { Customer cust = new Customer(); cust.FirstName = "Ronald"; cust.LastName = "McDonald"; cust.AccountId = 3; database.Customers.Add(cust); database.SaveChanges(); } using (EntityConnection connection = new EntityConnection("TestEntities")) { using (EntityTransaction trans = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)) { EntityCommand CurrentCommand = new EntityCommand("SOME UPDATE STATEMENT", connection, trans); connection.Open(); Int32 RecordsAffected = CurrentCommand.ExecuteNonQuery(); trans.Commit(); } } SqlConnection myConnection = new SqlConnection("Connection String"); }
//This is not working //TODO: Fix syntax of updateCommand private void UpdateUsingEntityCommand(User entity) { using (EntityConnection entityConnection = new EntityConnection("name=AppEntities")) { entityConnection.Open(); using (EntityTransaction entityTransaction = entityConnection.BeginTransaction(IsolationLevel.Serializable)) { string updateCommand = "UPDATE AppEntities.USERS AS U " + "SET U.FirstName = @FirstName, " + "U.LastName = @lastName, " + "U.Username = @userName, " + "U.City = @city " + "WHERE U.Id = @Id"; using (EntityCommand command = new EntityCommand(updateCommand, entityConnection, entityTransaction)) { EntityParameter firstName = new EntityParameter() { ParameterName = "firstName", Value = entity.FirstName }; EntityParameter lastName = new EntityParameter() { ParameterName = "lastName", Value = entity.LastName }; EntityParameter username = new EntityParameter() { ParameterName = "username", Value = entity.Username }; EntityParameter city = new EntityParameter() { ParameterName = "city", Value = entity.City }; EntityParameter id = new EntityParameter() { ParameterName = "Id", Value = entity.Id }; command.Parameters.Add(firstName); command.Parameters.Add(lastName); command.Parameters.Add(username); command.Parameters.Add(city); command.Parameters.Add(id); try { command.ExecuteNonQuery(); entityTransaction.Commit(); } catch (Exception ex) { entityTransaction.Rollback(); } } } } }
public static void PostAdjustment(int adjustmentId) { var connection = new EntityConnection(ConfigurationManager.ConnectionStrings[INVENTORY_CONNECTION].ConnectionString); connection.Open(); var context = new InventoryEntities(connection); var adjust = context.Adjustments.Single(r => r.AdjustmentId == adjustmentId); try { using (EntityTransaction scope = connection.BeginTransaction()) { foreach (var item in adjust.adjustment_detail) { // Checking if the current item already exists in the 'stock_status' table if ( !context.StockStatus.Where( s => s.ItemId == item.ItemId && s.WarehouseId == adjust.WarehouseId). Any()) { using (var c = new InventoryEntities()) { var status = new StockStatus(); status.ItemId = item.ItemId.Value; status.WarehouseId = adjust.WarehouseId.Value; status.Quantity = item.AdjustedQuantity; c.StockStatus.AddObject(status); c.SaveChanges(); } } // If the item already exists in the table then update the quantity field var statusUpdate = context.StockStatus.SingleOrDefault( s => s.ItemId == item.ItemId && s.WarehouseId == adjust.WarehouseId); statusUpdate.Quantity = item.AdjustedQuantity; // Adding to stock transaction log var transaction = new StockTransaction(); transaction.TransactionTypeId = OWNER_ADJUSTMENT; transaction.OwnerId = adjust.AdjustmentId; transaction.ItemId = item.ItemId; transaction.Quantity = item.AdjustedQuantity; transaction.WarehouseId = adjust.WarehouseId; transaction.TransactionDate = DateTime.Now; AddTransactionRecord(transaction); adjust.StatusId = STATUS_POSTED; adjust.PostedDate = DateTime.Now; var user = SecurityHelper.GetUserDetail(Thread.CurrentPrincipal.Identity.Name); adjust.PostedBy = string.Format("{0} ({1})", user.FullName, user.UserName); // SEND ALL CHANGES TO THE DATABASE - MIGHTY SAVE!!!!!! context.SaveChanges(); scope.Commit(); } connection.Close(); } } catch (Exception exception) { throw new ApplicationException("Unable to post adjustment", exception); } }
public static void PostTransfer(int transferId) { // Open a connection manualy since EF has issues with MSDTC transactions var connection = new EntityConnection(ConfigurationManager.ConnectionStrings[INVENTORY_CONNECTION].ConnectionString); connection.Open(); var context = new InventoryEntities(connection); // Tasks: // 1. Check if requested transfer amount exist at the source warehouse // 2. Check if transfered item exist in stock status @ destination warehouse // 3. Decrease source warehouse balance and increase destination warehouse // 4. Post transfer record and update audit log. var transfer = context.Transfers.Single(t => t.TransferId == transferId); var source = transfer.WarehouseFrom; var destination = transfer.WarehouseTo; try { using (EntityTransaction scope = connection.BeginTransaction()) { foreach (var item in transfer.TransferItems) { var itemId = item.ItemId; // Check if requested transfer amount exist in source warehouse. var sourceStatus = context.StockStatus.Single( s => s.ItemId == itemId && s.WarehouseId == source); if (sourceStatus.Quantity < item.Quantity) { throw new ApplicationException( "The current balance at the source warehouse is less than the requested transfer amount."); } // Check if we have a stock status record at the destination warehouse with the current item id. If not then we need to create one. if (!context.StockStatus.Where(s => s.ItemId == itemId && s.WarehouseId == destination).Any()) { using (var c = new InventoryEntities()) { var status = new StockStatus(); status.ItemId = itemId.Value; status.WarehouseId = destination.Value; status.Quantity = 0; c.StockStatus.AddObject(status); c.SaveChanges(); } } // Decrease the quantity field for the source status update and increase that of the destination status update var sourceStatusUpdate = context.StockStatus.SingleOrDefault( s => s.ItemId == itemId && s.WarehouseId == source); sourceStatusUpdate.Quantity -= item.Quantity; var destinationStatusUpdate = context.StockStatus.SingleOrDefault( d => d.ItemId == itemId && d.WarehouseId == destination); destinationStatusUpdate.Quantity += item.Quantity; // Add transaction log var transaction = new StockTransaction(); transaction.TransactionTypeId = OWNER_TRANSFER; transaction.OwnerId = transfer.TransferId; transaction.ItemId = itemId; transaction.Quantity = item.Quantity; transaction.WarehouseId = transfer.WarehouseFrom; transaction.TransactionDate = DateTime.Now; AddTransactionRecord(transaction); } // Update PostedDate and PostedBy fields for the Receiving record transfer.StatusId = STATUS_POSTED; transfer.PostedDate = DateTime.Now; //TODO: Consider getting the date from the server instead of the client. var user = SecurityHelper.GetUserDetail(Thread.CurrentPrincipal.Identity.Name); transfer.PostedBy = string.Format("{0} ({1})", user.FullName, user.UserName); // SEND ALL CHANGES TO THE DATABASE - MIGHTY SAVE!!!!!! context.SaveChanges(); scope.Commit(); } } catch (Exception exception) { throw new ApplicationException("Error occured while posting stock transfer operation.", exception); } connection.Close(); }
public static void PostReturn(int returnId) { // Open a connection manualy since EF has issues with MSDTC transactions var connection = new EntityConnection(ConfigurationManager.ConnectionStrings[INVENTORY_CONNECTION].ConnectionString); connection.Open(); var context = new InventoryEntities(connection); var returnItem = context.ItemReturns.Single(r => r.ReturnId == returnId); try { using (EntityTransaction scope = connection.BeginTransaction()) { foreach (var item in returnItem.ReturnedItems) { // Check if the current item already exists in the 'stock_status' table if (!context.StockStatus.Where(s => s.ItemId == item.ItemId && s.WarehouseId == returnItem.WarehouseId).Any()) { using (var c = new InventoryEntities()) { var status = new StockStatus(); status.ItemId = item.ItemId.Value; status.WarehouseId = returnItem.WarehouseId.Value; status.Quantity = 0; c.StockStatus.AddObject(status); c.SaveChanges(); } } // If the item already exists in the table then update the quantity field var statusUpdate = context.StockStatus.SingleOrDefault( s => s.ItemId == item.ItemId && s.WarehouseId == returnItem.WarehouseId); statusUpdate.Quantity += item.Quantity; // Add transaction log var transaction = new StockTransaction(); transaction.TransactionTypeId = OWNER_RETURN; transaction.OwnerId = returnItem.ReturnId; transaction.ItemId = item.ItemId; transaction.Quantity = item.Quantity; transaction.WarehouseId = returnItem.WarehouseId; transaction.TransactionDate = DateTime.Now; AddTransactionRecord(transaction); } // Update PostedDate and PostedBy fields for the Receiving record returnItem.StatusId = STATUS_POSTED; returnItem.PostedDate = DateTime.Now; //TODO: Consider getting the date from the server instead of the client. var user = SecurityHelper.GetUserDetail(Thread.CurrentPrincipal.Identity.Name); returnItem.PostedBy = string.Format("{0} ({1})", user.FullName, user.UserName); // SEND ALL CHANGES TO THE DATABASE - MIGHTY SAVE!!!!!! context.SaveChanges(); scope.Commit(); } } catch (Exception exception) { throw new ApplicationException("Error occured while posting stock return operation.", exception); } // Close the connection connection.Close(); }