Connecting to Access and Oracle Databases

In this section you'll see examples of connecting to both an Access and an Oracle database. To interact with either of these databases in your program, you use classes from the System.Data.OleDb namespace. This namespace contains classes for use with databases that support object linking and embedding for databases (OLE DB) such as Access or Oracle. You'll learn more about the System.Data.OleDb namespace in Chapter 5, "Overview of the ADO.NET Classes."

Connecting to an Access Database

You connect to an Access database using an OleDbConnection object-rather than a SqlConnection object-with a connection string of the following format:

 provider=Microsoft.Jet.OLEDB.4.0;data source=databaseFile 

where databaseFile is the directory and filename of your Access database. Notice that you specify the provider in the connection string, which is set to Microsoft.Jet.OLEDB.4.0.

The following example creates a string named connectionString with the appropriate format to connect to the Access Northwind database stored in the Northwind.mdb file:

 string connectionString =   "provider=Microsoft.Jet.OLEDB.4.0;" +   "data source=F:\\Program Files\\Microsoft   Office\\Office\\Samples\\Northwind.mdb"; 

Note 

Notice the use of two backslash characters in the data source part of the connection string. The first backslash is used to specify that the second backslash is to be treated literally; therefore \\ is treated as \ in the connection string. You'll need to locate the Northwind.mdb file on your hard disk and set your connection string appropriately.

Assuming the System.Data.OleDb namespace has been imported, the following example creates an OleDbConnection object, passing connectionString (set in the previous line of code) to the constructor:

 OleDbConnection myOleDbConnection =   new 01eDbConnection(connectionString); 

Listing 1.2 illustrates how to connect to the Northwind Access database using an OleDbConnection object and retrieve a row from the Customers table. Notice that you use an OleDbCommand and OleDbDataReader object to run a SQL statement and read the returned results from an Access database.

Listing 1.2: OLEDBCONNECTIONACCESS.CS

start example
 /*   OleDbConnectionAccess.cs illustrates how to use an   OleDbConnection object to connect to an Access database */ using System; using System.Data; using System.Data.OleDb; class OleDbConnectionAccess {   public static void Main()   {     // formulate a string containing the details of the     // database connection     string connectionString =       "provider=Microsoft.Jet.OLEDB.4.0;" +       "data source=F:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb";     // create an OleDbConnection object to connect to the     // database, passing the connection string to the constructor     OleDbConnection myOleDbConnection =       new OleDbConnection(connectionString);     // create an OleDbCommand object     OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();     // set the CommandText property of the OleDbCommand object to     // a SQL SELECT statement that retrieves a row from the Customers table     myOleDbCommand.CommandText =       "SELECT CustomerID, CompanyName, ContactName, Address "+       "FROM Customers "+       "WHERE CustomerID = 'ALFKI'";     // open the database connection using the     // Open() method of the OleDbConnection object     myOleDbConnection.Open();     // create an OleDbDataReader object and call the ExecuteReader()     // method of the OleDbCommand object to run the SELECT statement     OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();     // read the row from the OleDbDataReader object using     // the Read() method     myOleDbDataReader.Read();     // display the column values     Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = "+       myOleDbDataReader["CustomerID"]);     Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = "+       myOleDbDataReader["CompanyName"]);     Console.WriteLine("myOleDbDataReader[\" ContactName\"] = "+       myOleDbDataReader["ContactName"]);     Console.WriteLine("myOleDbDataReader[\" Address\"] = "+       myOleDbDataReader["Address"]);     // close the OleDbDataReader object using the Close() method     myOleDbDataReader.Close();     // close the OleDbConnection object using the Close() method     myOleDbConnection.Close();   } } 
end example

The output from this program is as follows:

 myOleDbDataReader["CustomerID"] = ALFKI myOleDbDataReader["CompanyName"] = Alfreds Futterkiste myOleDbDataReader["ContactName"] = Maria Anders myOleDbDataReader["Address"] = Obere Str. 57 

Connecting to an Oracle Database

You connect to an Oracle database using an OleDbConnection object with a connection string of the following format:

 provider=MSDAORA;data source=OracleNetServiceName;user id=username;password=password 

where

  • OracleNetServiceName Specifies the Oracle Net service name for the database. Oracle Net is a software component that allows you to connect to a database over a network. You'll need to speak with your DBA to get the Oracle Net service name.

  • username Specifies the name of the database user you want to connect to the database as.

  • password Specifies the password for the database user.

The following example creates a connection string named connectionString with the correct format to connect to an Oracle database:

 string connectionString =   "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER"; 

Note 

The user ID of SCOTT with a password of TIGER is the default for accessing one of the example databases that comes with Oracle. This database contains a table called emp that contains sample employee data.

Assuming the System.Data.OleDb namespace has been imported, the following example creates an OleDbConnection object, passing connectionString to the constructor:

 OleDbConnection myOleDbConnection =   new OleDbConnection(connectionString); 

Listing 1.3 illustrates how to connect to an Oracle database using an OleDbConnection object and retrieve a row from the emp table. Notice that you use an OleDbCommand and OleDbDataReader object to run a SQL statement and read the returned results from an Oracle database.

Listing 1.3: OLEDBCONNECTIONORACLE.CS

start example
 /*   OleDbConnectionOracle.cs illustrates how to use an   OleDbConnection object to connect to an Oracle database */ using System; using System.Data; using System.Data.OleDb; class OleDbConnectionOracle {   public static void Main()   {     // formulate a string containing the details of the     // database connection     string connectionString =       "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";     // create an OleDbConnection object to connect to the     // database, passing the connection string to the constructor     OleDbConnection myOleDbConnection =       new OleDbConnection(connectionString);     // create an OleDbCommand object     OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();     // set the CommandText property of the OleDbCommand object to     // a SQL SELECT statement that retrieves a row from the emp table     myOleDbCommand.CommandText =       "SELECT empno, ename, sal "+       "FROM emp "+       "WHERE empno = 7369";     // open the database connection using the     // Open() method of the SqlConnection object     myOleDbConnection.Open();     // create an OleDbDataReader object and call the ExecuteReader()     // method of the OleDbCommand object to run the SELECT statement     OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();     // read the row from the OleDbDataReader object using     // the Read() method     myOleDbDataReader.Read();     // display the column values     Console.WriteLine("myOleDbDataReader[\" empno\"] = "+       myOleDbDataReader["empno"]);     Console.WriteLine("myOleDbDataReader[\" ename\"] = "+       myOleDbDataReader["ename"]);     Console.WriteLine("myOleDbDataReader[\" sal\"] = "+       myOleDbDataReader["sal"]);     // close the OleDbDataReader object using the Close() method     myOleDbDataReader.Close();     // close the OleDbConnection object using the Close() method     myOleDbConnection.Close();   } } 
end example

The output from this program is as follows:

 myOleDbDataReader["empno"] = 7369 myOleDbDataReader["ename"] = SMITH myOleDbDataReader["sal"] = 800 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net