Coding Parameter Queries

Okay, so do you want to have many of these ad hoc query issues magically disappear? If you're considering use of parameter queries, you've come to the right place. The ADO.NET parameter query is designed to help address several complex issues you're already aware of and several issues you might not even realize existuntil they bite you in the butt just as you sit down for dinner some evening. T-SQL and ADO.NET are tuned to help developers build parameter-based queries, and Visual Studio can help. No, this does not mean you have to graduate to stored procedures right away. I sometimes prototype applications with parameter-based ad hoc queries even though I know I'm going to convert to stored procedures some time in the future. Let's step through the process of creating a parameter-based ad hoc query. From there, we can easily step into calling stored procedures.

A parameter-based query is not that mysteriousit simply passes the job of inserting the properly formatted and framed string into the T-SQL just before it's sent to the server. In this case, ADO.NET assumes the responsibility of framing string and date query parameters in single quotes, if necessary, formatting any date values, and dealing with Unicode and internationalization/localization issuesjust to name a few.

While ADO.NET's Parameter object handlers know how to deal with the O'Malley issue and can help eliminate SQL injection attacks, they can't deal with all of the issues I described earlier. For example, ADO.NET can't automatically insert the "%" in the correct place in your LIKE expressionyou'll still have to do that.

As with any query you build, you're going to want to test it (well, I hope you will) before you cast it in bronze. While Visual Studio has a number of useful tools, it supports only a subset of the tools you'll find in SQL Management Studio. There, you'll find a Visual Studiolike interface (well, sorta like Visual Studio), a full-featured object browser that exposes all of the objects, rights, and utilities needed to manage your database, along with a query editor/analyzer to help debug the T-SQL. The problem with this editor/T-SQL debugger is that it sucks (that's a technical term). Once you learn how easy it is to write and debug code in Visual Studio, it's tough to go back to a stone-age tool like Query Analyzer.

Once you've coded and tested the query, you're ready to commit it to your application by either hard-coding the T-SQL into the SqlCommand.CommandText or taking the next big step and creating a stored procedure to manage the query. I don't think we're ready to take that leap just yet.

Inserting Ad Hoc Queries

Your next little challenge comes when you find that you must incorporate hand-written T-SQL into your source code. Microsoft has still not provided a tool to do this easily. Their own attempts to incorporate generated SQL into their behind-the-scenes classes are pretty sad. Sure, it works, but the code is far from readable, which makes it hard to understand and maintain. When you first paste your working SQL into the Visual Studio code editor, it looks something like Figure 10.9. Notice that Visual Studio has already started to syntax check the inserted text.

Figure 10.9. Pasting T-SQL into your Visual Studio code editor.

At this point, you must decide if you want to use the "&" operator to connect these loose strings into a single string, use the StringBuilder class, or simply use the String.Format method. Unless you're concatenating a few small strings, the StringBuilder performs better. Figure 10.10 shows how to use the StringBuilder instance to build up your T-SQL query string. Note that it's not necessary (or advisable) to break up the T-SQL string into many parts like this. While I have to do so to fit the code on the printed page, you should not worry about human readability that much.

Figure 10.10. Using the StringBuilder to construct an ad hoc query.

You'll want to add an Imports statement to your Visual Basic application to add the System.Text namespace unless you're being paid by the keystroke. As you use the StringBuilder Append method, be sure to add whitespace between the T-SQL operators so the string will execute. No, you do not need to add carriage-return and line-feed characters to the stringthis makes a difference only if you're coding a stored procedure. While this example is pretty simple, you get the idea. Sure, it could have been done with "&", but many queries are far longer than this, so getting accustomed to using the StringBuilder is an important skill to learn.

Another approach that can make coding easier (and less prone to concatenation errors) is to use the String.Format method to build up the T-SQL query. This approach works fine for simple queries or anytime you need to insert a value into a string as shown in Figure 10.11. Since Peter twisted my arm about this a couple of years ago, I've grown to like the ability of String.Format to deal with a litany of formatting and other issues.

Figure 10.11. Using String.Format to construct simple ad hoc queries.

Once again, regardless of the technique you use, it's dangerous to concatenate user-provided arguments into T-SQL strings, as it exposes your application to SQL injection attacks. Not only that, but if you insist on this approach, you'll have to be extremely careful about properly formatting the T-SQL, including dealing with framing dates and strings as well as the O'Malley issue. Nowadays, I simply can't trust users to provide "clean" parameters. Remember, all data is guilty until proven innocent.

Understanding the SqlParameterCollection Class

Unless you're executing very simple queries or action commands that don't accept user-supplied values, you should code a Parameters collection to handle the values passed to query input parameters, as well as capture the values passed from the OUTPUT and RETURN value parameters. As I've said before, the SqlCommand is capable of dealing with a litany of issuessome of the most complex involve handling parameters. The Parameters collection holds some or all of the Parameter objects that map to the parameter placeholders in your T-SQL. When you execute the SqlCommand, the Parameters collection is scanned (by name) to find Parameter objects that match the named placeholders. If a Parameter Value is set to Nothing (in Visual Basic .NET) or null (in C#), the Parameter is skipped and not sent to SQL Server. This assumes that the missing parameter(s) have defaults defined in the stored procedure. Of course, this won't work for ad hoc queriesyou'll have to provide Parameters with Values set for all query parameters.

After the query is executed and all rowsets (if any) have been fetched, any OUTPUT and RETURN value Parameter.Value properties are populated. It won't do any good to access these Parameter object Value properties until the last rowset of the last resultset has been fetchedor you use the Cancel method, as described elsewhere in this chapter.

Exploring the SqlParameterCollection Methods

The SqlParameterCollection class has all of the typical Collection methods and functions that can be used to manage the collection itself. Those that are most interesting (and most useful) are listed in Table 10.4. Note that the SqlParameter Collection is based on the System.Collections.Arraylist and supports all of the inherited methods and functions used to manage the array.

Table 10.4. Selected SqlParameterCollection Methods
Methods	Accepts	Description
AddRange	Array or Parameter array	(New for 2.0) Permits you to create a System.Array or Parameters array and add a block of Parameters in one operation
Clear	(no arguments)	Removes all SqlParameter objects from the collection

Using the AddRange Method

I don't really see a lot of utility here, but it comes included with all other ArrayList Collection classes in the 2.0 Framework. Why create and populate a separate array of Parameter objects when you can just as easily create a Parameters collection and populate it directly? If you do find a need to use AddRange, Figure 10.12 shows how to set up an array of Parameter objects, populate it, and use the ParametersCollection AddRange method to add the pre-configured Parameter objects to the collection. Note that AddRange requires a fully populated array of Parameter objects.

Figure 10.12. Using the AddRange method to add an array of Parameter objects to the Parameters collection.

Using the Clear Method

If you want to reuse the SqlCommand object, you might consider using the Clear method to zero out the Parameters collection. Frankly, I think it's better to simply create a new SqlCommand object and repopulate the Parameters collection.

Understanding the SqlParameter Class

The SqlParameter class is (as I've said before) used to describe the named query parameters that match the parameter placeholders used in the CommandText query. Each SqlParameter you create is appended to the SqlParameterCollection. Let's look at some basic facts about the SqlParameter class:

Each Parameter object must be assigned a unique name that matches the placeholder name in the T-SQL. No, you can't use "?" as an SQL parameter placeholder. That technique is used with OleDb and Odbc providersnot SqlClient.
Named Parameter objects can be added to the Parameters collection in any order. Positional parameters are no longer supported.
When building a Parameters collection for an ad hoc query, all query parameters must have a corresponding Parameter object appended to the Parameters collection.
Before executing the SqlCommand containing an ad hoc query, each input parameter must have the Value property set.
When building a Parameters collection for a stored procedure, you must add a Parameter object for each parameter that does not have a default value set in the stored procedure. Yes, you can provide "override" Parameter objects that set a Value other than the stored procedure default setting.
If a named Parameter is missing from the collection or if the Value property is set to Nothing (Visual Basic .NET) or null (C#), the stored procedure default value setting is used.
When defining a Parameter object, you need to supply only the ParameterName and Value properties prior to execution. The default parameter datatype is nvarchar.
If you plan to use the Prepare method (for better performance), you'll have to provide a specific datatype for all Parameter objects and a specific (maximum) Size property setting for all variable-length datatypes (such as varchar, nvarchar, char, nchar, varbinary, etc.).
While you can define the Precision and Scale properties for a Parameter object, these values are basically ignored by ADO.NET.
When defining Parameter objects, the default Direction is Input. If you're calling a stored procedure that returns OUTPUT parameter(s), you'll need to create Parameter objects with the Direction property set to Output.
All stored procedures return an integer upon completionwhether the stored procedure sets the integer RETURN value or not. By default, this value is 0. To capture the RETURN value, you must create an (optional) Parameter object with the Direction set to ReturnValue.
If the Parameter can accept a null value (since all SQL Server Parameters can accept null, this should read "if you plan to pass a null to a Parameter Value), you must explicitly set the IsNullable property to true.
When using the Parameter object in conjunction with the SqlDataAdapter or TableAdapter, Update method, you can map the Value property to a specific column in the DataTable by setting the SourceColumn property. This is discussed in more detail in Chapter 6, "Building Data Sources, DataSets, and TableAdapters," and Chapter 12.

What Can Be Parameterized?

Ah, ADO.NET (and T-SQL) won't permit you to parameterize just anything. T-SQL insists that the column, table, and other object names be hard-coded along with most of the rest of the query. What can be parameterized are arguments passed to WHERE clause expressionsand then again, not all of these expressions can accept parameters. You can also parameterize the items in a VALUES list, as when using the INSERT statement or in a SET statement as a placeholder for the column value. Generally, parameters can be used as a placeholder for a literal value. That is, when the T-SQL is executed, the placeholder is filled with the Parameter Value property. Typically, you'll see parameters in a WHERE or HAVING clause search expression. For example, you can provide a parameter for:

A TOP expression
A LIKE expression
The equality and comparison expressions
An INSERT or UPDATE statement VALUES list
An assignment statement, including SET and SELECT

As I'll show you later in this chapter, you can resort to using the T-SQL EXEC operator to pass any and all of the components of your SQL query to the server to be compiled and executed. The approach is not by the furthest stretch of the imagination recommended for those who don't fully understand its performance and security vulnerabilities.

Marking Parameters

In SQL Server stored procedures, you must mark parameters with named placeholders prefixed with the "@" characterno, you can't use "?" or ":" to mark parameters, as you did with ADO.NET classic. Ad hoc queries are no different in this respect. Figure 10.13 illustrates typical parameter placeholders in an ad hoc query. This T-SQL could just as easily be the contents of the As clause in a stored procedure.

Figure 10.13. Passing parameters into a SELECT query.

No, the IN expression cannot accept a parameter, due to SQL Server stored procedure compiler restrictions. One approach to get around this limitation is to pass the list of acceptable values (which is a comma-delimited list of numbers or quoted strings) to a function that returns a table-type variable that feeds a SELECT statement for the IN expression. Get it? Well, if not, Figure 10.14 shows an example of how this is done. Later in this chapter, I'll show another (less reputable) approachdynamic SQL; in Chapter 13, "Managing SQL Server CLR Executables," I'll show you how to do this using a CRL function. First, let's see how to use a table-valued function without a parameter.

Figure 10.14. Passing a delimited list to an IN expression by using a Table-valued function.

Figure 10.15 illustrates how the code replaces the literal string in the function call with a parameter. I also wrote a test script to make sure it works. The script, like your ADO.NET SqlCommand, sets the SqlParameter Value property before executing the query.

Figure 10.15. Replacing a literal string with a parameter.

The table-valued function shown in Figure 10.16 is a block of custom code that I wrote to convert a delimited string to a set of rows in a data table. This table is returned by the function with one row for each item in the delimited string. The function accepts a delimited string up to 4,000 characters long and a delimiter that's used to delineate the items in the string. Once you have a table, you can use a nested SELECT with the IN expression, as shown in Figure 10.15.

Figure 10.16. The StripToTable table-valued function.

Tip

Would it be more efficient to implement this T-SQL function with a CLR function? I'll discuss that possibility in Chapter 13.

Another use for the SqlCommand object's Clone function is to create instances of a SqlCommand that does not include SqlParameter objects in the Parameters collection that are supplied by a stored procedure's parameter default settings. In other words, when you call a stored procedure that has defaults set for one or more parameters, you can simply set up a clone of the SqlCommand that does not include these Parameter objects in the Parameters collectiona "sparse" parameter list.

Another approach would be to set up your SqlCommand object's Parameters collection with Parameter objects whose Value property is set to Nothing in Visual Basic (and "null" in C#). This tells ADO.NET to skip the Parameter, which forces the stored procedure to use the defined default.

Setting Default Parameter Values

Whenever you call a stored procedure, you must supply all input (or input/output) parameters specified in the stored procedure's signatureunless the stored procedure parameter has a default value assigned, as shown in Figure 10.17, where the stored procedure defines default values for the first and third parameters.

Figure 10.17. Setting default values for input stored procedure parameters.

So, to configure a Command object to call a stored procedure that includes default parameters, the Parameters collection should be configured to either

Build a Parameters collection that includes all stored procedure parameters and provide specific Parameter.Value settings for each parameter. At execution time, when you supply a specific Value property value, it overrides the default value defined in the stored procedure. Consider that if you set the Value property to "NULL" (DBNull.Value), NULL is passed as the Parameter value. I discuss other ways to set the Parameter Value to NULL later in this chapter.
Build a Parameters collection that includes all stored procedure parameters and provide specific Parameter.Value settings for parameters that should override any default settings, and set those that should take the stored procedure-defined default value to Nothing (Visual Basic .NET) or null (C#).
Build a Parameters collection that includes only those stored procedure parameters that do not have default values defined in the stored procedure (the required parameters) and any other parameters that you wish to provide (that override the default). Set the Value property of all of these Parameters.
Build one or more copies/clones of the SqlCommand that define hard-coded defaults (Value property settings) for selected stored procedure parameters and require just-in-time Value property values.

Let's walk through an example of these techniques. As shown in Figure 10.18, I set up two SqlCommand objects. The first is configured with a Parameters collection to accept a single Parameter. In this case, I assume that the application wants to use the stored proceduredefined default setting for the other two stored procedure parameters.

Figure 10.18. Setting up two SqlCommand configurations.

When it comes time to execute the SqlCommand objects I've created in Figure 10.18, the application chooses between one of two routinesone that simply sets the required parameter and another that forces the first and third parameters to use the stored proceduredefined default value. Let's see how this is implemented in Figure 10.19.

Figure 10.19. Setting Parameter.Value determines whether stored procedure default is used.

Building a Simple Parameters Collection

Once you get your T-SQL properly formatted, it's time to build the Parameters collection. In this case, I have two ad hoc query parameters to deal withone passed into the Sub (the delimited list of prices) and the maximum rows to return from the query, which is hard-coded as 10. But the code shown in Figure 10.20 has a few problems. Can you spot them?

Figure 10.20. Building a parameter-driven querywith some issues.

The first problem you'll encounter when building concatenated SQL queries involves whitespace. Remember that when you build T-SQL strings by concatenating or appending to the StringBuilder, you need to be vigilant of the whitespace between the operators. Note that the "FROM" clause is not prefixed with a space. This means the T-SQL submitted will be "...dsValueFROM StripToTable...", which generates a T-SQL syntax error.

The next problem is that there is no exception handlinganywhere. While it's possible to set up an exception handler on the routine that calls this function, you really need to remember to set it up somewhere or the end user has to deal with unhandled exception messages.

See anything else? There is a hint in Figure 10.20. See the squiggly line under the "dt" near the end of the routine? In Visual Studio, this green squiggly is a sign that something might be wrong. In this case, it's a warning that the object being referenced has not been initialized. Remember that when you create a variable cast as a datatype, that does not create an instance of the objectit simply creates a pointer to the place in memory where it's going to be stored. You still need to create a new instance of the object or expect the method to do it for you. That's what happens with the SqlDataReaderthere is no way to create a "new" DataReader streamonly the ExecuteReader method can do that.

What about the connection? Don't you think that the SqlConnection (cn) should be closed before the end of the Sub? Well, it's not instantiated in the Sub scope, so the SqlConnection is not closed by the GC when I leave the Suband you should never depend on that behavior anyway. The SqlDataReader (dr) is closed by ADO.NET because I used the Load method to complete population (as I'll discuss later). Briefly, any method or control that completes DataReader rowset population (like any complex bound control) closes the DataReader. But that does not close the associated SqlConnection. In this case, I changed the state of the SqlConnection object but did not change it back. What happens when this routine is called again? If I don't fix it, it will fail, as the SqlConnection is already openunless some other part of the application closes it. It's always a good idea to leave object state as it was when the routine started.

Let's look at the corrected code in Figure 10.21. The green squiggly has gone, and I added the CommandBehavior.CloseConnection option to the DataReader to tell ADO.NET to close the SqlConnection when the DataReader is closed. I also added SqlConnection.Close to the Finally block to ensure that the SqlConnection gets closedwhether or not there are exceptions. Sure, it's okay to close a closed SqlConnection.

Figure 10.21. The corrected subroutine to fetch rows based on input parameters.

Constructing the Parameters Collection

When I build the Parameters collection, I start from a clean slatean empty collection. To add Parameters, I can use any one of a variety of syntax variations. Since the code is using named parameters, they can be appended to the Parameters collection in any order. When I start talking about stored procedure parameter lists, there are a few more considerations to worry about, but now let's get the basics down. There are (at least) three ways to fill the Parameters collection with Parameter objects.

The Add function: Appends a Parameter object to the Parameters collection using one of four overloads (constructors). This method permits you to pass in a pre-built SqlParameter object or set the name, type, size, and source column of a newly constructed Parameter object.
The AddWithValue function: This function (new with the 2.0 Framework) appends a Parameter object to the Parameters collection by simply passing the Parameter name and value. The datatype defaults to nvarchar.
The AddRange method: This method (new with the 2.0 Framework) appends either a typed array of pre-built SqlParameter objects or a system.array (but it can't accept the Parameters collection of another SqlCommand).

The Add function is used by the wizards (that I discussed in Chapter 7) coupled with one of the seven Parameter class New constructors. These are a lot harder to use but might be necessary when you start building special-use action queries that need to fill in Parameter values drawn from current or changed versions of the data rows you're working with.

The list in Figure 10.22 shows a partial list of these syntax variations:

Figure 10.22. Syntax variations used to add Parameters to the Parameters collection.

Note that two lines of code have the dreaded green squiggly disease. In this case, the warning advises that this syntax is no longer in styleit's obsolete, passé, not cool. Microsoft wants you to use the AddWithValue function so the compiler does not have to guess that you're really creating a Parameter with a pre-set value. Actually, except in ASP applications where SqlCommand objects are often used and discarded on each page load, it's unusual to pre-set a Parameter Value property. Generally, this is done "just-in-time" before the SqlCommand is executed. I'll stick with the old tried-and-true methods.

Remember that Parameter objects can be added to the Parameters collection in any orderthey do need unique names and these names must match the parameter placeholders in the CommandText one for one. You aren't permitted to create too many or too few Parameter objects when working with ad hoc queries.

Yes, Parameters can be "gazintas" or "gazoutas"passed to or from the stored procedure. I'll discuss how to set the Direction property later in this section.

Managing ParametersBest Practices

As I've discussed, you should keep in mind that data access is not a constitutional democracy. All input is considered evil until proven innocentespecially when it comes from a user. This means your code is going to have to figure out how, when, and where to make sure the data you pass to SQL Server is pure as the driven snowand it's probably (but not always) best to make those checks before you send the data to the server. While Parameter objects seem to be strongly typed objects, ADO.NET does not care what type of value is assigned to the Value property until the SqlCommand is executed. Basically, the checks your code must perform can be done in several basic ways:

Does the value supplied conform to the specified Parameter datatype? That is, are you setting an integer value into an integer type or a string into a string-typed parameter?
Is the supplied value's size larger than the capacity of the Parameter datatype? That is, are you passing a 32-bit integer into a 16-bit typed Parameter? While it might be okay to pass smaller-sized values, you won't be able to pass values whose range exceeds the capacity of the target data structure.
What about the string values you're assigning to the Parameter Value property? Are they longer than the defined length of the Parameter? Strings are handled a bit differently, as I'll illustrate, but the code behind the scenes^[3] won't let you move too much string data into a variable without complaining. Note that you need to set the Size property only for variable-length string Parameter objects.
^[3] Is this code behind the scenes ADO.NET or the .NET Framework, or is it simply the compiler? Does it really make any difference? Not really, but it does make a difference when the validating code does the checks.
Is the data formatted to conform to the requirements of the target datatype? For example, when you're working with dates, the strings you supply must conform to one of the recognized date formats. You also need to consider international date-formatting issues.
Is the parameter constructed to include special search or wildcard characters? I often have to do last-second concatenation to parameters that pass values into LIKE expressionsare these correct and in the correct place?

Sure, ad hoc queries are pretty tolerant when it comes to the scaling and size of the data you pass as parameters, so you don't have to worry too much about sizes or numeric data ranges. Just make sure your dates and LIKE expressions are correct. However, remember that the data columns in the database tables are all specifically sized one way or anothereven the variable-length columns and TEXT/IMAGE types have upper limits (albeit very large).

Discussion about when, where, and how this validation should be done has been raging for as long as intelligent workstations have existed. Should these tests be done on the user's system (as you can easily do with a client/server application) or in code running in a browser? Should a middle-tier application or the ASP page perform the checks or simply depend on the server, where criteria checks, rules, and complex logic in stored procedures can weed out the trash? All of these questions are for you and your application architect to answer.

Yes, some of these checks are "business rules" that should be isolated and performed on the server so that when (not just if) they change, deployed applications need not be redeployed, but some of these checks simply must be done before you can pass the data to another tier for processing. In this case, you'll need to mask, format, and range-check the input data.

I like to use "pick lists" for as many values as possible. This eliminates the possibility that users will incorrectly enter the values. Pick lists can be loaded very quickly from tables designed for this purpose. These are often the "foreign" key tables such as "ValidStateCodes" or "CurrentDiscounts" that are joined to other tables to ensure data integrity. Lookup tables are usually fairly short, simple lists of codes and descriptions that make it easy for users to choose correct entriesinstead of trying to type them correctly.

In any case, you should eliminate situations where the user is encouraged to type in SQL. As I've said, this is especially dangerous and requires considerable end-user training to accomplish. If you take this approach, it means you'll have to parse every single string provided as "SQL" to ensure that the user does not damage, see, or change anything they're not allowed to view or access. You shouldn't depend on users to enter wildcard ("%") or pattern-matching characters when performing "fuzzy" or ambiguous searches. Of course, when this is necessary, your code should assume responsibility to fill in wildcard characters at the appropriate places.

Regardless of the query approachbut especially in fuzzy searchesbe sure to limit the number of rows returned to keep from overwhelming the system with work and the user with information they can't possibly use. This can be done with the TOP operator or by setting ROWCOUNT in your query, as shown in Figure 10.23.

Figure 10.23. Setting the maximum rows to be returned by using ROWCOUNT or TOP.

Setting the Parameter Value Property

When the time is right and the data is right, you're ready to set the SqlParameter object's Value property.

So what happens when you pass invalid data to a Parameter.Value property? Well, it depends. What do you suppose would happen if I executed the code shown in Figure 10.24? Clearly, there are a lot of errors here, but when is the SqlException handler calledor is it?

Figure 10.24. Discovering when Parameter data failures occurthe hard way.

It turns out that when you assign junk to the Parameter Value property, nothing happensnothing at all. Even though each and every one of the errors shown in Figure 10.24 can cause the ExecuteReader to abort and throw an Exception or SqlExceptionwhich is sometimes (just sometimes) useful in figuring out what went wrong. So, adding a TRy/Catch around the Value assignment statements will do you no good at all. It seems that ADO.NET blindly accepts anything thrown at it when assigning the Value property. Even when the types are defined correctly but the data is clearly not in type specs, the Value assignment goes through.

None of these Parameter values is questioned at all until the ExecuteReader function is calledyou attempt to execute the TSQL. By then, you have no way of knowing which Parameter is at fault or even if it's an invalid Parameter Value assignment at all. The single exception is also returned by either the SqlException or the general Exception handler, and there is no back reference to the code that actually made the assignmentbut how would it know?

Using the ResetDbType, ResetSQLType Methods

As discussed earlier, when you set the Parameter Value in code, ADO.NET infers the DbType and SqlDbType from the value supplied. In cases where you want to get ADO.NET to reinterpret the Value property, you can call either of these methodsthey both reset the DbType and SqlType based on the Value property.