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.