Passing Array or DataSet from C# to Sql Server as parameter

Hi:

I have an application in which I generate either an array or a dataset from answers to a test (usually about 35 in length)

Currently, I make 35 trips to the database with code something like

 string[,] scorray = new string[100, 5];
PopulateScorray();
bool Success = false;
for (int i = 0; i <= scorray.GetUpperBound(0); i++)
{
Success = AddToDb(scorray[i,0], scorray[i,1];
}

Open in new window


Is there anyway I could send the array as a dataset to SQL Sever and then use that dataset/table as a parameter which would be inserted into the the table at the SQL server end?

If so, what would the C# code look like, what would the T-SQL look like?  Would it be much faste?

Any help appreciated.

RBS
RBSAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
if you are using SQL 2008 or better, you can use TVP (table value parameter) as shown in http://emoreau.com/Entries/Articles/2010/11/SQL-Table-Value-parameters.aspx

If you are still using SQL 2005, check http://emoreau.com/Entries/Blogs/2011/10/SQL-TVP-workaround-for-SQL-2005.aspx
0
 
RBSAuthor Commented:
Great emoreau - thanks - I have 2008 - excellent article!

RBS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.