【下載dotConnect for Oracle最新版本】

dotConnect for Oracle(原名OraDirect.NET)建立在ADO.NET技術上，為基于Oracle數據庫的應用程序提供完整的解決方案。它為設計應用程序結構帶來了新的方法，提高工作效率，使數據庫應用程序的開發更簡便。

檢索和修改數據

• 介紹
• 要求
• 一般信息
• 使用連接的模型檢索和更新數據
• 使用斷開連接的模型檢索和更新數據
• 附加信息

介紹

本教程介紹如何使用OracleCommand、OracleDataReader和OracleDataTable組件。

要求

本教程假設您知道如何連接到服務器，如何在服務器上創建必要的對象，以及如何將數據插入到創建的表中。

請注意，如果您不使用設計時（特別是，如果您不從工具箱放置在設計器或OracleConnection組件上），則必須手動嵌入許可證信息。

一般信息

如我們所知，任何數據庫應用程序的原始功能都是建立到數據源的連接，并處理其中包含的數據。ADO.NET的.NET框架數據提供程序充當應用程序和數據源之間的橋梁，允許您執行命令以及使用DataReader或DataAdapter檢索數據。更新數據涉及到使用命令和數據適配器對象；它還可能涉及使用事務。

讓我們進行一些分類，以便更好地理解ADO.NET模型。使用數據有兩種方法：連接和斷開連接的模型。您可以使用連接模型的類來建立連接和設置事務、獲取數據和更新數據源。這些類直接與數據庫交互：oracleProviderFactory、oracleConnection、oracleTransaction、oracleDataAdapter、oracleCommand、oracleParameter和oracleDataReader。

這些對象表示ADO.NET的斷開連接的模型，不會立即與數據源進行互操作。這些類提供了脫機處理數據存儲的能力：數據集、數據表、數據列、數據行、約束、數據關系、數據視圖和數據行視圖。

我們將在示例中使用兩個模型中的類。

本教程的目標是從table dept檢索和更新數據（適當的DDL/DML腳本位于\Program Files\Devart\dotConnect\Oracle\Samples\tables.sql——用于dotConnect for Oracle的默認路徑）。

使用連接的模型檢索和更新數據

在本示例中，我們使用OracleCommand和 OracleDataReader來檢索和操作數據。

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

使用斷開連接的模型檢索和更新數據

使用數據表和數據集的傳統方法假定連續創建和初始化連接、命令、數據適配器和commandbuilder對象。Devart OracleDataTable和OracleDataset具有高級功能，可以更輕松地處理數據。更重要的是，使用我們的組件，您可以在設計時檢索和操作數據。

下面是一個演示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

使用Devart數據集向導可以輕松創建OracleDataset，并使用Devart數據集管理器進行可視化管理。

附加信息

本教程只介紹處理數據的基本方法。此外，還可以利用存儲過程、類型化數據集和ORM解決方案。Dotconnect for Oracle支持LinqConnect和實體框架ORM技術，用于在關系數據庫中的不兼容類型系統和面向對象編程語言之間轉換數據。這些技術允許您減少面向數據應用程序所需的代碼和維護量。

購買dotConnect for Oracle正版授權的朋友可以點擊""哦~~~