Tài liệu Practical Database Programming With Visual C#.NET- P16 - Pdf 87

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

773
To save time and space, we can copy and modify an existing Web Service project
WebServiceSQLSelect we developed in the previous section as our new Web Service
project WebServiceSQLInsert.
9.4.1 Modify Existing Web Service Project
First, let ’ s create a new folder such as Chapter 9 in our root directory using Windows
Explorer, and then copy the WebServiceSQLSelect project from the folder DBProjects\
Chapter 9 located at the accompanying ftp site (see Chapter 1 ), and paste it into our new
created folder C:\Chapter 9 . Rename it to WebServiceSQLInsert and open this new
project to perform the following modiﬁ cations to this project:
1. Change the main Web Service page from WebServiceSQLSelect.asmx to
WebServiceSQLInsert.asmx in the Solution Explorer window.
2. Change the name of our base class from SQLSelectBase , which is located in the folder
App_Code, to SQLInsertBase in the Solution Explorer window.
3. Open Visual Studio.NET and our new project WebServiceSQLInsert, and then open our
entry page WebServiceSQLInsert.asmx by double - clicking on it, and change the compiler
directive from

CodeBehind= " ~ /App_Code/WebServiceSQLSelect.cs "

to CodeBehind= " ~ /App_Code/WebServiceSQLInsert.cs "
Also change the class name from

Class= " WebServiceSQLSelect "

based on the
course_id
. These methods are listed below:
1. Develop a Web method SetSQLInsertSP() to call a stored procedure to perform this new
course insertion.
2. Develop a Web method GetSQLInsert() to retrieve the new inserted course information
from the database using a joined table query.
3. Develop a Web method SQLInsertDataSet() to perform the data insertion by using multi-
query and return a DataSet that contains the updated Course table.
4. Develop a Web method GetSQLInsertCourse() to retrieve the detailed course information
based on the input
course_id
.
The reason we use two different methods to perform this data insertion is to try to
compare them. As you know, there is no faculty name column in the Course table, and
each course is related to a faculty member identiﬁ ed by the
faculty_id
column in the
Course table. In order to insert a new course into the Course table, you must ﬁ rst perform
a query to the Faculty table to get the desired
faculty_id
based on the selected faculty
name, and then you can perform another insertion query to insert a new course based
on that
faculty_id
obtained from the ﬁ rst query. The ﬁ rst Web method combines those
two queries into a stored procedure, and the third method uses a DataSet to return the
whole Course table to make this data insertion more convenient to the user.
The main code developments and modiﬁ cations are performed in our code - behind
page WebServiceSQLInsert.cs, that is, the most modiﬁ cations will be performed on the

publ
{
ic class WebServiceSQLInsert : System.Web.Services.WebService
public WebServiceSQLInsert()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public SQLInsertBase SetSQLInsertSP(string FacultyName, string CourseID, string Course, string Schedule,
string Classroom, int Credit, int Enroll)
{
string cmdString = "dbo.InsertFacultyCourse";
SqlConnection sqlConnection = new SqlConnection();
SQLInsertBase SetSQLResult = new SQLInsertBase();
SqlCommand sqlCommand = new SqlCommand();
int intInsert = 0;
SetSQLResult.SQLInsertOK = true;
sqlConnection = SQLConn();
if (sqlConnection == null)
{
SetSQLResult.SQLInsertError = "Database connection is failed";
ReportError(SetSQLResult);
return null;
}
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = cmdString;
sqlCommand.Parameters.Add("@FacultyName",
SqlDbType.Text).Value = FacultyName;

WebServiceSQLInsert SetSQLInsertSP()

Figure 9.43
Modiﬁ cation to the ﬁ rst Web method.
c09.indd 775c09.indd 775 2/11/2010 3:01:30 PM2/11/2010 3:01:30 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
776
Chapter 9 ASP.NET Web Services
@Credit, and @Enroll. All of these parameters will be input by the user as this Web
Service project runs.
Let ’ s take a closer look at these codes to see how they work.
A. The name of our Web Service class and the constructor of this class is changed to
WebServiceSQLInsert to distinguish it from the original items.
B. The Web method ’ s name is also changed to SetSQLInsertSP, which means that this Web
method will call a stored procedure to perform the data insertion action. Seven input
parameters are passed into this method as the new data for a new inserted course record.
The returned object should be an instance of our modiﬁ ed base class SQLInsertBase.
C. The content of the query string must be equal to the name of the stored procedure we
developed in Section 6.10.1.2 in Chapter 6 . Otherwise a possible running error may be
encountered as this Web Service is executed since the stored procedure is identiﬁ ed by its
name when it is called.
D. A returned object SetSQLResult is created based on our modiﬁ ed base class SQLInsert
Base; that is, no data is supposed to be returned for this data insertion action. However,
in order to enable our client project to get a clear feedback from executing this Web
Service, we prefer to return an object that contains the information indicating whether
this Web Service is executed successfully or not.
E. A local integer variable intInsert is declared, and this variable is used to stop the returned
value from calling the ExecuteNonQuery() method of the Command class, and that method
will run the stored procedure to perform the data insertion action. This returned value is
equal to the number of rows that have been successfully inserted into our database.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

777
M. Finally the instance of our base class, SetSQLResult, is returned to the calling procedure
to indicate the running result of this Web method.
At this point we have ﬁ nished the coding development and modiﬁ cation to this Web
method. Now we can run this Web Service project to test inserting new course informa-
tion to our sample database via this Web Service. However, before we can build and run
this project, we have to comment out all other Web methods we created in the project
WebServiceSQLSelect we developed in the previous sections since we have modiﬁ ed
some user - deﬁ ned classes such as SQLSelectBase that are still used in those Web methods
in this project. The Web methods to be commented out are:
• GetSQLSelectSP()
• GetSQLSelectDataSet()
After comment out these Web methods, now we can build and run this project to
test the data insertion action. Click on the Start Debugging button to run the project.
The built - in Web interface is shown in Figure 9.44 .
Click on the Web method SetSQLInsertSP to select it to open another built - in Web
interface to display input parameters window, which is shown in Figure 9.45 .
Enter the following parameters to this Web method:

• FacultyName Ying Bai
• CourseID CSE - 556
• Course Advanced Fuzzy Systems
• Schedule M - W - F: 1:00 - 1:55 PM
• Classroom TC - 315
• Credit 3
• Enroll 28

Input parameter interface.
c09.indd 778c09.indd 778 2/11/2010 3:01:32 PM2/11/2010 3:01:32 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

779
9.4.3.2 Develop Second Web Method Get SQLI nsert
The function of this Web method is to retrieve all
course_id
, which includes the original
and the new inserted
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

D. Initially we set the running status of our Web method to OK.
E. The user - deﬁ ned method SQLConn() is called to connect to our sample database. A
warning message is assigned to the member data in our returned object and the user -
deﬁ ned method ReportError() is executed to report this error if an error occurs for this
connection.
F. The Command object is initialized with appropriate properties such as the Connection
object, Command type, and Command text.
G. The real input parameter FacultyName is assigned to the dynamic parameter
@name
using
the Add() method.
[WebMethod]
public SQLInsertBase GetSQLInsert(string FacultyName)
{
string cmdString = "SELECT Course.course_id FROM Course JOIN Faculty " +
"ON (Course.faculty_id LIKE Faculty.faculty_id) AND (Faculty.faculty_name LIKE @name)";
SqlConnection sqlConnection = new SqlConnection();
SQLInsertBase GetSQLResult = new SQLInsertBase();
SqlCommand sqlCommand = new SqlCommand();
SqlDataReader sqlReader;
GetSQLResult.SQLInsertOK = true;
sqlConnection = SQLConn();
if (sqlConnection == null)
{
GetSQLResult.SQLInsertError = "Database connection is failed";
ReportError(GetSQLResult);
return null;
}
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;

Codes for our second Web GetSQLInsert method.
c09.indd 780c09.indd 780 2/11/2010 3:01:34 PM2/11/2010 3:01:34 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

781
H. The ExecuteReader() method is called to trigger the DataReader and perform the data
query. This method is a read - only method and the returned reading result is assigned to
the DataReader object sqlReader .
I. By checking the HasRows property of the DataReader, we can determine whether this
reading is successful or not. If this reading is successful ( HasRows = true ), the user - deﬁ ned
FillCourseReader() method, whose detailed codes will be discussed in Figure 9.48 , is called
to assign the returned
course_id
to each associated member data in our returned object
GetSQLResult.
J. Otherwise if this reading has failed, a warning message is assigned to our member data
SQLInsertError in our returned object and this error is reported by calling the user - deﬁ ned
ReportError() method.
K. A cleaning job is performed to release all data objects used in this Web method.
L. The returned object that contains all queried
course_id
is returned to the calling
procedure.
The detailed codes for our user - deﬁ ned FillCourseReader() method are shown in
Figure 9.48 .
The function of this piece of code is straightforward and without tricks. A
while

Figure 9.48
Codes for the FillCourseReader method.
c09.indd 781c09.indd 781 2/11/2010 3:01:34 PM2/11/2010 3:01:34 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
782
Chapter 9 ASP.NET Web Services
9.4.3.3 Develop and Modify Third Web Method SQLInsertDataSet
The function of this Web method is similar to the ﬁ rst one: to insert a new course into
the Course table based on the selected faculty member. The difference is that this Web
method uses multiquery to insert a new course record into the Course table and uses a
DataSet as the returned object. The returned DataSet contains the updated Course table
Figure 9.49
Running status of our Web Service project.
Figure 9.50
Running status of our Web Service project.
c09.indd 782c09.indd 782 2/11/2010 3:01:35 PM2/11/2010 3:01:35 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

783
that includes the new inserted data. The advantages of using a DataSet as the returned
object are:
1. Unlike Web methods 1 and 2, which are a pair of methods with the ﬁ rst used to insert data
into the database and the second used to retrieve the new inserted data from the database
to conﬁ rm the data insertion, method 3 contains both inserting data into and retrieving data
back functions. Later when a client project is developed to consume this Web Service,
methods 1 and 2 must be called together from that client project to perform both data
insertion and data validation jobs. However, method 3 has both data insertion and data

DataSet dsCourse = new DataSet();
int intResult = 0;
string FacultyID;
SetSQLResult.SQLInsertOK = true;
sqlConnection = SQLConn();
if (sqlConnection == null)
{
SetSQLResult.SQLInsertError = "Database connection is failed";
ReportError(SetSQLResult);
return null;
}
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT faculty_id FROM Faculty WHERE faculty_name LIKE @Name";
sqlCommand.Parameters.Add("@Name",
SqlDbType.Text).Value = FacultyName;
FacultyID = (string)sqlCommand.ExecuteScalar();
sqlCommand.CommandText = cmdString;
sqlCommand.Parameters.Add("@faculty_id", SqlDbType.Text).Value = FacultyID;
sqlCommand.Parameters.Add("@course_id", SqlDbType.Char).Value = CourseID;
sqlCommand.Parameters.Add("@course", SqlDbType.Text).Value = Course;
sqlCommand.Parameters.Add("@schedule", SqlDbType.Char).Value = Schedule;
sqlCommand.Parameters.Add("@classroom", SqlDbType.Text).Value = Classroom;
sqlCommand.Parameters.Add("@credit", SqlDbType.Int).Va
lue = Credit;
sqlCommand.Parameters.Add("@enrollment", SqlDbType.Int).Value = Enroll;
CourseAdapter.InsertCommand = sqlCommand;
intResult = CourseAdapter.InsertCommand.ExecuteNonQuery();
if (intResult == 0)
{

S
T
WebServiceSQLInsert
SQLInsertDataSet()

Figure 9.52
Codes for the Web SQLInsertDataSet method.
B. The data insertion query string is declared here. In fact, in total, we have three query strings
in this method. The ﬁ rst two queries are used to perform the data insertion, and the third
one is used to retrieve the new inserted data from the database to validate the data inser-
tion. For the data insertion, ﬁ rst we need to perform a query to the Faculty table to get
the matched
faculty_id
based on the input faculty name since there is no faculty name
c09.indd 784c09.indd 784 2/11/2010 3:01:41 PM2/11/2010 3:01:41 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

785
column available in the Course table and each course is related to a
faculty_id
. Second,
we can insert a new course record into the Course table by executing another query based
on the
faculty_id
obtained from the ﬁ rst query. The query string declared here is the
second string.
C. All data objects and variables used in this Web method are declared here, which include
the Connection, Command, DataAdapter, DataSet, and an instance of our base class

to make it ready to perform the second query; insert a new course record into the Course
table.
K. All seven input parameters to the INSERT command are initialized by assigning them with
the actual input values. The point to note is the data types of the last two parameters. Both

credit
and
enrollment
are integers therefore the data type SqlDbType.Int is used
for both of them.
L. The initialized Command object is assigned to the InsertCommand property of the
CourseDataAdapter.
M. The ExecuteNonQuery() method is called to perform this data insertion query to insert a
new course record into the Course table in our sample database. This method will return
an integer to indicate the number of rows that have been successfully inserted into the
database.
N. If this returned integer is zero, which means that no row has been inserted into the database
and this insertion has failed, a warning message is assigned to the member data
SQLInsertError, and our method ReportError() is called to report this error.
O. The third query string, which is used to retrieve all courses including the new inserted
courses from the database based on the input
faculty_id
, is assigned to the
CommandText property of the Command object.
P. The dynamic parameter
faculty_id
is initialized with the actual
faculty_id
obtained
from the ﬁ rst query as we did above.

• FacultyName Ying Bai
• CourseID CSE - 665
• Course Neural Network Systems
• Schedule T - H: 1:00 - 2:25 PM
• Classroom TC - 309
• Credit 3
• Enroll 32
Your ﬁ nished parameter dialog box should match the one shown in Figure 9.53 .
Click on the Invoke button to run this Web method to perform this new course inser-
tion. The running result is shown in Figure 9.54 .
All six courses, including the sixth course CSE - 665, which is the new inserted course,
are displayed in the XML format or tags in this running result dialog box.
A point to note is that you can only insert this new course record into the database
once, which means that after this new course has been inserted into the database, you
cannot continue to click on the Invoke button to perform another insertion with the same
course information since the data to be inserted into the database must be unique.
Click on the Close button located at the upper - right corner of this Web interface to
terminate our service. Next let ’ s develop our fourth Web method.

Figure 9.54
Running result of our third Web method.
c09.indd 787c09.indd 787 2/11/2010 3:01:43 PM2/11/2010 3:01:43 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
788
Chapter 9 ASP.NET Web Services
Figure 9.55
Codes for the stored procedure WebSelectCourseSP.
9.4.3.4 Develop Fourth Web Method Get SQLInsertCourse
The function of this method is to retrieve the detailed course information from the data-
base based on the input

and click on the OK button to run this
stored procedure. The running result is displayed in the Output window, which is shown
in Figure 9.56 .
One row is found and returned from the Course table in our sample database. To
view all returned columns, move the horizontal bar at the bottom of this dialog box to
the right. Our stored procedure works ﬁ ne.
Right - click on our database folder CSE_DEPT.mdf and select the item Close
Connection from the pop - up menu to close this database connection.
c09.indd 788c09.indd 788 2/11/2010 3:01:45 PM2/11/2010 3:01:45 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

789
9.4.3.4.2 Develop Codes to Call This Stored Procedure Now let ’ s develop the
codes for our fourth Web method GetSQLInsertCourse() to call this stored procedure to
perform the course information query. Open the code - behind page WebServiceSQLInsert.
cs and add the codes shown in Figure 9.57 into this page to create this Web method.

Figure 9.56
Running result of the stored procedure WebSelectCourseSP.
[WebMethod]
public SQLInsertBase GetSQLInsertCourse(string CourseID)
{
string cmdString = "dbo.WebSelectCourseSP";
SqlConnection sqlConnection = new SqlConnection();
SQLInsertBase GetSQLResult = new SQLInsertBase();
SqlDataReader sqlReader;
GetSQLResult.SQLInsertOK = true;
sqlConnection = SQLConn();

I
J
K
L
WebServiceSQLInsert GetSQLInsertCourse()

Figure 9.57
Codes for the Web method GetSQLInsertCourse.
c09.indd 789c09.indd 789 2/11/2010 3:01:46 PM2/11/2010 3:01:46 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
790

Chapter 9 ASP.NET Web Services
Let ’ s take a look at the codes in this Web method to see how they work.
A. The name of the Web method is GetSQLInsertCourse(), and it returns an instance of our
base class SQLInsertBase. The returned instance contains the detailed course
information.
B. The content of the query string is the name of the stored procedure we developed in the
last section. This is required if a stored procedure is used and called later to perform a data
query. This name must be exactly identical with the name of the stored procedure we
developed, otherwise a running error may be encountered since the stored procedure is
identiﬁ ed by its name when the project runs.
C. Some data objects such as the Connection and the DataReader are created here. Also a
returned instance of our base class is also created.
D. The user - deﬁ ned SQLConn() method is called to perform the database connection. A
warning message is displayed and reported using the ReportError() method if any error
is encountered during the database connection process.
E. The Command object is created with two arguments: query string and connection object.
The coding load can be reduced but the working load cannot when creating a Command
object in this way. As you know, the Command class has four kinds of constructors and

B
C
WebServiceSQLInsert FillCourseDetail()

Figure 9.58
Codes for the FillCourseDetail method.
c09.indd 790c09.indd 790 2/11/2010 3:01:47 PM2/11/2010 3:01:47 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

9.4 Build ASP.NET Web Service Project to Insert Data into SQL Server Database

791
each piece of course information to the associated member data deﬁ ned in our base class,
and an instance of this class will be returned as this method is done.
J. Otherwise, if this property returns
false
, which means that no row has been selected and
returned from our database, a warning message is displayed and reported using the
ReportError() method.
K. A cleaning job is performed to release all data objects used in this Web method.
L. Finally an instance of our base class SQLInsertBase, GetSQLResult that contains the
queried course detailed information, is returned to the calling procedure.
The codes for the FillCourseDetail() method are shown in Figure 9.58 .
Let ’ s take a closer look at this piece of code to see how it works.
A. Two arguments are passed into this method: The ﬁ rst one is our returned object, which
contains all member data, and the second one is the DataReader, which contains queried
course information. The point is that the passing mode for the ﬁ rst argument is passing by
reference, which means that an address of our returned object is passed into this method.
In this way, all modiﬁ ed member data that contain the course information in this returned
object can be returned to the calling procedure or our Web method — GetSQLInsert-

At this point, we have ﬁ nished developing jobs in our Web Service project on the
server side. In the following sections, we want to develop some professional Windows -
based and Web - based applications with beautiful graphic user interfaces to use the Web
Service application we developed in this section. Those Windows - based and Web - based
applications can be considered as Web Service clients.
A complete Web Service project WebServiceSQLInsert that contains all four Web
methods can be found at the folder DBProjects\Chapter 9 located at the accompanying
ftp site (see Chapter 1 ).
c09.indd 791c09.indd 791 2/11/2010 3:01:47 PM2/11/2010 3:01:47 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
792
Chapter 9 ASP.NET Web Services
9.4.4 Build Windows -Based Web Service Clients to Use
Web Services
To use the Web Service WebServiceSQLInsert we developed in the last section, we need
ﬁ rst to create a Web Service proxy class in our Windows - based or Web - based applica-
tions. Then we can create a new instance of the Web Service proxy class and execute the
desired Web methods located in that Web Service class to perform the data insertion
actions against our sample database via the Web server. The process of creating a Web
Service proxy class is equivalent to adding a Web reference to our Windows - based or
Web - based applications. We provided a detailed discussion on how to create a Web
Service proxy class in Section 9.3.10.1 . Refer to that Section to get more detailed informa-
tion in creating a proxy class. In order to save space, Sections 9.4.4.1 to 9.4.4.3.4 , which
provide a detailed discussion in how to build a Windows - based client project
WinClientSQLInsert to consume our Web Service project WebServiceSQLInsert, have
been moved to the accompanying ftp site with a ﬁ le named WinClientSQLInsert.pdf that
can be found from the folder DBProjects\Chapter 9\Doc that is located at the site ftp://
ftp.wiley.com/public/sci_tech_med/practical_database . For your convenience, a com-
pleted Windows - based client project, WinClientSQLInsert, has also been developed and
debugged, which can be found from the folder DBProjects\Chapter 9 at the same ftp

Nhờ tải bản gốc

Tài liệu, ebook tham khảo khác

Tài liệu Practical Database Programming With Visual C#.NET- P16 - Pdf 87

Tài liệu, ebook tham khảo khác

Học thêm