Ad Hoc Queries

An ad hoc query is created dynamically to retrieve information that's required, such as a list of contact addresses; such a query might contain qualifying information that's known, such as a user id. For instance, if your application needed to perform a query to find all orders (in a table named Orders) for a customer with the customer ID of 567, you might use the following SQL query:

 SELECT * FROM Orders WHERE CustumerID=567 

Taking that query from SQL to programming code, you might have the following code shown in Listing 4.1:

Listing 4.1 This Code Performs a Query for the Customer ID of 567.

C#

 SqlConnection objConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=SomeDatabase"); try {     objConnection.Open();     SqlCommand objCommand =       new SqlCommand("SELECT * FROM Orders WHERE CustomerID=567",       objConnection );     SqlDataReader reader = objCommand.ExecuteReader();     // Do something with the data...     reader.Close();     objConnection.Close(); } catch { } finally {     if( objConnection.State == ConnectionState.Open )     {         objConnection.Close();     } } 

VB

 Dim objConnection as new _ SqlConnection("server=localhost;uid=sa;pwd=;database=SomeDatabase" ) Try     objConnection.Open()     Dim objCommand as new _       SqlCommand("SELECT * FROM Orders WHERE CustomerID=567", _       objConnection )     Dim reader as SqlDataReader = objCommand.ExecuteReader()     ' Do something with the data...     reader.Close()     objConnection.Close() Catch Finally     If objConnection.State = ConnectionState.Open Then         objConnection.Close()     End If End Try 

Dynamically Created Queries

The preceding code works great if the customer ID is always 567, but that won't usually be the case. Instead, you'll either have to use a bound parameter or dynamically create a string that contains the appropriate SQL for the query.

Let's assume, for the sake of the examples in this section, that the customer ID is contained in a session variable named CID. The session ID may have been assigned at customer login, but in any case, the session variable contains the unique identifier for the customer who's logged on.

Using Parameter Binding

Parameter binding with SQL commands was covered in Chapter 3 in the section entitled "Parameter Binding with SQL Commands." I'd like to show a quick example here, in case you haven't read Chapter 3. The following code shown in Listing 4.2 uses a SqlCommand parameter with which the customer ID value is bound. This arrangement makes creating queries based on dynamic information fairly easy.

Listing 4.2 This Code Performs a Query Based on a Bound Parameter.

C#

 SqlConnection objConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=SomeDatabase"); try {     objConnection.Open();     SqlCommand objCommand =       new SqlCommand("SELECT * FROM Orders WHERE CustomerID=@CustID",       objConnection );     objCommand.Parameters.Add("@CustID", SqlDbType.Int );     objCommand.Parameters["@CustID"].Direction =       ParameterDirection.Input;     objCommand.Parameters["@CustID"].Value =       Convert.ToInt32( Session["CID"] );     SqlDataReader reader = objCommand.ExecuteReader();     // Do something with the data...     reader.Close(); } catch { } finally {     if( objConnection.State == ConnectionState.Open )     {         objConnection.Close();     } } 

VB

 Dim objConnection as new _ SqlConnection("server=localhost;uid=sa;pwd=;database=SomeDatabase" ) Try     objConnection.Open()     Dim objCommand as new _       SqlCommand("SELECT * FROM Orders WHERE CustomerID=@CustID ", _       objConnection )     objCommand.Parameters.Add("@CustID", SqlDbType.Int )     objCommand.Parameters("@CustID").Direction = _       ParameterDirection.Input     objCommand.Parameters("@CustID").Value = _       Convert.ToInt32( Session("CID") )     Dim reader as SqlDataReader = objCommand.ExecuteReader()     ' Do something with the data...     reader.Close() Catch Finally     If objConnection.State = ConnectionState.Open Then         objConnection.Close()     End If End Try 

Two Clarifications

I've been reading technical books for 25 years, and I have been teaching and speaking for 5 years. Over the years, when I've read technical books, I always seemed to miss something either it wasn't clear or I just plain missed it. And I see my students go through the same thing. For that reason, I try to anticipate these spots and clarify them. Forgive me if you don't need these two clarifications, but I am guessing that half of you do.

Clarification One is this: The names used for the session variable, the database field, and the parameter do not have to be the same thing. In the above example, the session variable is named "CID", the parameter "@CustID", and the database field "CustomerID". I purposely chose different identifications for each of these objects to emphasize the point that they are different objects and therefore can be named differently. I can't tell you how many students I've had over the years who interchanged session variables with parameters because they had the same name.

Clarification Two is this: C# cannot convert a session variable to an integer because a session variable is an object, and C# won't perform an implicit conversion, but VB will. The C# compiler sees a session variable as a generic object, not knowing what the variable contains (in this case, an integer). VB, however, finds out what's in the session variable and performs the implicit conversion automatically. For instance, the following will cause a compile error in C#:

 Session["Test"] = 14; // The following line attempts to implicitly convert an object //   to an integer. The compiler will not allow this  without a //   type cast. int nTest = Session["Test"]; 

while the following will compile with no difficulty in VB:

 Session("Test") = 14 Dim nTest as Integer = Session("Test") 

Why would C# introduce a complexity that VB does not have? The answer has to do with the difference in the languages. VB has always performed implicit conversions, going back to the fact that all variables in VB5 and VB6 were variants (and going back even further, most of the earliest versions of BASIC had a dynamic type system). C# was designed with tighter type safety, and thus requires more explicit coding.

In some ways this dynamic nature makes VB easier and more convenient to use. But this freedom can backfire because the compiler may not catch errant code. Suppose, for example, that your code expects an integer, but the object from which you're getting an integer value contains a string. The compiler won't catch this error, but at runtime an error will be generated. Hopefully, the error will be caught when the software is tested, but C# catches the type conversion error at compile time.

So how do we coerce C# to get an integer value out of a session variable? There are two recommended ways. I normally use the Convert.ToInt32() method, which takes an object and converts its contents to an integer. This method is somewhat cumbersome to type, but it's hard to miss what's going on when you read your code. You can alternately use a type cast to let the C# compiler know that you intend to convert the contents of the object into an integer. To perform a type cast, place the data type you expect to get in front of the object from which the data will come. The type must be in parentheses, as the following C# examples show:

 nMyInteger = (int) objSomeObject; strMyString = (string) objOtherObject; objThisObject = (ThisObjectType) = objThatObject; 

The following two C# code snippets give the same final results. One uses Convert.ToInt32(), and the other uses a type cast:

 Example 1 Session["Test"] = 14; int nTest = Convert.ToInt( Session["Test"] ); Example 2 Session["Test"] = 14; int nTest = (int) Session["Test"]; Example 3 int nTest = Session["Test"] as int; Example 4 int nTest = int.Parse( Session["Test"].ToString() ); 

The last question in all of this is "Why, when VB performs explicit type conversions, did the VB example code in Listing 4.2 use Convert. ToInt32()?" Good question. The answer is that I like to be consistent across all languages. I write as much code in VB as in C#. For that reason, the more unified my code can be, the easier it is for me to go back and forth. So you'll see Convert.ToInt32() (and other similar conversion methods) throughout the code in this book.

Dynamic SQL in a String

The alternative to parameter binding is to create a string that contains a SQL query. Let's consider, for instance, the query used in Listing 4.1, as shown here:

 SELECT * FROM Orders WHERE CustomerID=567 

The customer ID can be any value, depending on who is logged on. We can create a string to reflect this variability. Suppose that the customer ID is contained in a session variable named CID. The following code shows how to create a string for the query:

C#

 string strSql =   string.Format( "SELECT * FROM Orders WHERE CustomerID={0}", Session["CID"] ); 

VB

[View full width]
 Dim strSql As string = string.Format( "SELECT * FROM Orders WHERE CustomerID={0}",   Session("CID") ) 

The string.Format() method uses a string as its first argument, and then a variable number of arguments. The first argument that's a string can contain references to the remaining arguments. For instance, {0} refers to the second argument (that comes after the first string argument). The {0} in the string is replaced by the contents of the second argument. And if a second replacement is to made in the string, it'll be indicated with {1}.

The situation gets a little more difficult when a query string has text data because this must be enclosed between two '' characters, as in the following SQL:

 SELECT * FROM SomeTable WHERE Name='Rick' 

For SQL that contains text data between single quotes, you must be careful how you structure your SQL string. Suppose that the name we want to use in the query is in a string variable named strName. We can concatenate this variable with other string data to form the SQL string, but we must make sure that the final string encloses the contents of the strName variable with single quotes. The following example shows how to form a query using the contents of a string variable:

C#

 strName = "George Washington"; // Other code might be here... string strSql =   string.Format( "SELECT * FROM Orders WHERE Name='{0}'", strName ); 

VB

 strName = "George Washington" ' Other code might be here... Dim strSql As string = _   String.Format( "SELECT * FROM Orders WHERE Name='{0}'", strName ) 

Parameter Binding vs. Strings

After being presented with two methods of creating ad hoc queries, you're probably wondering which method you should use. It depends. Here are some things to keep in mind to help you decide:

• Parameters require at least two lines of code, and usually three. This requirement might make code sections long if there are a lot of parameters.

• Parameters can make code more readable.

• If there's only one record, parameters can eliminate the need for a data reader. (See Chapter 3 for more details.)

• Queries in strings usually contain less code than the code required for parameter binding.

• Queries in strings can be difficult to read and debug.

• Parameters eliminate a security issue when users type in a name such as ' go Drop Table UserInfo.

I almost always prefer a SqlDataReader for queries because I find it easier to create and use than using parameters. My students, on the other hand, are usually better off with parameters because they sometimes get confused with single quotes and commas. If you're very comfortable with SQL, you might favor string queries.

If I want to perform a query that retrieves one record with one or two fields, I use parameters. The code's cleaner, and the application doesn't suffer the overhead of a data reader (which makes the program slower).

One last note about parameters vs. strings: If you really want to get performance gains and eliminate the security issues, stored procedures are the way to go. They give you the benefit of having the execution plan pre-built.