Introduction
Using the Microsoft OLE DB.NET Data Provider, together with SQL Anywhere Native OLE DB Provider, your managed .NET applications can have standardized access to your SQL Anywhere database. Alternatively, you may want to consider using SQL Anywhere .NET Data Provider as an integrated, higher-performance solution.
Required Software
Steps
- Start Visual Studio 2008
- Create a new project.
- From the File menu, choose New > Project.
The New Project dialog appears.
- In the left pane, select Visual C#.
- In the right pane, select Console Application.
- In the Name field, type CustomerDataReader.
- In the Location field, type c:\temp.
- Click OK to close the New Project dialog.
- After a few moments, a project will be created with the following code stub:
- Add the following using directives to the list located at the top of the code file. The System.Data.OleDB 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 System.Data.OleDb;
namespace CustomerDataReader
{
class Program
{
static void Main(string[] args)
{
}
}
}
- Copy the following code to the static void Main(string[] args method:
//Create a connection
OleDbConnection myConnection = new OleDbConnection(
@"Data Source=SQL Anywhere 12 Demo;Provider=SAOLEDB.12");
//open the connection
myConnection.Open();
//Create a command object.
OleDbCommand myCommand = myConnection.CreateCommand();
//Specify a query
myCommand.CommandText = "Select GivenName, Surname FROM Customers";
//Create a DataReader for the command
OleDbDataReader myDataReader = myCommand.ExecuteReader();
//Display the resulting Data
while (myDataReader.Read())
{
Console.WriteLine("{0} {1}", myDataReader["GivenName"], myDataReader["Surname"]);
}
//Close the DataReader.
myDataReader.Close();
//Close the connection
myConnection.Close();
//Keep the console open until a keystroke.
Console.ReadKey(true);
- Run the project by pressing F5.You should see a listing of names like the following:
Michaels Devlin
Beth Reiser
Erin Niedringhaus
Meghan Mason
Laura McCarthy
Paul Phillips
Kelly Colburn
....
- Hit any key to close the console window.
How does the application work?
Creating the OleDbConnection Object
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 (in this case SAOLEDB.12). 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:
OleDbConnection myConnection = new
OleDbConnection (@"Provider=SAOLEDB.12;UID=DBA;PWD=sql");
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:
OLEDbConnection myConnection = new
OleDbConnection (@"Provider=SAOLEDB.12;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.
Opening the Connection Object
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).
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.
OleDbCommand 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} {1}", myDataReader["GivenName"], myDataReader["Surname"]);
}
Cleanup
Finally, you close the DataReader and Connection objects.
myDataReader.Close();
myConnection.Close();
