Section 10.2. Getting Started with ADO.NET

10.2. Getting Started with ADO.NET

Create a new web site named SimpleADONetGridView. Drag a GridView onto the page and accept all its default values. Do not attach a data source. Switch to the code-behind file. In the code-behind page, you will create a DataSet and then assign one of the tables from that DataSet to the DataSource property of the GridView .

To get started, add a using statement for the SqlClient namespace to your source code:

 using System.Data.SqlClient; 

You'll need to add this using statement in all the examples in this chapter.

That done, you will implement the Page_Load method to get the Customers table from the Northwind database and bind it to your GridView . You do this in a series of steps:

1. Create a connection string and a command string.

2. Pass the strings to the constructor of the SqlDataAdapter .

3. Create an instance of a DataSet .

4. Ask the DataAdapter to fill the DataSet .

5. Extract the table from the DataSet .

6. Bind the GridView to that table.

The complete source code for this example is shown in Example 10-1.

Example 10-1. SimpleADONetGridView Default.aspx.csSource

 using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         // 1. Create the connection string and command string         string connectionString =           "Data Source=Mozart;Initial Catalog=Northwind;Integrated Security=True";         string commandString =         "Select * from Customers";         // 2. Pass the strings to the SqlDataAdapter constructor         SqlDataAdapter dataAdapter =             new SqlDataAdapter(                 commandString, connectionString);         // 3. Create a DataSet         DataSet dataSet = new DataSet(  );  // 4. fill the dataset object         dataAdapter.Fill(dataSet,"Customers");         // 5. Get the table from the dataset         DataTable dataTable = dataSet.Tables["Customers"];         // 6. Bind to the Gridview         GridView1.DataSource=dataTable;  GridView1.DataBind(  );     } } 

Easy as pie. The result is indistinguishable from using a DataSource control, as shown in Figure 10-3.

10.2.1. Using a DataReader

In the previous example, the grid was filled from a table in a dataset. Though datasets are powerful, disconnected data sources, they may require more overhead than you want. If you want to retrieve a set of records and then immediately display them, an SqlDataReader object, introduced above, may be more efficient.

Figure 10-3. SimpleADONetGridView - a GridView from ADO.NET

DataReaders are limited compared to datasets (see below). They offer only a "firehose" cursor for forward-only iteration through a set of results.

To demonstrate a DataReader , create a new website named SimpleDataReader and use Copy Web Site to copy over SimpleADONetGridView . Copy all the old files over the new files created by VS2005 and open Default.aspx.cs and replace the existing code with the code shown in Example 10-2.

Example 10-2. Default.aspx.cs SimpleDataReader

 using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         // Create the connection string         string connectionString =             "Data Source=Mozart;Initial Catalog=Northwind;Integrated                     Security=True";         string commandString = "Select * from Customers";         // Create the connection object         SqlConnection conn = new SqlConnection(connectionString);         // Create a command object         SqlCommand command = new SqlCommand(commandString);         // open the connection         try         {             // open the connection             conn.Open(  );             // attach connection to command object             command.Connection = conn;             // get the data reader  SqlDataReader reader =                 command.ExecuteReader(CommandBehavior.CloseConnection);  // bind to the data reader             GridView1.DataSource = reader;             GridView1.DataBind(  );         }         finally         {             conn.Close(  );   // make sure the connection closes         }     } } 

The connection is opened in a TRy block and closed in the finally block. Database transactions are limited resources, and it is important to ensure they are closed. Normally, you would catch any exceptions and handle them, but you do want to make sure that whatever happens, the connection is explicitly closed before leaving this method.

You begin by setting the connection string and command string as you did previously. The last time you passed your Connection string and a Command string to the DataAdapter object, which implicitly created a Connection object and a Command object for you. This time you create those objects explicitly:

 SqlConnection conn = new SqlConnection(connectionString);     SqlCommand command = new SqlCommand(commandString); 

Once your Command object is established, create the DataReader . You cannot call the DataReader 's constructor directly; instead, you call ExecuteReader on the SqlCommand object. What you get back is an instance of SqlDataReader :

 SqlDataReader reader =         command.ExecuteReader(CommandBehavior.CloseConnection); 

You can now bind the GridView to the DataReader you created:

 GridView1.DataSource = reader;     GridView1.DataBind(  ); 

Run the application, and the DataReader acts as the data source, populating your grid, as shown in Figure 10-4.

Figure 10-4. DataReader Grid View

DataReaders have less overhead that DataSets (covered next ); when you use a DataReader , it can be more efficient to do so. That said, there are significant limitations to DataReaders . For one, DataReaders are not disconnected.

In any case, you will need a dataset to meet any of the following requirements:

• To pass a disconnected set of data to another tier in your application or to a client application.

• To persist your results either to a file or to a Session object.

• To provide access to more than one table and to relationships among the tables.

• To bind the same data to multiple controls. Remember, a DataReader object provides forward-only access to the data; you cannot reiterate through the data for a second control.

• To jump to a particular record or to go backwards through a set of data.

• To update a number of records in the back-end database using a batch operation.

10.2.2. Creating Data Relations Within DataSets

Because the DataSet acts as a disconnected model of the database, it must be able to represent the tables within the database and the relations among the tables as well.

The DataSet captures these relationships in a DataRelationCollection that you access through the read-only Relations property. The DataRelationCollection is a collection of DataRelation objects, each of which represents a relationship between two tables.

Each DataRelation object relates a pair of DataTable objects to each other through DataColumn objects. The relationship is established by matching columns in the two tables, such as matching a customer's orders to the customer by matching the CustomerID column in both tables (see Appendix B).

The DataRelation objects retrieved through the Relations property of the DataSet provides you with metadata : data about the relationship among the tables in the database. You can use the metadata in a number of ways. For example, you can generate a schema for your database from the information contained in the dataset.

In the next example, you will create DataRelation objects to model two relationships within the Northwind database. The first DataRelation object you create will represent the relationship between the Orders table and the Order Details table through the OrderID . The second relationship you will model is between the Order Details table and the Products table through the ProductID .

To begin, create a new web site name DataRelations. On the default.aspx page, add three grids, one of which is in a panel. The first grid represents the orders and displays five orders at a time, with a button to select a particular order:

 <asp:GridView ID="GridView1" runat="server"        CellPadding="4"        ForeColor="#333333"        GridLines="None"        DataKeyNames="OrderID"        AutoGenerateColumns="False"        PagerSettings-Mode="Numeric"        AllowPaging="true"        PageSize="5"        OnSelectedIndexChanged="OnSelectedIndexChangedHandler" >        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />        <EditRowStyle BackColor="#999999" />        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />        <Columns>           <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Details" />           <asp:BoundField DataField="OrderID" HeaderText="Order ID" />           <asp:BoundField DataField="OrderDate" HeaderText="Order Date" />           <asp:BoundField DataField="CompanyName" HeaderText="Company" />           <asp:BoundField DataField="ContactTitle" HeaderText="Contact" />           <asp:BoundField DataField="Phone" HeaderText="Phone" />        </Columns>     </asp:GridView> 

A few things to notice about this grid. First, it is set up for paging so you see only five rows at a time:

 PagerSettings-Mode="Numeric"     AllowPaging="true"     PageSize="5" 

Second, the columns are not automatically created; they will be created at your discretion. This was done by using the Edit Columns choice from the smart tag which brings up the Fields editing dialog, as shown in Figure 10-5. Ensure the fields to be displayed are the fields in the table you bind to.

Figure 10-5. Setting the fields for GridView1

The second data grid is in a panel so you can show or hide it as necessary. This second data grid displays the details for a particular order:

  <asp:Panel ID="OrderDetailsPanel" runat="server" Height="50px" Width="125px">  <asp:GridView ID="DetailsGridView" runat="server"            AutoGenerateColumns="False"            BackColor="LightGoldenrodYellow"            BorderColor="Tan"            BorderWidth="1px"            CellPadding="2"            ForeColor="Black"            GridLines="None">               <FooterStyle BackColor="Tan" />               <Columns>                  <asp:BoundField DataField="OrderDate" HeaderText="Order Date" />                  <asp:BoundField DataField="ProductName" HeaderText="Product" />                  <asp:BoundField DataField="UnitPrice" HeaderText="Price" />                  <asp:BoundField DataField="Quantity" HeaderText="Quantity" />               </Columns>            <PagerStyle BackColor="PaleGoldenrod"               ForeColor="DarkSlateBlue" HorizontalAlign="Center" />            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />            <HeaderStyle BackColor="Tan" Font-Bold="True" />            <AlternatingRowStyle BackColor="PaleGoldenrod" />         </asp:GridView>     </asp:Panel> 

This grid turns off AutoGenerateColumns and shows only the columns of interest.

Finally, create a third grid that will display the relations in the dataset:

 <asp:GridView ID="OrderRelationsGridView" runat="server"        BackColor="White"        BorderColor="#CC9966"        BorderStyle="None"        BorderWidth="1px"        CellPadding="4">        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />        <RowStyle BackColor="White" ForeColor="#330099" />        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />     </asp:GridView> 

The complete code-behind for this example is shown in Example 10-3 and analyzed immediately after.

Example 10-3. Data Relations Default.aspx.cs

 using System;  using System.Text;                // for string builder  using System.Data;  using System.Data.SqlClient;      // for dataadapter, etc  . using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             UpdateDetailsGrid(  );             DataSet ds = CreateDataSet(  );             GridView1.DataSource = ds.Tables[0];             GridView1.DataBind(  );             // create the dataview and bind to the details grid             DataView detailsView = new DataView(ds.Tables[1]);             DetailsGridView.DataSource = detailsView;             Session["DetailsView"] = detailsView;             DetailsGridView.DataBind(  );             // bind the relations grid to the relations collection             OrderRelationsGridView.DataSource = ds.Relations;             OrderRelationsGridView.DataBind(  );         }     }     // get order details     public void OnSelectedIndexChangedHandler(        Object sender, EventArgs e)     {         UpdateDetailsGrid(  );     }     private void UpdateDetailsGrid(  )     {         int index = GridView1.SelectedIndex;         if (index != -1)         {             // get the order id from the data grid             DataKey key = GridView1.DataKeys[index];             int orderID = (int) key.Value;             DataView detailsView = (DataView)Session["detailsView"];             detailsView.RowFilter = "OrderID = " + orderID;             DetailsGridView.DataSource = detailsView;             DetailsGridView.DataBind(  );             OrderDetailsPanel.Visible = true;         }         else         {             OrderDetailsPanel.Visible = false;         }     }  private DataSet CreateDataSet(  )     {         // connection string to connect to the Orders Database         string connectionString =         "Data Source=Mozart;Initial Catalog=Northwind;Integrated Security=True";         // Create connection object, initialize with         // connection string and open the connection         System.Data.SqlClient.SqlConnection connection =            new System.Data.SqlClient.SqlConnection(connectionString);         connection.Open(  );         // Create a SqlCommand object and assign the connection         System.Data.SqlClient.SqlCommand command =            new System.Data.SqlClient.SqlCommand(  );         command.Connection = connection;         StringBuilder s = new StringBuilder(             "select OrderID, c.CompanyName, c.ContactName, ");         s.Append(" c.ContactTitle, c.Phone, orderDate");         s.Append(" from orders o ");         s.Append("join customers c on c.CustomerID = o.CustomerID");         // set the command text to the select statement         command.CommandText = s.ToString(  );         // create a data adapter and assign the command object         // and add the table mapping for bugs         SqlDataAdapter dataAdapter = new SqlDataAdapter(  );         dataAdapter.SelectCommand = command;         dataAdapter.TableMappings.Add("Table", "Orders");         // Create the dataset and use the data adapter to fill it         DataSet dataSet = new DataSet(  );         dataAdapter.Fill(dataSet);         // create a second command object for the order details         System.Data.SqlClient.SqlCommand command2 =            new System.Data.SqlClient.SqlCommand(  );         command2.Connection = connection;         // This time be sure to add a column for Severity so that you can         // create a relation to Products         StringBuilder s2 =            new StringBuilder(            "Select od.OrderID, OrderDate, p.ProductID, ");         s2.Append(" ProductName, od.UnitPrice, Quantity ");         s2.Append("from Orders o ");         s2.Append("join [Order Details] od on o.orderid = od.orderid ");         s2.Append("join products p on p.productID = od.productid ");         command2.CommandText = s2.ToString(  );         // create a second data adapter and         // add the command and map the table         // then fill the dataset  from this second adapter         SqlDataAdapter dataAdapter2 = new SqlDataAdapter(  );         dataAdapter2.SelectCommand = command2;         dataAdapter2.TableMappings.Add("Table", "Order Details");         dataAdapter2.Fill(dataSet);         // create a third command object for the Products table         System.Data.SqlClient.SqlCommand command3 =            new System.Data.SqlClient.SqlCommand(  );         command3.Connection = connection;         string strCommand3 = "Select ProductID, ProductName from Products";         command3.CommandText = strCommand3;         // create a third data adapter         // and add the command and map the table         // then fill the dataset  from this second adapter         SqlDataAdapter dataAdapter3 = new SqlDataAdapter(  );         dataAdapter3.SelectCommand = command3;         dataAdapter3.TableMappings.Add("Table", "Products");         dataAdapter3.Fill(dataSet);         // declare the DataRelation and DataColumn objects         System.Data.DataRelation dataRelation;         System.Data.DataColumn dataColumn1;         System.Data.DataColumn dataColumn2;         // set the dataColumns to create the relationship         // between Bug and Order Details on the OrderID key         dataColumn1 =             dataSet.Tables["Orders"].Columns["OrderID"];         dataColumn2 =            dataSet.Tables["Order Details"].Columns["OrderID"];         dataRelation =            new System.Data.DataRelation(            "OrdersToDetails",            dataColumn1,            dataColumn2);         // add the new DataRelation to the dataset         dataSet.Relations.Add(dataRelation);         // reuse the DataColumns and DataRelation objects         // to create the relation between Order Details and Products         dataColumn1 = dataSet.Tables["Products"].Columns["ProductID"];         dataColumn2 = dataSet.Tables["Order Details"].Columns["ProductID"];         dataRelation =            new System.Data.DataRelation(            "ProductIDToName",            dataColumn1,            dataColumn2);         // add the HistoryToSeverity relationship to the dataset         dataSet.Relations.Add(dataRelation);         return dataSet;     }     // end createDataSet  }           // end class 

The key to the code-behind page is in the CreateDataSet method, shown highlighted. The job of this method is to create a dataset with three tables and two sets of relations. A connection to the Northwind database is created and opened as you've seen previously.

The first select statement is created to fill the Orders table in the DataSet :

 StringBuilder s = new StringBuilder(         "select OrderID, c.CompanyName, c.ContactName, ");     s.Append(" c.ContactTitle, c.Phone, orderDate");     s.Append(" from orders o ");     s.Append("join customers c on c.CustomerID = o.CustomerID");     command.CommandText = s.ToString(  );     SqlDataAdapter dataAdapter = new SqlDataAdapter(  );     dataAdapter.SelectCommand = command;     dataAdapter.TableMappings.Add("Table", "Orders");     // Create the dataset and use the data adapter to fill it     DataSet dataSet = new DataSet(  );     dataAdapter.Fill(dataSet); 

A second command object is used to create the Order Details table, which will provide the order details for a given order, joining the Product table to turn a ProductID (stored in Order Details) into a product name:

 System.Data.SqlClient.SqlCommand command2 =        new System.Data.SqlClient.SqlCommand(  );     command2.Connection = connection;     StringBuilder s2 =        new StringBuilder(        "Select od.OrderID, OrderDate, p.ProductID, ");     s2.Append(" ProductName, od.UnitPrice, Quantity ");     s2.Append("from Orders o ");     s2.Append("join [Order Details] od on o.orderid = od.orderid ");     s2.Append("join products p on p.productID = od.productid ");     command2.CommandText = s2.ToString(  );     SqlDataAdapter dataAdapter2 = new SqlDataAdapter(  );     dataAdapter2.SelectCommand = command2;     dataAdapter2.TableMappings.Add("Table", "Order Details");     dataAdapter2.Fill(dataSet); 

The second command object shares the original connection. The second table is added to the original dataset using the second DataAdapter . Similarly, a third command object is used to fill a third table: Products.

With the three tables in place, two DataRelation objects will be created. To get started, you'll create an instance of a DataRelation and two instances of DataColumn objects:

 System.Data.DataRelation dataRelation;     System.Data.DataColumn dataColumn1;     System.Data.DataColumn dataColumn2; 

You assign each of the DataColumn objects to a column in a table that creates a relationship:

 dataColumn1 = dataSet.Tables["Orders"].Columns["OrderID"];     dataColumn2 = dataSet.Tables["Order Details"].Columns["OrderID"]; 

This corresponds to the OrderID column in Order Details being a foreign key into the Orders table, where it is the primary key. With these two data columns in place, you can create the DataRelation object:

 dataRelation =        new System.Data.DataRelation(           "OrdersToDetails",           dataColumn1,           dataColumn2); 

The constructor for the DataRelation object takes an (arbitrary) name for the data relation, in this case, "OrdersToDetails." You can now add your new DataRelation object to the Relations collection in the dataset:

 dataSet.Relations.Add(dataRelation); 

Then reuse the DataColumns to establish the relationship between the ProductID column in Order Details and the ProductID column in Products, create a second DataRelation object, and add that to the DataSet as well.

That done, you return the DataSet to the calling method, in this case Page_Load . Once Page_Load has the newly created dataset, it can bind the first GridView to the first table in the dataset:

 protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             UpdateDetailsGrid(  );  DataSet ds = CreateDataSet(  );             GridView1.DataSource = ds.Tables[0];             GridView1.DataBind(  );  

It can get a DataView (a filtered view) of the second table and stash that in SessionState and bind the Details table to that view:

 DataView detailsView = new DataView(ds.Tables[1]);     DetailsGridView.DataSource = detailsView;     Session["DetailsView"] = detailsView;     DetailsGridView.DataBind(  ); 

Finally, Page_Load can bind the Relations collection to the third GridView to display all the relations in the dataset:

 OrderRelationsGridView.DataSource = ds.Relations;     OrderRelationsGridView.DataBind(  ); 

Because the details panel's Visible property is initially set false , when you first open the page, you see only the Orders grid and the relations grid, as shown in Figure 10-6.

Figure 10-6. Relations page first view

If you click on a Details button, the OnSelectedIndexChanged method will fire (as set declaratively in the first GridView ):

 public void OnSelectedIndexChangedHandler(Object sender, EventArgs e)     {        UpdateDetailsGrid(  );     } 

This method calls the UpdateDetailsGrid method, which asks the GridView for its DataKeys collection and then extracts the DataKey using the GridView 's selected index and translates the key's value to an integer (ordered).

It then extracts the details view from session state, applies a row filter (to get only those rows with the appropriate OrderID) and binds the second gridView to the resulting view, which makes the panel visible.

 private void UpdateDetailsGrid(  )     {         int index = GridView1.SelectedIndex;         if (index != -1)         {             // get the order id from the data grid  DataKey key = GridView1.DataKeys[index];             int orderID = (int) key.Value;             DataView detailsView = (DataView)Session["detailsView"];             detailsView.RowFilter = "OrderID = " + orderID;             DetailsGridView.DataSource = detailsView;             DetailsGridView.DataBind(  );             OrderDetailsPanel.Visible = true;  }         else         {             OrderDetailsPanel.Visible = false;         }     } 

The result is that the details for the given order are displayed in the panel, as shown in Figure 10-7.