Avatar of websss
Flag for Kenya asked on

insert multiple records to SQL table - using SP and c#


I have the following  code

  string result = "";
            SqlParameter[] param = new SqlParameter[2];

                param[0] = new SqlParameter("@vSequenceID", SqlDbType.BigInt);
                param[0].Value = this.vSequenceID;

                param[1] = new SqlParameter("@vpkDeviceID", SqlDbType.VarChar, 50);
                param[1].Value = this.Deviceid;
                ATPLDAL.SqlHelper.ExecuteNonQuery(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], CommandType.StoredProcedure, "sp_SaveAlertsCaptured", param);


Open in new window

Its not currently working... i'm wondering how i pass in multiple rows to be inserted?
currently the logic works one call and one row insert
but now i need to insert upto 10 rows with one call and efficiently as possible as it will be executed 100 times a second

can someone help me in the correct direction
C#Microsoft SQL ServerASP.NET

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

the answer is: it depends on what exactly the 10 rows to be inserted are referring to?
for a stored procedure, you only have 2 options:
* call the stored procedure 10 times indeed with the different values
* recode the stored procedure to split the parameter values into the x rows

the 2nd option is not recommended for several reasons, but may be an option still for several scenarios, and you might get inspired by this article: https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

Thanks Guy

Each insert will have these 3 items:
- ifkAlertId
- ifkCommonId
- iSoftwarePriority

The first 2 are refering to ID's in other tables
the 3rd one will either be a "1" "2" or "3" value

actually it will mostly be between 1 to 5 rows being inserted
but this SP will eventually be called very often (could be 100 of times a sec) - it will need to scale to this frequency at least!

I figured it would make more sense to insert all records in one go (i.e. 100 times a second instead of 500 times a second if there are 5 rows needing to be inserted on each of the 100)

what do you suggest given the above info?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Guy Hengel [angelIII / a3]

quite some preparation work to be done, but in your case this can and should be done to win as much time as possible.

Thanks Guy
Based on this article

if you scroll down to graph, it says you need at least 4 inserts to get the performance increase

I will know how many i need to insert before hand, so if its 4 or above, I could use TVP's and if its below 4 I could use separate inserts

Do you think this is a good approach?
Guy Hengel [angelIII / a3]

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.