How Can I Execute Multiple MySQL Queries At Once In C#?

Question

So I have a really really really big pre-compiled MySQL Query Set. This query executes perfectly fine outside of my C# Application, but when I try to execute it in my C# application I run into a problem. Please note I can NOT modify the query itself nor can I recompile it in code, I need it to execute as is.

The query itself is comprised of a multitude of different queries that follow the following pattern:

DELETE FROM stores WHERE region_id = 1;
INSERT INTO stores (region_id, name, score, type) VALUES (17, '1', 0, 'Profit'),(17, '2', 0, 'Profit'),(17, '3', 0, 'Profit'),(17, '4', 0, 'Profit'),(17, '5', 0, 'Profit'),(17, '6', 0, 'NonProfit'),(17, '7', 0, 'NonProfit'),(17, '8', 0, 'NonProfit'),(17, '9', 0, 'NonProfit'),(17, '10', 0, 'NonProfit');
SELECT id INTO @store_id0 FROM stores WHERE region_id=17 AND name = '1' AND type = 'Profit';
INSERT INTO store_merch(store_id, item_id, stock) VALUES (@store_id0, 1360, 0),(@store_id0, 440, 5);

This works as expected when I execute it on something like navicat (database management tool), but with my C# program it generates the following exception:

Parameter '@store_id0' must be defined.

Here's my Query Execute function...

  public bool ExecuteQuery(
     string raw_query)
  {
     if (this.Open() == true)
     {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(raw_query, connection);

        try
        {
           int changed_rows = cmd.ExecuteNonQuery();
           if (changed_rows > 0)
           {
              this.Close();
              return true;
           }
           else
           {
              Logger.GetInstance().Error("MySQLConnection::ExecuteQuery", "Zero records changed for in raw query.");
           }
        }
        catch (MySqlException e)
        {
           Logger.GetInstance().Error("MySQLConnection::ExecuteQuery", e.Message);
        }

        this.Close();
     }

     return false;
  }

Anyone know what the problem is?

I don't know the C# database API well, but in Java it's not possible to mix DML and non DML statements with the same API. — Tim Biegeleisen, Apr 14 '18 at 12:12
Is it possible you just need to set [Allow User Variables in your connection string](https://stackoverflow.com/a/5530620/300836)? I can also [see some advice](https://stackoverflow.com/a/986766/300836) saying you may need to update your MySQL data provider; newer versions use question marks instead of @variables to avoid this clash between user variables and parameters. — Matt Gibson, Apr 14 '18 at 12:19
@MattGibson PERFECT, thank you! The Allow User Variables worked! Thanks thanks! — Rick, Apr 14 '18 at 12:30
@Rick No problem. I'm going to mark this question as a duplicate of that earlier one (this is not a [bad thing](https://stackoverflow.blog/2010/11/16/dr-strangedupe-or-how-i-learned-to-stop-worrying-and-love-duplication/)!) — Matt Gibson, Apr 14 '18 at 14:55
Possible duplicate of [Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?](https://stackoverflow.com/questions/958953/is-it-possible-to-use-a-mysql-user-defined-variable-in-a-net-mysqlcommand) — Matt Gibson, Apr 14 '18 at 14:56

score -1 · Answer 1 · answered Apr 14 '18 at 13:11

//You can use StringBuilder and Write more than Queries

StringBuilder strb = new StringBuilder(); strb.Append(" SELECT IncentiveCode as Code, IncentiveDescription as PoliceyName, ");

            strb.Append(" IncPeriodCode as IncetvePeriodCode, PolP.PoliceyPeriodDesc as IncetivePeriodName,");

            strb.Append(" EffectedDate ,EndDate ,pol.SuppCode as SuppCode ,comp.SuppName as SuppName,pol.IsActive from IncentivePolicey pol");

            strb.Append(" LEFT outer join IncentivePoliceyPeriod PolP");
            strb.Append(" ON pol.IncPeriodCode = PolP.PoliceyPeriodCode");

            strb.Append(" LEFT OUTER JOIN Inventory_Suppliers comp ON pol.SuppCode = comp.SuppCode");

sqlcommandText=strb.toString();

strb is a Object of StringBuilder – jameel Apr 14 '18 at 13:12 — jameel, Apr 14 '18 at 13:12
Like StringBuilder strb=new StringBuilder(); – jameel Apr 14 '18 at 13:12 — jameel, Apr 14 '18 at 13:12

score -2 · Answer 2 · answered Apr 14 '18 at 12:11

-2

For each variable with the pattern @name you must create a parameter and provide its value:

cmd.Parameters.AddWithValue("@store_id0", 123);

answered Apr 14 '18 at 12:11

Rubens Farias

57,174
8
131
162

That's what the SELECT id INTO @store_id0 FROM stores WHERE region_id=17 AND name = '1' AND type = 'Profit'; query is for, this should be doable via strictly MySQL as it runs perfectly fine everywhere else. – Rick Apr 14 '18 at 12:12
2

In this context the value assigned at @store_id0 is meaningless. However, if you have a parameter in your query you should still add the matching one in the parameters collection – Steve Apr 14 '18 at 12:16
Can we please [stop suggesting `AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/)? – DavidG Apr 14 '18 at 12:41
@DavidG, by looking OP question, we can't know for sure what is that field type. Feel free to edit this answer and provide an example with `Parameters.Add` – Rubens Farias Apr 14 '18 at 14:15
1

It doesn't matter what the type is, it's a method that shouldn't be used at all. – DavidG Apr 14 '18 at 14:18
In the _concrete_ case, is it an integer, double, decimal? How would be that statement? – Rubens Farias Apr 14 '18 at 14:30
Sorry, I don't understand what you mean with that statement. However, I'm saying that you should *never* recommend a bad technique, even if it works well in one situation. It promotes bad habits in future. – DavidG Apr 14 '18 at 21:44

How Can I Execute Multiple MySQL Queries At Once In C#?

2 Answers2