<p>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?</p>

<p>I have an a "multi-environment" scenario where there are multiple versions of the same database schema. Examples of environments might be "Development", "Staging", &amp; "Production". "Development" is going to have one version of the stored procedure and "Staging" is going to have another.</p>

<p>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.</p>

<p>Joshua</p>

[{'answer_id': 33772, 'author': 'Joshua Hayworth', 'author_id': 2049, 'author_profile': 'https://Stackoverflow.com/users/2049', 'pm_score': 2, 'selected': False, 'text': '<pre><code>SqlCommandBuilder.DeriveParameters(command)\n</code></pre>\n\n<p>This statement does what I need it to.</p>\n\n<p>Here is a full code sample for the way I solved this problem.</p>\n\n<pre><code>Public Sub GetLogEntriesForApplication(ByVal settings As FilterSettings,\n                              Optional ByVal RowGovernor As Integer = -1)\n\n    Dim command As New SqlCommand("GetApplicationActions", \n        New SqlConnection(m_environment.LoggingDatabaseConnectionString))\n    Dim adapter As New SqlDataAdapter(command)\n\n    Using command.Connection\n\n        With command\n\n            .Connection.Open()\n            .CommandType = CommandType.StoredProcedure\n\n            SqlCommandBuilder.DeriveParameters(command)\n\n            With .Parameters\n\n                If settings.FilterOnLoggingLevel Then\n                    If .Contains("@loggingLevel") Then\n                        .Item("@loggingLevel").Value = settings.LoggingLevel\n                    End If\n                End If\n\n                If settings.FilterOnApplicationID Then\n                    If .Contains("@applicationID") Then\n                        .Item("@applicationID").Value = settings.ApplicationID\n                    End If\n                End If\n\n                If settings.FilterOnCreatedDate Then\n                    If .Contains("@startDate") Then\n                        .Item("@startDate").Value = settings.CreatedDate.Ticks\n                    End If\n                End If\n\n                If settings.FilterOnEndDate Then\n                    If .Contains("@endDate") Then\n                        .Item("@endDate").Value = settings.EndDate.Ticks\n                    End If\n                End If\n\n                If settings.FilterOnSuccess Then\n                    If .Contains("@success") Then\n                        .Item("@success").Value = settings.Success\n                    End If\n                End If\n\n                If settings.FilterOnProcess Then\n                    If settings.Process &gt; -1 Then\n                        If .Contains("@process") Then\n                            .Item("@process").Value = settings.Process\n                        End If\n                    End If\n                End If\n\n                If RowGovernor &gt; -1 Then\n                    If .Contains("@topRows") Then\n                        .Item("@topRows").Value = RowGovernor\n                    End If\n                End If\n\n            End With\n\n        End With\n\n        adapter.TableMappings.Clear()\n        adapter.TableMappings.Add("Table", "ApplicationActions")\n        adapter.TableMappings.Add("Table1", "Milestones")\n\n        LogEntries.Clear()\n        Milestones.Clear()\n        adapter.Fill(m_logEntryData)\n\n    End Using\n\nEnd Sub\n</code></pre>\n'}, {'answer_id': 33773, 'author': 'John Sheehan', 'author_id': 1786, 'author_profile': 'https://Stackoverflow.com/users/1786', 'pm_score': 4, 'selected': True, 'text': '<p>You can use SqlCommandBuilder.DeriveParameters() (see <a href="https://web.archive.org/web/20110304121600/http://www.davidhayden.com/blog/dave/archive/2006/11/01/SqlCommandBuilderDeriveParameters.aspx" rel="nofollow noreferrer">SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials</a>) or there\'s <a href="http://www.codeproject.com/KB/database/enumeratesps.aspx" rel="nofollow noreferrer">this way</a> which isn\'t as elegant.</p>\n'}, {'answer_id': 33776, 'author': 'David Wengier', 'author_id': 489, 'author_profile': 'https://Stackoverflow.com/users/489', 'pm_score': 2, 'selected': False, 'text': "<p>You can use the SqlCommandBuilder object, and call the DeriveParameters method.</p>\n\n<p>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</p>\n\n<p><em>EDIT:</em> You're all too fast!!</p>\n"}, {'answer_id': 33781, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 4, 'selected': False, 'text': '<p>You want the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx" rel="noreferrer">SqlCommandBuilder.DeriveParameters(SqlCommand)</a> 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.</p>\n\n<p>An example call:</p>\n\n<pre><code>using (SqlConnection conn = new SqlConnection(CONNSTRING))\nusing (SqlCommand cmd = new SqlCommand("StoredProc", conn)) {\n   cmd.CommandType = CommandType.StoredProcedure;\n   SqlCommandBuilder.DeriveParameters(cmd);\n\n   cmd.Parameters["param1"].Value = "12345";\n\n   // ....\n}\n</code></pre>\n'}, {'answer_id': 33809, 'author': 'Steve Wranovsky', 'author_id': 3429, 'author_profile': 'https://Stackoverflow.com/users/3429', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>Steve</p>\n\n<pre><code>    public void LoadProcedureInfo()\n    {\n        SqlConnection connection = new SqlConnection();\n\n        ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["ConnectionString"];\n\n        connection.ConnectionString = settings.ConnectionString;\n        connection.Open();\n\n        DataTable procedureDataTable = connection.GetSchema("Procedures");\n        DataColumn procedureDataColumn = procedureDataTable.Columns["ROUTINE_NAME"];\n\n        if (procedureDataColumn != null)\n        {\n            foreach (DataRow row in procedureDataTable.Rows)\n            {\n                String procedureName = row[procedureDataColumn].ToString();\n\n                DataTable parmsDataTable = connection.GetSchema("ProcedureParameters", new string[] { null, null, procedureName });\n\n                DataColumn parmNameDataColumn = parmsDataTable.Columns["PARAMETER_NAME"];\n                DataColumn parmTypeDataColumn = parmsDataTable.Columns["DATA_TYPE"];\n\n                foreach (DataRow parmRow in parmsDataTable.Rows)\n                {\n                    string parmName = parmRow[parmNameDataColumn].ToString();\n                    string parmType = parmRow[parmTypeDataColumn].ToString();\n                }\n            }\n        }\n    }\n</code></pre>\n'}, {'answer_id': 33870, 'author': 'Portman', 'author_id': 1690, 'author_profile': 'https://Stackoverflow.com/users/1690', 'pm_score': 1, 'selected': False, 'text': '<p>Mark has the best implementation of DeriveParameters. As he said, make sure you cache like in <a href="http://davidhayden.com/blog/dave/archive/2006/11/03/CachingStoredProcedureParameters.aspx" rel="nofollow noreferrer">this tutorial</a>.</p>\n\n<p>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:</p>\n\n<ul>\n<li>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.</li>\n<li>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.</li>\n</ul>\n\n<p>Relying on DeriveParameters to validate what version of the sproc you\'re using seems like the wrong tool for the job, IMHO.</p>\n'}, {'answer_id': 472819, 'author': 'MatthewMartin', 'author_id': 33264, 'author_profile': 'https://Stackoverflow.com/users/33264', 'pm_score': 0, 'selected': False, 'text': "<p>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 </p>\n\n<pre><code>Select count(*) from information_schema.parameters \nwhere ...(proc name =.. param name=...) (pseudo-code)\n</code></pre>\n\n<p>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.</p>\n"}, {'answer_id': 1501716, 'author': 'VictorEspina', 'author_id': 182233, 'author_profile': 'https://Stackoverflow.com/users/182233', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>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</p>\n\n<p>Any ideas?</p>\n'}]
['https://Stackoverflow.com/questions/33761', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2049/']