C # & Oracle - margin notes - 1

Once upon a time, long ago ...

The company bought a license to the Oracle database. Then I got a job in this company. Accordingly, beginning to promote. Net "to the masses." Oracle DB is not used in many organizations, but is used.
How to interact with the Oracle database using C #?


')
I will make a reservation in advance, I am not an Oracle guru; and also not a guru of beautiful use of patterns, but I try and know where the pie is lying I approach the question philosophically, I know what I have, I know what I want, but I use it in the key I need.

But let's leave philosophizing. Let's do the task.
First of all, it is necessary to prepare the “environment”:
1. Put the Oracle client, without which the interaction with the database is not implemented.
2. Customize TNS to fit your needs.
3. Create a project in Visual Studio.
4. Add reference to the System.Data.OracleClient.dll assembly, by which we will “manipulate” the database.

Prepare the soil, perhaps we will make the database of the military registration and enlistment office. In the simplest case, one table is enough for this:

create table CONSCRIPT_INFO (
ID NUMBER not null ,
FIRST_NAME VARCHAR2(128),
LAST_NAME VARCHAR2(128),
AGES NUMBER,
GROWTH FLOAT ,
BIOGRAPHY CLOB ,
constraint PK_CONSCRIPT_INFO primary key (ID)
);


ID, Name, Last Name, Age, Growth, Biography. Weight does not interest us.

We define the procedures for adding, in my opinion the most interesting, then I will explain why:

PROCEDURE ADD_CONSCRIPT
(FirstNameIn IN VARCHAR2, LastNameIn IN VARCHAR2,
AgesIn IN NUMBER, GrowthIn IN FLOAT , BiographyIn IN CLOB )
IS
BEGIN
INSERT INTO CONSCRIPT_INFO
(
ID,
FIRST_NAME,
LAST_NAME,
AGES,
GROWTH,
BIOGRAPHY
)
VALUES
(
CONSCRIPT_INFO_SEQ.NEXTVAL,
FirstNameIn,
LastNameIn,
AgesIn,
GrowthIn,
BiographyIn
);
END ;

Uninstall:

PROCEDURE DELETE_CONSCRIPT
(ConscriptIDIn IN NUMBER)
IS
BEGIN

DELETE
FROM CONSCRIPT_INFO
WHERE ID = ConscriptIDIn;

END ;

Receiving data:

PROCEDURE GET_CONSCRIPTS
(ConscriptsOut OUT sys_refcursor)
IS
BEGIN

OPEN ConscriptsOut FOR
SELECT *
FROM CONSCRIPT_INFO;

END ;

Now I will explain what is interesting in the procedure for adding a new user - an object of type CLOB. This type is capable of storing string data up to 4 gigabytes in size, in contrast to Varchar, which can operate on strings up to 4,000 bytes. Those. if you try to create a VARCHAR field with a size of 5000, you will get a severe "bummer." But those who worked with the CLOB type from C # know that this is very Chelyaben. But I am running ahead, about everything in a row.

When working with Oracle, there is one unpleasant moment, if you do not explicitly close the connection, then the number of cursors will be after SELECT `a will grow exponentially. This problem is solved "in the forehead":

Creating and opening a connection

using (OracleConnection connection = new OracleConnection())
{
...
}

Thus, IDisposable will do all the rough work for you.
If you pay attention to the constructor of OracleConnection , you will see that there are 2 options, "empty" and using the string describing the connection to the database.
The line looks like this:
Data Source = out_database_name; Password = our_password; User ID = our_username
I think in explanations that here that, this line does not need.

The next step is to open the connection:

connection.Open();

Team building

Now we are ready to execute the command. After using the command, it is also necessary to “release” it, we will not deviate from the principles and use the same remarkable construction using :

using (OracleConnection connection = new OracleConnection())
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "GET_CONSCRIPTS" ;
}
}

Let's see what this “piece” of code does: first a team is created, it is assigned a connection, within which our team will work. Then the type of command is indicated, in total 3 types are distinguished:
1. StoredProcedure - The name of the stored procedure.
2. TableDirect - The name of the table.
3. Text - Text SQL command. (Default).

The next parameter is the text, the name of the stored procedure itself or the command. In our case, we will use a stored procedure with the name "GET_CONSCRIPTS"

Ok let's go

Use of passed parameters

I will make a reservation right away, you can use the parameters with CommandType.Text , below I will show how this is done.

In the meantime, back with our parameters:

OracleParameter ConscriptsOut = new OracleParameter()
{
ParameterName = "ConscriptsOut" ,
Direction = System.Data.ParameterDirection.Output,
OracleType = OracleType.Cursor
};

command.Parameters.Add(ConscriptsOut);

What happens here: created a parameter, indicated that it works on “output” and indicated the type of the parameter (for more information about the types used, as well as their compatibility with standard .Net types, see MSDN ). If the parameter worked on the “input”, then it would be necessary to specify the value - Value . Attached parameter to the team and ...

Command execution

command.ExecuteNonQuery();

the command is executed ... now you can "retrieve" the data for example:

DataTable table = new DataTable();
table.Load(command.Parameters[ "ConscriptsOut" ].Value as OracleDataReader);

The returned parameter is a cursor that can be read as an OracleDataReader. How to parse the parameter will leave as a "homework";).

On the road

In conclusion, I want to tell you about how to pass a parameter to the text, or rather, I will show:

using (OracleConnection connection = new OracleConnection())
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"SELECT *
FROM CONSCRIPT_INFO
WHERE AGES < :max_ages;" ;

OracleParameter maxAges = new OracleParameter()
{
ParameterName = "max_ages" ,
Direction = ParameterDirection.Input,
OracleType = OracleType.Number,
Value = 27
};

command.Parameters.Add(maxAges);

OracleDataReader reader = command.ExecuteReader();

...
}
}

As you can see the whole trick is to use the sign - :.

In the next article I will talk about working with CLOB and give my code, sharpened by me.