.NET

How to Use Oracle Sequences for Auto-Increment IDs in C# .NET Applications

Introduction

In Oracle Database, generating unique numbers for primary keys or identifiers isn't as simple as using IDENTITY columns like in SQL Server. Instead, Oracle provides a SEQUENCE object that can auto-generate unique numeric values efficiently.

In this blog, you'll learn:

  • What Oracle Sequences are
  • How to create and use them
  • How to integrate Oracle Sequences in C# .NET applications using ADO.NET

This blog is ideal for .NET developers working with Oracle databases.

What Is a SEQUENCE in Oracle?

A SEQUENCE is a database object in Oracle that generates unique, sequential numbers. It’s widely used for:

  • Auto-incrementing Primary Keys
  • Generating Invoice Numbers
  • Creating Employee IDs
  • Any scenario where unique numbers are required

Why Do .NET Developers Need Oracle Sequences?

Unlike SQL Server’s IDENTITY property or MySQL’s AUTO_INCREMENT, Oracle requires developers to use Sequences explicitly for auto-incrementing IDs.

When building C# .NET applications with Oracle, you need to fetch the next value from a sequence while inserting records.

How to Create a SEQUENCE in Oracle

Here’s the basic syntax:

CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

Explanation

  • START WITH: First value to generate.
  • INCREMENT BY: Value increase per generation.
  • NOCACHE: No pre-allocation of sequence numbers (can use CACHE for better performance).
  • NOCYCLE: Sequence won’t restart after reaching the maximum value.

How to Use SEQUENCE in Oracle (SQL Example)

Fetch Next Value

SELECT emp_seq.NEXTVAL FROM dual;

Insert Data Using SEQUENCE

INSERT INTO EMP_IND (empno, ename)
VALUES (emp_seq.NEXTVAL, 'Ravi Kumar');

Integrating Oracle SEQUENCE in C# .NET Application (With ADO.NET)

Here’s how you can use Oracle Sequences in a C# .NET Console Application using Oracle.ManagedDataAccess.Client:

Required NuGet Package

Oracle.ManagedDataAccess

C# Code Example

using System;
using Oracle.ManagedDataAccess.Client;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=system;Password=Oracle123;Data Source=XE;";

        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            conn.Open();

            string insertQuery = "INSERT INTO EMP_IND (empno, ename) VALUES (emp_seq.NEXTVAL, :ename)";

            using (OracleCommand cmd = new OracleCommand(insertQuery, conn))
            {
                cmd.Parameters.Add(new OracleParameter("ename", "Ravi Kumar"));
                cmd.ExecuteNonQuery();
                Console.WriteLine("Record inserted successfully with auto-generated Employee ID.");
            }
        }
    }
}

Key Notes

  • emp_seq.NEXTVAL automatically generates a new ID.
  • :ename is a bind parameter for safer query execution.
  • Works seamlessly for auto-incrementing records.

Benefits of Using SEQUENCE

  1. Generates unique numbers automatically.
  2. Handles multi-user concurrency safely.
  3. High performance with CACHE option.
  4. Can be reused across multiple tables if needed.

Best Practices

  • Always use CACHE in high-performance apps.
  • Avoid manual insertion of primary key values where sequences are used.
  • Test sequence behavior in multi-threaded environments if needed.

Conclusion

Using Oracle Sequences is a reliable, efficient, and scalable way to generate unique identifiers in your database applications.

For .NET developers, integrating Oracle Sequences using ADO.NET is straightforward and ensures smooth record insertion with auto-generated IDs.

By leveraging sequences correctly, you can avoid complex key generation logic and maintain clean, high-performance applications.
Membership not found