0

I know there are much more articles about this question, but my question is a special case -as I think-

I have a DataSet filled with Datatable named 'Categories' by OleDataAdapter. After filling 'Categories' table from the database on a local PC, I've made a huge editing to it (adding rows -maybe thousands- and updating fields).

now, I want to update all of these edits to the database table on the PC. how to do this as fast as possible?

Askcomp eugi-tech
  • 73
  • 8
  • https://stackoverflow.com/questions/25323560/most-efficient-way-to-insert-rows-into-mysql-database – Waleed Naveed Oct 02 '18 at 11:22
  • Are you just adding new rows or editing existing rows as well? – Rui Jarimba Oct 02 '18 at 11:49
  • Yours is not a special case. DataAdapters are made for tracking changes and then updating the database. You don't need to loop over anything, I would avoid those solutions. [See this documentation](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters) – Crowcoder Oct 02 '18 at 12:03
  • thank you all for all your replies, but I use OleDb not Sql and SqlConnection doesnot support my provider (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=......) – Askcomp eugi-tech Oct 03 '18 at 05:44

1 Answers1

0

Try SqlBulkCopy

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?redirectedfrom=MSDN&view=netframework-4.7.2

a quick sample:

 DataTable dt = new DataTable();
    foreach (var itm in sampleList) {
        DataRow row = dt.NewRow();
        row["Field1"] = itm.Field1;
        row["Field2"] = itm.Field2;
        row["Field3"] = itm.Field3;
        dt.Rows.Add(row);
    }
    using (SqlConnection cn = new SqlConnection(connectionString)) {
        cn.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn)) {
            bulkCopy.DestinationTableName = "dbo.Categories";
            bulkCopy.WriteToServer(dt);
        }
        cn.Close();
    }
ubaldisney
  • 66
  • 1
  • 10