Execute a stored procedure multiple times in the same connection

Question

Is this even possible with the command object? I need to change this piece or code to run a proc (sp_insertRecord) multiple times for the set of Parameters (created from the Records List object) in the same connection based. Right now it creates comma separated insert script and executes all at once. Team Leader suggested call the stored procedure and parameters with semicolon separating them at once. How do I do this as the Leader adviced instead of calling everything in the loop that executes for each set of the Records object. Thats the only way I can think.

sql.Append(first.GetSqlInsertStatment());
Records.ForEach(a => sql.Append(CreateInsertValuesCommaSeperated()));

sql.Length -= 2;
using (var connection = GetDbConnection())
using (var cmd = connection.CreateCommand())
{     
    cmd.Connection.ChangeDatabase(schema);
    cmd.CommandText = sql.ToString();
    cmd.ExecuteNonQuery();          
}

Your title/tag is about `ExecuteScalar` but your code is doing `ExecuteNonQuery`. What exactly are you trying to do here? — Parrish Husband, Sep 08 '18 at 00:19
`ExecuteScalar` is for returning a single value. If you're not interested in returning anything, you can run a stored procedure by changing the `DbCommand.CommandType` to `StoredProcedure`. — Parrish Husband, Sep 08 '18 at 00:26
Check here for more info: https://stackoverflow.com/questions/21058970/when-to-use-executescalar-executereader-executenonquery — Parrish Husband, Sep 08 '18 at 00:28
You don't have a proc but a regular query. Yes you can create multiple insert queries and then call execute on the query. It will execute all the inserts. — CodingYoshi, Sep 08 '18 at 00:33
I know I have a regular query now, I need to change it to run a proc multiple times in the same connection based on the Records list — Southern Coder Chic, Sep 08 '18 at 00:39
You can reuse the command after it finishes, just change the `CommandText` to the next one you want to run. — Parrish Husband, Sep 08 '18 at 00:48
Yes, which will be built according to the Records List, those are the set of params the Insert is currently sending comma separated. — Southern Coder Chic, Sep 08 '18 at 01:02

Parrish Husband · Answer 1 · 2018-09-08T01:30:49.603

1

This uses the same DbConnection, and DbCommand, but iterates through the records:

using (var connection = GetDbConnection())
using (var cmd = connection.CreateCommand())
{     
    cmd.Connection.ChangeDatabase(schema);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_insertRecord";

    cmd.Parameters.Add("@stringVal", SqlDbType.NVarChar);
    cmd.Parameters.Add("@numVal", SqlDbType.Int);
    cmd.Parameters.Add("@dateVal", SqlDbType.DateTime);

    int insertCount = 0;
    foreach(var record in records)
    {
        cmd.Parameters["@stringVal"].Value = record["stringVal"];
        cmd.Parameters["@numVal"].Value = record["numVal"];
        cmd.Parameters["@dateVal"].Value = record["dateVal"];

        insertCount += cmd.ExecuteNonQuery();
    }        
}

I'm not sure what class type your records are, but you get the idea. If you have the ability to use SQLBulkCopy and not stored procedures, that's likely the fastest option.

edited Sep 08 '18 at 01:30

answered Sep 08 '18 at 01:05

Parrish Husband

3,148
18
40

I get the idea! idea is what I need. Thanks. I'm going to try that. I just need something to replace it and not impact the performance calling it one by one. – Southern Coder Chic Sep 08 '18 at 01:08
You should be able to load fairly fast since you aren't recreating the `DbConnection`, `DbCommand`, and `DbParameter` objects for every record, but it will be highly dependent on the procedure, the data you're loading, and the server. – Parrish Husband Sep 08 '18 at 01:10
Yeah its not recreating the connection or command but its calling ExecuteNonQuery each time in the loop right ? He suggested it can be called once sending semicolon separated execution scripts. I am not sure thats possible – Southern Coder Chic Sep 08 '18 at 01:17
1

I'd be surprised if the semicolon delimited version you're suggesting executed much faster than the above. I've seen stored procedures that can load multiple records, but never implemented in C# with parameterization. – Parrish Husband Sep 08 '18 at 01:30
Yeah, same here. Thats what he suggested so I was doubtful that can be done – Southern Coder Chic Sep 08 '18 at 01:34
Yes I believe in this code I was using `SqlCommand`. Therefore if you're working just with `DbCommand` or `IDbCommand` (which is better for provider agnostic code), you'll only have `cmd.CreateParameter`. Either write a few extension methods that give you some good overloads for creating parameters, or just write it out the long way. – Parrish Husband Sep 09 '18 at 22:45

Execute a stored procedure multiple times in the same connection

1 Answers1