CODEWITHSUNDEEP
c#mysql
Jacob Lambert
Jacob Lambert

Reputation: 7679

Insert multiple entries in parameterized query

I'm trying to do a multiple insert query using a parameterized command but I am getting a syntax exception thrown. The query in sql would look like:

CREATE TEMPORARY TABLE IF NOT EXISTS TEMP_PTO ( 
    ng_id INT, 
    requested_hours DECIMAL(7,4)
);

INSERT INTO TEMP_PTO VALUES
(1, 0.0000), (2, 1.5000);

I generate it in C# via the following where ptoHours is an IEnumerable of a class containing the ng_id and the requested_hours. Queries is an resx file containing the query text, and Queries.PTOLoadTempCommand = INSERT INTO TEMP_PTO VALUES @v;:

        using (var conn = new MySqlConnection(this.connString))
        {
            MySqlTransaction trans = null;
            try
            {
                await conn.OpenAsync();
                trans = conn.BeginTransaction();

                using (var tempCommand = conn.CreateCommand())
                {
                    tempCommand.CommandText = Queries.PTOTempTableCommand;
                    await tempCommand.ExecuteNonQueryAsync();
                }

                using (var loadCommand = conn.CreateCommand())
                {
                    loadCommand.CommandText = Queries.PTOLoadTempCommand; //INSERT INTO TEMP_PTO VALUES @v;
                    loadCommand.Parameters.AddWithValue("@v", String.Join(", " ,ptoHours.Select(p => String.Format("({0}, {1:N4})", p.AgentId, p.UsedVacationHours))));
                    //Exception thrown here
                    var affected = await loadCommand.ExecuteNonQueryAsync();
                    if (affected != entryCount)
                        throw new Exception("Not All Entries Loaded");
                }

                trans.Commit();
            }
            catch (Exception e)
            {
                if(trans!= null) trans.Rollback();
            }
        }

Looking at the loadCommand object, I can see that the value of the parameter @v is (1, 0.0000), (2, 1.5000). Are you able to do an insert like this or do I need to modify the function to insert them one at a time? I know I could go the StringBuilder route, but then I cannot use the safety of the parameterization.

Upvotes: 0

Views: 1009

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

You will have to do it one at a time. The query parameters are parsed and it is not allowed to have a parameter in that position.

There shouldn't be a need to insert rows in one command, if you need both to succeed or fail, just use transactions.

And as you said, using a StringBuilder or other string catenations is not smart.

Upvotes: 1

Related Questions