Send DataTable to Access DB using C#

Question

I have table (about 250k rows) in Access db with date, time and name (other columns are irrelevant for this problem). I need to get just first (or last) date/time for name value and send it to new table.

I have tried SQL statement but for large number of rows it is too slow. here: Access SQL to return only first or last occurence by date

I also tried read data from db to DataTable, looping through and deleting rows that I don't want, but I'm not able to send this edited DataTable back to db. Similar to this: How do I insert a datatable into Microsoft Access?

using(OleDbConnection con = new OleDbConnection(ConnectionString))
{
    SQL = "Select * From OriginalTable";
    var adapter = new OleDbDataAdapter();
    adapter.SelectCommand = new OleDbCommand(SQL, con);
    var cbr = new OleDbCommandBuilder(adapter);

    try
    {
        con.Open();

        DataTable dtFilter = new DataTable();
        adapter.Fill(dtFilter);
        string id = dtFilter.Rows[dtFilter.Rows.Count - 1][4].ToString();
        for(int i = dtFilter.Rows.Count - 2; i >= 0; i--)
        {
            DataRow dr = dtFilter.Rows[i];
            if(dtFilter.Rows[i][4].ToString() == id)
            {
                dr.Delete();
            }
            else
            {
                id = dtFilter.Rows[i][4].ToString();
            }
        }
        dtFilter.AcceptChanges(); // DataTable looks as I want
        adapter.Update(dtFilter); // Returns 0 
    }
    catch(OleDbException ex)
    {
        MessageBox.Show(ex.Message, "OledbException Error");
    }
    catch(Exception x)
    {
        MessageBox.Show(x.Message, "Exception Error");
    }
}

I expect to export dtFilter to Access db. Why it returns 0?

I'm open for SQL statement as long as it is fast.

UPDATE: SQL Statement used for selecting first or last entry

SELECT DISTINCT 
    cdate(Format(t.DateOS + t.TimeOS, 'dd.MM.yyyy HH:mm:ss')) AS DateTimeOS, 
    cdate(Format(t.DateOS, 'dd.MM.yyyy ')) AS DateOS, 
    cdate(Format(t.TimeOS, 'HH:mm:ss')) AS TimeOS, 
    t.EP AS EP, t.ID AS ID 
FROM TestFirstLast AS t 
WHERE t.EP = 'L100' 
    AND (((t.DateOS + t.TimeOS) > #1/1/2016 12:00:00 AM# AND (t.DateOS + t.TimeOS) <= #3/1/2016 12:00:00 AM#)) 
    AND NOT EXISTS(SELECT 1 FROM TestFirstLast AS t2 WHERE t2.EP = t.EP AND t2.ID = t.ID AND (t2.DateOS < t.DateOS OR t2.DateOS = t.DateOS AND t2.TimeOS < t.TimeOS))

GOOD SOLUTION: I found good description of problem and solution here

What SQL statement did you try exactly, and how slow was it exactly? What speed would you consider is acceptable for your purposes? I can't remember if access allows you to create indexes on your table, but perhaps that would help. You're right that looping through all rows is not going to be a good solution. Although, as a general point, 250k is quite a lot of rows by the standards of Access. Maybe you should consider using something more suitable for the scale of your data, like SQL Server Express, or MySQL — ADyson, Apr 10 '19 at 08:51
SQL Statement is in the link [here](https://stackoverflow.com/questions/52496411/access-sql-to-return-only-first-or-last-occurence-by-date) - that is my previous question, but with larger data it became slow. Right now I have to work with Access, but SQL Server is next step. — Stana Macala, Apr 10 '19 at 13:48
How slow? Does it get slower directly proportional to the quantity of data, or exponentially, or something? We need some actual performance data. And what speed would be acceptable? And please repost the SQL here so that _this_ question is complete, and it's easy for people to see, and in case the old question ever changes or disappears. Each question should be complete in its own right. — ADyson, Apr 10 '19 at 13:54
Also please show the table definition (as a Create statement would make sense) — ADyson, Apr 10 '19 at 13:55
Maybe this would be a useful article to read: https://support.office.com/en-us/article/create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce — ADyson, Apr 10 '19 at 13:56
I don't have exact performance data. I can tell you, that 30s would be acceptable, but this took about 15-20 mins and still did not finish. This sql is send to DataTable and I work with it later on. — Stana Macala, Apr 10 '19 at 14:25

score 0 · Answer 1 · 2019-04-10T09:24:14.163

0

To use the .Update method, you have to provide an update SQL command.

The OleDbDataAdapter provides

InsertCommand
UpdateCommand
DeleteCommand

If you are not setting the corresponding command, the DataAdapter does not know what to do with your data.

Have a look at OleDbDataAdapter Class

edited Apr 10 '19 at 09:24

answered Apr 10 '19 at 08:58

I have followed example in **Update(DataTable)** in link you provided, but I might not understand. *adapter.Fill(dtFilter)* -> fills DataTable with original data from sql, then I work on *dtFilter* table and at the end *adapter.Update(customers)* should update table in DB. That's how I understand it. – Stana Macala Apr 10 '19 at 13:57
Roughly said, you are on the right way. But can you provide your code? What is your UpdateCommand? – Apr 10 '19 at 14:02
Sorry, my bad I was following this [example](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.update?view=netframework-4.7.2#System_Data_Common_DbDataAdapter_Update_System_Data_DataTable_). I don't have any UpdateCommand...I'm not sure how does it work. – Stana Macala Apr 10 '19 at 14:36
Your OleDbDataAdapter knows only how to select the data because you told him how to select them, by setting *adapter.SelectCommand = ...* Unless you tell the OleDbDataAdapter how to update, it does not what to do when you call adapter.Update(). You need something like "UPDATE tableName SET ... WHERE Y=Z". Same for insert and delete. – Apr 10 '19 at 14:42

Send DataTable to Access DB using C#

1 Answers1