Inserting, updating, and deleting rows using the SACommand object

To perform an insert, update, or delete with the SACommand object, use the ExecuteNonQuery function. The ExecuteNonQuery function issues a query (SQL statement or stored procedure) that does not return a result set. 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 SACommand 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 SQL statement, you must use the SACommand object as part of an SATransaction object. When you modify data without an SATransaction object, the provider operates in autocommit mode and any changes that you make are applied immediately. See Transaction processing.

To issue a statement that inserts a row

Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection(
    c_connStr );
conn.Open();

Open the connection.
conn.Open();

Add an SACommand object to define and execute an INSERT statement.

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

SACommand insertCmd = new SACommand(
    "INSERT INTO Departments( DepartmentID, DepartmentName )
    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 SAParameter class.

Set the parameters for the SACommand object.

The following code defines parameters for the DepartmentID and DepartmentName columns respectively.

SAParameter parm = new SAParameter();
parm.SADbType = SADbType.Integer;
insertCmd.Parameters.Add( parm );
parm = new SAParameter();
parm.SADbType = SADbType.Char;
insertCmd.Parameters.Add( parm );

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";
recordsAffected = insertCmd.ExecuteNonQuery();

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

SACommand selectCmd = new SACommand(
    "SELECT * FROM Departments", conn );
SADataReader dr = selectCmd.ExecuteReader();

System.Windows.Forms.DataGrid dataGrid;
dataGrid = new System.Windows.Forms.DataGrid();
dataGrid.Location = new Point(10, 10);
dataGrid.Size = new Size(275, 200);
dataGrid.CaptionText = "iAnywhere SACommand Example";
this.Controls.Add(dataGrid);

dataGrid.DataSource = dr;
dataGrid.Show();

Close the SADataReader and SAConnection objects.
dr.Close(); conn.Close();

To issue a statement that updates a row

Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection(
    c_connStr );

Open the connection.
conn.Open();

Add an SACommand object to define and execute an UPDATE statement.

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

SACommand updateCmd = new SACommand(
    "UPDATE Departments SET DepartmentName = 'Engineering'
    WHERE DepartmentID=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 SAParameter class.

Call the ExecuteNonQuery method to apply the changes to the database.
int recordsAffected = updateCmd.ExecuteNonQuery();

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

SACommand selectCmd = new SACommand(
    "SELECT * FROM Departments", conn );
SADataReader   dr = selectCmd.ExecuteReader();
dataGrid.DataSource = dr;

Close the SADataReader and SAConnection objects.
dr.Close(); conn.Close();

To issue a statement that deletes a row

Declare and initialize an SAConnection object.

SAConnection conn = new SAConnection(
    c_connStr );

Open the connection.
conn.Open();

Create an SACommand object to define and execute a DELETE statement.

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

SACommand deleteCmd = new SACommand(
    "DELETE FROM Departments WHERE ( DepartmentID > 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 SAParameter class.

Call the ExecuteNonQuery method to apply the changes to the database.
int recordsAffected = deleteCmd.ExecuteNonQuery();
Close the SAConnection object.
conn.Close();

Discuss this page in DocCommentXchange.