ADO.NET DataTableOperation in VB.NET

In this article I will explain about DataTableOperation in ADO.NET.
  • 2088

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.

datatable1.JPG

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.

datatable2.JPG

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.

datatable3.JPG

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.

datatable4.JPG

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.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.