11.3 Getting Started with ADO.NET

In the coming examples, you'll create a more complex display with a DataGrid, and you'll display data from multiple tables, but to get started, you'll keep it as simple as possible. In this first example, you'll create a simple Web Form with a single list box called lbBugs. You'll populate this list box with bits of information from the Bugs table in the ProgASPDotNetBugs database.

Create a new C# ASP.NET web application project named SimpleBugListBox. Drag a list box onto the form and name it lbBugs (that is, change the value of its ID property to lbBugs). The list box will size itself to fit the data, so you can leave its default size for now.

Example 11-2 is the complete C# source for the code-behind page; code lines that are not generated by Visual Studio automatically are shown in boldface. Analysis follows the listing.

Example 11-2. A simple ADO.NET example

using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace SimpleBugsListBox {    /// <summary>    /// Summary description for WebForm1.    /// </summary>    public class WebForm1 : System.Web.UI.Page    {       protected System.Web.UI.WebControls.ListBox lbBugs;           public WebForm1(  )       {          Page.Init += new System.EventHandler(Page_Init);       }       private void Page_Load(object sender, System.EventArgs e)       {          // connect to the Bugs database        string connectionString =            "server=YourServer; uid=sa;             pwd=YourPassword; database=ProgASPDotNetBugs";        // get records from the Bugs table        string commandString =            "Select BugID, Description from Bugs";        // create the DataAdapter         // and the DataSet        SqlDataAdapter dataAdapter =            new SqlDataAdapter(           commandString, connectionString);        DataSet dataSet = new DataSet(  );        // fill the dataset object        dataAdapter.Fill(dataSet,"Bugs");        // Get the one table from the DataSet        DataTable dataTable = dataSet.Tables[0];                     // for each row in the table, display the info        foreach (DataRow dataRow in dataTable.Rows)        {           lbBugs.Items.Add(              dataRow["BugID"].ToString(  ) +               ": " + dataRow["Description"].ToString(  )  );        }       }       private void Page_Init(object sender, EventArgs e)       {          InitializeComponent(  );       }       #region Web Form Designer generated code       private void InitializeComponent(  )       {              this.Load += new System.EventHandler(this.Page_Load);       }       #endregion    } }

With just about eight lines of code in the Page.Load event handler, you have extracted a set of data from the database and displayed it in the list box, as shown in Figure 11-6.

Figure 11-6. Displaying the list of bugs

The eight lines accomplished the following tasks:

1. Created the string for the connection. The connection string is whatever string is needed to connect to the database, in the case of our example:

   string connectionString =     "server=YourServer; uid=sa; " +    "pwd=YourPassword; database=ProgASPDotNetBugs";

2. Created the string for the select statement, which generates a table containing bug IDs and their descriptions:

   string commandString =     "Select BugID, Description from Bugs";

3. Created the DataAdapter to extract the data from the SQL Server database and pass in the selection and connection strings:

   SqlDataAdapter dataAdapter =  new SqlDataAdapter( commandString, connectionString);

4. Created a new DataSet object:

   DataSet dataSet = new DataSet(  );

5. Filled the dataset with the data obtained from the SQL select statement using the DataAdapter:

   dataAdapter.Fill(dataSet,"Bugs");

6. Extracted the data table from the DataTableCollection object:

   DataTable dataTable = dataSet.Tables[0];

7. Iterated the rows in the data table to fill the list box:

   foreach (DataRow dataRow in dataTable.Rows) {    lbBugs.Items.Add(       dataRow["BugID"] +        ": " + dataRow["Description"]  ); }

The Visual Basic .NET Page_Load equivalent is shown in Example 11-3.

Example 11-3. Page_Load in VB.NET

Private Sub Page_Load(ByVal sender As System.Object, _    Dim connectionString As String    connectionString = _       "Server=YourServer; uid=sa; pwd=YourPassword; " + _       "database=ProgASPDotNetBugs"    Dim commandString As String    commandString = "Select BugID, Description from Bugs"    Dim myDataAdapter As New System.Data.SqlClient.SqlDataAdapter( _        commandString, connectionString)    Dim myDataSet As New DataSet(  )    myDataAdapter.Fill(myDataSet, "Bugs")    Dim myDataTable As DataTable    myDataTable = myDataSet.Tables(0)    Dim theRow As DataRow    For Each theRow In myDataTable.Rows       lbBugs.Items.Add(theRow("BugID") & ": " & theRow("Description"))    Next End Sub