The SqlReader Object

• Download SqlReader.cs - 4 Kb

Introduction

A while back I became frustrated with the fact that .NET doesn’t provide any method of querying a database that takes less than about 8 lines. Every time I wanted to make a query on a database, or even run a stored procedure in SQL, I found it impossible to do without at least three sets of squiggly brackets. First, I open the SQL connection, then I create the SQL command, then I use a data reader to run through the data and get whatever I want out of it. My code had constructions like this all over the place:

List<string> result = new List<string>();

using (SqlConnection cn = new SqlConnection("Data Source=DatabaseServer;
                                             Initial Catalog=Database;
                                             User ID=UserID;Password=Password")) {
    cn.Open();
    using (SqlCommand cm = cn.CreateCommand()) {
        cm.CommandText = "mystoredproc";
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.AddWithValue("@param1", "1");
        cm.Parameters.AddWithValue("@param2", "2");
        using (SqlDataReader dr = cm.ExecuteReader()) {
            if (dr.HasRows) {
                while (dr.Read()) {
                    result.Add(dr.GetString(0));
                }
            }
        }
    }
}

return result;

Writing something like this can get very tedious, and although I do have a tendency to type pretty quickly, this still becomes nothing more than a rote waste of time. In an effort to fix this, I created two classes called SqlReader and SqlData. Both classes are serializable.

SqlReader Class

The SqlReader class can perform queries using the command types stored procedure and Text. It differentiates the user’s intention by quite simply looking for a space in the query string. If there is one, then it’s a text commandtype, if there’s not, then it’s a stored procedure.

The class is created with a static constructor as follows:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
                                 User ID=UserID;Password=Password", 
                                 "select * from myTable");

It takes two arguments, the first being the SQL connection string, and the second being the actual query. If this is a stored procedure, then the query would look like the following:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
                                 User ID=UserID;Password=Password", "mystoredproc");

Once again, the SqlReader class itself is smart enough to discover whether you want to execute a stored procedure or a text command. The constructor looks like this in the code:

/// <summary>
/// Creates a SqlReader object with CommandType of Text
/// </summary>

/// <param name="connectionString">The connection string to use to connect to 
///                                   the server.>/param>

/// <param name="commandText">The text to execute on the SQL server.>/param>
public static SqlReader Create(string connectionString, string commandText) {
    SqlReader sr = new SqlReader();
    sr._connectionString = connectionString;
    sr._commandText = commandText;
    if (commandText.Trim().Contains(" "))
        sr._commandType = CommandType.Text;
    else
        sr._commandType = CommandType.StoredProcedure;
    return sr;
}

Note that the commandtype is set based on the text of the query. If there’s a space, then the commandtype is text, if there is no space, then the command type is a stored procedure.

Let’s say you need to add parameters to the stored procedure. The following will do the trick:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
                                 User ID=UserID;Password=Password", "mystoredproc")
                                 .AddParam("@param1", "1").AddParam("@param2", "2");

Now we’ve added two parameters to our query, the first being @param1 and the second being @param2. Each AddParam method returns a new SqlReader object, so there is a sort of recursion in the process that will fill in the parameters. Using this system, an unlimited number of parameters can be added to the query.

Now for execution. There are two methods for execution, ExecuteQuery() and ExecuteNonQuery(). These correspond to the methods of the same name that are in the SqlCommand class. The only difference is that ExecuteQuery() now returns a SqlData class, while ExecuteNonQuery() is simply a void.

SqlData sd = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
                               User ID=UserID;Password=Password", "mystoredproc")
                               .AddParam("@param1", "1").AddParam("@param2", "2")
                               .Execute();

Notice I’ve changed the type of our newly created variable to a SqlData type, this is because the Execute() method returns a SqlData object, and not a SqlReader object. This is important for several reasons, but to be understood, I should explain the SqlData class.

SqlData Class

Each row from a SQL query is stored in a SqlRow class, which maintains the values and the column names of each row. A collection of these rows makes up the SqlData class. The SqlData class has several methods of acquiring values, several of which are overloaded: GetValueList, GetStringList, GetInt32List, GetInt64List, GetValue, IsNull, GetString, GetInt32 and GetInt64. The list commands will take a column name or 0-based column ID, and will return a list of all the values within that particular column. This is great for iteration loops. I have code that looks similar to the following:

foreach (string s in SqlReader.Create("Data Source=DatabaseServer;
                     Initial Catalog=Database;User ID=UserID;Password=Password", 
                     "mystoredproc").AddParam("@param1", "1").AddParam("@param2", "2")
                     .Execute().GetStringList(0))
    Console.WriteLine(s);

This will write to the console every string from the leftmost column returned when executing mystoredproc with @param1 set to 1 and @param2 set to 2.

The methods that don’t return a list are overloaded four ways. The first two overloads don’t indicate a row, and will always return the value from the first row returned in a query. They can be called by either specifying the column number or the column name. The second two overloads do call a row by calling its 1-based row number, and either the column name or column number. In this way, other iterations can be used.

static void Main(string[] args) {
    SqlData sd =SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
                                  User ID=UserID;Password=Password", "mystoredproc")
                                  .AddParam("@param1", "1").AddParam("@param2", "2")
                                  .Execute();
    for (int i = 1; i < sd.RowCount; i++) {
        Console.WriteLine(string.Format("Data from column 1: {0}, 
		                         Data from column 2: {1}", sd.GetString(i, 0),
                                         sd.GetString(i, 1)));
    }
}

The Heart of the SqlReader Class - The Query

If you’ve made it this far, here’s the fun stuff. The heart of the SqlReader class is the Execute method. This is where all the fun work happens. Here’s the code:

/// <summary>
/// Executes a query.
/// </summary>
public SqlData Execute() {
    if (_hasRun)
        _sqlData = new SqlData();
    _hasRun = true;
    if (string.IsNullOrEmpty(_connectionString))
        throw new Exception("Morton's Common Tasks - SqlReader - 
		             No Connection String Entered");
    if (string.IsNullOrEmpty(_commandText))
        throw new Exception("Morton's Common Tasks - SqlReader - 
		             No Command to Perform");
    using (SqlConnection cn = new SqlConnection(_connectionString)) {
        cn.Open();
        using (SqlCommand cm = cn.CreateCommand()) {
            cm.CommandType = _commandType;
            cm.CommandText = _commandText;
            foreach (KeyValuePair<string, object> kvp in _parameters)
                cm.Parameters.AddWithValue(kvp.Key, kvp.Value);
            if (_returnData) {
                using (SqlDataReader dr = cm.ExecuteReader()) {
                    if (dr.HasRows) {
                        while (dr.Read()) {
                            SqlRow sqlRow = new SqlRow();
                            for (int i = 0; i < dr.FieldCount; i++) {
                                if (string.IsNullOrEmpty(dr.GetName(i)))
                                    sqlRow.AddSqlData("column_" + i.ToString(), 
                                                       dr.GetValue(i));
                                else
                                    sqlRow.AddSqlData(dr.GetName(i).ToLower(), 
                                                      dr.GetValue(i));
                            }
                            _sqlData.Add(sqlRow);
                        }
                    }
                }
            } else {
                cm.ExecuteNonQuery();
            }
        }
              
    }
    return this.Result;
}

The first couple of lines check to see if the query has run or not. If it has, then we need to reset the value that will be returned. We will throw a couple of exceptions if our query and connection string are null, and then we’ll start in on the query.

First, we connect to the server using the connection string, and open the connection. The command type and command text are set. After this, the parameters are added to the command.

The _returnData variable is true by default, and may be set false by the ExecuteNonQuery() method if it is called. If _returnData is true, then we will be looking for a result. If we are, then we create a SqlDataReader object, and add all of our data to the SqlRow, then all of our rows to the SqlData object. If there’s a column without a name, then the column name is programmatically set at an arbitrary value. In this case, it would set them to column_1, column_2, etc…. If there is a column name, then the actual column name is used.

Finally, at the end, the result will be returned. The line “return this.Result;” uses the following property to return the _sqlData object.

/// <summary>
/// An object returning the result of a Sql Query.
/// </summary>
public SqlData Result {
    get {
        return _sqlData;
    }
}

Conclusion

I’ve been using this class in some production-level applications now for a while without problems, and it has been saving me an immense amount of time in the construction of queries.

Feel free to use this code. Let me know how it works!