How to: Dynamically Invoke a Stored Procedure

Article
09/12/2011

Applies to: Functional Programming

Authors: Tomas Petricek and Jon Skeet

Referenced Image

Get this book in Print, PDF, ePub and Kindle at manning.com. Use code “MSDN37b” to save 37%.

Summary: This article shows helper types that can be used to call stored procedures easily using the F# dynamic operator (?). The article explains the implementation, but the helper can also be used as-is.

This topic contains the following sections.

Introducing the Dynamic Operator
Building Commands
Executing the Commands
Summary
Additional Resources
See Also

This article is associated with Real World Functional Programming: With Examples in F# and C# by Tomas Petricek with Jon Skeet from Manning Publications (ISBN 9781933988924, copyright Manning Publications 2009, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Introducing the Dynamic Operator

The F# language provides a simple dynamic invoke operator that can be used in scenarios where the existence of a member cannot be checked at compile time. When calling a stored procedure, the compiler doesn't know if the stored procedure exists. The dynamic operator makes it possible to write an expression similar to a member invocation that calls the procedure (and may fail if the procedure doesn't exist).

This article shows how to use the dynamic operator for calling stored procedures. It is encapsulated in several helper types. Although the tutorial explains the implementation, it is not necessary to understand its every detail. You can just copy the code (the complete source is available at the end of the article) and start using it. The usage is demonstrated in the following tutorial:

Tutorial: Creating an Online Poll

How the Dynamic Operator Works

The dynamic operator in F# is extremely simple but surprisingly powerful. Assume that the MyType type implements the dynamic operator as a static method and that myVal is a value of MyType. An expression that uses the dynamic operator such as myVal?Foo is translated to a call that passes the name Foo to the dynamic operator (that is, it is translated to MyType.(?) myVal "Foo").. This way, the implementation can perform a dynamic lookup using the string.

The operator doesn't have a direct support for method calls. However, it can return a function that takes an arbitrary argument. If the return type of (?) is a function of type 'T -> unit, then it is possible to write, for example: myVal?Foo(1, "test"). This is translated to a call that invokes the dynamic operator (which returns a function) and then calls the returned function with a tuple as an argument.

The helper for calling stored procedures uses exactly this approach. It returns a function that expects a tuple. When called, the function uses F# reflection to extract a list of arguments from the tuple (e.g., the number 1 and the string "test" in the previous example) and passes these arguments to the SQL-stored procedure. The following section shows a utility function that takes a tuple as input and constructs a SQL command object.

Building Commands

The article implements a type called DynamicDatabase. The type provides exposes two different ways of calling database using the (?) operator. One makes it possible to call stored procedures that return collection of results and the other for calling procedures that perform some action without returning a result.

Both implementations share the same core part that takes the name of the stored procedure and a tuple containing the arguments and builds a SqlCommand object. The following listing shows this core functionality implemented as the createCommand function in the Internal module:

namespace FSharpWeb.Core

open System.Data 
open System.Data.SqlClient
open Microsoft.FSharp.Reflection

module Internal =
    let createCommand name (args:'T) connection = 
        let cmd = new SqlCommand(name, connection)
        cmd.CommandType <- CommandType.StoredProcedure
        SqlCommandBuilder.DeriveParameters(cmd)
        let parameters = 
          [| for (p:SqlParameter) in cmd.Parameters do
                 if p.Direction = ParameterDirection.Input then
                     yield p |]
        let arguments = 
            if typeof<'T> = typeof<unit> then [| |]
            elif FSharpType.IsTuple(typeof<'T>) then
                FSharpValue.GetTupleFields(args)
            else [| args |]
        if arguments.Length <> parameters.Length then 
            failwith "Incorrect number of arguments!"
        for (par, arg) in Seq.zip parameters arguments do 
            par.Value <- arg
        cmd

The implementation first opens necessary .NET namespaces to access the ADO.NET objects (that requires references to System.Data.dll and System.Transactions.dll) and the F# reflection library. The function takes three parameters: the first is the name of the stored procedure (as a string), the second represents a tuple with arguments for the call, and the last one is an opened SqlConnection to the database.

The parameter representing arguments is generic because the function can be called with various different arguments. When called without parameters, the value of args will be () of unit type. When called with a number and a string, the value can be, for example, (42, "test") of type int * string. As already mentioned, the type of the value is inspected dynamically using F# reflection. The body of the function performs the following tasks:

It creates a new SqlCommand representing a stored procedure call and uses the DeriveParameters method to retrieve information about the parameters of the stored procedure from the database server. This populates the information in the collection cmd.Parameters.
Next, it filters the collection to get all of the stored procedure's input parameters. Other types of parameters, such as output parameters, are not supported.
Then, the function builds an array containing the actual arguments provided by the caller. When the type of args is unit, it creates an empty array. When the type is a tuple, it gets elements of the tuple using F# reflection. In any other case, the args value is treated as a single argument.
Next, the function checks whether the expected number of parameters matches the supplied number of arguments. If that's the case, it iterates over the two collections and assigns arguments to the Value property of the SqlParameter objects.

The function does all of the hard work. It analyzes the parameters of the SQL-stored procedure and the supplied arguments. The result is an SqlCommand object that is ready to be executed. The following section shows two ways of using it.

Executing the Commands

When using ADO.NET directly, a command can be executed using one of three methods. The ExecuteReader method returns an object for iterating over a result set returned by the stored procedure. ExecuteNonQuery just runs the command without returning a result, and ExecuteScalar can be used when the stored procedure returns a single value. The following two sections implement helpers that wrap the first two methods.

Returning the Row Collection

The ExecuteReader method returns an instance of SqlDataReader, which can be used to access the columns of the returned result set. This can be done using indexing and casting the result to an appropriate type. For example, to read a Name column that contains a string, you could write: reader.["Name"] :?> string.

We can make this syntax nicer using the dynamic operator once again. The object that provides the dynamic database access returns a seq<RowReader> collection. The RowReader value stored in the collection makes it possible to access individual columns using a syntax like reader?Votes. When used in a context where a string is expected, the (?) operator automatically casts the result to the right type. The following listing implements RowReader:

type RowReader(reader:SqlDataReader) = 
    member private x.Reader = reader
    static member (?) (x:RowReader, name:string) : 'R = 
        x.Reader.[name] :?> 'R

The (?) operator is implemented as a static member of the type taking two parameters. The first parameter is the RowReader object and the second one is the name of the accessed member. RowReader takes an underlying SqlDataReader and exposes it as a private field, so that it can be used from the static operator. The implementation of the operator is very simple. It performs the lookup using the member name and then casts the value to the required result type.

The implementation of the (?) operator for calling the stored procedures that return result sets is wrapped in the DatabaseQuery type. The type follows a similar pattern and uses a private member to pass the connection string to the dynamic operator. The implementation of the operator uses the createCommand function from the previous section:

type DatabaseQuery(connectionString:string) = 
    member private x.ConnectionString = connectionString
    static member (?) (x:DatabaseQuery, name) = fun (args:'T) -> seq {
        use cn = new SqlConnection(x.ConnectionString)
        cn.Open()
        let cmd = Internal.createCommand name args cn
        let reader = cmd.ExecuteReader()
        let row = RowReader(reader)
        while reader.Read() do yield row }

The operator returns a function that takes any value as an argument (the type is a generic type parameter 'T) and uses sequence comprehension to return a collection of results as seq<RowReader>. As already discussed, the args argument can be unit (for parameterless procedures), a tuple (for procedures with more than one parameter), or any other type (for procedures with exactly one parameter).

The body of the operator is quite simple. It creates a SqlConnection to the database, opens it, and calls the createCommand function (which requires an opened connection). Then, it iterates over the returned rows and repeatedly returns the reader object to the caller. As a result, the user of the object cannot store the reader object for subsequent use (because it is mutable), but this is the nature of database access and it shouldn't cause issues when the object is used in the usual way.

The method cannot be easily used for the calling of the stored procedures that return a result. The difficulty is that the returned sequence must be iterated over in order to dispose of the opened connection. The following section adds another variant of DatabaseQuery for this purpose.

Running Commands without Results

The following type is almost the same as DatabaseQuery. There are two differences. First, the body of the dynamic operator isn't written as a sequence expression, and second, the command is executed using ExecuteNonQuery instead of ExecuteReader:

type DatabaseNonQuery(connectionString:string) = 
    member x.ConnectionString = connectionString
    static member (?) (x:DatabaseNonQuery, name) = fun (args:'T) -> 
        use cn = new SqlConnection(x.ConnectionString)
        cn.Open()
        let cmd = Internal.createCommand name args cn
        cmd.ExecuteNonQuery() |> ignore

The similarity between the two snippets demonstrates a nice aspect of F# sequence expressions. Both of the snippets use the use keyword that ensures that a connection is properly closed. The syntax is the same but the behavior is different to fit the context. Here, the connection is closed when the operator returns. In the previous case, the connection is closed after the caller iterates over the output.

Finally, the last snippet provides easier access to the two helper objects. It takes a connection string as an argument and exposes both a DatabaseQuery and a DatabaseNonQuery:

type DynamicDatabase(connectionString:string) =
    member x.Query = DatabaseQuery(connectionString)
    member x.NonQuery = DatabaseNonQuery(connectionString)

After creating a value db of this type, you can write db.Query?Foo() to call a stored procedure that returns a result and db.NonQuery?Bar() to call a procedure without a result. This is demonstrated in Tutorial: Creating an Online Poll and is referenced in additional resources below.

Summary

This article presented a helper object that makes it possible to call stored procedures using the dynamic operator (?). The helper builds on standard ADO.NET libraries but makes the syntax very simple and straightforward. The dynamic operator is used both for calling a stored procedure as well as for reading columns from the returned result set.

The implementation of the operator uses F# reflection to dynamically process arguments when calling a procedure. This makes it possible to use natural syntax for procedure calls using a unit value (), a single argument (42), or a tuple (42, "test"). The details of the implementation are not very important, but they demonstrate some interesting techniques that can be used in F#. The helper can be used as-is in any .NET application and is not limited to the web.

Additional Resources

This article describes the implementation of a helper for calling stored procedures. The following tutorial uses the helper to implement an online poll application:

Tutorial: Creating an Online Poll

The following articles provide more details about developing web applications using ASP.NET MVC 3 and F#. The covered topics include the structure of an MVC web application as well as using asynchronous workflows in web programming:

To download the code snippets shown in this article, go to https://code.msdn.microsoft.com/Chapter-5-Bulding-Data-ec639934