Loading DataTable into SP

Question

I have SQL Server 2008 and VS 2008 Pro. And I am coding in C#. I accept a text input file from the customer and I parse this data into a DataTable in my C# aspx.cs file. I know that this is working correctly. So I populated the DataTable. But now how do I load this data into an SP? Like can I use a dynamic table parameter? Or should I use XML instead?

The problem is that the number of required columns may vary depending on which table they want to insert into. The details are I let the user select which table they want to append data to. For simplicity, let's say: TABLE_NAME NUM_COLS A 2 B 3 C 4

And also let's assume that the first column in each of these is an INT primary key. So if they choose Table B, then DataTable would look something like: PK C1 C2 C3 1 'd' 'e' '3/10/99' 2 'g' 'h' '4/10/99'

So now I want to append this data above into Table B in my AdventureWorks DB. What is the easiest way to implement this both in the SP definition and also the C# code which calls this SP? Thank you!

Answer 1

I think I understand what you're asking. I'm going to assume each row of your data import will map directly/cleanly to a table in the database. I am also going to assume your application logic can determine where each row of data shall be persisted.

This said, I suggest working with each row of the .NET DataTable individually rather than passing the data in bulk to SQL as a single stored procedure parameter and then depending on SQL to do any data parsing and table mapping.

Basically, loop through your DataTable, determine the type of data and execute the appropriate insert for each row. I hope this helps.