ADO.NET provides good support for "data-bound" objects (that is, objects that can be tied to a particular data set, such as those retrieved from a database by ADO.NET).
A simple example of a data-bound control is the DataGrid control provided with both Windows Forms and Web Forms.
In its simplest use, a DataGrid is easy to implement. Once again, first create a DataSet and then fill it from the Customers table of the Northwind database, but this time, rather than iterating through the rows of the data set and writing the output to a listbox, you can simply bind the Customers table in your data set to a DataGrid control.
To illustrate, alter Example 14-1 by deleting the listbox from the form you created and replace it with a DataGrid. The default name provided by the Visual Studio design tool is DataGrid1, but let's change it to CustomerDataGrid. After the data set is created and filled, you bind the DataGrid through its DataSource property:
CustomerDataGrid.DataSource =
myDataSet.Tables("Customers").DefaultView
Example 14-3 provides the complete source code for this example.
Option Strict On
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Public Class ADOForm3
Inherits System.Windows.Forms.Form
Private components As System.ComponentModel.Container
Friend WithEvents CustomerDataGrid As _
System.Windows.Forms.DataGrid
Public Sub New( )
InitializeComponent( )
' set up connection and command strings
Dim connectionString As String = _
"server=localhost; " & _
"uid=sa; pwd=YourPassword; database=northwind"
Dim commandString As String = _
"Select CompanyName, ContactName, ContactTitle, " & _
"Phone, Fax from Customers"
' create a data set and fill it
Dim myDataAdapter As _
New SqlDataAdapter(commandString, connectionString)
Dim myDataSet As New DataSet( )
myDataAdapter.Fill(myDataSet, "Customers")
' bind the DataSet to the grid
CustomerDataGrid.DataSource = _
myDataSet.Tables("Customers").DefaultView
End Sub 'New
Private Sub InitializeComponent( )
' Removed to save space
End Sub 'InitializeComponent
End Class 'ADOForm3
The code is embarrassingly easy to implement and the results are quite impressive, as shown in Figure 14-3. Notice that every field in the record is represented by a column in the DataGrid, and that the titles of the columns are the names of the fields. All of this is the default behavior of the DataGrid.
It is possible to precisely control every aspect of creating the DataSet, rather than using the default settings. In the previous examples, when you created the DataSet you passed in a commandString and a connectionString:
Dim myDataAdapter As _
New SqlDataAdapter(commandString, connectionString)
These were assigned internally to a SqlCommand object and a SqlConnection object, respectively. You can instead explicitly create these objects to gain finer control over their properties.
In this next example, you'll give the class four new class members:
Private myConnection As System.Data.SqlClient.SqlConnection Private myDataSet As System.Data.DataSet Private myCommand As System.Data.SqlClient.SqlCommand Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
The connection is created by instantiating a SqlConnection object with the connection string:
Dim connectionString As String = _
"server=localhost; uid=sa; " & _
"pwd=YourPassword; database=northwind"
myConnection = _
New System.Data.SqlClient.SqlConnection(connectionString)
and then it is opened explicitly:
myConnection.Open( )
By hanging on to this connection, you can reuse it (as you'll see in a subsequent example) and you can also use its transaction support if needed.
Next, explicitly create the DataSet object and set one of its properties:
myDataSet = New System.Data.DataSet( ) myDataSet.CaseSensitive = True
Setting CaseSensitive to true indicates that string comparisons within DataTable objects are case-sensitive.
Next, explicitly create the SqlCommand object and give that new command object the connection object and the text for the command:
myCommand = New System.Data.SqlClient.SqlCommand( ) myCommand.Connection = myConnection myCommand.CommandText = "Select * from Customers"
Finally, create the SqlDataAdapter object and assign to it the SqlCommand object you just established. Then tell the DataSet how to map the table columns, using the table you're searching, and instruct the SqlDataAdapter to fill the DataSet object:
myDataAdapter = New System.Data.SqlClient.SqlDataAdapter( )
myDataAdapter.SelectCommand = myCommand
myDataAdapter.TableMappings.Add("Table", "Customers")
myDataAdapter.Fill(myDataSet)
That done, you're ready to fill the DataGrid:
dataGrid1.DataSource = _
myDataSet.Tables("Customers").DefaultView
(This time I've used the default name for the DataGrid.)
Example 14-4 provides the complete source code.
Option Strict On
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Public Class ADOForm1
Inherits System.Windows.Forms.Form
Private components As System.ComponentModel.Container
Private dataGrid1 As System.Windows.Forms.DataGrid
' private System.Data.ADO.ADOConnection myConnection;
Private myConnection As System.Data.SqlClient.SqlConnection
Private myDataSet As System.Data.DataSet
Private myCommand As System.Data.SqlClient.SqlCommand
Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
Public Sub New( )
InitializeComponent( )
' create the connection object and open it
Dim connectionString As String = _
"server=localhost; uid=sa; " & _
"pwd=YourPassword; database=northwind"
myConnection = _
New System.Data.SqlClient.SqlConnection(connectionString)
myConnection.Open( )
' create the DataSet and set a property
myDataSet = New System.Data.DataSet( )
myDataSet.CaseSensitive = True
' create the SqlCommand object and assign the
' connection and the select statement
myCommand = New System.Data.SqlClient.SqlCommand( )
myCommand.Connection = myConnection
myCommand.CommandText = "Select * from Customers"
' create the myDataAdapter object and pass in the
' SQL Command object and establish the table mappings
myDataAdapter = New System.Data.SqlClient.SqlDataAdapter( )
myDataAdapter.SelectCommand = myCommand
myDataAdapter.TableMappings.Add("Table", "Customers")
' Tell the myDataAdapter object to fill the DataSet
myDataAdapter.Fill(myDataSet)
' display it in the grid
dataGrid1.DataSource = _
myDataSet.Tables("Customers").DefaultView
End Sub 'New
Private Sub InitializeComponent( )
' Removed to save space
End Sub 'InitializeComponent
End Class 'ADOForm1
The result of this is shown in Figure 14-4. Now that you have this control, you are in a position to get much fancier in your use of the grid.
With the work you've done so far, it is easy now to build a grid that reflects the relationship between two or more tables. For example, you might like to examine all the orders that each customer has placed over some period of time.
Relational databases are built on the idea that one table relates to other tables. The relationship between Orders and Customers is that every order includes a CustomerID, which is a foreign key in Orders and a primary key in Customers. Thus, you have a one-to-many relationship, in which one customer can have many orders, but each order has exactly one customer. You'd like to be able to display this relationship in the grid.
ADO.NET makes this fairly easy, and you can build on the previous example. This time, you want to represent two tables, Customers and Orders, rather than just the Customers table. To do so, you need only a single DataSet object and a single Connection object, but you need two SqlCommand objects and two SqlDataAdapter objects.
After you create the SqlDataAdapter for Customers, just as you did in the previous example, go on to create a second command for Orders:
myCommand2 = New System.Data.SqlClient.SqlCommand( ) myCommand2.Connection = myConnection myCommand2.CommandText = "Select * from Orders"
Notice that DataAdapter2 can reuse the same connection as used by the earlier DataAdapter object. The new CommandText is different, of course, because you are searching a different table.
Next, instantiate a second SqlDataAdapter object with this new command and map its table to Orders. You can then fill the DataSet with the second table:
myDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter( )
myDataAdapter2.SelectCommand = myCommand2
myDataAdapter2.TableMappings.Add("Table", "Orders")
You now have a single DataSet with two tables. You can display either one or both of the tables, but in this example you'll do more. There is a relationship between these tables, and you want to display that relationship. Unfortunately, the DataSet is ignorant of the relationship, unless you explicitly create a DataRelation object and add it to the DataSet.
Start by declaring an object of type DataRelation:
Dim myDataRelation As System.Data.DataRelation
This relation will represent the relationship in the database between Customers.CustomerID and Orders.CustomerID. To model this, you need a pair of DataColumn objects:
Dim dataColumn1 As System.Data.DataColumn Dim dataColumn2 As System.Data.DataColumn
Each DataColumn must be assigned a column in the table within the DataSet:
dataColumn1 = _
myDataSet.Tables("Customers").Columns("CustomerID")
dataColumn2 = _
myDataSet.Tables("Orders").Columns("CustomerID")
You're now ready to create the DataRelation object, passing into the constructor the name of the relationship and the two DataColumn objects:
myDataRelation = New System.Data.DataRelation( _
"CustomersToOrders", dataColumn1, dataColumn2)
You can now add that relation to the DataSet:
myDataSet.Relations.Add(myDataRelation)
Next, create a DataViewManager object that provides a view of the DataSet for the DataGrid, and set the DataGrid.DataSource property to that view:
Dim dataSetView As DataViewManager = _
myDataSet.DefaultViewManager
DataGrid1.DataSource = _
dataSetView
Finally, because the DataGrid now has more than one table, you must tell the grid which table is the "parent" table, or the one table to which many other tables can relate. Do this by setting the DataMember property as shown:
DataGrid1.DataMember= "Customers"
Example 14-5 provides the complete source for this program.
Option Strict On
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Public Class ADOForm1
Inherits System.Windows.Forms.Form
Private components As System.ComponentModel.Container
' private System.Data.ADO.ADOConnection myConnection;
Private myConnection As System.Data.SqlClient.SqlConnection
Private myDataSet As System.Data.DataSet
Private myCommand As System.Data.SqlClient.SqlCommand
Private myCommand2 As System.Data.SqlClient.SqlCommand
Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
Private myDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
Public Sub New( )
InitializeComponent( )
' create the connection object and open it
Dim connectionString As String = _
"server=localhost; uid=sa; " & _
"pwd=YourPassword; database=northwind"
myConnection = _
New System.Data.SqlClient.SqlConnection(connectionString)
myConnection.Open( )
' create the DataSet and set a property
myDataSet = New System.Data.DataSet( )
myDataSet.CaseSensitive = True
' create the SqlCommand object and assign the
' connection and the select statement
myCommand = New System.Data.SqlClient.SqlCommand( )
myCommand.Connection = myConnection
myCommand.CommandText = "Select * from Customers"
myCommand2 = New System.Data.SqlClient.SqlCommand( )
myCommand2.Connection = myConnection
myCommand2.CommandText = "Select * from Orders"
' create the myDataAdapter object and pass in the
' SQL Command object and establish the table mappings
myDataAdapter = New System.Data.SqlClient.SqlDataAdapter( )
myDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter( )
myDataAdapter.SelectCommand = myCommand
myDataAdapter2.SelectCommand = myCommand2
myDataAdapter.TableMappings.Add("Table", "Customers")
myDataAdapter2.TableMappings.Add("Table", "Orders")
' Tell the myDataAdapter object to fill the DataSet
myDataAdapter.Fill(myDataSet)
myDataAdapter2.Fill(myDataSet)
Dim myDataRelation As System.Data.DataRelation
Dim dataColumn1 As System.Data.DataColumn
Dim dataColumn2 As System.Data.DataColumn
dataColumn1 = _
myDataSet.Tables("Customers").Columns("CustomerID")
dataColumn2 = _
myDataSet.Tables("Orders").Columns("CustomerID")
myDataRelation = New System.Data.DataRelation( _
"CustomersToOrders", dataColumn1, dataColumn2)
myDataSet.Relations.Add(myDataRelation)
Dim dataSetView As DataViewManager = _
myDataSet.DefaultViewManager
' display it in the grid
DataGrid1.DataSource = _
dataSetView
DataGrid1.DataMember = "Customers"
End Sub 'New
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Private Sub InitializeComponent( )
' Removed to save space
End Sub 'InitializeComponent
End Class 'ADOForm1
The result is impressive. Figure 14-5 shows the grid with one customer chosen. The CustomersToOrders link is open under customer ID CACTU.
Clicking the link opens all the orders for that customer, as shown in Figure 14-6.
|
|
|
| Top |