Getting data using the AsaCommand object

Using the AsaCommand object to retrieve and manipulate data

Inserting, updating, and deleting rows using the AsaCommand object

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

Getting data using the AsaCommand object

The AsaCommand object allows you to issue a SQL statement or call a stored procedure against an Adaptive Server Anywhere database. You can issue the following types of commands to retrieve data from the database:

ExecuteReader Used to issue a command that returns a result set. This method uses a forward-only, read-only cursor. You can loop quickly through the rows of the result set only in one direction.

For more information, see ExecuteReader method.
ExecuteScalar Used to issue a command that returns a single value. This can be the first column in the first row of the result set, or a SQL statement that returns an aggregate value such as COUNT or AVG. This method uses a forward-only, read-only cursor.

For more information, see ExecuteScalar method.

When using the AsaCommand object you can use the AsaDataReader to retrieve a result set that is based on a join. However, 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.

The following instructions use the Simple code sample included with the .NET data provider.

For more information about the Simple code sample, see Understanding the Simple sample project.

To issue a command that returns a complete result set

Declare and initialize a Connection object.

AsaConnection conn = new AsaConnection(
    "Data Source=ASA 9.0 Sample;UID=DBA;PWD=SQL" );

Open the connection.
```
try {
    conn.Open();
```
Add a Command object to define and execute a SQL statement.
```
AsaCommand cmd = new AsaCommand(
     "select emp_lname from employee", 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.
Call the ExecuteReader method to return the DataReader object.
```
AsaDataReader reader = cmd.ExecuteReader();
```

Display the results.

listEmployees.BeginUpdate();
while( reader.Read() ) {
     listEmployees.Items.Add( reader.GetString( 0 ) );
}
listEmployees.EndUpdate();

Close the DataReader and Connection objects.
```
reader.Close();
conn.Close();
```

To issue a command that returns only one value

Declare and initialize an AsaConnection object.

AsaConnection conn = new AsaConnection(
    "Data Source=ASA 9.0 Sample" );

Open the connection.
```
conn.Open();
```
Add an AsaCommand object to define and execute a SQL statement.
```
AsaCommand cmd = new AsaCommand(
    "select count(*) from employee where sex = 'M'",
    conn );
```
If you are calling a stored procedure, you must specify the parameters for the stored procedure.

For more information, see Using stored procedures.
Call the ExecuteScalar method to return the object containing the value.
```
int count = (int) cmd.ExecuteScalar();
```
Close the AsaConnection object.
```
conn.Close();
```

When using the AsaDataReader, there are several Get methods available that you can use to return the results in desired the data type.

For more information, see AsaDataReader class.

Visual Basic .NET DataReader example

The following Visual Basic .NET code opens a connection to the Adaptive Server Anywhere sample database and uses the DataReader to return the last name of the first five employees in the result set:

Dim myConn As New .AsaConnection()
Dim myCmd As _
  New .AsaCommand _
  ("select emp_lname from employee", myConn)
Dim myReader As AsaDataReader
Dim counter As Integer
myConn.ConnectionString = _
  "Data Source=ASA 9.0 Sample;UID=DBA;PWD=SQL"
myConn.Open()
myReader = myCmd.ExecuteReader()
counter = 0
Do While (myReader.Read())
  MsgBox(myReader.GetString(0))
  counter = counter + 1
  If counter >= 5 Then Exit Do
Loop
myConn.Close()