Generic DataTable & TableAdaptor updates with Reflection (C#)

Question

I have several strongly typed datasets throughout my application. Writing methods to update the data is getting tedious as each has several tables. I want to create one generic function that I can update all of the tables easily. I don't mind if I have to create one of these for each DataSet but if one function could handle all of them, that would be amazing!

There will be any number of new, updated, or deleted records and each row should be flagged properly. This function should just be handling the actual saving. Here is what I have so far:

    private bool SaveData(object oTableAdaptor, object ds)
    {
        try
        {
            Type oType = oTableAdaptor.GetType();
            MethodInfo[] oMethodInfoArray = oType.GetMethods();

            foreach (MethodInfo oMI in oMethodInfoArray)
            {
                if (oMI.Name == "Update")
                {
                    ParameterInfo[] oParamaterInfoArray = oMI.GetParameters();
                    foreach (ParameterInfo oPI in oParamaterInfoArray)
                    {
                        Type DsType = null;

                        if (oPI.ParameterType.Name == "NameOfDataSet")
                        {
                            DsType = typeof(MyDataSet);

                            // get a list of the changed tables???
                        }

                        if (((DataSet)ds).HasChanges() == true)
                        {
                            if (oPI.ParameterType == DsType)
                            {
                                object[] values = { ds };
                                try
                                {
                                    oMI.Invoke(oTableAdaptor, values);
                                }
                                catch (Exception ex)
                                {
                                    System.Diagnostics.Debug.WriteLine(oTableAdaptor.GetType().Name + Environment.NewLine + ex.Message);
                                }
                            }
                        }

                    }
                }
            }
        }
        catch (Exception Exp)
        {
            System.Diagnostics.Debug.WriteLine(Exp.Message);
            if (Exp.InnerException != null) System.Diagnostics.Debug.WriteLine(Exp.InnerException.Message);

            return false;
        }

        return true;

I have adapted this from another bit of code another developer has in a different application. The main difference thus far is he is passing in an array (of type object) of dataadaptors and has each of the three DataSets (globally instantiated) set up as individual if blocks inside the foreach (ParameterInfo oPI in oParamaterInfoArray) block (where my 'NameOfDataSet' would be one of the datasets)

Can anybody give me a little push (or a shove?) in the direction of finishing this function up? I know I am right there but it feels like I am over looking something. This code does compile without error.

Answer 1

Do you really want reflection to be used that much in your DAL? Perhaps an ORM such as LINQ to SQL or NHibernate would be a good alternative?

Answer 2

Can't you just treat them as their base classes, DbDataAdapter, DataSet and DataTable?

You can access the table by name by doing DataSet.Tables["name"]. This returns a DataTable object that you can pass to the DbDataAdapters update method.

Or if your TableAdapter updates all the tables in your DataSet then you can pass the entire DataSet to the update method directly.

With that said I would suggest you rethink the use of typed data sets if you have the chance to do so. In my experience they end up being a hassle to maintain and use and have found the general DataTable, DataSet and DbDataAdapter classes to be much easier to use directly.

Answer 3

I've been using this. It would need some optimizations though. This also takes care of updating the tables in correct order depending on the relations in dataset (in case there are no self-references, which can be handled by sorting the rows, but for simplicity I'm not posting it here).

    public static void Save(DataSet data, SqlConnection connection)
    {
        /// Dictionary for associating adapters to tables.
        Dictionary<DataTable, SqlDataAdapter> adapters = new Dictionary<DataTable, SqlDataAdapter>();

        foreach (DataTable table in data.Tables)
        {
            /// Find the table adapter using Reflection.
            Type adapterType = GetTableAdapterType(table);
            SqlDataAdapter adapter = SetupTableAdapter(adapterType, connection, validityEnd);
            adapters.Add(table, adapter);
        }

        /// Save the data.
        Save(data, adapters);
    }

    static Type GetTableAdapterType(DataTable table)
    {
        /// Find the adapter type for the table using the namespace conventions generated by dataset code generator.
        string nameSpace = table.GetType().Namespace;
        string adapterTypeName = nameSpace + "." + table.DataSet.DataSetName + "TableAdapters." + table.TableName + "TableAdapter";
        Type adapterType = Type.GetType(adapterTypeName);
        return adapterType;
    }

    static SqlDataAdapter SetupTableAdapter(Type adapterType, SqlConnection connection)
    {
        /// Set connection to TableAdapter and extract SqlDataAdapter (which is private anyway).
        object adapterObj = Activator.CreateInstance(adapterType);
        SqlDataAdapter sqlAdapter = (SqlDataAdapter)GetPropertyValue(adapterType, adapterObj, "Adapter");
        SetPropertyValue(adapterType, adapterObj, "Connection", connection);

        return sqlAdapter;
    }

    static object GetPropertyValue(Type type, object instance, string propertyName)
    {
        return type.GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance).GetValue(instance, null);
    }

    static void SetPropertyValue(Type type, object instance, string propertyName, object propertyValue)
    {
        type.GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.Instance).SetValue(instance, propertyValue, null);
    }

    static void Save(DataSet data, Dictionary<DataTable, SqlDataAdapter> adapters)
    {
        if (data == null)
            throw new ArgumentNullException("data");

        if (adapters == null)
            throw new ArgumentNullException("adapters");

        Dictionary<DataTable, bool> procesedTables = new Dictionary<DataTable, bool>();
        List<DataTable> sortedTables = new List<DataTable>();

        while (true)
        {
            DataTable rootTable = GetRootTable(data, procesedTables);
            if (rootTable == null)
                break;

            sortedTables.Add(rootTable);
        }

        /// Updating Deleted rows in Child -> Parent order.
        for (int i = sortedTables.Count - 1; i >= 0; i--)
        {
            Update(adapters, sortedTables[i], DataViewRowState.Deleted);
        }

        /// Updating Added / Modified rows in Parent -> Child order.
        for (int i = 0; i < sortedTables.Count; i++)
        {
            Update(adapters, sortedTables[i], DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
        }
    }

    static void Update(Dictionary<DataTable, SqlDataAdapter> adapters, DataTable table, DataViewRowState states)
    {
        SqlDataAdapter adapter = null;

        if (adapters.ContainsKey(table))
            adapter = adapters[table];

        if (adapter != null)
        {
            DataRow[] rowsToUpdate = table.Select("", "", states);

            if (rowsToUpdate.Length > 0)
                adapter.Update(rowsToUpdate);
        }
    }

    static DataTable GetRootTable(DataSet data, Dictionary<DataTable, bool> procesedTables)
    {
        foreach (DataTable table in data.Tables)
        {
            if (!procesedTables.ContainsKey(table))
            {
                if (IsRootTable(table, procesedTables))
                {
                    procesedTables.Add(table, false);
                    return table;
                }
            }
        }

        return null;
    }

    static bool IsRootTable(DataTable table, Dictionary<DataTable, bool> procesedTables)
    {
        foreach (DataRelation relation in table.ParentRelations)
        {
            DataTable parentTable = relation.ParentTable;
            if (parentTable != table && !procesedTables.ContainsKey(parentTable))
                return false;
        }

        return true;
    }