Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / VB
 
Print

Data Access using Dynamics - Part II: The DynamicDataSet

5.00/5 (1 vote)
31 May 2011CPOL3 min read 17.7K   174  
How to create a dynamic version of another staple component of the ADO.NET framework, namely the DataSet.

Introduction

In my previous article, I demonstrated how to leverage the Dynamic Language Runtime (DLR) to create a flexible, dynamic wrapper for the ADO.NET DbDataReader class and its subclasses.

In this article, we are going to look at creating a dynamic version of another staple component of the ADO.NET framework, namely the DataSet. Again, I'm sure many seasoned .NET developers will recognize the following hypothetical example:

// C#
using (SqlConnection connection = 
new SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString))
{
    string sql = "SELECT SomeColumn, AnotherColumn FROM SomeTable";
    SqlCommand command = new SqlCommand(sql, connection);
    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet, "SomeTable");
    foreach (DataRow row in dataSet.Tables["SomeTable"].Rows)
    {
        int foo = (int)row["SomeColumn"];
        string bar = (string)row["AnotherColumn"];
        // Do some stuff with the data.
    }
}

The Dynamic DataSet and its Components

Before we look at the code for the DynamicDataSet class and all its component classes, we first need to extend the DynamicDataObjectWrapper class we looked at in the previous article:

// C#
public abstract class DynamicListSourceDataObjectWrapper<T> : DynamicDataObjectWrapper<T>, IListSource
    where T : IListSource
{
    public DynamicListSourceDataObjectWrapper(T obj)
        : base(obj)
    {
    }

    public virtual bool ContainsListCollection
    {
        get { return Obj.ContainsListCollection; }
    }

    public virtual IList GetList()
    {
        return Obj.GetList();
    }
}

As you can see, this class implements the IListSource, which will enable us to use our DynamicDataSet and DynamicDataTable classes with the standard ASP.NET data controls.

Now, let's look at the code for the DynamicDataSet class:

Shrink ▲   
// C#
public class DynamicDataSet : DynamicListSourceDataObjectWrapper<DataSet>
{
    public DynamicDataSet()
        : this(new DataSet())
    {
    }

    public DynamicDataSet(DataSet dataSet)
        : base(dataSet)
    {
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        if (base.TryGetMember(binder, out result))
            return true;
        else
        {
            try
            {
                if (Obj.Tables.Contains(binder.Name))
                    result = (DynamicDataTable)Obj.Tables[binder.Name];
                else
                    result = (DynamicDataTable)Obj.Tables.Add(binder.Name);
                return true;
            }
            catch (Exception)
            {
                result = null;
                return false;
            }
        }
    }

    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        if (base.TrySetMember(binder, value))
            return true;
        else
        {
            try
            {
                dynamic table = value;
                table.TableName = binder.Name;
                Obj.Tables.Add(table);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public override bool TryGetIndex(GetIndexBinder binder, object[] indexes, out object result)
    {
        try
        {
            object index = indexes[0];
            if (index is int)
            {
                result = (DynamicDataTable)Obj.Tables[(int)index];
                return true;
            }
            else if (index is string)
            {
                result = (DynamicDataTable)Obj.Tables[(string)index];
                return true;
            }
            else
            {
                result = null;
                return false;
            }
        }
        catch (Exception)
        {
            result = null;
            return false;
        }
    }

    public static implicit operator DataSet(DynamicDataSet dataSet)
    {
        return dataSet.Obj;
    }

    public static explicit operator DynamicDataSet(DataSet dataSet)
    {
        return new DynamicDataSet(dataSet);
    }
}

Notice how we provide overrides for both the TryGetMember() and TrySetMember() methods which will allow us not only to select a table from our data set via dynamic properties, but also to create a new table. The TryGetIndex() and TrySetIndex() overrides allow us to do the same thing using indexers.

As with the DynamicDataReader class from the previous article, we also provide a pair of conversion operators for easy conversion between the static object and its dynamic wrapper. This is a pattern we will follow throughout this exercise.

Now it is time to take a look at the DynamicDataTable class. Like the DynamicDataSet, it inherits from DynamicListSourceDataObjectWrapper, allowing it to be used with ASP.NET data controls. We override the TryGetMember() and TrySetMember() methods in such a way that accessing a dynamic property will return the appropriate column from the table; and setting a dynamic property will create a new column in the table:

Shrink ▲   
// C#
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
    if (base.TryGetMember(binder, out result))
        return true;
    else
    {
        try
        {
            result = Obj.Columns[binder.Name];
            return true;
        }
        catch
        {
            result = null;
            return false;
        }
    }
}

public override bool TrySetMember(SetMemberBinder binder, object value)
{
    if (base.TrySetMember(binder, value))
        return true;
    else
    {
        try
        {
            Type columnType = (Type)value;
            Obj.Columns.Add(binder.Name, columnType);
            return true;
        }
        catch(Exception)
        {
            return false;
        }
    }
}

With the TryGetIndex(), however, we are going to be slightly more creative. If the indexer is accessed via an integer, then we return the respective row from the table. On the other hand, if the indexer is accessed via a string, then we return the respective column from the table:

Shrink ▲   
// C#
public override bool TryGetIndex(GetIndexBinder binder, object[] indexes, out object result)
{
    try
    {
        object index = indexes[0];
        if (index is int)
        {
            result = (DynamicDataRow)Obj.Rows[(int)index];
            return true;
        }
        else if (index is string)
        {
            result = Obj.Columns[(string)index];
            return true;
        }
        else
        {
            result = null;
            return false;
        }
    }
    catch (Exception)
    {
        result = null;
        return false;
    }
}

The DynamicDataTable also implements the IEnumerable interface so that we can easily iterate through all the rows in the table without having to explicitly call the Rows property:

Shrink ▲   
// C#
public IEnumerator GetEnumerator()
{
    return new DynamicDataTableEnumerator(Obj.Rows.GetEnumerator());
}

private class DynamicDataTableEnumerator : IEnumerator
{
    private IEnumerator enumerator;

    public DynamicDataTableEnumerator(IEnumerator enumerator)
    {
        this.enumerator = enumerator;
    }

    public object Current
    {
        get { return (DynamicDataRow)(DataRow)enumerator.Current; }
    }

    public bool MoveNext()
    {
        return enumerator.MoveNext();
    }

    public void Reset()
    {
        enumerator.Reset();
    }
}

This finally leaves the DynamicDataRow class. Here we override the TryGetMember() and TrySetMember() methods to get and set the appropriate column value respectively, using the indexers of the wrapped DataRow object. We also override the TryGetIndex() and TrySetIndex() methods so we still have the option of using the indexers if we wish. For the sake of brevity, I've omitted the code, as it is very similar to what we've seen already.

Now for Some Examples

Here are some examples of our DynamicDataSet in action. As in the previous article, they all use the Northwind database are not intended to serve as examples of good data-access practice. Firstly, selecting from a database table and populating a data object:

Shrink ▲   
// C#
public static Employee[] GetEmployees()
{
    List<Employee> employees = new List<Employee>();
    using (SqlConnection connection = new SqlConnection
       (WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
    {
        string query = "SELECT EmployeeID, LastName, FirstName, Title, 
                        TitleOfCourtesy, BirthDate, HireDate, Address, City, 
                        Region, PostalCode, Country, HomePhone, Extension, 
                        Photo, Notes, ReportsTo, PhotoPath " +
                       "FROM dbo.Employees";
        SqlCommand command = new SqlCommand(query, connection);
        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        dynamic dataSet = new DynamicDataSet();
        dataAdapter.Fill(dataSet, "Staff");
        foreach (dynamic row in dataSet.Staff)
        {
            Employee employee = new Employee()
            {
                Id = row.EmployeeID,
                Surname = row.LastName,
                FirstName = row.FirstName,
                Title = row.Title,
                CourtesyTitle = row.TitleOfCourtesy,
                DateOfBirth = row.BirthDate,
                DateHired = row.HireDate,
                Address = row.Address,
                City = row.City,
                Region = row.Region,
                PostCode = row.PostalCode,
                Country = row.Country,
                HomePhone = row.HomePhone,
                Extension = row.Extension,
                Photo = row.Photo,
                Notes = row.Notes,
            };
            employees.Add(employee);
        }
    }
    return employees.ToArray();
}

The following example shows how to create a new table in a dataset and use it to insert its values into a table in the database:

ASP.NET
Shrink ▲   
<!-- ASP.NET -->
<form id="form1" runat="server">
<div>
    <h2>
        Insert Example</h2>
    <div>
        <table>
            <tbody>
                <tr>
                    <th>
                        First Name:
                    </th>
                    <td>
                        <asp:TextBox ID="firstNameTextBox" runat="server" />
                    </td>
                </tr>
                <tr>
                    <th>
                        Surname:
                    </th>
                    <td>
                        <asp:TextBox ID="surnameTextBox" runat="server" />
                    </td>
                </tr>
                <tr>
                    <th>
                        Home Phone:
                    </th>
                    <td>
                        <asp:TextBox ID="homePhoneTextBox" runat="server" />
                    </td>
                </tr>
                <tr>
                    <th>
                        Extension:
                    </th>
                    <td>
                        <asp:TextBox ID="extensionTextBox" runat="server" />
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    <div>
        <asp:Button ID="createButton" runat="server" Text="Create" OnClick="createButton_Click" />
        <asp:Label ID="resultLabel" runat="server" ForeColor="Red" />
    </div>
</div>
</form>
Shrink ▲   
// C#
protected void createButton_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection
         (WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
    {
        string insertQuery = "INSERT INTO dbo.Employees
        (FirstName, LastName, HomePhone, Extension) " +
                             "VALUES(@FirstName, @LastName, @HomePhone, @Extension)";
        SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
        insertCommand.Parameters.Add("@FirstName", 
        SqlDbType.NVarChar, 10).SourceColumn = "FirstName";
        insertCommand.Parameters.Add("@LastName", 
        SqlDbType.NVarChar, 20).SourceColumn = "LastName";
        insertCommand.Parameters.Add("@HomePhone", 
        SqlDbType.NVarChar, 24).SourceColumn = "HomePhone";
        insertCommand.Parameters.Add("@Extension", 
        SqlDbType.NVarChar, 4).SourceColumn = "Extension";
        SqlDataAdapter dataAdapter = new SqlDataAdapter() 
        { InsertCommand = insertCommand };
        dynamic dataSet = new DynamicDataSet();
        dataSet.Employees.EmployeeID = typeof(int);
        dataSet.Employees.FirstName = typeof(string);
        dataSet.Employees.LastName = typeof(string);
        dataSet.Employees.HomePhone = typeof(string);
        dataSet.Employees.Extension = typeof(string);
        dynamic newRow = dataSet.Employees.NewRow();
        newRow.FirstName = firstNameTextBox.Text;
        newRow.LastName = surnameTextBox.Text;
        newRow.HomePhone = homePhoneTextBox.Text;
        newRow.Extension = extensionTextBox.Text;
        dataSet.Employees.Rows.Add(newRow);
        dataAdapter.Update(dataSet.Employees);
    }
    resultLabel.Text = "Item created!";
}

The following example shows how to use the DynamicDataSet to update a database table:

ASP.NET
Shrink ▲   
<!-- ASP.NET-->
<form id="form1" runat="server">
<div>
    <h2>
        Update Example</h2>
    <div>
        <asp:DropDownList ID="employeeDropDown" 
        runat="server" AppendDataBoundItems="True"
            DataTextField="LastName" 
            DataValueField="EmployeeId" AutoPostBack="true" 
            OnSelectedIndexChanged="employeeDropDown_SelectedIndexChanged">
            <asp:ListItem Text="Please select..." />
        </asp:DropDownList>
    </div>
    <div>
        <table>
            <tbody>
                <tr>
                    <th>
                        Home Phone:
                    </th>
                    <td>
                        <asp:TextBox ID="homePhoneTextBox" 
                        runat="server" />
                    </td>
                </tr>
                <tr>
                    <th>
                        Extension:
                    </th>
                    <td>
                        <asp:TextBox ID="extensionTextBox" 
                        runat="server" />
                    </td>
                </tr>
            </tbody>
        </table>
        <asp:Button ID="submitButton" runat="server" 
        Text="Change" OnClick="submitButton_Click" />
    </div>
    <asp:Label ID="resultLabel" runat="server" 
    ForeColor="Red" />
</div>
</form>
Shrink ▲   
// C#
protected void submitButton_Click(object sender, EventArgs e)
{
    int employeeId = int.Parse(employeeDropDown.SelectedValue);
    string homePhone = homePhoneTextBox.Text;
    string extension = extensionTextBox.Text;
    ChangeContactNumbers(employeeId, homePhone, extension);
    resultLabel.Text = "Contact Details Changed";
}

private void ChangeContactNumbers(int employeeId, string homePhone, string extension)
{
    using (SqlConnection connection = new SqlConnection
          (WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
    {
        string selectQuery = "SELECT EmployeeID, HomePhone, Extension " +
                             "FROM dbo.Employees " +
                             "WHERE EmployeeID = @EmployeeID";
        string updateQuery = "UPDATE dbo.Employees " +
                             "SET HomePhone = @HomePhone, " +
                             "Extension = @Extension " +
                             "WHERE EmployeeID = @EmployeeID";
        SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
        selectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employeeId;
        SqlCommand updateCommand = new SqlCommand(updateQuery, connection);
        updateCommand.Parameters.Add("@EmployeeID", 
        SqlDbType.Int).SourceColumn = "EmployeeID";
        updateCommand.Parameters.Add("@HomePhone", 
        SqlDbType.NVarChar, 24).SourceColumn = "HomePhone";
        updateCommand.Parameters.Add("@Extension", 
        SqlDbType.NVarChar, 4).SourceColumn = "Extension";
        SqlDataAdapter dataAdapter = new SqlDataAdapter() 
                { SelectCommand = selectCommand, UpdateCommand = updateCommand };
        dynamic dataSet = new DynamicDataSet();
        dataAdapter.Fill(dataSet, "Employees");
        dataSet.Employees[0].HomePhone = homePhone;
        dataSet.Employees[0].Extension = extension;
        dataAdapter.Update(dataSet.Employees);
    }
}

Finally, here is an example of how to delete a row from our DynamicDataTable:

ASP.NET
<!-- ASP.NET -->
<form id="form1" runat="server">
<div>
    <h2>
        Delete Example</h2>
    <div>
        <asp:DropDownList ID="employeeDropDown" 
        runat="server" AppendDataBoundItems="True"
            DataTextField="LastName" DataValueField="EmployeeId">
        </asp:DropDownList>
         <asp:Button ID="deleteButton" 
         runat="server" OnClick="deleteButton_Click" 
         Text="Delete" />
    </div>
    <div>
        <asp:Label ID="resultLabel" runat="server" 
        ForeColor="Red" />
    </div>
</div>
</form>
// C#
protected void deleteButton_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection
          (WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
    {
        string selectQuery = "SELECT EmployeeID, LastName " +
                             "FROM dbo.Employees " +
                             "WHERE EmployeeID = @EmployeeID";
        string deleteQuery = "DELETE FROM dbo.Employees " +
                             "WHERE EmployeeID = @EmployeeID";
        SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
        selectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 
                                      int.Parse(employeeDropDown.SelectedValue);
        SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection);
        deleteCommand.Parameters.Add("@EmployeeID", 
        SqlDbType.Int).SourceColumn = "EmployeeID";
        SqlDataAdapter dataAdapter = new SqlDataAdapter() 
                { SelectCommand = selectCommand, DeleteCommand = deleteCommand };
        dynamic dataSet = new DynamicDataSet();
        dataAdapter.Fill(dataSet, "Employees");
        dataSet.Employees[0].Delete();
        dataAdapter.Update(dataSet.Employees);
    }
    resultLabel.Text = "Item deleted.";
}

Summary

The DynamicDataSet and DynamicDataTable classes provide a loosely-typed means of data access which improves code readability and can be used with syntax which is an approximation to that found with strongly-typed datasets, but without the need to auto-generate the code beforehand.

The source code for this and the previous article can be found here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2011 by MBigglesworth79
Everything else Copyright © CodeProject, 1999-2025

Web01 2.8:2024-12-08:1