ADO.NET OracleDataAdapter

 ADO.NET OracleDataAdapter 

 In this article we are exploring more about OracleDataAdapter class. We use an object of the OracleDataAdapter class to move rows between a DataSet object and a Oracle database. A OracleDataAdapter object allows we to synchronize our locally stored information with the database.

 Each Data Provider has its own Data Adapter in the same way that it has its own Connection, Command, and Data Reader. In below image display the relation between the data set, data adapter, and data source.

  Figure: Dataset, data adapter, and data source Connectivity 

 The data adapter constructor is overloaded. We can use any of the following to get a new data adapter. Here We are using the Oracle Database data provider, but the constructors for the other data providers are similar.

 OracleDataAdapter da = new OracleDataAdapter();
 OracleDataAdapter da = new OracleDataAdapter(cmd);
 OracleDataAdapter da = new OracleDataAdapter(sql, conn);
 OracleDataAdapter da = new OracleDataAdapter(sql, connString);

 So, we can create a data adapter in four ways:
   • use its parameterless constructor (assigning SQL string and the connectionlater).
   • pass its constructor a command (here, cmd is a SqlCommand object).
   • pass a SQL string and a connection.
   • pass a SQL string and a connection string.

  OracleDataAdapter Properties:

Sr.No	Property	Type	Description
1)	AcceptChangesDuringFill	bool	Gets or sets a value indicating whether the AcceptChanges() method is called after a DataRow object has been added, modified, or removed in a DataTable object. The default is true.
2)	DeleteCommand	OracleCommand	Gets or sets a command containing a SQL DELETE statement to remove rows from the database.
3)	InsertCommand	OracleCommand	Gets or sets a command containing a SQL INSERT statement to add rows to the database.
4)	SelectCommand	OracleCommand	Gets or sets a command containing a SQL SELECT statement to retrieve rows from the database.
5)	UpdateCommand	OracleCommand	Gets or sets a command containing a SQL UPDATE statement to modify rows in the database.

  OracleDataAdapter Methods:

Sr.No	Method	Return Type	Description
1)	Fill()	int	Synchronizes the rows in the DataSet object to match those in the data source. The int returned by this method is the number of rows synchronized in the DataSet with the database.
2)	FillSchema()	DataTable	Adds a DataTable to a DataSet object.
3)	GetFillParameters()	IDataParameter[]	Returns an array of any parameters set for the SQL SELECT statement.
4)	Update()	int	Calls the respective SQL INSERT, UPDATE, or DELETE statements (stored in the InsertCommand, UpdateCommand, and DeleteCommand properties, respectively) for each row that has been added, modified, or removed from a DataTable object. The int returned by this method is the number of rows updated

 In below program show how to use the Data Adapter to store data in dataset.

  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=10; Min pool size=2;";
            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: