Return Sql Data From A Web Service

Having the ability to access databases from a Web Service enables you to build applications that require data from various sources. Originating data from a SQL data store is one way to provide interoperability between applications. The SQL data can be central data storage for any application that can connect and issue commands against the SQL data store. This shared data is one way to enable applications to interact with each other.

There are a number of steps that you have to take to provide SQL Data from your Web Service. The first is to add the SQL.Data and the SQL.Data.SQLClient aliases so you can work with the objects in those namespaces. The SQL.Data.SQLClient is optimized to Microsoft SQL Server databases; if you want access to other SQL data stores, you can reference the SQL.Data.OleDb namespace. You can then create a Web Method that will return a DataSet object. In the Web Method, you create a connection to the database, retrieve the data using a SQL statement, and return the data that was retrieved.

When creating Web Services that access data, you need to also consider what parameters you need to return the data (for example, primary keys). To learn more about working with data access, see page 126.

RETURN SQL DATA FROM A WEB SERVICE

RETURN SQL DATA FROM A WEB SERVICE

D Open the WebServices Template.asmx template from the Code Templates directory.

0 Add an alias for the System.Data and System.Data.SqlClient namespaces.

L-Q Add a WebMethod that returns the DataSet class.

0 Create a new SqlConnection object to connect to the database and initialize the connection with the connection string to connect to the Pubs database.

D Open the WebServices Template.asmx template from the Code Templates directory.

0 Add an alias for the System.Data and System.Data.SqlClient namespaces.

L-Q Add a WebMethod that returns the DataSet class.

0 Create a new SqlConnection object to connect to the database and initialize the connection with the connection string to connect to the Pubs database.

WORK WITH WEB SERVICES

The task returned the business titles as a Web Service. You can also provide a Web Service that takes in the type of book and returns the SQL data for that type of book.

TYPE THIS:

<%@ WebService Language="C#" Class="SQLDataWebService_ai" %>

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.Services;

public class SQLDataWebService_ai {

[webMethod] public DataSet GetTitles(string stringTitleType) { stringTitleType = stringTitleType.ToLower(); if (stringTitleType != "trad_cook" & stringTitleType != "mod_cook" & stringTitleType != "business") stringTitleType = "business"; string stringSQLStatement = "select title, notes, price " +

"from titles where type='" + stringTitleType + ;

SqlConnection sqlconnectionPubs = new SqlConnection

("server=(local)\\NetSDK;uid=QSUser;pwd=QSPassword;" + "database=pubs"); SqlDataAdapter sqldataadapterTitles = new SqlDataAdapter

(stringSQLStatement, sqlconnectionPubs); DataSet datasetTitles = new DataSet(); sqldataadapterTitles.Fill(datasetTitles, "Titles"); return datasetTitles;

RESULT:

This produces a Web Service that returns a DataSet for the type of title requested.

'—0 Create a new SqlDataAdapter variable that uses the

SqlConnection object and a SQL string for retrieving business titles.

□ Create a DataSet object.

□ Fill the DataSet object using the SqlDataAdapter object.

0 Return the DataSet object.

, Save the file and test the Web Service.

■ The SQL data returns from the Web Service.

■ Note the SQL data in the response.

RK WITH THE SESSION OBJECT N A WEB SERVICE

Working with the Session object in a Web Service gives your Web Services the capability to have variables that can be used across different requests to Web Services during the same user session.

The process for using the Session object is simple. When you declare the Web Method that uses Session, you need to specify that Session is enabled. You do this by adding (EnableSession = true) just after WebMethod. By default, WebMethods do not have Session enabled. After enabling the session, you can access the Session object.

You can use Session to track state from page to page requests for a particular user. You can work with the Session object in different states such as New Session, Existing Session, and Abandoned Session.

You can use New session when the user does not have an existing valid session. New session enables you to initialize the Session object by setting any Session variables to any initial value. You can use Existing Session on subsequent requests to update or access Session variables. You can use Abandoned Session when the Session times out or you abandon the session. You will need to perform any cleanup necessary that is associated with the Session object. For more information on working with the Session object, see page 226.

WORK WITH THE SESSION OBJECT IN A WEB SERVICE

WORK WITH THE SESSION OBJECT IN A WEB SERVICE

D Open the WebServices Template.asmx template from the Code Templates directory.

0 Rename the class to SessionWebService.

'-0 Add a WebMethod that returns a string variable with the Session state enabled.

□ Add an if statement to initialize the Session variable.

D Open the WebServices Template.asmx template from the Code Templates directory.

0 Rename the class to SessionWebService.

'-0 Add a WebMethod that returns a string variable with the Session state enabled.

□ Add an if statement to initialize the Session variable.

I With the session Web Service, you can retain state across requests to a Web Service.

TYPE THIS:

<%@ WebService Language="C#" Class="SessionWebService_ai" %> using System;

using System.Web.Services;

public class SessionWebService_ai : WebService {

[ WebMethod(EnableSession=true) ] public String RememberName(string stringName) { string stringPreviousName = ""; string stringCurrentName = "";

if (Session["sessionName"] == null) { stringPreviousName = "Null";

stringPreviousName = Session["sessionName"].ToString();

Session["sessionName"] = stringName; stringCurrentName = stringName;

return "The previous value for the session variable was " +

stringPreviousName + ". The new value for the session variable is + stringCurrentName + " ." ;

RESULT:

This produces a Web Service that returns information on the current request and preceding request.

'—0 Add an else statement to increment the Session variable by one.

0 Return a formatted message to the user using the Session variable.

Q Save the file and test the Web Service.

-■ The Web Service returns a message about the number of times the service has been accessed.

Q Refresh the Web page.

-■ The number of times the service has been accessed is incremented.

WORK WITH THE APPLICATION OBJECT IN A WEB SERVICE

ou can work with the Application object in a Web Service to enable your Web Services to use variables across all requests to Web Services.

The Application object does not require Session to be enabled, so you can either leave the Web Method definition as the default or add the EnableSession=false statement to explicitly disable Session. Application data is available in your ASP.NET applications.

In many ways, working with the Application object is similar to working with the Session object. You have the ability to initialize Application variables when the Application object is accessed and there is not an existing Application object. You can access or update Application variables each time a page is requested. You also have the ability to clean up objects or other memory when the Application shuts down.

You want to be aware that all issues associated with the Application object are applicable when using the Application object in Web Services. For example, you need to lock Application variables to ensure the serial access to Application variables. To learn more about working with the Application object, see page 222.

WORK WITH THE APPLICATION OBJECT IN A WEB SERVICE

WORK WITH THE APPLICATION OBJECT IN A WEB SERVICE

D Open the WebServices Template.aspx template from the Code Templates directory.

0 Rename the class to ApplicationWebService.

-0 Add a WebMethod that returns a string variable with the Session state disabled.

'—Q Add an if statement to initialize the Application variable.

D Open the WebServices Template.aspx template from the Code Templates directory.

0 Rename the class to ApplicationWebService.

-0 Add a WebMethod that returns a string variable with the Session state disabled.

'—Q Add an if statement to initialize the Application variable.

To prevent corruption of your Application variables, it is a good idea to lock the Application variable before you update it.

TYPE THIS:

<%@ WebService Language="C#" using System-

using System.Web.Services;

Class="ApplicationWebService_ai" %>

public class ApplicationWebService_ai : WebService { [ WebMethod(EnableSession=false)] public String UpdateAppCounter() {

(Application["applicationHitCounter"] == null) Application.Lock();

Application["applicationHitCounter"] = 1; Application.UnLock();

Application.Lock();

Application["applicationHitCounter"] =

((int) Application["applicationHitCounter"]) + 1; Application.UnLock();

return "This service has been accessed 11 +

Application["applicationHitCounter"].ToString() +

RESULT:

This produces a Web Service that returns the current hit count of a Web Service. Note that the updating of the

Application variable, which keeps track of the count, is locked during the update.

—0 Add an else statement to increment the Application variable by one.

0 Return a formatted message to the user using the Application variable.

Q Save the file and test the Web Service.

0 Refresh the Web page.

—0 Add an else statement to increment the Application variable by one.

0 Return a formatted message to the user using the Application variable.

Q Save the file and test the Web Service.

■ The Web Service returns a message about the number of times the service has been accessed.

0 Refresh the Web page.

■ The number of times the service has been accessed is incremented.

Was this article helpful?

0 0

Post a comment