Chapter 12: Oracle, SQLXML, and Other .NET Data Providers

In previous chapters, we discussed the Sql, OleDb, and ODBC data providers and how to use them work with various databases including SQL Server, Access, Excel, and MySQL. As mentioned, ADO.NET is flexible enough so anyone can develop their data provider and add it to the library as an add-on.

In this chapter, you'll see some more data providers. Once you've come to grips with any one data provider, working with the rest of them is a piece of cake. All data providers implement a similar class hierarchy model with only a few changes.

Using Oracle .NET Data Providers

Besides the OleDb and ODBC providers, you can use other data providers to access an Oracle database:

• Microsoft has developed the Oracle .NET data provider (although some documentation refers to it as the Oracle managed provider.

• Oracle has developed the Oracle data provider for .NET or ODP.NET.

• CoreLab Software Development has developed the OraDirect.NET data provider.

So, why there are so many approaches to access a database? Why can't they just provide a single provider? That's the pity and beauty of software development. Users are never satisfied. No matter how good a product you develop, you'll always find somebody somewhere complaining about it. It may be a performance issue, it may lack features, or it may contain bugs.

By having the ODBC and OleDb data providers, you can leverage the existing ODBC and OLE DB technologies satisfactory to developers working with Oracle database. However, data access using OLE DB and ODBC isn't satisfactory to developers. For example, when you compare the performance of the OleDb data provider to the performance of the Sql provider.

The OleDB data provider provides data access for generic data sources and isn't designed to work with a specific database. OLE DB is a COM library, so the OleDb data provider uses COM Interop services internally to provide connectivity with Oracle databases, which causes a performance hit for each use because of moving between the managed and unmanaged spaces. On the other hand, the Oracle data provider works with Oracle databases only. Not only that, the Oracle data provider skips COM Interop services and provides database connectivity by directly using the Oracle Call Interface (OCI) as provided by Oracle client software.

Note

A recent survey, published in an article on MSDN (www.microsoft.com/indonesia/msdn/ManProOracPerf.asp), claims that the Oracle data provider could be lot faster than the OleDb data provider.

Microsoft's Oracle data provider is free and is included as part of Visual Studio .NET (VS .NET) 2003. If you're running VS .NET 1.0, you need to install it separately. You can download it from the Microsoft's download section (http://microsoft.com/downloads/). The ODP.NET provider from Oracle Corporation is also free. You can download it from Oracle's Web site at http://otn.oracle.com/tech/windows/odpnet/content.html. OraDirect.NET currently costs $99 and is available at www.crlab.com/products.html.

Caution

There may be some changes in these URLs. See the C# Corner (www.c-sharpcorner.com) Downloads section for the latest links.

In this chapter, you'll use Microsoft's Oracle .NET data provider.

Adding a Reference to the Oracle .NET Data Provider

The Oracle data provider's functionality resides in the System.Data.OracleClient.dll assembly. You can find this assembly in your installation folder's Bin folder. The default path of this assembly is C:\Program Files\Microsoft .NET\OracleClient.NET\Bin. To add a reference to this assembly, you can use the Add Reference dialog box of VS .NET, as shown in Figure 12-1.

Figure 12-1: Adding a reference to System.Data.OracleClient.dll

Note

In Microsoft VS .NET 2003, the System.Data.OracleClient namespace is already available without adding a reference to the System.Data.OracleClient.dll.

Adding a reference to System.Data.OracleClient.dll adds the System.Data.OracleClient namespace to your project. To use the Oracle data provider classes, you must add the following line to your project:

 Imports System.Data.OracleClient 

Using the Oracle .NET Data Provider Classes

The Oracle data provider class hierarchy model is similar to other data providers. As with other data providers, it also has classes for Connection, Command, DataAdapter, DataReader, Transaction, Parameter, and so on. Table 12-1 describes the Oracle data provider common classes briefly.

Table 12-1: Oracle Data Provider Classes

CLASS	DESCRIPTION
OracleConnection	Connection class
OracleCommand	Command class
OracleCommandBuilder	CommandBuilder class
OracleDataAdapter	DataAdapter class
OracleDataReader	DataReader class
OracleParameter	Parameter class
OracleParameterCollection	ParameterCollection class
OracleTransaction	Transaction class
OraclePermission	Permission class
OracleException	Exception class
OracleBFile	Represents OracleBFile objects designed to work with the Oracle Binary File BFILE data type
OracleLob	Represents a Large Object Binary (LOB) data type

Creating the Connection: The First Step

The first step to access a database is to create a connection. The OracleConnection class of the Oracle data provider is responsible for opening and closing a connection with the database.

The OracleConnection class has four properties: ConnectionString, DataSource, ServerVersion, and State. The ConnectionString represents a connection string, which can contain data source or server name, security type, user ID, and password. Like the other data provider connection classes, the OracleConnection provides Open, Close, BeginTransaction, CreateCommand, and Dispose methods.

A typical connection string looks like the following:

 "Data Source=Oracle8i;Integrated Security=yes" 

where Oracle8i is the default Oracle database server instance.

Note

The Oracle .NET data provider works for Oracle 8i and later versions of Oracle.

The following is another version of connection string with the user ID system and password manager for the database server instance MYDB:

 "Data Source=MYDB;User ID=system;Password=manager;" 

Listing 12-1 creates an OracleConnection, opens the connection, reads data, and closes the connection.

Listing 12-1: Connecting to an Oracle Database

    Dim connectionString As String = _         "Data Source=MYDB;User ID=system;Password=manager;"    Dim conn As OracleConnection = _       New OracleConnection(connectionString)    Try      If (conn.State = ConnectionState.Closed) Then          conn.Open()      End If ' Do something here      Catch exp As Exception         MessageBox.Show(exp.Message)      Finally             If (conn.State = ConnectionState.Open) Then                 conn.Close()             End If      End Try 

Note

Remember that for the ODBC .NET data provider, Oracle 8i has different connection string than Oracle 9i. When you use the Oracle .NET data provider, the same connection string format works for all Oracle databases version 8i or later.

Executing SQL Commands

In a similar manner to the other data providers (Sql, OleDb, and ODBC), the Oracle data provider's OracleCommand class represents a command object. The OracleCommand class provides the functionality to execute SQL queries directly or indirectly (through a DataAdapter).

The CommandText, CommandType, Connection, DesignTimeVisible, Parameters, Transaction, and UpdatedRowSource are OracleCommand properties. The CommandText property represents the SQL query or stored procedure to execute. The CommandType represents the type of command, which is of type System.Data.CommandType. The Connection property represents the connection associated with a command. By default OracleCommand isn't visible in the Toolbox, but if you want to make it visible and want to use it at design-time, you can set the DesignTimeVisible property to true. The OracleTransaction represents an Oracle transaction, and the UpdatedRowState property represents how the Update method should apply command results to the DataRow.

The OracleCommand methods include Cancel, Clone, CreateParameter, ExecuteNonQuery, ExecuteOracleNonQuery, ExecuteOracleScalar, ExecuteReader, and ExecuteScalar.

Apart from the ExecuteOracleNonQuery and ExecuteOracleScalar methods, other methods will look familiar to you.

Now let's create a sample application. Create a Windows application, add a reference to the Oracle .NET data provider, and import the System.Data and System.Data.OracleClient namespaces. After that, add three buttons and a DataGrid control to the form, and name the buttons InsertCmdBtn, UpdateCmdBtn, and DeleteCmdBtn. Then, we recommend you download the source code and open the project in VS .NET.

On the form's load event handler, you call the method FillDataGrid, which fills data from a database to the DataGrid control. Listing 12-2 shows the form's load event handler and the FillDataGrid method.

Listing 12-2: FillDataGrid Method

 Private Sub Form1_Load(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles MyBase.Load         FillDataGrid()     End Sub     Public Sub FillDataGrid()         conn = New OracleConnection(connectionString)         sql = "SELECT * FROM STDTABLE"         Try             ' Open the connection             If (conn.State = ConnectionState.Closed) Then                 conn.Open()             End If             ' Create a command with SELECT query             Dim cmd As OracleCommand = New OracleCommand(sql)             cmd.Connection = conn             ' Create a data adapter Dim adapter As OracleDataAdapter = _             New OracleDataAdapter(cmd)             Dim builder1 As OracleCommandBuilder = _             New OracleCommandBuilder()             ' Create and fill a DataSet             Dim ds As DataSet = New DataSet()             adapter.Fill(ds, "STDTABLE")             ' Bind DataSet with DataGrid             DataGrid1.DataSource = ds.Tables(0)             ' Close the connection         Catch exp As Exception             MessageBox.Show(exp.Message)         Finally             If (conn.State = ConnectionState.Open) Then                 conn.Close()             End If         End Try     End Sub 

In the FillDataGrid method, you create a connection and a DataAdapter, and then you call the Fill method of the DataAdapter to fill a DataSet with data from a database. You may also have noticed that you're reading data from STDTABLE. You may want to change this table name to the table that you want to access. If you want to use STDTABLE, create this table in an Oracle database with four columns named as STDNAME, STDADDRESS, STDDOB, and STDSRN of type varchar, varchar, datetime, and long, respectively.

The next step is to execute a SQL query using OracleCommand. The method ExecuteSqlQuery, shown in Listing 12-3, executes a SQL query. As you can see, the code opens a connection, creates an OracleCommand object, sets its connection, CommandText, and CommandType properties, and then calls ExecuteNonQuery method.

Listing 12-3: ExecuteSqlQuery Method

 Public Sub ExecuteSqlQuery(ByVal sql As String)     Try         conn = New OracleConnection()         conn.ConnectionString = connectionString         If (conn.State = ConnectionState.Closed) Then             conn.Open()         End If         Dim cmd As OracleCommand = New OracleCommand()         cmd.Connection = conn         cmd.CommandText = sql         cmd.CommandType = CommandType.Text         cmd.ExecuteNonQuery()     Catch exp As Exception         MessageBox.Show(exp.Message)     Finally         If (conn.State = ConnectionState.Open) Then         conn.Close()         End If     End Try End Sub 

Now let's write the code for InsertCmdBtn, UpdateCmdBtn, and DeleteCmdBtn and construct SQL INSERT, UPDATE, and DELETE queues. Once the queries are constructed, you need to call ExecuteSqlQuery. Listing 12-4 shows the code for all three buttons.

Listing 12-4: Constructing SQL INSERT, UPDATE, and DELETE Queries

 Private Sub InsertCmdBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles InsertCmdBtn.Click     sql = "INSERT INTO "     sql += " STDTABLE(STDNAME, STDADDRESS, STDSRN) "     sql += "VALUES('AName', 'Some Address', 3583)"     ExecuteSqlQuery(sql)     FillDataGrid() End Sub Private Sub UpdateCmdBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles UpdateCmdBtn.Click     sql = "UPDATE STDTABLE SET STDADDRESS = " & _     "'New Address' WHERE STDNAME = 'AName' "     ExecuteSqlQuery(sql)     FillDataGrid() End Sub Private Sub DeleteCmdBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles DeleteCmdBtn.Click     sql = "DELETE STDTABLE WHERE STDNAME = 'AName' "     ExecuteSqlQuery(sql)     FillDataGrid() End Sub 

Note

You may also notice that you call the FillDataGrid method to update the DataGrid once a query is executed.

Reading Fast Data Using a DataReader

The OracleDataReader class provides the DataReader functionality for Oracle data providers. Besides the methods to read generic data types, the OracleDataReader class also provides methods to read Oracle-specific data types. You can use all the methods of format ReadXXX to read general data types, and you can use methods that start with ReadOracleXXX to read Oracle-specific data types. GetOracleDbFile, GetOracleDateTime, GetOracleString, and GetOracleNumber are some of the Oraclespecific read methods.

The OracleDataReader has six properties: Depth, FieldCount, HasRows, IsClosed, Item, and RecordsAffected. The Depth property returns the depth of a row in a loop. The FieldCount property returns the number of columns in a row. The HasRows and IsClosed properties represent whether the reader has any rows and closed, respectively. The Item property returns the value in a native format, and the RecordsAffected property returns the number of rows affected after the execution of a SQL query.

Listing 12-5 reads data from STDTABLE and adds it to a ListBox control. To test this code, create a Windows application and add a ListBox control to the form.

Listing 12-5: Reading Data Using OracleDataReader

   Dim connectionString As String = _         "Data Source=MYDB;User ID=system;Password=manager;"         Dim conn As OracleConnection = _         New OracleConnection(connectionString)         Dim sql As String = "SELECT * FROM STDTABLE"         Try             If (conn.State = ConnectionState.Closed) Then                 conn.Open()             End If             Dim cmd As OracleCommand = New OracleCommand(sql)             cmd.Connection = conn             Dim reader As OracleDataReader = cmd.ExecuteReader()             ListBox1.Items.Add("NAME ADDRESS ")             While (reader.Read())                 Dim str As String                 str = reader.GetString(0)                 Str += ", "                 str += reader.GetString(1)                 ' Read more fields depends on your table                 ' Do something here                 ListBox1.Items.Add(str)             End While             reader.Close() Catch exp As Exception             MessageBox.Show(exp.Message)         Finally             If (conn.State = ConnectionState.Open) Then                 conn.Close()                 conn.Dispose()             End If         End Try 

Listing 12-5 used the generic data methods, but the code in Listing 12-6 demonstrates using the Oracle-specific DataReader methods. As you can see, the code uses the GetOracleString, GetOracleNumber, and GetOracleDateTime methods to read data in Oracle data types.

Listing 12-6: Using the Oracle-Specific DataReader Methods

 While (reader.Read())  Dim oraStr As OracleString  oraStr = reader.GetOracleString(0)  Dim str As String = oraStr.ToString()  oraStr = reader.GetOracleString(1)  Dim oraDtTime As OracleDateTime = _  reader.GetOracleDateTime(2)  Dim oraNum As OracleNumber = _  reader.GetOracleNumber(3) End While 

Reading and Writing Data Using a DataAdapter

The OracleDataAdapter class represents the DataAdapter for the Oracle data provider. Like the other data provider's DataAdapter classes, this class also defines the Fill and Update methods. The Fill method fills a DataSet from a data source, and the Update method saves the changes made to a DataSet back to the database.

Listing 12-7 creates an OracleDataAdapter object from a command. After that, code fills a DataSet, adds a row by using the Update method, and displays the updated data in a DataGrid control. To test this code, create a Windows application, add a DataGrid control, and add this code on the form load or a button click event handler.

Listing 12-7: Reading and Writing Data Using OracleDataAdapter

   Dim connectionString As String = _      "Data Source=MYDB;User ID=system;Password=manager;"        Dim conn As OracleConnection = _        New OracleConnection(connectionString)        Dim sql As String = "SELECT * FROM STDTABLE"        Try            ' Open the connection            If (conn.State = ConnectionState.Closed) Then                conn.Open()             End If             ' Create a command with SELECT query             Dim cmd As OracleCommand = New OracleCommand(sql)             cmd.Connection = conn             ' Create a data adapter             Dim adapter As OracleDataAdapter = _                  New OracleDataAdapter(cmd)              Dim builder1 As OracleCommandBuilder = _                  New OracleCommandBuilder()            builder1.DataAdapter = adapter             ' Create and fill a DataSet             Dim ds As DataSet = New DataSet()             adapter.Fill(ds, "STDTABLE")             Dim stdTable As DataTable = ds.Tables("STDTABLE")             ' Add a row             Dim row As DataRow = ds.Tables(0).NewRow()             row(0) = "New Name"             row(1) = "New Address"             row(2) = DateTime.Today             row(3) = "1234"             stdTable.Rows.Add(row)             adapter.Update(ds, "STDTABLE")             ' Bind DataSet with DataGrid             DataGrid1.DataSource = ds.Tables(0) Catch exp As Exception             MessageBox.Show(exp.Message)         Finally             ' Close the connection             If (conn.State = ConnectionState.Open) Then                 conn.Close()             End If         End Try 

Caution

When it comes to performance, the DataReader should be your first choice over a DataSet when reading data from a database. Don't forget to close a DataReader when you're done accessing data.

Reading BFILEs and LOBs

Reading Binary File (BFILE) and Large Object Binary (LOB) data types are two more tasks that Oracle developers don't encounter with other databases. The Oracle .NET data provider handles both of these data types wisely with the help of the OracleBFile and OracleLob classes.

The OracleLob class provides access to three Oracle data types: Blob, Clob, and NClob. The Blob data type contains binary data up to 4GB in the form of an array of bytes. The Clob data type contains character data up to 4GB in the form of a string. The NClob data type contains the character data based on the national character set of the server with up to 4GB.

The GetOracleBFile method of OracleDataReader returns an OracleBFile object. This code reads the BFILE data type from an Oracle database:

 Dim bf As OracleBFile = reader.GetOracleBFile(1) 

The GetOracleLob method of OracleDataReader returns an OracleLob object containing data. The following code uses the GetOracleLob method to read LOB data types:

 reader.Read() Dim blb As OracleLob = reader.GetOracleLob(1) Dim buffer() As Byte = New Byte(100) {} buffer(0) = 0xCC buffer(1) = 0xDD blb.Position = 0 Dim lob As OracleLob = CreateTempLob(cmd,blb.LobType) Dim actual As Long = blb.CopyTo(lob) 

Note

If you want to know more about BFILE, LOB, and other data types and related classes, see the documentation of the Oracle .NET data provider.

We could easily write more pages discussing other classes of Oracle data provider, but luckily, the documentation that comes with the Oracle data provider is full of samples. You shouldn't have to go anywhere else to find the solution for your problems.