Home > Programming > Making .NET 2.0 SQL Connections Fast and Efficient

Making .NET 2.0 SQL Connections Fast and Efficient

Lately, while cleaning up some legacy code at my company and upgrading it from .NET 1.1 to .NET 2.0, I have run into some interesting coding habits concerning database connections and using stored procedures. I wanted to explore these different methods and detail what is good, bad, or just plain ugly about each of these methods.

To create a connection to a database, most beginning C# developers are taught to open the connection like this:

SqlConnection conn = new SqlConnection("MyConnectionString");
conn.Open();
.
.
.
conn.Close();

There is nothing wrong with this code (it will work correctly) and the connection is closed when it is done being used. That does not mean that this is the best method available.

What is often neglected (or plain forgotten about) is the keyword using available in C# that allows you to “forget” about managing the objects created within the using-block. What this means is that once a using-block is executed, Dispose() is automatically called to clean up you objects for you. Think of it as a lazy shortcut for developers who do not want to fret over closing connections or cleaning up after other objects. Using a using-block (boy does that sound funny) on the above code becomes:

using (SqlConnection conn = new SqlConnection("MyConnectionString")) {
    conn.Open();
    .
    .
    .
}

No more explicitly closing the connection in the code; the using keyword takes care of that for you. This is not what really troubles me, though. What I have been more concerned with is programmers incorrectly utilizing the using keyword in their code. The offender is explicitly closing the connections within the code. Why do they do this? Most likely because they do not really know what is going on behind the scenes.

So, everyone is using the using keyword now, and all is right with the world, right? Unfortunately, no. The using keyword gives you ease of coding, but code optimization is more important, and more often ignored.

Consider the following code:

string connString = "MyConnectionString";

using (SqlConnection conn = new SqlConnection(connString)) {
    string SQL = "SELECT * FROM table1";

    using (SqlCommand cmd = new SqlCommand(SQL, conn)) {
        conn.Open();
        .
        .
        .
    }
}

I have one problem with the above code – it is declaring variables to hold some text that is only going to be used once in this code. There is no reason for this. The strings should be built inline within the method parameter, instead of creating a variable first and then using that variable. This may simply be a matter of taste, but these are what I would call wasted variables – wasted because it is memory that does not need to be taken up in this way. In the above code, the SQL variable may only remain in memory for the length of the execution of the first using-block, but the connString variable could be taking up memory it does not need to for a much longer period of time. A better way to implement this code is as follows:

using (SqlConnection conn = new SqlConnection("MyConnectionString")) {
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM table1", conn)) {
        conn.Open();
        .
        .
        .
    }
}

This eliminates the variables and frees up memory that would have been otherwise wasted. So, now we are using the using keyword correctly, and we are no longer using wasted variables. It sounds like we are headed in the right direction to make .NET 2.0 SQL connections fast and efficient. We are, but now we have what I think shows the most variation in how programmers understand the relationship between .NET code and the SQL database – and that is passing variables to stored procedures.

There are two important steps that I have seen programmers skip that are important to the efficiency and speed of a program. The first is forgetting to let the SqlCommand know that it is dealing with a stored procedure and not a text command or a table. This can be accomplished simply by adding the following code:

cmd.CommandType = CommandType.StoredProcedure;

The other step programmers sometimes skip is with defining parameters. There is a “shorthand” way of defining a parameter, and it is a nice way of condensing code (more on this in a moment) but you should always let the SqlCommand know what the variable type is. This is how a parameter is sometimes defined:

cmd.Parameters.Add("@UserId).Value = "Some Value";

As you can see, there is no type defined in the parameter, and this wastes time because now the SQL server must figure out what type it is supposed to be before executing the statement. The verbose way of defining a parameter is like this:

SqlParameter spUserId = new SqlParameter("@UserId", SqlDbType.VarChar, 15);
spUserId.Direction = ParameterDirection.Input;
spUserId.Value = "Some Value";
cmd.Parameters.Add(spUserId);

This defines everything that needs to be defined, and the parameter is ready to go, with a value to send to the SQL server and everything. This code is kind of clunky, though. It lacks style and a sense of “I know what I am doing” that shows peers how technical you really are. To remedy that, we can condense all of that code in to one nice, slick, clean line of code that says “ask me anything about this stuff, I know it.”

cmd.Parameters.Add("@UserId", SqlDbType.VarChar, 15).Value = "Some Value";

Here is a final version of the structure that I believe is the best, most efficient, and fastest way of creating a .NET 2.0 SQL connections.

using (SqlConnection conn = new SqlConnection("MyConnectionString")) {
    using (SqlCommand cmd = new SqlCommand("Some_sp", conn)) {
        conn.Open();
        cmd.Parameters.Add("@UserId", SqlDbType.VarChar, 15).Value = "Some Value";
        .
        .
        .
    }
}

With this way of creating connections, you are correctly utilizing the using statement to take care of closing connections, etc. You also do not waste any memory that could potentially be valuable to your Web server (you never know). You also let the SQL server know everything it needs to ahead of time so that it does not have to blindly figure out things on the fly. This makes your program faster and more efficient.

I am sure there are those that will disagree, but this is a good way of setting up your code to get the job done right. Don’t waste processor time with things that will be done for you, don’t waste SQL server time with figuring out things you could have told it in the beginning, and don’t explicitly create things that can be done in a slicker and leaner way.

Good luck, and happy coding!

Advertisements
Categories: Programming Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: