彩票走势图

    dotConnect for Oracle可以使用用户自定义类型，由于在Oracle数据库中提供机制使用类型化和非类型化的oracleobjects。

    非类型化的OracleObjects是由OracleObject类实例表示。这是一种处理Oracle对象灵活的机制，但是却不能够类型验证，同时比起使用类型化OracleObjects.而言，也不是很便捷。

    类型化oracleobject代表单一的用户定义类型，允许像普通的.NET对象一样使用实例。本文主要是讲解如何创建和使用类型化的OracleObjects。值得注意的是，类型化和无类型化的OracleObjects都可以在OCI模式下使用。

    现在需要用户定义的类型TAddress，和一张有在你的Oracle 数据库中创建的TAddress类型字段的表EmpObject，这个可以通过下面的代码实现：

CREATE TYPE TAddress AS OBJECT (
  Country VARCHAR2(30),
  City VARCHAR2(30),
  Street VARCHAR2(30),
  Apartment NUMBER
);
/

CREATE TABLE EmpObject (
  Code NUMBER PRIMARY KEY,
  Person VARCHAR2(40),
  Address TADDRESS,
  Job VARCHAR2(9)
);
/

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (1, 'SMITH', TAddress('UK', 'London', 'Street', 12), 'CLERK');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (2, 'JONES', TAddress('USA', 'New York', 'Street', 418), 'MANAGER');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (3, 'SCOTT', TAddress('CANADA', 'Ottawa', 'Street', 26),'PRESIDENT');

INSERT INTO EmpObject (Code, Person, Address, Job)
            VALUES (4, 'MARTIN', TAddress('FRANCE', 'Paris', 'Street', 162), 'ANALYST');

创建类型化OracleObjects

要创建一个类型化的OracleObject ，可以执行下列步骤：

1、启动类型化Oracle对象向导：在vs的主菜单中选择Tools -> Oracle -> Oracle Objects。

2、将连接属性设置到你创建对象类型化的服务器上，然后点击“下一步”：

注意在OCI模式下使用连接，主要是因为Oracle对象在直接模式下不可用。

3、选择你想要的类型并单击“下一步”。在此示例中， 使用的是TAddress类型：

4、设置生成的类的名称、命名空间的放置、对象的命名规则。示例中选择的是默认值。

导航将会生成用于展示TAddress对象类型的TAddress类。
使用类型化的OracleObjects

通过产生的的类型OracleObject类，Oracle UDTs的属性就可以像通常的.NET对象一样使用。下面来看看如何使用Taddress实例，代码如下所示：

C#

// Create and open a connection. Connection should not be in Direct mode,
// as in this case Oracle Objects would be unavailable.
OracleConnection connection = new OracleConnection
	("Server = Ora; User Id = Scott; Password = tiger; Direct = false;");

connection.Open();

// Create a command selecting all rows from the table EmpObject
OracleCommand command = new OracleCommand
	("select * from empobject", connection);

// By default, all Oracle Objects are retrieved by the application as instances 
// of the OracleObject class. Thus, to use typed OracleObjects, we need to set 
// the mapping from the TADDRESS Oracle UDT to Taddress class used in the application.
OracleType.GetObjectType("TAddress", connection).UdtType = typeof(TAddress);

// Execute the command, retrieve the data reader and print the table content.
OracleDataReader r = command.ExecuteReader();
while (r.Read())
{
	TAddress addr = (Taddress)r["Address"];
	Console.WriteLine
		(r["Person"].ToString() + ": " + addr.Country + ", " + 
		addr.City + ", " + addr.Street + " " + addr.Apartment);
}

[Visual Basic]

 Create and open a connection. Connection should not be in Direct mode,
' as in this case Oracle Objects are unavailable.
 Dim connection = New OracleConnection( _
	"Server = Ora; User Id = Scott; Password = tiger; Direct = false;")
connection.Open()

' Create a command selecting all rows from the table EmpObject
Dim command = New OracleCommand
	("select * from empobject", connection)

' By default, all Oracle Objects are retrieved by the application as instances
' of the OracleObject class. Thus, to use typed OracleObjects, we need to set
' the mapping from the TADDRESS Oracle UDT to Taddress class used in the application.
OracleType.GetObjectType("TAddress", connection).UdtType = GetType(TAddress)

' Execute the command, retrieve the data reader and print the table content.
Dim r As OracleDataReader = command.ExecuteReader()
While (r.Read())
	Dim addr As Taddress = r("Address")
	Console.WriteLine( _
		r("Person").ToString() + ": " + addr.Country + ", " + _
		addr.City + ", " + addr.Street + " " + addr.Apartment.ToString())
End While

    下一个示例是使用Taddress类的参数更新了EmpObject表，代码如下所示：

[C#]

// Create an UPDATE command with parameter
OracleCommand command = new OracleCommand
	("update empobject set address = :pAddress where code = 1", connection);
            
// Create and set a Taddress object
Taddress addr = new Taddress();
addr.Country = "USA";
addr.City = "New York";
addr.Street = "Street";
addr.Apartment = 418;

// Set up the command parameter. 
OracleParameter param = new OracleParameter("pAddress", OracleDbType.Object, "TAddress");
param.Value = addr;
command.Parameters.Add(param);

// Execute the command.
command.ExecuteNonQuery();

[Visual Basic]

 Create an UPDATE command with parameter
Dim command = New OracleCommand( _
	"update empobject set address = :pAddress where code = 1", connection)

' Create and set a Taddress object
Dim addr As Taddress = New Taddress()
addr.Country = "USA"
addr.City = "New York"
addr.Street = "Street"
addr.Apartment = 418

' Set up the command parameter. 
Dim param = New OracleParameter("pAddress", OracleDbType.Object, "TAddress")
param.Value = addr
command.Parameters.Add(param)

' Execute the command.
command.ExecuteNonQuery()