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 recognise 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.
}
}
' Visual Basic
Using connection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString)
Dim sql As String = "SELECT SomeColumn, AnotherColumn FROM SomeTable"
Dim command As SqlCommand = New SqlCommand(sql, connection)
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(command)
Dim dataSet As DataSet = New DataSet()
dataAdapter.Fill(dataSet, "SomeTable")
For Each row As DataRow In dataSet.Tables("SomeTable").Rows
Dim foo As Integer = DirectCast(row("SomeColumn"), Integer)
Dim bar As String = DirectCast(row("AnotherColumn"), Integer)
' Do some stuff with the data.
Next
End Using
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();
}
}
' Visual Basic
Public MustInherit Class DynamicListSourceDataObjectWrapper(Of T As IListSource)
Inherits DynamicDataObjectWrapper(Of T)
Implements IListSource
Public Sub New(ByVal obj As T)
MyBase.New(obj)
End Sub
Public ReadOnly Property ContainsListCollection As Boolean Implements IListSource.ContainsListCollection
Get
Return Obj.ContainsListCollection
End Get
End Property
Public Function GetList() As System.Collections.IList Implements IListSource.GetList
Return Obj.GetList()
End Function
End Class
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 a the code for the DynamicDataSet class:
// 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);
}
}
' Visual Basic
Public Class DynamicDataSet
Inherits DynamicListSourceDataObjectWrapper(Of DataSet)
Public Sub New()
Me.New(New DataSet())
End Sub
Public Sub New(ByVal dataSet As DataSet)
MyBase.New(dataSet)
End Sub
Public Overrides Function TryGetMember(binder As GetMemberBinder, ByRef result As Object) As Boolean
If MyBase.TryGetMember(binder, result) Then
Return True
Else
Try
If Obj.Tables.Contains(binder.Name) Then
result = CType(Obj.Tables(binder.Name), DynamicDataTable)
Else
result = CType(Obj.Tables.Add(binder.Name), DynamicDataTable)
End If
Return True
Catch ex As Exception
result = Nothing
Return False
End Try
End If
End Function
Public Overrides Function TrySetMember(binder As SetMemberBinder, value As Object) As Boolean
If MyBase.TrySetMember(binder, value) Then
Return True
Else
Try
Dim table As Object = value
table.TableName = binder.Name
Obj.Tables.Add(table)
Return True
Catch ex As Exception
Return False
End Try
End If
End Function
Public Overrides Function TryGetIndex(binder As GetIndexBinder, indexes() As Object, ByRef result As Object) As Boolean
If MyBase.TryGetIndex(binder, indexes, result) Then
Return True
Else
Try
Dim index As Object = indexes(0)
If TypeOf (index) Is Integer Then
result = CType(Obj.Tables(DirectCast(index, Integer)), DynamicDataTable)
Return True
ElseIf TypeOf (index) Is String Then
result = CType(Obj.Tables(DirectCast(index, String)), DynamicDataTable)
Return True
Else
result = Nothing
Return False
End If
Catch ex As Exception
result = Nothing
Return False
End Try
End If
End Function
Public Shared Widening Operator CType(ByVal dataSet As DynamicDataSet) As DataSet
Return dataSet.Obj
End Operator
Public Shared Narrowing Operator CType(ByVal dataSet As DataSet) As DynamicDataSet
Return New DynamicDataSet(dataSet)
End Operator
End Class
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:
// 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;
}
}
}
' Visual Basic
Public Overrides Function TryGetMember(binder As GetMemberBinder, ByRef result As Object) As Boolean
If MyBase.TryGetMember(binder, result) Then
Return True
Else
Try
result = Obj.Columns(binder.Name)
Return True
Catch ex As Exception
result = Nothing
Return False
End Try
End If
End Function
Public Overrides Function TrySetMember(binder As SetMemberBinder, value As Object) As Boolean
If MyBase.TrySetMember(binder, value) Then
Return True
Else
Try
Dim columnType As Type = DirectCast(value, Type)
Obj.Columns.Add(binder.Name, columnType)
Return True
Catch ex As Exception
Return False
End Try
End If
End Function
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:
// 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;
}
}
' Visual Basic
Public Overrides Function TryGetIndex(binder As GetIndexBinder, indexes() As Object, ByRef result As Object) As Boolean
If MyBase.TryGetIndex(binder, indexes, result) Then
Return True
Else
Try
Dim index As Object = indexes(0)
If TypeOf (index) Is Integer Then
result = CType(Obj.Rows(DirectCast(index, Integer)), DynamicDataRow)
Return True
ElseIf TypeOf (index) Is String Then
result = Obj.Columns(DirectCast(index, String))
Return True
Else
result = Nothing
Return False
End If
Catch ex As Exception
result = Nothing
Return False
End Try
End If
End Function
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:
// 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();
}
}
' Visual Basic
Public Function GetEnumerator() As IEnumerator Implements IEnumerable.GetEnumerator
Return New DynamicDataTableEnumerator(Obj.Rows.GetEnumerator())
End Function
Private Class DynamicDataTableEnumerator
Implements IEnumerator
Private _enumerator As IEnumerator
Public Sub New(ByVal enumerator As IEnumerator)
_enumerator = enumerator
End Sub
Public ReadOnly Property Current As Object Implements IEnumerator.Current
Get
Return CType(DirectCast(_enumerator.Current, DataRow), DynamicDataRow)
End Get
End Property
Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
Return _enumerator.MoveNext()
End Function
Public Sub Reset() Implements IEnumerator.Reset
_enumerator.Reset()
End Sub
End Class
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:
// 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();
}
' Visual Basic
Public Shared Function GetEmployees() As Employee()
Dim employees As List(Of Employee) = New List(Of Employee)
Using connection As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim query As String = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath " + _
"FROM dbo.Employees"
Dim command As SqlCommand = New SqlCommand(query, connection)
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(command)
Dim dataSet As DynamicDataSet = New DynamicDataSet
dataAdapter.Fill(dataSet, "Staff")
For Each row As Object In DirectCast(dataSet, Object).Staff
Dim employee As Employee = New Employee() With _
{ _
.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)
Next
End Using
Return employees.ToArray()
End Function
The following example shows how to create a new table in a data set and use it to insert the its values into a table in the database:
<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>
// 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!";
}
' Visual Basic
Protected Sub createButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles createButton.Click
Using connection As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim insertQuery As String = "INSERT INTO dbo.Employees(FirstName, LastName, HomePhone, Extension) " + _
"VALUES(@FirstName, @LastName, @HomePhone, @Extension)"
Dim insertCommand As SqlCommand = 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"
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter() With {.InsertCommand = insertCommand}
Dim dataSet As Object = New DynamicDataSet()
dataSet.Employees.EmployeeID = GetType(Integer)
dataSet.Employees.FirstName = GetType(String)
dataSet.Employees.LastName = GetType(String)
dataSet.Employees.HomePhone = GetType(String)
dataSet.Employees.Extension = GetType(String)
Dim newRow As Object = 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)
End Using
resultLabel.Text = "Item created!"
End Sub
The following example shows how to use the DynamicDataSet to update a database table:
<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>
// 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);
}
}
' Visual Basic
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles submitButton.Click
Dim employeeId As Integer = Integer.Parse(employeeDropDown.SelectedValue)
Dim homePhone As String = homePhoneTextBox.Text
Dim extension As String = extensionTextBox.Text
ChangeContactNumbers(employeeId, homePhone, extension)
resultLabel.Text = "Contact Details Changed"
End Sub
Private Sub ChangeContactNumbers(ByVal employeeId As Integer, homePhone As String, ByVal extension As String)
Using connection As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim selectQuery As String = "SELECT EmployeeID, HomePhone, Extension " + _
"FROM dbo.Employees " + _
"WHERE EmployeeID = @EmployeeID"
Dim updateQuery As String = "UPDATE dbo.Employees " + _
"SET HomePhone = @HomePhone, " + _
"Extension = @Extension " + _
"WHERE EmployeeID = @EmployeeID"
Dim selectCommand As SqlCommand = New SqlCommand(selectQuery, connection)
selectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employeeId
Dim updateCommand As SqlCommand = 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"
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter() With {.SelectCommand = selectCommand, .UpdateCommand = updateCommand}
Dim dataSet As Object = New DynamicDataSet()
dataAdapter.Fill(DirectCast(dataSet, DynamicDataSet), "Employees")
dataSet.Employees(0).HomePhone = homePhone
dataSet.Employees(0).Extension = extension
dataAdapter.Update(dataSet.Employees)
End Using
End Sub
Finally, here is an example of how to delete a row from our DynamicDataTable:
<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.";
}
' Visual Basic
Protected Sub deleteButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles deleteButton.Click
Using connection As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
Dim selectQuery As String = "SELECT EmployeeID, LastName " + _
"FROM dbo.Employees " + _
"WHERE EmployeeID = @EmployeeID"
Dim deleteQuery As String = "DELETE FROM dbo.Employees " + _
"WHERE EmployeeID = @EmployeeID"
Dim selectCommand As SqlCommand = New SqlCommand(selectQuery, connection)
selectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = Integer.Parse(employeeDropDown.SelectedValue)
Dim deleteCommand As SqlCommand = New SqlCommand(deleteQuery, connection)
deleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).SourceColumn = "EmployeeID"
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter() With {.SelectCommand = selectCommand, .DeleteCommand = deleteCommand}
Dim dataSet As Object = New DynamicDataSet()
dataAdapter.Fill(DirectCast(dataSet, DynamicDataSet), "Employees")
dataSet.Employees(0).Delete()
dataAdapter.Update(dataSet.Employees)
End Using
resultLabel.Text = "Item deleted."
End Sub
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.
