Execute a stored procedure multiple times in the same connection
Execute a stored procedure multiple times in the same connection
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();
ExecuteScalar
ExecuteNonQuery
I have to change it to Scalar right to execute a proc
– BenMiner
Sep 8 '18 at 0:23
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 8 '18 at 0:26
ExecuteScalar
DbCommand.CommandType
StoredProcedure
Check here for more info: stackoverflow.com/questions/21058970/…
– Parrish Husband
Sep 8 '18 at 0: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 8 '18 at 0:33
1 Answer
1
This uses the same DbConnection, and DbCommand, but iterates through the records:
DbConnection
DbCommand
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.
SQLBulkCopy
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.
– BenMiner
Sep 8 '18 at 1: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 8 '18 at 1:10
DbConnection
DbCommand
DbParameter
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
– BenMiner
Sep 8 '18 at 1:17
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 8 '18 at 1:30
Yeah, same here. Thats what he suggested so I was doubtful that can be done
– BenMiner
Sep 8 '18 at 1:34
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Your title/tag is about
ExecuteScalarbut your code is doingExecuteNonQuery. What exactly are you trying to do here?– Parrish Husband
Sep 8 '18 at 0:19