[C#]Get DataTable from Database, Update DataTable to Database

just take a note.

When we want to update database with a list, as you know, there will be 3 kinds of operation: insert(for new rows), update(for changed rows), delete(for deleted rows)

for a single record, it will be easy. But for a list of data, it will be a nightmare.

use the .NET DataAdapter, we can make it easier.
for base, we have sqlConnectString for default connection, and we try to access all columns of demoTable. There is a global DataTable named dt.

to make the example for common case, I will type it for SQL server.

1. to get DataTable from Database

string sql = @"SELECT * FROM demoTable;";
using(var conn = new SQLConnection(sqlConnectString))
{
    conn.Open();
    using(var cmd = new SQLCommand(conn))
    {
        cmd.CommandText = sql;
        var reader = cmd.ExecuteReader();
        dt.Load(reader);
    }
}

2. to update DataTable's data to Database

string sql = @"SELECT * FROM demoTable;";
using(var conn = new SQLConnection(sqlConnectString))
{
    conn.Open();
    var adp = new SQLDataAdapter(sql, conn);
    var cmb = new SQLCommandBuilder(adp);
    adp.Update(dt);
}

to speed up the database access, can add transaction commands. Begin the transaction before update, and commit the transaction after update.