Previous parts of this
article can be read at:
Part 1,
Part 2,
Part 3,
Part 4
Part four of this article demonstrated how to
to centralize CRUD operations (create, retrieve, update, and delete
database data) in a DAL component. In addition, part four of the article
explained how to use Microsoft.NET 2.0 partial types to modify and
extend the behavior of the DataSet.
This part of the article explains:
How to create a custom
BindingSource class that can be used to bind a DataTable in the DAL
to a Windows Forms controls such as a DataGridView.
How to bind a custom BindingSource
class to a DataGridView.
How to extend a TableAdapter by
adding additional queries to it.
How to modify and/or extend a
TableAdapter via its partial class.
Create Custom BindingSource Classes
In Windows Forms 2.0, the BindingSource
class was added to the System.Windows.Forms namespace. The BindingSource
class simplifies binding controls on a form to data by providing
currency management, change notification, and other services between
Windows Forms controls and data sources. In addition, the BindingSource
component can act as a strongly typed data source.
Those who used the DataView class in
.NET 1.x will find it worthwhile to compare the similarity of the
BindingSource class methods with the DataView class methods. In most
cases, the BindingSource class should be preferred over the DataSet
class for binding in Windows Forms 2.0 applications.
To bind tables from the DAL object
which is declared and instantiated in the sample Windows Forms'
MyApplicationExtensions.vb file, custom BindingSource classes must be
added to the sample Windows Forms project. While they could be declared
elsewhere, adding them to the MyApplicationExtensions.vb file is
convenient and centralizes all custom BindingSource classes in one well
known place.
Custom BindingSource classes must be
declared out side the Namespace My declaration in the file:
' GetDotNetCode MyApplication
Extensions
' Wrapping it in the My namespace.
' This will make Public members
available
' available in intellisense when
' a developer types 'My.Application.[public
members will pop up here.]
Namespace
My
Partial
Class MyApplication
......
......
End
Class
End
Namespace
Add custom binding sources here...
A simple custom BindingSource class can be
created by declaring a new class that inherits from the Binding Source class in
the System.Windows.Forms namespace and adding only a constructor ('New') method
to the custom class. Here is the code that was added to the sample Windows
Application's MyApplicationExtensions.vb file to declare a custom BindingSource
class for each of the DataTables in the DAL component.
#Region
"Custom BindingSource Classes"
' AjaxCustomer Binding Source
Public
Class AjaxCustomer
Inherits
System.Windows.Forms.BindingSource
Public
Sub
New()
' Must call BindingSource base
class' New method first.
MyBase.New()
' Set the DataSource of this
BindingSource
' to be the AjaxDataSevice in the
DAL.
Me.DataSource =
My.Application.DAL.AjaxDataService
' Set the DataMember of this
BindingSource
' to be the Customer table winith
the AjaxDataService
Me.DataMember =
My.Application.DAL.AjaxDataService.Customer.ToString
End
Sub
End
Class
' AjaxOrders Binding Source
Public
Class AjaxOrders
Inherits
System.Windows.Forms.BindingSource
Public
Sub
New()
MyBase.New()
Me.DataSource =
My.Application.DAL.AjaxDataService
Me.DataMember =
My.Application.DAL.AjaxDataService.Orders.ToString
End
Sub
End
Class
' NorthwindEmployees Binding Source
Public
Class NorthwindEmployees
Inherits
System.Windows.Forms.BindingSource
Public
Sub
New()
MyBase.New()
Me.DataSource =
My.Application.DAL.NorthwindDataService
Me.DataMember =
My.Application.DAL.NorthwindDataService.Employees.ToString
End
Sub
End
Class
' NorthwindProducts Binding Source
Public
Class NorthwindProducts
Inherits
System.Windows.Forms.BindingSource
Public
Sub
New()
MyBase.New()
Me.DataSource =
My.Application.DAL.NorthwindDataService
Me.DataMember =
My.Application.DAL.NorthwindDataService.Products.ToString
End
Sub
End
Class
#End
Region
Bind a Custom DataSource Class to a
DataGridView
Next, the custom BindingSource class 'AjaxCustomer'
will be added to the example Windows Forms project's data sources.
Select the
GetDotNetCodeFreeVisualBasicExample
Windows Forms project in the Solution Explorer Panel and then click the
'Data Sources' Tab.
The Data Sources panel is shown. Click
the Add New Data Source... link in the panel.
The Data Source Configuration Wizard opens. Select the
Object icon then click the Next button.
The 'Select the Object You Wish to Bind
to' panel of the Data Source Configuration Wizard opens.
Click the + in front of
DalForMicrosoftAjaxDataBases and the the + to the left of GetDotNetCode.
The custom BindingSource classes are
listed.
Double click the AjaxCustomer object.
The Add Object Data Source panel of the
Data Source Configuration Wizard opens.
The AjaxCustomer class is shown as the
object that will be added to the example Windows Forms project's data
sources.
Click the Finish button.
The Data Sources panel now lists
AjaxCustomer.
Now that the AjaxCustomer data source
has been added to the example Windows Forms project it can be bound to a
Windows Forms control on any form in the project.
Open the project's ExampleOneForm in
design view. Click the DataGridView on the form to select it.
In the Properties panel, click the drop down arrow of
the DataSource property.
A data source navigation dialog will
pop up.
Drill down into 'Other Data Sources'
until you can see the AjaxCustomer data source. Click AjaxCustomer to
bind it to the DataGridView.
The DataGridView is now bound to the
Ajax Customer table in the application's DAL, via AjaxCustomer (the
custom BindingSource class).
Now that the DataGridView is bound via
the custom AjaxCustomer binding source class, code used in an earlier
part of the article should be removed.
Open the code behind the DemoOneForm.
Remove the code marked in yellow below
from the code behind DemoOneForm.
Modify the DeleteButton_Click
method so that it uses the form's AjaxCustomerBindingSource component:
Private
Sub DeleteButton_Click(ByVal
sender As System.Object,
ByVal e
As System.EventArgs)
Handles DeleteButton.Click
' Call the DAL's AjaxCustomerDelete
method passing it the row currently selected in this form's
' customerDataSource object.
' This will mark the row as deleted.
' The row will not be permanently
deleted until the AjaxCustomerUpdate
' method in the DAL is called.
My.Application.DAL.AjaxCustomerDelete(CType(CType(Me.AjaxCustomerBindingSource.Current,
_
DataRowView).Row,
DataService.AjaxDataSet.CustomerRow))
End
Sub
This is a good time to test all the changes made to the example
Windows Forms project.Run the application and
test DemoOneForm.
Extend a TableAdapter by Adding Queries to It
A TableAdapter can be extend by adding
one or more queries to it.
Open the AjaxDataSet.xsd file in the
DataService project.
Right-click CustomerTableAdaper under the Customer
table in the DataSet designer and select 'Add Query'.
The TableAdapter Query Configuration Wizard opens.
Click the Next> button.
The Choose a Query type panel of the wizard opens.
Click the Next> button.
The Specify a SQL SELECT Statement panel of the wizard
opens. Click the Query Builder... button.
The Query Builder opens. Set the filter on the Name column to: =?
Press the Enter key. A WHERE clause is added to the SELECT statement.
Setting the filter on one or more columns to =? creates a
parameterized SELECT statement.
In code that uses the query, values will be passed to the query which
will replace the ? in a filter with the value before the query is
executed.
Click the OK button.
The Specify a SQL SELECT Statement now shows the WHERE clause just
created.
Click the Next> button.
The Choose Methods to Generate panel of the
TableAdapter Configuration Wizards is shown.
Name the Fill a DataTable method:
FillByCustomerName
Name the Return a DataTable method:
GetDataByCustomerName
Click the Next> button.
The Wizard Results panel of the wizard is shown.
Click the Finish button.
The new query is now a part of the
CustomerTableAdapter.
In the DataService project, add the code below to the DAL component
code's 'Fill
Methods' region.
' Ajax Customer Fill By Customer
Name Method
Public
Sub
AjaxOrdersFillByCustomerName(ByVal
customerName As
String)
Dim customerTA
As
New AjaxDataSetTableAdapters.CustomerTableAdapter
' Call the CustomerTableAdapter's
FillByCustomerName method
' passing in the name of the
customer.
customerTA.FillByCustomerName(Me.AjaxDataService.Customer,
customerName)
End
Sub
The new query is now accessible through the DAL
component.
Try Out the New Query
Open the DemoFormOne in the example Windows Forms
Application in the form designer.
Add a button named FindByCustomerNameButton to the
form.
Open the code behind the DemoFormOne and add the method below:
Private
Sub
FindByCustomerNameButton_Click(ByVal
sender As System.Object,
_
ByVal e
As System.EventArgs)
Handles
FindByCustomerNameButton.Click
' Call the
AjaxCustomerFillByCustomerName method passing in the name of a
Customer.
' Only the customer(s) having the
name passed in will be selected and shown in the DataGridView.
My.Application.DAL.AjaxCustomerFillByCustomerName("York
Fish and Chips")
End
Sub
Note: If you do not have the 'York Fish and Chips' customer in
your Ajax Customer table change the code above to pass in the name of a customer
from your Ajax Customer table.
Test the application. Run it, open the DemoOneForm and click the Find By
Customer Name button.
Modify a TableAdapter's Partial Class to
Extend It's Features
TableAdapters are auto generated by
Visual Studio as partial classes. You can modify or extend the behavior
of a TableAdapter by adding your own partial class for the TableAdapter.
Code that adds a new method to the
AjaxDataSetTableAdapters partial CustomerTableAdapter class has been
added to the code behind the AjaxDataSet. Open the AjaxDataSet.xsd,
right-click on the design surface, then select 'View Code' to view the
code behind the AjaxDataSet. The code is also shown below:
' TableAdapters for a DataSet are
located in their own namespace.
Namespace
AjaxDataSetTableAdapters
Partial
Class CustomerTableAdapter
' CustomFill method added to the
CustomerTableAdapter class.
' Pass in a DataSet, the name of a
DataTable, and a query string to fill
' the DataTable.
Public
Sub CustomFill(ByVal
theDataSet As DataSet,
ByVal theDataTable
As
String, ByVal
selectCommandText As
String)
' Create a new OleDb.OleDbCommand
using query string passed in and
' the CustomerTableAdapter's
connection;
' assign the new OleDbCommand to
the CustomerTableAdapter's SelectComman.
Me.Adapter.SelectCommand =
New
OleDb.OleDbCommand(selectCommandText,
Me.Connection)
' Fill the DataTable.
Me.Adapter.Fill(theDataSet,
theDataTable)
End
Sub
End
Class
End
Namespace
To better understand how to use TableAdapter partial classes,
read the MSDN article 'How to: Extend the Functionality of
TableAdapters'. A link is provided at the end of this article.
In the DataService project, add the code below to the DAL component
code's 'Fill
Methods' region.
' Ajax Customer Custom Fill Method
Public
Sub AjaxCustomerCustomFill(ByVal
selectQueryString As
String)
Dim customerTA
As
New AjaxDataSetTableAdapters.CustomerTableAdapter
customerTA.CustomFill(Me.AjaxDataService,
Me.AjaxDataService.Customer.ToString,
selectQueryString)
End
Sub
Try The CustomFill Method
Open the DemOneForm in the example Windows Forms
project in design view, and add a button named CustomFill to it.
Open the code behind the form and add the method below
to it.
Private
Sub CustomFillButton_Click(ByVal
sender As System.Object,
ByVal e
As System.EventArgs)
Handles CustomFill.Click
' Call the DAL's
AjaxCustomerCustomFill method, passing in a query string.
My.Application.DAL.AjaxCustomerCustomFill("SELECT
* FROM Customer WHERE Name LIKE 'Bill%'")
End
Sub
Note: If you do not have a customer in your Ajax Customer table
with a name that starts with 'Bill', change the code above to pass
part of the name of a customer from your Ajax Customer table.
Test the application. Run it, open the DemoOneForm and click the
Custom Fill button.
The next installment of this article will provide a
architecture diagram of the DAL component and the example Windows Forms
project.
Help
BindingSource Class
DataView Class
How to: Create TableAdapter Queries
How to: Extend the Functionality of a TableAdatper
|
