dotConnect for Oracle Documentation
In This Topic
    Retrieving and Modifying Data
    In This Topic

    Introduction

    This tutorial describes how to use OracleCommand, OracleDataReader, and OracleDataTable components.

    Requirements

    This walkthrough supposes that you know how to connect to server, how to create the necessary objects on the server, and how to insert the data to the created tables.

    Note that if you do not use design-time (specifically, if you do not place on a designer OracleConnection component from toolbox), you have to embed license information manually. This is described in the Licensing topic.

    General Information

    As we know, an original function of any database application is establishing a connection to a data source and working with data contained in it. The .NET Framework data providers of ADO.NET serve as a bridge between an application and a data source, and allow you to execute commands as well as to retrieve data by using a DataReader or a DataAdapter. Updating data involves using the Command and DataAdapter objects; and it may also involve using transactions.

    Let's make some classification to get better understanding of ADO.NET model. There are two approaches to work with data: connected and disconnected models. You can use classes of the connected model for establishing connection and setting transactions, for fetching data and updating datasources. These classes interoperate with database directly: OracleProviderFactory, OracleConnection, OracleTransaction, OracleDataAdapter, OracleCommand, OracleParameter, and OracleDataReader.

    The objects, which represent the disconnected model of ADO.NET, don't interoperate with datasource immediately. These classes provide the ability to work off-line against your data storage: DataSet, DataTable, DataColumn, DataRow, Constraints, DataRelation, DataView, and DataRowView.

    We will use in our samples the classes from both models.

    The goal of this tutorial is to retrieve and update the data from the table dept (the appropriate DDL/DML script is located at \Program Files\Devart\dotConnect\Oracle\Samples\tables.sql - the default path of dotConnect for Oracle installation).

    OracleDataAdapter and OracleDataTable components are available for full .NET Framework only. They are not available in dotConnect for Oracle .NET Standard 1.3 compatible assemblies, because there are no base classes for them. Thus, you cannot use disconnected model classes and OracleDataAdapter in projects targeted .NET Core or other .NET platforms except for full .NET Framework.

    Retrieving and Updating Data using the Connected Model

    In this sample we are using OracleCommand and OracleDataReader to retrieve and manipulate data. For more information, refer to the description of these classes in our documentation.

    using Devart.Data.Oracle;
    ...
    class Program
    {
        void PrintDept(OracleConnection connection)
        {
            OracleCommand command = connection.CreateCommand();
            command.CommandText = "select * from dept";
            
            // Call the Close method when you are finished using the OracleDataReader 
            // to use the associated OracleConnection for any other purpose.
            // Or put the reader in the using block to call Close implicitly.
            using (OracleDataReader reader = command.ExecuteReader())
            {
                // printing the column names
                for (int i = 0; i < reader.FieldCount; i++)
                    Console.Write(reader.GetName(i).ToString() + "\t");
                Console.Write(Environment.NewLine);
                // Always call Read before accesing data
                while (reader.Read())
                {
                    // printing the table content
                    for (int i = 0; i < reader.FieldCount; i++)
                        Console.Write(reader.GetValue(i).ToString() + "\t");
                    Console.Write(Environment.NewLine);
                }
            }
        }
        
        void ModifyDept(OracleConnection connection)
        {
            OracleCommand command = connection.CreateCommand();
            command.CommandText = "UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO > 20";
            
            // return value of ExecuteNonQuery (i) is the number of rows affected by the command
            int i = command.ExecuteNonQuery();
            Console.WriteLine(Environment.NewLine + "Rows in DEPT updated: {0}", i + Environment.NewLine);
        }
        
        static void Main(string[] args)
        {
            using (OracleConnection conn 
                = new OracleConnection("User Id=Scott;Password=tiger;Data Source=Ora;"))
            {
                try
                {
                    conn.Open();
                    Program program = new Program();
                    
                    // printing out the Dept table to console
                    program.PrintDept(conn);
                    
                    // updating records in Dept
                    program.ModifyDept(conn);
                    
                    // printing out the Dept table to console
                    program.PrintDept(conn);
                }
                catch (OracleException ex)
                {
                    Console.WriteLine("Exception occurs: {0}", ex.Message);
                }
                finally
                {
                    Console.ReadLine();
                }
            }
        }
    }
    
    Imports Devart.Data.Oracle
    ...
    Module Module1
        Sub PrintDept(ByVal connection As OracleConnection)
            Dim command As OracleCommand = connection.CreateCommand()
            command.CommandText = "select * from dept"
            
            ' Call the Close method when you are finished using the OracleDataReader 
            ' to use the associated OracleConnection for any other purpose.
            ' Or put the reader in the using block to call Close implicitly.
            Using reader As OracleDataReader = command.ExecuteReader()
                ' printing the column names
                For i As Integer = 0 To reader.FieldCount - 1
                    Console.Write(reader.GetName(i).ToString() & VbCrlf)
                Next i
                Console.Write(Environment.NewLine)
                
                ' Always call Read before accesing data
                While reader.Read()
                    ' printing the table content
                    For i As Integer = 0 To reader.FieldCount - 1
                        Console.Write(reader.GetValue(i).ToString() & VbCrlf)
                    Next
                    Console.Write(Environment.NewLine)
                End While
            End Using
        End Sub
    
        Sub ModifyDept(ByVal connection As OracleConnection)
            Dim command As OracleCommand = connection.CreateCommand()
            command.CommandText = "UPDATE DEPT SET LOC='VEGAS' WHERE DEPTNO > 20"
            
            ' return value of ExecuteNonQuery (i) is the number of rows affected by the command
            Dim i As Integer = command.ExecuteNonQuery()
            Console.WriteLine(Environment.NewLine & "Rows in DEPT updated: {0}", i & Environment.NewLine)
        End Sub
    
        Sub Main()
            Using conn _
                As New OracleConnection("User Id=Scott;Password=tiger;Data Source=Ora;")
                Try
                    conn.Open()
                    
                    ' printing out the Dept table to console
                    Module1.PrintDept(conn)
                    
                    ' updating records in Dept
                    Module1.ModifyDept(conn)
                    
                    ' printing out the Dept table to console
                    Module1.PrintDept(conn)
                Catch ex As OracleException
                    Console.WriteLine("Exception occurs: {0}", ex.Message)
                Finally
                    Console.ReadLine()
                End Try
            End Using
        End Sub
    End Module
    
    

    Retrieving and Updating Data using the Disconnected Model

    Applicable only for projects targeting full .NET Framework.

    A traditional way for working with DataTable and DataSet assumes consecutive creating and initializing Connection, Command, DataAdapter, and CommandBuilder objects. Devart OracleDataTable and OracleDataSet have advanced features that make work with data easier. Even more, with our components you can retrieve and manipulate data in design time. For more information, refer to the descriptions of these classes.

    Here is a small sample that demonstrates usage of OracleDataTable.

    public void UseDataTable()
    {
       OracleDataTable myDataTable 
        = new OracleDataTable("SELECT * FROM Dept", "User Id=Scott;Password=tiger;Data Source=Ora;");
       try
       {
           // FetchAll=true means to retrieve data from server entirely when DataTable is opened.
           //  By default, FetchAll is set to false - only minimal quantity of rows is requested at once,
           //  which leads to better initial response time and less network traffic.
           myDataTable.FetchAll = true;
         
           // populating DataTable with data from data source
           myDataTable.Active = true;
         
           // modifying the third record
           myDataTable.Rows[3]["DName"] = "Researches";
         
           // Update method executes the appropriate commands (delete, insert, or update) in the data source.
           Console.WriteLine(myDataTable.Update() + " rows updated.");
           
           // printing the DataTable content
           foreach (DataRow myRow in myDataTable.Rows)
           {
              foreach (DataColumn myCol in myDataTable.Columns)
              {
                  Console.Write(myRow[myCol] + "\t");
              }
              Console.WriteLine();
           }
         }
         finally
         {
            //Active=false does not clear the data, but frees the resources allocated on the server, if any.
            myDataTable.Active = false;
         }
    }
    Public Sub UseDataTable()
        Dim myDataTable As OracleDataTable _
            As New OracleDataTable("SELECT * FROM Dept", "User Id=Scott;Password=tiger;Data Source=Ora;")
        Try
            ' FetchAll=true means to retrieve data from server entirely when DataTable is opened.
            '  By default, FetchAll is set to false - only minimal quantity of rows is requested at once,
            '  which leads to better initial response time and less network traffic.
            myDataTable.FetchAll = True
            
            ' populating DataTable with data from data source
            myDataTable.Active = True
            
            ' modifying the third record
            myDataTable.Rows(3)("DName") = "Researches"
            
            ' Update method executes the appropriate commands (delete, insert, or update) in the data source.
            Console.WriteLine(myDataTable.Update() & " rows updated.")
            Dim myRow As DataRow
            Dim myCol As DataColumn
            
            ' printing the DataTable content
            For Each myRow In myDataTable.Rows
                For Each myCol In myDataTable.Columns
                    Console.Write(myRow(myCol) & VbCrlf)
                Next myCol
                Console.WriteLine()
            Next myRow
        Finally
            ' Active=false does not clear the data, but frees the resources allocated on the server, if any.
            myDataTable.Active = False
        End Try
    End Sub
    
    

    OracleDataSet can be easily created with Devart DataSet Wizard and visually managed with Devart DataSet Manager. Please refer to our Using DataSet Wizard and Using DataSet Manager articles.

    Additional Information

    This tutorial describes only basic ways of working with data. Besides, you can take advantage of using stored procedures, typed datasets and ORM solutions. dotConnect for Oracle supports the LinqConnect and Entity Framework ORM technologies which are intended for converting data between incompatible type systems in relational databases and object-oriented programming languages. These technologies allow you to decrease the amount of code and maintenance required for data-oriented applications. For more information, please refer to Entity Framework and LinqConnect sections.

    See Also

    Using DataSet Wizard  | Using DataSet Manager  | OracleDataTable Advanced Features  | ORM Solutions