8

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();
Improve this question
6
  • It is already dynamically created, or you want it to be dynamically created? Commented Nov 10, 2011 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) Commented Nov 10, 2011 at 18:11
  • Sorry @reg can you be more explicit in what it is you're looking for. Can we see examples? Commented Nov 10, 2011 at 18:15
  • So, it appears as if you want to know how to parse CSV? stackoverflow.com/questions/1544721/… Commented Nov 10, 2011 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. Commented Nov 10, 2011 at 18:20

3 Answers 3

Reset to default
5

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

For the line dtCSV = ds.Tables[0], where do you get the variable ds?
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.

Improve this answer

1 Comment

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

Improve this answer

6 Comments

This almost works but it doesn't write to the access table. Am I missing something?
Try initializing CommandBuilder before filling the datable with the adapter.
Sorry I am a noob. How would I do that? Does the using statement above initialize without calling it in any way?
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); }
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.