I have a SqlDataSource that calls a stored proc. When the page loads a Gridview loads that uses that SqlDataSource.
At this time I want to be able to debug the stored proc.
I DON'T want to debug the stored proc on it's own.
I want to see what values are actually being passed to the stored proc and what it is actually doing.
Use Sql Server Profiler. You can listen all requests to your sql server if you have rights.
ADDED:
"SQL Profiler without sysadmin rights" or "this one" can help you.
Test your code on your own sql server(with sa rights), not production. If it works at your one it will work on production. Otherwise demand Trace rights to do to your job!
So, use a local server (express edition for example, or developer edition) with ALTER TRACE or sysadmin rights to use SQL Server Profiler. Don't use table and don't change your stored procedure. Profiler traces all calls to the sql server with param values. Just catch this calls!
ADDED:
I have found ADO.NET Trace Logging. I think, this is what you are looking for. On my opinion this tracer is not so good to use, but in your case, I think it can help.
OK - this is not debugging exactly , but what you can do is put a break point in the ASP.NET where exactly you are about to call the Database ( command parameters is ready)
then go to immediate window and call this method
?DebugUtils.DebugStoredProc("Your stored procedure ",CommandObj); this will return you a string which you can directly copy and execute in Query Analyser.
public class DebugUtils
{
public DebugUtils()
{
//
// TODO: Add constructor logic here
//
}
public static string DebugStoredProc(string spName, SqlCommand cmd)
{
//string szSPComand = string.Empty;
string szSPComandFirstPart = "EXEC "+spName + " ";
string szSPComandSecondPart = string.Empty;
string szDEBug = string.Empty;
foreach (SqlParameter sqlparam in cmd.Parameters)
{
if (szSPComandSecondPart.Length > 0) { szSPComandSecondPart += " , "; szDEBug += " , "; }
szSPComandSecondPart += string.Format("{0}={1}",sqlparam.ParameterName, ReturnParamToken(sqlparam));
szDEBug += String.Format(" {0} ", sqlparam.ParameterName);
}
return szSPComandFirstPart + " " + szSPComandSecondPart;
}
private static string ReturnParamToken(SqlParameter param)
{
string sToken = string.Empty;
SqlDbType dbtype = param.SqlDbType;
if ( dbtype == SqlDbType.Char
|| dbtype == SqlDbType.VarChar
|| dbtype == SqlDbType.Xml
|| dbtype == SqlDbType.UniqueIdentifier )
{
#region String - chars
if (param.Value == DBNull.Value)
{
sToken = " null ";
}
else
{
sToken = "'" + FixQuotes(Convert.ToString(param.Value)) + "'";
}
#endregion
}
else if (
dbtype == SqlDbType.BigInt
|| dbtype == SqlDbType.Decimal
|| dbtype == SqlDbType.Float
|| dbtype == SqlDbType.Int
|| dbtype == SqlDbType.Money
|| dbtype == SqlDbType.Real
|| dbtype == SqlDbType.SmallInt
|| dbtype == SqlDbType.SmallMoney
|| dbtype == SqlDbType.TinyInt
)
{
#region Numbers
if (param.Value == DBNull.Value)
{
sToken = " null ";
}
else
{
sToken = Convert.ToString(param.Value);
}
#endregion
}
else if (
dbtype == SqlDbType.DateTime
|| dbtype == SqlDbType.DateTime2
|| dbtype == SqlDbType.SmallDateTime
|| dbtype == SqlDbType.Time
|| dbtype == SqlDbType.Timestamp
)
{
#region Dates
if (param.Value == DBNull.Value)
{
sToken = " null ";
}
else
{
sToken = string.Format("'{0}'", Convert.ToString(param.Value));
}
#endregion
}
else if (
dbtype == SqlDbType.Bit
)
{
#region BIT or BOOL
if (param.Value == DBNull.Value)
{
sToken = " null ";
}
else
{
if (Convert.ToBoolean(param.Value) == true)
{
sToken = "1";
}
else
{
sToken = Convert.ToString(param.Value);
}
}
#endregion
}
else if (
dbtype == SqlDbType.Binary
|| dbtype == SqlDbType.Date
|| dbtype == SqlDbType.DateTimeOffset
|| dbtype == SqlDbType.Image
|| dbtype == SqlDbType.NChar
|| dbtype == SqlDbType.NText
|| dbtype == SqlDbType.NVarChar
|| dbtype == SqlDbType.Structured
|| dbtype == SqlDbType.Text
|| dbtype == SqlDbType.Udt
|| dbtype == SqlDbType.VarBinary
|| dbtype == SqlDbType.Variant
)
{
if (param.Value == DBNull.Value)
{
sToken = " null ";
}
else
{
sToken = string.Format("UNKNOWN DATATYPE - NO HANDLER FOUND for @param : {0} of datatype:{1} ", param.ParameterName, Convert.ToString(dbtype.ToString()));
}
}
return sToken;
}
private static string FixQuotes(string str)
{
return str.Replace("'", "''");
}
}
