Intro

The purpose of this post, is to speak of a small project that I have just published: ORAPOCO. You can find it both in GITHUB or Nuget.

What is ORAPOCO?

It is a small project that will allow to work with objects little and against our Oracle database.

The project consists of the following files:

- OracleDB.cs:

This class will be in charge of carrying out all actions against the database.

The available methods are:

Method Desc
QueryAll<T> Given a type T(poco object) will execute a query on the database and will return a list of the T.
Query<T>

In the same way that QueryAll, will return a set of results of T, but with the option of selecting columns, sort, establish a clause where, do take and skip.

Insert<T> Given a type T and an entity of such kind will try to make an insertion in the database.
Update<T>

Given a type T and an entity of such kind will try to make an update in the database according to the values of primary key of the entity.

Delete<T>

Given a type T and an entity of such kind is going to try to make a deletion in the database according to the values of primary key of the entity.

MultipleQuery<T1,T2>

It will return an object of type Tuple with the set of results indicated by T1, T2 and T3 (2 overloads)

 

- ORAPOCO.tt: Template code generation via a connection against the ORACLE database will define classes shortly that we use in our application.

Within this template makes use of the first link in the application configuration file and defines a variable "_schemma" which will be that we need to indicate the owner to find tables in Oracle.

- IsPkAttribute.cs:

Attribute that will serve to set primary keys of our little objects, and that will be used to carry out inserts or updates in the database.

Where to start using ORAPOCO?

You can download the source code from GITHUB, or install the package from Nuget:

PM> Install-Package ORAPOCO

Sample:

An example of bit generated by the template object:

   1: public class TA_USUARIOS
   2:    {
   3:        [IsPK(true)]
   4:        public System.Int32 USUA_CODIGO_USUARIO { get; set; }
   5:  
   6:        public System.String USUA_NOMBRE { get; set; }
   7:  
   8:        public System.String USUA_APELLIDO1 { get; set; }
   9:  
  10:        public System.String USUA_APELLIDO2 { get; set; }
  11:  
  12:        public System.DateTime? USUA_FECHA_ALTA { get; set; }
  13:  
  14:        public System.String USUA_LOGIN_USUARIO { get; set; }
  15:  
  16:    }

How to carry out an insertion?

   1: var db = new POCO.Ora.TP.OracleDB("SIFCO");
   2:  
   3:   var usu = new POCO.Ora.TP.TA_USUARIOS { 
   4:                               USUA_APELLIDO1 = "Torrecilla",
   5:                               USUA_APELLIDO2 = "Puertas", 
   6:                               USUA_CODIGO_USUARIO = 874,
   7:                               USUA_FECHA_ALTA = DateTime.Now, 
   8:                               USUA_LOGIN_USUARIO = "test", 
   9:                               USUA_NOMBRE = "Javi" };
  10:   db.Insert<POCO.Ora.TP.TA_USUARIOS>(usu);

How to carry out an update?

   1: var usu = new POCO.Ora.TP.TA_USUARIOS { 
   2:                             USUA_APELLIDO1 = "Torrecilla",
   3:                             USUA_APELLIDO2 = "Puertas", 
   4:                             USUA_CODIGO_USUARIO = 874,
   5:                             USUA_FECHA_ALTA = DateTime.Now, 
   6:                             USUA_LOGIN_USUARIO = "test", 
   7:                             USUA_NOMBRE = "Javi" };
   8: db.Update<POCO.Ora.TP.TA_USUARIOS>(usu);

 

How to delete a record?

   1: var usu = new POCO.Ora.TP.TA_USUARIOS { 
   2:                             USUA_APELLIDO1 = "Torrecilla",
   3:                             USUA_APELLIDO2 = "Puertas", 
   4:                             USUA_CODIGO_USUARIO = 874,
   5:                             USUA_FECHA_ALTA = DateTime.Now, 
   6:                             USUA_LOGIN_USUARIO = "test", 
   7:                             USUA_NOMBRE = "Javi" };
   8: db.Delete<POCO.Ora.TP.TA_USUARIOS>(usu);

 

How to query data

-All results:

   1: var query = db.QueryAll<POCO.Ora.TP.TA_USUARIOS>();

 

-Take 5 elements and skip 5:

   1: var query = db.Query<POCO.Ora.TP.TA_USUARIOS>(take: 5);

 

   1: var query = db.Query<POCO.Ora.TP.TA_USUARIOS>(skip: 5);

 

 

- Results ordering:

   1: var query = db.Query<POCO.Ora.TP.TA_USUARIOS>(order: "USUA_APELLIDO1 ASC, USUA_APELLIDO2 ASC");

- Results filtering:

   1: var query = db.Query<POCO.Ora.TP.TA_USUARIOS>(where: "USUA_APELLIDO1 LIKE (:0)",args: new object[]{"%or%"});

- Multiple results:

   1: var multiQuery = db.MultipleQuery<POCO.Ora.TP.TA_USUARIOS, POCO.Ora.TP.TA_FUNCIONALIDADES>();

multiQuery  "item1" will contain the collection of “TA_USUARIOS", and in "item2" will contain the collection of “TA_FUNCIONALIDADES”.

 

 

I hope to hear from you. Please let me know your FeedBack.

 

REGARDS!