Lesson 4: Inserting, updating, and deleting data

In this lesson you add code to your application to modify the data in your database. The following procedures use Dynamic SQL. The same techniques can be performed using the Table API.

The following procedure creates a supporting method to maintain the listbox. This method is required for data manipulation methods created in the remaining procedures.

Add code to maintain the listbox

Right-click the form and click View Code.

Add a method of the Form1 class to update and populate the listbox. This method carries out the following tasks:

Clears the listbox.
Instantiates a ULCommand object and assigns it a SELECT query that returns data from the Names table in the database.
Executes the query, returning a result set as a ULDataReader.
Instantiates an integer array with length equal to the number of rows in the result set.
Populates the listbox with the names returned in the ULDataReader and populates the integer array with the ids returned in the ULDataReader.
Closes the ULDataReader.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code.

For C#, add the following code to your application as a method of the Form1 class.

//Visual C#
private void RefreshListBox(){
    try{
        long NumRows;
        int I = 0;
        lbNames.Items.Clear();
        using( ULCommand cmd = Conn.CreateCommand() ){
            cmd.CommandText = "SELECT ID, Name FROM Names";
            using( ULDataReader dr = cmd.ExecuteReader()){
                dr.MoveBeforeFirst();
                NumRows = dr.RowCount;
                ids = new int[ NumRows ];
                while (dr.MoveNext())
                {
                    lbNames.Items.Add(
                    dr.GetString(1));
                    ids[ I ] = dr.GetInt32(0);
                    I++;
                }
            }
            txtName.Text = " ";   
        }
    }
    catch( Exception err ){
        MessageBox.Show(
        "Exception in RefreshListBox: " + err.Message );
    }
}

For Visual Basic, add the following code to your application as a method of the Form1 class.

'Visual Basic
Private Sub RefreshListBox()
    Try
        Dim cmd As ULCommand = Conn.CreateCommand()
        Dim I As Integer = 0
        lbNames.Items.Clear()
        cmd.CommandText = "SELECT ID, Name FROM Names"
        Dim dr As ULDataReader = cmd.ExecuteReader()
        ReDim ids(dr.RowCount)
        While (dr.MoveNext)
            lbNames.Items.Add(dr.GetString(1))
            ids(I) = dr.GetInt32(0)
            I = I + 1
        End While
        dr.Close()
        txtName.Text = " "   
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Build the project.

Building the project should result in no errors.

Implement INSERT, UPDATE, and DELETE

On the form design tab, double-click Insert to create a btnInsert_Click method. This method carries out the following tasks:

Instantiates a ULCommand object and assigns it an INSERT statement that inserts the value in the text box into the database.
Executes the statement.
Disposes of the ULCommand object.
Refreshes the listbox.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code.

For C#, add the following code to the btnInsert_Click method.

//Visual C#
try {
    long RowsInserted;
    using( ULCommand cmd = Conn.CreateCommand() ) {
        cmd.CommandText = 
            "INSERT INTO Names(name) VALUES (?)";
        cmd.Parameters.Add("", txtName.Text);
        RowsInserted = cmd.ExecuteNonQuery();
    }
    RefreshListBox();
} 
catch( Exception err ) {
    MessageBox.Show("Exception: " + err.Message );
}

For Visual Basic, add the following code to the btnInsert_Click method.

'Visual Basic
Try
    Dim RowsInserted As Long
    Dim cmd As ULCommand = Conn.CreateCommand()
    cmd.CommandText = "INSERT INTO Names(name) VALUES (?)"
    cmd.Parameters.Add("", txtName.Text)
    RowsInserted = cmd.ExecuteNonQuery()
    cmd.Dispose()
    RefreshListBox()
Catch
    MsgBox("Exception: " + Err.Description)
End Try

On the form design tab, double-click Update to create a btnUpdate_Click method. This method carries out the following tasks:

Instantiates a ULCommand object and assigns it an UPDATE statement that inserts the value in the text box into the database based on the associated ID.
Executes the statement.
Disposes of the ULCommand object.
Refreshes the listbox.
If an error occurs, prints the error message. For SQL errors, the code also prints the error code.

For C#, add the following code to the btnUpdate_Click method.

//Visual C#
try {
    long RowsUpdated;
    int updateID = ids[ lbNames.SelectedIndex ];
    using( ULCommand cmd = Conn.CreateCommand() ){
        cmd.CommandText = 
            "UPDATE Names SET name = ? WHERE id = ?" ;
        cmd.Parameters.Add("", txtName.Text );
        cmd.Parameters.Add("", updateID);
        RowsUpdated = cmd.ExecuteNonQuery();
    }
    RefreshListBox();
}
catch( Exception err ) {
    MessageBox.Show(
        "Exception: " + err.Message);
}

For Visual Basic, add the following code to the btnUpdate_Click method.

'Visual Basic
Try
    Dim RowsUpdated As Long
    Dim updateID As Integer = ids(lbNames.SelectedIndex)
    Dim cmd As ULCommand = Conn.CreateCommand()
    cmd.CommandText = "UPDATE Names SET name = ? WHERE id = ?"
    cmd.Parameters.Add("", txtName.Text)
    cmd.Parameters.Add("", updateID)
    RowsUpdated = cmd.ExecuteNonQuery()
    cmd.Dispose()
    RefreshListBox()
Catch
    MsgBox("Exception: " + Err.Description)
End Try

On the form design tab, double-click Delete to create a btnDelete_Click method. Add code to perform the following tasks:

Instantiates a ULCommand object and assigns it a DELETE statement. The DELETE statement deletes the selected row from the database, based on the associated ID from the integer array ids.
Executes the statement.
Disposes of the ULCommand object.
Refreshes the listbox.
If an error occurs, displays the error message. For SQL errors, the code also displays the error code.

For C#, add the following code to the btnDelete_Click method.

//Visual C#
try{
    long RowsDeleted;
    int deleteID = ids[lbNames.SelectedIndex];
    using( ULCommand cmd = Conn.CreateCommand() ){
        cmd.CommandText = 
            "DELETE From Names WHERE id = ?" ;
        cmd.Parameters.Add("", deleteID);
        RowsDeleted = cmd.ExecuteNonQuery ();
    }
    RefreshListBox();
} 
catch( Exception err ) { 
    MessageBox.Show("Exception: " + err.Message );
}

For Visual Basic, add the following code to the btnDelete_Click method.

'Visual Basic
Try
    Dim RowsDeleted As Long
    Dim deleteID As Integer = ids(lbNames.SelectedIndex)
    Dim cmd As ULCommand = Conn.CreateCommand()
    cmd.CommandText = "DELETE From Names WHERE id = ?"
    cmd.Parameters.Add("", deleteID)
    RowsDeleted = cmd.ExecuteNonQuery()
    cmd.Dispose()
    RefreshListBox()
Catch
    MsgBox("Exception: " + Err.Description)
End Try

Build your application to confirm that it compiles properly.