ADO.NET provides good support for “data-bound” objects: that is, objects that can be tied to a particular data set, such as one 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 list box, you can simply bind the Customers table in your data set
to a DataGrid
control.
To illustrate, alter Example 14-2 by deleting the
list box from the form you created in the previous example 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= DataSet.Tables["Customers"].DefaultView;
Example 14-3 provides the complete source code for this example.
Example 14-3. Using a DataGrid Control
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace ProgrammingCSharpWindows.Form { public class ADOForm3 : System.Windows.Forms.Form { private System.ComponentModel.Container components; private System.Windows.Forms.DataGrid CustomerDataGrid; public ADOForm3( ) { InitializeComponent( ); // set up connection and command strings string connectionString = "server=localComputer; uid=sa; pwd=; database=northwind"; string commandString = "Select CompanyName, ContactName, ContactTitle, " + "Phone, Fax from Customers"; // create a data set and fill it SqlDataAdapter DataAdapter = new SqlDataAdapter(commandString, connectionString); DataSet DataSet = new DataSet( ); DataAdapter.Fill(DataSet,"Customers"); // bind the DataSet to the grid CustomerDataGrid.DataSource= DataSet.Tables["Customers"].DefaultView; } public override void Dispose( ) { base.Dispose( ); components.Dispose( ); } private void InitializeComponent( ) { this.components = new System.ComponentModel.Container ( ); this.CustomerDataGrid = new System.Windows.Forms.DataGrid ( ); CustomerDataGrid.BeginInit ( ); CustomerDataGrid.Location = new System.Drawing.Point (8, 24); CustomerDataGrid.Size = new System.Drawing.Size (656, 224); CustomerDataGrid.DataMember = ""; CustomerDataGrid.TabIndex = 0; CustomerDataGrid.Navigate += new System.Windows.Forms.NavigateEventHandler (this.dataGrid1_Navigate); this.Text = "ADOFrm3"; this.AutoScaleBaseSize = new System.Drawing.Size (5, 13); this.ClientSize = new System.Drawing.Size (672, 273); this.Controls.Add (this.CustomerDataGrid); CustomerDataGrid.EndInit ( ); } protected void dataGrid1_Navigate (object sender, System.Windows.Forms.NavigateEventArgs ne) { } public static void Main(string[] args) { Application.Run(new 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 control precisely 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
:
SqlDataAdapter DataAdapter = 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 System.Data.SqlClient.SqlConnection myConnection; private System.Data.DataSet myDataSet; private System.Data.SqlClient.SqlCommand myCommand; private System.Data.SqlClient.SqlDataAdapter DataAdapter;
The connection is created by instantiating a
SqlConnection
object with the connection string:
string connectionString = "server=localhost; uid=sa; pwd=; database=northwind"; myConnection = new System.Data.Sql.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 you
instruct the SqlDataAdapter
to fill the
DataSet
object:
DataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); DataAdapter.SelectCommand= myCommand; DataAdapter.TableMappings.Add("Table","Customers"); DataAdapter.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.
Example 14-4. Customizing a Dataset
namespace ProgrammingCSharpWindows.Form { using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class ADOForm1 : System.Windows.Forms.Form { private System.ComponentModel.Container components; private System.Windows.Forms.DataGrid dataGrid1; // private System.Data.ADO.ADOConnection myConnection; private System.Data.SqlClient.SqlConnection myConnection; private System.Data.DataSet myDataSet; private System.Data.SqlClient.SqlCommand myCommand; private System.Data.SqlClient.SqlDataAdapter DataAdapter; public ADOForm1( ) { InitializeComponent( ); // create the connection object and open it string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; 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 DataAdapter object and pass in the // SQL Command object and establish the table mappings DataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); DataAdapter.SelectCommand= myCommand; DataAdapter.TableMappings.Add("Table","Customers"); // Tell the DataAdapter object to fill the DataSet DataAdapter.Fill(myDataSet); // display it in the grid dataGrid1.DataSource= myDataSet.Tables["Customers"].DefaultView; } public override void Dispose( ) { base.Dispose( ); components.Dispose( ); } private void InitializeComponent( ) { this.components = new System.ComponentModel.Container ( ); this.dataGrid1 = new System.Windows.Forms.DataGrid ( ); dataGrid1.BeginInit ( ); dataGrid1.Location = new System.Drawing.Point (24, 32); dataGrid1.Size = new System.Drawing.Size (480, 408); dataGrid1.DataMember = ""; dataGrid1.TabIndex = 0; this.Text = "ADOFrm1"; this.AutoScaleBaseSize = new System.Drawing.Size (5, 13); this.ClientSize = new System.Drawing.Size (536, 501); this.Controls.Add (this.dataGrid1); dataGrid1.EndInit ( ); } public static void Main(string[] args) { Application.Run(new 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( ); DataAdapter2 = new System.Data.SqlClient.SqlDataAdapter( ); 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:
DataAdapter2.SelectCommand = myCommand2; DataAdapter2.TableMappings.Add ("Table", "Orders"); DataAdapter2.Fill(myDataSet);
You now have a single DataSet
with two tables. You
can display either 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
:
System.Data.DataRelation 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:
System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2;
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:
dataRelation = new System.Data.DataRelation("CustomersToOrders", dataColumn1, dataColumn2);
You can now add that relation to the DataSet
:
myDataSet.Relations.Add(dataRelation);
Next, create a DataViewManager
object that
provides a view of the DataSet
for the
DataGrid
, and set the
DataGrid.DataSource
property to that view:
DataViewManager DataSetView = 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 process.
Example 14-5. Using a DataGrid with two tables
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; namespace ProgrammingCSharpWindows.Form { using System.Data.SqlClient; public class ADOForm1 : System.Windows.Forms.Form { private System.ComponentModel.Container components; private System.Windows.Forms.DataGrid dataGrid1; // private System.Data.ADO.ADOConnection myConnection; private System.Data.SqlClient.SqlConnection myConnection; private System.Data.DataSet myDataSet; private System.Data.SqlClient.SqlCommand myCommand; private System.Data.SqlClient.SqlCommand myCommand2; private System.Data.SqlClient.SqlDataAdapter DataAdapter; private System.Data.SqlClient.SqlDataAdapter DataAdapter2; public ADOForm1( ) { InitializeComponent( ); // create the connection string connectionString = "server=Neptune; uid=sa; pwd=oWenmEany; database=northwind"; myConnection = new System.Data.SqlClient.SqlConnection(connectionString); myConnection.Open( ); // create the data set myDataSet = new System.Data.DataSet( ); myDataSet.CaseSensitive=true; // set up the command and DataSet command for the first table myCommand = new System.Data.SqlClient.SqlCommand( ); myCommand.Connection=myConnection; myCommand.CommandText = "Select * from Customers"; DataAdapter = new System.Data.SqlClient.SqlDataAdapter( ); DataAdapter.SelectCommand= myCommand; DataAdapter.TableMappings.Add("Table","Customers"); DataAdapter.Fill(myDataSet); // set up the command and DataSet command for the second table myCommand2 = new System.Data.SqlClient.SqlCommand( ); DataAdapter2 = new System.Data.SqlClient.SqlDataAdapter( ); myCommand2.Connection = myConnection; myCommand2.CommandText = "SELECT * FROM Orders"; DataAdapter2.SelectCommand = myCommand2; DataAdapter2.TableMappings.Add ("Table", "Orders"); DataAdapter2.Fill(myDataSet); // establish the relationship between the tables System.Data.DataRelation dataRelation; System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2; dataColumn1 = myDataSet.Tables["Customers"].Columns["CustomerID"]; dataColumn2 = myDataSet.Tables["Orders"].Columns["CustomerID"]; dataRelation = new System.Data.DataRelation( "CustomersToOrders", dataColumn1, dataColumn2); // add the relation object to the data set myDataSet.Relations.Add(dataRelation); // set up the grid's view and member data and display it DataViewManager DataSetView = myDataSet.DefaultViewManager; dataGrid1.DataSource = DataSetView; dataGrid1.DataMember= "Customers"; } public override void Dispose( ) { base.Dispose( ); components.Dispose( ); } private void InitializeComponent( ) { this.components = new System.ComponentModel.Container ( ); this.dataGrid1 = new System.Windows.Forms.DataGrid ( ); dataGrid1.BeginInit ( ); //@this.TrayHeight = 0; //@this.TrayLargeIcon = false; //@this.TrayAutoArrange = true; dataGrid1.Location = new System.Drawing.Point (24, 32); dataGrid1.Size = new System.Drawing.Size (480, 408); dataGrid1.DataMember = ""; dataGrid1.TabIndex = 0; this.Text = "ADOFrm1"; this.AutoScaleBaseSize = new System.Drawing.Size (5, 13); this.ClientSize = new System.Drawing.Size (536, 501); this.Controls.Add (this.dataGrid1); dataGrid1.EndInit ( ); } public static void Main(string[] args) { Application.Run(new 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.
3.129.210.102