Connecting to a SQL Anywhere Studio Database Using ADO.NET

 This document explains how to dynamically create a connection to a SQL Anywhere Studio database through a C# project.Required Software

• • Sybase SQL Anywhere Studio 7.x or later
  • asademo.db file (included with Adaptive Server Anywhere)
  • ASA 8.0 Sample data source (created by default when Adaptive Server Anywhere is installed)
  • Microsoft Visual Studio .NET version 7.0
  • Microsoft ADO.NET
  • Windows NT, 98, 2000, Me, or XP

Steps

1. Start Visual Studio .NET.

2. Create a new project. Select Visual C# Projects from the left side.

• • • Select Console Application from the right side.
    • Enter the project name CustomerDataReader.
    • Enter the project location: c:\temp.
    • Click OK to close the New Project dialog.

3. In your code, you must set the  System.Data name space. This is where all the ADO.NET classes are located. Enter the following  using directive at the beginning of your project:

   Using system.Data;

4. The next required  using directive is the OLE DB .NET Data Provider. Add the following  using directive to your project to use the Microsoft OLE DB .NET provider:

   Using System.Data.OleDb;

   Your source should now look like the following:

   using System; using System.Data; using System.Data.OleDb; namespace CustomerDataReader { /// <summary> /// Summary description for Class1. /// </summary> class Class1 { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { // // TODO: Add code to start application here // } } }

   Now you can write the code required to establish communication between Adaptive Server Anywhere and your C# application.

5. Add the following code to the  public static void Main() function after the  //TODO: Add code to start application here  comment:

//Set your connection string OleDbConnection myConnection =  new OleDbConnection(   @"Data Source=ASA 8.0 Sample;Provider=ASAProv.80"); //open the connection myConnection.Open(); //Creating command object. OleDbCommand myCommand = myConnection.CreateCommand(); //Specify query myCommand.CommandText = "Select fname, lname from Customer";

//DataReader for the command OleDbDataReader myDataReader = myCommand.ExecuteReader(); //Let's display data while ( myDataReader.Read()) { Console.WriteLine("\t{0}\t{1}", myDataReader["fname"],myDataReader["lname"]); } myDataReader.Close(); myConnection.Close();

6. Run the project by pressing CTRL+F5. You should see the following listing:

　　　　　　　  : 　　　　　　　  : 　　　  Dominic Johansen 　　　  Stanley Jue 　　　  Harry　 Jones 　　　  Marie　 Curie 　　　  Elizabeth　　　 Bordon 　　　  Len　　 Manager 　　　  Tony　  Antolini 　　　  Tom　　 Cruz 　　　  Janice  O'Toole 　　　  Stevie  Nickolas 　　　  Philipe Fernandez 　　　  Jennifer　　　  Stutzman 　　　  William Thompson Press any key to continue

How does the application work?

1. OleDbConnection Object

OleDbConnection myConnection =  new OleDbConnection( 　　 @"Data Source=ASA 8.0 Sample;Provider=ASAProv.80");

The OleDbConnection object must be initialized before you can use any other ADO.NET objects. It creates the connection between the application and the database provider. This object requires the provider, in this case ASAProv or ASAProv.80. Then, you have to pass the rest of the connection string, which could be contained in a data source. If the engine is already running, then you only need to pass the user id and password, as follows:  uid=dba;pwd=sql ; The connection string would look similar to the following:

OleDbConnection myConnection =  new OleDbConnection("Provider=ASAProv.80;uid=dba;pwd=sql");

If you need the application to start the engine when you run it without using a DSN, then the connection string would look similar to the following:

OleDbConnection myConnection =  new OleDbConnection( 　　 @"Provider=ASAProv.80;uid=dba;pwd=sql;dbf=c:\temp\dbfile.db");

The @ sign prefacing the connection string allows the backslash in the file name to work; otherwise, double backslashes are necessary to escape the backslash character inside a C# string.

2. Open Connection Object

myConnection.Open()

This method is required to open the connection between the .NET application and the provider. If this method fails, an exception is thrown.

'System.Data.OleDb.OleDbException' occurred in system.data.dll

3. SQL Command

   OleDbCommand myCommand = myConnection.CreateCommand(); //Specify query myCommand.CommandText = "Select fname, lname from Customer";

   Once the connection is opened successfully, you can issue a SQL statement. First, a command object must be created to perform database operations. Once the command object is created,  the CommandText property must be set. Since you want to fetch the first name and last name of the customers, you pass the SQL statement to the CommandText property of the Command object. DataReader

   OleDbDataReader myDataReader = myCommand.ExecuteReader();

   The DataReader object is used in this example to quickly get the result of a query. This is a read only object: you cannot update the data. The following code displays the data:

   while ( myDataReader.Read())  { Console.WriteLine("\t{0}\t{1}", myDataReader["fname"],myDataReader["lname"]); }

   The DataReader's read() method allows you to read one row at a time. It returns true as long as there is data to read; otherwise, it returns false.

   myDataReader.Close();
   myConnection.Close();

Finally, you close the DataReader and Connection objects.