Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Getting data using the SACommand object

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

• ExecuteReader    Issues 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 in one direction.

  For more information, see ExecuteReader methods.

• ExecuteScalar    Issues 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 SACommand object, you can use the SADataReader 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

1. Declare and initialize a Connection object.

   SAConnection conn = new SAConnection(
       "Data Source=SQL Anywhere 10 Demo;UID=DBA;PWD=sql" );

2. Open the connection.

   try {
       conn.Open();

3. Add a Command object to define and execute a SQL statement.

   SACommand cmd = new SACommand(
        "SELECT Surname FROM Employees", 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.

4. Call the ExecuteReader method to return the DataReader object.

   SADataReader reader = cmd.ExecuteReader();

5. Display the results.

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

6. Close the DataReader and Connection objects.

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

To issue a command that returns only one value

1. Declare and initialize an SAConnection object.

   SAConnection conn = new SAConnection(
       "Data Source=SQL Anywhere 10 Demo" );

2. Open the connection.

   conn.Open();

3. Add an SACommand object to define and execute a SQL statement.

   SACommand cmd = new SACommand(
       "SELECT COUNT(*) FROM Employees 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.

4. Call the ExecuteScalar method to return the object containing the value.

   int count = (int) cmd.ExecuteScalar();

5. Close the SAConnection object.

   conn.Close();

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

For more information, see SADataReader class.

The following Visual Basic .NET code opens a connection to the SQL 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 .SAConnection()
Dim myCmd As _
  New .SACommand _
  ("SELECT Surname FROM Employees", myConn)
Dim myReader As SADataReader
Dim counter As Integer
myConn.ConnectionString = _
  "Data Source=SQL Anywhere 10 Demo;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()