Problem using a Stored Proc to read data from a .CSV file to a database

Using SQL server 2008 and C# -

So I am trying to implement the feature for a user to import a .CSV file which then I call a Stored Proc to write the contents of that file to a temp table which then gets merged to the actual table.

My question/problem is that I am trying to determine the layout of the .xls file that the customer will need to have and supply them the template .xls file for them to use. The data is a matrix of shipping rates based on zones. Therefore the column and row headers are duplicated:

 -  A    B    C
 A  1    2    3

 B  2    1    4

 C  3    4    1

But I am not sure how to tackle this through a stored procedure. Therefore the other options I had was setting up the .xls template as:

From  To   Rate

 A    A     1
 A    B     2
 A    C     3
 B    A     2
 B    B     1
 .....

You get the picture. Doing it this second way would be fairly straight forward and I have a stored proc that would allow me to traverse the data.

However, I am interested in the problem of the first example and figuring out how you would do开发者_StackOverflow中文版 that. Any suggestions?

There are several ways to accomplish this.

A simple way is to simply iterate the rows and columns (2 loops) and pass those values to a proc for storage.

Something like (psuedo code):

foreach(DataRow row in myDataTable.Rows) {
  foreach(DataColumn col in row.Columns) {
    String value = row[col].Value
    StoreValue(row.Name, col.Name, value); // <-- this would call your proc.
  }
}

If you have SQL 2008, then you could use a table value parameter and pass in the entire table to the stored procedure. From there you could do your inserts.