首页 / 知识

关于C＃：如何从SQL Server中的存储过程中检索参数列表

2023-04-13 13:58:00

How can I retrieve a list of parameters from a stored procedure in SQL Server

使用C＃和System.Data.SqlClient，是否可以在实际执行之前检索属于SQL Server上存储过程的参数列表？

我有一个"多环境"方案，其中同一数据库架构有多个版本。环境的示例可能是"开发"，"分段"和"生产"。"开发"将具有存储过程的一个版本，"分期"将具有另一个版本。

我要做的就是在传递参数值和调用存储过程之前，验证参数是否存在。对我来说，避免该SqlException而不是必须捕获它是一个加号。

约书亚记

您需要SqlCommandBuilder.DeriveParameters(SqlCommand)方法。请注意，它需要额外的数据库往返行程，因此对性能的影响很大。您应该考虑缓存结果。

调用示例：

1
2
3
4
5
6
7
8
9

using (SqlConnection conn = new SqlConnection(CONNSTRING))
using (SqlCommand cmd = new SqlCommand("StoredProc", conn)) {
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);

cmd.Parameters["param1"].Value ="12345";

// ....
}

您可以使用SqlCommandBuilder.DeriveParameters()(请参阅SqlCommandBuilder.DeriveParameters-获取存储过程的参数信息-ADO.NET教程)，或者这种方法不太理想。

尽管这并不是您所需要的，但这是一些示例代码，该示例代码使用SqlConnection.GetSchema()方法返回与数据库关联的所有存储过程，然后返回每个存储过程的所有参数名称和类型。下面的示例将其加载到变量中。请注意，这也将返回所有"系统"存储过程，这可能是不希望的。

史蒂夫

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

public void LoadProcedureInfo()
{
SqlConnection connection = new SqlConnection();

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ConnectionString"];

connection.ConnectionString = settings.ConnectionString;
connection.Open();

DataTable procedureDataTable = connection.GetSchema("Procedures");
DataColumn procedureDataColumn = procedureDataTable.Columns["ROUTINE_NAME"];

if (procedureDataColumn != null)
{
foreach (DataRow row in procedureDataTable.Rows)
{
String procedureName = row[procedureDataColumn].ToString();

DataTable parmsDataTable = connection.GetSchema("ProcedureParameters", new string[] { null, null, procedureName });

DataColumn parmNameDataColumn = parmsDataTable.Columns["PARAMETER_NAME"];
DataColumn parmTypeDataColumn = parmsDataTable.Columns["DATA_TYPE"];

foreach (DataRow parmRow in parmsDataTable.Rows)
{
string parmName = parmRow[parmNameDataColumn].ToString();
string parmType = parmRow[parmTypeDataColumn].ToString();
}
}
}
}

您可以使用SqlCommandBuilder对象，并调用DeriveParameters方法。

基本上，您需要向它传递一个命令，该命令被设置为调用存储的proc，它将命中数据库以发现参数，并在SqlCommand的Parameters属性中创建适当的参数

编辑：你们都太快了！

1	SqlCommandBuilder.DeriveParameters(command)

这句话满足了我的需要。

这是解决此问题的完整代码示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

Public Sub GetLogEntriesForApplication(ByVal settings As FilterSettings,
Optional ByVal RowGovernor As Integer = -1)

Dim command As New SqlCommand("GetApplicationActions",
New SqlConnection(m_environment.LoggingDatabaseConnectionString))
Dim adapter As New SqlDataAdapter(command)

Using command.Connection

With command

.Connection.Open()
.CommandType = CommandType.StoredProcedure

SqlCommandBuilder.DeriveParameters(command)

With .Parameters

If settings.FilterOnLoggingLevel Then
If .Contains("@loggingLevel") Then
.Item("@loggingLevel").Value = settings.LoggingLevel
End If
End If

If settings.FilterOnApplicationID Then
If .Contains("@applicationID") Then
.Item("@applicationID").Value = settings.ApplicationID
End If
End If

If settings.FilterOnCreatedDate Then
If .Contains("@startDate") Then
.Item("@startDate").Value = settings.CreatedDate.Ticks
End If
End If

If settings.FilterOnEndDate Then
If .Contains("@endDate") Then
.Item("@endDate").Value = settings.EndDate.Ticks
End If
End If

If settings.FilterOnSuccess Then
If .Contains("@success") Then
.Item("@success").Value = settings.Success
End If
End If

If settings.FilterOnProcess Then
If settings.Process > -1 Then
If .Contains("@process") Then
.Item("@process").Value = settings.Process
End If
End If
End If

If RowGovernor > -1 Then
If .Contains("@topRows") Then
.Item("@topRows").Value = RowGovernor
End If
End If

End With

End With

adapter.TableMappings.Clear()
adapter.TableMappings.Add("Table","ApplicationActions")
adapter.TableMappings.Add("Table1","Milestones")

LogEntries.Clear()
Milestones.Clear()
adapter.Fill(m_logEntryData)

End Using

End Sub

Mark具有DeriveParameters的最佳实现。正如他所说，请确保像本教程一样进行缓存。

但是，我认为这是解决数据库存储过程版本控制的原始问题的危险方法。如果要通过添加或删除参数来更改过程的签名，则应执行以下操作之一：

通过使用默认值(对于新参数)或简单地忽略参数(对于已删除参数)，以向后兼容的方式进行编码。这样可以确保您的客户端代码始终可以调用存储过程的任何版本。
通过名称明确版本化该过程(因此您将拥有my_proc和my_proc_v2)。这样可以确保您的客户端代码和存储过程保持同步。

依靠DeriveParameters来验证您使用的sproc版本是错误的工具，恕我直言。

从几年前开始，我就一直将DeriveParameters与.NET 1.1和2.0结合使用，并且每次都像魅力一样。

现在，我正在使用.NET 3.5进行第一次分配，并且发现了一个令人震惊的丑闻：DeriveParameters使用SqlDbType" Variant"创建所有参数，而不是使用适当的SqlDbTypes。尝试使用数字参数执行SP时，这会创建SqlException，因为SQL Server 2005说不能将sql变量类型隐式转换为int(或smallint或数字)值。

我刚刚使用.NET CF 2.0和SQL Server 2000测试了相同的代码，并按预期工作，为每个参数分配了正确的SqlDbType。

我已经针对SQL Server 2005数据库测试了.NET 2.0应用程序，因此不是与SQL Server相关的问题，因此它必须与.NET 3.5相关。

有任何想法吗？

所有这些ADO.NET解决方案都在要求代码库代表您查询数据库的元数据。如果您无论如何都要受到性能的影响，也许您应该编写一些帮助函数来调用