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

Hi

I have the following  code

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


                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
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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: http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
websssCEOAuthor Commented:
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?
websssCEOAuthor Commented:
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Guy Hengel [angelIII / a3]Billing EngineerCommented:
quite some preparation work to be done, but in your case this can and should be done to win as much time as possible.
websssCEOAuthor Commented:
Thanks Guy
Based on this article
http://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/

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]Billing EngineerCommented:
if you had 1-3 or 1-4 inserts, you still can use that technique, as the overhead is not that big, and having to support 2 versions of the procedure is not what I would recommend.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.