[EF + Oracle] Inserting Data (Sequences) (2/2)
- by JTorrecilla
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.
