CODEWITHSUNDEEP
c#sql-servervb.netdebuggingvisual-studio-debugging
Gullu
Gullu

Reputation: 3529

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

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

Upvotes: 0

Views: 319

Answers (3)

Marc Gravell
Marc Gravell

Reputation: 1063864

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).

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

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

Upvotes: 0

BStateham
BStateham

Reputation: 1629

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.

Upvotes: 1

Related Questions