ADO.NET, Part 4

Related Reading Programming Visual Basic .NET By Dave Grundgeiger

This is the forth installment from the Programming Visual Basic .NET chapter on ADO.NET, focusing on binding a DataSet to a Windows and a Web Forms DataGrid.

Binding a DataSet to a Windows Forms DataGrid

DataSet and DataTable objects can be bound to Windows Forms DataGrid objects to provide an easy way to view data. This is done by calling a DataGrid object's SetDataBinding method, passing the object that is to be bound to the grid. The syntax of the SetDataBinding method is:

Public Sub SetDataBinding( _
   ByVal dataSource As Object, _
   ByVal dataMember As String _
)

The parameters are:

dataSource

The source of the data to show in the grid. This can be any object that exposes the System.Collections.IList or System.Data.IListSource interfaces, which includes the DataTable and DataSet classes discussed in this chapter.

dataMember

If the object passed in the dataSource parameter contains multiple tables, as a DataSet object does, the dataMember parameter identifies the table to display in the DataGrid. If a DataTable is passed in the dataSource parameter, the dataMember parameter should contain either Nothing or an empty string.

Example 8-8 shows how to bind a DataSource object to a DataGrid. The DataSource object contains a Customers table and an Orders table, and a relation between them. The call to the DataGrid object's SetDataBinding method specifies that the Customers table should be shown in the grid. Figure 8-3 shows the resulting DataGrid display.

Example 8-8: Creating a DataSet and binding it to a Windows Forms DataGrid

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data adapter object.
Dim strSql As String = _
   "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers" _
   & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, cn)
 
' Load a data set.
Dim ds As DataSet = New DataSet(  )
da.Fill(ds, "Customers")
 
' Set up a new data adapter object.
strSql = _
   "SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate," _
   & " Orders.ShippedDate" _
   & " FROM Customers, Orders" _
   & " WHERE (Customers.CustomerID = Orders.CustomerID)" _
   & "    AND (Customers.City = 'Buenos Aires')" _
   & "    AND (Customers.Country = 'Argentina')"
da = New SqlDataAdapter(strSql, cn)
 
' Load the data set.
da.Fill(ds, "Orders")
 
' Close the database connection.
cn.Close(  )
 
' Create a relation.
ds.Relations.Add("CustomerOrders", _
   ds.Tables("Customers").Columns("CustomerID"), _
   ds.Tables("Orders").Columns("CustomerID"))
 
' Bind the data set to a grid.
' Assumes that grid contains a reference to a 
' System.WinForms.DataGrid object.
grd.SetDataBinding(ds, "Customers")

Figure 8-3. The display generated by the code in Example 8-8

Note in Figure 8-3 that each row in this DataGrid has a "+" icon. The reason is that the DataGrid object has detected the relation between the Customers table and the Orders table. Clicking on the "+" reveals all of the relations for which the Customers table is the parent. In this case, there is only one, as shown in Figure 8-4.

Figure 8-4. Clicking the "+" reveals relations

The name of the relation in the display is a link. Clicking on this link loads the grid with the child table in the relation, as shown in Figure 8-5.

Figure 8-5. The Orders table

While the child table is displayed, the corresponding row from the parent table is displayed in a header (shown in Figure 8-5). To return to the parent table, click the left-pointing triangle in the upper-right corner of the grid.

Binding a DataSet to a Web Forms DataGrid

Example 8-9 shows how to bind a DataTable object to a Web Forms DataGrid object. Figure 8-6 shows the resulting display in a web browser.

Example 8-9: Creating a DataTable and binding it to a Web Forms DataGrid

<%@ Page Explicit="True" Strict="True" %>
 
<script language="VB" runat="server">
 
   Protected Sub Page_Load(ByVal Sender As System.Object, _
      ByVal e As System.EventArgs)
      
      If Not IsPostback Then ' True the first time the browser hits the page.
         ' Bind the grid to the data.
         grdCustomers.DataSource = GetDataSource(  )
         grdCustomers.DataBind(  )
      End If   
      
   End Sub ' Page_Load
    
   Protected Function GetDataSource(  ) As System.Collections.ICollection
 
      ' Open a database connection.
      Dim strConnection As String = _
         "Data Source=localhost;Initial Catalog=Northwind;" _
         & "Integrated Security=True"
      Dim cn As New System.Data.SqlClient.SqlConnection(strConnection)
      cn.Open(  )
      
      ' Set up a data adapter object.
      Dim strSql As String = _
         "SELECT CustomerID, CompanyName, ContactName, Phone" _
         & " FROM Customers" _
         & " WHERE City = 'Buenos Aires' AND Country = 'Argentina'"
      Dim da As New System.Data.SqlClient.SqlDataAdapter(strSql, cn)
      
      ' Load a data set.
      Dim ds As New System.Data.DataSet(  )
      da.Fill(ds, "Customers")
      
      ' Close the database connection.
      cn.Close(  )
      
      ' Wrap the Customers DataTable in a DataView object.
      Dim dv As New System.Data.DataView(ds.Tables("Customers"))
         
      Return dv
      
   End Function ' GetDataSource
 
</script>
 
<html>
   <body>
      
      <asp:DataGrid id=grdCustomers runat="server" ForeColor="Black">
         <AlternatingItemStyle BackColor="Gainsboro" />
         <FooterStyle ForeColor="White" BackColor="Silver" />
         <ItemStyle BackColor="White" />
         <HeaderStyle Font-Bold="True" ForeColor="White" 
               BackColor="Navy" />
      </asp:DataGrid>
      
   </body>
</html>

Figure 8-6. The display generated by the code in Example 8-9

Note the following:

• Unlike the Windows Forms DataGrid class, the Web Forms DataGrid class has no SetDataBinding method. Instead, set the Web Forms DataGrid's DataSource property and then call the DataGrid's DataBind method.
• Unlike the Windows Forms DataGrid class, the Web Forms DataGrid class's DataSource property can't directly consume a DataTable or DataSet. Instead, the data must be wrapped in a DataView or DataSetView object. The properties and methods of the DataView and DataSetView classes provide additional control over how data is viewed in a bound DataGrid. DataView and DataSetView objects can be used by either Windows Forms or Web Forms DataGrids, but they are mandatory with Web Forms DataGrids.
• The DataGrid's DataSource property can consume any object that exposes the System.Collections.ICollection interface.

The next installment concludes this series of excerpts with Typed DataSets, reading data into a DataReader, and executing stored procedures through a SqlCommand object.

View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.

Copyright © 2009 O'Reilly Media, Inc.