In this post, I would like to talk about how to perform the same basic database programming that .NET developers quickly become familiar with in Microsoft SQL Server on an Oracle database.

Currently, I use both the Oracle Data Access Components and Developer Tools for .NET, as well as, Oracle SQL Developer (Oracle’s Java-based database IDE) when working with Oracle databases. I find that Oracle’s SQL Developer, in comparison to the developer tools which are integrated into the Visual Studio IDE, provides the advanced database tools such as schema comparison and richer view of the database objects across multiple schemas when necessary. But, for those times when you are primarily writing .NET code and PL/SQL scripts, then the developer tools are a big plus because you can perform all of these tasks directly within the Visual Studio IDE.

So here’s the skinny…you have a table, a sequence, and a package, which contains stored procedures.

Although it was deprecated with the release of .NET 4.0, I am currently still using the System.Data.OracleClient assembly because, well, it’s hard to sell refactoring on something that is working…but that’s another topic.

PL/SQL (Oracle) and T-SQL (MS SQL Server) share most of the same syntax for normal CRUD operations. However, where the two begin to differ is with built-in functions, types, and other things. So the database code for our CRUD operations should be readable, except for when we implement those objects foreign to T-SQL such as the sequence.

The Database Table

create table "XNODE"."BOOKS"
{
    "BOOKID"        NUMBER NOT NULL,
    "TITLE"        VARCHAR2(200 BYTE),
    "CREATEDON"    DATE DEFAULT SYSDATE,
    "ISACTIVE"    NUMBER DEFAULT 1
}

The primary differences between this create table script and one of the same written in T-SQL are the types. The focus of this post is not DBA level so I will not go into detail on database design, but I’ll mention that in a practice, you may not use such large data types for some of the fields.

The Sequence

In case you didn’t notice, there isn’t an identity specification as we would normally find on an ID field in T-SQL. This is because Oracle uses a different method to provide auto-generated or should I say quasi-auto-generated and quasi-sequential numbers called sequences. A sequence is an object that increments in value each time that it’s “nextval()” function is called. The sequence then returns the new value and finally stores it as the last number used. While you may choose your own convention, sequences do not directly link to particular tables. They are independent objects, thus the id’s for multiple tables can be populated by one sequence. 

create sequence "XNODE"."SEQ_BOOKS"
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 1
CACHE 20
ORDER
NOCYCLE;

You also have freedom with the naming convention that you choose however, remember that Oracle has a thirty-character limit on object names. I typically use the “SEQ_” prefix and the corresponding table name when creating sequences.

The Stored Procedure Inside The Package

PL/SQL (Oracle) stored procedures appear almost identical to those of SQL Server except that in Oracle result sets are returned via cursors. Packages are like containers for database objects. Packages consist of a package specification and a package body which from a programming perspective, may resemble C++ class definitions with function signatures and bodies. This is because all procedures that are defined in the package specification, must be implemented in the package body. In addition, the signatures of the procedures in the specification and the implementations in the body must be identical in order for the package to compile. Packages are convenient because instead of managing all of your stored procedures, functions, and types independently, you can package them and simply manage the package. Packages can also boost performance as when one method on the package is called, the entire package is compiled and stored in memory so a subsequent call to the same procedure or even a different procedure will not require compilation. On the downside, whenever the package is modified and has to be updated, it is locked for application access so modifications should be scheduled during off-hours for large applications. The following package contains a type definition and two procedure specifications and implementations for our books table from earlier.

create or replace PACKAGE BOOKAPP AS

    TYPE t_cursor is ref cursor;

    PROCEDURE BOOKS_FETCH(v_results out t_cursor);
    PROCEDURE BOOKS_ADD(v_title in varchar2);

END BOOKAPP

As mentioned earlier, first there is simply the specification. Again, the package name is at your discretion and because result sets must be returned inside of a cursor, a variable of type ref cursor is defined in the specification. In the package, we have two signatures, one for a procedure which will fetch all records from the books table and one that will simply add a new record to the table. Now for the package body.

create or replace PACKAGE BODY BOOKAPP AS

PROCEDURE BOOKS_FETCH(v_results out t_cursor)

    IS

        v_cursor t_cursor;

    BEGIN

        OPEN v_cursor FOR
        SELECT * FROM XNODE.BOOKS;

    v_results := v_cursor;

END BOOKS_FETCH;

PROCEDURE BOOKS_ADD(v_title in varchar2)
    IS
    BEGIN
    
        INSERT INTO XNODE.BOOKS(bookid, title)
        VALUES(XNODE.SEQ_BOOKS.nextval, v_title);
    
    COMMIT;

END BOOKS_ADD

END BOOKAPP

Spacing was added to the package body above for readability. As you can see, the first procedure implements the first signature by declaring a variable of our package type (cursor) then we have to “open” the cursor and select records into it. Then the output variable is set to the results and returned. The second procedure implements the “Books_Add” signature. Because the table definition has two fields that have defaults that are acceptable, we need only insert the bookid which is generated from the sequence and the title which is passed in from the call to the package. Finally, there is a call to commit the changes made. You will want to call commit on inserts and updates in order to have your changes committed to the database immediately.

And now finally, something familiar. For simplicity, I’ve marked up a controller class to illustrate the .NET code.

using System;
using System.Data.OracleClient;
using System.Web;
using System.Data;


namespace Xnode.Model.Controllers
{
    public static class BookController
    {
        /// <summary>
        /// Fetch all books from database.
        /// </summary>
        /// <returns>Dataset: Representing all books.</returns>
        public static DataSet Fetch()
        {
            // defaults
            var results = new DataSet();

            //The convention for the command text for packages is: SCHEMA.PACKAGE.PROCEDURE
            const string commandText = @"XNODE.BOOKAPP.BOOKS_FETCH";
            using (var connection = new OracleConnection(DatabaseHelper.Load(true, "object")))
            {
                using (var command = new OracleCommand(commandText, connection))
                {
                    var p1 = new OracleParameter("v_results", OracleType.Cursor)
                    {
                        Direction = ParameterDirection.Output
                    };

                    command.Parameters.Add(p1);
                    command.CommandType = CommandType.StoredProcedure;
                    
                    using(var adapter = new OracleDataAdapter(command))
                    {
                        adapter.Fill(results);
                    }
                }
            }
            return results;
        }

        public static void AddBook(string title)
        {
                       
            //Add the book.
            const string commandText = @"XNODE.BOOKAPP.BOOKS_ADD";
            using (var connection = new OracleConnection(DatabaseHelper.Load(true, "object")))
            {
                using (var command = new OracleCommand(commandText, connection))
                {
                    var p1 = new OracleParameter("v_title", OracleType.NVarChar)
                    {
                        Direction = ParameterDirection.Input,
                        Value = title
                    };

                    command.Parameters.Add(p1);

                    command.CommandType = CommandType.StoredProcedure;
                    
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }
    }
}

So here we have a controller that makes calls to the Oracle backend. The DatabaseHelper class is simply a helper class that is used to retrieve the database connection string through an overloaded Load() method. You should substitute this code with the appropriate call to retrieve your connection string.

In any case, there you have it…working with Oracle stored procedures and packages using .NET.

I’ve read that there is a third party data access provider available that implements EF and works well. This is great news for those projects where you simply have no influence as to which database backend to use.

Comments


Comments are closed