This article is basically meant for those developers who are searching for implementing CRUD (Create, Read, Update, Delete operation for DB) layer for database operation using Microsoft technology as front end (for e.g. ASP.net) and Oracle database as backend.
In this article I am going to explain how developers can implement CRUD Operations using Stored Procedures for Oracle and ASP.net.
All code that I have provided is developed in asp.net 2.0 and the Oracle Database Version is Oracle 10g.
You will find lot of articles on net that are related to asp.net and SQL server but only few are related to asp.net and oracle and that too in bits and pieces.
I was in desperate need to implement it for my project and it took time before I could design and implement it, I want to share it so that its saves time for other users who are looking for the same.
CRUD – Stands for C-Create, R-Read, U-Update, and D-Delete.
These are the basic operations that we perform on any database Table. These operations can be performed either by using SQL queries or can be implemented using Stored Procedures for each operation .We are going to used Stored procedures for implementing CRUD Operations.
This article will give users complete information about each operation of CRUD using Oracle database.
I assume that the readers of this article have a fair idea of PL/SQL.This Article is not about how to write procedures, it’s about how to perform CRUD operation on oracle Db using ASP.net.
So Lets Start!
Lets us go step by step
Lets us design a simple page for storing User Data i.e. user Master Data.
Step 1: Define a database table for user Entity, name it “Users”
Step 1: Define a database table for user Entity, name it “Users”
The table will have following schema
Sr. No. | FieldName | FieldType | Description |
1. | UserId | Number | Oracle Sequence Auto-generated field. |
2. | FirstName | Varchar2(25) | |
3. | LastName | Varchar2(25) | |
4. | Email | Varchar2(25) | |
5. | Title | Varchar2(25) | For E.g. Manager, Administrator etc. |
Here is Create Query for the same,
Note: I am using Benthic Software to fire my all oracle Queries & PL/SQL.
CREATE TABLE USERS
(
UserId NUMBER, FirstName VARCHAR2 (25), LastName VARCHAR2 (25), Email VARCHAR2 (25), Title VARCHAR2 (25)
);
UserId is a sequence you can create a sequence as follows
Here is the Create Sequence for UserId
CREATE SEQUENCE USERID START WITH 1 NOMAXVALUE MINVALUE 1 NOCACHE;
A Sequence in oracle is Auto-generated field.
Step 2: Once our entity table is created let us now define the stored procedure for CRUD operations except read operation everything else will seem to be straight forward.
1. C – Create
The Stored procedure for Create Operation is as follows
CREATE OR REPLACE PROCEDURE UserCreate
(
p_firstname_in users.firstname%TYPE,
p_lastname_in users.lastname%TYPE,
p_email_in users.email%TYPE,
p_title_in users.title%TYPE
)
AS
BEGIN
INSERT INTO users
(
userid, firstname, lastname, email, title)
VALUES
(
userid.NEXTVAL, p_firstname_in, p_lastname_in, p_email_in, p_title_in);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('RECORD INSERTION FAILED');
END;
Note: Exception handling Section, I leave it to the user it can be implemented as per business requirement.
2. R – Read
The Stored procedure for Read Operation is as follows
For Read Operation you need to create a Package as we will require a Ref Cursor to return values but don’t worry its pretty simple
First Lets us create a Package .A Package has a header and a body, first we will
define the header as follows
define the header as follows
CREATE OR REPLACE PACKAGE UserPackage
AS
TYPE c_user IS REF CURSOR;
PROCEDURE UserRead (p_user_record_out OUT c_user);
END;
The Package body is as follows.
CREATE OR REPLACE PACKAGE BODY UserPackage
AS
PROCEDURE UserRead
(
p_user_record_out OUT c_user
)
AS
BEGIN
OPEN p_user_record_out FOR
SELECT userid, firstname, lastname, email, title
FROM users;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('NO RECORDS FOUND');
END;
END;
3. U – Update
The Stored procedure for Update Operation is as follows
CREATE OR REPLACE PROCEDURE UserUpdate
(
p_userid_in users.userid%TYPE,
p_firstname_in users.firstname%TYPE,
p_lastname_in users.lastname%TYPE,
p_email_in users.email%TYPE,
p_title_in users.title%TYPE
)
AS
BEGIN
UPDATE users
SET firstname=p_firstname_in, lastname=p_lastname_in, email=p_email_in, title=p_title_in
WHERE userid=p_userid_in;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('FAILED TO UPDATE RECORD');
END;
4. D – Delete
The Stored procedure for Delete Operation is as follows.
CREATE OR REPLACE PROCEDURE UserDelete
(
p_userId_in users.userid%TYPE
)
AS
BEGIN
DELETE users
WHERE userid=p_userId_in;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('FAILED TO DELETE RECORD');
END;
Once all above procedures are executed, we are done with CRUD on Database side; the rest is calling these procedures from .net
So let’s start.
Again let’s go step wise
Calling CRUD Operations from .Net
Step 1: Define a class in .net I am going to create C# Class (this class can be Data Access layer Class or Data object Class or the business layer class).
We will name our class as User.cs.
We will have following methods in the class
- Create ()
- Update ()
- Delete ()
- GetUserList()
Note: I am using Visual Studio 2005 (.net 2.0)
The class with its method and properties will be as follows
public class User
{
int userId;
public int UserID
{
get { return userId; }
set { userId = value; }
}
string firstName;
public string FirstName
{
get { return firstName; }
set { firstName = value; }
}
string lastName;
public string LastName
{
get { return lastName; }
set { lastName = value; }
}
string userEmail;
public string UserEmail
{
get { return userEmail; }
set { userEmail = value; }
}
string userTitle;
public string UserTitle
{
get { return userTitle; }
set { userTitle = value; }
}
public void Insert() {}
public void Update() {}
public void Delete() { }
public void GetUserList() { }
}
The Code for Each method is as follows
All methods will use a valid Connection object in this Case “objConn” .You also need to add reference of “System.Data.OracleClient”
How to populate objConn
OracleConnection objConn = new OracleConnection();
objConn.ConnectionString = “Your Connection String Goes Here”;
objConn.Open();
1. Insert
public void Insert()
{
try
{
OracleCommand objCmd = new OracleCommand();
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
objCmd.CommandText = "UserCreate";
OracleParameter objParam1 = new OracleParameter();
objParam1.ParameterName = "p_firstname_in";
objParam1.Value = this.FirstName;
objCmd.Parameters.Add(objParam1);
objParam1 = null;
OracleParameter objParam2 = new OracleParameter();
objParam2.ParameterName = "p_lastname_in";
objParam2.Value = this.LastName;
objCmd.Parameters.Add(objParam2);
objParam2 = null;
OracleParameter objParam3 = new OracleParameter();
objParam3.ParameterName = "p_email_in";
objParam3.Value = this.UserEmail;
objCmd.Parameters.Add(objParam3);
objParam3 = null;
OracleParameter objParam4 = new OracleParameter();
objParam4.ParameterName = "p_title_in";
objParam4.Value = this.UserTitle;
objCmd.Parameters.Add(objParam4);
objParam4 = null;
objCmd.ExecuteNonQuery();
objConn.Dispose();
}
catch (Exception)
{
throw;
}
}
2. Update
public void Update()
{
try
{
OracleCommand objCmd = new OracleCommand();
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
objCmd.CommandText = "UserUpdate";
OracleParameter objParam1 = new OracleParameter();
objParam1.ParameterName = "p_userid_in";
objParam1.OracleType = OracleType.Int32;
objParam1.Value = this.UserID;
objCmd.Parameters.Add(objParam1);
objParam1 = null;
OracleParameter objParam2 = new OracleParameter();
objParam2.ParameterName = "p_firstname_in";
objParam2.Value = this.FirstName;
objCmd.Parameters.Add(objParam2);
objParam2 = null;
OracleParameter objParam3 = new OracleParameter();
objParam3.ParameterName = "p_lastname_in";
objParam3.Value = this.LastName;
objCmd.Parameters.Add(objParam3);
objParam3 = null;
OracleParameter objParam4 = new OracleParameter();
objParam4.ParameterName = "p_email_in";
objParam4.Value = this.UserEmail;
objCmd.Parameters.Add(objParam4);
objParam4 = null;
OracleParameter objParam5 = new OracleParameter();
objParam5.ParameterName = "p_title_in";
objParam5.Value = this.UserTitle;
objCmd.Parameters.Add(objParam5);
objParam5 = null;
objCmd.ExecuteScalar();
objConn.Dispose();
}
catch (Exception)
{
throw;
}
}
3. Delete
public void Delete()
{
try
{
OracleCommand objCmd = new OracleCommand();
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
objCmd.CommandText = "UserDelete";
OracleParameter objParam1 = new OracleParameter();
objParam1.ParameterName = "p_userid_in";
objParam1.OracleType = OracleType.Int32;
objParam1.Value = this.UserID;
objCmd.Parameters.Add(objParam1);
objParam1 = null;
objCmd.ExecuteScalar();
objConn.Dispose();
}
catch (Exception)
{
throw;
}
}
4. GetUserList
public void GetList()
{
try
{
OracleCommand objCmd = new OracleCommand();
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
objCmd.CommandText = "UserPackage.UserRead";
OracleParameter objParam1 = new OracleParameter();
objParam1.ParameterName = "p_user_record_out";
objParam1.OracleType = OracleType.Cursor;
objParam1.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(objParam1);
objParam1 = null;
OracleDataAdapter objAdap = new OracleDataAdapter(objCmd);
DataSet objDs = new DataSet();
objAdap.Fill(objDs);
}
catch (Exception)
{
throw;
}
}
Once the class is created user can call the class methods from .aspx page creating an object of this class and then initializing its properties from page input and calling the methods.
I hope this article is useful for all .net developers working with Oracle Database.
I will post more articles related to oracle and .net.
Thanks,
Mukund
Thanks for this article is very useful, i want to know how it will be CREATE OR REPLACE PACKAGE BODY UserPackage but using where and how it will be in the part of public void GetList()
ReplyDelete