Connecting to a SQL Anywhere Studio Database Using ADO.NET
2006-06-05 23:10:03 来源:WEB开发网核心提示: This document explains how to dynamically create a connection to a SQL Anywhere Studio database through a C# project.Required Software Sybase SQ
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
- Start Visual Studio .NET.
- 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.
- 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;
- 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. - 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();
- 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 continueHow does the application work?
- 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' occurred 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.
Tags:Connecting to SQL
编辑录入:coldstar [复制链接] [打 印][]
更多精彩
赞助商链接