how to get exec string for sql management studio from .net code

Question

I am debugging code someone else wrote that calls a lot of stored procedures (sql server 2008 r2) from C# code. The C# code looks like this

SqlCommand sqlCommand = new SqlCommand(strSP, ConnectionOpen());
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = intTimeOut;
//System.Data.SqlClient.SqlParameter[] prmSQL
if (prmSQL != null)
{
  while (i < prmSQL.Length)
  {
    sqlCommand.Parameters.Add(prmSQL[i]);
    i = i + 1;
  }
}

SqlDataReader sqlReader = sqlCommand.ExecuteReader();

For debugging my stored procedures I really need the string that sql management studio needs which is like exec sp_name param one, param two (with quotes if needed for strings and dates..) The sql command object does not provide this string via some property. The only way I know is to run the sql profiler on sql server and grab the string. Unfortunately the DBA's do not like this since they say running the profiler impacts performance. Is there any addin or code snippet you guys use to get the sp exec string from c# code ? Whats the best way to get this string ? Thanks

Have a look here [1]: http://stackoverflow.com/questions/5356467/from-net-can-i-get-the-full-sql-string-generated-by-a-sqlcommand-object-with-s [2]: http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object — StaWho, Aug 30 '12 at 20:41
@StaWho thx for the links. Some nice tips there and looks like my question is a dup, but they do not include the elegant solution proposed by Marc below. — Gullu, Aug 31 '12 at 17:25

score 1 · Answer 1 · answered Aug 30 '12 at 20:37

1

Rep is too low (still a noob to StackOverflow)to comment so I'm posting this as an answer. My apologies. However, you might consider looking at SMO. SMO is a .NET object model that can be used to interact with SQL Server. Using SMO you can get a reference to a specific Stored Procedure and then enumerate it's parameters.

That might help you get started.

answered Aug 30 '12 at 20:37

BStateham

1,609
10
15

Never thought of SMO. I am guessing the sql server profiler uses a lot of SMO under the hood and maybe I can easily get the string I want via some SMO class. But for now Marc's solution looks best. thx – Gullu Aug 31 '12 at 17:30

score 1 · Accepted Answer · edited Jan 18 '21 at 12:32

You could use a tool like mvc-mini-profiler available on NuGet (note: the name is misleading; it isn't limited to MVC). Minor clarification - since it wraps the connection, you would need to use the abstract DbConnection rather than SqlConnection, and then you just tweak the one line of code (probably in a utility class somewhere) that creates your connection, i.e. instead of:

var conn = new SqlConnection(someString);
return conn;

you might use:

var conn = new SqlConnection(someString);
return new StackExchange.Profiling.Data.ProfiledDbConnection(
            conn, MiniProfiler.Current);

There's a couple of other steps to enable it (all shown on the site page), but it literally takes 2 minutes to add to an MVC application. The output is that it monitors, in real time, for enabled users (developers etc), all the activity. We use it 24x7 on stackoverflow/stackexchange (meaning: we made very sure it didn't impact performance). A live demo is available on https://data.stackexchange.com/ - just log in, and the profiling data is visible top-left. It automatically presents the data in a form runnable from SSMS, because that is how we often use it - so: it presents parameters as though they were variable declarations / initializations.

It also plays nicely with ORMs such as LINQ-to-SQL and dapper-dot-net (and many others).

thx Marc. I think it is worthwhile to look into this mini profiler that will get the job done for me. — Gullu, Aug 31 '12 at 17:32

ron tornambe · Answer 3 · 2012-08-30T20:49:25.490

In order to construct the EXEC command, you will need to know the parameter names used by the procedure. I believe you can find them by using the GetDbSchemaTable method, whcih will retrieve stored procedure SQL (I have done this using MS-Access/OLEDB and am assuming it works the same for MS-SQL/SqlClient):

using (conn == new OleDb.OleDbConnection(DBConnection)) {
    conn.Open();
    DataTable DBObject = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Procedures, null);
}

The column named "PROCEDURE_DEFINITION" contains the procedure's SQL and hopefully the parameter list.

You may also want to have a look at Obtaining Schema Information from a Database.

HTH

how to get exec string for sql management studio from .net code

3 Answers3