In .NET 1.1, I tried the original MS Data Access Application Block’s SqlHelper (you can still download it here). It was great for most of the common uses, but was lacking in some areas. The consuming code looked sloppy and encouraged blind faith that database objects never changed. It also didn’t support transactions as I would have liked, and didn’t support my obsession with custom entities. I started out writing an extension library that wrapped SqlHelper, but that felt very wrong… wrapping the ADO.NET wrapper (SqlHelper). I ended up writing my own version of SqlHelper called SqlHelper (nice name, eh?). You see, at this time I was getting over a bad relationship with a series of ORM products that had a negative effect on my productivity. I decided to revolt with good ol’ fashion data access methods that have never let us down.

The only thing worse than my ORM experience was the disgusting over-use of DataSet and DataTable. For my dollar, DataReader is where it’s at. I agree that using the reader is slightly more dangerous in the hands of an inexperienced or inattentive developer (did you know you have to close the reader when you’re done with it??). Nothing can compare with the speed and flexibility of the reader, which is why DataSet and DataAdapter use it at their core. If you are working with custom entities, instead of DataSets and DataTables, you would be crazy to not use the DataReader.

My SqlHelper worked in conjunction with my DataAccessLayer class that defined a few delegates that made reader-to-object-mapping a simple task.  Once the mapping methods were written to be used with the delegates, which returned object or System.Collections.CollectionBase because we did not yet have generics (can you imagine??), you simply called the SqlHelper to do all of the hard work. SqlHelper did not implement all of the craziness that the original version contained. It was a short 450 lines of code that did nothing but access data in a safe and reliable way. In the example below, we have the GenerateDocumentFromReader method that is used by the GenerateObjectFromReader delegate. When SqlHelper.ExecuteReaderCmd is called, the delegate is passed in to map the reader results to my object… in this case a Document.

// Object generation method 
private static object GenerateDocumentFromReader(IDataReader returnData) 
{
     Document document = new Document();
     if (returnData.Read())
     {
         document = new Document(
             (int)returnData["DocumentId"],
             (byte[])returnData["DocumentBinary"],
             returnData["FileName"].ToString(),
             returnData["Description"].ToString(),
             returnData["ContentType"].ToString(),
             (int)returnData["FileSize"],
             returnData["MD5Sum"].ToString(),
             (bool) returnData["EnabledInd"],
             (int)returnData["CreatorEmpId"],
             Convert.ToDateTime(returnData["CreateDt"]),
             (int)returnData["LastUpdateEmpId"],
             Convert.ToDateTime(returnData["LastUpdateDt"]));
     }     return document;
} 
public static Document GetDocumentByDocumentId(int documentId)
{
     SqlCommand sqlCmd = new SqlCommand();
     SqlHelper.SetCommandArguments(sqlCmd, CommandType.StoredProcedure, "usp_Document_GetDocumentByDocumentId");
     SqlHelper.AddParameterToSqlCommand(sqlCmd, "@DocumentId", SqlDbType.Int, 0, ParameterDirection.Input, documentId);
     DataAccessLayer.GenerateObjectFromReader gofr = new DataAccessLayer.GenerateObjectFromReader(GenerateDocumentFromReader);
     Document document = SqlHelper.ExecuteReaderCmd(sqlCmd, gofr) as Document;
     return document;
}

This worked wonderfully for years. After converting, I couldn’t imagine a project that used ORM, DataSets, or DataTables again. I’ve been on many 1.1 projects since writing my SqlHelper in 2004, and I have successfully converted them all. In early 2006, MS graced us with .NET 2.0. Generics, System.Transactions, and partial classes changed my life. In my first few exposures to generics, like Vinay “the Generic Guy” Ahuja’s 2005 Jax Code Camp presentation and Juval “My Hero” Lowy’s MSDN article “An Introduction to Generics”, I listened/read and pondered the millions of uses of generics. I adapted my SqlHelper heavily to use these new technologies and morphed it into something else that closely represented the newest version of the DAAB, Enterprise Library 3.

By this point, I wanted to convert to Enterprise Library. It was far better than the simple SqlHelper. It had better transaction support, though I don’t know if that included System.Transactions. I could have put my object generation extensions on top of it and it would have worked well for years. On home projects I had already converted to use EntLib. At work I was not so lucky. The deep stack trace when something went wrong scared everyone, and that is still a fear for those starting out in EntLib today. To ease the fears, I just created my replacement to SqlHelper… the Database class.

I used a lot of the same naming conventions as Enterprise Library. In fact, much of the consuming code was nearly identical (except for the fact that it did not implement the provider pattern and worked only with SQL Server). This was in anticipation of a quick adoption of Enterprise Library 3 in the workplace. Kind of a “see… not so bad” move on my part. Just like EntLib, you created a Database class using the DatabaseFactory that used your default connection string key. Commands and parameters were created and added with methods off of the Database class. Aside from the SqlCommand/DbCommand, everything looked and felt the same, but came in a small file with only 490 lines of code instead of 5 or more projects with 490 files. Using it felt the same, too. Only my object/collection generation extensions looked different from the standard reader, scalar, dataset routines. Below is the same code from above using the Database class and related classes to create a Document from a reader.

// Object generation method
private static Document GenerateDocumentFromReader(IDataReader returnData)
{
     Document document = new Document();
     if (returnData.Read())
     {
         document = new Document(
             GetIntFromReader(returnData, "DocumentId"),
             GetIntFromReader(returnData, "DocumentTypeId"),
             GetStringFromReader(returnData, "DocumentTypeName"),
             GetByteArrayFromReader(returnData, "DocumentBinary"),
             GetStringFromReader(returnData, "FileName"),
             GetStringFromReader(returnData, "Description"),
             GetStringFromReader(returnData, "ContentType"),
             GetIntFromReader(returnData, "FileSize"),
             GetStringFromReader(returnData, "MD5Sum"),
             GetStringFromReader(returnData, "CreatorEmpID"),
             GetDateTimeFromReader(returnData, "CreateDt"),
             GetStringFromReader(returnData, "LastUpdateEmpID"),
             GetDateTimeFromReader(returnData, "LastUpdateDt"));
     }
     return document;
} 
public static Document GetDocumentByDocumentId(int documentId)
{
     Database db = DatabaseFactory.CreateDatabase(AppSettings.ConnectionStringKey);
     SqlCommand sqlCmd = db.GetStoredProcCommand("usp_Document_GetDocumentByDocumentId");
     db.AddInParameter(sqlCmd, "DocumentId", SqlDbType.Int, documentId);
     GenerateObjectFromReader<Document> gofr = new GenerateObjectFromReader<Document>(GenerateDocumentFromReader);
     Document document = CreateObjectFromDatabase<Document>(db, sqlCmd, gofr);
     return document;
}

This, too, worked great for years. Other than a brief period in 2007 when I tried to wrap all of my data access code with WCF services, .NET 3.0 came and went with no changes to my data access methodology. In late 2007, I had lost all love of my SqlHelper and my Database/DataAccessLayer classes. With .NET 3.5 and Enterprise Library 4.0, I no longer felt the need to roll my own. .NET now had extension methods for me to extend Enterprise Library however I pleased. Enterprise Library supported System.Transactions making its use a dream if behind a WCF service that allowed transaction flow. With a succinct 190 lines of extension code, I had it made in the shade with Enterprise Library 4.0. In fact, I haven’t used anything since.

The consuming code was almost exactly the same. You’ll notice the SqlCommand has changed to DbCommand. The SqlDbType has changed to DbType. Other than that, it feels and works the same.

// Object generation method
private static Document GenerateDocumentFromReader(IDataReader returnData)
{
     Document document = new Document();
     if (returnData.Read())
     {
         document = new Document(
             returnData.GetInt32("DocumentId"),
             returnData.GetInt32("DocumentTypeId"),
             returnData.GetString("DocumentTypeName"),
             returnData.GetByteArray("DocumentBinary"),
             returnData.GetString("FileName"),
             returnData.GetString("Description"),
             returnData.GetString("ContentType"),
             returnData.GetInt32("FileSize"),
             returnData.GetString("MD5Sum"),
             returnData.GetString("CreatorEmpID"),
             returnData.GetDateTime("CreateDt"),
             returnData.GetString("LastUpdateEmpID"),
             returnData.GetDateTime("LastUpdateDt"));
     }
     return document;
}
public static Document GetDocumentByDocumentID(int documentId)
{
     Database db = DatabaseFactory.CreateDatabase();
     DbCommand cmd = db.GetStoredProcCommand("usp_Document_GetDocumentByDocumentId");
     db.AddInParameter(cmd, "DocumentID", DbType.Int32, documentId);
     GenerateObjectFromReader<Document> gofr = new GenerateObjectFromReader<Document>(GenerateDocumentFromReader);
     Document document = db.CreateObject<Document>(cmd, gofr);
     return document;
}

With a full suite of unit test projects available for download with the Enterprise Library source files, the fear should be abated for the remaining holdouts. Getting started is as easy as including two DLL references, and adding 5 lines of config. You can’t beat that!

I downloaded Enterprise Library 5 last week. I’ve been making use of new features such as result set mapping (eliminating the need for my object generation extensions), parameter mapping, and accessors that bring them all together. There’s a bunch of inversion of control features in place as well. I think I’ll be quite comfortable in my new EntLib5 home.

For the few MSMQ or NetMsmqBinding WCF users I’ve encountered, here is an error you may encounter in a highly secured environment.

0xC00E008F Binding to the forest root failed. This error usually indicates a problem in the DNS configuration. MQ_ERROR_DS_BIND_ROOT_FOREST

This is most likely another firewall problem. If port 3268 is not open, MSMQ cannot register or authenticate with the user’s certificate in AD. Here is the port description:

3268/TCP,UDP msft-gc, Microsoft Global Catalog (LDAP service which contains data from Active Directory forests)

Max Depree... I think I need a Herman Miller chair for the home office.

As I sit through the last few hours of Juval Lowy’s Architects Master Class, drinking my Honest Tea, I see a great quote on the inside of the bottle. How appropriate.

“The greatest difficulty in the world is not for people to accept new ideas, but to make them forget about old ideas.”

-- John Maynard Keynes

Despite its pitiful adoption in the developer community, I am implementing Transactional NTFS (TxF) transactions using the Microsoft.KtmIntegration.TransactedFile class. This allows me to reap the benefits of TransactionScope and distributed transactions for file operations (e.g. creates, updates, deletes). This is the only missing piece for typical transactional business applications. With the “KTM” and “KtmRm for Distributed Transactions” services, available only on Vista, Windows 7, and Windows Server 2008, file operations will roll back if the TransactionScope is not completed.

There’s just one problem… Transactional NTFS does not work with file shares. I can’t remember the last time I put a “C:\FileStore” reference in a config file. A friendly share like “\\server\FileStore” is always preferred, especially since DFS came about. Attempting to use a share results in the following error message:

The remote server or share does not support transacted file operations

Don’t read this as “your remote server” or “your remote share”, but rather “all remote servers and shares”. As mentioned in this MSDN article, TxF is not supported by the CIFS/SMB protocols. The error was probably written with the expectation that one day some remote servers and shares would support TxF. I emailed Microsoft about it and received a response fairly quickly. The response was simply:

“We understand the need and have plans to eventually support TxF over SMB2, but we’re not there yet and are not ready to announce if or when this will be supported. When it is the documentation will be updated.”

I’m not getting my hopes up, but Windows Server 2011 looks to be our only hope before .NET changes beyond recognition and TxF is a distant memory. Until then, I wrapped up all of my TxF code in a WCF service and install that service on the server with the FileStore folder.

MSDN article – When to Use Transactional NTFS

      http://msdn.microsoft.com/en-us/library/aa365738(v=VS.85).aspx

TxF Sandbox – Sample Projects (including Microsoft.KtmIntegration.TransactedFile)

      TxFSandbox.zip

The sole 2010 offering in the USA of IDesign’s Architect’s Master Class conducted by the man himself, Juval Lowy, is only a few weeks away. I checked in at the IDesign web site, and found some updates the world needs to see.

• The IDesign C# Coding Standard – updated for .NET 4.0
• The IDesign WCF Coding Standard – updated for .NET 4.0
• The IDesign Code Library - updated for .NET 4.0, including Azure AppFabric Service Bus extensions and the Service Bus Explorer (priceless)

If you want to learn something new every day, start at the top of the IDesign Code Library and step through one example each day. Be careful, you might need to re-write every line of code you’ve ever written.

I’ve been using the full desktop version for only a few days, but already love this tool! In just a matter of minutes, I am able to draw a mockup for a screen and start getting feedback. Here is a sample, a CodePlex-like UI, that took less than five minutes.

Creating this mockup goes as follows:

1. Drag a browser element onto the design surface, stretch it and type a URL and title
2. Using the “Quick Add” box, type “tab” <Enter> to add the tab control. Type “Home, Downloads, Documentation, Discussions, etc.” and <Enter> again. Reposition the tab element.
3. Using the “Quick Add” box, type “link” <Enter> twice to create a link bar for the top right and another link bar to be positioned below the tabs.
4. Continue using the “Quick Add” box to add textboxes, label text, search boxes, images, subtitle text, links, and data grids. Type values and reposition.

This tool has a few features I find to be more compelling than the imitators:

1. Speed – Being able to create and recreate mockups with such speed and usefulness really promotes a good prototyping/review process.
2. Look and Feel – Having an obvious “rough draft” look and feel gets your customer thinking about usability and element placement and less about colors, styles, images, copy, fonts, etc. This makes for a very productive meeting involving customers in “the task at hand” and nothing more.
3. Demo mode – Similar to MS SketchFlow, you can assign links to buttons that can simulate events. When entering full-screen demo mode, you can click on these links and move between your mockups as if it were a live site. It also has a large pointer (arrow) and always points to the center, staying out of the way.
4. Good user community – tons of free extensions like wizards, toolbars, reporting chart elements, and plenty of iPhone/iPad junk for “those people”. I’m a PC.
5. Updates and Upgrades – weekly updates based on user feedback and perpetual free upgrades. My emails have been answered within 12 hours, which is quite impressive considering the 6+ hour time difference.

This is free to try on the web, but you cannot save or export the images. You can, however, export the PNG with a watermark. Desktop version gives you some nice added features like copy/paste, undo, and demo mode.

Try it out, buy it, and love it!

Check out http://www.balsamiq.com to see one of the best mockup tools ever created. I just started using it and it has already paid off. I have made several mock-ups in minutes that cut my development time in half. Having the ability to demo a new UI to users and change it during the conversation is priceless.

More to come… samples too!

In a previous post, I discussed solutions to the dreaded “The flowed transaction could not be unmarshaled” error commonly experienced when using MSDTC transactions with WCF, SQL, TxF, etc. I have once again experienced the un-trusted domain scenario, and can now report with certainty that adding hosts file entries on both machines will correct the problem. Testing this solution with DTCPing.exe between the two machines proves that making only the hosts file change acquaints the client and server and allows distributed transactions to occur.

You will find many blog and forum post non-solutions. Adding the hosts file entry or the equivalent domain redirects are the only solutions when working with two machines in disparate, un-trusted domains. Some of the non-solutions you’ll find go so far as to say to change your SQL connection string to prevent current (ambient) transaction enlistment. Not quite a complete solution as your first rollback unit test will fail.