Friday, 12 April 2013

Getting Oracle Sequence nextval in asp.net C#

Getting Oracle Sequence nextval in asp.net 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);
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select cars_seq.nextval from dual";
            cmd.CommandType = CommandType.Text;
            seqVal=Int16.Parse(cmd.ExecuteScalar().ToString());

            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)
            conn.Open()

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

            Return seqVal
      End Function
 

Oracle Connection String

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

1 (first time)

2 (second time) ....


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