Posts
12
Comments
10
Trackbacks
0
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)