Creating Maintainable Database Queries In C# (with source code)

By Ashiq Alibhai

Many applications (and websites, especially) rely on some sort of database to get their work done. As projects and teams grow beyond a one-person-working-in-his-garage model, you can’t rely on the fact that you, or someone who knows the query well, will be the one in charge of modifying it when the time comes. So what are some of the practises you can implement in order to create easily-maintainable and understandable queries?

If you have a website, aim for three-tiered architecture and create objects in your App_Code folder (data/application tier) that call the database—don’t code the query directly in the class! Or, if you have an application, create some centralized folders/classes to perform database queries. The more you can abstract (eg. SomeObject.FetchData vs. SqlHelper.Execute…), the easier it will be if you have to switch data-sources later.

Stored procedures–while offering a dubious performance upgrade–also come with the tag that they are very difficult to maintain. Not only will newbies to SQL become befuddled by the syntax, you need, but they require database-level permissions to modify it. And, in terms of releasing, it adds the extra maintenance of having to propagate stored procedures along with data—as opposed to a query embedded in a .cs file.

C# string-concatenation requires creating a new buffer of the total length of both strings, then copying both into it. If you use concatenation in several places, especially if your code gets run frequently, it may become a bottleneck. Avoid it by using stringBuilder.Append(…) instead of string1 + string2.

As much as possible, keep your query in one piece and avoid breaking it up into parts as much as possible. This makes your query more readable, and also more efficient (in terms of string-concatenation, which is very slow because it requires allocating space for a new string and copying over the original). (You can even use the @ sign in front of your constant to allow you to break up the assignment over multiple lines without concatenating at each line – which, if you use proper indenting, also makes your query much easier to read.)

The only exception to keeping your query together is if you have if-conditions in a where clause, such as the following example:

            StringBuilder queryBuilder = new StringBuilder();
            queryBuilder.Append(@"
                SELECT
                    *
                FROM
                    Contacts
                    INNER JOIN ContactPurchases ON
                        Conacts.Id = ContactPurchases.ContactId
                    INNER JOIN Purchases ON
                        Purchases.Id = ContactPurchases.PurchaseId
                WHERE
                    Purchaser.Id = @purchaserId
            ");

            if (targetDate != DateTime.MinValue)
            {
                queryBuilder.Append(" AND Purchases.Date = @targetDate");
            }

            // ... similar checks and additions to the query

Of course, there are always reasons to break up query-building into more complicated logic—such as for-loops, cases, etc. as your application requires. The trade-off is yours to consider!

Wherever possible, code logic into the SQL query instead of the page—so functions like while-loops, cases, checking for null, etc. Should be done inside the query.

Why? Just like keeping your query in one piece, it allows anyone sufficiently skilled in SQL to read and understand the query without the control flow jumping all over the place. Plus, it makes it easy to copy-paste the query and execute it.

If you have parameters to pass to your query, you should be using SqlParameter instances in order to avoid potential SQL injections. Additionally, instead of building your query line-by-line, like this:

            SqlParameter[] parameters = newSqlParameter[2];
            parameters[0] = newSqlParameter("@purchaserId", purchaserId);
            parameters[1] = newSqlParameter("@targetDate", targetDate);

Build them using the array initializer, like this:

            SqlParameter[] parameters = {
                new SqlParameter("@purchaserId", purchaserId),
                new SqlParameter("@targetDate", targetDate)
            };

This has one main advantage over the array: it doesn’t require you to manually maintain the list (and count) of parameters, which can become a maintenance headache if the number goes over three or four parameters.  Read more about using SqlParameter arrays.

Above all, trust your judgement and the judgement of your peers. Sometimes, past (or existing) coders may choose to write queries certain ways. Or maybe you feel that this one piece of logic really should be inside a C# for-loop.

It’s up to you! Ultimately, it’s all a trade-off, and the goal here is not to create the "ultimate" style of writing queries, but only to guide you towards building something that creates readable and maintainable queries.

Other Interesting Posts

• PHP VS. ASP.NET
• Creating SqlParameters Best Practices
• Are You a Sharp Developer?? (Part 1)