Handling Parameters

for RuBoard

As you've seen many times throughout this book, command objects use parameters to provide arguments to the command being executed against the data store. You can either create parameters for the particular provider directly by using the New operator or by using the CreateParameter method of the command object. In the case of the former, the constructor is overloaded and can be used to populate almost all the properties shown in Table 8.7. In the case of the latter, the CreateParameter method simply returns a new instance of the parameter and doesn't allow you to provide any of the default values for the property. In both cases, the parameter must be explicitly added to the parameters collection (exposed through a class that implements IDataParameterCollection interface) of the command object using its Add method.

Parameters can be used to both send data into a command and to return data to the client. We'll explore both aspects in the following sections.

Handling Input Parameters

Perhaps the most common use of parameters is to pass values into a stored procedure executed through a command. Listing 10.10 shows a typical example where the SaveTitles method accepts a DataSet and uses a data adapter to synchronize insertions and updates with the ComputeBooks database.

Listing 10.10 Using parameters. This method creates and populates the parameters collection of a `SqlCommand` object for use in a data adapter.

 Public Function SaveTitles(ByVal dsTitles As DataSet) As DataSet   If dsTitles Is Nothing OrElse dsTitles.HasChanges = False Then     Return Nothing   End If   Dim cmSave As New SqlCommand("usp_SaveTitle", _sqlCon)   Dim da As New SqlDataAdapter()   da.UpdateCommand = cmSave   da.UpdateCommand.CommandType = CommandType.StoredProcedure   da.InsertCommand = da.UpdateCommand   With cmSave     .Parameters.Add(New SqlParameter("@isbn", SqlDbType.NVarChar, 10, "isbn"))     .Parameters.Add(New SqlParameter( _        "@description", SqlDbType.NVarChar, 2048, "Description"))     .Parameters.Add(New SqlParameter( _        "@title", SqlDbType.NVarChar, 100, "Title"))     .Parameters.Add(New SqlParameter( _        "@author", SqlDbType.NVarChar, 250, "Author"))     .Parameters.Add(New SqlParameter("@price", SqlDbType.Money, 4, "Price"))     .Parameters.Add(New SqlParameter( _        "@pubDate", SqlDbType.DateTime, 4, "PubDate"))     .Parameters.Add(New SqlParameter( _        "@publisher", SqlDbType.NChar, 5, "Publisher"))     .Parameters.Add(New SqlParameter( _        "@catId", SqlDbType.UniqueIdentifier, 8, "CatId"))   End With   Try     da.Update(dsTitles)   Catch e As SqlException     ' Check for errors     If dsTitles.HasErrors Then       Return dsTitles.GetChanges(DataRowState.Modified)     Else       _throwcompubookexception("SaveTitles error", e)       Return Nothing     End If   Finally     If Not _sqlCon Is Nothing Then _sqlCon.Close()   End Try End Function

What you should notice about the SaveTitles method is that parameters are created using the New operator and added to the collection in a single statement. Because these parameters are of type SqlParameter , the second argument to the constructor uses the appropriate SqlDbType , which maps to the DbType enumerated type as we discussed on Day 7. If you use one of the constructors that doesn't specify the type, DbType.String will be the default. The Direction property of each parameter isn't set because in this case all are input parameters ( ParameterDirection.Input ), which is the default. In this case, the Value property of each parameter will be set by the data adapter when the command is executed for a DataRow that has been inserted or updated.

One of the other interesting things to note about using parameters with the SqlClient provider is that when the provider creates the Transact-SQL statement to execute against the server, it uses named rather than positional arguments, the opposite of what was done in early versions of ADO 2.x. In others words, the Transact-SQL that would be generated in the case of the usp_DeleteTitle stored procedure shown earlier would be equivalent to

 exec usp_DeleteTitle @isbn='006720034X'

If the procedure accepted additional arguments, they would be appended in a comma-delimited list. Using named arguments means that you can create stored procedures in SQL Server whose parameters are defaulted to NULL , as shown in Listing 10.11.

Listing 10.11 Optional parameters. Because `SqlCommand` supports positional arguments, your stored procedures can accept `NULL` values so that you don't have to create the `SqlParameter` objects.

 CREATE PROCEDURE usp_GetTitles         @ISBN      [nvarchar](10) = NULL,         @Author    [nvarchar](250) = NULL,         @Publisher [nchar](5) = NULL AS declare @where nvarchar(250) declare @author_w nvarchar(100) declare @Publisher_w nvarchar(100) declare @sql nvarchar(500) set @author_w = '' set @Publisher_w = '' set @where = 'WHERE' if @isbn is not null set @where = @where +  ' isbn = ''' + @isbn + '''' + ' and ' if @author is not null set @author_w = ' author like ''%'  + @author + '%''' + ' and ' if @publisher is not null set @publisher_w = ' Publisher = '''  + @publisher +  '''' + ' and ' set @sql = 'SELECT * FROM Titles ' + @where + @author_w + @publisher_w set @sql = substring(@sql,1, LEN(@sql)-4) exec sp_executesql @sql GO

In this way, your code can be made more efficient because the SqlCommand object needn't contain any parameters if you don't plan on passing a value into these arguments. In this case, the usp_GetTitles stored procedure executes queries with different WHERE clauses, depending on which arguments are passed in.

Tip

Whether the OleDbCommand object uses positional or named arguments is dependant on the OLE DB provider. For example, when using the SQLOLEDB provider with OleDbCommand , positional arguments are used rather than named arguments. This implies that you must add all the parameters to the parameters collection.

Input parameters can also be used with dynamic SQL by simply inserting the parameter into the SQL statement and then adding it to the parameters collection, as shown in the following code snippet:

 Dim com As New SqlCommand("SELECT * FROM Titles WHERE ISBN = @isbn", con) com.CommandType = CommandType.Text com.Parameters.Add(New SqlParameter("@isbn", "06720001X"))

As with positional and named arguments, the syntax you can use to specify the parameters and how they can be named is dependent on the OLE DB provider.

Handling Output Parameters and Return Values

The Direction property of parameter objects can also be set to InputOutput , Output , or ReturnValue . Each of these three options enables you to read information returned from the data store. As implied by the name , InputOutput enables you to both pass a value into the command and retrieve a (possibly) new value assigned by the command. Output can be used to return new parameters. ReturnValue can, for example, catch the value returned from a stored procedure.

To illustrate the use of output parameters, consider the stored procedure shown here:

 CREATE PROCEDURE usp_RevByBook @isbn nchar(10), @revenue money OUTPUT, @units integer OUTPUT AS SELECT @units = SUM(Quantity), @revenue = SUM(Quantity * UnitPrice) FROM OrderDetails WHERE ISBN = @isbn GO

In this case, the procedure calculates the number of units of a particular ISBN that have sold and how much revenue has resulted. Rather than return the results in a result set, you can write code that retrieves the output parameters. For simple results like this, using output parameters is more efficient because a result set needn't be created on the server and the code on the client to retrieve the values is simpler, as shown in Listing 10.12.

Listing 10.12 Using output parameters. This code calls a stored procedure that returns data through output parameters.

 Dim com As New SqlCommand("usp_RevByBook", con) With com   .CommandType = CommandType.StoredProcedure   .Parameters.Add(New SqlParameter("@isbn", "06720222X"))   .Parameters.Add(New SqlParameter("@revenue", SqlDbType.Money))   .Parameters("@revenue").Direction = ParameterDirection.Output   .Parameters.Add(New SqlParameter("@units", SqlDbType.Int))   .Parameters("@units").Direction = ParameterDirection.Output End With con.Open() com.ExecuteNonQuery() rev = CType(com.Parameters("@revenue").Value, Decimal) units = CType(com.Parameters("@units").Value, Integer) con.Close()

Note that after the parameters have been created, their Direction property is set to Output . After the command has been executed using ExecuteNonQuery , the values can then be read using the Value property.

Return values are typically used with stored procedures and are ideal for returning a value that indicates the success or failure of the stored procedure. For example, it's very common to use the return value from a SQL Server stored procedure to return the new value inserted into the identity column for a table. You'll learn more about identity columns on Day 13, "Working with SQL Server."

Note

In SQL Server, if no value is specified for the return value using a RETURN statement, 0 will be returned. Also, SQL Server always returns an integer value from a stored procedure.

As with output parameters, the only requirement to using return values is that you add the parameters to the collection and set its Direction property to ReturnValue .