9.5 Build ASP.NET Web Service to Update and Delete Data for SQL Server Database

823

Figure 9.86
Running result for the Web method GetSQLCourse.

Figure 9.87
Parameter - input Web interface.
c09.indd 823c09.indd 823 2/11/2010 3:02:07 PM2/11/2010 3:02:07 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
824
Chapter 9 ASP.NET Web Services
Figure 9.89
Parameter - input interface.
Figure 9.88
Running result of the Web method GetSQLCourseDetail.
To confi rm this data deleting, close the current running result interface shown in
Figure 9.90 and click on the Back button to return to the home page of the Web Service
project. Click on the Web method GetSQLCourse to run it to pick up all courses taught
by the selected faculty
Ying Bai
. Enter the faculty name
Ying Bai
into the Value box
as the input parameter to this method and click on the Invoke button to run it. The
running result is shown in Figure 9.91 .
From the running result shown in Figure 9.91 , it can be found that the course with
the
course_id

played, which is shown in Figure 9.92 . The following returned values are displayed for
two member data:
• SQLOK:
false
• SQLError: No matched course found
This is identical with the warning message displayed in the message box as this
method runs. Close the current page and our Web Service project. Our Web Service
project is very successful.
As a reminder, it is highly recommended to recover all deleted data from all tables
in our sample database. To do that, open our sample database and the Course table from
either the Server Explorer in Visual Studio.NET or Microsoft SQL Server Management
Figure 9.92
Running result of the Web method GetSQLCourseDetail.
c09.indd 826c09.indd 826 2/11/2010 3:02:11 PM2/11/2010 3:02:11 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.7 Build Web-Based Web Service Clients to Use Web Services

827
Studio, and add all pieces of course information shown in Table 9.6 in Section 9.5.3.2 in
this chapter for the deleted course CSE - 526 into our Course table.
You can remove all message box methods MessageBox() from this Web Service
project to speed up the execution of this Web Service if you like. A completed Web
Service project WebServiceSQLUpdateDelete can be found at the folder DBProjects\
Chapter 9 located at the accompanying ftp site (see Chapter 1 ).
Next let ’ s take care of building some Windows - based and Web - based client projects
to use this Web Service.
9.6 BUILD WINDOWS - BASED WEB SERVICE CLIENTS TO
USE WEB SERVICES
In order to save space, Section 9.6, which provided a detailed discussion in how to build

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
828
Chapter 9 ASP.NET Web Services
c. Remove the TextChanged event method of the Course ID textbox since we do not need
this event and its event method in this application.
d. Modify the codes inside the Page_Load() method.
e. Develop the codes for the Update button ’ s Click method.
f. Develop the codes for the Delete button ’ s Click method.
g. Modify the codes in the Select button ’ s Click method and the related methods such as
ProcessObject() and FillCourseListBox().
h. Modify the codes in the SelectedIndexChanged event method of the course listbox
control and the related method FillCourseDetail().
Now let ’ s start these modifi cations with the fi rst step listed above.
9.7.1 Create New Website Project and Add Existing Web Page
Open Visual Studio.NET and go to the File|New Web Site menu item to create a new
website project. Enter C:\Chapter 9\WebClientSQLUpdateDelete into the name box
that is next to the Location box, and click on OK to create this new project.
On the opened new project window, right - click on our new project icon
WebClientSQLUpdateDelete from the Solution Explorer window, and select the item
Add Existing Item from the pop - up menu to open the Add Existing Item dialog box.
Browse to our Web project WebClientSQLInsert, select it, and then click on the Add
button to open all existing items for this website project. Select both items, Course.aspx
and Course.aspx.cs , from the list and click on the Add button to add these two items
into our new website project.
9.7.2 Add Web Service Reference and Modify
Web Form Window
To add a Web reference of our Web Service to this new Website project, right - click
on our new project icon from the Solution Explorer window and select the item
Add Web Reference from the pop - up menu. Now open our Web Service project
WebServiceSQLUpdateDelete and click the Start Debugging button to run it. As the

operations to delete some unused methods in our new project:
1. Remove the Insert button ’ s Click method cmdInsert_Click() since we do not need any data
insertion action in this application.
2. Remove the user - defi ned FillCourseDataSet() method since no DataSet method will be
used in this application.
3. Remove the TextChanged event method of the Course ID textbox since we do not need
this event and its event method in this application.
The fi rst code modifi cation is to change the codes in the Page_Load() method and
some global variables.

Figure 9.93
Finished Add Web Reference dialog box.
c09.indd 829c09.indd 829 2/11/2010 3:02:11 PM2/11/2010 3:02:11 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
830
Chapter 9 ASP.NET Web Services
9.7.3.1 Modify Codes in Page_Load Method
Perform the following changes to complete this modifi cation:
1. Remove the fi eld - level variables dsFlag and wsDataSet .
2. Change the name of the base class for the fi eld - level instance wsSQLResult from WS_
SQLInsert.SQLInsertBase to WS_SQLUpdateDelete.SQLBase .
3. In the Page_Load() method, remove the code that is used to add and display the second
Web method, DataSet Method , from the ComboMethod control.
Your modifi ed codes for the Page_Load() method should match that shown in Figure
9.95 . The modifi ed codes have been highlighted in bold. The next step is to develop the
codes for the Update button ’ s Click method.
9.7.3.2 Develop Codes for Update Button ’s Click Method
The function of this method is: When a faculty name is selected and all six pieces of
updating course information are entered in the six textbox controls, the updating course
information will be passed to the Web method SQLUpdateSP() we developed in our

ComboMethod.Items.Add("Stored Procedure Method");
}
}
Course Page_Load()

Figure 9.95
Modifi ed Page_Load method.
protected void cmdUpdate_Click(object sender, EventArgs e)
{
WS_SQLUpdateDelete.WebServiceSQLUpdateDelete wsSQLUpdate = new
WS_SQLUpdateDelete.WebServiceSQLUpdateDelete();
string errMsg;
try
{
wsSQLResult = wsSQLUpdate.SQLUpdateSP(ComboName.Text, txtCourseID.Text, txtCourseName.Text,
txtSchedule.Text, txtClassRoom.Text, Convert.ToInt32(txtCredits.Text), Convert.ToInt32(txtEnroll.Text));
}
catch (Exception err)
{
errMsg = "Web service is wrong: " + err.Message;
Response.Write("<script>alert('" + errMsg + "')</script>");
}
if (wsSQLResult.SQLOK == false)
Response.Write("<script>alert('" + wsSQLResult.SQLError + "')</script>");
}
A
B
C
D
E

data updating encountered some application error, and the error source stored in another
member data SQLError is displayed using the Java script function alert() .
In a similar way, we can develop the codes for the Delete button ’ s Click method to
perform the data deleting actions against our sample database.
9.7.3.3 Develop Codes for Delete Button ’ s Click Method
The function of this method is: When a
course_id
has been selected either from
the listbox control or from the Course ID textbox control in this client page window,
the selected course with a primary key that equals to that
course_id
will be deleted
from all tables, including the child and parent tables, from our sample relational
database.
Double - click on the Delete button from our client page window to open the Delete
Click method, and enter the codes shown in Figure 9.97 into this method.
Let ’ s take a closer look at this piece of code to see how it works.
A. A new instance of our Web proxy class, wsSQLDelete , is created, and this instance is used
to access the Web method SQLDeleteSP() we developed in our Web Service class
WebServiceSQLUpdateDelete to perform the data deleting action in our sample
database.
B. A local string variable errMsg is also created, and it is used to reserve the error source
that will be displayed as a part of an error message later.
C. A
try
…
catch
block is used to call the Web method SQLDeleteSP() with one piece of
course information,
course_id


9.7 Build Web-Based Web Service Clients to Use Web Services

833
D. An error message will be displayed if any error is encountered during that data deleting
action. Note the displaying format of this error message. To displayed a string variable
in a message box in the client side, one must use the Java script function alert() with
the input string variable as an argument that is enclosed and represented by
' " + input_string + " ' .
E. Besides the system error checking, we also need to check the member data SQLOK that
is defi ned in our base class in the Web Service project to make sure that this data deleting
is application error free. A returned
false
value of this member data indicates that this
data deleting encountered some application error and the error source stored in another
member data SQLError is displayed.
Go to the File|Save All menu item to save these modifi cations and developments.
Next let ’ s perform the code modifi cation to the Select button ’ s Click method.
9.7.3.4 Modify Codes in Select Button ’ s Click Method and Related Methods
The function of this method is: Either after a data updating or deleting action is per-
formed, we need to confi rm this operation by retrieving the related courses taught by the
selected faculty from our sample database. To do that, a desired faculty should be selected
from the Faculty Name combobox control, and the Select button should be clicked by
the user. Then this method will call the Web method GetSQLCourse() we developed in
our Web Service project, and an instance that contains all retrieved courses taught by the
selected faculty is returned from that Web method. Some user - defi ned methods are
executed to extract those courses from the returned instance and display them in the
listbox control in our client page window. Open this method and perform the modifi ca-
tions shown in Figure 9.98 to this method:
Let ’ s take a closer look at this piece of code to see how it works.

D
E
F
Course
cmdSelect_Click()

Figure 9.98
Modifi ed codes for the Select button Click method.
c09.indd 833c09.indd 833 2/11/2010 3:02:14 PM2/11/2010 3:02:14 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
834

Chapter 9 ASP.NET Web Services
only one method, stored procedure method, used in this application. Also remove all codes
between the
else
and
end if
half - block since we do not have the DataSet method used
in this project.
C. Change the instance name of our Web proxy class from wsSQLInsert to wsSQLSelect
and Web method ’ s name from GetSQLInsert() to GetSQLCourse() .
D. Change the name of the member data from SQLInsertOK to SQLOK .
E. Change the name of another member data from SQLInsertError to SQLError .
F. Remove the last two methods, FillCourseDataSet() and Application["dsFlag"] =
false , since we do not need these two operations in this application.
All modifi cation parts have been highlighted in bold.
Two user - defi ned methods are related to this Select button ’ s Click method, and they
are ProcessObject() and FillCourseListBox(). The modifi cations to these two methods
include the following steps:

int index = 0;
CourseList.Items.Clear(); //clean up the course listbox
for (index = 0; index <= sqlResult.CourseID.Length - 1; index++)
{
CourseList.Items.Add(sqlResult.CourseID[index]);
}
}
A
B
C
D
Course ProcessObject()

Figure 9.99
Modifi ed methods ProcessObject and FillCourseListBox.
c09.indd 834c09.indd 834 2/11/2010 3:02:14 PM2/11/2010 3:02:14 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.7 Build Web-Based Web Service Clients to Use Web Services

835
9.7.3.5 Modify Codes in Selected I ndex C hanged Method
Open the SelectedIndexChanged method of the listbox control CourseList and perform
the modifi cations shown in Figure 9.100 to this method. Let ’ s take a closer look at these
modifi cations.
A. Rename the new instance ’ s name to
wsSQLSelect
and change the Web proxy class ’ s
name to WS_SQLUpdateDelete.WebServiceSQLUpdateDelete .
B. Change the instance name of our Web proxy class from wsSQLInsert to wsSQLSelect

if (wsSQLResult.SQLOK == false)
Response.Write("<script>alert('" + wsSQLResult.SQLError + "')</script>");
FillCourseDetail(ref wsSQLResult);
}
A
B
C
D
Course CourseList_SelectedIndexChanged()

Figure 9.100
Modifi ed codes for the SelectedIndexChanged method.
c09.indd 835c09.indd 835 2/11/2010 3:02:14 PM2/11/2010 3:02:14 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
836
Chapter 9 ASP.NET Web Services
updating a course record CSE - 665. However, before we can do that, we prefer to retrieve
the current information for the course CSE - 665. Click on the Select button to get all
courses currently taught by the selected faculty member
Ying Bai
, and all
course_id
will be retrieved and displayed in the listbox control. Click on the course CSE - 665 from
the listbox control to get the detailed information for this course. Immediately the
detailed information related to course CSE - 665 is displayed in the associated textbox
control, which is shown in Figure 9.101 .
Now enter the following updating information for the course CSE - 665 into the associ-
ated textbox, which is shown in Figure 9.102 .
Now click on the Update button to call the Web method SQLUpdateSP() in our Web
service project to update this course record. To check whether the course CSE - 665 has


Chapter 9 ASP.NET Web Services
It is highly recommended to recover the deleted course CSE - 665 for our Course table
since we want to keep our database neat and complete. You can recover this data by
using one of the following fi ve methods:
1. Use the Server Explorer window in Visual Studio.NET to open our sample database
CSE_DEPT.mdf and our Course data table.
2. Use the Microsoft SQL Server Management Studio or Studio Express to open our sample
database CSE_DEPT.mdf and our Course data table.
3. Use our Web Service project WebServiceSQLInsert to insert a new course to perform this
course recovering.
4. Use our Windows - based Web Service client project WinClientSQLInsert to perform this
course recovering.
5. Use our Web - based Web Service client project WebClientSQLInsert to insert a new course
to recover this course record.
Relatively speaking, using the last three methods to recover this course information
is professional since normally no one wants to access and change the content of a database
directly by opening the database to do modifi cations.
Refer to Table 9.7 to recover the deleted course CSE-665.
A complete Web - based Web Service client project WebClientSQLUpdateDelete can
be found at the folder DBProjects\Chapter 9 at the accompanying ftp site (see Chapter 1 ).
At this point, we have fi nished the discussion about how to access and manipulate
data in the SQL Server database via ASP.NET Web Services. In the next section, we will
discuss how to access and manipulate data in the Oracle database via ASP.NET Web
Services.
9.8 BUILD ASP . NET WEB SERVICE PROJECT TO ACCESS
ORACLE DATABASE
Basically, the procedure to build an ASP.NET Web Service to access the Oracle database
is very similar to the procedure to build an ASP.NET Web Service to access the SQL
Server database. The main differences are:

which means that different Data Providers are needed to access the different databases.
For the Oracle database, ADO.NET provides the namespace System.Data.OracleClient
that contains all necessary data components to access and manipulate data stored in an
Oracle database. In other words, to use matched data components provided by the ADO.
NET to access an Oracle database, one must use the associated namespace to access those
data components.
Third, the prototype and structure of a stored procedure are different for the differ-
ent databases. To call a stored procedure to perform a data action against an SQL Server
database is totally different from calling a stored procedure to perform the similar data
action against an Oracle database.
For differences 4 and 5 listed above, it is clear that the format of a query string is
different when calling the different databases. Also the nominal name of the dynamic
parameter is distinguished when it is used for the different databases.
Based on the above discussion and analysis as well as the similarity between the
SQL Server and Oracle databases, we try to develop our Web Service projects to access
the Oracle database by modifying some existing Web Service projects in the
following sections. In this section, we concentrate on the modifi cations to the Web Service
project WebServiceSQLSelect and make it our new Web Service project
WebServiceOracleSelect.
9.8.1 Build Web Service Project Web S ervice O racle S elect
In this section, we try to modify an existing Web Service project WebServiceSQLSelect
to make it our new Web Service project WebServiceOracleSelect, and allow it to access
the Oracle database to perform the data selection queries.
Open Windows Explorer and create a new folder Chapter 9 under the root directory
if you have not done that, and then open Internet Explorer and browse to our desired
source Web Service project WebServiceSQLSelect located at the folder DBProjects\
Chapter 9 at the accompanying ftp site (see Chapter 1 ). Copy and paste this project into
our new folder Chapter 9. Rename it WebServiceOracleSelect. Perform the following
modifi cations to this project:
1. Change the main Web Service page from WebServiceSQLSelect.asmx to

the Solution Explorer window, and perform the following modifi cations:
• Change the class name and the constructor ’ s name from SQLSelectResult to
OracleSelectResult .
• Change the base class name (after the resolution operator : ) from SQLSelectBase to
OracleSelectBase .
9.8.2 Modify Connection String
Double - click on our Web confi guration fi le web.confi g from the Solution Explorer
window to open it. Change the content of the connection string that is under the tag
< connectionStrings > to:
< add name= " ora_conn " connectionString= " Server=XE;User
ID=CSE_DEPT;Password=reback; " / >
The Oracle database server XE is used for the server name, the User ID is our sample
database CSE_DEPT and the Password is determined by the user when adding a new
account to create a new user database.
9.8.3 Modify Namespace Directories
First, we need to add an Oracle Data Provider reference to our Web Service project. To
do that, right - click on our new project icon WebServiceOracleSelect from the Solution
Explorer window, and then select the item Add Reference from the pop - up menu to
c09.indd 840c09.indd 840 2/11/2010 3:02:19 PM2/11/2010 3:02:19 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.8 Build ASP.NET Web Service Project to Access Oracle Database

841
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 WebServiceOracleSelect.cs to open it.
On the opened page, add one more namespace line to the top of this page:
using System.Data.OracleClient;

symbol before the
nominal name
facultyName
. Also change its data type from SqlDbType.Text to
OracleType.VarChar .
H. Change the name of the returned instance from SQLResult to OracleResult and Change
the prefi x from sql to ora for all data objects.
Your modifi ed Web method GetOracleSelect() should match that shown in Figure
9.104 . All modifi ed parts have been highlighted in bold.
c09.indd 841c09.indd 841 2/11/2010 3:02:20 PM2/11/2010 3:02:20 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
842

Chapter 9 ASP.NET Web Services
Next let ’ s modify three user - defi ned methods: SQLConn(), FillFacultyReader(), and
ReportError(), which are related to the GetOracleSelect() method. Open these methods
and perform the modifi cations shown in Figure 9.105 .
Let ’ s talk a closer look at these modifi cations to see how they work:
A. Change the name of this method from SQLConn() to OracleConn() and the returning
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 .
C. Change the data type of the fi rst passed argument from SQLSelectResult to
OracleSelectResult . Also change the data type of the second passed argument from
SqlDataReader to OracleDataReader .
D. Change the data type of the passed argument from SQLSelectResult to OracleSelectResult .

OracleResult.OracleRequestError = "No matched faculty found";
ReportError(OracleResult);
}
oraReader.Close();
oraConnection.Close();
oraCommand.Dispose();
return OracleResult;
}
A
B
C
D
E
F
G
H
WebServiceOracleSelect
GetOracleSelect()

Figure 9.104
Modifi ed Web method GetOracleSelect.
c09.indd 842c09.indd 842 2/11/2010 3:02:20 PM2/11/2010 3:02:20 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.