如何在dotConnect for Oracle中檢索和修改數(shù)據(jù)？本文將會(huì)講到如何使用OracleCommand 、 OracleDataReader、OracleDataTable組件。

    如果說你已經(jīng)連接到Oracle服務(wù)器，在以前的文章中已經(jīng)提到過如何在服務(wù)器上創(chuàng)建對(duì)象。

    如果說你不使用設(shè)計(jì)時(shí)，就是說你沒有將來自工具箱中的OracleConnection組件放在設(shè)計(jì)器上的話，你就必須要手動(dòng)的完成許可信息。

檢索和更新的數(shù)據(jù)使用的連接模型

    在下面的例子中，將會(huì)使用OracleCommand和OracleDataReader來檢索和操作數(shù)據(jù)。

[C#]

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;Server=ORA;Persist Security Info=True;"))
        {
            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();
            }
        }
    }
}

[Visual Basic]

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;Server=ORA;Persist Security Info=True;")
            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

使用Disconnected模式檢索和更新數(shù)據(jù)

    對(duì)于數(shù)據(jù)表格和數(shù)據(jù)集有一個(gè)傳統(tǒng)的方法，來創(chuàng)建和初始化連接、命令、數(shù)據(jù)更新和命令創(chuàng)建器。下面是演示了使用oracledatatable的一個(gè)例子：

[C#]

public void UseDataTable()
{
   OracleDataTable myDataTable 
    = new OracleDataTable("SELECT * FROM Dept", "User Id=scott;Password=tiger;Server=ORA;Persist Security Info=True;");
   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;
     }
}

[Visual Basic]

Public Sub UseDataTable()
    Dim myDataTable As OracleDataTable _
        As New OracleDataTable("SELECT * FROM Dept", "User Id=scott;Password=tiger;Server=ORA;Persist Security Info=True;")
    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可以輕松創(chuàng)建數(shù)據(jù)集向?qū)В约笆褂肈evart數(shù)據(jù)集管理器實(shí)現(xiàn)可視化的管理。