Connect to Oracle DB Using ADO.NET

 Performing a Oracle SELECT Statement Using ADO.NET 

  In this post we are leaning How to Connect to Oracle Database using ADO.NET. We perform a SQL SELECT statement to retrieve data from table.

 We can use the following steps to do this:

1. Create a string containing the details of the database connection.

2. Create a OracleConnection object to connect to the database, passing the connection string to the constructor.

3. Create a string containing a SELECT statement to retrieve data from table.

4. Create a OracleCommand object to hold the SELECT statement.

5. Set the CommandText property of the OracleCommand object to the SELECT string.

6. Create a OracleDataAdapter object.

7. Set the SelectCommand property of the OracleAdapter object to the OracleCommand object.

8. Create a DataSet object to store the results of the SELECT statement.

9. Open the database connection using the Open() method of the OracleConnection object.

10. Call the Fill() method of the OracleDataAdapter object to retrieve the rows from the table, storing the rows locally in a DataTable of the DataSet object.

11. Get the DataTable object from the DataSet object.

12. Display the columns for each row in the DataTable, using a DataRow object to access each row in the DataTable.

13. Close the database connection, using the Close() method of the OracleConnection object created in step 2.

Step 1: Formulate a String Containing the Details of the Database Connection
   The first step is to formulate a string containing the details of the database connection. When connecting to a Oracle database, our string must specify the following:
 ◆ The name of the database. We set this in the database part of the string. For example: data source=OracleNetServiceName.
 ◆ The name of the user to connect to the database as. We set this in the 'user id' part of the string. For example: user id=sa.
 ◆ The password for the database user. We set this in the 'password' part of the string. For example: password=sa.

  string connectionString = "data source=LearnAsp;user id=LearnAsp;
            password=LearnAsp; Pooling=true; Max pool size=200; Min pool size=0;";

Step 2: Create a OracleConnection Object to Connect to the Database.
   Create a OracleConnection object to connect to the database, passing the connection string created in the previous step to the constructor. We use an object of the OracleConnection class to connect to a Oracle Database.
 The following example creates a OracleConnection object named myOracleConnection, passing connectionString (created in the previous step) to the constructor:

 OracleConnection myConnection =
            new OracleConnection(connectionString);

Step 3: Formulate a String Containing the SELECT Statement
   Formulate a string containing the SELECT statement to retrieve or fetch data from the table.
 For example:

 string selectString = " select * from emp ";

Step 4: Create a OracleCommand Object to Hold the SELECT Statement
   Create a OracleCommand object to hold our SELECT statement. We can call the CreateCommand() method of myOracleConnection to create a new OracleCommand object for that connection. The CreateCommand() method returns a new OracleCommand object for the OracleConnection object.
 In the following example, a new OracleCommand object named myOracleCommand is set to the OracleCommand object returned by calling the CreateCommand() method of myOracleConnection:

 OracleCommand myCommand = myConnection.CreateCommand(); 

 we can then use myOracleCommand to run SQL statements or stored procedures.

Step 5: Set the CommandText Property of the OracleCommand Object to the SELECT String
   TSet the CommandText property of our OracleCommand object to the SELECT string created in step 4. The CommandText property contains the SQL statement (or the stored procedure call) we want to perform.
  In the following example, the CommandText property of myOracleCommand is set to selectString:

 myOracleCommand.CommandText = selectString;

Step 6: Create a OracleDataAdapter Object
   Create a OracleDataAdapter object. We use a OracleDataAdapter object to move information between our DataSet object and the database. We’ll see how to create a DataSet object later in step 8.
  The following example creates a OracleDataAdapter object named myOracleDataAdapter:

 OracleDataAdapter myDataAdapter = new OracleDataAdapter(); 

Step 7: Set the SelectCommand Property of the OracleAdapter Object to the SqlCommand Object
   Set the SelectCommand property of our OracleAdapter object to our SqlCommand object. In the following example, the SelectCommand property of myOracleDataAdapter is set to myOracleCommand:

 myOracleDataAdapter.SelectCommand = myOracleCommand;

 This enables we to perform the SELECT statement defined in myOracleCommand.Step 10 actually performs the SELECT statement.

Step 8: Create a DataSet Object to Store the Results of the SELECT Statement
   Create a DataSet object to store the results of the SELECT statement. We use a DataSet object to store a local copy of information retrieved from the database.
 The following example creates a DataSet object named myDataSet:

 DataSet myDataSet = new DataSet(); 

Step 9: Open the Database Connection Using the Open() Method of the OracleConnection Object
   Open the database connection using the Open() method of our OracleConnection object. The following example calls the Open() method for myOracleConnection:

 myOracleConnection.Open();

 Once we’ve opened the database connection, we can access the database.

Step 10: Call the Fill() Method of the SqlDataAdapter Object to Retrieve the Rows from the Table
   The Fill() method then creates a DataTable in the DataSet with the specified name and runs the SELECT statement. The DataTable created in our DataSet is then populated with the rows retrieved by the SELECT statement.
 The following example creates a string named dataTableName and sets it to "table_name" and then calls the Fill() method of mySqlDataAdapter, passing myDataSet and dataTableName to the method:

string dataTableName = "table_name";
            myDataAdapter.Fill(myDataSet, dataTableName);

Step 11: Get the DataTable Object from the DataSet Object
   Get the DataTable object created in the previous step from the DataSet object. We get a DataTable from our DataSet using the Tables property, passing the name of the DataTable in brackets (Customers, for example). The Tables property will then return our requested DataTable, which we can store in a new DataTable object that we declare.
 In the following example, myDataSet.Tables[dataTableName] returns the Customers DataTable created in myDataSet in the previous step, and stores the returned DataTable in myDataTable:

 DataTable myDataTable = myDataSet.Tables[dataTableName]; 

Step 12: Display the Columns for Each Row in the DataTable
   Display the columns for each row in the DataTable, using a DataRow object to access each row in the DataTable. The DataTable class defines a property named Rows that gets the collection of DataRow objects stored in a DataTable. We can use the Rows property in a foreach loop to iterate over the DataRow objects. For example:

 foreach (DataRow myDataRow in myDataTable.Rows)
 {
     Console.WriteLine("empno = " + myDataRow["empno"]);
     Console.WriteLine("Emp Name = " + myDataRow["ename"]);
     Console.WriteLine("Job = " + myDataRow["job"]);
 }

Step 13: Close the Database Connection
   The last step is to close the database connection using the Close() method of the OracleConnection object created in the second step.

 myOracleConnection.Close();

  Code: Program.cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        public static void Main()
        {
            Console.WriteLine("Program Started");
            string connectionString = "data source=LearnAsp;user id=LearnAsp;
            password=LearnAsp; Pooling=true; Max pool size=200; Min pool size=0;";
            OracleConnection myConnection =
            new OracleConnection(connectionString);
            string selectString = " select * from emp ";
            OracleCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = selectString;
            OracleDataAdapter myDataAdapter = new OracleDataAdapter();
            myDataAdapter.SelectCommand = myCommand;
            DataSet myDataSet = new DataSet();

            Console.WriteLine("DB Connection Opening...");
            myConnection.Open();
            Console.WriteLine("DB Connection Opened...");
            Console.WriteLine("Retrieving rows from the Emp table");
            string dataTableName = "Emp";
            myDataAdapter.Fill(myDataSet, dataTableName);

            DataTable myDataTable = myDataSet.Tables[dataTableName];

            foreach (DataRow myDataRow in myDataTable.Rows)
            {
                Console.WriteLine("empno = " + myDataRow["empno"]);
                Console.WriteLine("Emp Name = " + myDataRow["ename"]);
                Console.WriteLine("Job = " + myDataRow["job"]);
            }

            myConnection.Close();

            Console.ReadLine();
        }
    }
}

  Output: