Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - .NET Programming » Tutorial: Build an UltraLite.NET Application

Lesson 3: Connect to the database Next Page

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.

See Accessing and manipulating data with 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.

To add code to maintain the listbox
  1. Right-click the form and choose View Code.

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

    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
  3. Build the project.

    Building the project should result in no errors.

To implement INSERT, UPDATE, and DELETE
  1. Double-click the Insert button to create a btnInsert_Click method. This method carries out the following tasks:

    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
  2. Double-click the Update button to create a btnUpdate_Click method. This method carries out the following tasks:

    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
  3. Double-click the Delete button to create a btnDelete_Click method. Add code to carry out the following tasks:

    For C#, add the following code to the 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 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
  4. Build your application to confirm that it compiles properly.