Advanced data access using ADO.NET and Oracle

Bill Hamilton

Scope of application:

Microsoft ADO.NET 1.1

Oracle data type

Summary: Learn how to retrieve data from complex Oracle data types using ADO.NET 1.1.

introduction

.NET Framework Data Provider for Oracle does not always easily retrieve data stored in a complicated manner. This article will discuss how to access the data in the Oracle specific data type listed in the following table:

Data Type Description Any This data type is used to explicitly define data types that can store any type of data. BFile points to a pointer to an operating system file containing unstructured binary data (outside the Oracle database). Interval This data type is used to specify time intervals. LOB This data type is used to store non-structured binary data. RAW This data type is used to store binary data not interpreted by Oracle. Ref cursor points to the PL / SQL pointer of the SQL cursor for returning a row in the database. RowID This data type is used to represent a specific address of a row in the database. TimeStamp This data type is used to extend the functionality of the DATE data type. URI This data type is used to store a unified resource identifier (URI) that references a particular part in a document or document. User-defined is based on the basic Oracle data type and the complex data type created by the user-defined data type. XMLTYPE This data type is used to store XML data.

System.Data.OracleClient Namespace contains the type of Oracle data source for accessing the .NET Framework Data Provider for Oracle. In addition to Oracle-specific DataRead, DataAdapter, Command, and Connection classes, this namespace also contains some types to support Oracle data types and simplify the use of Oracle data types. The following table lists these types:

Type Description Oraclebfile is used to represent an Oracle BFile data type class. Oraclebinary is used to represent the structure of the binary data stream of variable length. OracleBoolean is used to represent the structure of the value returned from the database between the Oracle data type. OracleDateTime is used to represent the structure of the date and time value from January 1, 4712 BC. ORACLOB is used to represent a class of large binary objects (LOBs) stored in the Oracle database. OracleMonthspan is used to store the time interval expressed in the month and represent the structure of the Oracle Interval Year TO MONTH data type. OracleNumber is used to indicate a structure in which a fixed precision and range of -10-38 - 1 to 1038 - 1 is stored in the Oracle database. ORACLESTRING is used to represent the structure of a variable length character stream stored in the Oracle database. OracleTimeSpan is used to store the interval and represent the structure of the Oracle Interval Day To Second Data Type. OracletypeOracle data type enumeration

For each data type, the following code example shows how to create this data type in the table, how to populate each data type column, and how to access these data.

Back to top

Any

Oracle 9i introduces three data types that store universal data (ie any known data types or unnamed data types). These data types are:

•

Sys.Anytype

Any SQL type of SQL types including objects and collection types.

•

Sys.Anydata

An example of a given type and an instructions for this type. AnyData can contain different data types in different rows.

•

Sys.anydataset

A description of a set of data and data types.

The following code will create a table that contains anydata columns:

OracleCommand cmd = conn.createCommand (); cmd.commandtext = "CREATE TABLE MYANYTABLE (ID Number,

AnyDataCol sys.anydata) ";

cmd.executenonquery ();

AnyData contains a set of static functions that allow you to create an AnyData type by clearing the parameters and returns the parameters as the ANYDATA data type value:

ConvertNumber (Num in Number)

ConvertDate (Dat in Date)

Convertchar (c in char)

Convertvarchar (c in varchar)

Convertvarchar2 (c in varchar2)

Convertraw (R in Raw)

Convertblob (B IN blob)

ConvertClob (C IN Clob)

Convertbfile (b in bfile)

ConvertObject (Obj in ")

ConvertRef (RF in ")

ConvertCollection (N in "Collection_1")

The following code will insert two lines in the table, that is, insert VARCHAR2 in the ANYDATA column of the first row, insert Number in the ANYDATA column of the second row:

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "Insert Into MyanyTable (ID, Anydatacol) VALUES

(1, sys.anydata.convertvarchar2 ('test string')) ";

cmd.executenonquery ();

cmd.commandtext = "Insert Into MyanyTable (ID, Anydatacol) VALUES

(2, sys.anydata.convertnumber (100)) ";

cmd.executenonquery ();

CONN.CLOSE ();

Anytype type GetTypename function returns anytype corresponding to data actually stored in the AnyData field. The following code will use GetTypename to retrieve data types in the AnyData column in the inserted two rows:

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand

"SELECT E.Nydatacol.gettypename () from myanytable e", conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

While (Dr.Read ())

Console.writeline (Dr.getstring (0));

Dr.close ();

CONN.CLOSE ();

The console output is:

Sys.varchar2

Sys.Number

AnyData contains a set of member functions that allow you to retrieve anydata data as the value of the appropriate data type:

GetNumber (Self in Anydata, Num Out Nocopy Number) GetDate (Self In AnyData, Dat Out NoPy Date)

GetChar (Self in Anydata, C Out NoCopy Char)

Getvarchar (Self in Anydata, C Out NoCopy Varchar)

Getvarchar2 (Self in Anydata, C out no copy varchar2)

Getraw (Self in AnyData, R Out Nocopy RAW)

GetBlob (Self in Anydata, B Out NoCopy Blob)

GetClob (Self in Anydata, C Out NoCopy Clob)

GetBFile (Self in Anydata, B Out NoCopy Bfile)

GetObject (Self in Anydata, Obj Out NoCopy "")

GetRef (Self in Anydata, Rf Out Nocopy "")

GetCollection (Self in coliadata, out no copy "")

The following functions allow you to query the AnyData column and return the results of each row in Varchar2:

Create or Replace Function GetAnydata (Data in Sys.Anydata)

Return varcha2

AS

l_varchar2 varchar2 (4000);

l_rc number;

Begin

Case Data.gettypename

When 'sys.number' Then

L_rc: = DATA.GETNUMBER (L_VARCHAR2);

When 'sys.varchar2' Then

L_rc: = DATA.GETVARCHAR2 (L_VARCHAR2);

Else

l_varchar2: = 'Error: unknown data type';

End Case;

Return L_Varchar2;

END;

This function can be extended to support other native data types and user-defined data types. The following code will use the function to return the value of two lines in the table.

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new OracleCommand ("SELECT ID,

E.Anydatacol.gettypename (), getanydata (anydatacol) DataValue

From myanytable e ", conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

While (Dr.Read ())

Console.Writeline (Dr.Getint32 (0) "; Dr.getstring (1) "; "

Dr.getstring (2));

Dr.close ();

CONN.CLOSE ();

The console output is:

1; sys.varchar2; test string

Sys.Number; 100

You can also store instances of user-defined data types in the AnyType field. The following code will create a user-defined type EMPLOYEE_T: OracleCommand cmd = conn.createCommand ();

cmd.commandtext = "Create or Replace Type Employee_t is Object

(Name VARCHAR (50), Yearsservice Integer ";

cmd.executenonquery ();

You can use the following code to insert the value of the Employee_t data type in the table:

cmd.commandtext = "Insert Into MyanyTable (ID, Anydatacol) VALUES

(3, sys.anydata.convertObject (Employee_t ('Bill Hamilton', 5)) ";

cmd.executenonquery ();

The GetTypename function will return data types in the format of . , such as myschema.employee_t. You can use access to user-defined data types (discussed later) to extend the stored procedure to access the contents of the AnyType field containing the user-defined type.

Back to top

Bfile

The BFile data type is a reference to binary data other than the database stored in the operating system file. Its maximum is 4GB. The data stored in these columns is read-only.

The following code will create a table containing the BFile data type column:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyBFileTable (ID Number, Bfilecol Bfile);

cmd.executenonquery ();

The following code will create a Directory and install the sh_analz.sql file (installed with Oracle 9i) into the BFile data type. Accounts that run this code need to have Create Any Directory permissions:

INT ID = 1;

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = @ "Create or Replace Directory MybfileDir AS

'C: / oracle / ora92 / demo / schema / sales_history'

cmd.executenonquery ();

cmd.commandtext = "Insert Into MybfileTable Values

(" ID ", BFileName ('mybfiledir', 'sh_analz.sql') ";

cmd.executenonquery ();

The following code will retrieve BFile and save its content to a string:

INT ID = 1;

Byte [] bfile = NULL;

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

CMD.comMandtext = "SELECT * from mybfiletable where id =" id; OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

Oraclebfile BF = Dr.GETORACLEBFILE (1);

Bfile = new byte [bf.length];

Bf.read (bfile, 0, bfile.length);

bf.close ();

}

Dr.close ();

CONN.CLOSE ();

Utf7encoding utf = new utf7encoding ();

String s = utf.getstring (bfile);

Back to top

Interval

The Interval data type is used to specify the time interval. There are two types of Interval data types:

•

Interval day to second

The fixed size of this data type is 11 bytes, which stores time intervals indicated by days, hours, minutes, and seconds.

•

Interval year to Month

The fixed size of this data type is 5 bytes, which stores the time interval expressed in the year and month.

The following code will create a table containing the Interval Day To Second and Interval Year To Month Data Type:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyIntervalTable (ID Number,

Intervalmcol Interval year to Month,

Intervaldtscol interval day to second ";

cmd.executenonquery ();

The following code will use the OracleMonthspan and the ORACLetimeSPAN structure to insert a line containing time intervals:

OracleDataAdapter Da = New OracleDataAdapter

"SELECT ID, Intervalmcol, Intervaldtscol from MyInterValtable",

ConnectString);

DataTable DT = New DataTable ();

// Get the architecture

Da.fillschema (DT, Schematype.Source);

OracleCommandbuilder CB = New OracleCommandBuilder (DA);

INT ID = 1;

// 1 year, 2 months

OracleMonthspan Intervalmcol = New OracleMonthspan (1, 2);

// 1 day, 2 hours, 3 minutes, 4 seconds, 5 ms

OracleTimeSpan Intervaldtscol = New OracleTimeSpan (1, 2, 3, 4, 5);

// Create a row that contains the data

DataRow Row = DT.NEWROW ();

ROW ["ID"] = ID;

Row ["Intervalmcol"] = intervaltmcol.value;

ROW ["Intervaldtscol"] = intervaldtscol.value;

Dt.Rows.Add (Row);

Da.UPDATE (DT);

The following code will retrieve the time interval data using the accesser method of the GetOracleMonthspan () and getoracletimeSpan () types: int ID = 1;

OracleMonthspan Intervalmcol;

OracleTimeSpan Intervaldtscol;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new OracleCommand ("Select Intervalmcol,

Intervaldtscol from MyInterValTable where id = " id, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

Intervalmcol = Dr.GetoracleMonthspan (0);

Intervaldtscol = Dr.GETORACLETIMESPAN (1);

Console.writeline (Intervalmcol.toString () ";

Intervaldtscol.tostring ());

}

Dr.close ();

CONN.CLOSE ();

The console output is:

14; 1.02: 03: 04.0050000

Back to top

Lob

Oracle LOB data types can store up to 4GB of unstructured data (eg, multimedia content and text). LOB allows random and segmentation access data. Oracle recommends using the LOB type in the new application without using the long Raw type. Unlike long Raw, a table can contain multiple LOB columns. LOB data types include:

•

BLOB

Store binary data.

•

Clob

Store single-byte database character set data, based on the default server character set.

•

NClob

Store unicode character data to base in a database of global character sets.

The following code will create a table containing BLOB, Clob, and Nclob:

OracleCommand cmd = conn.createcommand ();

CMD.Commandtext = "Create Table Myblobtable (ID Number, Blobcol Blob,

Clobcol Clob, Nclobcol NClob)

cmd.executenonquery ();

The following code Inserts a user selected file into the BLOB column and inserts some test text into the Clob and NClob columns:

OpenFileDialog OFD = New OpenFiledialog ();

IF (OFD.SHOWDIALOG () == DialogResult.ok)

{

FileStream Fs = New fileStream (OFD.FileName, Filemode.Open,

FileAccess.read;

Byte [] blob = new byte [fs.length];

fs.read (blob, 0, blob.length);

fs.close ();

OracleDataAdapter Da = New OracleDataAdapter ("

Select ID, blobcol, clobcol, nclobcol from myblobtable,

CNECTSTRING; DATATABLE DT = New DataTable ();

// Get the architecture

Da.fillschema (DT, Schematype.Source);

OracleCommandbuilder CB = New OracleCommandBuilder (DA);

INT ID = 1;

String Clob = "Test Clob Text";

String nclob = "Test NClob Text";

// Create a row that contains the data

DataRow Row = DT.NEWROW ();

ROW ["ID"] = ID;

Row ["BLOBCOL"] = BLOB;

ROW ["Clobcol"] = Clob;

Row ["Nclobcol"] = NCLOB;

Dt.Rows.Add (Row);

// Update form

Da.UPDATE (DT);

}

The following code will retrieve the LOB data:

INT ID = 1;

Oraclelob Blob = NULL;

String Clob = "";

String nclob = "";

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand

"Select Blobcol, Clobcol, Nclobcol from myblobtable where id =" id,

CONN);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

IF (! Dr.Indbnull (0))

BLOB = Dr.GETORACLOB (0);

MemoryStream MS = New MemoryStream ((byte []) blob.value);

/ / Execute some operations on the data stream

Ms.close ();

IF (! Dr.isdbnull (1))

Clob = Dr.GETORACLOB (1) .Value.toString ();

IF (! Dr.isdbnull (2))

NClob = Dr.GETORACLOB (2) .Value.toString ();

Console.writeLine ("blob length =" blob.length ";"

Clob.toString () ";" nclob.toString ());

}

Dr.close ();

CONN.CLOSE ();

The console output is:

BLOB Length = 1171; Test Clob Text; Test Nclob Text

Back to top

Raw

Binary data or other data that stores the variable lengths of the interpretation. Oracle does not appear like processing varchar2 to explain these RAW data, for example, does not convert character sets or fill blank.

The RAW data type is provided with existing applications, in the new application, you should use the LOB and BFILE data types.

There are two types of RAW types:

•

Raw

This length must be specified when the maximum length of VARCHAR2 is the same (32767 bytes), and this length must be specified when the variable is declared.

•

Long Raw

The binary data of variable length, the maximum length is 2GB. A LONG RAW data type is allowed in a table. The following code will create a table that contains RAW and Long Raw columns:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyRawTable (ID Number, Rawcol Raw (50), LongRawcol Long Raw";

cmd.executenonquery ();

The following code will create a row and insert the data into the RAW field: Insert the byte array in the RAW field, and insert the user selected file in the long Raw field:

OpenFileDialog OFD = New OpenFiledialog ();

IF (OFD.SHOWDIALOG () == DialogResult.ok)

{

// Create RAW

BYTE [] RAW = New Byte [50];

For (Byte I = 0; i

RAW [I] = i;

// Create longraw

FILESTREAM FS = New FileStream (OFD.FileName,

Filemode.open, FileAccess.read;

BYTE [] longraw = new byte [fs.length];

fs.read (longraw, 0, raw.length);

fs.close ();

OracleDataAdapter Da = New OracleDataAdapter

"SELECT ID, RAWCOL, longrawcol from myrawtable", connectstring;

DataTable DT = New DataTable ();

// Get the architecture

Da.fillschema (DT, Schematype.Source);

OracleCommandbuilder CB = New OracleCommandBuilder (DA);

INT ID = 1;

// Create a row that contains the data

DataRow Row = DT.NEWROW ();

ROW ["ID"] = ID;

Row ["Rawcol"] = RAW;

Row ["LongRawcol"] = longraw;

Dt.Rows.Add (Row);

// Update form

Da.UPDATE (DT);

}

The following code retrieves the data in the RAW and Long Raw fields into the Oraclebinary structure:

INT ID = 1;

Oraclebinary Raw = NULL;

Oraclebinary longraw = NULL;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand

"SELECT RAWCOL, LongRawcol from MyRawTable where id =" ID, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

IF (! Dr.Indbnull (0))

RAW = Dr.GETORACLEBINARY (0);

IF (! Dr.Isdbnull (1)) longraw = Dr.GETORACLEBINARY (1);

}

Dr.close ();

CONN.CLOSE ();

Back to top

Ref cursor

This data type points to the result set returned by the PL / SQL query. The REF CURSOR is used as an output parameter, which is responsible for returning the result set from the Oracle structured program to the calling application. The cursor can be moved in the line to return multiple lines.

Use the REF CURSOR output parameter to return the result set from the Oracle structured program to the calling application.

The following packets and packets are returned to the HR.Employees table containing the result set of all employees in the HR.employees table. The packet is defined as follows:

Create Or Replace Package Get_Employees AS

TYPE T_CURSOR IS Ref Cursor;

Procedure getEmployees (cur_employees out t_cursor);

End get_employees;

The data package text defines a separate process that retrieves all the data of all employees and returns as a REF CURSOR output parameter:

Create Or Replace Package Body Get_Employees AS

Procedure getEmployees (cur_employees out t_cursor)

IS

Begin

Open cur_employees for

Select * from Employees;

End geteMployees;

End get_employees;

The following code shows how to use OracleDataReader to retrieve the result set returned by the stored procedure getEmployees.

// Create a connection

OracleConnection Conn = New OracleConnection (ConnectString);

/ / Create a command for the stored procedure

OracleCommand cmd = new oracleCommand ();

cmd.connection = conn;

cmd.commandtext = "get_employees.getemployees";

cmd.commandtype = commandtype.storedProcedure;

// Add the Ref Cursor parameter to retrieve the result set

Cmd.Parameters.Add ("cur_employees", oracletype.cursor) .direction =

ParameterDirection.output;

// Open the connection and create DataReader

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

/ / Output employee ID, name, and email address (the first four fields)

/ / And turn off the connection.

While (Dr.Read ())

{

For (int i = 0; i <= 3; i )

Console.write (DR [i] .tostring () ";");

Console.writeLine ();

}

CONN.CLOSE ();

The console output will list the ID, name, and email address of all employees. The top five behaviors:

STEVEN;

NEENA; kochhar; nkochhar;

102; Lex; de Haan; Ldehaan

Alexander; Hunold; Ahunold;

Bruce; ernst;

Back to top

RowID

Oracle uses this data type to store the unique address of each line in the database. RowID types include:

•

RowID

10-byte length data types are used to represent pages, records, and offset addresses in the database. Its value represents the actual display binary data in bbbbbbbb.ssss.ffff format, where:

•

Bbbbbbb is block ID

•

SSSS is the sequence in the block

•

FFFF is the file ID

•

UroWID

Universal RowID (Urowid), it supports logic and physical ROWID, and ROWIDs of external tables (including non-Oracle forms). The maximum length of the uroD is 4000 bytes.

•

Rowid pseudo column

Each table has a pseudo column named RowID, which allows you to use the reserved word RowID as the column name to access the address of the intend. The RowID pseudo column is not stored in the database, does not occupy any space, and cannot be modified or deleted. As long as there is a line in the form, you can't modify the RowID of the line, and RowID provides the fastest way to access the table.

The following code will create a table with RowID and urowid columns:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MYROWIDTABLE (ID Number, Rowidcol Rowid,

Urowidcol urowid ";

cmd.executenonquery ();

The following code will create a record of the RowId pseudo column of the first line in the Hr.employees form in the RowID and Urowid fields:

INT ID = 1;

Oraclestring rowid = NULL;

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

/ / Get the value of the RowId pseudo column in Hr.employees

OracleCommand cmd =

New OracleCommand ("Select Rowid from Hr.employees", CONN);

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

RowId = Dr.Getoraclestring (0);

CONN.CLOSE ();

// Store the value of the RowID pseudo column to the RowID and urowid fields

OracleDataAdapter Da = New OracleDataAdapter ("SELECT ID, ROWIDCOL,

Urowidcol from myrowidtable ", connectstring);

DataTable DT = New DataTable ();

Da.fillschema (DT, Schematype.Source);

OracleCommandbuilder CB = New OracleCommandBuilder (DA);

DataRow Row = DT.NEWROW ();

ROW ["ID"] = ID;

Row ["Rowidcol"] = ROWID;

Row ["" "= rowid;

Dt.Rows.Add (Row);

Da.UPDATE (DT);

The following code will retrieve the values ​​in the RowID and UroWID columns, and then retrieve the first line in the Hr.employees form using the retrieved value:

INT ID = 1;

Oraclestring rowid = NULL;

Oraclestring urowid = null; // Get the value of RowID and uroDID

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new OracleCommand ("SELECT ROWIDCOL, Urowidcol from

Myrowidtable where id = " id, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

RowId = Dr.Getoraclestring (0);

Urowid = Dr.GETORACLESTRING (1);

Console.writeline (Rowid ";" urowid);

}

Int EmployeeID;

String dimstname;

String lastname;

// Get a line from the Employees form

CMD = New OracleCommand ("SELECT * from Employees Where RowId = '"

Rowid.Value "'", conn);

DR = cmd.executeReader ();

IF (Dr.Read ())

{

EmployeeID = Dr.Getint32 (0);

FirstName = Dr.getstring (1);

Lastname = Dr.getstring (2);

Console.writeLine (Employeeid.toString () ";" firstname ";"

Lastname);

}

CONN.CLOSE ();

The console output is:

Aaahm7aafaaaabwaaa; Aaahm7aafaaaabwaaa

STEVEN; KING

Back to top

Timestamp

The data type used to represent the date / time value. This data type adds a decimal second to the DATE type to increase accuracy. The TimeStamp data type uses the accuracy parameter to define the accuracy of the data stored in the second field, the accuracy value ranges from 0 to 9, the default value is 6. There are three typesTAMP data types:

•

Timestamp

Used to indicate the variable size value of the date / time, the size range from 7 bytes to 11 bytes.

•

TimeStamp with Time Zone

13 bytes of fixed size values, including time zones in addition to date / time. You can set the time zone with the time zone name between the UK offset (between the current time between the British Greenwich Zero Morning, or Greenwich Time [GMT]) or the time zone name in the V $ Timezone_Names table. You can use the following query to get a list of valid zones:

Select Tzname, Tzabbrev from V $ TIMEZONE_NAMES

•

TimeStamp with local time

The value of TimeStamp is similar to the value of TimeStamp WITH TIME ZONE, but the data is standardized into the time zone of the database server. This data type allows you to adjust the date / time to explain the time zone difference, including whether the time zone complies with the summer time. UTC offset will not be stored.

The following code will create a table containing TimeStamp, TimeStamp With Timezone and TimeStamp With Local Time Zone:

OracleCommand cmd = conn.createCommand (); cmd.commandtext = "CREATE TABLE MyTimeStamptable (ID Number,

TimeStampcol TimeStamp, TimeStamptzcol TimeStamp with Time Zone,

TimeStampltzcol TimeStamp with Local Time Zone

cmd.executenonquery ();

The following code plugs the current time into the column of each TimeStamp data type:

INT ID = 1;

DateTime TimeStampcol = datetime.now;

DateTime TimeStamptzcol = datetime.now;

DateTime TimeStampltzcol = datetime.now;

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "Insert INTO MyTimeStamptable Values ​​(" ID ", '"

TimeStampCol.Tostring ("DD MMM YYYY HH: MM: SSTT") "','"

TimeStamptzcol.Tostring ("DD MMM YYYY HH: MM: SSTT") "','"

TimeStampltzcol.Tostring ("DD MMM YYYY HH: MM: SSTT") ")"

cmd.executenonquery ();

CONN.CLOSE ();

The following code will retrieve the values ​​of three TimeStamp data types:

INT ID = 1;

DateTime TimeStampcol;

DateTime TimeStamptzcol;

DateTime TimeStampltzcol;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand ("SELECT TIMESTAMPCOL,

TimeStamptzcol, TimeStamPLTZCOL from MyTimeStamptable where id = "

ID, conn);

Cn.open ();

// Get the TimeStamp value

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

TimeStampCol = Dr.GetdateTime (0);

TimeStamptzcol = Dr.getdateTime (1);

TimeStampltzcol = Dr.GetdateTime (2);

Console.writeline (TimeStampcol.tostring () ";

TimeStamptzcol.toTostring () ";"

Timestampltzcol.toTString ());

}

Dr.close ();

Conn.Close (); console output is:

7/9/2004 1:43:31 PM; 7/9/2004 1:43:31 PM; 7/9/2004 10:43:31 AM

Back to top

URITYPE

URITYPE Data Type Storage Pointer to the Internal or External Uniform Resource Identifier (URI) resource, which can reference a specific part of a document or document. Derive the following subtypes from the general URITYPE data type:

•

Httpuritype

Use the UHC Transport Protocol (HTTP) to access the URL of the web page or file.

•

DBURITYPE

Quote for data in the database (single line, multi-line or single column). You can use the member function to access the data, and the syntax of these member functions is similar to the syntax of XPath.

•

XDBuritype

Store a reference to an XML document, which is stored in the Oracle XML DB Knowledge Base in the database.

You can create columns using uritype or a particular subtype.

URITYPE contains the following functions that can be used for any subtype:

Function Description Createuri (URI In Varchar2) Constructs a URITYPE subtype. Each subtype has its own constructor: DBURITY DBURITYPE () httpuritype httpuritype () xdburitype xdburitype () getBlob () Returns the URL pointing to the BLOB. getClob () Returns the value pointing to the URL as the CLOB encoded using the database character set. getContentType () Returns MIME information of the URL. getExternalURL () similar to getURL (), just it escapes characters in the URL. getURL () returns the URL stored in the data type. getXML () Returns the XMLTYPE object of the URI, and the URI must point to a valid XML document.

The following code will create a table that contains the URITYPE, HTTPURITYPE, and DBURITYPE:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyurityPetable (ID Number,

URITYPECOL SYS.URitype, httpuritypecol sys.httpuritype,

DBURITYPECOL SYS.DBURITYPE "";

cmd.executenonquery ();

The following code will insert a data line in the table. This trip contains a reference to the employee of employee_id = 100 in the URL of the MSDN article and the employee of Employee_ID = 100 in the Hr.employees table:

INT ID = 1;

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "Insert Into MyurityPetable Values" ID

", Sys.urifactory.geturi ('http://msdn.microsoft.com/data/default.aspx?

PULL = / library / en-us / DNADONET / HTML / MSDNORSPS.ASP ') "

", Sys.httpuritype ('msdn.microsoft.com/data/default.aspx?

Pull = / library / en-us / DNADONET / HTML / MSDNORSPS.ASP ') " ", sys.dburitype (' / hr / employees / row [Employee_ID = '' 100 ''] / email ',

NULL) ";

cmd.executenonquery ();

CONN.CLOSE ();

The following code will retrieve three URITYPES's URLs and the contents of HttPurityPE and DBURITYPE as Clob:

INT ID = 1;

String uritypecol;

String httpuritypecol;

String DBURITYPECOL;

Oracleob httpuritypecolclob;

Oraclelob DBURITYPECOLCLOB;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand ("Select E.URITYPECOL.GETURL (),

E.httpuritypecol.geturl (), E.DBURITYPECOL.GETURL (),

E.httpuritypecol.getClob (), E.DBURITYPECOL.GETCLOB () from FROM

Hr.MyurityPetable e where id = " ID, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

/ / Get URI

URITYPECOL = Dr.getstring (0);

Httpuritypecol = Dr.getstring (1);

DBURITYPECOL = Dr.getstring (2);

/ / Get httpuritype columns HTML

Httpuritypecolblob = Dr.GetoracleLob (3);

/ / Get the email value in hr.employees

DBURITYPECOLCLOB = Dr.Getoraclelob (4);

Console.writeline (uritypecol ";"

HTTPURITYPECOL ";" DBURITYPECOL

"; Httpuritype clob length =" httpuritypecolclob.length

"; DBURITYPECOLCLOB.VALUE);

}

Dr.close ();

CONN.CLOSE ();

The console output is:

Http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp;

Http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnadonet/html/msdnorsps.asp;

/ ORADB / HR / Employees / ROW [Employee_ID = '100'] / email;

HTTPURITYPE Clob Length = 112964;

SKING Back to top

User defined

User-defined data types include built-in data types and other data types defined by user. User-defined data types extract the structure of real world entities in application data. This type is a template for the following object: This object is identified by name and has an attribute (built-in type, or other type of user-defined type) and method.

The following code will create a user-defined type Employee_t:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE OR REPLACE TYPLOYEE_T

IS Object (Name Varchar (50), Yearsservice Integer ";

cmd.executenonquery ();

The following code will create a table containing the Employee_t data type column:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MyUserDefinedTable

(Id Number, Employee Hr.employee_t) ";

cmd.executenonquery ();

The following code will insert a line in the table:

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "Insert Into MyUserDefinedTable Values

(1, Employee_t ('Bill Hamilton', 5) ";

cmd.executenonquery ();

CONN.CLOSE ();

The following code will retrieve the data of the line:

INT ID = 1;

String Employee_name;

OracleNumber Employee_YearSservice;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand ("Select ID, E.employe.name,

E.employee.YearSservice from myUserDefinedTable e where id = " id,

CONN);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

ID = Dr.Getint32 (0);

Employee_name = Dr.getstring (1);

Employee_YearSservice = Dr.Getoraclenumber (2);

Console.writeline (id.tostring () ";" EMPLOYEE_NAME ";"

Employee_yearsservice);

}

Dr.close ();

CONN.CLOSE ();

The console output is:

1; Bill Hamilton; 5

Back to top

XMLTYPE

This data type uses a hidden Clob to store an XML document in the table. This data type contains multiple member functions, which can be used to create XML, perform XPath queries for documents, extract data dishes, verify XML, and return content. The following code will create a table containing the XMLTYPE column:

OracleCommand cmd = conn.createcommand ();

cmd.commandtext = "CREATE TABLE MYXMLTYPETABLE (ID Number,

XMLTYPECOL SYS.XMLTYPE "";

cmd.executenonquery ();

The following code will insert a row and use the XMLType's CreateXML member function to set an XMLTYPE column:

INT ID = 1;

String xmltypecol = @ " SKING ";

OracleConnection Conn = New OracleConnection (ConnectString);

Cn.open ();

OracleCommand cmd = conn.createcommand ();

cmd.comMandtext = "Insert Into MyXMLTYPETABLE VALUES (" ID

", Sys.xmltype.createxml ('" xmltypecol ")";

cmd.executenonquery ();

CONN.CLOSE ();

The following code will use the XMLType's GetStringVal member function to retrieve the value of the XMLTYPE column:

INT ID = 1;

String XMLTYPECOL;

OracleConnection Conn = New OracleConnection (ConnectString);

OracleCommand cmd = new oracleCommand

"Select sys.xmltype.getstringval (xmltypecol) from myXMLTYPETABLE WHERE

ID = " ID, conn);

Cn.open ();

OracleDataReader DR = cmd.executeReader ();

IF (Dr.Read ())

{

XMLTYPECOL = Dr.getstring (0);

Console.writeLine (XMLTYPECOL);

}

Dr.close ();

CONN.CLOSE ();

The console output is:

SKING

Back to top

in conclusion

Oracle provides a variety of data types to help you process complex data, including large objects stored in the database or outside of the database, XML, universal (ANY) data, date, and time, and user-defined aggregated data structures. Microsoft .NET Oracle Provider in Microsoft .NET Framework 1.1 provides types in the System.Data.OracleClient namespace that allows you to easily access and operate data stored in these types.

Back to top

Reference

ADO.NET cookbook

ADO.NET IN A NUTSHELL