The search for a C# DAL code generator that actually works. Part 3: the solution.
September 4, 2008
In part 2 of this series, I outlined my requirements for an ideal DAL generator.
Well, not having found any tool out there that meets my criteria, I ended up writing the tool myself, meeting all but one of my objectives [I dont handle stored procedures yet].
Sql Server 2005 is the target DBMS for now, using VS2005 as the compiler.
Lets walk through how to use the tool.
- From the command line, the tool is invoked like this.
mycodegentool.exe config.xml- The simplest config.xml file is
<CodeGen>
<Directory>D:\development\codegenExample\myDatabase</Directory>
<ConnectionString>Data Source=.;Initial Catalog=myDatabase;
Integrated Security=SSPI;</ConnectionString>
<ConcurrencyColumn>update_date</ConcurrencyColumn>
<CodeGen>- To get a unit test dll as well, add this xml fragment to the config file.
<UnitTest>
<Directory>D:\development\codegenExample\myDatabase\CodeGenUnitTest</Directory>
</UnitTest>This takes care of the plumbing. Now reference the DAL dll from your business layer dll, and happy porcelain coding.
Here are a few use case scenarios in actually using the DAL from your business layer.
- Load up a table.
ProductsFactory factory = new ProductsFactory();
List rows = factory.LoadAll( connection ) ;
Console.WriteLine( rows.Count ) ;- Quicker way to see have many rows in a table.
ProductsFactory factory = new ProductsFactory();
int rowcount = factory.GetRowCount( connection ) ;- Load up a specific row from a table using the primary key.
int id = 17 ;
ProductsFactory factory = new ProductsFactory();
Products product = factory.LoadByPrimaryKey( connection, id ) ;- Load up a set of rows from a table based on custom search criteria.
bool discontinued = true ;
ProductsFactory factory = new ProductsFactory();
List rows = factory.LoadByDiscontinued( connection, discontinued ) ;<Tables>
<Table Class="Products" >
<Methods>
<Method Name="LoadByDiscontinued" where="LoadByDiscontinued.sql">
<Parameters>
<Parameter Name="discontinued" Type="bool?" />
</Parameters>
</Method>
</Methods>
</Table>
<Tables>where discontinued = @discontinued order by productname- Get a row’s parent row.
EmployeesFactory factory = new EmployeesFactory();
// robert - the pleb
int? employeeId = 7;
Employees robert = factory.LoadByPrimaryKey( connection, employeeId);
Assert.AreEqual( robert.FirstName, "Robert" ) ;
// robert reports to steven
Employees steven = robert.parentEmployeesByReportsTo( connection ) ;
Assert.AreEqual(steven.FirstName, "Steven");
// steven reports to andrew
Employees andy = steven.parentEmployeesByReportsTo( connection);
Assert.AreEqual(andy.FirstName, "Andrew");
// andrew is the big cheese
Employees nevilleNobody = andy.parentEmployeesByReportsTo(connection);
Assert.IsNull(nevilleNobody);
- Get a row’s children rows.
OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;
// get an order
Orders order = factory.LoadByPrimaryKey( connection, orderId);
// the customer for this order
Customers customer = order.parentCustomersByCustomerID( connection) ;
// all the orders of this customer
List orderList = customer.childOrdersByCustomerID( connection ) ;
- Load up from a view.
Alphabetical_list_of_productsFactory factory = new Alphabetical_list_of_productsFactory();
List rows = factory.LoadAll( connection ) ;- Load up from a custom query.
CustomerOrderFactory factory = new CustomerOrderFactory();
List rows = factory.LoadByRegion( connection, "Quebec" );
List rows2 = factory.LoadByCustomer( connection, "quick" ) ;
<Queries>
<Query Class="CustomerOrder" CodeFile="CustomerOrder.cs" Select="CustomerOrder.sql" >
<Methods>
<Method Name="LoadByRegion" Where="LoadByRegion.sql">
<Parameters>
<Parameter Name="region" Type="string" />
</Parameters>
</Method>
</Methods>
<Method Name="LoadByCustomer" Where="LoadByCustomer.sql">
<Parameters>
<Parameter Name="customerId" Type="string" />
</Parameters>
</Method>
</Query>
</Queries>
The CustomerOrder.sql file contains
select * from customers c, orders o
whilst the LoadByCustomer method has the following criteria in the config sql file reference
where c.customerid = o.customerid
and c.customerid = @customerid
order by c.companyname, o.orderdate
and the LoadByRegion method’s where clause is
where c.customerid = o.customerid
and isnull(c.region, '') = isnull(@region, '')
order by c.companyname, o.orderdate
- Insert a new row
OrdersFactory factory = new OrdersFactory();
Orders order = new Orders() ; // create a new row in memory
order.CustomerID = 123;
order.EmployeeID = 7 ;
order = factory.Save( order, connection ) ; // does the insert- Update an existing row
OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order
order.ShipPostalCode = "2565"
order = factory.Save( order, connection ) ; // does the update- Insert then update a row
OrdersFactory factory = new OrdersFactory();
Orders order = new Orders() ; // create a new row in memory
order.CustomerID = 123;
order.EmployeeID = 7 ;
order = factory.Save( order, connection ) ; // does the insert
order.ShipPostalCode = "2565"
order = factory.Save( order, connection ) ; // does the update
- Delete an existing row
OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order
order.MarkForDelete = true ;
factory.Save( order, connection ) ; // does the delete
- Negative use case - get a concurrency error.
TestTableFactory factory = new TestTableFactory();
TestTable alice = factory.LoadByPrimaryKey( connection, 12345 ); // get a row
TestTable bob = factory.LoadByPrimaryKey( connection, 12345 ); // get it again
alice.update_date = DateTime.Now ;
bob.update_date = DateTime.Now ;
alice = factory.Save( alice, connection ) ; // does the save ok
bob = factory.Save( bob, connection ) ; // will throw a concurrency exception
- Negative use case - get a save error when saving a stale object
OrdersFactory factory = new OrdersFactory();
int orderId = 10289 ;
Orders order = factory.LoadByPrimaryKey( connection, orderId); // get an order
order.ShipPostalCode = "2565"
factory.Save( order, connection ) ; // does the update - ok
order.ShipPostalCode = "2000"
factory.Save( order, connection ) ; // will throw a stale row exception
- Many users simultaneously inserting rows into a table.
In the first version of this use case, the inserts fail, as the code and sql that determine the next primary key value are not protected against concurrent threads. The test fails with the error “Violation of PRIMARY KEY constraint ‘PK_TestTable3′. Cannot insert duplicate key”.
protected void InsertLotsOfRowsInParallel()
{
using ( MyThreadPoolManager tpm = new MyThreadPoolManager( 50, 5000 ))
{
for (int i = 1; i <= rowsToInsert; i++)
tpm.Queue( InsertOneRow() ) ;
tpm.WaitUntilAllFinished() ;
}
}
// every invocation of this method runs in its own thread
protected void InsertOneRow()
{
using (TransactionScope transaction =
new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (SqlConnection connection = new SqlConnection( _connectionString ) )
{
connection.Open();
TestTable3Factory factory = new TestTable3Factory();
TestTable3 obj = new TestTable3();
// BAD: get the next available ID = max(ID) + 1
obj.ID = select max(ID) + 1 from the table
obj.update_date = DateTime.Now;
// insert the row - this will fail with PRIMARY KEY constraint violation
obj = factory.Save(obj, connection);
transaction.Complete(); // commit - wont get here
}
}
}
In the correct version of this use case, the codegen tool has ootb capability to handle this common scenario. The tool will escalate the lock around the next primary key value and serialise access to the table, giving everyone a warm fuzzy feeling.
// every invocation of this method runs in its own thread
protected void InsertOneRow()
{
using (TransactionScope transaction =
new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (SqlConnection connection = new SqlConnection( _connectionString ) )
{
connection.Open();
TestTable3Factory factory = new TestTable3Factory();
TestTable3 obj = new TestTable3();
// GOOD: the tool will get the next available ID, the safe way
obj.ID = factory.GetNextId( connection, TestTable3.Column_ID, true );
obj.update_date = DateTime.Now;
// insert the row - now it works
obj = factory.Save(obj, connection);
transaction.Complete(); // commit - aha
}
}
}
- Here are a few .NET Reflector screen dumps of the DAL layer that the tool generates for Northwind.
- One of the databases that I have recently codegened is the popular DotNetNuke database.
I recommend .NET Reflector to inspect the generated code.
Contact Buildmasters if you would like a DAL generated for your database.
- This is the to do list for the tool
- Make sure it compiles and works under Mono and Debian Linux.
- Make sure it compiles and runs under VS2008.
- Extend to target SqlServer 2008, MySql, Oracle databases.
- Extend to handle stored procedures.
So far I have used the tool against 13 corporate, vendor and public databases, and it has my made my programming experience easier, faster, and more reliable.
I do not think about the DAL layer at all now - it just works.
Comments
9 Responses to “The search for a C# DAL code generator that actually works. Part 3: the solution.”
Got something to say?
Codegen now works on Windows Server 2008 / .NET 3.5 / Visual Studio 2008 / Sql Server 2008 platform.
- tubby
Hello,
I would like to know how can I get a copy of your DAL code generator.
I’ve a lots of problems loading tables from a ODBC database into my dataset.
My PC freezes for 30+ minutes for each table (and sometimes for each step). I need another option and I think this tool can help me with that problem.
Can I download the tool from this site?
Manu.
Codege now certified on .NET 4.0 / visual studio 2010 / sql server 2008.
Codegen now natively and seemlessly handles the Sql Server 2008 .NET UDT spatial types [geography] and [geometry], and also [hierarchyid].
Linq2sql or entity framework 4 users will need to hack their code to get these UDT types to work in their code!
Here is a codegen example illustrating the use of the Sql Server UDT CLR type [geography].
We haved codegened the AdventureWorks2008 database, and are doing CRUD operations
on the Person.Address table.
The interesting property is .SpatialLocation, which is a UDT of type SqlGeography.
[TestFixture]
public class TestTableWithUdtFields
{
protected const string _connectionString = @”Data Source=localhost;Initial Catalog=adventureworks2008;Network Library=DBMSSOCN;Integrated Security=SSPI;”;
protected const string _cityInsert = “Bikini Bottom” ;
protected const string _cityUpdate = “Shell City” ;
protected SqlGeography _locationInsert = SqlGeography.Parse( “POINT (1 1)” ) ;
protected SqlGeography _locationUpdate = SqlGeography.Parse( “POINT (2 2)” ) ;
[Test]
public void Address_CodegenTest()
{
acr.CodeGenEtc.DebugSql = true ;
var addressFactory = new aw.AddressFactory();
var stateProvinceFactory = new aw.StateProvinceFactory() ;
//get a state province for referentiall integrity purposes
aw.StateProvince stateProvince = null ;
using ( SqlConnection conn = new SqlConnection( _connectionString ) )
{
conn.Open() ;
stateProvince = stateProvinceFactory.LoadAll( conn, null, 1, null )[0] ;
}
//
// a new address - notice disconnected object here with no data context ——————————
//
var address = new aw.Address() ;
address.AddressLine1 = “the crusty crab” ;
address.AddressLine2 = “1 crabby patty street” ;
address.City = _cityInsert ;
address.PostalCode = “2000″ ;
address.StateProvinceID = stateProvince.StateProvinceID ;
address.ModifiedDate = DateTime.Now ;
address.rowguid = new Guid() ;
address.SpatialLocation = _locationInsert ; // this is the udt field
using ( TransactionScope transaction = new TransactionScope( TransactionScopeOption.RequiresNew ) )
using ( SqlConnection conn = new SqlConnection( _connectionString ) )
{
conn.Open() ;
// address table row count before we begin porking
var rowsBefore = addressFactory.GetRowCount(conn);
// assert address does not exist yet
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityInsert ).Count ) ;
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityUpdate ).Count ) ;
//
// insert the address —————————–
//
// notice no need to attach object,
// and no if statement to determine whether insert or update,
// it’s the 21st century FFS - just pork it in
//
var insertedAddress = addressFactory.Save( address, conn ) ;
// assert that we have 1 additional row in the table
Assert.AreEqual( rowsBefore + 1, addressFactory.GetRowCount(conn) );
// assert that we can reload the address
Assert.AreEqual( 1, addressFactory.LoadByCity( conn, _cityInsert ).Count ) ;
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityUpdate ).Count ) ;
// assert we can read back the udt type correctly
// this is the udt money shot
Assert.AreEqual( “POINT (1 1)” , insertedAddress.SpatialLocation.ToString() ) ;
//
// update the address —————————–
//
insertedAddress.City = _cityUpdate ;
insertedAddress.SpatialLocation = _locationUpdate ; // this is the udt field
insertedAddress.ModifiedDate = DateTime.Now ;
var updatedAddress = addressFactory.Save( insertedAddress, conn ) ;
// assert that we still have only have 1 additional row in the table since we started
Assert.AreEqual( rowsBefore + 1, addressFactory.GetRowCount(conn) );
// assert that there this still only one spongebob address
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityInsert ).Count ) ;
Assert.AreEqual( 1, addressFactory.LoadByCity( conn, _cityUpdate ).Count ) ;
// assert we can read back the udt type correctly
// this is the udt money shot
Assert.AreEqual( “POINT (2 2)” , updatedAddress.SpatialLocation.ToString() ) ;
//
// delete the address ———————————-
//
updatedAddress._MarkForDeletion = true ;
addressFactory.Save( updatedAddress, conn ) ;
// assert that we now have the same number of rows as when we started this adventure
Assert.AreEqual( rowsBefore, addressFactory.GetRowCount(conn) );
// assert that the address does not exist
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityInsert ).Count ) ;
Assert.AreEqual( 0, addressFactory.LoadByCity( conn, _cityUpdate ).Count ) ;
}
} // end test
} // end class
I had hoped to provide the same UDT CLR example as above, but using linq2sql instead as a comparison to my codegen.
But to my horror, the out of the box experience with visual studio 2010 was that I could not even generate the linq2sql .dbml file for the Address table, which contains a [geography] typed column.
So Visual studio 2010 cacks its daks on UDT fields. Oh dear, Microsoft, baby.
I’m not sure what people who use linq2sql are going to do when they have to use a table that has a UDT field.
I googled for an answer to this problem but did not come up with any usefule workarounds.
http://www.buildmasters.com.au/wp-content/uploads/2010/05/linq2sqlcantdoudt.png
I will next try to see if EF4 can handle UDT CLR fields OOTB.
- tubby
No luck with entity framework 4 either.
The wizard just throws an error saying that [geography] is not supported.
http://www.buildmasters.com.au/wp-content/uploads/2010/06/ef4cantdoudt.png
Yep, found this thread on msdn social that says, “support for CLR UDTs did not make it into EF4…”
http://social.msdn.microsoft.com/Forums/en/adonetefx/thread/0862812c-b9a8-451f-ad1d-af478a21cce0
and this one on M$ connect
http://connect.microsoft.com/VisualStudio/feedback/details/535121/sql-server-2008-data-types-support-in-linq-to-sql-entity-framework
So until VS2010 SP1, at the earliest, UDT fields are persona non grata when it comes to entity framework.
- tubby
Ok, here’s a linq2sql basic crud sequence, similar to the codegen example above, but using the AdventureWorks2008 Person.Person table (you may remember that linq2sql sh@t itself when codegening the Person.Address table above).
I think linq2sql is a pretty good attempt from M$, and it is good for simple use cases, but I really have strong reservations about this DataContext caching bizo, amongst others.
For example, one has to set concurrency options on a table-by-table basis via the UI, which is no fun if you have 300 tables in your database, girlfriend.
And you really do want your concurrency columns set up properly in linq2sql, ’cause if you don’t, its version of optimistic concurrency will produce the following fugly sql where clause, for updates:
UPDATE [Person].[Person]
SET [FirstName] = @p7, [ModifiedDate] = @p8
WHERE ([BusinessEntityID] = @p0)
AND ([PersonType] = @p1)
AND
(NOT ([NameStyle] = 1))
AND ([Title] IS NULL)
AND ([FirstName] = @p2)
AND ([MiddleName] IS NULL)
AND ([LastName] = @p3)
AND ….
That is, it will compare EVERY non primary key field. This is horrible, especially in a high traffic OLTP scenario.
This will definitely increase the heart rate of your DBA, and give him/her an excuse to rant on about developers and dynamic sql.
After one sets the concurrency column correctly, sanity prevails, and the DBA can calm down:
UPDATE [Person].[Person]
SET [FirstName] = @p2
WHERE ([BusinessEntityID] = @p0)
AND ([ModifiedDate] = @p1)
Read my comments // below.
protected const string _connectionString = @”Data Source=localhost;Initial Catalog=adventureworks2008;Network Library=DBMSSOCN;Integrated Security=SSPI;”;
protected const string _firstNameInsert = “Martika” ;
protected const string _firstNameUpdate = “Marta”;
protected const string _LastName = “Marrero”;
[Test]
public void Person_Linq2Sql()
{
using ( TransactionScope transaction = new TransactionScope( TransactionScopeOption.RequiresNew, TimeSpan.FromHours(1) ) )
//
// OH DEAR - databases objects are cached in DataContext
// scalability, multi-threaded, concurrency, state-related issues, to be sure!
//
using( var dc = new ns::adworksDataContext( _connectionString ) )
{
dc.Log = System.Console.Out ;
// get a new business entity for referential integrity purposes
ns.BusinessEntity businessEntity = new ns.BusinessEntity() ;
businessEntity.ModifiedDate = DateTime.Now ;
businessEntity.rowguid = Guid.NewGuid() ;
dc.BusinessEntities.InsertOnSubmit( businessEntity ) ;
// person table row count before we begin porking
var rowsBefore = dc.Persons.Count() ;
// assert person does not exist yet
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
//
// a new person —————————
//
ns.Person person = new ns.Person() ;
person.FirstName = _firstNameInsert ;
person.LastName = _LastName;
person.PersonType = “EM” ;
person.NameStyle = false ;
person.EmailPromotion = 0;
person.rowguid = Guid.NewGuid() ;
person.ModifiedDate = DateTime.Now ;
person.BusinessEntity = businessEntity ;
//
// insert the person —————————-
//
// OH DEAR - insert vs update - non-orthogonal persistance pattern
// why do I have to tell linq2sql that this is an insert?
// can’t it figure this out, to be sure?
//
// call it .Save or .Upsert FFS
//
dc.Persons.InsertOnSubmit( person ) ;
dc.SubmitChanges() ;
// assert that we have 1 additional row in the table
Assert.AreEqual( rowsBefore + 1, dc.Persons.Count() ) ;
// assert that we can reload the person ok
Assert.AreEqual( 1, dc.Persons.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
Assert.AreEqual( _firstNameInsert, dc.Persons.Where( p => p.FirstName == _firstNameInsert ).First().FirstName ) ;
//
// update the person —————————-
//
// WTF OH DEAR
// - linq2sql supports concurrency fields via the UI.
// - BUT now linq2sql /owns/ this field, and wont let me set it myself.
// - what if I want to use UTC time, instead of local time ?
//
// person.ModifiedDate = DateTime.UtcNow ; // non compredo, signor
//
person.FirstName = _firstNameUpdate ;
dc.SubmitChanges() ;
// assert that we still have only have 1 additional row in the table since we started
Assert.AreEqual( rowsBefore + 1, dc.Persons.Count() );
// assert that there this still only 1 martika
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 1, dc.Persons.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
Assert.AreEqual( _firstNameUpdate, dc.Persons.Where( p => p.FirstName == _firstNameUpdate ).First().FirstName ) ;
//
// delete the person —————————-
//
dc.Persons.DeleteOnSubmit( person ) ;
dc.SubmitChanges() ;
// assert that we now have the same number of rows as when we started this adventure
Assert.AreEqual( rowsBefore, dc.Persons.Count() );
// assert that the person does not exist
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, dc.Persons.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
}
} // end test
- tubby
And here below is the EF4 version of the above:
So where should you invest your learning time - L2S or EF4?
I would say that EF4 is your best bet, if your project has the time for the learning curve. It is M$’s long term ORM going forward, and it handles non Sql Server DBMSs. The ADO.NET team now own both L2S and EF4, and their focus is on EF4.
However if you need to do something quick and you are using Sql Server, then go for L2S.
You should try to keep things simple in EF4, and you will need to become familiar with its quirks and limitatons. The scary part of EF4 as an ORM is it that gives licence to your architects/tech leads to go berserk in the object layer with the modelling and object mapping. Most of the time you only need the default 1 table to 1 object modelling that L2S (and codegen) provide.
EF4’s API, as you can see from the below example, is very similar to L2S. For example, L2S.SubmitChanges() vs EF4.SaveChanges() etc, so you can leverage your L2S knowledge to some extent.
A minus for EF4 is that it definitely needs a better SQL inspection / logging model, and the SQL it generates can look butt ugly if you use relationships. All the more reason why we need to get a hold of the generated SQL to troubleshoot performance issues, for example. The .ToTraceString() method is not globally available, it needs context.Log implemented, just like L2S has. Without this, in order to debug all the sql generated from EF4, you will need to run profiler, a request that your friendly production DBA will reject every time
Enjoy.
[TestFixture]
public class TableWithOrdinaryFields_EntityFramework4
{
protected static string _databaseConnectionString =
@”Data Source=localhost;Initial Catalog=adventureworks2008;Network Library=DBMSSOCN;Integrated Security=SSPI;MultipleActiveResultSets=True;” ;
//
// INTERESTING - we’re in conceptual ORM entity city here, dont even think about using the word database.
//
protected static string _entityConnectionString = string.Format(
@”metadata=res://*/adworks2008.csdl|res://*/adworks2008.ssdl|res://*/adworks2008.msl;
provider=System.Data.SqlClient;
provider connection string=”"{0}”" “, _databaseConnectionString ) ;
protected const string _firstNameInsert = “Martika” ;
protected const string _firstNameUpdate = “Marta”;
protected const string _LastName = “Marrero”;
[Test]
public void Person_EntityFramework4()
{
using ( TransactionScope transaction = new TransactionScope( TransactionScopeOption.RequiresNew, TimeSpan.FromHours(1) ) )
{
using( var context = new ns::AdventureWorks2008Entities( _entityConnectionString ) )
{
Console.WriteLine( context.Connection.ConnectionString ) ;
// OH DEAR
//
// context.Log = Console.Out ; // compile error - no log method provided as in linq2sql
//
// note to EF4 team:
// Please fix this.
// .ToTraceString() doesn’t do the job, mate.
// Need to see all the sql - we are not allowed to run profiler in production.
//
// get a new business entity for referential integrity purposes
ns.BusinessEntity businessEntity = new ns.BusinessEntity() ;
businessEntity.ModifiedDate = DateTime.Now ;
businessEntity.rowguid = Guid.NewGuid() ;
context.BusinessEntities.AddObject( businessEntity ) ;
context.SaveChanges() ;
// person table row count before we begin porking
var rowsBefore = context.People.Count() ;
// assert person does not exist yet
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
//
// a new person —————————
//
ns.Person person = new ns.Person() ;
person.FirstName = _firstNameInsert ;
person.LastName = _LastName;
person.PersonType = “EM” ;
person.NameStyle = false ;
person.EmailPromotion = 0;
person.rowguid = Guid.NewGuid() ;
person.ModifiedDate = DateTime.Now ;
// WTF ??
// If I don’t add the following line, EF4 complains about concurrency errors.
// EF4 wants me to refresh the object, even though it has NOT been changed by anyone since I saved it 3ms ago.
// (I am running this unit test on the train on my non-3G laptop, no ssms running or other users connected)
context.Refresh( RefreshMode.StoreWins, businessEntity ) ;
person.BusinessEntity = businessEntity ;
//
// insert the person —————————-
//
context.People.AddObject( person ) ;
context.SaveChanges() ;
// assert that we have 1 additional row in the table
Assert.AreEqual( rowsBefore + 1, context.People.Count() ) ;
// assert that we can reload the person ok
Assert.AreEqual( 1, context.People.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
Assert.AreEqual( _firstNameInsert, context.People.Where( p => p.FirstName == _firstNameInsert ).First().FirstName ) ;
//
// update the person —————————-
//
// WTF - see above
context.Refresh( RefreshMode.StoreWins, person ) ;
person.ModifiedDate = DateTime.UtcNow ;
person.FirstName = _firstNameUpdate ;
context.SaveChanges() ;
// assert that we still have only have 1 additional row in the table since we started
Assert.AreEqual( rowsBefore + 1, context.People.Count() );
// assert that there is still only 1 martika
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 1, context.People.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
Assert.AreEqual( _firstNameUpdate, context.People.Where( p => p.FirstName == _firstNameUpdate ).First().FirstName ) ;
//
// delete the person —————————-
//
// WTF - see above
context.Refresh( RefreshMode.StoreWins, person ) ;
context.DeleteObject( person ) ;
context.SaveChanges() ;
// assert that we now have the same number of rows as when we started this adventure
Assert.AreEqual( rowsBefore, context.People.Count() );
// assert that the person does not exist
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameInsert ).Count() ) ;
Assert.AreEqual( 0, context.People.Where( p => p.FirstName == _firstNameUpdate ).Count() ) ;
}
}
} // end test
BTW, on other topic, I love Google’s new search algorithm.
:-)
Searching for /Martika C#/ brings up this page on the first page of google’s results
Now that’s agile!
- tubby