CRUD Operations in ASP.NET Web Forms Using Oracle Stored Procedures

Reference

Original logic and concept adapted from https://www.c-sharpcorner.com/blogs/crud-operation-in-asp-net-web-forms-with-oracle-database.

Overview

In this blog, we’ll explore how to implement full CRUD operations (Create, Read, Update, Delete) in ASP.NET Web Forms using Oracle Database by leveraging stored procedures. While many tutorials perform direct SQL queries in code-behind, this guide shows you a cleaner, modular, and more secure way of using PL/SQL procedures.

Why Use Stored Procedures?

Better performance with precompiled SQL.
Enhanced security.
Centralized logic for easier maintenance.

Environment Setup

IDE: Visual Studio 2015/2022
Database: Oracle

1. Bind Grid with Oracle Stored Procedure

We fetch records based on dynamic input filters like name, email, PAN, date range, etc.

public void BindGrid()
{
    loader.Visible = true;

    string name = txtName.Text;
    string email = txtemail.Text;
    string pan = txtPAN.Text;
    string ucc = txtUCC.Text;
    string dpid = txtdematid.Text;

    // Parse date strings
    if (DateTime.TryParseExact(fdate, "dd MMM yyyy", null, System.Globalization.DateTimeStyles.None, out var startDate) &&
        DateTime.TryParseExact(sdate, "dd MMM yyyy", null, System.Globalization.DateTimeStyles.None, out var endDate))
    {
        fdate = startDate.ToString("yyyy-MM-dd");
        sdate = endDate.ToString("yyyy-MM-dd");
    }

    try
    {
        using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleCon"].ConnectionString))
        using (OracleCommand cmd = new OracleCommand("GET_USER_DETAILS_SIMPLE", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("p_name", OracleDbType.Varchar2).Value = (object)name ?? DBNull.Value;
            cmd.Parameters.Add("p_email", OracleDbType.Varchar2).Value = (object)email ?? DBNull.Value;
            cmd.Parameters.Add("p_pan", OracleDbType.Varchar2).Value = (object)pan ?? DBNull.Value;
            cmd.Parameters.Add("p_ucc", OracleDbType.Varchar2).Value = (object)ucc ?? DBNull.Value;
            cmd.Parameters.Add("p_dpid", OracleDbType.Varchar2).Value = (object)dpid ?? DBNull.Value;
            cmd.Parameters.Add("p_start_date", OracleDbType.Varchar2).Value = (object)fdate ?? DBNull.Value;
            cmd.Parameters.Add("p_end_date", OracleDbType.Varchar2).Value = (object)sdate ?? DBNull.Value;
            cmd.Parameters.Add("p_result", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

            con.Open();

            using (OracleDataAdapter da = new OracleDataAdapter(cmd))
            {
                ds = new DataSet();
                da.Fill(ds);
            }
        }

        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    catch (Exception ex)
    {
        // log exception
    }
    finally
    {
        loader.Visible = false;
    }
}

2. Delete User by IDs

Supports deletion of multiple users via comma-separated IDs passed to procedure.

protected void Deletebtn_Click(object sender, ImageClickEventArgs e)
{
    string chkUserId = Request["chkSelect"]?.ToString()?.Trim() ?? "";

    if (string.IsNullOrEmpty(chkUserId))
    {
        ScriptManager.RegisterStartupScript(this.Page, typeof(string), "alert", "alert('No Items Selected.');", true);
        return;
    }

    try
    {
        OracleParameter param = new OracleParameter("p_ids", OracleDbType.Varchar2)
        {
            Value = chkUserId
        };

        OracleHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "Delete_User_By_CommaIds", param);

        ScriptManager.RegisterStartupScript(this.Page, typeof(string), "alert", "alert('Record deleted successfully');", true);
    }
    catch (Exception ex)
    {
        ScriptManager.RegisterStartupScript(this.Page, typeof(string), "alert", $"alert('Error: {ex.Message}');", true);
    }

    BindGrid();
}

3. Update User Data (Insert or Update)

This handles both add and update operations via the manage_user stored procedure.

protected void btnUpdate_Click(object sender, EventArgs e)
{
    using (OracleConnection con = new OracleConnection(OracleCon))
    {
        con.Open();
        using (OracleTransaction transaction = con.BeginTransaction())
        {
            try
            {
                string srno = Request.QueryString["data"] ?? "";
                string dematId = (drpdpid.Value == "CDSL") ? txtcdslnum.Text : txtnsdlalpha.Text + txtnsdlnum.Text;

                using (OracleCommand cmd = new OracleCommand("manage_user", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Transaction = transaction;

                    cmd.Parameters.Add("p_action", OracleDbType.Varchar2).Value = "SAVE";
                    cmd.Parameters.Add("p_srno", OracleDbType.Varchar2).Value = srno;
                    cmd.Parameters.Add("p_username", OracleDbType.Varchar2).Value = txtclientname.Text;
                    cmd.Parameters.Add("p_pan", OracleDbType.Varchar2).Value = txtPAN.Text;
                    cmd.Parameters.Add("p_email", OracleDbType.Varchar2).Value = txtemail.Text;
                    cmd.Parameters.Add("p_phone", OracleDbType.Varchar2).Value = txtmobile.Text;
                    cmd.Parameters.Add("p_demat_id", OracleDbType.Varchar2).Value = dematId;
                    cmd.Parameters.Add("p_demat_type", OracleDbType.Varchar2).Value = drpdpid.Value;
                    cmd.Parameters.Add("p_family", OracleDbType.Varchar2).Value = txtfamily.Text;
                    cmd.Parameters.Add("p_isfamily", OracleDbType.Int32).Value = chkfamily.Checked ? 1 : 0;
                    cmd.Parameters.Add("p_result", OracleDbType.Int32).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    int result = Convert.ToInt32(cmd.Parameters["p_result"].Value);

                    if (result == 1)
                    {
                        transaction.Commit();
                        ScriptManager.RegisterStartupScript(
                            this.Page,
                            typeof(string),
                            "showalert",
                            "alert('Client data updated successfully'); window.location='/OracleDB/OracleCRUDOperationWithParameter/CRUDOperation.aspx';",
                            true
                        );
                    }
                    else if (result == -1)
                    {
                        ScriptManager.RegisterStartupScript(
                            this,
                            this.GetType(),
                            "checkuser",
                            "alert('Client PAN already exists');",
                            true
                        );
                    }
                    else if (result == -2)
                    {
                        ScriptManager.RegisterStartupScript(
                            this,
                            this.GetType(),
                            "checkfamilyhead",
                            "alert('Family head already exists');",
                            true
                        );
                    }
                    else
                    {
                        ScriptManager.RegisterStartupScript(
                            this,
                            this.GetType(),
                            "error",
                            "alert('Unexpected error occurred');",
                            true
                        );
                    }
                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                ScriptManager.RegisterStartupScript(
                    this,
                    this.GetType(),
                    "dberror",
                    $"alert('Error: {ex.Message}');",
                    true
                );
            }
        }
    }
}

4. Get Data by SRNO (Edit/View)

Used to populate form fields with data fetched from the get_user_by_srno procedure.

public void getClientData(string srno)
{
    if (!string.IsNullOrEmpty(srno))
    {
        btnSubmit.Text = "Update";

        using (OracleConnection con = new OracleConnection(OracleCon))
        using (OracleCommand cmd = new OracleCommand("get_user_by_srno", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("p_srno", OracleDbType.Varchar2).Value = srno;
            cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

            using (OracleDataAdapter da = new OracleDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    var dr = ds.Tables[0].Rows[0];

                    txtclientname.Text = dr["USERNAME"].ToString();
                    txtPAN.Text = dr["PAN"].ToString();
                    txtemail.Text = dr["Email"].ToString();
                    txtmobile.Text = dr["PHONENUMBER"].ToString();
                    drpdpid.Value = dr["DEMATTYPE"].ToString();
                    txtfamily.Text = dr["family"].ToString();
                    chkfamily.Checked = dr["isfamily"].ToString() == "1";

                    if (drpdpid.Value == "CDSL")
                    {
                        cdsl.Style.Add("display", "block");
                        txtcdslnum.Text = dr["CDSL_DEMAT_ID"].ToString();
                    }
                    else
                    {
                        nsdlnumber.Style.Add("display", "none");
                        nsdlalpha.Style.Add("display", "block");
                        txtnsdlalpha.Text = dr["CDSL_DEMAT_ID"].ToString();
                    }
                }
            }
        }
    }
}

Purpose: Retrieves user details with flexible filtering options.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE GET_USER_DETAILS_SIMPLE (
    p_name        IN  VARCHAR2 DEFAULT NULL,
    p_email       IN  VARCHAR2 DEFAULT NULL,
    p_pan         IN  VARCHAR2 DEFAULT NULL,
    p_ucc         IN  VARCHAR2 DEFAULT NULL,
    p_dpid        IN  VARCHAR2 DEFAULT NULL,
    p_start_date  IN  VARCHAR2 DEFAULT NULL,
    p_end_date    IN  VARCHAR2 DEFAULT NULL,
    p_result      OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_result FOR
        SELECT *
        FROM UsersDetails
        WHERE Srno IS NOT NULL
          AND (p_name IS NULL OR USERNAME LIKE '%' || p_name || '%')
          AND (p_email IS NULL OR EMAIL = p_email)
          AND (p_pan IS NULL OR PAN = p_pan)
          AND (p_ucc IS NULL OR USERID = p_ucc)
          AND (p_dpid IS NULL OR CDSL_DEMAT_ID = p_dpid)
          AND (
              p_start_date IS NULL OR p_end_date IS NULL OR
              TRUNC(CREATEDDATE) BETWEEN TO_DATE(p_start_date, 'YYYY-MM-DD')
                                    AND TO_DATE(p_end_date, 'YYYY-MM-DD')
          )
        ORDER BY CREATEDDATE DESC;
END GET_USER_DETAILS_SIMPLE;

Purpose: Retrieves user details with flexible filtering options.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE Delete_User_By_CommaIds (
    p_ids IN VARCHAR2
)
AS
BEGIN
    FOR r IN (
        SELECT REGEXP_SUBSTR(p_ids, '[^,]+', 1, LEVEL) AS id
        FROM dual
        CONNECT BY REGEXP_SUBSTR(p_ids, '[^,]+', 1, LEVEL) IS NOT NULL
    )
    LOOP
        DELETE FROM UsersDetails
        WHERE srno = TO_NUMBER(TRIM(r.id));
    END LOOP;

    COMMIT;
END;

Purpose: Unified procedure for both retrieving and saving user data with validation.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE manage_user (
    p_action        IN VARCHAR2,         -- 'GET' or 'SAVE'
    p_srno          IN VARCHAR2,
    p_username      IN VARCHAR2 DEFAULT NULL,
    p_pan           IN VARCHAR2 DEFAULT NULL,
    p_email         IN VARCHAR2 DEFAULT NULL,
    p_phone         IN VARCHAR2 DEFAULT NULL,
    p_demat_id      IN VARCHAR2 DEFAULT NULL,
    p_demat_type    IN VARCHAR2 DEFAULT NULL,
    p_family        IN VARCHAR2 DEFAULT NULL,
    p_isfamily      IN NUMBER DEFAULT NULL,
    p_result        OUT NUMBER,          -- Used only for 'SAVE'
    p_cursor        OUT SYS_REFCURSOR    -- Used only for 'GET'
)
AS
    v_count_pan     NUMBER;
    v_count_family  NUMBER;
BEGIN
    IF UPPER(p_action) = 'GET' THEN
        -- Fetch user by srno
        OPEN p_cursor FOR
            SELECT * FROM UsersDetails
            WHERE srno = p_srno;
        p_result := 1;

    ELSIF UPPER(p_action) = 'SAVE' THEN
        -- Check if PAN exists for another user
        SELECT COUNT(*) INTO v_count_pan
        FROM UsersDetails
        WHERE PAN = p_pan AND (p_srno IS NULL OR srno != p_srno);

        IF v_count_pan > 0 THEN
            p_result := -1; -- PAN already exists
            RETURN;
        END IF;

        -- Check if family head already exists
        IF p_isfamily = 1 THEN
            SELECT COUNT(*) INTO v_count_family
            FROM UsersDetails
            WHERE family = p_family AND isfamily = 1 AND (p_srno IS NULL OR srno != p_srno);

            IF v_count_family > 0 THEN
                p_result := -2; -- Family head exists
                RETURN;
            END IF;
        END IF;

        IF p_srno IS NULL OR p_srno = '' THEN
            -- INSERT
            INSERT INTO UsersDetails (
                USERNAME, PAN, Email, PHONENUMBER,
                CDSL_DEMAT_ID, DEMATTYPE, family, isfamily, CREATEDDATE
            ) VALUES (
                p_username, p_pan, p_email, p_phone,
                p_demat_id, p_demat_type, p_family, p_isfamily, SYSDATE
            );
        ELSE
            -- UPDATE
            UPDATE UsersDetails
            SET USERNAME      = p_username,
                PAN           = p_pan,
                Email         = p_email,
                PHONENUMBER   = p_phone,
                CDSL_DEMAT_ID = p_demat_id,
                DEMATTYPE     = p_demat_type,
                family        = p_family,
                isfamily      = p_isfamily,
                CREATEDDATE   = SYSDATE
            WHERE srno = p_srno;
        END IF;

        p_result := 1; -- Success

    ELSE
        p_result := -98; -- Invalid action
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        p_result := -99; -- General error
        IF p_action = 'GET' THEN
            p_cursor := NULL;
        END IF;
END;

Purpose: Simple procedure to retrieve a single user by Srno.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE get_user_by_srno (
    p_srno    IN  VARCHAR2,
    p_cursor  OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_cursor FOR
        SELECT * 
        FROM UsersDetails 
        WHERE srno = p_srno;
END;

Summary

This blog presented a clean approach to performing CRUD operations in ASP.NET Web Forms using Oracle stored procedures. Using this structure, you can centralize logic in Oracle, enhance reusability, and keep your C# code modular and easy to maintain.