Using WCF REST HTTP to call Stored Procedures on SQL Server

• Download source code - 670.2 KB

Introduction

Doing contract .NET WCF coding over the last few years, I noticed an issue that was preventing clients from using WCF/Rest as a means of exposing their middle tier API’s (a model I usually strongly recommend for enterprise apps). The obstacle that was preventing them from fully embracing the WCF Rest Web API model was that their development teams relied heavily on SQL Stored Procedures. Their development managers were getting the feeling that support for SQL Stored Procedures was waning and that before long they would not be able to use the new WCF models unless they were willing to stop using Stored Procedures altogether. This may or not be so, but it has sometimes been the case that to use some new tool or technology, some existing tools or technologies become obsolete. Many companies rely heavily on TSQL. They are wary of new samples and starter-kits highlighting new WCF technologies (Soap, Rest, and now Web API/MVC) that seem to exclude SQL development outside of basic data repository tasks (very weak support for stored procedures, UDFs, triggers, views, and so on).

One client asked this specific question: Could they implement a framework whereby all their API methods were exposed via WCF Rest and still be able to create client code to run new Stored Procedures or execute SQL statements unknown at design time without having to re-code or re-compile their middle tier BLL/DAL layers? This was a big deal to them because they could write stored procedures and modify the client apps a lot more easily than updating, recompiling, and retesting the Dot Net middle tier code. One issue was expertise – some companies have strong SQL developers and web developers to support client app development, but have a hard time finding experienced Dot Net coders for the middle tier.

So can an unknown SQL Stored Procedure or command string be called via a REST WCF service, return the appropriate results in a way the client can consume them (even non-Dot Net clients, if necessary)? The answer is yes, but there are several caveats. First I did a version of the tool using SOAP/XML services, but looking ahead it seemed probable they would really need one to work with a fully RESTful HTTP Service.

The specific requirements were:

• Work on SQL Server 2005 or above, Visual Studio 2010 or above, Dot Net 3.5 or above.
• Call any Stored Procedure with any number of parameters (input and output).
• Allow execution of any SQL Query Statement.
• Return results in DataSet (for Dot Net Clients), XML or JSON.
• Run from Dot Net Http Clients, or use JavaScript/jQuery and consume results in client script.
• Return multiple resultsets, if necessary.
• Return values for Output Parameters, if any.
• Return Scalar Values, if any, and Rows Affected counts from ExecuteNonQuery commands.
• If the SQL code or WCF methods generates an error, return the message to caller.

While I was able meet the requirements listed above, there are some limitations to what this tool can do: No table parameters in or out, and I haven’t done a check of all different datatypes. Not sure if it could be modified to handle binary fields, for example. There are also serious safety considerations and problems with data persistence, which I explain further below.

Using the code

First, the caveats/disclaimers: This is not a best practice or an efficient way of processing SQL data. Stored Procedures or queries that can be identified at design time should be included in whatever entity generator tools are being used. The function importing for EF is a bit awkward but usually works ok, and there’s a big advantage to having Intellisense pick up the objects being returned from SQL, although the service can import directly into known types on the client. Calling SQL directly can negate persistence management. No tracking or DTO framework will know what’s happened unless all the dbContect objects are refreshed from the database after any call is made, which is hardly practical.

If this code doesn’t violate the letter of Rest Service design it certainly violates the spirit of using only the URI and HTTP verb for service messagingion. We are passing an object that is itself a set of instructions, with method names and a variable length list of parameters for stored procedures. However, it does allow Http clients to consume any stored procedure or query from the db, which was the original intent, so it might help development managers feel more comfortable about using WCF/REST in their ESB applications.

The WcfRestSql service uses Http Post with a simple object wcf_rest_command serialized in XML or JSON as payload. This object contains fields for the name of the stored procedure to run, the connection string that will match a name in the service's config file, and a delimited list of parameter names and parameter values (if needed). There is a commandtype field to specify we are running a "text" or a "storedProcedure" command type, and a field named executetype to specify "scaler" or "nonquery" for SQL command executetype.

// wcf_rest_sqlcommand is the basic command object used to build http request object.
// values are serialized into json or xml and submitted as payload.
public class wcf_rest_sqlcommand 
{
     // for stored proc, name of stored proc, for command text of command
     public string command { get; set; }     
     // either "text" or "storedprocedure" blank defaults to storedprocedure
     public string commandtype { get; set; } 
     // connect string name for matching service connectionstring
     public string connect { get; set; }     
     // paramater names delimited list for sp only, not text command
     public string paramnames { get; set; }  
     // parameter values delimited list for sp only, not text command
     public string paramvalues { get; set; } 
     // can be "scaler" or "nonquery" or blank for default (execute reader)
     public string executetype { get; set; } 
}

The service methods RunSqlDs, RunSqlXml, or RunSqlJson examine the values in the payload, call the appropriate stored procedure or run the SQL text command, and return the results either as DataSet, or JSON/XML string.

Under the hood, the stored procedure's parameters are evaluated using reflection and matched to the values supplied in the Http Request. A connection is made based on a name supplied by the client a field of wcf_rest_command. If the service has valid named connection strings for multiple DBs, any one of them can be used by the client.

I have included a console client app that demonstrates a variety of SQL calls made via the WcfRestSql service. The method runWcfRestSql() takes the wcf_rest_sqlcommand object, prepares the HTTP Request, and returns the Response. There is also an Html page that demonstrates how to use jQuery to call the service.

// Calling the Jquery method that will issue Http Request, from WcfRestJqueryClient.html 

// test call to run sql stored procedure with parameters
function runSqlStoredProcedure() {
    Type = "POST";
    Url =  "<a href="http://localhost:61446/WcfRestSql." + 
           "svc/">http://localhost:61446/WcfRestSql.svc/</a>";
    var foo = {};
    foo.command = "Employee Sales by Country";
    foo.connect = "Northwind";
    foo.commandtype = "";
    foo.paramnames = "@Beginning_Date|@Ending_Date";
    foo.paramvalues = "1990-01-01|2020-01-01";
    foo.executetype = "";
    Data = JSON.stringify(foo);
    ContentType = "application/json; charset=utf-8";
    DataType = "json";
    ProcessData = true;
    Method = "runSqlJson";
    callService();
}

In program.cs, runWcfRestSql is an extension method for wcf_rest_sqlcommand object that formats HTTP Request and returns an HTTP Response in proper format to caller.

public static object runWcfRestSql(this wcf_rest_sqlcommand sqlcmd, string methodToRun)
{
    // extension method for wcf_rest_sqlcommand creates http request and returns response
    // "methodToRun" is name of method called in IWcfRestSql.  Possible values:
    //      "runSqlDS" (returns DataSet)
    //      "runSqlXml" (returns xml as string)
    //      "RunSqlJson" (returns Json as string)
    //
    byte[] data; // payload
    object returnValue = new object(); // might be a dataset or a string
    // set up uri for service call -- methodToRun can be "runSqlDS", 
    //         "runSqlJson", or "runSqlXml" 
    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(
         @"http://localhost:61446/WcfRestSql.svc/" + methodToRun);
    req.Method = "POST"; 
    if (methodToRun.ToLower().Contains("json")) // need json payload
    {
        req.ContentType = @"application/json; charset=utf-8";
        data = sqlcmd.toJsonPayload();
        // extension method to generate json payload in byte[] data
    }
    else // xml payload
    {
        req.ContentType = @"application/xml; charset=utf-8";
        data = sqlcmd.toXmlPayload();
        // extension method to generate xml payload in byte[] data
    }
    req.ContentLength = data.Length;
    Stream requestStream = req.GetRequestStream();
    requestStream.Write(data, 0, data.Length); // write payload to http request
    HttpWebResponse response = (HttpWebResponse)req.GetResponse();
    if (response.StatusCode == HttpStatusCode.OK) // process result
    {
        using (StreamReader sr = new StreamReader(response.GetResponseStream(), Encoding.UTF8))
        {   // check if we're returning string (xml / json) or dataset
            if (methodToRun.ToLower().Contains("json") || 
                      methodToRun.ToLower().Contains("xml"))
                returnValue = sr.ReadToEnd(); // returns json or xml formatted results
            else // DataSet (one or more DataTables) 
            {
                DataSet ds = new DataSet(); // get dataset directly from service call
                StringReader xmlSR = new StringReader(sr.ReadToEnd());
                ds.ReadXml(xmlSR, XmlReadMode.Auto);  
                returnValue = ds; // return dataset to caller
            }
        }
    }
    else // service not run, check error returned
        Console.WriteLine(string.Format("ERROR: Status Code: {0}, Status Description: {1}",
            response.StatusCode, response.StatusDescription));
    return returnValue;
}

Please examine the code for more information -- anyone familiar with HTTP Request/Response should understand it. It should probably be made more robust by using WCF Extensibility, or even migrated up to an Asp Web API service. There are a group of extension methods which handle basic tasks, i.e. converting the results of a stored procedure or SQL command directly into a collection of a known data type on the client.

A copy of the Northwind database is included with three new stored procedures and a script file for adding them to an existing db. They highlight the use of output parameters and scalar return values.

This is a stand-alone WCF REST service that requires its own SQL connection. I am working on another version to integrate the service into an existing EF dbContext, but I’m not sure if I’ll have time to complete it.

There are some serious down-sides to this code. This project was to determine if something could be done, and I believe it is successful at proving that unknown stored procedures can be executed via Wcf Http Rest without having to modify middle-tier code. There is no security built into my test and I in no way recommended using it as-is in a production environment. It’s a huge threat to open SQL calls to clients in any scenario. There are several ways that Http activity can be validated in the WCF code so no damaging activity can be triggered, but they are not part of this demo. To use this service for adding new queries only (i.e. for reporting services), we could use a named Sql User Account and allow read-only access to the database. Another way to enforce security would be to use extensibility to intercept all calls to the WCF and validate the requested Stored Procedure against an external list in a table or config file. The ability to run text queries can simply be removed altogether, if desired.

I hope this code is helpful to anyone interested in mixing SQL Stored Procedures and WCF Rest Services. These two important tools don’t always work very well together even though they are made by the same company and are often used side-by-side.

If anyone has suggestions or improvements, especially if you see ways to integrate this into an existing dbContext/Entity Framework entity set, please let me know.

History

• Original submission 6/12/13.