SQL statement under the covers

[Rene]

As we all know, ADO.Net allows you to use a command object to execute a
query. We all also know that the command object also exposes a parameter
collection that you can use if you are building a parameterized SQL
statement.

I am assuming that internally, ADO.Net takes the parameterized query and the
parameter collection to build the query statement that is sent to the
backend database engine (SQL server, Jet engine etc). So for example,
assuming that I have a parameterized query and that I add a parameter to the
command parameter collection as show below:

SELECT * FROM Customers WHERE CustomerName = @CustName
myCommand.Parameters.Add("@CustName", "Pupu Caca");

I am assuming that ADO.Net will take the information entered above and
produce an SQL statement that is sent to the database engine such as the one
below:

SELECT * FROM Customers WHERE CustomerName = 'Pupu Caca'

If this is true, is there a way to get this (already parsed) SQL statement?

Thanks.

 

[luxspes]

If you are working in SQLServer... you can look at the resulting
statement using the Profiler included with SQLServer
("C:\Program Files\Microsoft SQL Server\80\Tools\Binn\profiler.exe")

If you are working with some other database... i think it would be kind
a hard... why would you like to able to "see" the final statement?

 

[Rene]

If you are working with some other database... i think it would be kind a

hard... why would you like to able to "see" the final statement?

Well, I have a mix mode database application. When the application connects
to the online database I use SQL server and when the application is working
locally then I use Jet/Access database.

The problem is with Jet, there are some parameterized queries that are not
working correctly and although I found work around them I am simply too
curious to find out whets going on.

And in case you ask, no, I can't use MSDE or SQL Express for my local
database, the download is too big for those database engines and we need to
make our application as small as possible to make it more attractive. Jet
works beautifully and it's already included with the OS (distribution size
== 0).

Thanks.

 

[luxspes]

Well, I have a mix mode database application. When the application connects
to the online database I use SQL server and when the application is working
locally then I use Jet/Access database.

The problem is with Jet, there are some parameterized queries that are not
working correctly and although I found work around them I am simply too
curious to find out whets going on.

And in case you ask, no, I can't use MSDE or SQL Express for my local
database, the download is too big for those database engines and we need to
make our application as small as possible to make it more attractive. Jet
works beautifully and it's already included with the OS (distribution size
== 0).

Have you tried this: (How To Generate an ODBC Trace with ODBC Data
Source Administrator) http://support.microsoft.com/kb/q274551/ ?

 

[luxspes]

Well, I have a mix mode database application. When the application connects
to the online database I use SQL server and when the application is working
locally then I use Jet/Access database.

The problem is with Jet, there are some parameterized queries that are not
working correctly and although I found work around them I am simply too
curious to find out whets going on.

And in case you ask, no, I can't use MSDE or SQL Express for my local
database, the download is too big for those database engines and we need to
make our application as small as possible to make it more attractive. Jet
works beautifully and it's already included with the OS (distribution size
== 0).

Or this (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp

 

[luxspes]

Or this (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp

Copy&Pasted from (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):

Using Tracing to Debug a Parameter Binding Problem

Now that we've gone through the quick overview of tracing, I'd like to
present a simple use case. I'd often use ODBC trace to do problem
determination when an application would "eat" a rich error message and
produce a polite but fairly "information-free" message. Such application
code would look like this:

string s = GetConnectionStringFromConfigFile();
using (SqlConnection conn = new SqlConnection(s))
using (SqlCommand cmd = new SqlCommand(
"select * from authors where au_id = @auid", conn))
{
// the error is hardcoded here but could have come from suboptimal
// editing in a graphic user interface
cmd.Parameters.Add("@auid", SqlDbType.Int);
cmd.Parameters[0].Value = 123456789;
SqlDataReader rdr = null;
try {
// some code that could fail goes here
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0]);
rdr.Close();
}
catch (Exception e) {
MessageBox.Show("polite error message");
}
}

In this case, the error was caused by a parameter type mismatch, and the
person diagnosing the error might not have access to the source code of
the program. Turning on the trace, we'll see output like this:

"enter_01 <sc.SqlCommand.ExecuteReader|API> 1#"
"<sc.SqlCommand.get_Connection|API> 1#"
"<sc.SqlCommand.get_Connection|API> 1#"
"<sc.TdsParser.CreateSession|ADV> 1# created session 3"
"<sc.TdsParserSessionPool.CreateSession|ADV> 1# adding session 3 to pool"
"<sc.TdsParserSessionPool.GetSession|ADV> 1# using session 3"
"<sc.TdsParser.GetSession|ADV> 1# getting session 3 from pool"
"<sc.SqlCommand.ExecuteReader|INFO> 1# Command executed as RPC."
"<sc.SqlCommand.get_Connection|API> 1#"
"leave_01"
"enter_01 <sc.SqlDataReader.Read|API> 1#"
"<sc.SqlError.SqlError|ERR> infoNumber=245 errorState=1 errorClass=16
errorMessage='Syntax error converting the varchar value '172-32-1176' to a
column of data type int.' procedure='' lineNumber=1"
"leave_01"

This shows us directly that there is a parameter value mismatch. The
sample and the trace file are provided in the article code. Note that
the trace file is much more compact in this case because we're only
tracing with the System.Data.1 provider.

 

[Rene]

This looks very good!



I definitely have to do some reading on this.



Thanks for the help!

Or this (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp

Copy&Pasted from (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):

Using Tracing to Debug a Parameter Binding Problem

Now that we've gone through the quick overview of tracing, I'd like to
present a simple use case. I'd often use ODBC trace to do problem
determination when an application would "eat" a rich error message and
produce a polite but fairly "information-free" message. Such application
code would look like this:

string s = GetConnectionStringFromConfigFile();
using (SqlConnection conn = new SqlConnection(s))
using (SqlCommand cmd = new SqlCommand(
"select * from authors where au_id = @auid", conn))
{
// the error is hardcoded here but could have come from suboptimal
// editing in a graphic user interface
cmd.Parameters.Add("@auid", SqlDbType.Int);
cmd.Parameters[0].Value = 123456789;
SqlDataReader rdr = null;
try {
// some code that could fail goes here
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0]);
rdr.Close();
}
catch (Exception e) {
MessageBox.Show("polite error message");
}
}

In this case, the error was caused by a parameter type mismatch, and the
person diagnosing the error might not have access to the source code of
the program. Turning on the trace, we'll see output like this:

"enter_01 <sc.SqlCommand.ExecuteReader|API> 1#"
"<sc.SqlCommand.get_Connection|API> 1#"
"<sc.SqlCommand.get_Connection|API> 1#"
"<sc.TdsParser.CreateSession|ADV> 1# created session 3"
"<sc.TdsParserSessionPool.CreateSession|ADV> 1# adding session 3 to pool"
"<sc.TdsParserSessionPool.GetSession|ADV> 1# using session 3"
"<sc.TdsParser.GetSession|ADV> 1# getting session 3 from pool"
"<sc.SqlCommand.ExecuteReader|INFO> 1# Command executed as RPC."
"<sc.SqlCommand.get_Connection|API> 1#"
"leave_01"
"enter_01 <sc.SqlDataReader.Read|API> 1#"
"<sc.SqlError.SqlError|ERR> infoNumber=245 errorState=1 errorClass=16
errorMessage='Syntax error converting the varchar value '172-32-1176' to a
column of data type int.' procedure='' lineNumber=1"
"leave_01"

This shows us directly that there is a parameter value mismatch. The
sample and the trace file are provided in the article code. Note that the
trace file is much more compact in this case because we're only tracing
with the System.Data.1 provider.

 

[David Browne]

As we all know, ADO.Net allows you to use a command object to execute a
query. We all also know that the command object also exposes a parameter
collection that you can use if you are building a parameterized SQL
statement.

I am assuming that internally, ADO.Net takes the parameterized query and
the parameter collection to build the query statement that is sent to the
backend database engine (SQL server, Jet engine etc). So for example,
assuming that I have a parameterized query and that I add a parameter to
the command parameter collection as show below:

SELECT * FROM Customers WHERE CustomerName = @CustName
myCommand.Parameters.Add("@CustName", "Pupu Caca");

I am assuming that ADO.Net will take the information entered above and
produce an SQL statement that is sent to the database engine such as the
one below:

SELECT * FROM Customers WHERE CustomerName = 'Pupu Caca'

If this is true, is there a way to get this (already parsed) SQL
statement?

This is not true (at least for SQL Server). For SQL Server the query with
the parameter markers is sent to the server along with the values for the
parameters. SQL Server compiles, optimizes and caches a query plan for the
query with the parameter markers which will be reused for any subsequent
query with the same parameter markers.

David