1

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];
        }
    }
}
  • 1
    You could take a look at the System.Data.Common.DbProviderFactory class to create connections, commands, and data adapters, and use the base System.Data.Common objects instead of using Ole* objects directly. – Dan Lyons Jul 3 '14 at 22:57
  • This turned out to be the winning approach. Thank You! Would you like to propose as answer? I am going to post the completed class as well. – wesmantooth Jul 4 '14 at 18:05
-1

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

}}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.