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:

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”.

StepsIssuing a command that returns a complete result set

  1. 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
    
  2. 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)
    

    NoteWhen 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”.

  3. 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()
    
  4. 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()
    
  5. Close the DataReader and Connection objects:

    For C#:

    reader.Close(); 
    
    conn.Close();
    

    For Visual Basic .NET:

    reader.close()
    conn.close()
    

StepsIssuing a command that returns only one value

  1. 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.

  2. Open the connection:

    For C#:

    conn.Open();
    

    For Visual Basic .NET:

    conn.Open()
    
  3. 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 );
    
  4. 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()
    
  5. 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”.

StepsIssuing a command that returns an XmlReader object

  1. Declare and initialize a Connection object:

    For C#:

    AseConnection conn = new AseConnection(connStr);
    

    For Visual Basic .NET:

    Dim conn As New AseConnection(connStr)
    
  2. 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
    
  3. 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
    
  4. 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()
    
  5. Use the XML Result:

    For C#:

    reader.read();
    <process xml>
    

    For Visual Basic .NET:

    reader.read()
    <process xml>
    
  6. Close the DataReader and Connection objects:

    For C#:

    reader.Close();
    conn.Close();
    

    For Visual Basic .NET:

    reader.close()
    conn.close()