Tuesday, February 19, 2008

Oracle sequence autoincrement field and DataGrid

I needed to create a Form that does insert/edit/update/delete an Oracle table data in a DataGrid. The Oracle table has primary key that is autoincremented. I was using a trigger on before insert new row that sets sequence's nextval into primary key field. I bound DataTable to a GridControl and then inserted new row. The key value of new row in table in DBMS was correct, but GridControl's primary key field value was not updated as in DBMS. I wasted so much time to make it formally done.

My first solution was a forced one. I wrote a stored procedure that returns the sequence's next value. Called it from my C# code and set it to DataTable's primary field column. To use that i disabled the trigger.

Next to it found a Oracle 10g new feature. It's not supported in previous versions of Oracle, i heard. It's RETURNING clause. RETURNING clause is used for return values after executing a query.
For instance:

INSERT (pk_field, field1, field2, field3) INTO table1 VALUES (seq1.NEXTVAL, 'field1', 'field2', 'field3') RETURNING pk_field INTO :pk_field;

This is an example of insertQuery property of TableAdapter. Insert query itself tells that "I" insert nextval of sequence into primary key field and return it back to the grid using :pk_field parameter.

Unfortunately i couldnt get it worked. Visual Studio 2005 was not "understand" RETURNING clause, sayd me wrong SQL.

Later, a few days ago used it Visual Studio 2008. It's working. But i'm still disappointed. I need to do it in VS2005. I hope there must have any solutions. Please let me know if you have any other.

No comments: