C# Store Unknown Amount of Elements in SQL Table

Question

I am currently reading and parsing 10 different file(.txt, .csv), each file has a different number of columns.

Here is a sample from one file:

SqlCommand cmd = new SqlCommand("insert into " + table_name + " VALUES('"+data_from_file[0]+"', '" + data_from_file[1] + "', '" + data_from_file[2] + "', '" + data_from_file[3] + "', '" + data_from_file[4] + "','" + data_from_file[5] + "', '" + data_from_file[6] + "', '" + data_from_file[7] + "', '" + data_from_file[8] + "', '" + data_from_file[9] + "');", connection);
cmd.ExecuteNonQuery();

This file has a total of 10 columns that gets inserted into its own table. If I do it like this for the 10 files, I would have 10 different if/else if statements. And to me that sounds like bad way of doing this. Is there a way to iterate through the array and insert each element? I have been looking for ways to do it, but cannot find a proper solution for my problem. Thanks for the help.

http://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server — Tim Schmelter, Jun 21 '16 at 13:17
Are you able to integrate Entity Framework into this project? for a little bit of extra leg work, EF would make things look a whole lot cleaner.. You could create seperate classes for the 10 different types you have, then create a file reader class of some sort to create arrays of these new entities and let EF Data Context take care of the inserting for you. — uk2k05, Jun 21 '16 at 13:24

Zein Makki · Accepted Answer · 2016-06-21T13:32:31.123

1

The most classical way would be to iterate and append to a string and then execute:

string command = "insert into " + table_name + " VALUES(";

foreach(string data in data_from_file)
{
    command += "'" + data + "',";
}
command = command.TrimEnd(','); // remove the last extra ','
command += ");";

SqlCommand cmd = new SqlCommand(command);
cmd.ExecuteNonQuery();

However, I recommend that you have a look at SqlBulkCopy for Large Insert Queries.

edited Jun 21 '16 at 13:32

answered Jun 21 '16 at 13:25

Zein Makki

29,485
6
52
63

Thank you very much fo the help. Your solution worked perfectly! – D3athWish Jun 21 '16 at 14:06

score 0 · Answer 2 · answered Jun 21 '16 at 13:20

First you iterate over your collection, and then you insert every entry from the collection into the database.

            foreach (var oneColumn in data)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO " + table_name + " VALUES(" + oneColumn + ")");
            cmd.ExecuteNonQuery();
        }

C# Store Unknown Amount of Elements in SQL Table

2 Answers2

However, I recommend that you have a look at SqlBulkCopy for Large Insert Queries.