C# 如何从 SQL Server 的存储过程中检索参数列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I retrieve a list of parameters from a stored procedure in SQL Server
提问by Joshua Hayworth
Using C# and System.Data.SqlClient, is there a way to retrieve a list of parameters that belong to a stored procedure on a SQL Server before I actually execute it?
使用 C# 和 System.Data.SqlClient，有没有办法在我实际执行之前检索属于 SQL Server 上的存储过程的参数列表？
I have an a "multi-environment" scenario where there are multiple versions of the same database schema. Examples of environments might be "Development", "Staging", & "Production". "Development" is going to have one version of the stored procedure and "Staging" is going to have another.
我有一个“多环境”场景，其中有多个版本的相同数据库模式。环境示例可能是“开发”、“暂存”和“生产”。“开发”将拥有存储过程的一个版本，而“暂存”将拥有另一个版本。
All I want to do is validate that a parameter is going to be there before passing it a value and calling the stored procedure. Avoiding that SqlException rather than having to catch it is a plus for me.
我想要做的就是在传递一个值并调用存储过程之前验证参数是否存在。避免 SqlException 而不是必须捕获它对我来说是一个加分项。
Joshua
约书亚
采纳答案by John Sheehan
You can use SqlCommandBuilder.DeriveParameters() (see SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials) or there's this waywhich isn't as elegant.
您可以使用 SqlCommandBuilder.DeriveParameters()（请参阅SqlCommandBuilder.DeriveParameters - 获取存储过程的参数信息 - ADO.NET 教程）或者有这种不那么优雅的方式。
回答by Joshua Hayworth
SqlCommandBuilder.DeriveParameters(command)
This statement does what I need it to.
这个语句做了我需要的。
Here is a full code sample for the way I solved this problem.
这是我解决此问题的方式的完整代码示例。
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
回答by David Wengier
You can use the SqlCommandBuilder object, and call the DeriveParameters method.
您可以使用 SqlCommandBuilder 对象，并调用 DeriveParameters 方法。
Basically you need to pass it a command, that is setup to call your stored proc, and it will hit the DB to discover the parameters, and create the appropriate parameters in the Parameters property of the SqlCommand
基本上，您需要向它传递一个命令，即设置为调用您的存储过程，它将访问数据库以发现参数，并在 SqlCommand 的 Parameters 属性中创建适当的参数
EDIT:You're all too fast!!
编辑：你们都太快了！！
回答by Mark Brackett
You want the SqlCommandBuilder.DeriveParameters(SqlCommand)method. Note that it requires an additional round trip to the database, so it is a somewhat significant performance hit. You should consider caching the results.
您需要SqlCommandBuilder.DeriveParameters(SqlCommand)方法。请注意，它需要对数据库进行额外的往返，因此它对性能有一定的影响。您应该考虑缓存结果。
An example call:
一个示例调用：
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";
// ....
}
回答by Steve Wranovsky
Although its not exactly what you want, here's some sample code that uses the SqlConnection.GetSchema() method to return all the stored procedures associated with a database, and then subsequently all the parameter names and types for each stored procedure. The example below just loads this into variables. Note that this also returns all the "system" stored procedures, which might not be desirable.
虽然它不完全是您想要的，但这里有一些示例代码，它使用 SqlConnection.GetSchema() 方法返回与数据库关联的所有存储过程，然后返回每个存储过程的所有参数名称和类型。下面的示例只是将其加载到变量中。请注意，这也会返回所有“系统”存储过程，这可能是不可取的。
Steve
史蒂夫
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();
}
}
}
}
回答by Portman
Mark has the best implementation of DeriveParameters. As he said, make sure you cache like in this tutorial.
Mark 拥有 DeriveParameters 的最佳实现。正如他所说，确保您像本教程中那样缓存。
However, I think this is a dangerous way of solving your original problem of database sproc versioning. If you are going to change the signature of a procedure by adding or removing parameters, you should do one of the following:
但是，我认为这是解决数据库 sproc 版本控制的原始问题的危险方法。如果要通过添加或删除参数来更改过程的签名，则应执行以下操作之一：
- Code in a backwards-compatible manner by using defaults (for new params) or by simply ignoring a param (for deleted params). This ensures that your client code can always call any version of your stored procedure.
- Explicitly version the procedure by name (so you will have my_proc and my_proc_v2). This ensures that your client code and sprocs stay in sync.
- 通过使用默认值（对于新参数）或简单地忽略参数（对于已删除的参数），以向后兼容的方式进行编码。这可确保您的客户端代码始终可以调用任何版本的存储过程。
- 按名称显式版本化过程（因此您将拥有 my_proc 和 my_proc_v2）。这可确保您的客户端代码和 sproc 保持同步。
Relying on DeriveParameters to validate what version of the sproc you're using seems like the wrong tool for the job, IMHO.
依靠 DeriveParameters 来验证您使用的 sproc 版本似乎是错误的工作工具，恕我直言。
回答by MatthewMartin
All of these ADO.NET solutions are are asking the code library to query the database's metadata on your behalf. If you are going to take that performance hit anyhow, maybe you should just write some helper functions that call
所有这些 ADO.NET 解决方案都要求代码库代表您查询数据库的元数据。如果您无论如何都要承受这种性能损失，也许您应该编写一些调用的辅助函数
Select count(*) from information_schema.parameters
where ...(proc name =.. param name=...) (pseudo-code)
Or maybe even generate your parameters based on the param list you get back. This technique will work with multiple versions of MS SQL and sometimes other ANSI SQL databases.
或者甚至可以根据您返回的参数列表生成您的参数。此技术适用于多个版本的 MS SQL，有时也适用于其他 ANSI SQL 数据库。
回答by VictorEspina
I have been using DeriveParameters with .NET 1.1 and 2.0 since a couple of years now, and worked like a charm every time.
几年以来，我一直在 .NET 1.1 和 2.0 中使用 DeriveParameters，并且每次都像魅力一样工作。
Now I'm working on my first assignment with .NET 3.5, and just found and ugly surprise: DeriveParameters is creating all parameters with SqlDbType "Variant", instead proper SqlDbTypes. This is creating a SqlException when trying to execute SPs with numeric parameters, because SQL Server 2005 says that sql-variant types cant be implictily converted to int (or smallint, or numeric) values.
现在我正在使用 .NET 3.5 完成我的第一个任务，刚刚发现一个丑陋的惊喜：DeriveParameters 正在使用 SqlDbType“Variant”创建所有参数，而不是正确的 SqlDbTypes。这在尝试使用数字参数执行 SP 时会创建 SqlException，因为 SQL Server 2005 说 sql-variant 类型不能隐式转换为 int（或 smallint 或 numeric）值。
I just tested the same code with .NET CF 2.0 and SQL Server 2000, and worked as expected, assigning the correct SqlDbType to each parameters.
我刚刚用 .NET CF 2.0 和 SQL Server 2000 测试了相同的代码，并按预期工作，为每个参数分配了正确的 SqlDbType。
I had tested .NET 2.0 apps against SQL Server 2005 Databases, so is not a SQL Server related issue, so it has to be something related with .NET 3.5
我已经针对 SQL Server 2005 数据库测试了 .NET 2.0 应用程序，所以不是 SQL Server 相关问题，所以它必须与 .NET 3.5 相关
Any ideas?
有任何想法吗？
