Challenges faced while using ADO.NET while interacting with Database

Frankly, I've found the ADO.NET's disconnected database model to be quite a verbose and lengthy process to work with. First, you need to create a Connection object to connect to the database and then corresponding DataAdapter and DataSet objects to deal with each table in that database. For example, assuming just 4 tables in an SQLite table, there will be 1 SQLiteConnection, 4 SQLiteDataAdapter objects and 4 DataSet objects, so total 9 objects just to work with the database! This is perhaps too much to grasp for someone coming from Python or PHP where its much simpler to create a connection and/or cursor object which can then be used to query any given table using a SQL statement.

Of course, there are ways to solve this problem and make it as seamless as the Python/PHP way. The challenge of low level language like C# or Java is that a programmer has to solve this "making it seamless/reusable/dynamic" problem himself, nothing is served through the runtime as it happens in case of an interpreted language. A typical pattern that can be used to solve this problem is to encapsulate fetching and updating of tables in just one static function which can keep the track of DataAdapters and Connections while the user is free to work with Datasets. And instead of creating an adapter and dataset for every single table, we can just have two dictionaries called adapters and datasets! This is the pattern I typically use in my C# projects. Below example uses a SQLite database but should work for just about any.

private static SQLiteConnection conn = null;
private static Dictionary<string, SQLiteDataAdapter> adapters 
	= new Dictionary<string, SQLiteDataAdapter>();
private static Dictionary<string, DataSet> datasets 
	= new Dictionary<string, DataSet>();

public static DataSet openTable(string tableName) {
    if (conn == null) { //initialize connection if not already
        var connstr = @"Data Source=db.sqlite3;Version=3;New=True;Compress=True";
        conn = new SQLiteConnection(connstr); 
    }
    //initialize adapter and dataset if not already:
    if (!adapters.Keys.Contains(tableName))
    {
        adapters.Add(tableName,
        new SQLiteDataAdapter("select * from " + tableName, conn));
        datasets.Add(tableName, new DataSet());
        adapters[tableName].Fill(datasets[tableName]);
    }
    return datasets[tableName];
}

The code becomes much simpler and easier, almost as seamless as a dynamic language such as Python! You can place this in a static class called util or something (that's what I typically call it!) and then other parts of your code can just call util.openTable('foo') whenever they want to work with a database table.

Now the question arises as to what about saving or pushing the DataSet changes back to the table? No problem, that is also very easy once you've already done this groundwork!

public static int saveTable(DataSet ds) {
    DataSet changes = ds.GetChanges();
    SQLiteDataAdapter da = adapters[ds.Tables[0].TableName];
    if (changes == null) return 0;
    SQLiteCommandBuilder scb = new SQLiteCommandBuilder();
    scb.ConflictOption = ConflictOption.CompareRowVersion;
    int cnt = da.Update(changes, ds.Tables[0].TableName);
    ds.AcceptChanges();
    return cnt;
}

We just check if there are any changes in the DataSet and if changes are found, we simply call the DataAdapter's update method and pass to it the corresponding changed DataSet!