This section illustrates some key features of the SQL Anywhere .NET Data Provider by walking through some of the code from the Table Viewer code sample. The Table Viewer project uses the SQL Anywhere sample database, demo.db, which is held in your SQL Anywhere samples directory.

For information about the location of the SQL Anywhere samples directory, see Samples directory.

For information about the sample database, including the tables in the database and the relationships between them, see SQL Anywhere sample database.

In this section the code is described a few lines at a time. Not all code from the sample is included here. To see all the code, open the sample project in samples-dir\SQLAnywhere\ADO.NET\TableViewer.

Declaring controls   The following code declares a couple of Labels named label1 and label2, a TextBox named txtConnectString, a button named btnConnect, a TextBox named txtSQLStatement, a button named btnExecute, and a DataGrid named dgResults.

private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox txtConnectString;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.TextBox txtSQLStatement;
private System.Windows.Forms.Button btnExecute;
private System.Windows.Forms.DataGrid dgResults;

Declaring a connection object   The SAConnection type is used to declare an uninitialized SQL Anywhere connection object. The SAConnection object is used to represent a unique connection to a SQL Anywhere data source.

private SAConnection _conn;

For more information about the SAConnection class, see SAConnection class.

Connecting to the database   The Text property of the txtConnectString object has a default value of "Data Source=SQL Anywhere 11 Demo". This value can be overridden by the application user by typing a new value into the txtConnectString text box. You can see how this default value is set by opening up the region or section in TableViewer.cs labeled Windows Form Designer Generated Code. In this section, you find the following line of code.

this.txtConnectString.Text = "Data Source=SQL Anywhere 11 Demo";

Later, the SAConnection object uses the connection string to connect to a database. The following code creates a new connection object with the connection string using the SAConnection constructor. It then establishes the connection by using the Open method.

_conn = new SAConnection( txtConnectString.Text );
_conn.Open();

For more information about the SAConnection constructor, see SAConnection members.

Defining a query   The Text property of the txtSQLStatement object has a default value of "SELECT * FROM Employees". This value can be overridden by the application user by typing a new value into the txtSQLStatement text box.

The SQL statement is executed using an SACommand object. The following code declares and creates a command object using the SACommand constructor. This constructor accepts a string representing the query to be executed, along with the SAConnection object that represents the connection that the query is executed on.

SACommand cmd = new SACommand( txtSQLStatement.Text.Trim(), 
                                           _conn );

For more information about the SACommand object, see SACommand class.

Displaying the results   The results of the query are obtained using an SADataReader object. The following code declares and creates an SADataReader object using the ExecuteReader constructor. This constructor is a member of the SACommand object, cmd, that was declared previously. ExecuteReader sends the command text to the connection for execution and builds an SADataReader.

SADataReader dr = cmd.ExecuteReader();

The following code connects the SADataReader object to the DataGrid object, which causes the result columns to appear on the screen. The SADataReader object is then closed.

dgResults.DataSource = dr;
dr.Close();

For more information about the SADataReader object, see SADataReader class.

Error handling   If there is an error when the application attempts to connect to the database or when it populates the Tables combo box, the following code catches the error and displays its message:

try {
  _conn = new SAConnection( txtConnectString.Text );
  _conn.Open();

  SACommand cmd = new SACommand(
    "SELECT table_name FROM SYS.SYSTAB where creator = 101", _conn );
  SADataReader   dr = cmd.ExecuteReader();

  comboBoxTables.Items.Clear();
  while ( dr.Read() ) {
    comboBoxTables.Items.Add( dr.GetString( 0 ) );
  }
  dr.Close();
} catch( SAException ex ) {
  MessageBox.Show( ex.Errors[0].Source + " : " +
       ex.Errors[0].Message + " (" +
       ex.Errors[0].NativeError.ToString() + ")",
       "Failed to connect" );
}

For more information about the SAException object, see SAException class.