datatable 导入mysql 解决,如何直接将数据从DataTable添加到数据库表？

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?

解决方案

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.