Insert dataset records in database

c#, dataset, ms-access

Solution

For this sort of task, consider using the data adapter abstraction. With an Microsoft Access database, you can use the `OleDbDataAdapter` implementation as shown in the example below:

// Prerequisite: The data to be inserted is available in a DataTable/DataSet.
var data = new DataTable();
data.Columns.Add("CompanyName", typeof(string));
data.Columns.Add("Phone", typeof(string));
data.Rows.Add("Foo", "12345678");
data.Rows.Add("Bar", "87654321");

// Now, open a database connection using the Microsoft.Jet.OLEDB provider.
// The "using" statement ensures that the connection is closed no matter what.
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=Northwind.mdb"))
{
    connection.Open();

    // Create an OleDbDataAdapter and provide it with an INSERT command.
    var adapter = new OleDbDataAdapter();
    adapter.InsertCommand = new OleDbCommand("INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName , @Phone)", connection);
    adapter.InsertCommand.Parameters.Add("CompanyName", OleDbType.VarChar, 40, "CompanyName");
    adapter.InsertCommand.Parameters.Add("Phone", OleDbType.VarChar, 24, "Phone");

    // Hit the big red button!
    adapter.Update(data);
}

You can do the same against other brands of database engines as well by replacing `OleDbCommand`, `OleDbDataAdapter` and `OleDbConnection` with the appropriate implementations for your database engine. For Microsoft SQL Server, look for classes prefixed with `Sql`, eg. `SqlCommand`.

Problem

I need to insert the dataset records(C#) into the MS Accessdatabase table. I need to do the bulk insertion of records. How can I do this in C#

Original source