The Perfect Method To Access SQL Server Data

As I continue to program and experiment with C#/.Net technologies I develop new “favorite” ways of doing things. This is my current iteration of the perfect call to SQL Server. I use this style of method in middle layer classes that are called from ASP.NET pages and need access to SQL Server.  This style of method is built on many things, including a good understand of how SQL server works, real life performance and scalability testing, and the new fondness for yield return.

public IEnumerable<Member> Members()
{
    using (SqlConnection sqlConnection = 
        new SqlConnection(
            ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString))
    {
        sqlConnection.Open();

        StringBuilder sql = new StringBuilder();
        
        sql.Append("SELECT memberId\r\n");
        sql.Append("FROM tbMembers\r\n");
        sql.Append("WHERE deleted = 0 AND companyId = @CompanyId\r\n");

        SqlCommand sqlCommand = new SqlCommand(sql.ToString(), sqlConnection);

        sqlCommand.Parameters.AddWithValue("@CompanyId", companyId);

        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
                yield return (new Member(sqlDataReader));
            }
        }
    }
}

Uses the Microsoft .NET Data Layer

One of the most important parts of this method, is that it uses the .NET Data Layer instead of in-house abstract data layers.  Many times I will come into a company where a developer has designed a data layer to access SQL server that they think is better then what ships in the .NET CLR.   Most of the time he/she has wrapped the .NET Data layer with methods that try/catch, do tracing, build SQL on the fly, and objectify Select/Insert/Update/Delete.  I have yet to see a Data Layer that works better than Microsoft, or a developer that put the time into their Data layer that Microsoft put into theirs. 

 

One nice thing about the .NET data layer is every developer that has access SQL server has used this data layer, so new developer coming into the company don’t have to learn the in-house model.  And in-house employees don’t have to learn something that will not transfer to other companies.

 

Use Inline SQL

The method uses Inline parameterized SQL, not string concatenation inline SQL nor stored procedures.  Inline parameterized SQL solves the problem of inflated query caches on the SQL server and SQL injection attacks unlike string concatenation inline.  It also allows the developers to use easily use source control to track changes to the SQL being executed – unlike stored procedures.  An unlike stored procedures it ties the method directly to the SQL, preventing stored procedure reuse – usually leading to more generic stored procedures and slow performance.

 

This method uses StringBuilder makes the SQL faster to construct and by adding carriage returns and line feeds, makes it very readable if it needs to be copied from Visual Studio and pasted into SQL Server Management Studio to test the results.

 

Notice that the SQL is written by hand, there is no automated query constructor in C# code.  Writing it raw like this allows for performance optimizations per query that are not possible in an abstraction method.  I see a lot of companies use code contained in in-house access layers to construct their SQL which usually adds overhead to the calls.

Uses a Forward Only Cursor With IEnumerable

When your SQL call traverses data (requesting a set of rows) that are not found in a non-clustered index there is good chance that SQL Server will page data from the disk into RAM.  This can causes considerable performance issues.  The method above relies on IEnumerable and yield return to just return one row at a time, and the default forward only cursor on SQL server and the .NET data access layer only reads the row when the Read() method is called on the SQLDataReader.  What does this mean?  Only the rows that you access via the IEnumerable get paged off the disk (clustered seeks only).

 

For example if you call the method above like this:

 

foreach(Member member in Members())
    if (member.Name == "Wayne")
        break;

There is a good chance that if the name Wayne exists in the tbMembers table it will be found before SQL server pages all the data off of the disk.

Leverages The Classes Ability to Read a SQLDataReader

Notice that the method doesn’t know how to convert the data coming back from the SQL server into properties of the class.  This is the classes job, by pushing this task to the class you only need to change the code in one place (in the class constructor that takes the SqlDataReader) when a column changes.  It also allows other classes to construct instances of the table class (Member in this case) from calls to the database.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}