Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

View SQL statment to be executed in Dapper

I will like to know if is possible to see what the SQL statement actully looks after the parameters have been parsed in to the SQL string. For example given the following code:

const string SQL = @"
SELECT *
   FROM V_PROVIDER_WAGE_OVER_REVENUE
WHERE SITE_ID = :SITE_ID 
   AND VENDOR_ID = :VENDOR_ID
   AND date_paid >= :DATE_FROM 
   AND date_paid <= :DATE_TO;";

conn.Query<ProviderWageOverRevenueModel>(SQL, new { 
     VENDOR_ID = vendorId, 
     SITE_ID = siteId, 
     DATE_FROM = dateFrom, 
     DATE_TO = dateTo 
}).ToList();

I will like to know if at some point (maybe inside Dapper source?) can I see the something like:

SELECT *
   FROM V_PROVIDER_WAGE_OVER_REVENUE
WHERE SITE_ID = 199
   AND VENDOR_ID = 17
   AND date_paid >= '01/01/2014' 
   AND date_paid <= '20/11/2014';

Edit

I was assuming that the parameters placeholders will be replaced with the values, however according after looking closely at the source of Dapper I realize that its just relaying on a iDbCommand to executed the query, so after looking at this question It is possible to see executed statement inside .Net?

I try this:

string query = cmd.CommandText;
foreach (DbParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

however the result is not seem to be like I hope:

SELECT *
    FROM V_PROVIDER_WAGE_OVER_REVENUE
WHERE 23411127316 = :23411127316 
    AND 23411076816 = :23411076816
    AND date_paid >= :1/07/2014 10:11:34 PM 
    AND date_paid <= :12/12/2014 12:00:00 AM;
like image 722
dnlgmzddr Avatar asked Dec 12 '14 22:12

dnlgmzddr


People also ask

How do I display SQL statements?

The DISPLAY command must be placed immediately after the query statement on which you want it to take effect. For example: SELECT pno, pname FROM part WHERE color='BLUE'; DISPLAY; When the system encounters this DISPLAY command, it displays the Result window containing the part number and name for all blue parts.

How SQL statement is executed?

In order to execute an SQL statement, you must first prepare the SQL statement. During preparation, the database will usually precompile the SQL statement and creates an access plan for the statement. The access plan is kept as long as the statement exists. You can then execute the statement as many times as you want.

Does dapper handle SQL injection?

Dapper has great performance because it doesn't translate queries that we write in . NET to SQL. It is important to know that Dapper is SQL Injection safe because we can use parameterized queries, and that's something we should always do. One more important thing is that Dapper supports multiple database providers.


1 Answers

In terms of the fundamental question: the easiest way to do this is with a tool like mini-profiler and a wrapped connection.

Dapper does have some query manipulation things - but actually: not in the case shown. So the simple answer is that the SQL executed is exactly the contents of SQL.

Most ADO.NET providers support parameters all the way down to the database. If your provider does not, then it won't be visible except via your database's own monitoring tools.

like image 108
Marc Gravell Avatar answered Oct 13 '22 04:10

Marc Gravell