• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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


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];
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.

1 Solution
É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
RBSAuthor Commented:
Great emoreau - thanks - I have 2008 - excellent article!


Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now