【下载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来检索和操作数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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用法的小示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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;
     }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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技术，用于在关系数据库中的不兼容类型系统和面向对象编程语言之间转换数据。这些技术允许您减少面向数据应用程序所需的代码和维护量。