How to see SQL command execution with Stored Procedure-CodePudding

I have the below block of code:

private TData ExecuteReturnData<TData>(string procName, Func<IDataReader, TData> translator, SqlConnection sqlCon, params SqlParameter[] parameters)
{
    using var sqlCmd = CreateCommand(procName, sqlCon, parameters);
    sqlCmd.CommandTimeout=120;
    using var reader = sqlCmd.ExecuteReader();
    var elems = translator(reader);
    return elems
}

Here I want to see the complete SQL command execution with params in SQL Server code block. How can I check that here?

CodePudding user response：

What you're asking for does not exist. It never exists at any point. The entire purpose of using parameterized queries is the parameter data is NEVER substituted directly into the SQL command string, and therefore will not be available to view in that way.

Parameterized queries are more than simply sanitizing or escaping in the parameter data in the proper way; they quarantine the data from the command, so the two can never meet.

That is, if you have this query:

SELECT * FROM Users WHERE FirstName= @FirstName

and this parameter value:

Samuel

instead of something like this:

SELECT * FROM Users WHERE FirstName = 'Samuel'

The parameter data is sent to the server in a completely separate block than the SQL command. The server receives both parts and does something more like this:

DECLARE @FirstName nvarchar(40) = LoadParameterFromClient()
SELECT * FROM Users WHERE FirstName= @FirstName

(Note: the actual mechanism for this is sp_executesql)

But for what it's worth, I tend to structure similar C# code more like this:

private IEnumerable<TData> ExecuteReturnData<TData>(string SQL, Func<IDataRecord, TData> translator, Action<SqlParameterCollection> addParams)
{
    using var conn = new SqlConnection(" ... "); // My data layer knows about the database I'm using, so I don't need to pass in a conneciton
    using var cmd = new SqlCommand(SQL, conn);

    if (addParams is object) addParams(cmd.Parameters);

    conn.Open();
    using var reader = sqlCmd.ExecuteReader();
    while (reader.Read())
    {
        yield return translator(reader);
    }
}

Then I'd call it using a similar example as above like this:

var results = ExecuteReturnData<string>("SELECT FirstName, LastName FROM Users WHERE FirstName=@FirstName", 
    p => p.Add("@FirstName", SqlDbType.NVarchar,40).Value = "Samuel",
    r  => r["LastName"]   ", "   r["FirstName"]);

For more complex result types I'd have a static FromSQL(IDataRecord data) method on the target type, to avoid making this function call too difficult to read:

var results = ExecuteReturnData<User>("SELECT * FROM Users WHERE FirstName=@FirstName", 
    p => p.Add("@FirstName", SqlDbType.NVarchar,40).Value = "Samuel", 
    User.FromSQL);

As a project grows I might also collect these methods into a separate static type, to avoid over-coupling between the data layer and client code.

And of course you can run stored procedures the same way:

var results = ExecuteReturnData("exec MyProcedure @Param1, @Param2" ... );

CodePudding user response：

How to see SQL command execution with Stored Procedure

I want to see the complete SQL command execution with params in SQL Server code block. How can I check that here?

In Visual Studio open the SQL Server Object Explorer > New Connection to SQL Server > Expand Database > Expand Programmatibility > Stored Procedures and Right Click on a Sproc and choose Debug Procedure. You can step though the T-SQL Code. But you can't jump into the T-SQL from the .Net Code which sounds like what you want.

Debug Procedure > Press F11 to Step into the Stored Procedure:

REF: https://learn.microsoft.com/en-us/sql/ssms/scripting/transact-sql-debugger?view=sql-server-ver16