从 SQL 存储过程动态创建 ASP 表单

• 下载源代码 - 766.2 KB

引言

这个小型的 .aspx Web 应用程序的目的是允许用户执行 SQL 存储过程。它将构建一个输入表单来收集参数信息，并将存储过程生成的��果返回到一个简单的 GridView 或作为 .xlsx 文件直接下载到用户的 PC。它允许开发人员专门在 SQL 中工作。当存储过程完成后，可以通过调用 .aspx 页面并传入存储过程的名称来使其可供用户调用。它主要设计用于允许开发人员以 SQL 存储过程的形式创建基于参数的查询，然后直接将它们交给用户，而无需任何额外的编码。

背景

客户经常请求新的数据提取，通常只需要显示数据网格或将结果推送到 .xlsx 文件中，以便用户可以对结果集进行进一步操作。要运行存储过程，可以通过 querystring 或 Session 变量将其名称传递给页面。这就是该实用程序所需的一切——它将构建一个输入表单来收集参数，用默认值填充控件（如果存在），验证用户输入的参数值，并在提交时执行存储过程。结果将被发送到网页上的原始数据网格，或直接下载到 Excel 中。

虽然使用 EF 和其他 ORM 工具进行新开发使得处理存储过程有点过时，但仍有许多公司依赖 SQL 编码来处理简单的数据管理和查询任务。有了这个工具，就可以处理客户关于新报表或数据提取的请求，而无需重新编译或编码中间层或数据访问层，因此生产环境中运行的 .NET 代码无需修改和重新测试。

Using the Code

存储过程的结果存储在 DataSet 中，因此可以将其传递给各种操作——例如，Microsoft Report Viewer，或者任何可以绑定到 DataTable 的控件。通过使用简单的扩展方法，可以将存储过程的结果直接导入到已知数据类型的集合中。

我包含了一个 default.aspx 页面，演示了如何调用 spRunStoredProc.aspx。它列出了数据库中的所有存储过程（我提供了一个包含一些添加的存储过程的 Northwind 数据库版本）。对于任何选定的存储过程，都会创建一个 URI 并显示——导航到该 URI 将运行该实用程序并向用户显示存储过程。还包含了一个简单的 .html 页面，演示了如何仅通过客户端代码调用该实用程序。传递给该实用程序的所有值——存储过程名称、输出类型等——都可以通过查询字符串或会话变量传递。

基于存储过程参数构建输入表单

当 .aspx 页面加载时，它会收集有关存储过程参数的信息，并在动态 Web 表单上构建用户输入对象。目标是重现开发人员在需要为存储过程专门构建 Web 表单时所做的工作，这是一项相当常见的任务。

文本输入要么显示为文本输入框，要么（如果找到具有特定名称的存储过程）创建一个下拉列表（见下文）。日期字段参数将由 jQuery 日期选择器处理，布尔值或字节值通过复选框处理，数值输入被验证为数值，并且所有参数都假定为必需的。如果存储过程 SQL 为任何参数定义了默认值，它们将用于预填充表单上的输入控件。这是通过使用一些模式匹配代码来提取存储过程中定义的默认值来实现的——这可能不是 100% 有效，但我们已经能够找到所有测试过的存储过程的正确默认值。

要将参数输入呈现为下拉列表，我们使用简单的命名约定。因此，“Country”参数可以是一个下拉列表，其中包含 select 语句中的每个国家值。甚至可以实现动态过滤下拉列表，其中用户在一个下拉列表上的选择会过滤另一个下拉列表上的可用选项（例如，选择汽车的“品牌”会重置“型号”字段的值，就像在汽车搜索网站上一样）。所有这些都是通过创建返回适当列表用于下拉列表的存储过程来实现的，并以一种实用程序能在运行时找到它们的方式命名。

当运行存储过程时，数据库中所有其他存储过程的名称将被收集到一个列表中。对于每个文本输入参数，我们在存储过程名称列表中搜索一个名为 STOREDPROC_PARAMNAME 的过程。因此，如果我们要调用的存储过程“sp_GetCustomers”有一个“@country”输入参数，我们将搜索一个名为“sp_GetCustomers_@country”的存储过程。如果找到，我们将运行它并使用结果填充输入表单上的下拉列表。如果有两个字段，我们假设第一个是 ddl 项目的“id”，第二个字段是显示的“text”。

“sp_GetCustomers_@country”可能看起来像这样

CREATE PROCEDURE sp_GetCustomers_@country
  AS 
  SELECT DISTINCT countryId, CountryName as 'Country' FROM Customers

我们还希望允许在需要时向下拉列表中添加“任何值”的选择。这可以通过更改创建要绑定到下拉列表的结果集的存储过程来实现，如下所示

CREATE PROCEDURE sp_SearchInvoices_@SalesPerson 
AS SELECT DISTINCT i.Salesperson INTO #temp 
from
Invoices as I 
INSERT INTO #temp Values('%%') 
SELECT * FROM #temp 
DROP TABLE
#temp

此存储过程将基于 SalesPerson 创建一个列表，并将‘%%’添加到列值中。‘%%’ 稍后将被应用程序翻译为在下拉选择中显示 *Any*。

创建动态过滤下拉列表。当基于上面概述的正确命名的存储过程创建下拉列表时，它可能有自己的输入参数。如果此参数与正在调用的主存储过程中的其他参数之一的名称匹配，那么用户为该参数选择的值将用于过滤下拉列表的结果，即，您可以有一个用于 Country 的下拉选择，然后有一个用于 City 的下拉选择，它接受一个参数 Country 值，并且 City 下拉列表将基于用户的 Country 选择构建。由于下拉列表设置为自动触发回发，因此下拉列表的内容将重置以反映过滤下拉列表的值。因此，用于创建城市下拉列表的存储过程将如下所示

CREATE PROCEDURE
sp_SearchInvoices_@ShipCity @ShipCountry VARCHAR(50) = '%%' 
AS 
SELECT DISTINCT
i.ShipCity from Invoices as I 
WHERE I.ShipCountry LIKE @ShipCountry

如果提供了 ShipCountry 的值，结果将根据其进行过滤。如果未提供值，将返回所有城市来构建下拉列表。

通过使用这些命名约定，可以为任何存储过程创建一个复杂的输入表单。虽然这可能不是调用 SQL 存储过程的最佳方式，但它满足了完全在 SQL 中编码的要求，而无需任何 .NET 编码。

安装

默认情况下，将使用 AspSqlQuery.aspx 的 web.config 中定义的最后一个连接字符串。可以在查询字符串或会话变量中传递连接字符串名称，因此有可能对 AspSqlQuery.aspx 的不同调用使用多个连接，前提是每个连接字符串名称都与 web.config 中的有效连接字符串匹配。

运行 Web 页面的用户必须有权在数据库上运行存储过程。传递给 AspSqlQuery.aspx Web 页面的名称必须是有效的存储过程名称。Web 页面的外观相当原始，因此我建议进行一些自定义——样式表、母版页等。

摘要

此工具可能对任何编写 SQL 存储过程比编码中间层 C# .NET 应用程序更容易的企业有用。所有编码都可以在数据库中完成，当存储过程完成后，可以通过 Web 表单调用并执行它，而无需任何额外的开发。一些开发团队发现自己经常根据用户对数据的特定请求编写新的查询或数据提取，并希望能够只编写存储过程本身，将其添加到数据库，而不必花费时间在客户端或输出上。此外，对于拥有大量存储过程的系统，此工具允许将其中任何一个转换为功能齐全的即席报表 Web 页面。

我们计划将代码迁移到 MVC 4 或 Asp Web API。最初的计划是创建一个 ascx 控件，可以将其放置在任何 Web 页面上，但我不确定我们是否有时间实现这一点。

以下是 AspRunStoredProc 实用程序的一些功能和限制

• 为存储过程的每个结果集创建多个 GridView 或 Excel 工作表。
• 获取任何存储过程的完整文本
• 查找存储过程参数的默认值
• 根据存储过程参数自动生成完整的输入表单
• 使用通配符（接受“%”或将“*”转换为“%”）进行参数输入。
• 日期参数使用 JQuery 日期选择器，布尔参数使用复选框。
• 使用 ASP 验证来验证参数输入的完整性和数据类型。
• 基于支持过程中的选择语句创建下拉列表。
• 根据其他下拉列表选择动态过滤下拉列表。
• 如果需要，下拉列表可选择“全选”选项。

目前设置的实用程序存在的一些限制

• 仅平面结果集，无主从关系，无钻取。
• 无分页或列排序，尽管如果需要，可以在 GridView 中添加这些功能
• 仅导出到 Excel (.xlsx)，可以添加 ReportViewer、PDF 或 Microsoft Word。
• 未与所有数据类型（例如二进制）进行测试，但大多数基本类型应该工作正常。
• 对于大型查询会很慢（添加分页可能会有所帮助）。
• 无摘要/分组——可以在数据集加载到 Excel 后进行。

我们的目标是允许开发一个不需要在编译代码中进行任何工作的报表实用程序——所有内容都可以使用 SQL 实现。我希望其他开发人员认为这些代码有趣且有帮助。不直接使用该实用程序的开发人员可能仍然发现基于 SQL 存储过程参数构建输入表单的代码在其他场景中很有用。欢迎任何改进或评论，如果创建了 MVC 或 Web API 的新版本，我将发布它。