C # Dataset to Access Database

Question

I have a dataset that is dynamically generated from a csv file. What I want to do is insert rows into my MS Access table, but I cannot figure out where to start.

The data headers in the data set may vary depending on the order, but the name of the header will always match the access database. Should I statically invoke the header name in the insert command, or can I build the headers from the dataset?

I know how to create a connection and open it in the database, but I'm not sure how to create an insert command to dynamically pull table headers.

I am very green when it comes to C # programming, so if you can write this for me, I would really appreciate it!

Here is an example of access table headers:

Identifier, product, cost, retail

Then a CSV that populates the dataset table. It may have Retail, or it may not be:

Item, cost

Here is the code that I have, but it is not written to the access table. If I believe dtAccess, it will display correctly.

OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\Database.accdb\";Persist Security Info=False;"); myConnection.Open(); string queryString = "SELECT * from " + lblTable.Text; OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, myConnection); DataTable dtAccess = new DataTable(); DataTable dtCSV = new DataTable(); dtCSV = ds.Tables[0]; using (new OleDbCommandBuilder(adapter)) { adapter.Fill(dtAccess); dtAccess.Merge(dtCSV); adapter.Update(dtAccess); } myConnection.Close(); 

Answer 1

Figured it out. Here is the code I used:

 OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"Database.accdb\";Persist Security Info=False;"); //command to insert each ASIN OleDbCommand cmd = new OleDbCommand(); //command to update each column (ASIN, Retail... from CSV) OleDbCommand cmd1 = new OleDbCommand(); //load csv data to dtCSV datatabe DataTable dtCSV = new DataTable(); dtCSV = ds.Tables[0]; // Now we will collect data from data table and insert it into database one by one // Initially there will be no data in database so we will insert data in first two columns // and after that we will update data in same row for remaining columns // The logic is simple. 'i' represents rows while 'j' represents columns cmd.Connection = myConnection; cmd.CommandType = CommandType.Text; cmd1.Connection = myConnection; cmd1.CommandType = CommandType.Text; myConnection.Open(); for (int i = 0; i <= dtCSV.Rows.Count - 1; i++) { cmd.CommandText = "INSERT INTO " + lblTable.Text + "(ID, " + dtCSV.Columns[0].ColumnName.Trim() + ") VALUES (" + (i + 1) + ",'" + dtCSV.Rows[i].ItemArray.GetValue(0) + "')"; cmd.ExecuteNonQuery(); for (int j = 1; j <= dtCSV.Columns.Count - 1; j++) { cmd1.CommandText = "UPDATE " + lblTable.Text + " SET [" + dtCSV.Columns[j].ColumnName.Trim() + "] = '" + dtCSV.Rows[i].ItemArray.GetValue(j) + "' WHERE ID = " + (i + 1); cmd1.ExecuteNonQuery(); } } myConnection.Close(); 

Answer 2

Access has hidden tables that provide database access for tables and columns in the database. Their names depend on the version of access, but are they usually something like systables? It has been a while, but if you “show hidden tables” you can find them.

Answer 3

If two data types have the same structure, you can merge "CSV" Datatable with a datatable database table, for example you can get the database table as data using a data adapter:

 string queryString = "SELECT * FROM sometable"; SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection); DataTable dtAccess = new DataTable(); adapter.Fill(dtAccess); 

then you can combine the contents of this DataTable with the contents of the CSV DataTable:

 dtAccess.Merge(dtCSV); 

After that, you can write the contents of the data to the access database table using the command constructor:

  using (new SqlCommandBuilder(adapter)) { adapter.Update(dtAccess); } 

If the data data has the same structure, they should work without problems ...