9.12 Build ASP.NET Web Service to Update and Delete Data for Oracle Database

873
9.12.1 Build Web Service Project
Web S ervice O racle U pdate D elete
In this section, we will modify an existing Web Service project WebServiceSQLUpdateDelete
to make it our new Web Service project WebServiceOracleUpdateDelete, and enable it
to update and delete data in our sample Oracle database.
Open Windows Explorer and create a new folder Chapter 9 under the root directory
if you have not done that. Open Internet Explorer and browse to our desired Web Service
project WebServiceSQLUpdateDelete at the folder DBProjects\Chapter 9 located at the
accompanying ftp site (see Chapter 1 ). Copy and paste this project into our new folder
Chapter 9. Rename it WebServiceOracleUpdateDelete. Perform the following modifi ca-
tions to this project in the Windows Explorer window:
1. Change the main Web Service page from WebServiceSQLUpdateDelete.asmx to
WebServiceOracleUpdateDelete.asmx .
2. Open the App_Code folder and change the name of our base class fi le from SQLBase.cs
to OracleBase.cs .
3. Open the App_Code folder and change the name of our code - behind page from
WebServiceSQLUpdateDelete.cs to WebServiceOracleUpdateDelete.cs .
Now open Visual Studio.NET 2008 and our new Web Service project
WebServiceOracleUpdateDelete to perform the associated modifi cations to the contents
of the fi les we renamed above. First, let ’ s perform modifi cations to our main Web Service
page WebServiceOracleUpdateDelete.asmx . Open this page by double - clicking on it
from the Solution Explorer window and perform the following modifi cations:
• Change
CodeBehind= " ~ /App_Code/WebServiceSQLUpdateDelete.cs "
to
CodeBehind= " ~ /App_Code/WebServiceOracleUpdateDelete.cs " .
• Change

menu to open the Add Reference dialog box. Browse down along the list until you fi nd
the item System.Data.OracleClient , click to select it and then click on the OK button
to add it into our project.
Now double - click on our code - behind page WebServiceOracleUpdateDelete.cs to
open it. On the opened page, add the Oracle namespace to the namespace area on this
page.
using System.Data.OracleClient;
Also change the name of our Web Service class, which is located after the accessing
mode public class , from WebServiceSQLUpdateDelete to WebServiceOracle
UpdateDelete . Perform the same modifi cation to the constructor ’ s name of this class.
Next we will perform the necessary modifi cations to four Web methods developed
in this Web Service project combined with those fi ve differences listed above.
9.12.4 Modify Web Method SQLUpdateSP and Related Methods
The following issues are related to this modifi cation:
1. The name of this Web method and the name of the returned data type class
2. The content of the query string used in this Web method
3. The stored procedure used in this Web method
4. The names of the data components used in this Web method
5. The user - defi ned SQLConn() and ReportError() methods
6. The names of the dynamic parameters
Let ’ s perform those modifi cations step by step according to this sequence. Open the
Web method SQLUpdateSP() and perform the modifi cations shown in Figure 9.138 to
this method.
Let ’ s take a closer look at these modifi cations to see how they work.
A. Rename this Web method OracleUpdateSP and change the name of the returned class
to OracleBase .
B. Modify the content of the query string by changing the name of the stored procedure from
dbo.WebUpdateCourseSP to UpdateCourse_SP . The former is an SQL Server stored
procedure and the latter is an Oracle stored procedure that will be developed in the next
section.

Oracle
UpdateDelete()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public
Oracle
Base
Oracle
UpdateSP(string FacultyName, string CourseID, string Course, string Schedule,
string Classroom, int Credit, int Enroll)
{
string cmdString = "
UpdateCourse_SP
";

Oracle
Connection
ora
Connection = new
Oracle
Connection();

Oracle
Base
Oracle
Result = new
Oracle

Result);
return null;
}

ora
Command.Connection =
ora
Connection;

ora
Command.CommandType = CommandType.StoredProcedure;

ora
Command.CommandText = cmdString;

ora
Command.Parameters.Add("
FacultyName
",
Oracle
Type.
VarChar
).Value = FacultyName;

ora
Command.Parameters.Add("
inCourseID
",
Oracle
Type.Char).Value = CourseID;

Oracle
Type.Int
32
).Value = Credit;

ora
Command.Parameters.Add("
inEnroll
",
Oracle
Type.Int
32
).Value = Enroll;
intUpdate =
ora
Command.ExecuteNonQuery();

ora
Connection.Close();

ora
Command.Dispose();
if (intUpdate == 0)
{

Oracle
Result.
Oracle
Error = "Data updating is failed";
ReportError(

type for the last two input parameters from SqlDbType.Int to OracleType.Int32 . The
prefi x in for the last six parameters matchs the input parameters used for the stored pro-
cedure UpdateCourse_SP().
H. Change the prefi x from
sql
to
ora
for all data objects.
I. Change the name of the returned instance from SQLResult to OracleResult and change
the second member data from SQLError to OracleError .
Your modifi ed Web method OracleUpdateSP() is shown in Figure 9.138 . All modifi ed
parts have been highlighted in bold.
Next let ’ s perform modifi cations to two related user - defi ned methods SQLConn()
and ReportError().
Perform the following modifi cations to the SQLConn() method:
A. Change the name of this method from SQLConn to OracleConn and return class name
from SqlConnection to OracleConnection . Also change the connection string from
sql_
conn
to
ora_conn
.
B. Change the data type of the returned connection object to OracleConnection .
Perform the following modifi cations to the ReportError() method:
C. Change the data type of the passed argument from SQLBase to OracleBase .
D. Change the name of the fi rst member data from SQLOK to OracleOK .
E. Change the name of the second member data from SQLError to OracleError .
Your modifi ed methods OracleConn() and ReportError() should match that shown
in Figure 9.139 . All modifi ed parts have been highlighted in bold.
Next let ’ s develop the stored procedure UpdateCourseSP to perform the course

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.12 Build ASP.NET Web Service to Update and Delete Data for Oracle Database

877
9.12.4.1 Develop Stored Procedure Update C ourse_ SP
A very detailed discussion of creating and manipulating packages and stored procedures
in Oracle database is provided in Section 5.20.3.5 in Chapter 5 . Refer to that section to
get more detailed information for creating Oracle ’ s stored procedures.
The topic we discuss in this section is to update and delete data in the database.
Therefore no returned data is needed for these two data actions. We only need to create
stored procedures in Oracle database, not packages, to perform the data updating and
deleting function.
As discussed in Section 5.20.3.6 in Chapter 5 , different methods can be used to create
Oracle ’ s stored procedures. In this section, we will use the Object Browser page provided
by Oracle Database 10g XE to create our stored procedures.
Open the Oracle Database 10g XE home page by going to Start|All Programs|Oracle
Database 10g Express Edition|Go To Database Home Page items. Finish the login
process by entering the correct username and password such as CSE_DEPT and reback .
Click on the Object Browser and select Create|Procedures item to open the Create
Procedure window. Click on the Create button and select the Procedure icon from the
list to open this window.
Enter UpdateCourse_SP into the Procedure Name box and keep the Include
Argument checkbox checked, and click on the Next button to go to the next page.
The next window is used to allow us to enter all input parameters. For this
stored procedure we need to perform two queries. Therefore we have seven input
parameters. The fi rst query is to get the
faculty_id
from the Faculty table based
on the faculty name that is an input and selected by the user. The second query


Chapter 9 ASP.NET Web Services
attached after the procedure header to indicate that an intermediate query result,

faculty_id
, will be held by a local variable
facultyID
declared later.
Two queries are included in this procedure. The fi rst query is used to get the

faculty_id
from the Faculty table based on the input parameter FacultyName , and
the second query is to update a course record based on six input parameters in the Course
table. A semicolon must be attached after each PL - SQL statement.
One important issue is that you need to create one local variable
FacultyID
and
attach it after the IS command as shown in line 9 in Figure 9.142 , and this coding line has
been highlighted with shading. Click on the Edit button to add this local variable with its
data type of VARCHAR2(10) . This local variable is used to hold the returned
faculty_
id
from the execution of the fi rst query.
Another important issue in arranging the input parameters or arguments in the
UPDATE command is that the order of those parameters or arguments must be identical
with the order of the columns in the associated data table. For example, in the
Course table, the order of the data columns is course_id, course, credit, classroom,
schedule, enrollment , and faculty_id . Accordingly, the order of input parameters

Figure 9.140

course_id
, from the Course table based on the input
faculty name. This Web method will be called or consumed by a client project later to
get back and display all
course_id
in a listbox control in the client project.
Recall that in Section 5.19.2.5 in Chapter 5 , we developed a joined - table query to
perform the data query from the Course table to get all
course_id
based on the faculty
name. The reason for that is because there is no faculty name column available in the
Course table, and each course or
course_id
is related to a
faculty_id
in the Course
table. In order to get the
faculty_id
that is associated with the selected faculty name,
one must fi rst go to the Faculty table to perform a query to obtain it. In this situation, a
join query is a desired method to complete this function.
Open this Web method and perform the following modifi cations that are shown in
Figure 9.143 to this method. All modifi ed parts have been highlighted in bold.
c09.indd 879c09.indd 879 2/11/2010 3:02:49 PM2/11/2010 3:02:49 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
880

Chapter 9 ASP.NET Web Services
Let ’ s take a closer look at these modifi cations to see how they work.
A. Change the name of this Web method from GetSQLCourse to GetOracleCourse . Also

Base Get
Oracle
Course(string FacultyName)
{
string cmdString = "SELECT Course.course_id FROM Course, Faculty " +
"WHERE (Course.faculty_id = Faculty.faculty_id) AND (Faculty.faculty_name =: fname)";

Oracle
Connection
ora
Connection = new
Oracle
Connection();

Oracle
Base
Oracle
Result = new
Oracle
Base();

Oracle
Command
ora
Command = new
Oracle
Command();

Oracle
DataReader

Connection;

ora
Command.CommandType = CommandType.Text;

ora
Command.CommandText = cmdString;

ora
Command.Parameters.Add("
fname
",
Oracle
Type.
VarChar
).Value = FacultyName;

ora
Reader =
ora
Command.ExecuteReader();
if (
ora
Reader.HasRows == true)
FillCourseReader(ref
Oracle
Result,
ora
Reader);
else

H
WebServiceOracleUpdateDelete
GetOracleCourse()

Figure 9.143
Modifi ed Web method GetOracleCourse.
c09.indd 880c09.indd 880 2/11/2010 3:02:50 PM2/11/2010 3:02:50 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.12 Build ASP.NET Web Service to Update and Delete Data for Oracle Database

881
G. Change the names of two passed arguments to the method FillCourseReader() from
SQLResult to OracleResult and from sqlReader to oraReader .
H. Change the name of the returned instance from SQLResult to OracleResult , change the
second member data from SQLError to OracleError , and change the prefi x for all data
objects from
sql
to
ora
.
The modifi cations to the related user - defi ned FillCourseReader() method are rela-
tively simple. Perform the following modifi cations to this method:
A. Modify the data types of two passed arguments by changing the data type of the fi rst argu-
ment from SQLBase to OracleBase and changing the data type of the second argument
from SqlDataReader to OracleDataReader .
B. Change the method from GetSQLString(0) to GetOracleString(0) .
Your modifi ed user - defi ned FillCourseReader() method should match that shown in
Figure 9.144 . All modifi ed parts have been highlighted in bold. Next let ’ s modify another
Web method GetSQLCourseDetail().

pos++;
}
}
A
B
WebServiceOracleUpdateDelete FillCourseReader()

Figure 9.144
Modifi ed method FillCourseReader.
c09.indd 881c09.indd 881 2/11/2010 3:02:50 PM2/11/2010 3:02:50 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
882

Chapter 9 ASP.NET Web Services
B. Modify the content of the query string and make it equal to the name of the Package we
developed in the Section 9.10.7 . Change this query string from dbo.WebSelectCourseSP
to WebSelectCourseSP.SelectCourse . The prefi x WebSelectCourseSP is a Package
and the SelectCourse is a stored procedure.
C. Change the prefi x of all data classes from Sql to Oracle and from
sql
to
ora
for all data
objects used in this method. Also change the name of the returned instance from SQLResult
to OracleResult . Change the fi rst member data from SQLOK to OracleOK .
D. Add two OracleParameter objects paramCourseID and paramCourseInfo . Because some
differences exist between the SQL Server and Oracle databases, we need to use different
ways to assign parameters to the Parameters collection of the Command object later.
E. Change the name of the user - defi ned method from SQLConn to OracleConn . Change the
second member data from SQLError to OracleError .

OracleParameter paramCourseID = new OracleParameter();
OracleParameter paramCourseInfo = new OracleParameter();

Oracle
Result.
Oracle
OK = true;

ora
Connection =
Oracle
Conn();
if (
ora
Connection == null)
{

Oracle
Result.
Oracle
Error = "Database connection is failed";
ReportError(
Oracle
Result);
return null;
}

paramCourseID.ParameterName = "CourseID";
paramCourseID.OracleType = OracleType.VarChar;
paramCourseID.Value = CourseID;

ora
Reader);
else
{

Oracle
Result.
Oracle
Error = "No matched course found";
ReportError(
Oracle
Result);
}

ora
Reader.Close();

ora
Connection.Close();

ora
Command.Dispose();
return
Oracle
Result;
}
A
B
C
D

and change the data type for the second argument from SqlDataReader to
OracleDataReader .
9.12.7 Modify Web Method SQLD elete SP
As we discussed in Section 7.1.1 in Chapter 7 , to delete a record from a relational data-
base, one needs to follow the operation steps listed below:
1. Delete records that are related to the parent table using the foreign keys from child tables.
2. Delete records that are defi ned as primary keys from the parent table.
In other words, to delete one record from the parent table, all records that are related
to that record as foreign keys and located at different child tables must be deleted fi rst.
In our case, in order to delete a record using the
course_id
as the primary key from
the Course table (parent table), one must fi rst delete those records using the
course_id

as a foreign key from the StudentCourse table (child table). Fortunately we have only
one child table related to our parent table in our sample database. Refer to Section 2.5
and Figure 2.5 in Chapter 2 to get a clear relationship description among different data
tables in our sample database.
From this discussion, it can be found that to delete a course record from our sample
database two deleting queries should be performed: The fi rst query is used to delete the
related records from the child table or the StudentCourse table, and the second query is
used to delete the target record from the parent table or the Course table. Of course, we
can place these two queries into a stored procedure WebDeleteCourseSP(), which we
will develop in the following section to perform this deleting action. However, recall that
in Section 2.11.3.5 in Chapter 2 , we set a one - to - many constraint relationship between
the Course and the StudentCourse tables with an On Delete Cascade mode (refer to
Figure 2.65 ). This mode means that all records with a
course_id
that is equal to a

to
ora
for all data
objects used in this method. Also change the name of the returned instance from SQLResult
to OracleResult . Change the fi rst member data from SQLOK to OracleOK .
D. Change the name of the user - defi ned method from SQLConn to OracleConn . Change the
second member data from SQLError to OracleError .
E. Modify the nominal name for the input parameter to the stored procedure by removing
the
@
symbol before the nominal name
CourseID
. Also change the data type of this input
parameter from SqlDbType.Text to OracleType.VarChar .
F. Change the name of the returned instance from SQLResult to OracleResult . Also change
the second member data from SQLError to OracleError and the prefi x from
sql
to
ora

for all data objects.
[WebMethod]
public
Oracle
Base
Oracle
DeleteSP(string CourseID)
{
string cmdString = "
WebDeleteCourseSP

Result.
Oracle
Error = "Database connection is failed";
ReportError(
Oracle
Result);
return null;
}

Oracle
Command
ora
Command = new
Oracle
Command(cmdString,
ora
Connection);

ora
Command.CommandType = CommandType.StoredProcedure;

ora
Command.Parameters.Add("
CourseID
",
Oracle
Type.
VarChar
).Value = CourseID;
intDelete =

Modifi ed Web method OracleDeleteSP.
c09.indd 884c09.indd 884 2/11/2010 3:02:50 PM2/11/2010 3:02:50 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.