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.

Displaying the list of bugs

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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.222.231