How can I add data from DataTable to database table directly?

Question

How can I add data from a DataTable to a database table directly?

I have searched on the internet not being able to get information from any site.

I have a DataTable and now I want to add that data to a database table.

 importData.Tables[1];
 for(int r = 0; r< totalrecoreds; r++;)
 {
         Array test[] =  importData.Tables[1].Rows[r].ItemArray.ToArray;
 }

What can I do? Do I have to add data one by one using for loop or is there any other method?

Please consider giving more specific information about what DBMS you are using, what is behind `importData` etc. Short answer is No, you certainly have different ways to avoid looping like that using an ORM, Linq, and other options — Mehdi LAMRANI, Feb 17 '12 at 10:39

score 3 · Answer 1 · answered Feb 17 '12 at 10:52

Provided that the schema of the DataTable is the same as the schema of the database table you can just use a DataAdapter to insert the data.

using(var connection = new SqlConnection(...))
using(var adapter = new SqlDataAdapter("SELECT * FROM TABLENAME", connection))
using(var builder = new SqlCommandBuilder(adapter))
{
    adapter.UpdateCommand = builder.GetUpdateCommand();
    adapter.InsertCommand = builder.GetInsertCommand();
    adapter.DeleteCommand = builder.GetDeleteCommand();

    adapter.Update(importData.Tables[1]);
}

If the schemas differ you have to add mappings to the DataAdapter, like the MSDN DataAdapter example illustrates.

How can I add data from DataTable to database table directly?

1 Answers1

Linked