General purpose class to fill DataTable(s) from DataBase and to save DataTable(s) to DataBase using reflection

• Download DataBaseConnectorMSAccess_Demo.zip - 499.7 KB
• Download DataBaseConnectorMSAccess_Source.zip - 532.9 KB
• Download DataBaseConnectorMSSqlServer_Demo.zip - 1.9 MB
• Download DataBaseConnectorMSSqlServer_Source.zip - 1.9 MB

Introduction

In .NET, strongly typed dataset can be easily created using the AddNewDataSource wizard in MS VisualStudio. The strongly typed dataset is useful as the database fields can be accessed in strongly typed way. The wizard creates the strongly typed TableAdapter classes for filling and saving the DataTable in the DataSet. Because of the strongly typed nature of the TableAdapter, filling and saving the data of each DataTable of the DataSet has to be handled separately using the corresponding TableAdapter of the particular DataTable, for example as shown below:

NorthwindDataSetTableAdapters.CustomersTableAdapter
customersAdapter = new NorthwindDataSetTableAdapters.CustomersTableAdapter();
OleDbConnection connection = new OleDbConnection(
string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filePath));
connection.Open();
customersAdapter.Connection = connection;
customersAdapter.Fill(destinationDataSet.Customers);
connection.Close();

Eventhough this looks to be easy, but it becomes clumsy and there will be lot of code repitition, when there are several data tables, which is generally the case for a practical program, as the above code is to be repeated for each DataTable, both for filling the DataTable and for saving the DataTable. If all the data tables are filled or saved in one method, then it becomes difficult to fill or save only selected DataTable(s) and requires conditional statements. Otherwise, separate methods have to be created for handling the filling and saving of selected list of DataTable(s). Reuse of the code also becomes difficult between different projects as the names of TableAdapter(s) and DataTable(s) are strongly typed. To overcome this difficulty I have written a DataBaseConnector class using Reflection as shown under Implementing DataBaseConnector sub heading. After instantiating the DataBaseConnector class, all DataTables or selected DataTable(s) can be filled or saved by calling the corresponding methods as shown below. The class can reused in different projects without changing the code of the class.

Using the Code

I have used MS Access database for explanation in the article, but the class can also be used for SQL Server database, for which I have included a sample for download.

To use DataBaseConnector class, include the classes DataBaseConnector and derived class OleDbDataBaseConnector for MS Access database (SqlServerDataBaseConnector for MS SQL Server database) in the solution in Data layer project (this is required as explained under Implementing DataBaseConnector) and create an instance of the typed DataSet in the form load event or at the appropriate place in your code. Then create an instance of OleDbDataBaseConnector by passing in the typed DataSet instance and connection string as parameters as shown below:

NorthWindDataSet1 = new NorthwindDataSet();

try {
    NorthWindConnector1 = new OleDbDataBaseConnector(NorthWindDataSet1, 
        String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", 
        Application.StartupPath + "\\NorthWind.mdb"));
}
catch (Exception ex) {
    MessageBox.Show(ex.Message);                
}

To fill all DataTables of DataSet call FillDataTables method of OleDbDataBaseConnector instance.

private void fillAllTablesToolStripMenuItem_Click(object sender, EventArgs e) {
    try {

        NorthWindConnector1.FillDataTables();
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}

To fill only selected DataTables of DataSet call FillDataTables method overload of OleDbDataBaseConnector instance by passing in the list of selected tables as an Array of String

private void fillSelectedTablesToolStripMenuItem_Click(object sender, EventArgs e) {
    try {

        NorthWindConnector1.FillDataTables(GetSelectedTableList("Select the Tables to Fill")); 
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}

Similarly, to save all DataTables of DataSet call SaveDataTables method of OleDbDataBaseConnector instance.

private void saveAllTablesToolStripMenuItem_Click(object sender, EventArgs e) {
    try {
        NorthWindConnector1.SaveDataTables(); 
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}

To save only selected DataTables of DataSet call SaveDataTables method overload of DataBaseConnector instance by passing in the list of selected tables as an Array of String

private void saveSelectedTablesToolStripMenuItem_Click(object sender, EventArgs e) {
    try {
        NorthWindConnector1.SaveDataTables(GetSelectedTableList("Select the Tables to Save")); 
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}

The advantage of using OleDbDataBaseConnector is that once it is instantiated, all or some of the DataTables can be filled or saved with one call of the corresponding method of OleDbDataBaseConnector instance. The class can be used in different projects without changing the code.

I have used Microsoft Visual Studio 2008, MS Access 2000 format Microsoft NorthWind sample database and MS SQL Server 2008 R2 Express for the sample projects included with this article.

Implementing the DataBaseConnector class

For using the DataBaseConnector class it is not required to understand the code of this class as the class can be directly included in different projects without altering the code. However, the implementation of DataBaseConnector class is discussed below:

First an abstract class DataBaseConnector is defined, from which the DataBaseConnector for particular database is derived like OleDbDataBaseConnector for MS Access database and SqlServerDataBaseConnector for MS SQL Server database. First let us see DataBaseConnector class. In the class, fields are declared for holding the destination dataset and connection string. In the constructor of the class the values of these fields are set from parameters.

protected string connectionString;
private DataSet destinationDataset;
protected DbConnection connection;

public DatabaseConnector(DataSet destinationDataset, 
    string connectionString) {
    try {
        this.connectionString = connectionString;
        this.destinationDataset = destinationDataset;
    }
    catch {
        throw;
    }
}

FillDataTables method is defined, which takes tableList as a parameter array, to fill some or all the DataTables of the dataset from database. It calls OpenConnection, FillDataTable and CloseConnection methods.

public void FillDataTables(params string[] tableList) {
    if (tableList == null) return;
    destinationDataset.EnforceConstraints = false;
    OpenConnection();
    foreach (string table in tableList) {
        try {
            FillDataTable(table);
        }
        catch (Exception ex) {
            throw new Exception(String.Format("Could not fill {0}\n{1}",
                table, ex.Message));
        }
    }
    destinationDataset.EnforceConstraints = true;
    CloseConnection();
}

OpenConnection and CloseConnection are declared as abstract methods which are implemented in the derived class of the particular database. FillDataTable method takes tableName parameter. It calls GetTableAdapter to obtain an instance of the TableAdapter corresponding to the current strongly typed DataTable. Using reflection the Fill method of the TableAdapter is called to fill the DataTable.

private void FillDataTable(String tableName) {
    Type tableAdapterType;
    var tableAdapter = GetTableAdapter(tableName, out tableAdapterType);

    object[] parameterList = new object[] {destinationDataset.Tables[tableName]};
    MethodInfo methodInfo = tableAdapterType.GetMethod("Fill");
    destinationDataset.Tables[tableName].Clear();
    destinationDataset.Tables[tableName].BeginLoadData();

    //Invoke Fill method of tableadapter
    methodInfo.Invoke(tableAdapter, parameterList);
    destinationDataset.Tables[tableName].EndLoadData();
    destinationDataset.Tables[tableName].AcceptChanges();
    methodInfo = tableAdapterType.GetMethod("Dispose");
    //Invoke dispose method of table adapter
    methodInfo.Invoke(tableAdapter, null);
}

GetTableAdapter method constructs the name of the TableAdapter of the strongly typed DataTable and obtains the TableAdapter type using GetExecutingAssembly of Assembly class and then creates an instance of it using Activator.CreateInstance method. The Connection property of TableAdapter is defined as internal by the AddNewDataSource wizard and is accessible only in the same assembly. Hence, the DataBaseConnector is to be included in the same project where the typed DataSet is created. Further the spaces in the table name of the database is replaced by '_' by wizard while generating the typed DataSet.

private object GetTableAdapter(string tableName, out Type tableAdapterType) {
    try {
        //TypedDataSet designer replaces space in table name with
        //underscore _.  
        Assembly assembly = Assembly.GetExecutingAssembly();
        tableAdapterType = assembly.GetType(string.Format(
            "{0}.{1}TableAdapters.{2}TableAdapter", assembly.GetName().Name,
            destinationDataset.DataSetName, 
            tableName.Replace(" ", "_")));
        var tableAdapter = Activator.CreateInstance(tableAdapterType);
        PropertyInfo propertyInfo = tableAdapterType.GetProperty("Connection", 
            BindingFlags.NonPublic | BindingFlags.Instance);
        propertyInfo.SetValue(tableAdapter, connection, null);
        return tableAdapter;
    }
    catch (Exception ex) {
        throw new Exception(
            String.Format("Could not create table adapter for {0}\n {1}",
            tableName, ex.Message));
    }
}

Similarly the SaveDataTables and SaveDataTable methods are defined as shown below.

public void SaveDataTables(params string[] tableList) {
    if (tableList == null) return;
    destinationDataset.EnforceConstraints = false;
    OpenConnection();
    foreach (string table in tableList) {
        try {
            SaveDataTable(table);
        }
        catch (Exception ex) {
            throw new Exception(String.Format("Could not save {0}\n{1}",
                table, ex.Message));
        }
    }
    destinationDataset.AcceptChanges();
    destinationDataset.EnforceConstraints = true;
    CloseConnection();
}
private void SaveDataTable(string tableName) {
    Type tableAdapterType;
    var tableAdapter = GetTableAdapter(tableName, out tableAdapterType);

    Assembly assembly = Assembly.GetExecutingAssembly();
    Type dataTableType = assembly.GetType(String.Format(
        "{0}.{1}+{2}DataTable", assembly.GetName().Name,
        destinationDataset.DataSetName, tableName.Replace(" ", "_")));
    Type targetDatasetType = destinationDataset.GetType();
    PropertyInfo propertyInfo = targetDatasetType.GetProperty(tableName.Replace(" ", "_"));
    var dataTable = propertyInfo.GetValue(destinationDataset, null);
    object[] parameterList = new object[] { dataTable };
    MethodInfo methodInfo = tableAdapterType.GetMethod("Update", new Type[] { dataTableType });
    //Invoke update method of table adapter
    methodInfo.Invoke(tableAdapter, parameterList);
    //Invoke dispose method of table adapter
    methodInfo = tableAdapterType.GetMethod("Dispose");
    methodInfo.Invoke(tableAdapter, null);
}

To use DataBaseConnector class for a particular database, a class is derived from DataBaseConnector class eg. OleDbDataBaseConnector class is derived for MS Access database and SqlServerDataBaseConnector for MS SQL Server database. This derived class implements the abstract methods OpenConnection and CloseConnection declared in the base class for opening and closing connection specific to this database as shown below.

public class OleDbDataBaseConnector : DatabaseConnector {
    public OleDbDataBaseConnector (DataSet destinationDataset, 
        string connectionString) :
    base (destinationDataset, connectionString) {
    }
    protected override void OpenConnection() {
        try {
            if (connection == null)
                connection = new OleDbConnection(connectionString);
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }
        catch{
            throw;
        }
    }
    protected override void CloseConnection() {
        try {
            if (connection == null)
                connection = new OleDbConnection(connectionString);
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        catch {
            throw;
        }
    }
}

Comments / Suggestions

I will be happy to see your comments and suggestions to improve the article.