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

Hi @Josh S , Welcome to Microsoft Q&A,

If you're dealing with a large amount of data and performance is a concern, you might consider using the OleDbCommand.Parameters.AddWithValue method instead of explicitly adding parameters for each column. Additionally, using the OleDbDataAdapter.Fill method is typically used for reading data from the database into a DataTable, not for inserting data. For inserting data, the OleDbCommand.ExecuteNonQuery method is more appropriate. Here's a modified version of your code that uses the OleDbCommand.Parameters.AddWithValue method and OleDbCommand.ExecuteNonQuery:

string dbProvider = @"Provider=Microsoft.Jet...";
string databaseName = @"test.smith";
string mydbPath = "C:/tempTest/Smith";
string fPath = Path.Combine(mydbPath, databaseName);
string thisdbPath = $"{dbProvider}{mydbPath}\\{databaseName}";

using (var con = new OleDbConnection(thisdbPath))
{
    con.Open();

    foreach (DataRow tablerow in dataTable.Rows)
    {
        if (string.Equals(str_tableName, "table_customer", StringComparison.OrdinalIgnoreCase))
        {
            string insertQuery = "INSERT into customer_table (ID, name, address, city, st, zipcode, phone, amount, discount, spendAmount) " +
                                 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            using (var adapter = new OleDbDataAdapter())
            using (var insertCommand = new OleDbCommand(insertQuery, con))
            {
                insertCommand.Parameters.AddWithValue("ID", tablerow["ID"]);
                insertCommand.Parameters.AddWithValue("name", tablerow["name"]);
                insertCommand.Parameters.AddWithValue("address", tablerow["address"]);
                insertCommand.Parameters.AddWithValue("city", tablerow["city"]);
                insertCommand.Parameters.AddWithValue("st", tablerow["st"]);
                insertCommand.Parameters.AddWithValue("zipcode", tablerow["zipcode"]);
                insertCommand.Parameters.AddWithValue("phone", tablerow["phone"]);
                insertCommand.Parameters.AddWithValue("amount", tablerow["amount"]);
                insertCommand.Parameters.AddWithValue("discount", tablerow["discount"]);
                insertCommand.Parameters.AddWithValue("spendAmount", tablerow["spendAmount"]);

                insertCommand.ExecuteNonQuery();
            }
        }
        else if (string.Equals(str_tableName, "table_account", StringComparison.OrdinalIgnoreCase))
        {
            // Handle table_account insertion
        }
        // Add other tables as needed
    }
}

This modification simplifies the parameter adding process and may help improve performance. Also, note that I changed the comparison to StringComparison.OrdinalIgnoreCase to make it case-insensitive, which is a common practice when dealing with table names. Adjust the code according to your specific needs for other tables.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

JX's solution is RBAR, so likely slow. How did the data get in the dataTable? If from another data source, it may be possible to have Access query that source directly. That would give you a set-based insert which is likely faster.

Answer 3

JX's solution is RBAR, so likely slow. How did the data get in the dataTable? If from another data source, it may be possible to have Access query that source directly. That would give you a set-based insert which is likely faster.

Answer 4

You would be much better off loading data into a list and using Dapper which is easy to use and is high performance. With Dapper, you write an SQL INSERT statement, pass it to a method and execute. Very simple example (and yes access accepts parameter names but must be in ordinal position).

INSERT INTO dbo.Person
(
    FirstName,
    LastName,
    BirthDate
)
VALUES
(@FirstName, @LastName, @BirthDate);

Create a class, setup a connection and execute the statement as shown below. Person class has properties for each column shown above/

public class PersonRepository : IBaseRepository
{
    private IDbConnection _cn;
    public PersonRepository()
    {
        _cn = new SqlConnection(ConnectionString());
    }
    public async Task AddRange(List<Person> list)
    {
        await _cn.ExecuteAsync(SqlStatements.InsertPeople, list);
    }
}

You can also convert a DataTable to a list via the following extension.

public static class Extensions
{
    /// <summary>
    /// Convert DataTable to List of T
    /// </summary>
    /// <typeparam name="TSource">Type to return from DataTable</typeparam>
    /// <param name="table">DataTable</param>
    /// <returns>List of <see cref="TSource"/>Expected type list</returns>
    public static List<TSource> DataTableToList<TSource>(this DataTable table) where TSource : new()
    {
        List<TSource> list = new();

        var typeProperties = typeof(TSource).GetProperties().Select(propertyInfo => new
        {
            PropertyInfo = propertyInfo,
            Type = Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType
        }).ToList();

        foreach (var row in table.Rows.Cast<DataRow>())
        {

            TSource current = new();

            foreach (var typeProperty in typeProperties)
            {
                object value = row[typeProperty.PropertyInfo.Name];
                object safeValue = value is null || DBNull.Value.Equals(value) ? 
                    null : 
                    Convert.ChangeType(value, typeProperty.Type!);

                typeProperty.PropertyInfo.SetValue(current, safeValue, null);
            }

            list.Add(current);

        }

        return list;
    }
}

Answer 5

I put together a project with enough to get you started. IMHO this is the easiest and performant you can get without going to a paid library which I mention in the readme file. In the code below the AddTypeHandler is not required, I created the class from a template via ReSharper and only needed when working with DateOnly.

public class CustomerRepository : ICustomerRepository
{
    private IDbConnection _cn;

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

    public List<Customers> GetAll()
        => _cn.Query<Customers>("SELECT Identifier, FirstName, LastName, UserName FROM Customers").ToList();

    public void Add(List<Customers> customer)
    {

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