How to get query with parameters form MySqlCommand

Question

I am trying to get the MySQL query text that was generated with the parameters, but so far I have not succeeded.

string query = $"UPDATE table_inputs SET amount = @amount WHERE id = @id";
using (MySqlCommand command = new MySqlCommand(query, connection))
{
    command.CommandType = CommandType.Text;
    command.Prepare();

    command.Parameters.AddWithValue("@id", user_id);
    command.Parameters.AddWithValue("@amount", user_amount);
    
    var result = command.ExecuteScalar();   
    // always 'result' equal NULL 
}

The request text is required for logging. I plan to write logs from a multi-threaded application where each operation should have its own unique parameters. Consequently, queries with parameters are needed where it was clear what query and with what parameters was performed. Please tell me what I'm doing wrong and how to do it right.

I use ExecuteNonQuery () in some places, but how do I get the query text with the substituted parameters? — ArchiKu, Jun 22 '20 at 11:43
I don't see any attempt at "*trying to get the MySQL query text ... with parameters*". What have you tried? — Crowcoder, Jun 22 '20 at 11:43
As a result, for this example, I would like to receive a query like: "UPDATE table_inputs SET amount = 100 WHERE id = 45215647" — ArchiKu, Jun 22 '20 at 11:44
@ArchiKu That query doesn't exists in this form, by design. When you use prepared statements the query and the data for it are separated. — Progman, Jun 22 '20 at 11:46
@Progman Tell me how can I make a request with adding parameters in another way? (so that later you can get the correct request body. When adding parameters I use special characters, so i have to do parameters.add (...)) — ArchiKu, Jun 22 '20 at 11:52
@Crowcoder I read an article where it was written that the query text should return after executing from ExecuteScalar () and trying to get the text into the variable 'result'. Perhaps this is a mistake, please tell me how to get a request with parameters ?! — ArchiKu, Jun 22 '20 at 11:59
@ArchiKu I don't know what article you have read but the query text is not returned after `ExecuteScalar`. What that does is return the first column of the first row of the first result set. See my answer below, I think it is what you are looking for? — Crowcoder, Jun 22 '20 at 12:03
@ArchiKu Is it an option to use MySQL server side logging as described in https://stackoverflow.com/questions/303994/log-all-queries-in-mysql/14403905#14403905? — Progman, Jun 22 '20 at 12:06
@Progman Unfortunately no. The server is not mine, they only give me access to several operations with the table. — ArchiKu, Jun 22 '20 at 12:09
Does this answer your question? [How can I see the command string when using MySqlCommand parameters?](https://stackoverflow.com/questions/20205550/how-can-i-see-the-command-string-when-using-mysqlcommand-parameters) — Crowcoder, Jun 22 '20 at 12:13
Actually there is no replacement of parameters happening. In your case the following is sent to the db server -> DECLARE _@amount_ Int DECLARE _@id_ nvarchar SET _@amount_ = '435454' SET _@id_ = 'the_user' UPDATE table_inputs SET amount = _@amount_ WHERE id = _@id_ ... — Legacy Code, Jun 22 '20 at 12:36
@LegacyCode while one might rightly argue the merits of `AddWithValue`, that code will correctly set parameters, I'm not sure what you are talking about regarding "*there is no replacement of parameters..*" — Crowcoder, Jun 22 '20 at 13:00
@Crowcoder - The point of using parameters is SQL injection prevention. The CommandText itself is not replaced with the values you are passing to SqlParameters. That would kill the point. Instead a DECLARE and SET command is added. — Legacy Code, Jun 22 '20 at 13:14
@Crowcoder the link above uses SqlParameter, with work it looks a little different than with MySqlParameter — ArchiKu, Jun 22 '20 at 13:23
@LegacyCode I know how parameters work. And sql injection is not the only point. I don't understand what you are trying to say. That code is how it is done. The provider takes care of getting the proper command to the database. You don't need to write any DECLARE or SET yourself. — Crowcoder, Jun 22 '20 at 13:24
@ArchiKu it is basically the same. You can use IDbCommand, IDbParameter, etc., if you want to make it more generic. — Crowcoder, Jun 22 '20 at 13:25
@Crowcoder I know... I am just trying to say if you want to "extract" the correct executable command... If... Then you have to do it that way. — Legacy Code, Jun 22 '20 at 13:49
@LegacyCode I understand what you are saying now. I wouldn't expect that to be necessary if you just want to log the queries that occurred. The sql string should be enough to determine what action was taken but that is an assumption on my part. — Crowcoder, Jun 22 '20 at 14:03
@Crowcoder I misunderstand your question in the first place and assumed you want to use the logged sql statements e.g. for checking the result directly in the db. — Legacy Code, Jun 22 '20 at 14:11
@LegacyCode You wouldn't want to run DML statements again. Even for SELECT you have to assume the result set could have changed between when the command was run and when you execute the logged query. — Crowcoder, Jun 22 '20 at 14:21

score 0 · Accepted Answer · answered Jun 22 '20 at 11:59

0

This is not tested and not complete, consider it more psuedo code to give you an idea.

You could write a utility method that takes a command, replaces the parameter placeholders with parameter values, then logs the query.

public static void LogCommand(MySqlCommand cmd)
{   
    string cmdText = cmd.CommandText;
    
    foreach(MySqlParameter p in cmd.Parameters)
    {
        cmdText = cmdText.Replace(p.ParameterName, p.Value);
    }

    Log(cmdText); // <-- or however you are logging....
}

answered Jun 22 '20 at 11:59

Crowcoder

11,250
3
36
45

This is an interesting solution. Thank you for him! It can also be written in linq and written on one line! – ArchiKu Jun 22 '20 at 12:06
Same code you can find here https://stackoverflow.com/a/20205594/6527049 – Vivek Nuna Jun 22 '20 at 12:11
@viveknuna indeed you are right. You can vote to close as duplicate. – Crowcoder Jun 22 '20 at 12:13
1

@Crowcoder. no, this is leading to a bad programming practise – Vivek Nuna Jun 22 '20 at 12:14
@viveknuna because it is not a stored procedure? I also prefer stored procedures but you cannot make a blanket statement that having SQL in code is bad programming practice. – Crowcoder Jun 22 '20 at 12:18
@Crowcoder please provide me an example if it benefits? – Vivek Nuna Jun 22 '20 at 12:20
@viveknuna Stored procedures require more overhead regarding database maintenance. Using them generally requires a code deployment and a database deployment. That's just one example. Do you also have a problem with ORMs like Entity Framework? You know they generate sql right? – Crowcoder Jun 22 '20 at 12:22
@Crowcoder But in this case, you cannot deny that we should not use SP – Vivek Nuna Jun 22 '20 at 12:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/216426/discussion-between-crowcoder-and-vivek-nuna). – Crowcoder Jun 22 '20 at 12:24
This will fail with parameters that requires quotes :) .. Like varchar – Legacy Code Jun 22 '20 at 12:45
@LegacyCode I do not expect the query that is logged to be executed, that was a base assumption on my part. The logged value will show the intent of the query but you are right, if someone is going to try to execute the logged value it will not work unmodified. – Crowcoder Jun 22 '20 at 12:54

barath · Answer 2 · 2020-06-22T13:41:10.870

Kindly try this below code its working...,

Step 1: Declare the string value.

Step 2: Then Read to the ! Operator method ().

Step 3: After if a condition should be retrieved the data.

string val;
string query = $"UPDATE table_inputs SET amount = @amount WHERE id = @id";
using (MySqlCommand command = new MySqlCommand(query, connection))
  {
command.CommandType = CommandType.Text;
command.Prepare();

command.Parameters.AddWithValue("@id", user_id);
command.Parameters.AddWithValue("@amount", user_amount);

var result = command.ExecuteNonQuer();
// always 'result' equal NULL 
       
} 
 
 string qur = $"select * from table_inputs where amount = @amount and id = @id";
 using (MySqlCommand command = new MySqlCommand(qur, connection))
  {
        command.CommandType = CommandType.Text;
        command.Prepare();

        command.Parameters.AddWithValue("@id", user_id);
        command.Parameters.AddWithValue("@amount", user_amount);

   var result = command.ExecuteReader();  
   if(!result.Read())
   {
       Console.Write("Record not found ");  
   }
   else
   {
        string userid = Convert.ToString(val);  
        userid = dr["@id"].ToString();  
        Console.WriteLine("Your ID is: {@id}", userid.ToString());  
        Console.ReadLine();  
        
        string amt = Convert.ToString(val);  
        amd = dr["@amount"].ToString();  
        Console.WriteLine("Your Amount is: {@amount}", amount.ToString());  
        Console.ReadLine();  
   }
  }

It's code not working. ExecuteNonQuer() returned int. Int does not contain a definition for read, — ArchiKu, Jun 22 '20 at 13:35

barath · Answer 3 · 2020-06-22T14:16:50.007

-1

Try another option use Store Procedure:

 //----SQL Store Procedure----
 CREATE PROCEDURE  Procedurename @Id integer(), @amount number()  
 AS    
 UPDATE table_inputs SET amount = @amount WHERE id = @id   
 go

//-----C# code-----
using (MySqlCommand command = new MySqlCommand("Procedurename", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id", user_id);
command.Parameters.AddWithValue("@amount", user_amount);
command.ExecuteNonQuer();
}

edited Jun 22 '20 at 14:16

answered Jun 22 '20 at 13:58

barath

1
3

The question is about logging the sql command, not how to run a sql command. – Crowcoder Jun 22 '20 at 13:59
I am updating my answer. Kindly refer for your kind information – barath Jun 22 '20 at 14:22

How to get query with parameters form MySqlCommand

3 Answers3