Data access patterns in SQLCLR

Question

I'm using SQLCLR in my project and, for the record, I'm very pleased with it. However, I can't find any good sources of information for good data access patterns.

Usually I do communication between .net and SQL using stored procedures because I always want an API for my database. However, in this case the .net code is part of the API, so SPs seem bad.

Linq2SQL does not exist in SQL server (although it can be installed by doing things DBAs won't like), so it's not an option.

What I currently have is my code cluttered with standard ADO.NET code like

using (SqlCommand cmd = c.CreateCommand()) {
    cmd.CommandText = "SELECT ... FROM ...";
    using (SqlDataReader rdr = cmd.ExecuteReader()) {
        DoSomething(rdr);
    }
}

and, although, it works, it just seems very much like the wrong way to do it.

How do other people do it?

Answer 1

I use XSLT to generate C# code for DAL. I usualy load the XML from the database definition itself (eg. some form of SELECT name, type, length, ... FROM sys.columns JOIN sys.tables JOIN sys.types FOR XML PATH) and I have developed in time custom XSLT transformation for code generation. I have them added as part of the Visual studio build process itself, similar to this blog: http://rusanu.com/2009/04/11/using-xslt-to-generate-performance-counters-code/ (the blog is about performance counters generaiton, but it applies just as well to DAL code). I also build types for the result sets using XSLT code gen.

While on heavy clients this approach overlaps with ORMs and ADO datasets functionality (I still use it on those clients, but that is my problem...), in SQLCR is imho the best fit, due to the SQLCLR specific limitations.

This approach is first and foremost very flexible, allowing me to quickly do global changes that affect every DAL entry point, while I keep absolute control over the code base (no external dependencies, no external introduced bugs). It is extreamly lightweight compared to ADO datasets.

I would only trade off this approach for LINQ, because of the added value of passing IQueryable objects around. But in SQLCLR, as you know, this is not yet a viable option.

Answer 2

For the limited places that SQLCLR actually provides a performance gain over proper set based TSQL, I do data access exactly how you have it shown above. You'd have to be doing some pretty heavy loop based processing that can't be done set based, XML parsing, or extremely complex math to really need to use SQLCLR. If you are using SQLCLR just for data-access, you are doing so at the cost of performance. If you'd like some demonstrations of this let me know and I'll pull my examples for AdventureWorks out of my Presentations from last year.