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.