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.

• MILY: Helv">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

• 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.

//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();

                :

                :

        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

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.

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' oclearcase/" target="_blank" >ccurred in system.data.dll

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.

原文转自：http://www.ltesting.net