C# Dataset to Access DB

问题:

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

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

回答2:

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.

回答3:

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 ...