Switch to standard view 
  Sybase logo
 
 
 



Introduction

Using the SQL Anywhere .NET Data Provider, your managed .NET applications can have integrated and strandaradized access to your SQL Anywhere database. This is the recommended method of establishing an ADO.NET connection to the database.

Required software

This tutorial was created in the context of SQL Anywhere 11.0.0 or later, however its content may be applicable to previous and future releases.

SQL Anywhere Data Providers

SQL Anywhere supports the Microsoft .NET Framework through the following namespaces.

iAnywhere.Data.SQLAnywhere   The ADO.NET object model is an all-purpose data access model. ADO.NET components were designed to factor data access from data manipulation. There are two central components of ADO.NET that do this: the DataSet, and the .NET Framework data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects. SQL Anywhere includes a .NET Framework Data Provider that communicates directly with a SQL Anywhere database server without adding the overhead of OLE DB or ODBC. The SQL Anywhere .NET Data Provider is represented in the .NET namespace as iAnywhere.Data.SQLAnywhere.

The Microsoft .NET Compact Framework is the smart device development framework for Microsoft .NET. The SQL Anywhere .NET Compact Framework Data Provider supports devices running Windows Mobile.

System.Data.Oledb   This namespace supports OLE DB data sources. This namespace is an intrinsic part of the Microsoft .NET Framework. You can use System.Data.Oledb together with the SQL Anywhere OLE DB provider, SAOLEDB, to access SQL Anywhere databases.

System.Data.Odbc   This namespace supports ODBC data sources. This namespace is an intrinsic part of the Microsoft .NET Framework. You can use System.Data.Odbc together with the SQL Anywhere ODBC driver to access SQL Anywhere databases.

On Windows Mobile, only the SQL Anywhere .NET Data Provider is supported.

There are some key benefits to using the SQL Anywhere .NET Data Provider:

In the .NET environment, the SQL Anywhere .NET Data Provider provides native access to a SQL Anywhere database. Unlike the other supported providers, it communicates directly with a SQL Anywhere server and does not require bridge technology. As a result, the SQL Anywhere .NET Data Provider is faster than the OLE DB and ODBC Data Providers. It is the recommended data provider for accessing SQL Anywhere databases.

Steps

  1. Start Visual Studio 2008
  2. Create a new project.
    1. From the File menu, choose New > Project. The New Project dialog appears.
    2. In the left pane, select Visual C#.
    3. In the right pane, select Console Application.
    4. In the Name field, type EmployeeDataReader.
    5. In the Location field, type c:\temp.
    6. Click OK to close the New Project dialog.
    7. After a few moments, a project will be created with the following code stub:
    8. Add a reference to the SQL Anywhere .NET Data Provider assembly.
      1. In the Solution Explorer window, right-click References and choose Add Reference from the popup menu. The Add Reference dialog appears.
      2. On the .NET tab, click Browse to locate any of the following:
          1. iAnywhere.Data.SQL Anywhere.dll for .NET 2.0
          2. iAnywhere.Data.SQL Anywhere.dll for .NET 3.5
          3. iAnywhere.Data.SQL Anywhere.dll for .NET 4.0

      3. Note that there is separate version of the provider for Windows Mobile platforms. For the Windows Mobile SQL Anywhere .NET Data Provider, click the Browse tab and locate the Windows Mobile version of the provider. The default location is install-dir\CE\Assembly\V2.
      4. Select the appropriate assembly and then click Open.
    9. Add the following using directive to the beginning of your project to set the System.Data and iAnywhere.Data.SQLAnywhere namespaces, contain all of the ADO.NET classes necessary for database connectivity. Your code file should look like this:
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      Using System.Data;
      Using iAnywhere.Data.SQLAnywhere;

      namespace CustomerDataReader
      {
            class Program
            {
               static void Main(string[] args)
               {
               }
            }
      }
    10. Copy the following code into the static void Main(string[] args) method:
      //Create a connection, replace the data source name with the name of the SQL Anywhere Demo Database that you installed
      SAConnection myConnection = new SAConnection("Data Source=SQL Anywhere 11 Demo;UID=DBA;PWD=sql");
      //open the connection
      ;myConnection.Open();
      //Create a command object.
      SACommand myCommand = myConnection.CreateCommand();
      //Specify a query.
      myCommand.CommandText = "SELECT Surname FROM Employeess";
      //Create a DataReader for the command
      SADataReader myDataReader = myCommand.ExecuteReader();

      //Display the resulting data.
      while ( myDataReader.Read())
      {
         Console.WriteLine("{0}", myDataReader["Surname"]);
      }

      //Close the DataReader.
      myDataReader.Close();
      //Close the connection.
      myConnection.Close ();

      //Keep the console open until a keystroke
      Console.ReadKey (true);

    11. Run the project by hitting F5. You should see a list of names like the following:
        Whitney
        Cobb
        Chin
        Jordan
        ...
    12. Hit any key to close the console window.

    How does the application work?

    Creating the SA Connection Object

    The SAConnection object must be initialized before you can use any other ADO.NET objects. It creates the connection between the application and the SQL Anywhere .NET Provider. This example uses an ODBC data source. It is not a requirement for connecting to the database. You must pass the rest of the connection string, which can be contained in a data source. If the database server is already running, you only need to pass the user ID and password. The connection string looks similar to the following one:

    SAConnection myConnection = new SAConnection(@"UID=DBA;PWD=sql;ENG=MyDbEng");


    If you need the application to start the database server when you run it without using a DSN, then the connection string is similar to the following one:

    SAConnection myConnection = new SAConnection(@"UID=DBA;PWD=sql;DBF=C:\MyDB.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.

  3. Opening the SAConnection Object

    This method is required to open the connection between the .NET application and the provider. If this method fails, an exception is thrown. (iAnywhere.Data.SQLAnywhere.SAException).
    myConnection.Open()

    Specifying a Query Statement

    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 given name and surname of the customers, you pass the SQL statement to the CommandText property of the Command object.

    SACommand myCommand = myConnection.CreateCommand();
    myCommand.CommandText = "SELECT GivenName, Surname FROM Customers";

    Reading Data

    The DataReader object is used in this example to get the result of a query quickly. This is a read-only object. You cannot update the data. The DataReader's read method reads one row at a time. It returns true as long as there is data to read. It returns false once there is no more data to read.

    while ( myDataReader.Read())
    {
          Console.WriteLine("{0}", myDataReader["SurName"]);
    }

    Cleanup

    Finally, you close the DataReader and Connection objects.

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


Back to Top
© Copyright 2010, Sybase Inc.