C# Dataset to Access DB

Question

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

The headers of the data in the dataset can vary as far as the order but the name of the header will always match the access database. Do I have to statically call out the header name in the insert command or can I build the headers from the dataset?

I know how to create the connection and open it to the database but am not sure how to create in insert command to dynamically pull the table headers.

I am pretty green when it comes to C# programming so if you can spell it out for me I would really appreciate it!

Here is an example of the access table headers:

ID, Item, Cost, Retail

Then the CSV which will fill the dataset table. It might have Retail or it might not:

Item, Cost

Here is the code I have so far but it doesn't write to the access table. If I vew the dtAccess it shows 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();

It is already dynamically created, or you want it to be dynamically created? — McKay, Nov 10 '11 at 18:07
I want it to be dymanically created into a string so I can use it in an insert command. Somthing like: INSERT INTO [table] (datasetheaders) — Reg, Nov 10 '11 at 18:11
Sorry @reg can you be more explicit in what it is you're looking for. Can we see examples? — McKay, Nov 10 '11 at 18:15
So, it appears as if you want to know how to parse CSV? http://stackoverflow.com/questions/1544721/reading-csv-files-in-c-sharp — McKay, Nov 10 '11 at 18:18
Basically I have a dataset with maybe 2 columns that match 2 columns from an access table. The access table has maybe 10 columns. I want to be able to insert the values from those 2 columns where they match the column name in the access table. The order the columns appear will vary and the columns included in the CSV will also vary. — Reg, Nov 10 '11 at 18:20
@McKay I already have the CSV loaded into a dataset table, I am just trying to save the dataset table values into the access table. — Reg, Nov 10 '11 at 18:22

score 5 · Accepted Answer · answered Nov 12 '11 at 03:46

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();

For the line `dtCSV = ds.Tables[0]`, where do you get the variable `ds`? — Ahmed Anwar, Dec 09 '16 at 22:52

score 0 · Answer 2 · answered Nov 10 '11 at 18:09

0

Access has hidden tables that provide database access to the tables and columns in the database. The names of them are dependent upon access version, but they're typically something like systables? It's been a while, but if you "show hidden tables" you should be able to find them.

answered Nov 10 '11 at 18:09

McKay

12,334
7
53
76

I know about the systables in access but I am trying to create the headers from the dataset not from the access table. My dataset will only include maybe 4 of 10 columns (will vary) from the access table. – Reg Nov 10 '11 at 18:14

score 0 · Answer 3 · answered Nov 10 '11 at 18:21

0

If the the two datatable have the same structure you can merge the "CSV" Datatable with the Database table datatable, so for example you can retrieve the database table into a datatable 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 merge the content of this DataTable with the content of the CSV DataTable :

dtAccess.Merge(dtCSV);

After that you can write the content of the datatable into the access database table with a command builder :

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

If the datatables have the same structure should work without any problem ...

answered Nov 10 '11 at 18:21

aleroot

71,077
30
176
213

This almost works but it doesn't write to the access table. Am I missing something? – Reg Nov 10 '11 at 18:56
Try initializing CommandBuilder before filling the datable with the adapter. – aleroot Nov 10 '11 at 19:06
Sorry I am a noob. How would I do that? Does the using statement above initialize without calling it in any way? – Reg Nov 10 '11 at 19:16
Something like this : string queryString = "SELECT * FROM sometable"; SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection); DataTable dtAccess = new DataTable(); using (new SqlCommandBuilder(adapter)) { adapter.Fill(dtAccess); dtAccess.Merge(dtCSV); adapter.Update(dtAccess); } – aleroot Nov 10 '11 at 20:07
Still not writing to the access table. I posted what I have above. I do not see where it inserts data to the MS Access table in those lines of code. – Reg Nov 10 '11 at 21:52
Change the select query and insert only the two field that you need : SELECT Item,Cost FROM ... – aleroot Nov 10 '11 at 22:28

C# Dataset to Access DB

3 Answers3

Linked