Data manipulation: INSERT, UPDATE, and DELETE

With UltraLite, you can perform SQL data manipulation language operations. These operations are performed using the ULCommand.ExecuteNonQuery method.

See ULCommand class.

Placeholders for parameters in SQL statements are indicated by the ? character. For any INSERT, UPDATE, or DELETE, each ? is referenced according to its ordinal position in the command's parameters collection. For example, the first ? is referred to as 0, and the second as 1.

To insert a row
  1. Declare a ULCommand.

    ULCommand cmd;
  2. Assign a SQL statement to the ULCommand object.

    cmd = conn.CreateCommand();
    cmd.Command = "INSERT INTO MyTable(MyColumn) values (?)";
  3. Assign input parameter values for the statement.

    The following code shows a string parameter.

    String newValue;
    // assign value
    cmd.Parameters.add("", newValue);
  4. Execute the statement.

    The return value indicates the number of rows affected by the statement.

    int rowsInserted = cmd.ExecuteNonQuery();
  5. If you are using explicit transactions, commit the change.

    myTransaction.Commit();
To update a row
  1. Declare a ULCommand.

    ULCommand cmd;
  2. Assign a statement to the ULCommand object.

    cmd = conn.CreateCommand();
    cmd.Command = "UPDATE MyTable SET MyColumn1 = ? WHERE MyColumn2 = ?";
  3. Assign input parameter values for the statement.

    String newValue;
    String oldValue;
    // assign values
    cmd.Parameters.add("", newValue);
    cmd.Parameters.add("", oldValue);
  4. Execute the statement.

    int rowsUpdated = cmd.ExecuteNonQuery();
  5. If you are using explicit transactions, commit the change.

    myTransaction.Commit();
To delete a row
  1. Declare a ULCommand.

    ULCommand cmd;
  2. Assign a statement to the ULCommand object.

    cmd = conn.CreateCommand();
    cmd.Command = "DELETE FROM MyTable WHERE MyColumn = ?";
  3. Assign input parameter values for the statement.

    String deleteValue;
    // assign value
    cmd.Parameters.add("", deleteValue);
  4. Execute the statement.

    int rowsDeleted = cmd.ExecuteNonQuery();
  5. If you are using explicit transactions, commit the change.

    myTransaction.Commit();