ASA Programming Guide
  Developing Applications with the .NET Data Provider
    Accessing and manipulating data
      Using the AsaCommand object to retrieve and manipulate data

Inserting, updating, and deleting rows using the AsaCommand object

In order to perform an insert, update, or delete with the AsaCommand object, you use the ExecuteNonQuery function. The ExecuteNonQuery function issues a command (SQL statement or stored procedure) that does not return a result set.

For more information, see ExecuteNonQuery method.

You can only make changes (inserts, updates, or deletes) to data that is from a single table. You cannot update result sets that are based on joins. You must be connected to a database to use the AsaCommand object.

For information about obtaining primary key values for autoincrement primary keys, see Obtaining primary key values.

If you want to set the isolation level for a command, you must use the AsaCommand object as part of an AsaTransaction object. When you modify data without an AsaTransaction object, the .NET provider operates in autocommit mode and any changes that you make are applied immediately.

For more information, see Transaction processing.

To issue a command that inserts a row

1. Declare and initialize an AsaConnection object.

   AsaConnection   conn = new AsaConnection(
       c_connStr );

2. Open the connection.

   conn.Open();

3. Add an AsaCommand object to define and execute an INSERT statement.

   You can use an INSERT, UPDATE, or DELETE statement with the ExecuteNonQuery method.

   AsaCommand      insertCmd = new AsaCommand(
       "INSERT INTO department( dept_id, dept_name )
       VALUES( ?, ? )", conn );

   If you are calling a stored procedure, you must specify the parameters for the stored procedure.

   For more information, see Using stored procedures and AsaParameter class.

4. Set the parameters for the AsaCommand object.

   The following code defines parameters for the dept_id and dept_name columns respectively.

   AsaParameter parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Integer;
   insertCmd.Parameters.Add( parm );
   parm = new AsaParameter();
   parm.AsaDbType = AsaDbType.Char;
   insertCmd.Parameters.Add( parm );

5. Insert the new values and call the ExecuteNonQuery method to apply the changes to the database.

   insertCmd.Parameters[0].Value = 600;
   insertCmd.Parameters[1].Value = "Eastern Sales";
   int recordsAffected = insertCmd.ExecuteNonQuery();
   insertCmd.Parameters[0].Value = 700;
   insertCmd.Parameters[1].Value = "Western Sales";
   int recordsAffected = insertCmd.ExecuteNonQuery();

6. Display the results and bind them to the grid on the screen.

   AsaCommand      selectCmd = new AsaCommand(
       "SELECT * FROM department", conn );
   AsaDataReader   dr = selectCmd.ExecuteReader();
   dataGrid.DataSource = dr;

7. Close the AsaDataReader and AsaConnection objects.

   dr.Close();
   conn.Close();

To issue a command that updates a row

1. Declare and initialize an AsaConnection object.

   AsaConnection   conn = new AsaConnection(
       c_connStr );

2. Open the connection.

   conn.Open();

3. Add an AsaCommand object to define and execute an UPDATE statement.

   You can use an INSERT, UPDATE, or DELETE statement with the ExecuteNonQuery method.

   AsaCommand      updateCmd = new AsaCommand(
       "UPDATE department SET dept_name = 'Engineering'
       WHERE dept_id=100", conn );

   If you are calling a stored procedure, you must specify the parameters for the stored procedure.

   For more information, see Using stored procedures and AsaParameter class.

4. Call the ExecuteNonQuery method to apply the changes to the database.

   int recordsAffected = updateCmd.ExecuteNonQuery();

5. Display the results and bind them to the grid on the screen.

   AsaCommand      selectCmd = new AsaCommand(
       "SELECT * FROM department", conn );
   AsaDataReader   dr = selectCmd.ExecuteReader();
   dataGrid.DataSource = dr;

6. Close the AsaDataReader and AsaConnection objects.

   dr.Close();
   conn.Close();

To issue a command that deletes a row

1. Declare and initialize an AsaConnection object.

   AsaConnection   conn = new AsaConnection(
       c_connStr );

2. Open the connection.

   conn.Open();

3. Create an AsaCommand object to define and execute a DELETE statement.

   You can use an INSERT, UPDATE, or DELETE statement with the ExecuteNonQuery method.

   AsaCommand      deleteCmd = new AsaCommand(
       "DELETE FROM department WHERE ( dept_id > 500 )", conn );

   If you are calling a stored procedure, you must specify the parameters for the stored procedure.

   For more information, see Using stored procedures and AsaParameter class.

4. Call the ExecuteNonQuery method to apply the changes to the database.

   int recordsAffected = deleteCmd.ExecuteNonQuery();

5. Close the AsaConnection object.

   conn.Close();