DataTableOperation in ADO.NET

This article has been excerpted from the book "A Programmer's Guide to ADO.NET in C#".

 

Adding and deleting data are two common operations when working with databases. You've already learned in my previous article about how to add data to a data table using DataRow. In this article, you'll see how to add, delete, and search data programmatically. To test the sample application, you'll not store data in a database. All data will be stored in memory in the form of DataTable objects.

 

The first step is to build a Graphical User Interface (GUI). To build this GUI, you'll create a Windows application project. After that add a DataGrid control, three-button controls, four textbox controls, three group boxes, and a couple, of label controls. Then adjust them on your form. You can also change the background color of the controls, as shown in figure 1 below. As you can see from the form, the add button will add the name and address that you enter in the Name and Address textboxes. The Remove button will remove the row number you'll enter in the Enter Row# textbox. The search button searches for a name in the DataTable and returns the corresponding records in the DataGrid.

 

Note: You don't have to create the same form. The only thing you need to have a DataGrid with the same number for text boxes and button controls.

 

 

Figure 1: Add, delete, and search operations in DataTable

 

Now you change the names of the form controls and add the DataSet variable dtSet and the DataTable variable custTable to the beginning of the form. The final control name variables look like listing 1.

 

Listing 1: Variable of figure 1

private System.Windows.Forms.DataGrid datagrid1;
private System.Windows.Forms.Lable lable1;
private System.Windows.Forms.Lable lable2;
private System.Windows.Forms.TextBox textbox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.Label lable3;
private System.Windows.Forms.TextBox textBox3;
private System.Windows.Forms.Button DeleteRow;
private System.Windows.Forms.Button AddRow;
private System.Data.DataTable custTable;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.GroupBox groupBox3;
private System.Windows.Forms.Lable lable4;
private System.Windows.Forms.Button SearchButton;
private System.Windows.Forms.TextBox SearchBox;
private System.Data.DataSet dtSet;

Now you create the Customers table with three columns: id, Name, and Address. I've already discussed how to add columns to a DataTable using DataColumn and bind it to a DataGrid in the previous articles. The CreateCustomersTable method creates the Customers table. After creating the Customers table you add the DataTable to the DataSet using the DataSet.Tables. Add method. The CreateCustomersTable method looks like listing 2.

 

Listing 2: The CreateCustomersTable method

// This method creates customers Table
private void CreateCustomersTable() {
      // create a new DataTable.
      custTable = new DataTable("Customers");
      DataColumn dtColumn;

      // createe id column
      dtColumn = new DataColumn();
      dtColumn.DataType = System.Type.GetType("System.Int32");
      dtColumn.ColumnName = "id";
      dtColumn.AutoIncrement = true;
      dtColumn.AutoIncrementSeed = 100;
      dtColumn.AutoIncrementStep = 1;
      dtColumn.Caption = "Cust ID";
      dtColumn.ReadOnly = true;
      dtColumn.Unique = true;
      // Add id column to the DataColumnCollection.
      custTable.Columns.Add(dtColumn);

      // Create Name column.
      dtColumn = new DataColumn();
      dtColumn.DataType = System.Type.GetType("System.String");
      dtColumn.ColumnName = "Name";
      dtColumn.Caption = "cust Name";
      dtColumn.AutoIncrement = false;
      dtColumn.ReadOnly = false;
      dtColumn.Unique = false;

      // Add Name column to the Table.
      custTable.Columns.Add(dtColumn);

      // Create Address Column.
      dtColumn = new DataColumn();
      dtColumn.DataType = System.Type.GetType("System.String");
      dtColumn.ColumnName = "Address";
      dtColumn.Caption = " Address";
      dtColumn.ReadOnly = false;
      dtColumn.Unique = false;

      // Add Address column to the table.
      custTable.Columns.Add(dtColumn);

      // Make the Id column the primary key column.
      DataColumn[] PrimaryKeyColumns = new DataColumn[1];
      PrimaryKeyColumns[0] = custTable.Columns["id"];
      custTable.PrimaryKey = PrimaryKeyColumns;

      // Instantiate the DataSet variable.
      dtSet = new DataSet("Customers");

      // Add the custTable to the DataSet.
      dtSet.Tables.Add(custTable);

      RefreshData();
}

At the end of the CreateCustomersTable method, you call the RefreshData method, which refreshes the DataGrid contents and fills them with the current data of the DataTable by setting Data Grid's DataSource property to DatSet's DefaultViewManager. The RefreshData method looks like the following:

private void RefreshData() {
      dataGrid1.DataSource = dtSet.DefaultViewManager;
}

As you can see from figure 1, the Add Row button adds a new row to the Customers DataTable with the Name and Address columns reading from Name and address text boxes. The Delete Row button deletes the row member inserted in the Enter Row # text box. The search button searches and returns rows that contain the name entered in the Enter Name text box of the searches group box.

 

OK, now it's time to write code for the button event handlers. You can write button event handlers by double-clicking on the buttons or using the Properties windows. First, you write the event handler for the Add Row button with the handler name AddRow_click for this button. After that write event handlers for the Remove and search buttons; the event handler names for these buttons are DeleteRow_Click and SearchButton_Click respectively.

 

Now you can write code on these handlers. First, you're writing code for the Add Row button. Actually, there is nothing new about the code written for the Add Row button handler. You add a new row to the DataTable using DataRow and call the Add and AcceptChanges methods of the Data Table. The following code snippet listed in listing 2 shows the Add Row button click event handler.

 

As you can see from the code, I added Name as TextBox1's text and Address as TextBox2's text.

 

You call NewRow of DataTable to add a new row to DataTable, set its field values, and call the DataTable.Rows.Add method to add it. In the end, you call the RefreshData method to fill the DataGrid with the records.

 

Listing 2: The Add Row_Click method

private void AddRow_Click(object sender, System.EventArgs e) {
    // Add rows to the cust table using its NewRow method
    // I add three customers with their addresses, name and id
    DataRow myDataRow = custTable.NewRow();
    myDataRow["Name"] = textBox1.Text.ToString();
    myDataRow["Address"] = textBox2.Text.ToString();
    custTable.Rows.Add(myDataRow);
    custTable.AcceptChanges();
    RefreshData();
}

If you add six rows to the DataTable using the Add Row button the result looks like figure 2.

 

 

Figure 2: Adding rows to the DataTable

 

The Delete Row button deletes the row number you entered in the text box. You can delete a row from a DataTable by calling DataRow's Delete method. On the Delete Row button event handler, you call DataTable's Delete method. Before calling Delete, you need to know what row you want to delete from a DataTable. You get that from TextBox3 and return that row using DataTable.Rows (index). Once you have a DataRow, you call its Delete method and AcceptChanges method to make final changes to the DataTable. In listing 3, you first call the Delete method of DataRow and call AcceptChanges to accept the changes.

 

Listing 3: The Delete Row_Click method

// Deletes a row from the datatable
private void DeleteRow_Click(object sender, System.EventArgs e) {
    int idx = Convert.ToInt32(textBox3.Text.ToString());
    DataRow row = custTable.Rows[idx - 1];
    row.Delete();
    row.AcceptChanges();
}

To test this code, you delete the second row by entering 2 in the Remove Row # text box and click the Delete Row button. The result looks at figure 3.

 

 

Figure 3: Deleting rows from the DataTable

 

The DataTable class also provides the Select method to select certain rows in a DataTable based on a filter. A filter is a conditional statement. All SQL conditional operators are valid in the Filter string. For Example, to filter rows where id is greater than 22, the filter string will be "ID>22"; for selecting records of the name Row, the filter string will be "Name = 'Rox'". The SearchButton_Click method searches for the criteria and deletes all the rows from the DataTable that don't match the criteria to display only that match the criteria. Listing 4 shows the SearchButton_Click method.

 

Listing 4: The SearchButton_Click method

// search button searches for the criteria
private void SearchButton_Click(object sender, System.EventArgs e) {
    string str = "Name < >'" + textBox4.Text + " ' ";
    DataRow[] rows = custTable.Select(str);
    // if no record found
    if (rows.Length == 0) {
        MessageBox.Show("No records found!");
        return;
    }

    for (int i = 0; i < rows.Length; i++) {
        rows[i].Delete();
        rows[i].AcceptChanges();
    }

    RefreshData();
}

Now using the search button and text boxes, your search for records with the name Amy by entering "Amy" in the Search text box and clicking the search button. The result looks like figure 4.

 

 

Figure 4: Result of clicking the search after entering "AMY"

 

Conclusion

 

Hope this article would have helped you in understanding the DataTableOperation in ADO.NET. See my other articles on the website on ADO.NET.