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