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
– ArchiKu, Commented Jun 22, 2020 at 11:43
I don't see any attempt at "trying to get the MySQL query text ... with parameters". What have you tried? — Crowcoder
– Crowcoder, Commented Jun 22, 2020 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
– ArchiKu, Commented Jun 22, 2020 at 11:44
Does this answer your question? How can I see the command string when using MySqlCommand parameters? — Crowcoder
– Crowcoder, Commented Jun 22, 2020 at 12:13

Crowcoder · Accepted Answer · 2020-06-22 11:59:45Z

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, 2020 at 11:59

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

ArchiKu Over a year ago

This is an interesting solution. Thank you for him! It can also be written in linq and written on one line!

Vivek Nuna Over a year ago

Same code you can find here stackoverflow.com/a/20205594/6527049

Crowcoder Over a year ago

@viveknuna indeed you are right. You can vote to close as duplicate.

Vivek Nuna Over a year ago

@Crowcoder. no, this is leading to a bad programming practise

Legacy Code Over a year ago

This will fail with parameters that requires quotes :) .. Like varchar

|

barath · Accepted Answer · 2020-06-22 13:41:10Z

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,

barath · Accepted Answer · 2020-06-22 14:16:50Z

-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, 2020 at 14:16

answered Jun 22, 2020 at 13:58

barath

13 bronze badges

2 Comments

Crowcoder Over a year ago

The question is about logging the sql command, not how to run a sql command.

barath Over a year ago

I am updating my answer. Kindly refer for your kind information

Collectives™ on Stack Overflow

How to get query with parameters form MySqlCommand

3 Answers 3

11 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related