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.