Using a DataSet to Read Data

You just saw an example of working with an Access 2000 database. For variety, you'll now see an example of using a SQL Server database. Instead of writing a console-based application, this time you'll create a Windows Forms application and use the VS .NET IDE.

In this application, you'll use the SQL Server Northwind database. You'll read data from the Customers table using the same CustomerId, ContactName, and ContactTitle columns you selected in Listing 1-1 and display data in a DataGrid control.

Begin your first ADO.NET application by launching VS .NET and by creating a new project using File New Project. Choose Visual Basic Projects and select the Windows Application template from the available templates, name your project DataGridSamp, and click OK.

Clicking the OK button creates a Windows application project. Now you read data from the SQL Server Northwind database and display data in a DataGrid control. This example shows the Customers table data in a DataGrid control.

Drag a DataGrid control from the Toolbox onto the form using Toolbox Window Forms and then resize it. Also drag a Button control onto the form and assign it a Text property of Fill. Now double-click the button to write a click event handler for the button. Fill data from a database to the DataGrid on the Fill button click event handler.

To access SQL Server, you use the Sql .NET data provider, which is defined in the System.Data.SqlClient namespace. Before adding this namespace class, you need to import the namespace using the Imports statement. Add the following two lines in the beginning of your application:

 Imports System.Data Imports System.Data.SqlClient 

You implement your ADO.NET routine inside this method. To retrieve data from the database, you first need to connect to the database using a Connection object. The DataAdapter is the bridge between the data source (sql) and the DataSet (memory). The DataAdapter is constructed with two elements in this example: a SQL SELECT command to tell the DataAdapter which data to extract into the DataSet and the Connection object to tell the DataAdapter how to connect to the data source. In this example, select the CustomerId, ContactName, and ContactTitle column data from the Customers table. After creating a DataAdapter, the next step is to create and fill data from a DataAdapter to a DataSet, which you do by making a call to the Fill method of DataAdapter. The Fill method takes two parameters: a DataSet and a database table name.

Finally, you'll want to display the data from the Customers table in the DataGrid control. You do this by simply binding the DataSet to the DataGrid through its DefaultViewManager (Chapters 2 and 4 discuss DataViewManager in more detail) by using the DataGrid's DataSource property. You just set the DataSource property of the DataSet's DefaultViewManager.

Listing 1-2 shows the button click event handler. As you can see, this code creates a connection using the localhost server and the Northwind database, and you create a DataAdapter using the Customers table. After that, create a DataSet object and call the DataAdapter's Fill method. After that, bind DataSet.DefaultViewManager to the DataGrid using the DataSource property of the DataGrid.

Listing 1-2: Viewing Data from a SQL Server Database to a DataGrid Control

 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click   ' Create a Connection Object     Dim ConnectionString As String     Dim sql As String ConnectionString = "Integrated Security=SSPI; Initial Catalog=Northwind; " & _ "Data Source=localhost;"     Dim myConnection As SqlConnection = New SqlConnection()     myConnection.ConnectionString = ConnectionString     sql = "SELECT CustomerID, ContactName, ContactTitle FROM Customers"     Dim adapter As SqlDataAdapter = New SqlDataAdapter(sql, myConnection)     ' Construct the DataSet and fill it     Dim dtSet As DataSet = New DataSet("Customers")     adapter.Fill(dtSet, "Customers")     ' Bind the Listbox to the DataSet     DataGrid1.DataSource = dtSet.DefaultViewManager End Sub 

Now compile and run the project. Press the Fill button. The output of the program should look like Figure 1-16.

Figure 1-16: Output of the ADO.NET application in a DataGrid control

Caution

Make sure your SQL Server is running and the name of the SQL Server is correct. If you're not using a local server, you need to pass the correct server name, with valid a user ID and password. We discuss using user IDs and passwords in a connection in Chapter 4. If SQL Server isn't running, you will get an exception.