使用 WCF REST HTTP 调用 SQL Server 上的存储过程

• 下载源代码 - 670.2 KB

引言

过去几年里，我在进行合同 .NET WCF 编码时，注意到一个问题，这个问题阻碍了客户使用 WCF/Rest 作为暴露其中间层 API 的手段（我通常强烈推荐这种模型用于企业应用）。阻碍他们完全拥抱 WCF Rest Web API 模型的是，他们的开发团队严重依赖 SQL 存储过程。他们的开发经理们认为对 SQL 存储过程的支持正在减弱，很快他们就无法使用新的 WCF 模型，除非他们愿意完全放弃使用存储过程。这或许是真的，也或许不是，但有时为了使用某些新工具或技术，一些现有的工具或技术就会过时。许多公司严重依赖 TSQL。他们对突出新的 WCF 技术（Soap、Rest，以及现在的 Web API/MVC）的新示例和入门套件持谨慎态度，这些套件似乎除了基本的数据存储库任务（对存储过程、UDF、触发器、视图等支持非常薄弱）之外，都排除了 SQL 开发。

一位客户提出了一个具体问题：他们能否实现一个框架，通过 WCF Rest 暴露所有 API 方法，同时还能创建客户端代码来运行新的存储过程或执行在设计时未知的 SQL 语句，而无需重新编码或重新编译其中间层 BLL/DAL 层？这对他们来说是一个大问题，因为他们可以编写存储过程并更轻松地修改客户端应用程序，而不是更新、重新编译和重新测试 Dot Net 中间层代码。一个问题是专业技能——一些公司拥有强大的 SQL 开发人员和 Web 开发人员来支持客户端应用程序开发，但在寻找经验丰富的 Dot Net 编码人员来处理中间层方面却遇到了困难。

那么，能否通过 REST WCF 服务调用未知的 SQL 存储过程或命令字符串，并以客户端可以（甚至可能是不包含 .NET 的客户端）消费的方式返回适当的结果？答案是可以，但有几个注意事项。首先，我做了一个使用 SOAP/XML 服务的版本，但放眼未来，似乎很可能他们真的需要一个能够与完全 RESTful HTTP 服务配合使用的工具。

具体要求是

• 支持 SQL Server 2005 或更高版本、Visual Studio 2010 或更高版本、Dot Net 3.5 或更高版本。
• 调用任何存储过程，包含任意数量的参数（输入和输出）。
• 允许执行任何 SQL 查询语句。
• 以 DataSet（用于 Dot Net 客户端）、XML 或 JSON 的形式返回结果。
• 从 Dot Net Http 客户端运行，或使用 JavaScript/jQuery 并在客户端脚本中消费结果。
• 如果需要，返回多个结果集。
• 返回输出参数的值（如果存在）。
• 从 ExecuteNonQuery 命令返回标量值（如果存在）以及受影响的行数。
• 如果 SQL 代码或 WCF 方法生成错误，则将消息返回给调用者。

虽然我能够满足上述要求，但此工具的功能有一些限制：不支持输入或输出表参数，也没有检查所有不同的数据类型。不确定是否可以修改以处理二进制字段，例如。还有严重的安全性考虑和数据持久性问题，我将在下文进一步解释。

使用代码

首先，需要注意的注意事项/免责声明：这不是处理 SQL 数据的最佳实践或高效方式。在设计时就可以确定的存储过程或查询应该包含在正在使用的任何实体生成器工具中。EF 的函数导入有点笨拙，但通常还可以，并且让 IntelliSense 拾取从 SQL 返回的对象具有很大优势，尽管服务可以直接将结果导入到客户端已知的类型中。直接调用 SQL 会导致持久性管理失效。除非在进行任何调用后从数据库刷新所有 dbContect 对象，否则没有跟踪或 DTO 框架会知道发生了什么，这几乎是不切实际的。

如果此代码不违反 REST 服务设计规则，那么它至少违反了仅使用 URI 和 HTTP 动词进行服务消息传递的精神。我们正在传递一个本身就是一组指令的对象，其中包含方法名称以及用于存储过程的可变长度参数列表。然而，它确实允许 HTTP 客户端从数据库中消费任何存储过程或查询，这符合最初的意图，因此可能有助于开发经理对在 ESB 应用程序中使用 WCF/REST 感到更安心。

WcfRestSql 服务使用 Http Post，并使用一个简单的对象 wcf_rest_command，该对象以 XML 或 JSON 格式序列化为有效负载。该对象包含字段，用于存储过程名称、将匹配服务配置文件中的名称的连接字符串，以及一个分隔的参数名称和参数值列表（如果需要）。还有一个 commandtype 字段，用于指定我们正在运行“text”（文本）或“storedProcedure”（存储过程）命令类型，以及一个名为 executetype 的字段，用于指定 SQL 命令的“scaler”（标量）或“nonquery”（非查询）执行类型。

// 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; } 
}

服务方法 RunSqlDs、RunSqlXml 或 RunSqlJson 会检查有效负载中的值，调用相应的存储过程或运行 SQL 文本命令，并将结果以 DataSet、JSON 或 XML 字符串的形式返回。

在底层，使用反射评估存储过程的参数，并将其与 Http 请求中提供的值进行匹配。连接是根据客户端在 wcf_rest_command 字段中提供的名称建立的。如果服务有多个数据库的有效命名连接字符串，客户端可以使用其中任何一个。

我包含了一个控制台客户端应用程序，演示了通过 WcfRestSql 服务进行的各种 SQL 调用。runWcfRestSql() 方法接收 wcf_rest_sqlcommand 对象，准备 HTTP 请求，并以正确的格式向调用者返回响应。还有一个 HTML 页面演示了如何使用 jQuery 调用该服务。

// 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="https://:61446/WcfRestSql." + 
           "svc/">https://: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();
}

在 program.cs 中，runWcfRestSql 是 wcf_rest_sqlcommand 对象的一个扩展方法，它格式化 HTTP 请求并将 HTTP 响应以正确的格式返回给调用者。

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(
         @"https://: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;
}

请检查代码以获取更多信息——任何熟悉 HTTP 请求/响应的人都应该能够理解。它可能需要使用 WCF 扩展性使其更加健壮，或者甚至可以迁移到 Asp Web API 服务。有一组扩展方法可以处理基本任务，例如将存储过程或 SQL 命令的结果直接转换为客户端上已知数据类型的集合。

随附了 Northwind 数据库的副本，其中包含三个新的存储过程和一个用于将其添加到现有数据库的脚本文件。它们突出了输出参数和标量返回值的使用。

这是一个独立的 WCF REST 服务，需要自己的 SQL 连接。我正在开发另一个版本，以便将该服务集成到现有的 EF dbContext 中，但不确定是否有时间完成。

此代码存在一些严重的缺点。此项目的目的是确定是否可以实现某项功能，我相信它成功地证明了可以在不修改中间层代码的情况下通过 Wcf Http Rest 执行未知的存储过程。我的测试中没有内置任何安全性，我绝不建议在生产环境中使用它。在任何情况下，它都会对向客户端开放 SQL 调用构成巨大威胁。WCF 代码中有多种方法可以验证 HTTP 活动，以免触发任何破坏性活动，但这些都不是此演示的一部分。如果仅使用此服务来添加新查询（例如，用于报告服务），我们可以使用命名的 Sql 用户帐户并允许对数据库进行只读访问。强制执行安全的另一种方法是使用扩展性来拦截对 WCF 的所有调用，并将请求的存储过程与表或配置文件中的外部列表进行比较。如果需要，可以完全删除执行文本查询的功能。

我希望这段代码能对那些有兴趣混合使用 SQL 存储过程和 WCF REST 服务的人有所帮助。虽然这两个重要工具由同一家公司制造并且经常并排使用，但它们并不总是能很好地协同工作。

如果任何人有建议或改进，特别是如果您看到将此集成到现有 dbContext/Entity Framework 实体集中的方法，请告诉我。

历史

• 首次提交 2013 年 6 月 12 日。