Interesting Problem regarding Parameters

Backgroud:
My company is currently migrating to .Net from PHP for a number of reasons,
however we have one slight problem we would very much like to solve before
moving to .Net (ok, many but this one is a toughie).

Problem:
On our current PHP platform we have our own database abstraction class which
inherits from the PEAR DB abstraction class. One of the major reasons why we
did this is because we wanted to add automatic querie logging to our
database connection object. Here is a brief snippit of code to demonstarte
the functionality we currently have:

//Creating DB connection objects
$connection = mis_DB::connect( 'database-server', 'database' );
$connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE );
$connection->setLogMask( MISDB_LOG_ALL );

This will automatically log any queries thrown at at, for example (I'm using
prepare and execute syntax for a reason that will be come clear in just a
minute):

/* Disclaimer, such a DB does not exist on my company DB server, but it
should! */
$query = "SELECT * FROM beer.beer_styles WHERE style = ?";
$handle = $connection->prepare( $query );
$result_object = $connection->execute( $handle, array( "Imperial Stout" ) );

What our current database class will do (after executing the query) is logg
the following message to the /log_path/filename.log file:
Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM
beer.beer_styles WHERE style = 'Imerial Stout'

What we would like is to have some thing similar with our new .Net DB class,
however the problem I'm running into is I don't know how to get the query
that was actually executed on the server, what I mean by that is:

//Assum proper connection string
OleDbConnection Connection = new OleDbConnection( ConnectionString );
OleDbCommand Command = new OleDbCommand( Connection );
Connection.Open();

Command.CommandText = @"SELECT * FROM beer.beer_styles WHERE style =
@style";
Command.Paramaters.Add( "@style", "Imperial Stout" );
OleDbDataReader Reader = Command.ExecuteReader();

Now that the query has executed, how to I pull the value:
"SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'"
out of the database class v.
"SELECT * FROM beer.beer_styles WHERE style = @style"

Thanks for any help you can provide!
--------------------
Carlo Razzeto
carlo.razzeto@mtginfo.com

SevDer
Fri Jul 29 09:35:07 CDT 2005

Hi,

If you are also going to use MS SQL, you do not need to do anything,
because you will have transaction log.
I understand that it may not be in the same format that you've done but it
is already there.

If you will not do this, then I advise you to download
Microsoft.Application.Blocks.Data from microsoft site and then you will be
able to have all the parameters being sent in a better organized way to
database and at execution stage, you can easily manipulate the full
querystring.

By doing the second approach, you will have a more robust and organized way
of using ADO.NET and better performance of couse.

I hope this helps.
If you have more questions please don't hesitate to send me details.
--

SevDer
http://www.sevder.com
A new .NET Source For .NET Developers

"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:uwURWiElFHA.1948@TK2MSFTNGP12.phx.gbl...
> Backgroud:
> My company is currently migrating to .Net from PHP for a number of
> reasons,
> however we have one slight problem we would very much like to solve before
> moving to .Net (ok, many but this one is a toughie).
>
> Problem:
> On our current PHP platform we have our own database abstraction class
> which
> inherits from the PEAR DB abstraction class. One of the major reasons why
> we
> did this is because we wanted to add automatic querie logging to our
> database connection object. Here is a brief snippit of code to demonstarte
> the functionality we currently have:
>
> //Creating DB connection objects
> $connection = mis_DB::connect( 'database-server', 'database' );
> $connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE );
> $connection->setLogMask( MISDB_LOG_ALL );
>
> This will automatically log any queries thrown at at, for example (I'm
> using
> prepare and execute syntax for a reason that will be come clear in just a
> minute):
>
> /* Disclaimer, such a DB does not exist on my company DB server, but it
> should! */
> $query = "SELECT * FROM beer.beer_styles WHERE style = ?";
> $handle = $connection->prepare( $query );
> $result_object = $connection->execute( $handle, array( "Imperial
> Stout" ) );
>
> What our current database class will do (after executing the query) is
> logg
> the following message to the /log_path/filename.log file:
> Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM
> beer.beer_styles WHERE style = 'Imerial Stout'
>
> What we would like is to have some thing similar with our new .Net DB
> class,
> however the problem I'm running into is I don't know how to get the query
> that was actually executed on the server, what I mean by that is:
>
> //Assum proper connection string
> OleDbConnection Connection = new OleDbConnection( ConnectionString );
> OleDbCommand Command = new OleDbCommand( Connection );
> Connection.Open();
>
> Command.CommandText = @"SELECT * FROM beer.beer_styles WHERE style =
> @style";
> Command.Paramaters.Add( "@style", "Imperial Stout" );
> OleDbDataReader Reader = Command.ExecuteReader();
>
> Now that the query has executed, how to I pull the value:
> "SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'"
> out of the database class v.
> "SELECT * FROM beer.beer_styles WHERE style = @style"
>
> Thanks for any help you can provide!
> --------------------
> Carlo Razzeto
> carlo.razzeto@mtginfo.com
>

Jeff
Fri Jul 29 10:22:58 CDT 2005

IMO, I would write a data access abstraction class that could be used
for all database requests in your application(s). I did something
similar for my current project.

I created a class that is supplied the details of each data access
request (connection string, query/stored procedure, parameters, etc).
This class actually handles the creation of the SqlCommand object,
execution of the query, and logs the details of the request. This
allows all data access code to defer handling the SqlCommand object to
the abstraction layer rather than creating one in each location a stored
procedure/query needs to be executed.

Just something to consider.

Jeff Barnes
Microsoft Certified Application Developer

Carlo Razzeto wrote:
> Backgroud:
> My company is currently migrating to .Net from PHP for a number of reasons,
> however we have one slight problem we would very much like to solve before
> moving to .Net (ok, many but this one is a toughie).
>
> Problem:
> On our current PHP platform we have our own database abstraction class which
> inherits from the PEAR DB abstraction class. One of the major reasons why we
> did this is because we wanted to add automatic querie logging to our
> database connection object. Here is a brief snippit of code to demonstarte
> the functionality we currently have:
>
> //Creating DB connection objects
> $connection = mis_DB::connect( 'database-server', 'database' );
> $connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE );
> $connection->setLogMask( MISDB_LOG_ALL );
>
> This will automatically log any queries thrown at at, for example (I'm using
> prepare and execute syntax for a reason that will be come clear in just a
> minute):
>
> /* Disclaimer, such a DB does not exist on my company DB server, but it
> should! */
> $query = "SELECT * FROM beer.beer_styles WHERE style = ?";
> $handle = $connection->prepare( $query );
> $result_object = $connection->execute( $handle, array( "Imperial Stout" ) );
>
> What our current database class will do (after executing the query) is logg
> the following message to the /log_path/filename.log file:
> Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM
> beer.beer_styles WHERE style = 'Imerial Stout'
>
> What we would like is to have some thing similar with our new .Net DB class,
> however the problem I'm running into is I don't know how to get the query
> that was actually executed on the server, what I mean by that is:
>
> //Assum proper connection string
> OleDbConnection Connection = new OleDbConnection( ConnectionString );
> OleDbCommand Command = new OleDbCommand( Connection );
> Connection.Open();
>
> Command.CommandText = @"SELECT * FROM beer.beer_styles WHERE style =
> @style";
> Command.Paramaters.Add( "@style", "Imperial Stout" );
> OleDbDataReader Reader = Command.ExecuteReader();
>
> Now that the query has executed, how to I pull the value:
> "SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'"
> out of the database class v.
> "SELECT * FROM beer.beer_styles WHERE style = @style"
>
> Thanks for any help you can provide!
> --------------------
> Carlo Razzeto
> carlo.razzeto@mtginfo.com
>
>

David
Fri Jul 29 10:25:08 CDT 2005


"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:uwURWiElFHA.1948@TK2MSFTNGP12.phx.gbl...
> Backgroud:
> My company is currently migrating to .Net from PHP for a number of
> reasons,
> however we have one slight problem we would very much like to solve before
> moving to .Net (ok, many but this one is a toughie).
>
> Problem:
> On our current PHP platform we have our own database abstraction class
> which
> inherits from the PEAR DB abstraction class. One of the major reasons why
> we
> did this is because we wanted to add automatic querie logging to our
> database connection object. Here is a brief snippit of code to demonstarte
> the functionality we currently have:
>
> //Creating DB connection objects
> $connection = mis_DB::connect( 'database-server', 'database' );
> $connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE );
> $connection->setLogMask( MISDB_LOG_ALL );
>
> This will automatically log any queries thrown at at, for example (I'm
> using
> prepare and execute syntax for a reason that will be come clear in just a
> minute):
>
> /* Disclaimer, such a DB does not exist on my company DB server, but it
> should! */
> $query = "SELECT * FROM beer.beer_styles WHERE style = ?";
> $handle = $connection->prepare( $query );
> $result_object = $connection->execute( $handle, array( "Imperial
> Stout" ) );
>
> What our current database class will do (after executing the query) is
> logg
> the following message to the /log_path/filename.log file:
> Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM
> beer.beer_styles WHERE style = 'Imerial Stout'
>
> What we would like is to have some thing similar with our new .Net DB
> class,
> however the problem I'm running into is I don't know how to get the query
> that was actually executed on the server, what I mean by that is:
>
> //Assum proper connection string
> OleDbConnection Connection = new OleDbConnection( ConnectionString );
> OleDbCommand Command = new OleDbCommand( Connection );
> Connection.Open();
>
> Command.CommandText = @"SELECT * FROM beer.beer_styles WHERE style =
> @style";
> Command.Paramaters.Add( "@style", "Imperial Stout" );
> OleDbDataReader Reader = Command.ExecuteReader();
>
> Now that the query has executed, how to I pull the value:
> "SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'"
> out of the database class v.
> "SELECT * FROM beer.beer_styles WHERE style = @style"
>

You don't. You just append the parameter values to the query text.

public string DescribeCommand(IDbCommand Command)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(Command.CommandText).Append(", ");
foreach (IDbDataParameter p in Command.Parameters)
{
sb.Append(p. ParameterName).Append(" = [").Append(p.Value).Append("],
");
}
sb.Length = sb.Length - 2;
return sb.ToString();
}

Oh, and Use SqlClient instead of OleDb.

Oh, and do check out the Enterprise Library for data access patterns.
http://msdn.microsoft.com/practices/

David

Carlo
Fri Jul 29 12:06:23 CDT 2005


"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:eJLf1GFlFHA.2860@TK2MSFTNGP15.phx.gbl...
>
> You don't. You just append the parameter values to the query text.
>
> public string DescribeCommand(IDbCommand Command)
> {
> System.Text.StringBuilder sb = new System.Text.StringBuilder();
> sb.Append(Command.CommandText).Append(", ");
> foreach (IDbDataParameter p in Command.Parameters)
> {
> sb.Append(p. ParameterName).Append(" = [").Append(p.Value).Append("],
> ");
> }
> sb.Length = sb.Length - 2;
> return sb.ToString();
> }
>
> Oh, and Use SqlClient instead of OleDb.
>
> Oh, and do check out the Enterprise Library for data access patterns.
> http://msdn.microsoft.com/practices/
>
> David

Thanks for the input David, so my next question is will doing what you
suggested provide the actually executed query? We're actually using MySql
(and the MySql data provider, however my example used OleDb to abstract the
problem) and we need to know what was executed on teh database server after
the DP escaped the query paramaters. Example:

/* For some stupid reason MySql uses ? as opposed to @ for query params */
Command.CommandText = "INSERT INTO test.test SET value = ?datavalue";
Command.Paramaters.Add( "?datavalue", "I'm so crazy" );

Should execute the following query on the MySql server:
"INSERT INTO test.test SET value = 'I\'m so crazy'"

That's what I'm after, the actually executed query so we can log it. From
what I've read so far, it appears that the Enterprise Utilties are geared
more for SqlServer rather than general DB connections, so would they even
work in our situation.

Carlo
Fri Jul 29 12:08:27 CDT 2005

We are creating our own database abstaction class with will actually be
compatible with MySqlConnection, SqlConnection and OleDbConnection (all all
associated objects/methods). We could of course parse the string our selves,
but what we are really looking for is gaining access to *the* query the
lower level objects sent to the database server.

Carlo Razzeto

"Jeff Barnes" <jeff@jeffbarnes.net> wrote in message
news:42EA49D2.3040205@jeffbarnes.net...
> IMO, I would write a data access abstraction class that could be used for
> all database requests in your application(s). I did something similar for
> my current project.
>
> I created a class that is supplied the details of each data access request
> (connection string, query/stored procedure, parameters, etc). This class
> actually handles the creation of the SqlCommand object, execution of the
> query, and logs the details of the request. This allows all data access
> code to defer handling the SqlCommand object to the abstraction layer
> rather than creating one in each location a stored procedure/query needs
> to be executed.
>
> Just something to consider.
>
> Jeff Barnes
> Microsoft Certified Application Developer

David
Fri Jul 29 12:51:14 CDT 2005


"Carlo Razzeto" <crazzeto@hotmail.com> wrote in message
news:ueZqz%23FlFHA.3256@TK2MSFTNGP12.phx.gbl...
>
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
> message news:eJLf1GFlFHA.2860@TK2MSFTNGP15.phx.gbl...
>>
>> You don't. You just append the parameter values to the query text.
>>
>> public string DescribeCommand(IDbCommand Command)
>> {
>> System.Text.StringBuilder sb = new System.Text.StringBuilder();
>> sb.Append(Command.CommandText).Append(", ");
>> foreach (IDbDataParameter p in Command.Parameters)
>> {
>> sb.Append(p. ParameterName).Append(" =
>> [").Append(p.Value).Append("], ");
>> }
>> sb.Length = sb.Length - 2;
>> return sb.ToString();
>> }
>>
>> Oh, and Use SqlClient instead of OleDb.
>>
>> Oh, and do check out the Enterprise Library for data access patterns.
>> http://msdn.microsoft.com/practices/
>>
>> David
>
> Thanks for the input David, so my next question is will doing what you
> suggested provide the actually executed query? We're actually using MySql
> (and the MySql data provider, however my example used OleDb to abstract
> the problem) and we need to know what was executed on teh database server
> after the DP escaped the query paramaters. Example:
>
> /* For some stupid reason MySql uses ? as opposed to @ for query params */
> Command.CommandText = "INSERT INTO test.test SET value = ?datavalue";
> Command.Paramaters.Add( "?datavalue", "I'm so crazy" );
>
> Should execute the following query on the MySql server:
> "INSERT INTO test.test SET value = 'I\'m so crazy'"
>
> That's what I'm after, the actually executed query so we can log it. From
> what I've read so far, it appears that the Enterprise Utilties are geared
> more for SqlServer rather than general DB connections, so would they even
> work in our situation.

They would work if you used MSDE or SQL Server 2005 Express Edition instead
of MySql.

David