How to insert data into Access DB table from a C# class dataTable?

Question

I have a datatable that has thousands of rows and columns and I need to copy this data into AccessDB table with exactly the same table structure. There are several tables in AccessDB that need to get populate with rows from dataTable. Some of the table has 200 columns and thousands of rows. So adapter.InsertCommand , setting up var for each column and adapter.InsertCommand.Parameters.Add will be countless lines of code. I tested my code and insert is taking forever.

I heard adapter.Fill method could be faster and much quicker but I am not familiar with it, it would be great help if someone can assist me with code sample please. 

here is my code

string dbProvider = @”Provider=Microsoft.Jet……”;
string databaseName = @”test.smith”;
string mydbPath = “C:/tempTest/Smith” ;
string fPath = mydbPath  +  @”\”  +  databaseName;

string thisdbPath = dbProvider + mydbPath   + @”\” + databaseName;

using ( var con = new OleDbConnection(thisdbPath))
{
  con.Open();
  var adapter = new OleDbDataAdapter();
  
 foreach (var tablerow in dataTable .AsEnumerable())
{
   if (String.Equals(str_tableName, “table_customer))
   {
       adapter.InsertCommand = new OleDbCommand(“INSERT into customer_table (ID, name, address, 
                 city, st, zipcode, phone, amount, discount, spendAmount) VALUES (?, ?, ?, ?,…..)”, con);
    
       var this_ID = tablerow.ItemArray[0];
       var this_name = tablerow.ItemArray[1];
       var this_ address = tablerow.ItemArray[2];
       var this_ address = tablerow.ItemArray[3];

      …
      …
      adapter.InsertCommand.Parameters.Clear();
      adapter.InsertCommand.Parameters.Add(“ID”, OleDbType.BigInt, 0).Value = this_ID;
      adapter.InsertCommand.Parameters.Add(“name”, OleDbType.VarChar, 100).Value = this_name;
      adapter.InsertCommand.Parameters.Add(“address”, OleDbType.VarChar, 250).Value = this_ address;
     …
     …
     adapter.InsertCommand.ExecuteNonQuery();

 }
 elseif (String.Equals(str_tableName, “table_account))
{

}

}
}

   

Answer 1

This should be a comment but there is too much information. First, forget about using a DataTable, although Dapper can work with a DataTable a DataTable has functionality not necessarily and will affect performance. Using a concrete class is all that is needed which combined with Dapper provides the best performance.

Sticking with the above, a OleDbDataAdapter has no place in coding with Dapper, think what I said above, use classes not OleDbDataAdapter/Parameters/DataTable as Dapper handles all this for you.

The dictionary seems to be a new addition to your original question, not going to try and evaluate its purpose as this to me has nothing to do with the adding records. I may be wrong yet if so unsure what to say. Here is a modification to my original code sample where the repository accepts a table name and assumes each table has the same columns, if not than you need either a class for each table or an add method for each table.

Here the code assumes columns are the same and note although in this case all columns are type string that is irrelevant, Dapper does not care what data types are used, if Access accepts it do does Dapper. Yes this is one method call for add so you need a for or for-each and iterate all 25 list (not DataTable) to perform the insert/add.

using System.Data;
using System.Data.OleDb;
using Dapper;
using DapperForAccessDatabase.Interfaces;
using DapperForAccessDatabase.Models;
using kp.Dapper.Handlers;
#pragma warning disable CA1416

namespace DapperForAccessDatabase.Repositories;

public class CustomerRepository : ICustomerRepository
{
    private IDbConnection _cn;

    public CustomerRepository()
    {
        _cn = new OleDbConnection(ConnectionString());
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
    }

    private string _tableName = "";
    public CustomerRepository(string tableName)
    {
        _cn = new OleDbConnection(ConnectionString());
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
        _tableName = tableName;
    }
    public List<Customers> GetAll()
        => _cn.Query<Customers>($"SELECT Identifier, FirstName, LastName, UserName FROM {_tableName}").ToList();

    public void Add(List<Customers> customer)
    {

        var statement =
            $"""
            INSERT INTO {_tableName}
            (
                FirstName,LastName,UserName
            )
            VALUES
            (
                @FirstName,@LastName,@UserName
            )
            """;
        _cn.Execute(statement, customer);
    }
}

Usage: I used Bogus NuGet package to insert 10,000 rows and NuGet package ObjectDumper.NET to dump data to the console from the read operation.

namespace DapperForAccessDatabase;

internal partial class Program
{
    static void Main(string[] args)
    {
        CustomerRepository repository = new("Customers");

        var bogusCustomerList = BogusOperations.CustomersListHasNoIdentifiers(10_000);
        AnsiConsole.MarkupLine("[cyan]Bogus data generated[/] [yellow]Adding records...[/]");
        repository.Add(bogusCustomerList);
        var customers = repository.GetAll();
        Console.WriteLine(ObjectDumper.Dump(customers));
        AnsiConsole.MarkupLine($"[cyan]Record count {customers.Count}[/]");
        AnsiConsole.MarkupLine("[yellow]Press ENTER to quit[/]");
        Console.ReadLine();
    }
}

Of course you have many more rows and columns but I'm not going to try and replicate your environment.