Geek Answers Handbook

Debug sql stores proc in VS 2008 from inside asp.net code

I have a SqlDataSource that calls a stored proc. When a page loads Gridview loads that use this SqlDataSource.

At this time, I want to be able to debug a saved process.

I do NOT want to debug the saved process myself.

I want to see what values ​​are actually passed to the stored process and what it actually does.

+4
source share
2 answers

Use the Sql Server Profiler . You can listen to all requests to your sql server if you have rights.

ADDED:

"SQL Profiler without sysadmin rights" or "this" can help you.

Test your code on your own sql server (with sa rights), and not in production. If it works on yours, it will work on production. Otherwise, request trace rights to get your work done!

Therefore, use a local server (express or developer) with ALTER TRACE or sysadmin privileges to use the SQL Server profiler. Do not use the table or modify the stored procedure. Profiler tracks all calls to the sql server with parameters. Just catch these calls!

ADDED:

I found ADO.NET Trace Logging . I think this is what you are looking for. In my opinion, this tracer is not so good to use, but in your case, I think it can help.

+4
source

OK - this is not debugging for sure, but what you can do is put a breakpoint in ASP.NET where exactly you are going to call the database (command parameters are ready)

then go to the nearest window and call this method

? DebugUtils.DebugStoredProc ("Your stored procedure", CommandObj); this will return you a string that you can directly copy and execute in Query Analyzer.

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("'", "''"); } }
0
source

More articles:


All Articles