Inserting, updating, and deleting rows using the AseCommand object

To perform an Insert, Update, or Delete operation with the AseCommand object, 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”.

For information about obtaining primary key values for auto-increment primary keys, see “Obtaining primary key values”.

If you want to set the isolation level for a command, you must use the AseCommand object as part of an AseTransaction object. When you modify data without an AseTransaction object, Adaptive Server ADO.NET Data Provider operates in autocommit mode, and any changes that you make are applied immediately.

For more information, see “Transaction processing”.

Issuing a command that inserts a row

Declare and initialize an AseConnection object:

For C#:

AseConnection conn = new AseConnection( c_connStr );

For Visual Basic .NET:

Dim conn As New AseConnection(c_connStr)

Open the connection:

For C#:
```
conn.Open();
```
For Visual Basic .NET:
```
conn.Open()
```

Add an AseCommand object to define and execute an Insert statement:

For C#:

AseCommand insertCmd = new AseCommand(
   "INSERT INTO publishers " +
   " ( pub_id, pub_name, city, state) " +
   " VALUES( @pub_id, @pub_name, @city, @state )", 
   conn);

For Visual Basic .NET:

Dim insertCmd As new AseCommand( _
   "INSERT INTO publishers " + _
   " ( pub_id, pub_name, city, state) " + _
   " VALUES (@pub_id, @pub_name, @city, @state )",  _
   conn )

Set the parameters for the AseCommand object:

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

For C#:

AseParameter parm = new AseParameter("@pub_id", AseDbType.Char, 4);
insertCmd.Parameters.Add( parm );
parm = new AseParameter("@pub_name", AseDbType.VarChar, 40);
insertCmd.Parameters.Add( parm );
parm = new AseParameter("@city", AseDbType.VarChar, 20);
insertCmd.Parameters.Add( parm );
parm = new AseParameter("@state", AseDbType.Char, 2);
insertCmd.Parameters.Add( parm );

For Visual Basic .NET:

Dim parm As New AseParameter("@pub_id", AseDbType.Char, 4)
insertCmd.Parameters.Add(parm)
parm = New AseParameter("@pub_name", AseDbType.VarChar, 40)
insertCmd.Parameters.Add(parm)
parm = New AseParameter("@city", AseDbType.VarChar, 20)
insertCmd.Parameters.Add(parm)
parm = New AseParameter("@state", AseDbType.Char, 2)
insertCmd.Parameters.Add(parm)

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

For C#:

int recordsAffected = 0;
insertCmd.Parameters[0].Value = "9901";
insertCmd.Parameters[1].Value = "New Publisher";
insertCmd.Parameters[2].Value = "Concord";
insertCmd.Parameters[3].Value = "MA";
recordsAffected = insertCmd.ExecuteNonQuery();
insertCmd.Parameters[0].Value = "9902";
insertCmd.Parameters[1].Value = "My Publisher";
insertCmd.Parameters[2].Value = "Dublin";
insertCmd.Parameters[3].Value = "CA";
recordsAffected = insertCmd.ExecuteNonQuery();

For Visual Basic .NET:

Dim recordsAffected As Integer
insertCmd.Parameters(0).Value = "9901"
insertCmd.Parameters(1).Value = "New Publisher"
insertCmd.Parameters(2).Value = "Concord"
insertCmd.Parameters(3).Value = "MA"
recordsAffected = insertCmd.ExecuteNonQuery()
insertCmd.Parameters(0).Value = "9902"
insertCmd.Parameters(1).Value = "My Publisher"
insertCmd.Parameters(2).Value = "Dublin"
insertCmd.Parameters(3).Value = "CA"
recordsAffected = insertCmd.ExecuteNonQuery()

You can use an Insert, Update, or Delete statement with the ExecuteNonQuery method.

Display the results and bind them to the grid on the window:

For C#:

AseCommand selectCmd = new AseCommand("SELECT * FROM publishers", conn );
AseDataReader dr = selectCmd.ExecuteReader();
dataGrid.DataSource = dr;

For Visual Basic .NET:

Dim selectCmd As New AseCommand("SELECT * FROM publishers", conn)
Dim dr As AseDataReader = selectCmd.ExecuteReader()
DataGrid.DataSource = dr

Close the AseDataReader and AseConnection objects:

For C#:
```
dr.Close(); 
conn.Close();
```
For Visual Basic .NET:
```
dr.Close()
conn.Close()
```

Issuing a command that updates a row

Declare and initialize an AseConnection object:

For C#:

AseConnection conn = new AseConnection(
   c_connStr );

For Visual Basic .NET:

Dim conn As New AseConnection(c_connStr)

Open the connection:

For C#:

conn.Open();

For Visual Basic .NET:

conn.Open()

Add an AseCommand object to define and execute an update statement:

For C#:

AseCommand updateCmd = new AseCommand(
   "UPDATE publishers " +
   "SET pub_name = 'My Publisher' " +
   "WHERE pub_id='9901'",
   conn );

For Visual Basic .NET:

Dim updateCmd As New AseCommand( _
   "UPDATE publishers " + _
   "SET pub_name = 'My Publisher' " + _
   "WHERE pub_id='9901'", _
   conn )

For more information, see “Using stored procedures” and “AseParameter class”.

Call the ExecuteNonQuery method to apply the changes to the database:

For C#:

int recordsAffected = updateCmd.ExecuteNonQuery();

For Visual Basic .NET:

Dim recordsAffected As Integer = 
   updateCmd.ExecuteNonQuery()

Display the results and bind them to the grid on the window:

For C#:

AseCommand selectCmd = new AseCommand(
   "SELECT * FROM publishers", conn );
AseDataReader dr = selectCmd.ExecuteReader();
dataGrid.DataSource = dr;

For Visual Basic .NET:

Dim selectCmd As New AseCommand(_
   "SELECT * FROM publishers", conn)
Dim dr As AseDataReader = selectCmd.ExecuteReader()
DataGrid.DataSource = dr

Close the AseDataReader and AseConnection objects:

For C#:
```
dr.Close();
conn.Close();
```
For Visual Basic .NET:
```
dr.Close()
conn.Close()
```

Issuing a command that deletes a row

Declare and initialize an AseConnection object:

For C#:

AseConnection conn = new AseConnection(c_connStr);

For Visual Basic .NET:

Dim conn As New AseConnection(c_connStr)

Open the connection:

For C#:
```
conn.Open();
```
For Visual Basic .NET:
```
conn.Open()
```

Create an AseCommand object to define and execute a Delete statement:

For C#:

AseCommand updateCmd = new AseCommand
   "DELETE FROM publishers " +
   " WHERE (pub_id > '9900')",
   conn );

For Visual Basic .NET:

Dim updateCmd As New AseCommand(_
"DELETE FROM publishers " + _
"WHERE (pub_id > '9900')", _
conn )

Call the ExecuteNonQuery method to apply the changes to the database:

For C#:

int recordsAffected = deleteCmd.ExecuteNonQuery();

For Visual Basic .NET:

Dim recordsAffected As Integer = 
   updateCmd.ExecuteNonQuery()

Close the AseConnection object:

For C#:
```
conn.Close();
```
For Visual Basic .NET:
```
dr.Close()
conn.Close()
```