Querying Data
You will most often be trying to pull data out of your data source to display it on your ASP.NET pages. To do so, you would use a SQL Select statement. In these examples, the Northwind database is the data source and a DataReader is populated with the results of the SQL command.
DataReader
Remember, a DataReader is a forward-only group of rows of data. You can iterate through the DataReader to display its contents with the DataReader's Read() method, and you can find many examples of that in the SDK. I've also read that many sources are saying that a DataReader cannot be databound to the server controls you saw in the last chapter. I have not found that to be the truth, and I stand as the shining light in the darkness of misinformation. The following is an example of a DataReader being databound to a DataGrid just like the ones you saw in Chapter 9.
Visual Basic .NET ado_query_reader_sql_vb/cs.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() dim OurConnection as SqlConnection dim OurCommand as SqlCommand dim OurDataReader as SqlDataReader OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password;database=Northwind") OurConnection.Open() OurCommand = New SqlCommand("Select Top 15 ProductName,UnitPrice From Products" ,
C# ado_query_reader_sql_vb/cs.aspx
<%@ page language="cs" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load(){ SqlConnection OurConnection; SqlCommand OurCommand; SqlDataReader OurDataReader; OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurConnection.Open(); OurCommand = new SqlCommand("Select Top 20 ProductName,UnitPrice From Products" , OurConnection); OurDataReader = OurCommand.ExecuteReader(); OurDataGrid.DataSource=OurDataReader; OurDataGrid.DataBind(); OurDataReader.Close(); OurConnection.Close(); } </script> <html> <head> <title>ADO DataReader</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> </body> </html> As you can see in Figure 10.1, the DataReader bound the data just marvelously to the DataGrid. In this example, I first created variables that represented the object types with which they would be filled. Then I used SQLConnection to connect to the Northwind SQL Server database. I then used the SQLCommand to pass a SQL statement to the database to retrieve the ProductName and UnitPrice columns from the Products table.
Figure 10.1. The DataReader returned the results of the Select statement.
Next, I used the SQLCommand's ExecuteReader() method to populate the DataReader variable with the results from the SQLCommand. Next, as I've said and many would contradict, I bound the DataReader to the DataGrid.
The last thing to do is close the DataReader and then the Connection. When using a DataReader, you must do this explicitly, because the DataReader actively uses the Connection to directly populate the data to its destination and isn't capable of destroying itself or the Connection.
This isn't the case with the DataSet, though. The DataSet is a disconnected, memory-based version of the data that you are operating on from the database. It inherently manages its own cleanup, and you won't need to issue any Close() methods for it. Take a look at the DataSet now.
DataSet
Do you remember that I said that the DataSet is a collection of DataTable roadway decking in the bridge to the database? The DataSet is capable of holding and manipulating the contents of many in-memory tables of data. These tables are called DataTables. Look at an example of delivering many tables of data to an ASP.NET web page with the DataSet.
Visual Basic .NET ado_query_dataset_vb.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() dim OurConnection as SQLConnection dim OurCommand as SQLCommand dim OurCommand2 as SQLCommand dim OurDataAdapter as SQLDataAdapter dim OurDataAdapter2 as SQLDataAdapter dim OurDataSet as New DataSet() OurConnection = New SQLConnection("Server=server;uid=newriders;pwd=password;
C# ado_query_dataset_cs.aspx
<%@ page language="c#" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load(){ SqlConnection OurConnection; SqlCommand OurCommand; SqlCommand OurCommand2; SqlDataAdapter OurDataAdapter; SqlDataAdapter OurDataAdapter2; DataSet OurDataSet; OurDataSet = new DataSet(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurCommand = new SqlCommand("Select Top 10 ProductName, QuantityPerUnit, UnitPrice, UnitsInStock From Products",OurConnection); OurCommand2 = new SqlCommand("Select CategoryName,Description From Categories", OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter2 = new SqlDataAdapter(OurCommand2); OurDataAdapter.Fill(OurDataSet, "Products"); OurDataAdapter2.Fill(OurDataSet, "Categories"); OurDataGrid.DataSource = OurDataSet.Tables["Products"]; OurDataGrid2.DataSource = OurDataSet.Tables["Categories"]; DataBind(); } </script> <html> <head> <title>ADO DataSet</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> <br> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Category Name" DataField="CategoryName" /> <asp:BoundColumn HeaderText="Description" DataField="Description" /> </Columns> </asp:DataGrid> </body> </html> Again, as you can see, the SQLConnection and SQLCommand do exactly the same thing as a DataReader does, but you actually create two commands and use the SQLDataAdapter objects to insert the selected data into two different tables in the dataset. Then you bind the data from these two different DataTables in the DataSet to two different DataGrid objects and DataBind(), then voilá!! Notice that I didn't have to explicitly execute a DataBind() on each table. I basically caused databinding on all objects that had data sources and that could be databound by calling the DataBind() method without any specific object ID. You can see the results in Figure 10.2.
Figure 10.2. The DataSet object can hold multiple tables of data.
DataRelation
In pre-.NET ADO, there was a function of dealing with related data that was called data shaping. It allowed you to iterate through one parent table and iterate through the child data for each record of parent data. For instance, imagine you had a Fruit table and a Fruit Type table. The parent Fruit table would have many related Fruit Types.
-
Apples (Fruit)
-
Macintosh (Fruit Type)
-
Red Delicious (Fruit Type)
-
Granny Smith (Fruit Type)
-
-
Berry (Fruit)
-
Raspberry (Fruit Type)
-
Blueberry (Fruit Type)
-
Strawberry (Fruit Type)
-
Data shaping would allow you to define this type of parent/child relationship between two tables. The DataRelation object also allows you to replicate this function, and building multiple level dependencies is as easy as creating additional DataTables and DataRelations.
To create the dependencies, you have to tell the DataRelation object which columns of the DataTables are related. This operates much as a primary key/ foreign key relationship does in a database scenario. In the following example I create a few variables of datatypes you haven't seen yet. The DataColumn and DataRow are objects that make up a DataTable. I use these variables as a way to easily reference my columns when setting DataRelations and reference my rows when iterating through the data.
Visual Basic .NET ado_query_relation_sql_vb.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() dim OurConnection as SQLConnection dim OurCommand as SQLCommand dim OurCommand2 as SQLCommand dim OurDataAdapter as SQLDataAdapter dim OurDataAdapter2 as SQLDataAdapter dim OurDataSet as New DataSet() dim OurDataColumn as DataColumn dim OurDataColumn2 as DataColumn dim OurDataRelation as DataRelation dim Category as DataRow dim Product as DataRow dim ArrRows() as DataRow OurConnection = New SQLConnection("Server=server;uid=newriders;pwd=password;
C# ado_query_relation_sql_cs.aspx
<%@ page language="c#" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load() { SqlConnection OurConnection; SqlCommand OurCommand, OurCommand2; SqlDataAdapter OurDataAdapter, OurDataAdapter2; DataSet OurDataSet = new DataSet(); DataColumn OurDataColumn, OurDataColumn2; DataRelation OurDataRelation; DataRow[] ArrRows; OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurCommand = new SqlCommand("Select Top 10 ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, CategoryID From Products",OurConnection); OurCommand2 = new SqlCommand("Select CategoryName,Description, CategoryID From Categories",OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter2 = new SqlDataAdapter(OurCommand2); OurDataAdapter.Fill(OurDataSet, "Products"); OurDataAdapter2.Fill(OurDataSet, "Categories"); OurDataColumn = OurDataSet.Tables["Products"].Columns["CategoryID"]; OurDataColumn2 = OurDataSet.Tables["Categories"].Columns["CategoryID"]; OurDataRelation = new DataRelation("ProductCategories", OurDataColumn2, OurDataColumn); OurDataSet.Relations.Add(OurDataRelation); foreach(DataRow Category in OurDataSet.Tables["Categories"].Rows) { ArrRows=Category.GetChildRows(OurDataSet.Relations["ProductCategories"]); if (ArrRows.Length > 0){ OurLabel.Text += "<u><b>" + Category["CategoryName"] + "</b></u><br>"; OurLabel.Text += "<ul>"; foreach(DataRow Product in ArrRows) { OurLabel.Text += "<li>" + Product["ProductName"] + "</li><br>"; } OurLabel.Text +="</ul>"; } } } </script> <html> <head> <title>ADO DataRelation</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <asp:Label font-name="verdana" font-size="11px" runat="server" /> </body> </html> What this code does is associate or relate two tables in the DataSet called Products and Categories. The Categories table has a column called CategoryID that is its primary key Field, and the Products table has a CategoryID field that is a foreign key field to CategoryID in the Categories table.
As you can see in Figure 10.3, the DataRelation properly displayed the products that were selected from the Products table under their related categories from the Categories table.
Figure 10.3. The DataRelation object allows you to establish relationships between tables in the DataSet.
Primary Keys and Foreign Keys, although they may sound intimidating, are pretty simple concepts to understand. They are terms used in databases to help identify or relate rows to each other. A Primary Key is a column or group of columns that represent a unique identifier for each row. Often this is an integer that increments automatically by a set value as new rows are added to a table to ensure that no two primary keys are the same. A foreign key is a column or group of columns in a table that represent an identity that can be associated with another table's primary key. Take a look at Figure 10.4 to see a visual of two tables, each with a primary key, and one having a foreign key relationship the other.
Figure 10.4. Primary keys and foreign keys help you relate data in databases and in the DataSet in a parent/child format.
DataView
There may be times when you need specific data that is contained in a DataTable in your DataSet. Maybe you need multiple versions of that data to contain different rows. Enter the DataView. The DataView object in basic form is a copy of the data in a DataTable that you can manipulate independent of the DataTable.
In the following example, I am going to build a DataTable that contains all the products in the Northwind database. Then I will populate two DataViews with that data and filter them differently. One DataView's rows will be filtered by CategoryID = 3, which is all the Confection products, and the other DataView's rows will be filtered by CategoryID = 8, which is all the Seafood products.
Visual Basic .NET ado_query_dataview_vb.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() dim OurConnection as SQLConnection dim OurCommand as SQLCommand dim OurDataAdapter as SQLDataAdapter dim OurDataSet as New DataSet() OurConnection = New SQLConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurCommand = New SQLCommand("Select CategoryID, ProductName, UnitPrice From Products", OurConnection) OurDataAdapter = New SQLDataAdapter(OurCommand) OurDataAdapter.Fill(OurDataSet, "Products") dim OurDataView1 as new DataView(OurDataSet.Tables("Products")) dim OurDataView2 as new DataView(OurDataSet.Tables("Products")) OurDataView1.RowFilter = "CategoryID = 3" OurDataView2.RowFilter = "CategoryID = 8" OurDataGrid.DataSource = OurDataView1 OurDataGrid2.DataSource = OurDataView2 DataBind() End Sub </script> <html> <head> <title>ADO DataView</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <table border="0" cellpadding="0" cellspacing="20"> <tr><td> <h4>Confections</h4> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> </td><td valign="top"> <h4>Seafood</h4> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> </td></tr> </table> </body> </html> C# ado_query_dataview_cs.aspx
<%@ page language="c#" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load() { SqlConnection OurConnection; SqlCommand OurCommand; SqlDataAdapter OurDataAdapter; DataSet OurDataSet = new DataSet(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurCommand = new SqlCommand("Select CategoryID, ProductName, UnitPrice From Products",OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter.Fill(OurDataSet, "Products"); DataView OurDataView1 = new DataView(OurDataSet.Tables["Products"]); DataView OurDataView2 = new DataView(OurDataSet.Tables["Products"]); OurDataView1.RowFilter = "CategoryID = 3"; OurDataView2.RowFilter = "CategoryID = 8"; OurDataGrid.DataSource = OurDataView1; OurDataGrid2.DataSource = OurDataView2; DataBind(); } </script> <html> <head> <title>ADO DataView</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <table border="0" cellpadding="0" cellspacing="20"> <tr><td> <h4>Confections</h4> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> </td><td valign="top"> <h4>Seafood</h4> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> </td></tr> </table> </body> </html> The results of this can be seen in Figure 10.5. The two DataViews have been filtered independently of each other and provide two different views of the data that is contained in the DataTable.
Figure 10.5. DataViews let you make independent copies of DataTables that can filter, sort, and edit.
| Top |
