iAnywhere.com - Lesson 4: Insert, update, and delete data

Tutorial: Build an UltraLite.NET Application

Lesson 4: Insert, update, and delete 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.

For more information, see Choosing between components and static interfaces or Accessing and manipulating data with the Table API.

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

To add code to maintain the listbox

Right-click the form and select 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 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 more information about the error code, see ASA Error Messages.

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++;
                }
            }
        }
    }
    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()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Build the project.

Building the project should result in no errors.

To implement INSERT, UPDATE and DELETE

Double-click the Insert button 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 textbox 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 more information about the error code, see ASA Error Messages.

For C#, add the following code to the 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 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

Double-click the Update button 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 textbox 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 more information about the error code, see ASA Error Messages.

For C#, add the following code to the 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 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

Double-click the Delete button to create a btnDelete_Click method. Add code to carry out 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, prints the error message. For SQL errors, the code also prints the error code. For more information about the error code, see ASA Error Messages.

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

//Visual C#
try{
    int deleteID = ids[lbNames.SelectedIndex];
    long RowsDeleted;
    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 method.

'Visual Basic
Try
    Dim deleteID As Integer = ids(lbNames.SelectedIndex)
    Dim RowsDeleted As Long
    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.