Special Edition Using ASP.Net
The DataSet Object
The DataSet object is the core component of ADO.NET. It is best described as a collection of disconnected recordsets that contain a hierarchy of table, row, and column data. A major difference between DataSet objects and a group of disconnected recordsets is that the DataSet keeps track of the relationships between tables internally. With a DataSet , the information requested is cached on the client and disconnected from the server. As a result, the DataSet has no knowledge of its data sources, so separate objects must pass information between the DataSet and the data source. Figure 9.5 graphically illustrates the hierarchy of information for the DataSet and its internal relationships. Figure 9.5. DataSet hierarchy.
Using the disconnected model minimizes resources for open connections and server load. A typical use of a DataSet would include the following steps:
Another important distinction of the DataSet from its ADO Recordset predecessor is its capability to track changes as they are made to its internal data and provide error handling on a row-by-row basis. In the prior ADO Recordset model, changes were made and passed back to the server. The recordset would either succeed or fail. Now with the DataSet model, row errors can be trapped before the data is passed back to the database.
Note One of the most challenging feats for a developer working with ADO.NET will be using the disconnected DataSet model. ADO.NET uses this model for two main reasons: First, scalability reduces the demands placed on database servers, and second, it has XML support. Using XML enables DataSet objects to be independent of databases or a query language, leaving the data bound to a user -defined interface.
DataAdapter
The DataAdapter object is much like the Command object, but it is used specifically to access and manipulate information in a DataSet . The Command object has only one CommandText property, whereas the DataAdapter contains four: one for SelectCommand , InsertCommand , DeleteCommand , and UpdateCommand . DataAdapter also contains two methods for receiving and sending data to a database. The Fill method populates a DataSet , whereas the Update method sends data from the DataSet back to the data source. Fill Method
The Fill method uses two parameters to populate a DataSet : the DataSet object and the name of the table to associate with the data being loaded. Listings 9.18 through 9.21 are examples of using a DataAdapter object and filling a DataSet with the Fill method. Listing 9.18 SQL ImplementationVisual Basic Example
Dim myConnection as new SQLConnection("server=localhost;uid=sa;pwd=; database=pubs") Dim myDataAdapter as new SQLDataAdapter("select * from Authors",myConnection) Dim myDataSet as new DataSet myDataAdapter.Fill(myDataSet,"Authors") Listing 9.19 SQL ImplementationC# Example
SQLConnection myConnection = new SQLConnection("server=localhost;uid=sa; pwd=; Listing 9.20 OLE DB ImplementationVisual Basic Example
Dim myConnection as new OleDbConnection("Provider=SQLOLEDB; Data Source=localhost;uid=sa; Listing 9.21 OLE DB ImplementationC# Example
OleDbConnection myConnection = new OleDbConnection("Provider=SQLOLEDB; Data Right away you will notice several differences from a Managed Provider implementation of returning data. First, the connection to the database is not specifically opened or closed. The Fill method encapsulates these calls, so the connection is automatically handled. After the data is returned, the data is cached in the DataSet and the connection terminated , thus the disconnected DataSet model. Second, the Fill method populates a table named Authors in the DataSet . Because the table was not predefined in the DataSet before the Fill method was called, the SqlClient and OleDb DataAdapter objects automatically create the table schema if one is not predefined. If a table schema is created before the table is loaded in the DataSet , it uses the defined one. Therefore, if an Authors table schema exists before the Fill method is executed, the DataAdapter simply fills the existing defined schema. If you use the FillSchema method, the DataSet 's schema is forced to match the schema of the database. Because there are no physical relationships between the DataSet and the DataAdapter , the DataSetAdapter can be used to fill any number of DataSet instances. For example, I could add another table to the examples in Listings 9.18 through 9.21 by using the following code: Dim myConnection as new SQLConnection("server=localhost;uid=sa;pwd=; database=pubs") Dim myDataAdapter as new SQLDataAdaoter("select * from Authors",myConnection) Dim myDataSet as new DataSet myDataAdapter.Fill(myDataSet,"Authors") myDataAdapter.CommandText = "select * from Publishers" myDataAdapter.Fill(myDataSet,"Publishers") Now the DataSet contains two different tablesone Authors table and one Publishers tablewith completely different structures and data. Update
The Update method is used to send data from the DataSet back to the data source. Just like the Fill method, it also takes two parameters: the DataSet object and the table name reference. The connection handling is done automatically. Listing 9.22 takes the Fill method example, modifies some of the rows, and passes it back to the database. Listing 9.22 Modifying Data with the Fill MethodVisual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <head> <script language="VB" runat="server" ID=Script1> Sub Page_Load(Sender as Object, E as EventArgs) Dim myConnection as new SQLConnection("server=localhost;uid=sa;pwd=; database=pubs") Dim myDataAdapter as new SQLDataAdapter("select * from Authors", myConnection) Dim myDataSet as DataSet myDataAdapter.Fill(myDataSet,"Authors") Dim myDataView as DataView myDataView = new DataView(myDataSet.Tables("Authors")) ShowData(myDataView) myDataSet.Tables("Authors").Rows(0)("au_fname") = "John" myDataSet.Tables("Authors").Rows(0)("au_lname") = "Doe" myDataSet.Tables("Authors").Rows(1)("au_fname") = "Jane" myDataSet.Tables("Authors").Rows(1)("au_lname") = "Doe" myDataAdapter.Update(myDataSet,"Authors") End Sub Sub ShowData (myDataView as DataView) Dim I as integer Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") for I = 0 to myDataView.Count - 1 Response.Write("<tr><td>") Response.Write(myDataView(I)("au_id").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_fname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_lname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("address").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("city").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("state").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("zip").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("phone").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("contract").ToString) Response.Write("</td></tr>") Next Response.Write("</table><br>") End Sub </script> </head> </html> In Listing 9.23, I omit the ShowData function and show only the changes that are required in the Page_load . Listing 9.23 Using the ShowData MethodC# Example
void page_load(Object Sender, EventArgs e) SQLConnection myConnection = new SQLConnection("server=localhost;uid=sa; pwd=; If I change the first and last names of the first two rows of data and then call the Update method, the data is changed in the database table. To show the modified data, I create a DataView object that can easily parse the DataTable , and the ShowData function writes the data to the browser. Another feature of the Update method is its capability to automatically generate commands to complete the Update call if the Insert , Update , or Delete commands are not defined. A SQL update statement is generated based on the rows modified. Table Mappings
The DataAdapter contains a table mapping method that enables the data source table names to be mapped to table names used by the DataSet . Likewise, the returned DataTableMappingCollection object contains a ColumnMappings property that enables column names to be mapped as well. The TableMappings method becomes very useful if you have a SQL command or stored procedure that returns multiple select statements. The following example performs two select statements in a single SQL command: Dim myConnection as new SQLConnection("server=localhost;uid=sa; pwd=;database=pubs") Dim myDataAdapter as new _ SQLDataAdapter("select * from Authors;select * from Publishers", myConnection) myDataSetCOmmand.TableMappings.Add("Table","Authors") myDataSetCOmmand.TableMappings.Add("Table_1","Publishers") Dim myDataSet as DataSet myDataSetCommand.FillDataSet(myDataSet) Using the TableMappings method of the DataAdapter object, you can assign specific table names to each select statement. Each select statement is assigned a default name for the result, starting with "Table." After the first select , each additional mapping requires an underscore and sequence number as the source table name. DataSet Parameter Binding
Using parameter binding with a DataAdapter is similar to the Managed Provider's Command Object implementation. The only additional item of consideration is the DataRowVersion that is used to bind each parameter. Each parameter needs five pieces of information to be bound correctly. These are parameter name, data type, direction, SourceData , and SourceVersion . The first three work exactly like the Managed Provider's Command Object. Now I'll explain the other two. The SourceData parameter tells the DataAdapter which column is to be used when passing data to the data source. The SourceVersion specifies which version of row information in the DataSet should be used for binding. A DataTable can keep track of changes as data changes are made to its internal rows. Data can be in one of four different states at any given time. Table 9.3 describes these states. Table 9.3. The Four States Possible for Data
Look at the "DataTables" section for more information on these states. The next example shows how to bind parameters to a DataAdapter . In this example, shown in Listings 9.24 and 9.25, I need to create two SQL statements: one for the initial DataSet load and another to update the DateSet using the UpdateCommand property when the update method is called. Listing 9.24 Two SQL Statements Get the Job Done in This VB Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="VB" runat="server" ID=Script1> Sub Page_Load() Dim myConnection as new SqlConnection("server=localhost;uid=sa;pwd=;_ database=pubs") Dim myDataAdapter as new SqlDataAdapter("select * from Authors",_ myConnection) myDataAdapter.UpdateCommand = _ New SqlCommand("Update Authors set au_fname=@Fname," + _ "au_lname=@Lname where au_id=@ID",myConnection) Dim myDataSet as DataSet myDataSet = new DataSet() myDataAdapter.Fill(myDataSet,"Authors") Dim myDataView as DataView myDataView = new DataView(myDataSet.Tables("Authors")) ShowData(myDataView) myDataSet.Tables("Authors").Rows(0)("au_fname") = "yyyy" myDataSet.Tables("Authors").Rows(0)("au_lname") = "Smith" myDataSet.Tables("Authors").Rows(1)("au_fname") = "Jane" myDataSet.Tables("Authors").Rows(1)("au_lname") = "Smith" myDataAdapter.UpdateCommand.Parameters.Add(_ new SQLParameter("@Fname",SQLDBType.Varchar,20)) myDataAdapter.UpdateCommand.Parameters("@Fname").Direction = _ ParameterDirection.Input myDataAdapter.UpdateCommand.Parameters("@Fname").SourceColumn = "au_fname" myDataAdapter.UpdateCommand.Parameters("@Fname").SourceVersion = _ DataRowVersion.Current myDataAdapter.UpdateCommand.Parameters.Add(_ new SQLParameter("@Lname",SQLDBType.Varchar,40)) myDataAdapter.UpdateCommand.Parameters("@Lname").Direction = _ ParameterDirection.Input myDataAdapter.UpdateCommand.Parameters("@Lname").SourceColumn = "au_lname" myDataAdapter.UpdateCommand.Parameters("@Lname").SourceVersion = _ DataRowVersion.Current myDataAdapter.UpdateCommand.Parameters.Add(_ new SQLParameter("@ID",SQLDBType.Varchar,11)) myDataAdapter.UpdateCommand.Parameters("@ID").Direction = _ ParameterDirection.Input myDataAdapter.UpdateCommand.Parameters("@ID").SourceColumn = "au_id" myDataAdapter.UpdateCommand.Parameters("@ID").SourceVersion = _ DataRowVersion.Original myDataAdapter.Update(myDataSet,"Authors") End Sub Sub ShowData (myDataView as DataView) Dim I as integer Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") for I = 0 to myDataView.Count - 1 Response.Write("<tr><td>") Response.Write(myDataView(I)("au_id").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_fname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_lname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("address").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("city").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("state").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("zip").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("phone").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("contract").ToString) Response.Write("</td></tr>") Next Response.Write("</table><br>") End Sub </script> </html> Listing 9.25 Two SQL Statements Get the Job Done in This C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="CS" runat="server" ID=Script1> void Page_Load() { SqlConnection myConnection = new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs"); SqlDataAdapter myDataAdapter = new SqlDataAdapter(); myDataAdapter.SelectCommand = new SqlCommand("select * from _ Authors",myConnection); myDataAdapter.UpdateCommand = new SqlCommand( "Update Authors set au_fname=@Fname," + "au_lname=@Lname where au_id=@ID",myConnection); DataSet myDataSet = new DataSet(); myDataAdapter.Fill(myDataSet,"Authors"); DataView myDataView; myDataView = new DataView(myDataSet.Tables["Authors"]); ShowData(myDataView); myDataSet.Tables["Authors"].Rows[0]["au_fname"] = "John"; myDataSet.Tables["Authors"].Rows[0]["au_lname"] = "Smith"; myDataSet.Tables["Authors"].Rows[1]["au_fname"] = "Jane"; myDataSet.Tables["Authors"].Rows[1]["au_lname"] = "Smith"; myDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@Fname",SqlDbType.VarChar,20)); myDataAdapter.UpdateCommand.Parameters["@Fname"].Direction = ParameterDirection.Input; myDataAdapter.UpdateCommand.Parameters["@Fname"].SourceColumn = "au_fname"; myDataAdapter.UpdateCommand.Parameters["@Fname"].SourceVersion = DataRowVersion.Current; myDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@Lname",SqlDbType.VarChar,40)); myDataAdapter.UpdateCommand.Parameters["@Lname"].Direction = ParameterDirection.Input; myDataAdapter.UpdateCommand.Parameters["@Lname"].SourceColumn = "au_lname"; myDataAdapter.UpdateCommand.Parameters["@Lname"].SourceVersion = DataRowVersion.Current; myDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@ID",SqlDbType.VarChar,11)); myDataAdapter.UpdateCommand.Parameters["@ID"].Direction = _ ParameterDirection.Input; myDataAdapter.UpdateCommand.Parameters["@ID"].SourceColumn = "au_id"; myDataAdapter.UpdateCommand.Parameters["@ID"].SourceVersion = DataRowVersion.Original; myDataAdapter.Update(myDataSet,"Authors"); } void ShowData (DataView myDataView) { Response.Write("<table border=1>"); Response.Write("<th>au_id</th><th>au_fname</th>" + "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + "<th>zip</th><th>phone</th><th>contract</th>"); for(int i=0;i<myDataView.Count - 1;i++){ Response.Write("<tr><td>"); Response.Write(myDataView[i]["au_id"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["au_fname"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["au_lname"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["address"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["city"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["state"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["zip"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["phone"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["contract"].ToString()); Response.Write("</td></tr>"); } Response.Write("</table><br>"); } </script> </html> Notice in the example that I have created my own update command to update changes to the Authors table. I have created two SQL statements for the DataAdapter object: one for the initial DataSet load and another to update the DateSet when the update method is called. Because I defined an explicit UpdateCommand , the Update method uses this instead of dynamically creating one. Next, I create the parameter bindings to the names in the update statement. The first and last names are bound using the DataRowVersion.Current property of the table to capture the modified values. I set the ID parameter to use the DataRowVersion.Original property. Because this value was not altered , the Current version would have worked just as well. However, if I had changed its value and it was the table's primary key, I would need to use the Original row version to find a match. Finally, I modify the first two rows of data and call the Update method. DataTable
The DataTable is the primary building block of the DataSet . It is composed of collections containing Row and Column data. The columns collection makes up the schema of the DataTable , whereas the rows collection makes up the actual data in the DataTable . To create a DataTable I can do the following: Dim myDataTable as new DataTable("Employees") Two properties can be set with the table that might be of interest. The CaseSensitive property is used for string comparisons, sorting, searching, and filtering. Set this value to true if you require case-sensitive strings. The other property is MinimumCapacity , which specifies the minimum number of rows to be created before the data is fetched . If performance is critical to your application, you can use this value for optimization. Primary Keys Columns
A general rule in a database is that every table has a column, or group of columns, that uniquely identifies each row in the table. This column (or columns) is called the primary key . To create a primary key, additional properties of the column must be set. The first property is AllowNull . This specifies whether a null value can be used in the column. For a primary key, this must be set to false. The second property of the column must be its uniqueness. In other words, no two values in the column can be identical, so this value must be set true. Now that the properties are set, I can indicate that the column be a primary key for a DataTable . The following example shows how to add a primary key to the Employees table: myDataSet.Tables.Add("Employees") myDataSet.Tables("Employees").Columns.Add("ID",_ System.Type.GetType("System.Int32")) myDataSet.Tables("Employees").Columns("ID").AllowNull = false myDataSet.Tables("Employees").Columns("ID").Unique = true myDataSet.Tables("Employees").PrimaryKey = new DataColumn()_ {myDataSet.Tables("Employees").Columns("ID")} If the primary key needs to be more than one column, you can specify multiple columns for the primary key. AutoIncrement Columns
To use the auto-incrementing column feature, the AutoIncrement property of a column must be set to true. After the property is set, the column starts from the value defined in the column's AutoIncrementSeed property. With each row added, the value of the AutoIncrement column raises by the value held in the AutoIncrementStep property of the column. For example, I'm going to set the ID column of the Employees table to be auto-incrementing. I need to add three lines to the ID column properties. myDataSet.Tables.Add("Employees") myDataSet.Tables("Employees").Columns.Add("ID",_ System.Type.GetType("System.Int32")) myDataSet.Tables("Employees").Columns("ID").AllowNull = false myDataSet.Tables("Employees").Columns("ID").Unique = true myDataSet.Tables("Employees").PrimaryKey = new DataColumn()_ {myDataSet.Tables("Employees").Columns("ID")} myDataSet.Tables("Employees").Columns("ID").AutoIncrement = true myDataSet.Tables("Employees").Columns("ID").AutoIncrementSeed = 1 myDataSet.Tables("Employees").Columns("ID").AutoIncrementStep = 1 DataViews
A DataView is a bindable, customized view of a DataTable. Multiple DataViews can be created from a single DataTable with data sorted in different orders. Additionally, you can add, delete, or edit information in each view. DataRelations
A DataRelation matches a column in one table to a column in another table. Listing 9.26 shows how to create a relationship between the pubs.Authors table and the pubs.Titles table. Listing 9.26 Creating Relationships Between TablesC# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <body> <script language="C#" runat="server" ID=Script1> void Page_Load(Object Sender, EventArgs E) { SQLConnection myConnection = new SQLConnection("server=localhost;_ uid=sa;pwd=;database=pubs"); SQLDataAdapter myEmployeeDA = new SQLDataAdapter("select * from Authors",myConnection); SQLDataAdapter myJobsDA = new SQLDataAdapter("select * from TitleAuthor",myConnection); DataSet myDataSet = new DataSet(); myEmployeeDA.Fill(myDataSet,"Authors"); myJobsDA.Fill(myDataSet,"TitleAuthors"); // Add Relationship myDataSet.Relations.Add( "TitleDetail",myDataSet.Tables["Authors"].Columns["au_id"], myDataSet.Tables["TitleAuthors"].Columns["au_id"]); Response.Write("<table border=1>"); Response.Write("<th>AuthorID</th><th>TitleID</th>"); foreach (DataRow Author in myDataSet.Tables["Authors"].Rows){ Response.Write("<tr><td>" + Author["au_id"].ToString() + "</td> <td><table>"); foreach (DataRow Title in Author.GetChildRows(myDataSet. Relations["TitleDetail"])){ Response.Write("<tr><td>" + Title["title_id"].ToString() + "</td></tr>"); } Response.Write("</table></td></tr>"); } Response.Write("</table>"); } </script> </body> </html> Data Filtering
There are several ways to filter data using ADO.NET objects. The first is using a DataSet 's Select method that uses a SQL-formatted where like clause. The following example shows how a DataSet 's Select method can be used to filter the Authors table already in a DataSet : Dim myDataRow as DataRows() myDataRow = myDataSet.Select("au_fname like 'Jim'") The return value of the Select method is an array of DataRows and can be processed using a For loop. A second way of filtering is to use a DataView object that allows multiple filters placed on a DataSet through data binding. A DataView can be filtered using the same query language rules used with the DataSet 's Select method. Unlike the Select method, the DataView filter is dynamic, so if a row is added to the data and it matches the filter criteria, it will be visible in the view. The example in Listings 9.27 and 9.28 shows how to apply a filter and sort the Authors table. Listing 9.27 Filtering with a DataView Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="VB" runat="server" ID="script1"> Sub Page_Load() If NOT (IsPostBack) Dim myReader as SQLDataReader Dim myConnection as _ new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim myCommand as new SQLCommand("select * from Authors",myConnection) myConnection.Open() myReader = myCommand.ExecuteReader() Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") while(myReader.Read()) Response.Write("<tr><td>") Response.Write(myReader("au_id").ToString) Response.Write("</td><td>") Response.Write(myReader("au_fname").ToString) Response.Write("</td><td>") Response.Write(myReader("au_lname").ToString) Response.Write("</td><td>") Response.Write(myReader("address").ToString) Response.Write("</td><td>") Response.Write(myReader("city").ToString) Response.Write("</td><td>") Response.Write(myReader("state").ToString) Response.Write("</td><td>") Response.Write(myReader("zip").ToString) Response.Write("</td><td>") Response.Write(myReader("phone").ToString) Response.Write("</td><td>") Response.Write(myReader("contract").ToString) Response.Write("</td></tr>") End While Response.Write("</table>") myConnection.Close() myReader = Nothing ' Get the column names for the dropdownlist. myCommand.CommandText = _ "select name from syscolumns where id = " + _ "(select id from sysobjects where Name = 'Authors')" myConnection.Open() myReader = myCommand.ExecuteReader() ColumnSort.Items.Clear() ColumnSort.Items.Add("None") While (myReader.Read()) ColumnSort.Items.Add(myReader("name").ToString()) End While myConnection.Close() myReader = Nothing ' Add the Sort order. OrderSort.Items.Add("Asc") OrderSort.Items.Add("Desc") End If End Sub Sub ApplyFilter(Sender as Object, E as EventArgs) Dim myConnection as new SqlConnection("server=localhost;_ uid=sa;pwd=;database=pubs") Dim myDataAdapter as new SQLDataAdapter("select * from Authors", _ myConnection) Dim myDataSet as DataSet myDataSet = new DataSet() myDataAdapter.Fill(myDataSet,"Authors") Dim myDataView as DataView myDataView = new DataView(myDataSet.Tables("Authors")) If(ColumnSort.SelectedItem.ToString() <> "None") Then myDataView.Sort = ColumnSort.SelectedItem.ToString() + " " + _ OrderSort.SelectedItem.ToString() End If If(Filter.Text <> "") Then myDataView.RowFilter = Filter.Text End If Dim I as integer Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") for I = 0 to myDataView.Count - 1 Response.Write("<tr><td>") Response.Write(myDataView(I)("au_id").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_fname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("au_lname").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("address").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("city").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("state").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("zip").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("phone").ToString) Response.Write("</td><td>") Response.Write(myDataView(I)("contract").ToString) Response.Write("</td></tr>") Next Response.Write("</table>") End Sub </script> <body> <form id="Form1" runat="server"> Filter: <asp:textbox width="300" id="Filter" runat="Server"></asp:textbox> Sort By: <asp:dropdownlist id="ColumnSort" runat="Server"></asp:dropdownlist> <asp:dropdownlist id="OrderSort" runat="Server"></asp:dropdownlist> <asp:button id="button1" text="Filter" runat="Server" onclick="ApplyFilter"></asp:button> </form> </body> </html> Listing 9.28 Filtering with a DataView C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="C#" runat="server" ID="script1"> void Page_Load() { if(!IsPostBack){ SqlDataReader myReader; SqlConnection myConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs"); SqlCommand myCommand = new SqlCommand("select * from Authors",_ myConnection); myConnection.Open(); myReader = myCommand.ExecuteReader(); Response.Write("<table border=1>"); Response.Write("<th>au_id</th><th>au_fname</th>" + "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + "<th>zip</th><th>phone</th><th>contract</th>"); while(myReader.Read()){ Response.Write("<tr><td>"); Response.Write(myReader["au_id"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["au_fname"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["au_lname"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["address"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["city"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["state"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["zip"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["phone"].ToString()); Response.Write("</td><td>"); Response.Write(myReader["contract"].ToString()); Response.Write("</td></tr>"); } Response.Write("</table>"); myConnection.Close(); myReader = null; // Get the column names for the dropdownlist. myCommand.CommandText = "select name from syscolumns " + "where id = (select id from sysobjects where Name = 'Authors')"; myConnection.Open(); myReader = myCommand.ExecuteReader(); ColumnSort.Items.Clear(); ColumnSort.Items.Add("None"); while (myReader.Read()) ColumnSort.Items.Add(myReader["name"].ToString()); myConnection.Close(); myReader = null; // Add the Sort order. OrderSort.Items.Add("Asc"); OrderSort.Items.Add("Desc"); } } void ApplyFilter(Object sender,EventArgs e) { SqlConnection myConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs"); SqlDataAdapter myDataAdapter = new SqlDataAdapter("select * from Authors", myConnection); DataSet myDataSet = new DataSet(); myDataAdapter.Fill(myDataSet,"Authors"); DataView myDataView = new DataView(myDataSet.Tables["Authors"]); if(ColumnSort.SelectedItem.ToString() != "None") myDataView.Sort = ColumnSort.SelectedItem.ToString() + " " + OrderSort.SelectedItem.ToString(); if(Filter.Text != "") myDataView.RowFilter = Filter.Text; Response.Write("<table border=1>"); Response.Write("<th>au_id</th><th>au_fname</th>" + "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + "<th>zip</th><th>phone</th><th>contract</th>"); for(int i=0;i<myDataView.Count - 1;i++){ Response.Write("<tr><td>"); Response.Write(myDataView[i]["au_id"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["au_fname"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["au_lname"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["address"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["city"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["state"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["zip"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["phone"].ToString()); Response.Write("</td><td>"); Response.Write(myDataView[i]["contract"].ToString()); Response.Write("</td></tr>"); } Response.Write("</table>"); } </script> <body> <form id="Form1" runat="server"> Filter: <asp:textbox width="300" id="Filter" runat="Server"></asp:textbox> Sort By: <asp:dropdownlist id="ColumnSort" runat="Server"></asp:dropdownlist> <asp:dropdownlist id="OrderSort" runat="Server"></asp:dropdownlist> <asp:button id="button1" text="Filter" runat="Server" onclick="ApplyFilter"> </asp:button> </form> </body> </html> As you can see from the output of the example, the Authors table is loaded and displayed with a filter text box and two drop-down boxes. The first drop-down box contains a list of all the columns in the table, and the second defines the sort order. The list of columns was obtained by referencing the SQL system table syscolumns. An arbitrary filter of "State='CA' AND City='Oakland'" was applied to the table, resulting in a view of only the rows matching the filter criteria. The au_lname column was used as the sort column in ascending order. |