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 beginning for the form. The final control names variables look like listing 1.
Listing 1: Variable of figure 1
Private datagrid1 As System.Windows.Forms.DataGrid
Private lable1 As System.Windows.Forms.Label
Private lable2 As System.Windows.Forms.Label
Private textbox1 As System.Windows.Forms.TextBox
Private textBox2 As System.Windows.Forms.TextBox
Private lable3 As System.Windows.Forms.Label
Private textBox3 As System.Windows.Forms.TextBox
Private DeleteRow As System.Windows.Forms.Button
Private AddRow As System.Windows.Forms.Button
Private custTable As System.Data.DataTable
Private groupBox1 As System.Windows.Forms.GroupBox
Private groupBox2 As System.Windows.Forms.GroupBox
Private groupBox3 As System.Windows.Forms.GroupBox
Private lable4 As System.Windows.Forms.Label
Private SearchButton As System.Windows.Forms.Button
Private SearchBox As System.Windows.Forms.TextBox
Private dtSet As System.Data.DataSet
Now you create the Customers table with three columns: id, Name and Address. I've already discussed how 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
Private Sub CreateCustomersTable()
' create a new DataTable.
custTable = New DataTable("Customers")
Dim dtColumn As DataColumn
' 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.
Dim PrimaryKeyColumns As DataColumn() = New DataColumn(0) {}
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()
End Sub
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 Sub RefreshData()
dataGrid1.DataSource = dtSet.DefaultViewManager
End Sub
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. At the end, you call the RefreshData method to fill the DataGrid with the records.
Listing 2: The Add Row_Click method
Private Sub AddRow_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Add rows to the cust table using its NewRow method
' I add three customers with their addresses, name and id
Dim myDataRow As DataRow = custTable.NewRow()
myDataRow("Name") = textBox1.Text.ToString()
myDataRow("Address") = textBox2.Text.ToString()
custTable.Rows.Add(myDataRow)
custTable.AcceptChanges()
RefreshData()
End Sub
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 Sub DeleteRow_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim idx As Integer = Convert.ToInt32(textBox3.Text.ToString())
Dim row As DataRow = custTable.Rows(idx - 1)
row.Delete()
row.AcceptChanges()
End Sub
To test this code, you delete the second row by entering 2 in the Remove Row # text box and click Delete Row button. The result looks 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 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 Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim str As String = "Name < >'" & textBox4.Text & " ' "
Dim rows As DataRow() = custTable.[Select](str)
' if no record found
If rows.Length = 0 Then
MessageBox.Show("No records found!")
Exit Sub
End If
For i As Integer = 0 To rows.Length - 1
rows(i).Delete()
rows(i).AcceptChanges()
Next
RefreshData()
End Sub
Now using the search button and text boxes, you 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.