Extend DataTable in C#

Question

A static constructor for class SourceManager goes through all modules/classes and discovers all classes that implement ISource. It will instantiate each one of these and expose an IEnumerable of them as a static property called IEnumerable<ISource> Sources. For simplicity ISource has two properties, DataTable Table { get; } and string UniqueName { get; }. When instantiated each different ISource is responsible for populating its Table from SQL, MDX, etc. For all the ISource's I've written thus far, loading the Table with all DataRows when instantiated has been sufficient. However I now have a situation where I'd like to load the Table with DataRow's lazily, rather than all up front. How do I do this? I will walk through an example.

PermissionSource implements ISource. Its Table property, that has a private set is given a value of new PermissionDataTable(). Its UniqueName is "Permissions". As of right now there are no permissions loaded from the database into this Table property.

ISource permissionSource = SourceManager.Sources.
    Where(s => "Permission".Equals(s.UniqueName)).First();

Now we've obtained the PermissionSource, but through an interface. Let's get a permission.

DataRow row = permissionSource.Table.Rows.Cast<DataRow>().
    Where(r => r["PermissionName"].Equals("PermissionName")).First()

I've overriden the Rows property in PermissionDataTable so that the above, somehow, gets the value of the permissions associated with "PermissionName" in the database. Other permissions are not loaded.

I don't have a choice in the permissions system and I don't have a choice to not use a DataTable.

EDIT:

In my example I would need to override the Rows property of DataTable. Rows, though, is a DataRowCollection which is sealed. Thus there isn't much that can be done in terms of creating a minimal custom DataTable implementation like I want to do.

Answer 1

I am not sure I understand your restrictions in using a DataTable, but one thing I've done in the past when I needed to "refresh" the data in a DataTable or repopulate it using different criteria is to create a new class derived from DataTable that includes a reference to a DataAdapter with the connection and selection information originally used to fill the DataTable.

For example, the DataTable sub-class could look something like the LazyDataTable code below. Note that I have added several different methods of accessing the Rows. They might make more sense after taking a look at the PermissionSource and main Program code near the end of this post. Also, please note that I have not included all of the details related to properly opening and closing database connections in every case. How you handle that will depend on your model for database access (e.g. connection pooling, shared connections etc).

//using System.Data.Common;
public class LazyDataTable : DataTable {
    protected DbDataAdapter Adapter { get; set; }

    public LazyDataTable(DbDataAdapter a) {
        Adapter = a;
    }
    /// <summary>
    /// Save changes back to the database, using the DataAdapter
    /// </summary>
    public void Update() {
        Adapter.Update(this);
    }
    /// <summary>
    /// Fill this datatable using the SelectCommand in the DataAdapter
    /// The DB connection and query have already been set.
    /// </summary>
    public void Fill() {
        Adapter.Fill(this);
    }

    /// <summary>
    /// read and return one row at a time, using IEnumerable syntax
    /// (this example does not actually add the row to this table, 
    /// but that can be done as well, if desired.
    /// </summary>
    public IEnumerable<DataRow> LazyReadRows() {
        using (var reader = OpenReader()) {
            //Get the schema from the reader and copy it to this table.
            var schema = reader.GetSchemaTable();
            var values = new object[schema.Columns.Count];
            while (reader.Read()) {
                reader.GetValues(values);
                var row = schema.NewRow();
                row.ItemArray = values;
                yield return row;
            }
        }
    }

    /// <summary>
    /// Fill one row at a time, and return the new row.
    /// </summary>
    public DataRow ReadRow() {
        if (_reader == null || _reader.IsClosed) 
            _reader = OpenReader();
        //Get the schema from the reader and copy it to this table.
        if (this.Columns.Count == 0) 
            this.Columns.AddRange(_reader.GetSchemaTable().Columns.Cast<DataColumn>().ToArray());
        if (!_reader.Read()) {
            _reader.Dispose();
            return null;
        }
        var values = new object[_reader.FieldCount];
        _reader.GetValues(values);
        return this.Rows.Add(values);
    }
    private DbDataReader _reader = null;

    private DbDataReader OpenReader() {
        OpenConnect();
        return Adapter.SelectCommand.ExecuteReader();
    }

    private void OpenConnect() {
        var cn = Adapter.SelectCommand.Connection;
        if (cn.State == ConnectionState.Closed)
            cn.Open();
    }

    /// <summary>
    /// Change a Parameter in the SelectCommand, to filter which rows to retrieve.
    /// </summary>
    public void SetSelectParam(string name, object value) {
        var selparams = Adapter.SelectCommand.Parameters;
        selparams[name].Value = value;
    }
}

Then your PermissionSource would create a LazyDataTable and set the DataAdapter (including the connection and SELECT command) appropriately. It wouldn't fill the DataTable, but would instead return it empty, to be filled later, by the application code. So your PermissionSource might something like the code below. I've used System.Data.OleDb data objects as an example, but you would use whatever ADO providers you want.

interface ISource {
    public DataTable Table { get; }
    string UniqueName { get; }
}

public class PermissionSource : ISource {
    /// <summary>
    /// Loads a DataTable with all of the information to load it lazily.
    /// </summary>
    public DataTable Table { 
        get { 
            const string SELECT_CMD = "SELECT * FROM [Permissions] WHERE ([PermissionName] IS NULL OR [PermissionName]=@p1) AND [OtherProperty]=@p2";
            var conn = new OleDbConnection("...ConnectionString...");
            var selectCmd = new OleDbCommand(SELECT_CMD, conn);
            selectCmd.Parameters.AddWithValue("p1", "PermissionName");
            selectCmd.Parameters.AddWithValue("p2", 0);
            var adapter = new OleDbDataAdapter(selectCmd);
            var builder = new OleDbCommandBuilder(adapter); //used to generate the UPDATE and DELETE commands...
            adapter.UpdateCommand = builder.GetUpdateCommand(); //etc.
            //Do NOT fill the table here. Instead, let the caller fill it.
            return new LazyDataTable(adapter);
        }
    }
    public string UniqueName { get { return "Permission"; } }
}

Your main program code would use PermissionSource and LazyDataTable as follows:

    static class Program {
    void GetPermissions() {
        ISource permissionSource = SourceManager.Sources.
            Where(s => "Permission".Equals(s.UniqueName)).First();

        var table = permissionSource.Table as LazyDataTable;
        table.SetSelectParam("PermissionName", "Admin");

        //If you want to fill ALL rows in one step:
        table.Fill(); 

        // OR If you want to fill one row at a time, and add it to the table:
        DataRow row;
        while(null != (row = table.ReadRow())) {
            //do something with each individual row. Exit whenever desired.
            Console.WriteLine(row["PermissionName"]);
        }

        // OR If you prefer IEnumerable semantics:
        DataRow row = table.LazyReadRows().FirstOrDefault(someValue.Equals(row["columnname"]));

        //OR use foreach, etc. Rows are still ONLY read one at a time, each time IEnumerator.MoveNext() is called.
        foreach (var row in table.LazyReadRows())
            if (row["someColumn"] == "someValue")
                DoSomething(row["anothercolumn"]);
    }
}

You can certainly mix and match parts of the LazyDataTable shown here to achieve exactly what you want within your application constraints. It would of course be much better if you could switch to a different model of sharing data, but if you MUST return a DataTable from each Source, then at least you can return a more functional DataTable when necessary by subclassing it as I've demonstrated here. This allows you to pass back more information, which you can use to fill the table as you see fit. I would still encourage you to look into LinqToSQL as well as possibly trying to switch to simply passing back a DbDataReader or some other object similar to the LazyDataTable I've shown here that will allow you to both customize the original query (e.g. by using the SetSelectParam method) and also to read the data in one row at a time.

Hope that helps!