The DataTable: Filtering, Adding, Editing, Sorting, and Deleting DataRows

DataTable: Filtering, Adding, Editing, Sorting, and Deleting DataRows "-->

only for RuBoard

The `DataTable` : Filtering, Adding, Editing, Sorting, and Deleting `DataRows`

Because we believe it is important to develop a good fundamental understanding of new technology, we thought we should start this chapter with code illustrating how to create a DataTable and DataSet from "scratch." We are going to dynamically build the DataSet , DataTable , DataColumn , and DataRow objects and then bind to a DataGrid (Listing 11.1).

Listing 11.1 Constructing a `DataTable`

 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: 03: <script language="vb" runat="server"> 04: 05:  sub Page_Load(sender as Object, e as EventArgs) 06: 07:   dim DS as new DataSet() 08:   dim DC as DataColumn 09:   dim DR as DataRow 10: 11:   dim DT as new DataTable("Counters") 12: 13:   dim i as integer 14:   for i = 0 to 4 15: 16:    dc = new DataColumn("Column " & i, System.Type.GetType("System.String")) 17:    DT.Columns.Add(DC) 18: 19:   next 20: 21:   DS.Tables.Add(DT) 22: 23:   for i = 0 to  9 24: 25:    DR = DT.NewRow() 26:    DR(0) = "Row " & i 27:    DR(1) = "Row " & i 28:    DR(2) = "Row " & i 29:    DR(3) = "Row " & i 30:    DR(4) = "Row " & i 31: 32:    DT.Rows.Add(DR) 33:  next 34: 35:  DG.DataSource = DS.Tables("Counters") 36:  DG.DataBind() 37: 38:  end sub 39: 40: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: 03: <script language="c#" runat="server"> 04: 05:  void Page_Load(Object sender, EventArgs e){ 06: 07:   DataSet DS = new DataSet(); 08:   DataColumn DC; 09:   DataRow DR; 10: 11:   DataTable DT = new DataTable("Counters"); 12: 13:   int i; 14:   for (i = 0; i < 5; i++) { 15: 16:    DC = new DataColumn("Column " + i, System.Type.GetType( "System.String")); 17:    DT.Columns.Add(DC); 18: 19:   } 20: 21:   DS.Tables.Add(DT); 22: 23:   for (i = 0; i < 10; i ++) { 24: 25:    DR = DT.NewRow(); 26:    DR[0] = "Row " + i; 27:    DR[1] = "Row " + i; 28:    DR[2] = "Row " + i; 29:    DR[3] = "Row " + i; 30:    DR[4] = "Row " + i; 31: 32:    DT.Rows.Add(DR); 33:  } 34: 35:  DG.DataSource = DS.Tables["Counters"]; 36:  DG.DataBind(); 37: 38:  } 39: 40: </script> [VisualBasic.NET & C#.NET] 41: <html> 42:  <body> 43:   <asp:DataGrid 44:    font-size="10" 45:    runat="server" 46:    id="DG" 47:   /> 48:  </body> 49: </html>

The steps for constructing the DataTable in this example (Listing 11.1) are as follows :

Created a DataTable object ”line 11, a DataTable named "Counters" is created.
Created and added 5 DataColumns to the DataTable . On lines 13 “19, a loop is performed creating a new DataColumn object (line 16) and then adding that DataColumn to the DataTable (line 17) by calling the DataColumnsCollection.Add method. In this example, I only specify two attributes for the DataColumn using the DataColumn constructor ” ColumnName and DataType .
Add the DataTable to the DataSet ”This is done by calling the DataTableCollection.Add method ”(line 21).
Add 10 DataRows to the DataTable ”lines 23 “33, this is accomplished by first calling the DataTable.NewRow method. The NewRow method returns a DataRow object with the schema of the DataTable . After you have a DataRow to work with, give each field in the row values and call the DataRowCollection.Add method, which adds the row to the DataTable .

You can see an illustration of the page from Listing 11.1 in Figure 11.1.

Figure 11.1. A `DataGrid` control bound to a constructed `DataTable` with five columns and 10 rows.

As you can see, constructing a DataTable isn't too difficult after you understand what it is and what it's made of. All you do is construct each piece and put them together somewhat like a puzzle. When you retrieve data from your database and put it into a DataSet , the creation of the DataTable is done automatically behind the scenes, but you might run into a situation where you must make your own.

Filtering Rows in a `DataTable`

The DataTable.Select method enables you to filter out specific rows from a DataTable into an array of DataRow objects. For example, say you're using a DropDownList to show a list of animals in a zoo and you want to let the user see all animals whose names start with the letter A , but the DataTable you're working with has all the animals in the zoo in it. You can use the DataTable.Select method and filter out all the rows where the animal's name starts with A and show only those to the user instead of creating an additional call to the database to get those specific records.

There are four overloads to the DataTable.Select method and three different parameters. The following list describes each.

DataTable.Select() This is the only parameterless overload. This will return all the DataRow objects from the DataTable ordered by the Primary Key. If a Primary Key doesn't exsist, then the array is created by the order in which the DataRow objects are in the DataRowCollection .
DataTable.Select( FilterExpression) The FilterExpression is a string value in which you'll supply a valid Expression which is used to filter by Primary Key. (Ex: DataTable.Select("AnimalNameColumn LIKE 'A%'") )
DataTable.Select( FilterExpression, Sort ) A new parameter is added here, the Sort parameter. The Sort parameter is also a string value indicating the sort for the DataRow objects. For instance, after you have filtered out all the animals whose names start with A, you can order them by what type of animal they are, Mammal or Reptile. (Ex: DataTable.Select("AnimalNameColumn LIKE 'A%'", "TypeOfAnimalColumn DESC " )
DataTable.Select( FilterExpression, Sort, DataViewRowState) The third parameter deals with the DataRows state. We will be going over this later in this section, but essentially this is the state of the DataRow at the time of the filter. Some of the values can be Added, Deleted, or Modified. (Ex: DataTable.Select("AnimalNameColumn LIKE 'A%'", "TypeOfAnimalColumn DESC " , DataViewRowState.Added)

In Listing 11.2, the DataTable.Select method is used to filter out all rows from a DataTable , populated with the Products table from Northwind , where the ProductName columns value begins with the letter C .

Listing 11.2 Using the `Select` Method

 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="vb" runat="server"> 05: 06:  protected sub Page_Load(sender as Object, e as EventArgs) 07: 08:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 09:   dim SqlDA as new SqlDataAdapter("SELECT * FROM Products", SqlCon) 10:   dim ds as new DataSet() 11: 12:   SqlDA.Fill(ds, "Products") 13: 14:   dim DR() as DataRow = ds.Tables("Products").Select( "ProductName LIKE 'C%'") 15: 16:   dim i as integer 17:   for i = 0 to DR.Length - 1 18: 19:    me.DDL.Items.Add(new ListItem(DR(i)("ProductName").ToString())) 20: 21:   next 22: 23:  end sub 24: 25: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="C#" runat="server"> 05: 06:  protected void Page_Load(Object sender, EventArgs e){ 07: 08:   SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 09:   SqlDataAdapter SqlDA = new SqlDataAdapter( "SELECT * FROM Products", SqlCon); 10:   DataSet ds = new DataSet(); 11: 12:   SqlDA.Fill(ds, "Products"); 13: 14:   DataRow[] DR = ds.Tables["Products"].Select("ProductName LIKE 'C%'"); 15: 16:   int i; 17:   for (i = 0; i < DR.Length; i ++){ 18: 19:    this.DDL.Items.Add(new ListItem(DR[i]["ProductName"].ToString())); 20: 21:   } 22: 23:  } 24: 25: </script> [VisualBasic.NET & C#.NET] 26: <html> 27:  <body> 28: 29:   <asp:DropDownList id="DDL" runat="Server" font-size="10" /> 30: 31:  </body> 32: </html>

The DataTable.Select method in Listing 11.2 can be found on line 14. After calling the DataTable.Select method, we loop through the returned array of DataRow objects on lines 17 “21, dynamically building a ListItems and adding them to the DropDownList control. You can see this page in Figure 11.2.

Figure 11.2. A `DropDownList` control containing all product names that start with the letter "C".

Note

You don't have to set all the parameters when using the DataTable.Select method. For example, if you want to change the sort order for the DataTable you can specify nothing (VB) or null (C#) for the FilterExpression parameter (ex: DataTable.Select(null, "ProductName DESC") .

As previously mentioned, the fourth overload for the DataTable.Select method expects a DataViewRowState enumeration value. The following list contains a description of each:

CurrentRows ” The current rows
Deleted ” All deleted rows
ModifiedCurrent ” The current version of modified data
ModifiedOriginal ” The original version of modified data
Added ” A new row
None ” None
OriginalRows ” The original rows
Unchanged ” Unchanged rows

Adding New Rows to a `DataTable`

Just like you can add new rows to a table in a database, you can add new rows to a DataTable . When adding a new row to a DataTable , you must do three things. First, create a new DataRow object with the same schema as the DataTable you want to add it to. Second, apply values to each field in the DataRow . Third, add the new DataRow to the DataTable's DataRowCollection . The DataTable.NewRow method must be used to create a new DataRow object and as previously mentioned, the DataTable.NewRow method returns a DataRow object with the exact schema of the DataTable . Listing 11.3 is an example illustrating how to add a new row to a DataTable .

Listing 11.3 Demonstrating the `DataTable` 's `NewRow` Method

 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="vb" runat="server"> 05: 06:  protected sub Page_Load(sender as Object, e as EventArgs) 07: 08:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 09:   dim SqlDA as new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID FROM  Products", SqlCon) 10:   dim ds as new DataSet() 11: 12:   SqlDA.Fill(ds, "Products") 13: 14:   dim dt as DataTable = ds.Tables(0) 15: 16:   dim NewDataRow as DataRow = dt.NewRow() 17: 18:   NewDataRow("ProductName") = "DotNetJunkies .NET Power Bar" 19:   NewDataRow("ProductID") = "4999" 20: 21:   dt.Rows.Add(NewDataRow) 22:   'dt.Rows.InsertAt(NewDataRow, 0) 23: 24:   DG.DataSource = dt 25:   DG.DataBind() 26: 27:  end sub 28: 29: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="C#" runat="server"> 05: 06:  protected void Page_Load(Object sender, EventArgs e){ 07: 08:   SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 09:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID  FROM Products", SqlCon); 10:   DataSet ds = new DataSet(); 11: 12:   SqlDA.Fill(ds, "Products"); 13: 14:   DataTable dt = ds.Tables[0]; 15: 16:   DataRow NewDataRow = dt.NewRow(); 17: 18:   NewDataRow["ProductName"] = "DotNetJunkies .NET Power Bar"; 19:   NewDataRow["ProductID"] = "4999"; 20: 21:   dt.Rows.Add(NewDataRow); 22:   //dt.Rows.InsertAt(NewDataRow, 0); 23: 24:   DG.DataSource = dt; 25:   DG.DataBind(); 26: 27:  } 28: 29: </script> [VisualBasic.NET & C#.NET] 30: <html> 31:  <body> 32: 33:   <asp:DataGrid id="DG" runat="Server" font-size="10" /> 34: 35:  </body> 36: </html>

The DataTable.NewRow method is invoked on line 16 of Listing 11.3. Values are given to each field of the new DataRow ( NewDataRow ) on lines 18 “19. Finally, the new DataRow object is added to the DataTables DataRowCollection on line 21 by calling the DataRowCollection.Add method. You will notice that there is a commented out piece of code on line 22, dt.Rows.InsertAt(NewDataRow, 0); this is the DataRowCollection.InsertAt method and it can be used to insert a new DataRow to a DataRowCollection at a specified location within the DataRowCollection index. This code will add the DataRow object to the 0 index of the DataRowCollection . After running the code that utilizes the Add method comment out line 21 and run the code that uses the InsertAt method (line 22) to see the effect.

Figure 11.3 is this page after using the DataRowCollection.InsertAt method to add a new DataRow to the DataTable .

Figure 11.3. `DataGrid` bound to `DataTable` with a new `DataRow` added to its `DataRowCollection` .

Notice in Figure 11.3 that the new DataRow is located at the very top of the DataGrid . This is because by using the InsertAt method, I was able to insert the DataRow at 0 index of the DataRowCollection .

Editing Rows in a `DataTable`

You are going to run into situations where you will enable users to edit one or more rows in your web applications. For instance, you might have a web application that allows users to make changes to data, but you don't want to save the changes back to the database until the user is completely finished with the edits; hence the edited data will be persisted in the DataTable .

There are a few steps you must do to edit a DataRow :

Determine which DataRow you want to edit with the DataRowCollection .
Put the DataRow into edit mode by invoking the DataRow.BeginEdit method.
Edit the DataRow column values.
Invoke the DataRow.EndEdit method.

The following list contains a description of the preceding methods :

DataRow.BeginEdit ” This method puts a DataRow into edit mode.
DataRow.EndEdit ” This method takes the DataRow out of edit mode. (Note: when you invoke the DataTable.AcceptChanges , the EndEdit method is called implicitly.)

Another important method:

DataRow.CancelEdit ” This method cancels the current edit. This method cannot be called after the EndEdit method to cancel changes.

Listing 11.4 contains an example on how to edit a DataRow. In this example, I determine which DataRow I want to edit by using the DataTable.Select method to return a DataRow .

Listing 11.4 Editing a `DataRow`

 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="vb" runat="server"> 05: 06:  protected sub Page_Load(sender as Object, e as EventArgs) 07: 08:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 09:   dim SqlDA as new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID FROM  Products", SqlCon) 10:   dim ds as new DataSet() 11: 12:   SqlDA.Fill(ds, "Products") 13: 14:   dim dt as DataTable = ds.Tables(0) 15: 16:   dim dr() as DataRow = dt.Select("ProductID = '17'") 17: 18:   dr(0).BeginEdit() 19:   dr(0)("ProductName") = "New and Improved Alice Mutton" 20:   dr(0).EndEdit() 21: 22:   DG.DataSource = dt 23:   DG.DataBind() 24: 25:  end sub 26: 27: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="C#" runat="server"> 05: 06:  protected void Page_Load(Object sender, EventArgs e){ 07: 08:   SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 09:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID  FROM Products", SqlCon); 10:   DataSet ds = new DataSet(); 11: 12:   SqlDA.Fill(ds, "Products"); 13: 14:   DataTable dt = ds.Tables[0]; 15: 16:   DataRow[] dr = dt.Select("ProductID = '17'"); 17: 18:   dr[0].BeginEdit(); 19:   dr[0]["ProductName"] = "New and Improved Alice Mutton"; 20:   dr[0].EndEdit(); 21: 22:   DG.DataSource = dt; 23:   DG.DataBind(); 24: 25:  } 26: 27: </script> [VisualBasic.NET & C#.NET] 28: <html> 29:  <body> 30: 31:   <asp:DataGrid id="DG" runat="Server" font-size="10" /> 32: 33:  </body> 34: </html>

I found the DataRow I wanted to edit on line 16 using the DataTable.Select method to search the DataRowCollection for a DataRow with a ProductID value of 17 . In this example, I knew only one row would be returned, so I immediately put the DataRow into edit mode. (If multiple rows could have been returned, I would have had to loop through the returned DataRow array.)

To begin editing, I invoked the DataRow.BeginEdit method (line 18). After calling the BeginEdit method, you can change some or all the columns values by simply supplying new values (line 19) to the columns. After editing is finished, the DataRow.EndEdit method is invoked and the DataGrid is bound to the DataTable (lines 20 “23).

Note

You are not required to call the EndEdit method or AcceptChanges method when editing a DataRow , but it prevents data corruption by taking the DataRow out of edit mode.

The rendered page from Listing 11.4 can be seen in Figure 11.4. The first product is now named "New and Improved Alice Mutton" instead of "Alice Mutton."

Figure 11.4. The first row of the `DataGrid` is "New and Improved Alice Mutton" instead of "Alice Mutton," as it is in the database.

Sorting Rows in a `DataTable`

Sorting can be achieved in two ways when using a DataTable . You can sort data with your SQL statement before it is put into a DataTable . Or you can sort by the DataTable.Select method's second parameter like this:

 dr = dt.Select(Nothing, "ProductName DESC")

Deleting Rows in a `DataTable`

Deleting a row from a DataTable is a very simple task. Just determine which row you would like to delete and call the DataRow.Delete method, as shown in Listing 11.5.

Listing 11.5 Deleting a `DataRow` Using the `DataRow.Delete` Method

 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="vb" runat="server"> 05: 06:  protected sub Page_Load(sender as Object, e as EventArgs) 07: 08:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 09:   dim SqlDA as new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID FROM  Products", SqlCon) 10:   dim ds as new DataSet() 11: 12:   SqlDA.Fill(ds, "Products") 13: 14:   dim dt as DataTable = ds.Tables(0) 15: 16:   dim dr() as DataRow = dt.Select("ProductID = '17'") 17: 18:   dr(0).Delete() 19:   dt.AcceptChanges() 20: 21:   DG.DataSource = dt 22:   DG.DataBind() 23: 24:  end sub 25: 26: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="C#" runat="server"> 05: 06:  protected void Page_Load(Object sender, EventArgs e){ 07: 08:   SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 09:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT TOP 10 ProductName, ProductID  FROM Products", SqlCon); 10:   DataSet ds = new DataSet(); 11: 12:   SqlDA.Fill(ds, "Products"); 13: 14:   DataTable dt = ds.Tables[0]; 15: 16:   DataRow[] dr = dt.Select("ProductID = '17'"); 17: 18:   dr[0].Delete(); 19:   dt.AcceptChanges(); 20: 21:   DG.DataSource = dt; 22:   DG.DataBind(); 23: 24:  } 25: 26: </script> [VisualBasic.NET & C#.NET] 27: <html> 28:  <body> 29: 30:   <asp:DataGrid id="DG" runat="Server" font-size="10" /> 31: 32:  </body> 33: </html>

The DataRow.Delete method is invoked on line 18. This particular DataRow was retrieved via the DataTable.Select method. On line 19, I invoke the DataTable.AcceptChanges method to commit the DataRow deletion and finally bind the DataGrid to the DataTable . Figure 11.5 is an illustration of this page after the deletion.

Figure 11.5. The first `DataRow` of the `DataTable` has been deleted (Alice Mutton).

Note

You could delete all DataRows in a DataTable by invoking the DataTable.Clear method as seen in the following code: MyDataTable.Clear() .

Caution

If the DataTable has a relationship to one or more other DataTables , an exception will occur if you call the DataTable.Clear method.

only for RuBoard