Using a Data Table, how do I check if the data matches an entry in an Access database and adds the content?

Question

Using this code, I created a datatable out of an Excel document. The data set is also being displayed in a datagridview.

                OleDbcon.Open();                  
                tbContainer = new SD.DataTable();
                OleDbCommand cmd = new OleDbCommand();
                System.Data.DataTable dt = new System.Data.DataTable();
                OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), OleDbcon);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                DGVExcel.DataSource = ds.Tables[0];
                Console.WriteLine(dt.Rows.Count);
                OleDbcon.Close();

Now that I have this Dataset/DataTable, I want to be able to match its data with the contents of an already established access database and then add relevant values.

Say for example I have this in the database:

Name || Fingers || Thumbs || ID

Ross || 10 || 2 || 12

Boss || 9 || 1 || 14

The excel file happens to have the same exact data and the fields will be added except for the ID, which is used to identify the unique entries.

So Ross' value becomes 20||4, Boss becomes 18 || 2.

How do I make use of the DataSet/DataTable I just made to perform this operation?

The ideas I thought of would be to use an 'if not exists' Select * from Table where ID = [?]. But I don't know how to set the ID. Would a for loop work? Pseudocode:

for (int i = 2; i < DB.Count ; i++){
  string query = "if not exists Select * from Table where ID = " + DGridView.Rows[i].Cells[LastCell];

I was recommended Linq before but I honestly don't know where to start on that.

EDIT x2:

=======================================

So I think I've been going about this the wrong way, at any rate, I managed to insert my excel data DIRECTLY into the Access table without having to resort to a dataset/raw query. The only caveat is that the table structure needs to match the access table exactly, though I think that was always the case at any rate.

Code:

                    conn.Open();
                    string sqls = @"INSERT INTO ACXL SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" + openFileDialog.FileName + "].[" + txtSheetName.Text + "$];";
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sqls;
                    cmd.ExecuteNonQuery();
                    conn.Close();

On to the SQL table matching!

Answer 1

Here's how I would do it.

1. Create a "working table" in Access that contains the same column definitions as the main table, except the ID column should be an int and not an identity, so that you can manually insert values into it.

2. Insert rows into the working table, one row per row in the Excel spreadsheet. You can do this by iterating over the data set using for...each and executing an INSERT statement for each row.

3. Execute a single UPDATE statement that joins the working table to the main table and sums the fields as required. By performing this action in a single statement you avoid the sticky situation that would occur if you were doing the updates row by row and something failed partway through.

4. Truncate the working table.

To get the rows inserted into the temp table, I'd probably write something like this. Note that you don't need to pass the Name since it is completely dependent on ID anyway. This simplifies things-- if you passed it, you'd have to escape it too.

void InsertRowIntoTempTable(int id, int fingers, int thumbs)
{
    using (var conn = this.GetDatabaseConnection())
    {
        var cmd = new SqlCommand(conn);
        cmd.CommandText = String.Format("INSERT INTO TempTable VALUES ({0},{1},{2})", id, fingers, thumbs);
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

void InsertRowsIntoTempTable(DataTable source)
{
    for each (var row in source.Rows)
    {
        InsertRowIntoTempTable(row["ID"], row["Fingers"], row["Thumbs"]);
    }
}