Posts
12
Comments
10
Trackbacks
0
Wednesday, January 25, 2012
ORAPOCO: First Update

A few days ago, I have published the firs Version of ORAPOCO.

In this post I would to notify some changes and features.

Changes

First Change is related to IsPkAttribute. There is no sense to get a bool parameter, by that I removed it.

I have refactorized some methods.

Features

I Have created the SequenceAttribute. This attribute will let the use of Sequence in Oracle, using it’s name.

   1: internal class SequenceAttribute : System.Attribute 
   2: {
   3:     public SequenceAttribute(string sequenceName)
   4:     {
   5:         if (string.IsNullOrWhiteSpace(sequenceName)) throw new Exception("Sequence name is necessary.");
   6:         SequenceName = sequenceName;
   7:     }
   8:  
   9:     public string SequenceName { get; set; }
  10: }

A sample of it:

   1: public class Company
   2: {
   3:     [IsPK()]
   4:     [Sequence("S_Company")]
   5:     public System.Int16 CompanyID {get;set;}       
   6:     public System.String CompanyName {get;set;}       
   7: ///...
   8: }

Downloads:

- GitHub

- Nuget

 

I hope to get news from you. Please Give your FeedBack.

 

Regards!

Posted On Wednesday, January 25, 2012 8:21 AM | Comments (2)
Wednesday, January 18, 2012
ORAPOCO: ORM Light to work with Oracle

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!

Posted On Wednesday, January 18, 2012 10:54 AM | Comments (1)
Monday, July 4, 2011
[TIP] Getting Unused Identity Values

What’s the problem?

Given a DB table with an Identity key in SQL Server,  that field could not be repeated.

Well, if you delete a row, the value would be lost.

We are trying to reuse these values.

Solution

LINQ is something that I love, by that I will use it to solve the problem.

To sumup the post, we are going to use a list of integer that will replace our entityset, with the following keys: 1,2,3,5,6,7,9,10,11,15.

Well, how to get a jump in the list:

   1: var query = from n in listado
   2:             where (n > 1 && (listado.IndexOf(n)!=n-1))
   3:             select listado.IndexOf(n) + 1;
   4: int number = query.Tolist().FirstOrDefault();

Hope it helps!!

 

Regards!

Posted On Monday, July 4, 2011 9:50 AM | Comments (1)
Wednesday, March 9, 2011
[EF + ORACLE] Updating and Deleting Entities

Prologue

In previous chapters we have seen how to insert data through EF, with and without sequences. In this one, we are going to see how to Update and delete Data from the DB.

Updating data

The update of the Entity Data (properties) is a very common and easy action.

Before of change any of the properties of the Entity, we can check the EntityState property, and we can see that is EntityState.Unchanged.

 

For making an update it is needed to get the Entity which will be modified. In the following example, I use the GetEmployeeByNumber to get a valid Entity:

   1: EMPLEADOS emp=GetEmployeeByNumber(2);
   2: emp.Name="a";
   3: emp.Phone="2";
   4: emp.Mail="aa";

After modifying the desired properties of the Entity, we are going to check again Entitystate property, which now has the EntityState.Modified value.

To persist the changes to the DB is necessary to invoke the SaveChanges function of our context.

   1: context.SaveChanges();

After modifying the desired properties of the Entity, we are going to check again Entitystate property, which now has the EntityState.Modified value.

To persist the changes to the DB is necessary to invoke the SaveChanges function of our context.

If we check again the EntityState property we will see that the value will be EntityState.Unchanged.

 

Deleting Data

Another easy action is to delete an Entity.

 

The first step to delete an Entity from the DB is to select the entity:

   1: CLIENTS  selectedClient = GetClientByNumber(15);
   2: context.CLIENTES.DeleteObject(clienteSeleccionado);

Before invoking the DeleteObject function, we will check EntityStet which value must be EntityState.Unchanged. After deleting the object, the state will be changed to EntitySate.Deleted.

To commit the action we have to invoke the SaveChanges function. Aftar that, the EntityState property will be EntityState.Detached.

Cascade

Entity Framework lets cascade updates and deletes, although I never see cascade updates.

What is a cascade delete?

A cascade delete is an action that allows to delete all the related object to the object we desire to delete.

This option could be established in the DB manager, or it could be in the EF model designer.

For example:

With a given relation (1-N) between clients and requests.

The common situation must be to let delete those clients whose have no requests.

If we select the relation between both entities, and press the second mouse button, we can see the properties panel of the relation. The props are:

This grid shows the relations indicating the Master table(Clients) and the end point (Cabecera or Requests)

The property “End 1 OnDelete” indicates the action to do when a Entity from the Master will be deleted. There are two options:

- None: No action will be done, it is said, if a Entity has details entities it could not be deleted.

- Cascade: It will delete all related entities to the master Entity.

If we enable the cascade delete in a relation, and we invoke the DeleteObject function of the set, we could observe that all the related object indicates a Entitystate.Deleted state.

Like an update, insert or common delete, until we commit the changes with SaveChanges function, the data would not be commited.

Si habilitamos el borrado en cascada de una relación, e invocamos a la función DeleteObject del conjunto, podremos observar que todas las entidades de Detalle (de la relación indicada) presentan el valor EntityState.Deleted en la propiedad EntityState.

Del mismo modo que en el borrado, inserción o actualización, hasta que no se invoque al método SaveChanges, los cambios no van a ser confirmados en la Base de Datos.

Finally

In this chapter we have seen how to update a Entity, how to delete an Entity and how to implement Cascade Deleting through EF.

In next chapters we will see how to query the DB data.

Posted On Wednesday, March 9, 2011 11:19 AM | Comments (1)
Thursday, March 3, 2011
[EF + Oracle] Inserting Data (Sequences) (2/2)

Prologue

In the previous chapter we have see how to create DB records with EF, now we are going to Some Questions about Oracle.

 

ORACLE

One characteristic from SQL Server that differs from Oracle is “Identity”.

To all that has not worked with SQL Server, this property, that applies to Integer Columns, lets indicate that there is auto increment columns, by that way it will be filled automatically, without writing it on the insert statement.

In EF with SQL Server, the properties whose match with Identity columns, will be filled after invoking SaveChanges method.

In Oracle DB, there is no Identity Property, but there is something similar.

Sequences

Sequences are DB objects, that allow to create auto increment, but there are not related directly to a Table.

The syntax is as follows: name, min value, max value and begin value.

   1: CREATE SEQUENCE nombre_secuencia
   2: INCREMENT BY numero_incremento 
   3: START WITH numero_por_el_que_empezara 
   4: MAXVALUE valor_maximo | NOMAXVALUE 
   5: MINVALUE valor_minimo | NOMINVALUE 
   6: CYCLE | NOCYCLE 
   7: ORDER | NOORDER
   8:  

 

How to get sequence value?

To obtain the next val from the sequence:

   1: SELECT nb_secuencia.Nextval 
   2: From Dual

Due to there is no direct way to indicate that a column is related to a sequence, there is several ways to imitate the behavior: Use a Trigger (DB), Use Stored Procedures or Functions(…) or my particularly option.

EF model, only, imports Table Objects, Stored Procedures or Functions, but not sequences.

By that, I decide to create my own extension Method to invoke Next Val from a sequence:

   1: public static class EFSequence 
   2:     {
   3:         public static int GetNextValue(this ObjectContext contexto, string SequenceName) 
   4:         {
   5:             string Connection = ConfigurationManager.ConnectionStrings["JTorrecillaEntities2"].ConnectionString;
   6:             Connection=Connection.Substring(Connection.IndexOf(@"connection string=")+19);
   7:             Connection = Connection.Remove(Connection.Length - 1, 1);
   8:             using (IDbConnection con = new Oracle.DataAccess.Client.OracleConnection(Connection))
   9:             {
  10:                 using (IDbCommand cmd = con.CreateCommand())
  11:                 {
  12:                     con.Open();
  13:                     cmd.CommandText = String.Format("Select {0}.nextval from DUAL", SequenceName);
  14:                     return Convert.ToInt32(cmd.ExecuteScalar());
  15:                 }
  16:             }
  17:  
  18:          }
  19:     }

This Object Context’s extension method are going to invoke a query with the Sequence indicated by parameter. It takes the connection strings from the App settings, removing the meta data, that was created by VS when generated the EF model. And then, returns the next value from the Sequence.

The next value of a Sequence is unique, by that, when some concurrent users are going to create records in the DB using the sequence will not get duplicates.

This is my own implementation, I know that it could be several ways to do and better ways. If I find any other way, I promise to post it.

To use the example is needed to add a reference to the Oracle (ODP.NET) dll.

Posted On Thursday, March 3, 2011 4:56 PM | Comments (0)
[EF + Oracle] Inserting Data (1/2)

Prologue

Following EF series (I ,II y III) in this chapter we will see how to create DB record from EF.

Inserting Data

Like we indicated in the 2º post: “One Entity matches with a DB record, and one property match with a Table Column”.

To start, we need to create an object from one of the Entities:

   1: EMPLEADOS empleado = new EMPLEADOS();

Also like, I told previously, Exists the possibility to use the Static Function defined by VS for each Entity:

Once we have created the object, we can Access to it properties to fill like a common class:

 

   1: empleado.NOMBRE = "Javier Torrecilla";

 

After finish of fill our Entity properties, it must be needed to add the object to the appropriate ObjectSet in the ObjectContext:

   1: enti.EMPLEADOS.AddObject(empleado);

or

   1: enti.AddToEMPLEADOS(empleado);

Both methods will do the same action, create an insert statement.

Have we finished?

No.

Any Entity has a property called “EntityState”. This prop is an Enum from “EntityState”, which has the following:

  • Detached: the Entity is created, but not added to the Context.
  • Unchanged: There is no pending changes in the Entity.
  • Added: The entity is added to the ObjectSet, but it is not yet sent to the DB.
  • Deleted: The object is deleted form the ObjectSet, but not yet from the DB.
  • Modified: There is Pending Changes to confirm.

Let’s see, the several values of the property during the Creation steps:

1. While the Object is created and we are filling the props: EntityState.Detached;

2. After adding to the ObjectSet: EntityState.Added. This not indicated that the record is in the DB

3. Saving the Data:
To sabe the data in the DB, we are going to call “SaveChanges” method of the Object Context. After invoke it, the property will be EntityState.Unchanged.

     

    What does SaveChanges Method?

    This function will synchronize and send all pending changes to DB.

    It will add, modify or delete all Entities, whose EntityState property, is setted to Added, Deleted or Modified.

    After finishing, all added or modified entities will be change the State to “Unchanged”, and deleted Entities must take the “Detached” state.

    Posted On Thursday, March 3, 2011 4:24 PM | Comments (0)
    [EF + Oracle]Object Context

    Prologue

    After EF episodes I and II, we are going to see the Object Context.

    What is Object Context?

    It is a class which manages the DB connection, and the different Entities of our model.

    When Visual Studio creates the EF model, like I explain previously, also generates a Class that extends ObjectContext.

    ObjectContext provides:

    - DB connection

    - Add, update and delete functions.

    - Object Sets of Entities.

    - State of Pending Changes.

    This class will give a function, for each Entity, like 

    Esta clase va a contar con una función, para cada entidad, del tipo “AddTo{ENTITY}({Entity_Type } value)”, which are going to add a Entity to the related ObjectSet.

    In addition, it has a property, for each Entity, like “ObjectSet<TEntity> Entity”, does will keep the related record set. It will be filled with the CreateObjectSet<TEntity> function of Base class (ObjectContext).

    What is an ObjectSet?

    It is a class that allows us to manage the Entity Set from a Type.

    It inherits from:

    · ObjectQuery<TEntity>

    · IObjectSet<TEntity>

    · IQueryAble<TEntity

    · IEnumerable<TEntity

    · IQueryAble

    · IEnumerable

    An ObjectSet is a class property that allows query, insert, delete and update records from a determinate Entity.

    In following chapters we will see how to query Entities.

    LazyLoadingEnabled

    A very important property of the Context is “LazyLoadingEnabled”. This Boolean property lets indicate if the data loading is lazy, in other words, the Object will not be created and query until not be needed.

    Finally

    In this post we have seen what the VS generated context is, some of the characteristics, and where to see Entity data.

    In next chapters we will see, CRUD operations, and how to query ObjectSets.

    Posted On Thursday, March 3, 2011 3:53 PM | Comments (0)
    Wednesday, March 2, 2011
    [EF + Oracle] Entities

    Prologue

    Following with the Serie I started yesterday about Entity Framework with Oracle, Today I am going to start talking about Entities.

    What is an Entity?

    A Entity is an object of the EF model corresponding to a record in a DB table. For example, let’s see, in Image 1 we can see one Entity from our model, and in the second one we can see the mapping done with the DB.

    (Image 1)

    (Image 2)

    More in depth a Entity is a Class inherited from the abstract class “EntityObject”, contained by the “System.Data.Objects.DataClasses” namespace. At the same time, this class inherits from the following Class and interfaces:

    • StructuralObject: It is an Abstract class that inherits from INotifyPropertyChanging and INotifyPropertyChanged interfaces, and it exposes the events that manage the Changes of the class, and the functions related to check the data types of the Properties from our Entity.
    •  IEntityWithKey: Interface which exposes the Key of the entity.
    • IEntityWithChangeTracker: Interface which lets indicate the state of the entity (Detached, Modified, Added…)
    • IEntityWithRelationships: Interface which indicates the relations about the entity.

    Which is the Content of a Entity?

    A Entity is composed by: Properties, Navigation Properties and Methods.

    What is a Property?

    A Entity Property is an object that represents a column from the mapped table from DB. It has a data type equivalent in .Net Framework to the DB Type.

    When we create the EF model, VS, internally, create the code for each Entity selected in the Tables step, such all methods that we will see in next steps.

    For each property, VS creates a structure similar to:

    · Private variable with the mapped Data type.

    · Function with a name like On{Property_Name}Changing({dataType} value): It manages the event which happens when we try to change the value.

    · Function with a name like On{Property_Name}Change: It manages the event raised when the property has changed successfully.

    · Property with Get and Set methods:

      • The Set Method manages the private variable and do the following steps:
        • Raise Changing event.
        • Report the Entity is Changing.
        • Set the prívate variable. For it, Use the SetValidValue function of the StructuralObject. There is a function for each datatype, and the functions takes 2 params: the value, and if the prop allow nulls.
      • Invoke that the entity has been successfully changed.
      • Invoke the Changed event of the Prop.

    ReportPropertyChanging and ReportPropertyChanged events, let, respectively, indicate that there is pending changes in the Entity, and the changes have success correctly. While the ReportPropertyChanged is raised, the Track State of the Entity will be changed.

    What is a Navigation Property?

    Navigation Properties are a kind of property of the type: EntityCollection<TEntity>, where TEntity is an Entity type from the model related with the current one, it is said, is a set of record from a related table in the DB.

    The EntityCollection class inherits from:

    · RelatedEnd: There is an abstract class that give the functions needed to obtein the related objects.

    · ICollection<TEntity>

    · IEnumerable<TEntity>

    · IEnumerable

    · IListSource

    For the previous interfaces, I wish recommend the following post from Jose Miguel Torres.

    Navigation properties allow us, to get and query easily objects related with the Entity.

    Methods?

    There is only one method in the Entity object. “Create{Entity}”, that allow us to create an object of the Entity by sending the parameters needed to create it.

    Finally

    After this chapter, we know what is an Entity, how is related to the DB and the relation to other Entities.

    In following chapters, we will se CRUD operations(Create, Read, Update, Delete).

    Posted On Wednesday, March 2, 2011 3:36 PM | Comments (0)
    Tuesday, March 1, 2011
    [EF + Oracle] Intro

    Prologue

    I have a busy personal and working time, and at this moment that I start to get more free time, I decided to start a Serie about Entity Framework with Oracle.

    A few time ago, I got my first experience with EF and Oracle with Oracle 10 g express and Oracle 10 g with the same results, Doesn’t work.

    Now I download Oracle 11 g to Test again.

    Tools

    To start using EF with Oracle we need the following:

    1. Visual Studio 2010. No Express Edition

    2. Oracle 11g

    3 Oracle Driver for EF (ODAC)

    Intro

    People, who are starting with EF developments, I recommend to take a look into Unai Zorrilla’s Blog, the post were written in Spanish but they are great!

    To this Serie, we are going to define the DB from the Oracle administrator. For that we need to follow the next steps:

    1. Create a User with a PassWord. In my example the user will be Jtorrecilla

    2. Create a TableSpace

    3. Define some example tables

     

    (Image1)

    When we have created the DB, we are going to start a new project in VS 2010. I will start a C# Project.

    To start with EF, we need to add a new objet to our Project “ADO .NET Entity Data Model".

    (Image2)

    The next step will be to indicate that our model will be based on an existing DB, and indicate the connection string (Images 3 and 4):

    (Imagen3)

    (Imagen4)

    Once we selected the connection string, we will need to indicate that in the connection will be saved “Sensitive” data (Image 5), and in the next step we are going to select the DB objets to use in the project(Image 6).

     

    (Image 5)

    (Image 6)

    At the end, we will press Finish button, and it will generate a EDMX file to add to our solution, and in the IDE will appear the DB Schema with the selected Tables and Relations.

    (Imagen7)

    One Entity is composed by a set of properties (each matches with a column from the Table in the DB) and Navigation Properties that represents any relation with other Entities.

     

    Finally

    With this chapter we have installed the environment, defined a DB and configured the solution to start using EF with Oracle.

    In the next chapter we are going to see What is a Entity and how it works.

    I hope you enjoy this Serie!

    Posted On Tuesday, March 1, 2011 4:27 PM | Comments (6)
    Thursday, February 17, 2011
    [LINQ] Master – Detail Same Record(II)

    In my previous post, I introduced my problem, but I didn’t explain the problem with Entity Framework

    When you try the solution indicated you will take the following error:

    LINQ to Entities don’t recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])’ of the method, and this method can’t be translated into a stored expression.

    The query that produces that error was:

       1: var consulta = (from TCabecera cab in 
       2:                  contexto_local.TCabecera  
       3:    let Detalle = (from TDetalle detalle 
       4:                    in cab.TDetalle 
       5:    select detalle.Nombre)    
       6:    let Nombres = string.Join(",",Detalle )     
       7:    select new   
       8:       {  
       9:        cab.Campo1, 
      10:        cab.Campo2, 
      11:        Nombres
      12:        }).ToList();
      13: grid.DataSource=consulta;

     

    Why is this error happening?

    This error happens when the query couldn’t be translated into T-SQL.

    Solutions?

    To quit that error, we need to execute the query on 2 steps:

       1: var consulta = (from TCabecera cab in   
       2:                 contexto_local.TCabecera    
       3:       let Detalle = (from TDetalle detalle   
       4:                      in cab.TDetalle   
       5:                      select detalle.Nombre)      
       6:      select new     
       7:       {    
       8:         cab.Campo1, 
       9:         cab.Campo2,  
      10:         Detalle 
      11:         }).ToList();  
      12: var consulta2 = (from dato in consulta
      13:             let Nombes = string.Join(",",dato.Detalle)
      14:             select new 
      15:             {
      16:             dato.Campo1,
      17:             dato.Campo2,
      18:             Nombres
      19:             };
      20:         grid.DataSource=consulta2.ToList();

    Curiously

    This problem happens with Entity Framework but, the same problem can’t be reproduced on LINQ – To – SQL, that it works fine in one unique step.

    Hope It’s helpful

    Best Regards

    Posted On Thursday, February 17, 2011 1:04 PM | Comments (0)