|
|
Much has been said about the DataTable component throughout this book. In this section, this component is not dissected in detail as previously done with the DataSet, but you are given the tools you need to work with the DataTable.
The DataTable is the real container of data items in ADO .NET’s client components. Think of it as a database table. Data are stored in components called DataRows, which are analogous to a table’s row in database terms. The DataRow itself can have one or several DataColumns.
ADO .NET goes further by providing for the instrumentation of different views of the data stored by a DataTable through components called DataViews. The following example shows you how to create a DataTable component, assign columns to it, populate it, and create a view for it. The view is then used as a data source for a DataGrid on a Windows Form object. To view and perhaps extend this code, see the Chapter03 folder on the companion CD. The example uses the Northwind database in SQL Server 2000.
Public Class frmCustomers
Inherits System.Windows.Forms.Form
Private dtCustomers As New DataTable()
Private dvCustomers As DataView
#Region " Windows Form Designer generated code "
Private Sub cmdPopulateGrid_Click _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles _
cmdPopulateGrid.Click
PopulateGrid() 'Populate the grid
End Sub
Private Function PopulateGrid()
If dtCustomers.Rows.Count < 1 Then
PrepareData() 'Prepare the datasource
'Assign the data source to the grid
With dgCustomers
.DataSource = dvCustomers
End With
Else
'If there are rows in the DataTable, there is
'no need to retrieve again
lblMsg.Text = "The grid is already populated."
End If
End Function
Private Function PrepareData()
Dim dr As DataRow
Dim objCustReader As Data.SqlClient.SqlDataReader
'Create columns in the DataTable
With dtCustomers.Columns
.Add(New DataColumn("Customer ID", _
GetType(String)))
.Add(New DataColumn("Company Name", _
GetType(String)))
.Add(New DataColumn("Contact Name", _
GetType(String)))
.Add(New DataColumn("Address", _
GetType(String)))
End With
'Populate the DataReader
objCustReader = GetData()
'Populate the DataTable
With objCustReader
While .Read()
'A new DataRow is added upon each
'iteration
dr = dtCustomers.NewRow()
dr(0) = .Item("CustomerID")
dr(1) = .Item("CompanyName")
dr(2) = .Item("ContactName")
dr(3) = .Item("Address")
dtCustomers.Rows.Add(dr)
End While
End With
'Create a view of the DataTable
dvCustomers = New DataView(dtCustomers)
'Free Resources
objCustReader = Nothing
End Function
Private Function GetData() As _
Data.SqlClient.SqlDataReader
Dim strConn As String
Dim strQuery As String
Dim objConn As New Data.SqlClient.SqlConnection()
Dim objCmd As New Data.SqlClient.SqlCommand()
Dim objReader As Data.SqlClient.SqlDataReader
'Set up the connection string for the
'SQL Server database
strConn = "Data Source=LOCALHOST;"
strConn &= "Initial Catalog=Northwind;"
strConn &= "User ID=sa;password=;"
With objConn
'Assign connection string
.ConnectionString = strConn
'Open a connection to the database
.Open()
End With
'Set up the query string
strQuery = "SELECT CustomerID, CompanyName, "
strQuery &= " ContactName, Address "
strQuery &= " FROM Customers"
'Execute the command
With objCmd
.CommandText = strQuery
.Connection = objConn
objReader = .ExecuteReader()
.Dispose()
End With
'Return the reader
Return objReader
End Function
End Class
|
|