Getting data using the AseCommand object

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

ExecuteReader: Use to issue a command that returns a result set. By default, the Provider does not use cursors. The entire result set is fetched on the client side, and the user can fetch the rows one at a time in forward direction only. If the user turns on the use of cursors by adding the following line to the ConnectString:
```
"Use Cursor=true;"
```
then the Provider does not fetch the whole result set from the database server and instead uses a forward-only, read-only cursor.

Using cursors can improve performance when you expect your query to return a large resultset, but you do not necessarily expect the client to use the entire resultset.

In either case, you can loop quickly through the rows of the result set in only one direction.
For more information, see “ExecuteReader method”.
ExecuteScalar: Use 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.

For more information, see “ExecuteScalar method”.
ExecuteXmlReader: Use to issue a command that returns a result set in an XML format. Generally you use this method in select statements with a FOR XML clause.

For more information, see “ExecuteXmlReader method”.

The following instructions use the Simple code sample included with Adaptive Server ADO.NET Data Provider.

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

Issuing a command that returns a complete result set

Declare and initialize a Connection object:

For C#:

AseConnection conn = new AseConnection(connStr);

For Visual Basic .NET:

Dim conn As New AseConnection(connStr)

For C#:

try {
     conn.Open();
} 
catch (AseException ex)
{
     <error handling>
}

For Visual Basic .NET:

Try
     conn.Open()
Catch ex As AseExecption
     <error handling>
End Try

Add a Command object to define and execute a SQL statement:

For C#:
```
AseCommand cmd = new AseCommand(_
   "select au_lname from authors", conn );
```
For Visual Basic .NET:
```
Dim cmd As New AseCommand("select au_lname from authors", conn)
```
When you retrieve data from the database using a stored procedure, and the stored procedure returns both an output parameter value and a result set, then the result set will be reset and you will be unable to reference result set rows as soon as the output parameter value is referenced. In these situations, Sybase recommends that you reference and exhaust all rows in the result set and leave the referencing output parameter value to the end.
For more information, see “Using stored procedures” and “AseParameter class”.

Call the ExecuteReader method to return the DataReader object:

For C#:

AseDataReader reader = cmd.ExecuteReader();

For Visual Basic .NET:

Dim reader as AseDataReader = cmd.ExecuteReader()

Display the results:

For C#:

listAuthors.BeginUpdate();

while( reader.Read() ) {

   listAuthors.Items.Add( reader.GetString( 0 ) );
}
listAuthors.EndUpdate();

For Visual Basic .NET:

 listAuthors.BeginUpdate()
While reader.Read()
     listAuthors.Items.Add(reader.GetString(0))
End While
listAuthors.EndUpdate()

Close the DataReader and Connection objects:

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

Issuing a command that returns only one value

Declare and initialize an AseConnection object:

For C#:

AseConnection conn = new AseConnection(
   "Data Source='mango';" +
   "Port=5000;" +
   "UID='sa';" +
   "PWD='';" +
   "Database='pubs2';" );

For Visual Basic .NET:

Dim conn As New AseConnection( _
   "Data Source='mango';" + _ 
   "Port=5000;" + _
   "UID='sa';" + _
   "PWD='';" + _
   "Database='pubs2';")

where “mango” is the name of the database server.

Open the connection:

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

Add an AseCommand object to define and execute a SQL statement:

For C#:

AseCommand cmd = new AseCommand(
  "select count(*) from authors where state = 'CA'",
  conn );

For Visual Basic .NET:

Dim cmd As New AseCommand(
 "select count(*) from authors where state = 'CA'"_,
 conn );

Call the ExecuteScalar method to return the object containing the value:

For C#:
```
int count = (int) cmd.ExecuteScalar();
```
For Visual Basic .NET:
```
Dim count As Integer = cmd.ExecuteScalar()
```
Close the AseConnection object:

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

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

For more information, see “AseDataReader class”.

Issuing a command that returns an XmlReader object

Declare and initialize a Connection object:

For C#:

AseConnection conn = new AseConnection(connStr);

For Visual Basic .NET:

Dim conn As New AseConnection(connStr)

Open the connection:

For C#:

try {
   conn.Open();
}
catch (AseException ex)
{
   <error handling>
}

For Visual Basic .NET:

Try
   conn.Open()
Catch ex As AseExecption
   <error handling>
End Try

Add a Command object to define and execute a SQL statement:

For C#:

AseCommand cmd = new AseCommand(
   "select * from authors for xml",
   conn );

For Visual Basic .NET:

Dim cmd As New AseCommand( _
   "select au_lname from authors for xml", _
   conn

Call the ExecuteReader method to return the DataReader object:

For C#:

XmlReader reader = cmd.ExecuteXmlReader();

For Visual Basic .NET:

Dim reader as XmlReader = cmd.ExecuteXmlReader()

Use the XML Result:

For C#:

reader.read();
<process xml>

For Visual Basic .NET:

reader.read()
<process xml>

Close the DataReader and Connection objects:

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