Understanding the Table Viewer sample project

This section illustrates some key features of Adaptive Server ADO.NET Data Provider by walking through some of the code from the Table Viewer code sample. The Table Viewer project uses the Adaptive Server sample database, pubs2, which can be installed from the scripts located in the Adaptive Server installation directory.

In this section, the code is described a few lines at a time. To see all the code, open the sample project in Adaptive Server installation directory.

For C#:

<install dir> \Samples\CSharp\TableViewer\ TableViewer.csproj

For Visual Basic .NET:

<install dir>\Samples\VB.NET\TableViewer \TableViewer.vbproj

At the beginning of the program, it declares the import statement to import the Adaptive Server ADO.NET Data Provider information:

For C#:

using Sybase.Data.AseClient;

For Visual Basic .NET:

Imports Sybase.Data.AseClient

Use the AseConnection class to declare an instance variable of type AseConnection. This connection is used for the initial connection to the database, as well as when you click Execute to retrieve the result set from the database:

For C#:

private AseConnection
     _conn;

For Visual Basic .NET:

Private _conn As AseConnection

The following code provides a default value for the connection string that appears in the Connection String field by default:

For C#:

txtConnectString.Text = "Data Source='" + 
   System.Net.Dns.GetHostName() + 
   "';Port='5000';UID='sa';PWD='';Database='pubs2';";

For Visual Basic .NET:

txtConnectString.Text = "Data Source='" + _
   System.Net.Dns.GetHostName() + _
   "';Port='5000';UID='sa';PWD='';Database='pubs2';"

The Connection object later uses the connection string to connect to the sample database:

For C#:

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

For Visual Basic .NET:

_conn = New AseConnection(txtConnectString.Text)
_conn.Open()

The following code defines the default query that appears in the SQL Statement field:

For C#:

this.txtSQLStatement.Text = "SELECT * FROM authors";

For Visual Basic .NET:

Me.txtSQLStatement.Text = "SELECT * FROM authors"

Before the results are fetched, the application verifies whether the Connection object has been initialized. If it has, it ensures that the connection state is open:

For C#:

if( _conn == null || _conn.State != ConnectionState.Open )
{
     MessageBox.Show( "Connect to a database first.", "Not connected" );
     return;
}

For Visual Basic .NET:

If (_conn Is Nothing) OrElse (_conn.State <> ConnectionState.Open) Then
     MessageBox.Show("Connect to a database first.", "Not connected")
     Return
End If

When you are connected to the database, the following code uses the DataAdapter object (AseDataAdapter) to execute the SQL statement. A new DataSet object is created and filled with the results from the DataAdapter object. Finally, the contents of the DataSet are bound to the DataGrid control on the window.

For C#:

using(AseCommand cmd = new AseCommand( txtSQLStatement.Text.Trim(), _conn ))
{
     using(AseDataAdapter   da = new AseDataAdapter(cmd))
     {
          DataSet ds = new DataSet();
          da.Fill(ds, "Table");

          dgResults.DataSource = ds.Tables["Table"];
     }
}

For Visual Basic .NET:

Dim cmd As New AseCommand( _
   txtSQLStatement.Text.Trim(), _conn)
Dim da As New AseDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds, "Table")
dgResults.DataSource = ds.Tables("Table")

Because a global variable is used to declare the connection, the connection that was opened earlier is reused to execute the SQL statement.

If an error occurs when the application attempts to connect to the database, the following code catches the error and displays its message:

For C#:

catch( AseException ex ) 
{
   MessageBox.Show( ex.Source + " : " + ex.Message +
      " ("+ ex.ToString() + ")",
      "Failed to connect" );
}

For Visual Basic .NET:

Catch ex As AseException
   MessageBox.Show(ex.Source + " : " + ex.Message + _
   "(" + ex.ToString() + ")" + _
   "Failed to connect")
End Try