C# – Implement Generic DataSet Builder with C#

cdesign-patternsgenerics

I want to create a data access library that can build a DataSets with relations which can easily be written to XML with dataset.WriteXML(). This is a get to know C# endeavor that will hopefully gain me some productivity as well (lots of converting relational tables to XML from different data sources for document generation)

So far the only difference I see between the Data Access technologies (SQL, OLEDB, ODBC) with regards to how I will use them for this is that they require a type specified Connection and Adapter (SqlAdapter, OleDbAdapter, OdbcAdapter, etc).

So in my mind I envision classes with two methods and a public data set that will be filled. 

public DataSet DataSet { get; set; }

public void InsertTables(string ConnectionString, string[] TableNames, string[] Commands)

public void AddRelations(string[] PrimaryTables, string[] PrimaryKeys, string[] ChildTables, string[] ForeignKeys, bool[] NestingRules)

I already started with an OleDb Implementation that works well, and I want to set up something similar for other Data Access technologies. However, I want to be as efficient as possible with the code so am looking for advice on how to accomplish.

I was thinking that the Template Method Design pattern could be a solid approach, but then I also thought that a single class that utilizes generics might work as well (I am new to C# and not that familiar with them).

I am looking for a general example of how I could accomplish this with a good design pattern and/or generics.

Here is what I have for the OleDbDesign. Any advice is greatly appreciated.

 public class OleDbDataSetBuilder
{
    private DataSet _DataSet;

    public DataSet DataSet { get { return _DataSet; } }

    public OleDbDataSetBuilder(string DataSetName)
    {
        this._DataSet = new DataSet(DataSetName);
    }

    public void InsertTables(string ConnectionString, string[] TableNames, string[] Commands)
    {

        if (TableNames.Length != Commands.Length)
        {
            throw new Exception("Error: Must provide a table name for each command.");
        }

        OleDbConnection cn = new OleDbConnection(ConnectionString);

        OleDbDataAdapter adapter = new OleDbDataAdapter("", cn);

        adapter.SelectCommand = new OleDbCommand("", cn);

        for (int i = 0; i < TableNames.Length; i++)
        {
            adapter.SelectCommand.CommandText = Commands[i];

            adapter.Fill(_DataSet, TableNames[i]);
        }

        cn.Close();

    }

    public void AddRelations(string[] PrimaryTables, string[] PrimaryKeys, string[] ChildTables, string[] ForeignKeys, bool[] NestingRules)
    {

        for (int i = 0; i < PrimaryTables.Length; i++)
        {
            DataColumn pk = _DataSet.Tables[PrimaryTables[i]].Columns[PrimaryKeys[i]];

            DataColumn fk = _DataSet.Tables[ChildTables[i]].Columns[ForeignKeys[i]];

            DataRelation relation = _DataSet.Relations.Add(pk, fk);

            relation.Nested = NestingRules[i];
        }
    }
}

Best Answer

Here is a link describing this approach.

Below is my resultant class based on Dan's suggestion

using System;
using System.Data;
using System.Data.Common;

namespace CustomDataAccess{

public class DataSetBuilder
{

    #region Properties

    private DataSet _DataSet;

    public DataSet DataSet { get { return _DataSet; } }

    #endregion

    #region Constructors

    public DataSetBuilder()
    {
        this._DataSet = new DataSet();
    }

    public DataSetBuilder(string DataSetName)
    {
        this._DataSet = new DataSet(DataSetName);
    }

    public DataSetBuilder(DataSet DataSet)
    {
        this._DataSet = DataSet;
    }

    #endregion

    #region Public Methods

    public DataSetBuilder InsertTables(DataTable Table)
    {
        this._DataSet.Tables.Add(Table);

        return this;
    }

    public DataSetBuilder InsertTables(string DbProviderName, string ConnectionString, string TableName, string CommandText)
    {
        System.Data.Common.DbDataAdapter adapter = Create_Adapter(DbProviderName, ConnectionString);

        Fill_Adapter(adapter, TableName, CommandText);

        adapter.SelectCommand.Connection.Close();

        return this;
    }

    public DataSetBuilder InsertTables(string DbProviderName, string ConnectionString, string[] TableName, string[] CommandText)
    {
        if (TableName.Length != CommandText.Length)
        {
            throw new Exception("Error: Must provide a table name for each command.");
        }

        System.Data.Common.DbDataAdapter adapter = Create_Adapter(DbProviderName, ConnectionString);

        for (int i = 0; i < TableName.Length; i++)
        {

            Fill_Adapter(adapter, TableName[i], CommandText[i]);
        }

        adapter.SelectCommand.Connection.Close();

        return this;
    }

    public void AddRelations(string ParentTable, string PrimaryKey, string ChildTable, string ForeignKey, bool NestingRule)
    {
        Add_Relations(ParentTable, PrimaryKey, ChildTable, ForeignKey, NestingRule);
    }

    public void AddRelations(string[] ParentTable, string[] PrimaryKey, string[] ChildTable, string[] ForeignKey, bool[] NestingRule)
    {

        for (int i = 0; i < ParentTable.Length; i++)
        {
            Add_Relations(ParentTable[i], PrimaryKey[i], ChildTable[i], ForeignKey[i], NestingRule[i]);
        }
    }

    #endregion

    #region Private Methods

    private System.Data.Common.DbDataAdapter Create_Adapter(string DbProviderName, string ConnectionString)
    {
        DbProviderFactory dbFactory = System.Data.Common.DbProviderFactories.GetFactory(DbProviderName);

        System.Data.Common.DbConnection connection = dbFactory.CreateConnection();

        connection.ConnectionString = ConnectionString;

        connection.Open();

        System.Data.Common.DbCommand command = dbFactory.CreateCommand();

        command.Connection = connection;

        System.Data.Common.DbDataAdapter adapter = dbFactory.CreateDataAdapter();

        adapter.SelectCommand = command;

        return adapter;
    }

    private void Fill_Adapter(System.Data.Common.DbDataAdapter Adapter, string TableName, string CommandText)
    {
        Adapter.SelectCommand.CommandText = CommandText;

        Adapter.Fill(_DataSet, TableName);
    }

    private void Add_Relations(string ParentTable, string PrimaryKey, string ChildTable, string ForeignKey, bool NestingRule)
    {
        DataColumn pk = _DataSet.Tables[ParentTable].Columns[PrimaryKey];

        DataColumn fk = _DataSet.Tables[ChildTable].Columns[ForeignKey];

        DataRelation relation = _DataSet.Relations.Add(pk, fk);

        relation.Nested = NestingRule;
    }

    #endregion

}}
Related Topic