Beginning ASP.NET 1.1 Databases: From Novice to Professional

• Using the DataReader to work with the results of a query directly from the database
• Saving query results into a DataSet object on the server away from the database
• Creating a DataSet locally with your own data completely away from an external data source

THE DATAREADER OBJECT

• Using a DataReader is quick and efficient, as it doesn't need to worry about keeping track of every bit of data.
• A DataReader doesn't need to store data in memory, so it uses fewer resources in creating a page.

• You can't do anything with the data such as sending changes back to the database. This would mean referring to data already passed through the reader, which isn't possible; DataReaders work only from database to client.
• DataReaders require exclusive access to a connection. Once open, nothing else can use a connection until the DataReader is closed.

SqlDataReader myReader = myCommand.ExecuteReader();

myGrid.DataSource = myReader;
myGrid.DataBind();

Iterating Through a DataReader

//Create the DataReader by calling ExecuteReader()
SqlDataReader myReader = myCommand.ExecuteReader();

//Iterate through the DataReader in a while loop
while (myReader.Read())
{
   .. processing instructions for each row in DataReader
}

//Close DataReader
myReader.Close();

1. In Web Matrix, create a new ASP.NET page called Iterating_Through_A_DataReader.aspx and save it to a new folder under BAND called Chapter06.



2. In Design view, drag a label control onto the blank page. You'll use this to demonstrate that your objects have been created. Make its Text property empty.
3. In All view, make sure the right data provider is included at the top of the page, like so:

   <%@ Page Language="C#" %>
   <%@ import Namespace="System.Data" %>
   <%@ import Namespace="System.Data.SqlClient" %>
   

4. You have two distinct pieces of code to add to the page. The first is the class definition of the Publisher object. In Code view, add the following code:

   public class Publisher
   {
       public string Name;
       public string City;
       public string Email;
       public string Website;
   	
       public Publisher()
       {}
   	
       public string ToString()
       {
   
          string description = "";
          description = "Name : " + this.Name + "<br />";
          description += "City : " + this.City + "<br />";
          description += "Contact : <a href=mailto:" + this.Email + ">" + <--
             this.Email + "</a><br/>";
          description += "Homesite : <a href='" + this.Website + "'>" + <--
             this.Website + "</a><br/><br/>";
   		  
          return description;
      }
   }
   

5. The second piece of code to add is the code for the Page_Load handler.

   void Page_Load(object sender, EventArgs e)
   {
       //set up connection string and SQL query
       string ConnectionString = Convert.ToString( <--
         ConfigurationSettings.AppSettings["MSDEConnectString"]);
       string CommandText = "SELECT PublisherName, PublisherCity, <--
         PublisherContact_Email, PublisherWebsite FROM Publisher";
   	
       //create SqlConnection and SqlCommand objects
       SqlConnection myConnection = new SqlConnection(ConnectionString);
       SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
   
       try
       {
         //open connection to the database
         myConnection.Open();
   
         //run query
         SqlDataReader myReader = myCommand.ExecuteReader();
   
         while (myReader.Read())
         {
            Publisher p = new Publisher();
            p.Name = myReader.GetString(0);
            p.City = myReader.GetString(1);
            p.Email = myReader.GetString(2);
            p.Website = myReader.GetString(3);
   
            Label1.Text += p.ToString();
           }
            myReader.Close();
       }
       catch (Exception ex)
       {
          //If an exception occurs, handle it.
          throw (ex);
       }
       finally
       {
          //Whether you succeed or fail, close the database connection
          myConnection.Close();
       }
   }

6. Now save this code, and then run it. When the page loads, you'll see that the label control contains details of all the publishers in the Publisher table written out, as in Figure 6-2, but not in tabular form. You have hyperlinks that work and an easier-to-read collection of data instead.

public class Publisher
{
    public string Name;
    public string City;
    public string Email;
    public string Website;

public Publisher()
{}

  public string ToString()
  {
    string description = "";
    description = "Name : " + this.Name + "<br />";
    description += "City : " + this.City + "<br />";
    description += "Contact : <a href=mailto:" + this.Email + ">" + <--
       this.Email + "</a><br/>";
    description += "Homesite : <a href='" + this.Website + "'>" + <--
       this.Website + "</a><br/><br/>";


    return description;
  }
}

//run query
SqlDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read())
{

string CommandText = "SELECT PublisherName, PublisherCity,
  PublisherContact_Email, PublisherWebsite FROM Publisher";

Publisher p = new Publisher();
p.Name = myReader.GetString(0);
p.City = myReader.GetString(1);
p.Email = myReader.GetString(2);
p.Website = myReader.GetString(3);

Publisher p = new Publisher();
p.Name = myReader["PublisherName"].ToString();
p.City = myReader["PublisherCity"].ToString();
p.Email = myReader["PublisherContact_Email"].ToString();
p.Website = myReader["PublisherWebsite"].ToString();

    Label1.Text += p.ToString();
}
myReader.Close();