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.
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:
System.Data.IListSourceinterfaces, which includes the DataTable and DataSet classes discussed in this chapter.
Nothingor 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")
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.
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.
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.
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>
Note the following:
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.