Tutorial :How can I have 2 ADO access methods use the same Transaction?



Question:

I'm writing a test to see if my LINQ to Entity statement works.. I'll be using this for others if I can get this concept going..

my intention here is to INSERT a record with ADO, then verify it can be queried with LINQ, and then ROLLBACK the whole thing at the end.

I'm using ADO to insert because I don't want to use the object or the entity model that I am testing. I figure that a plain ADO INSERT should do fine.

problem is.. they both use different types of connections.

is it possible to have these 2 different data access methods use the same TRANSACTION so I can roll it back??

_conn = new SqlConnection(_connectionString);  _conn.Open();    _trans = _conn.BeginTransaction();    var x = new SqlCommand("INSERT INTO Table1(ID, LastName, FirstName, DateOfBirth) values('127', 'test2', 'user', '2-12-1939');", _conn);    x.ExecuteNonQuery();     //So far, so good.  Adding a record to the table.    //at this point, we need to do **_trans.Commit()** here because our Entity code can't use the same connection. Then I have to manually delete in the TestHarness.TearDown..  I'd like to eliminate this step      //(this code is in another object, I'll include it for brevity. Imagine that I passed the connection in)  //check to see if it is there  using (var ctx = new XEntities(_conn)) //can't do this.. _conn is not an EntityConnection!   {      var retVal = (from m in ctx.Table1                    where m.first_name == "test2"                    where m.last_name == "user"                    where m.Date_of_Birth == "2-12-1939"                    where m.ID == 127                    select m).FirstOrDefault();         return (retVal != null);   }    //Do test.. Assert.BlahBlah();    _trans.Rollback();  


Solution:1

Hmm...

http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand(v=VS.100).aspx

_conn = new EntityConnection(_connectionString);  EntityTransaction myTrans = _conn.BeginTransaction();    using(MyObjectContext x = new MyObjectContext(_conn))  {    x.ExecuteStoreCommand(insertString);  }    CallTest(_conn)    myTrans.Rollback();  

Of course, since you have an object context at that point, you don't have to use ExecuteStoreCommand if you'd instead prefer to new up a data instance, attach and save changes.


Solution:2

Yes, just use TransactionScope:

using (TransactionScope ts = new TransactionScope()) {      // Do something in ADO.NET with one connection.        // Do something with Linq in another connection        // Commit (or not if you want to roll back)      //ts.Complete();  }  

No other explicit transaction handling is needed. Note though that if you use two different connections, you will need to have DTC running.


Solution:3

Here was my final test that I created based upon my selected answer. I'm actually testing a service that makes a LINQ to Entities call to the database. (that LINQ you saw is basically what is in the WCF Service function)

This way I can call the service using the same connection I INSERTed with.. ( I could actually move the connection and transaction stuff to setup & teardown, but this is it in a nutshell)

   [Test]      public void Test_VerifyXXX()      {          var _conn = new EntityConnection(_connectionString2);            _conn.Open();          EntityTransaction myTrans = _conn.BeginTransaction();            using (var x = new XEntities(_conn))          {              x.ExecuteStoreCommand("INSERT INTO Table1(ID, LastName, FirstName, DateOfBirth) values('127', 'test2', 'user', '2-12-1939');");          }                         XService test = new XService_Mock(_conn);          bool results = test.CustomerVerified(127, "user", "test2", new DateTime(1939, 2, 12));            Assert.IsInstanceOf(typeof(bool), results);          Assert.AreEqual(true, results);            myTrans.Rollback();      }  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »