UltraLite.NET User's Guide
  Understanding UltraLite Development
    Accessing and manipulating data with dynamic SQL

Data manipulation: INSERT, UPDATE and DELETE

To perform SQL Data Manipulation Language operations (INSERT, UPDATE, and DELETE), you carry out the following sequence of operations:

1. Prepare the statement.

   You can indicate parameters in the statement using the ? character.

2. Assign values for parameters in the statement.

   For any INSERT, UPDATE or DELETE, each ? is referred to by its ordinal position in the prepared statement.

3. Execute the statement.

4. Repeat steps 2 and 3 as required.

To perform INSERT operations using ExecuteStatement:

1. Declare a PreparedStatement.

   PreparedStatement prepStmt;

2. Assign a SQL statement to the PreparedStatement object.

   prepStmt = conn.PrepareStatement(
      "INSERT INTO MyTable(MyColumn) values (?)");

3. Assign input parameter values for the statement.

   The following code shows a string parameter.

   String newValue;
   // assign value
   prepStmt.SetStringParameter(1, newValue);

4. Execute the statement.

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

   long rowsInserted = prepStmt.ExecuteStatement();

5. If you have set AutoCommit to off, commit the change.

   conn.Commit();

To perform UPDATE operations using ExecuteStatement:

1. Declare a PreparedStatement.

   PreparedStatement prepStmt;

2. Assign a statement to the PreparedStatement object.

   prepStmt = conn.PrepareStatement(
      "UPDATE MyTable SET MyColumn1 = ? WHERE MyColumn2 = ?");

3. Assign input parameter values for the statement.

   String newValue;
   String oldValue;
   // assign values
   prepStmt.SetStringParameter( 1, newValue );
   prepStmt.SetStringParameter( 2, oldValue );

4. Execute the statement.

   long rowsUpdated = prepStmt.ExecuteStatement();

5. If you have set AutoCommit to off, commit the change.

   conn.Commit();

To perform DELETE operations using ExecuteStatement:

1. Declare a PreparedStatement.

   PreparedStatement prepStmt;

2. Assign a statement to the PreparedStatement object.

   prepStmt = conn.PrepareStatement(
      "DELETE FROM MyTable WHERE MyColumn = ?");

3. Assign input parameter values for the statement.

   String deleteValue;
   prepStmt.SetStringParameter(1, deleteValue);

4. Execute the statement.

   long rowsDeleted = prepStmt.ExecuteStatement();

5. If you have set AutoCommit to off, commit the change.

   conn.Commit();