Data Access Architecture and IDisposable Faux-Pas

Today I was part of a conversation with another developer about the manner in which he was creating his database access layer for his not insubstantial VB.NET project.  He was making the common mistake of merging data access methods with business layer methods to create an interesting cludge of classes that were beginning to become quite substantial (circa 5000k+ lines).  What made it worse was his insistence on not following the established norms of not using inline SQL within methods.  At once the alarms sounded within me and I asked him whether he had thought of the future consequences to do with maintainability and security.  His response was generally as follows: 

"...I see no advantage of using Stored Procedures as they are awkward to use difficult to debug and anyway, I prefer to see all the code in one place..."

Excuse me?

What struck me was that while the .NET Data Access Architecture Guide pattern and practices guide is pretty much a must-read for any .NET developer, this person had obviously either not read it and/or understood it.  Upon asking he said he had glanced over it, "... but you know, it's Microsoft, shouldn't pay much attention to what they say, I mean, look at how insecure Windows XP is...".

Excuse me?

He had also not deigned it necessary to abide by the dispose pattern.

Rather than argue with him I pointed him to two places on the guide...(pasted from .NET Data Access Architecture Guide for benefit of blog):

Stored Procedures vs. Direct SQL

….

You should use stored procedures instead of embedded SQL statements for a number of reasons:

  • Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
  • Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
  • Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
  • Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
  • Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

And...

Using Stored Procedures

Using stored procedures for updates allows the administrator of your database to implement more granular security than is available with dynamic SQL, as well as more sophisticated data integrity checks. For example, the stored procedure might insert an entry into an audit log as well as perform the requested update. Stored procedures can provide the best performance as well because of offline query optimization performed within the database on stored procedures. Finally, the insulation between the database structure and the application that stored procedures provide allows for easy maintenance.

Because ADO.NET applications that use stored procedures provide many benefits and are no more difficult to implement than those that make changes directly to the database, this approach is recommended in nearly every case. The exception to this is if you must work with multiple back ends or a database, such as Microsoft Access, that doesn't support them. In those cases, use query-based updates.


and then to Implementing a Dispose Method on MSDN2.

Of course he has now read these articles.  And of course I informed the Project Manager of the large potential for future issues based on this knowledge.  I only hope this was discovered early enough for changes to be factored in.  Although I can see some judicious triage being actioned either way. 

The above example occurs way too often for my comfort...