如何从SQL Server中的存储过程中检索参数列表
使用Cand System.Data.SqlClient，是否可以在实际执行之前检索属于SQL Server上存储过程的参数列表？
我有一个"多环境"方案，其中同一数据库架构有多个版本。环境的示例可能是"开发"，"分段"和"生产"。 "开发"将具有存储过程的一个版本，"分期"将具有另一个版本。
我要做的就是在传递参数值和调用存储过程之前，验证参数是否存在。对我来说，避免该SqlException而不是必须捕获它是一个加号。
约书亚记
解决方案
回答
SqlCommandBuilder.DeriveParameters(command)
这句话满足了我的需要。
这是解决此问题的完整代码示例。
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
回答
我们可以使用SqlCommandBuilder.DeriveParameters()(请参阅SqlCommandBuilder.DeriveParameters获取存储过程ADO.NET教程的参数信息)，或者使用这种方法不太理想。
回答
我们可以使用SqlCommandBuilder对象，并调用DeriveParameters方法。
基本上，我们需要向它传递一个命令，该命令被设置为调用存储的proc，它将命中数据库以发现参数，并在SqlCommand的Parameters属性中创建适当的参数
编辑：你们都太快了！
回答
我们需要SqlCommandBuilder.DeriveParameters(SqlCommand)方法。请注意，这需要额外的数据库往返行程，因此对性能的影响很大。我们应该考虑缓存结果。
一个示例调用：
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";
// ....
}
回答
尽管这并不是我们所需要的，但这是一些示例代码，该示例代码使用SqlConnection.GetSchema()方法返回与数据库关联的所有存储过程，然后返回每个存储过程的所有参数名称和类型。下面的示例将其加载到变量中。请注意，这也将返回所有"系统"存储过程，这可能是不希望的。
史蒂夫
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();
}
}
}
}
回答
Mark具有DeriveParameters的最佳实现。正如他所说，请确保像本教程一样进行缓存。
但是，我认为这是解决数据库存储过程版本控制的原始问题的危险方法。如果要通过添加或者删除参数来更改过程的签名，则应执行以下操作之一：
- 通过使用默认值(对于新参数)或者仅忽略参数(对于已删除参数)，以向后兼容的方式进行编码。这样可以确保客户端代码始终可以调用存储过程的任何版本。
- 通过名称明确版本化该过程(因此我们将拥有my_proc和my_proc_v2)。这样可以确保客户端代码和存储过程保持同步。
依靠DeriveParameters来验证我们使用的sproc版本是错误的工具，恕我直言。
回答
所有这些ADO.NET解决方案都在要求代码库代表我们查询数据库的元数据。如果我们无论如何都要受到性能的影响，也许我们应该编写一些帮助函数来调用
Select count(*) from information_schema.parameters where ...(proc name =.. param name=...) (pseudo-code)
或者甚至可以根据返回的参数列表生成参数。该技术将与MS SQL的多个版本一起工作，有时还会与其他ANSI SQL数据库一起工作。
回答
从几年前开始，我就一直将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相关。
有任何想法吗？
