Creating Your First ADO.NET Web Application

You've already seen how to develop an ASP.NET application using VS .NET. Now you'll see how to develop database applications using ADO.NET and ASP.NET. To start creating your first ADO.NET application, you'll create a Web Application project as you did in the previous tutorial. In this example, you'll read data from the SQL Server Northwind database and view data in few data-bound controls. After creating a Web application, add a ListBox and a DataGrid control to the Web page. In this application, you'll fill these controls with data from the database.

After dragging these controls from the Web Forms control Toolbox and dropping it on the page, you write the code in Listing 14-1 on the Page_Load event. You can add a Page_Load event by typing the code directly into the Codebehind.vb file or by double-clicking on the page.

Listing 14-1: Filling Data from a Database to Data-Bound Controls

 Private Sub Page_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     Dim ConnectionString As String = "user id=sa;password=;" & _       "Initial Catalog=Northwind;" & _       "Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Open the connection     If (conn.State <> ConnectionState.Open) Then       conn.Open()     End If     Dim adapter As SqlDataAdapter = New SqlDataAdapter _     ("SELECT EmployeeID, LastName, FirstName FROM Employees", conn)     Dim ds As DataSet = New DataSet()     adapter.Fill(ds, "Employees")     ' Bind data set to the control     ' Set DataSource property of ListBox as DataSet's DefaultView     ListBox1.DataSource = ds     ListBox1.SelectedIndex = 0     ' Set Field Name you want to get data from     ListBox1.DataTextField = "FirstName"     'DataGrid1.DataSource = ds.DefaultViewManager     DataGrid1.DataSource = ds     ' DataGrid1.DataBind()     DataBind()     '  Close the connection     If (conn.State = ConnectionState.Open) Then       conn.Close()     End If   End Sub 

Note

We're using a SQL Server database and the SqlClient data provider, which means we import the System.Data.SqlClient namespace to the application.

As you can see, this code looks familiar. First you create a Connection object with the Northwind database. After that you create a DataAdapter and select the EmployeeID, FirstName, and LastName columns from the Employees table. Then you create a DataSet object and fill it using the DataAdapter's Fill method. Once you have a DataSet, you set the DataSet as the ListBox's DataSource property and set SelectIndex as 0. The SelectIndex property represents the index of the column from a DataSet you want to display in the control. The field name of the column is FirstName. You also set the DataSource property of DataGrid control as DataSet. At the end you call the DataBind method of the ListBox. This method binds the data to the data-bound controls.

The output looks like Figure 14-13. As you can see, the ListBox control displays data from the FirstName column of the Employees table, and DataGrid displays all three columns.

Figure 14-13: Your first ADO.NET Web application

In Figure 14-13, you may notice the DataGrid format looks different. You can set a DataGrid format by using the Auto Format option of the Properties window.