Friday, 12 April 2013

Getting Oracle Sequence nextval in C#

Getting Oracle Sequence nextval in C#

Oracle sequence is a schema object in Oracle Database. It serves the purpose of getting Auto increment values. Others databases like SQL Server/Mysql has Auto increment feature, but associated with table column.

Create a Sequence in Oracle

SQL> create sequences cars_seq;

Calling Oracle Sequence using ADO.NET C#

      int GetOracleSequenceValue()
            int seqVal = -1;

            String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
            OracleConnection conn = new OracleConnection(connString);

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select cars_seq.nextval from dual";
            cmd.CommandType = CommandType.Text;

            return seqVal;

Calling Oracle Sequence using ADO.NET VB.NET

      Private Function GetOracleSequenceValue() As Integer
            Dim seqVal As Integer =  -1

            Dim connString As String =  ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
            Dim conn As OracleConnection =  New OracleConnection(connString)

            Dim cmd As OracleCommand =  New OracleCommand()
            cmd.Connection = conn
            cmd.CommandText = "select cars_seq.nextval from dual"
            cmd.CommandType = CommandType.Text

            Return seqVal
      End Function

Oracle Connection String

    <add name="ConnectionString1" connectionString="Data Source=localhost;User ID=scott;password=tiger"
      providerName="System.Data.OracleClient" />

1 (first time)

2 (second time) ....

Tags:Oracle connection String in,Getting Oracle Sequence nextval in C#,Creating a oracle sequence, oracle sequence nextval in,Getting Oracle Sequence nextval in VB.NET