Where Learning is Pleasure

Jayaram Krishnaswamy

Subscribe to Jayaram Krishnaswamy: eMailAlertsEmail Alerts
Get Jayaram Krishnaswamy: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: SOA Best Practices Digest, SOA & WOA Magazine


Consuming a SQL Anywhere Native Web Service Using a .NET Client

Without writing a single line of code

Enabling interoperability is one of the greatest benefits of using web services. In the business world, applications work on different platforms with different operating systems running applications whose programming languages vary wildly. For applications to interact with each other there should be a common method to transparently talk to all the different systems. In fact, the charter of the WS-I, an open industry organization source, is to promote this idea of interoperability across platforms, operating systems, and programming languages.

A service created by one business entity is discovered by another entity through a WSDL. WSDL is an acronym for Web Services Discovery Language, a construct that makes these interactions possible. In the present case, as the data type used for the service is specific to .NET (DNET) it would limit interoperability with non-.NET clients but should work well for .NET clients as demonstrated in this article.

It is easy to create a native web service with SQL Anywhere that can be accessed with an HTTP request. In this tutorial you will be creating a SOAP web service that can be consumed by an ASP.NET web client.

Part 1: Creating the Web Service
SQL Anywhere Server 10
Extensive documentation on this subject is available at SQL Anywhere Server's home page. You may download an evaluation version of the software and take it for a test drive. Sybase Central is a one stop, graphical database management interface to the database and its various supporting applications. We will be using this tool for some of the tasks in this tutorial.

Server and Database Details Used in This Tutorial
A copy of the demo database that ships with the evaluation version will be used.

Web Service Types
It is possible to create various types of web services. The following web services can be created using SQL Anywhere 10:

  • Raw: The result set of the SQL statement or procedure is sent to the client without any additional formatting.
  • XML: The result set of the SQL statement or procedure is assumed to be XML; if it is not, then the result set is converted to XML RAW format.
  • HTML: The result set of the SQL statement or procedure is formatted as an HTML document with a table containing the rows and columns.
  • SOAP: The request must be a valid SOAP (Simple Object Access Protocol) request and the result set is formatted as a SOAP response.
  • DISH: A DISH service acts as a proxy for a group of SOAP services and generates a WSDL (Web Services Description Language) file for each of its SOAP services.

Creating a Web Service Directly Using Interactive SQL
In this tutorial, we will be creating a web service that can be consumed by a client making SOAP requests to the server. Since we will be using a WSDL URL reference for the web service, we will also need to create a DISH service to automatically generate a proxy for the service. Therefore, for a Framework 2.0 client (or any client for that matter) to access the web service created on the SQL Anywhere server we will need two services: one SOAP service and one DISH service.

While the Web Service Wizard in Sybase Central is a nice tool to create web services, it is also possible to create the service directly using Interactive SQL.

Create a Web Service of Type "SOAP"
We will need to start both the demo database server as well as Sybase Central. The login credentials are (User ID: dba; password: sql). We start the database with the HTTP server enabled. NB - the -xs http(port=8082) portion of the command line is what starts the server in HTTP mode. You can start the demo database server with the following command:

C:\HTTPWebserverDemo> dbsrv10 -xs http(port=8082) -n SAWeb Server demo.db -n demo

This starts the server and an icon will be placed in the desktop tray.

After starting Sybase Central, use the same user ID and password to connect to the server. After connection, Sybase Central should display all the objects in the demo database as shown in Figure 1.

Double-click Web Services in the Folders list. This opens up the Web Services folder displaying the existing Web Services on this server as shown in Figure 2.

To create a new SOAP service, you could use the Create a web service item in the Web Service Design Tasks group. This would bring up the Create Web Service Wizard. However, instead we will use Interactive SQL to create the web service directly. To start Interactive SQL, choose Tools -> SQLAnywhere 10 ->Open Interactive SQL. Once Interactive SQL is displayed, type the script in the SQL Statements window as shown in Figure 3 and execute it.

This will create a web service with the name "DotNetTest/Customers" that is accessible by a SOAP request. When consumed, this web service returns the Customers information stored in the Customers table. In order to consume this web service from a client we need to have this web service's WSDL information. This is provided by a DISH web service as mentioned earlier.

Interactive SQL is also used for creating the DISH service. A single DISH service can provide the WSDL for a group of SOAP services, one of which is "DotNetTest/Customers" that was just created. The group designator is "DotNetTest", which is also used in creating this proxy as shown in Figure 4.

With these two services created, it is now possible to get the WSDL needed for configuring a .NET client to invoke the SOAP service on the SQL Anywhere database.

In addition, you should now be able to see the newly created web services in the Web Services folder in Sybase Central as shown in Figure 5.

The created WSDL will be used as web reference in Visual Studio for a client to begin making requests to this service.

Part 2: Creating a Web Service Client Using ASP.NET 2.0
Visual Studio allows you to create several different client types including clients that access the data using consoles, using a Windows application, and many more. In this example you will be creating a web client.

Creating a web client to access the service consists of the following steps:

  • Create a web site project.
  • Add a web reference.
  • Create a web form to display the results by retrieving the results and displaying in a GridView data-bound control.

Create a Website Project
In Microsoft Visual Studio 2005, click on File ->New -> Website in the main menu to create a new web site project. Use the website name http://localhost/SqlAny10Client for the created project.

Adding a Web Reference
Once the project is created, right-click on the project name in the Solution Explorer. From the dropdown that appears, click on Add Web Reference... as shown in Figure 6. This brings up the Add Web Reference wizard in Figure 7.

Make sure you have started the demo database server to accept HTTP requests as discussed earlier. With this you will have access to the web services created earlier in Part 1. The proxy for the web service DotNetTest/Customers is the service DotNetTest as explained earlier.

Type http://localhost:8082/demo/DotNetTest in the URL and click the Go button. You now see options similar to Figure 8. Since the SQL Anywhere web server is running you will see that it accesses this service with a method called "Customers". The return value is a SQLRowSet.

Now click on the Add Reference button. This creates the App_WebReferences folder to the website, which contains the ‘localhost' proxy containing the WSDL file shown in Figure 9. The WSDL file is required to access the SOAP service grouped under that proxy.

Adding a Web Form to Access the SOAP Service
Drag and drop a GridView control from the Toolbox into the design pane of default.aspx as shown in Figure 10.

Also drag and drop an ObjectDataSource component from the Toolbox onto the web form. The design pane will display these two components as shown in Figure 11.

The Smart tag attached to the ObjectDataSource displays the tasks that are needed to complete the configuration of this source.

Click on ConfigureDataSource... to bring up the Configure Data Source wizard as shown in Figure 12. Click the dropdown button and choose localhost.DotNetTest [Business Object Type] from the list. This adds localhost.DotNetTest to the Choose your business object box. Make sure you read the instructions on this page.

Click on the Next button to bring up the Configure Data Source wizard shown in Figure 13.

In this screen you have to specify the method that returns the data. The methods are accessible by clicking on the dropdown handle as shown. From the drop-down list pick the one shown highlighted in the figure above (Customers, returns DataSet).

Click the Next button to bring up the next wizard screen as shown in Figure 14.

Accept the defaults on this screen and click the Finish button. This completes the configuration of the ObjectDataSource.

Configuring the GridView
The GridView has its own set of tasks that need configuring. Click on the dropdown handle along the Choose Data Source task in the GridViewTasks as shown in Figure 15.

From the dropdown for Choose Data Source click select ObjectDataSource1. This will be the data source (it's the dataset) that the GridView will display when the application is run. This will bind the columns in the grid view to those in the data source.

Click to expand the GridView tasks allowing you to configure the layout of the GridView as shown in Figure 16.

You may place check marks in the boxes [Enable Paging and Enable Sorting] that will allow you to do sorting and paging. The display of the GridView can be improved by individually applying styles to the individual elements of the GridView using its properties window shown in Figure 17.

You may apply style by clicking on the AutoFormat... link at the bottom of the property window, which brings up canned styles that can be chosen for the grid to display as in Figure 18.

Choose any of the styles by scrolling down in the Auto Format window's Select a Scheme listing that also provides a preview for the chosen style. For this tutorial we've picked the Sand & Sky. Click on the OK button. The GridView will be updated immediately. The completed design view of the default.aspx is shown in Figure 19.

You are almost done. Build the project and browse the default.aspx page on the browser by highlighting project name and right-clicking on default.aspx and choosing View in Browser as shown in Figure 20.

This opens the default browser and displays the default.aspx page as shown in Figure 21.

You can move to the page you want using the hyperlinks at the bottom and the columns can be sorted by clicking on the column headings.

This tutorial described how the data in a SQL Anywhere database can be accessed by making a request to a SOAP web service. The web services were created directly using Interactive SQL. We also created an ASP.NET client using Visual Studio 2005 to access the native web service on SQL Anywhere. Not a single line of code needed to be written because we used Visual Studio 2005's excellent RAD tools and the excellent integration of SQL Anywhere with Visual Studio. This tutorial also demonstrates how well SQL Anywhere interoperates with the Microsoft Framework.


•   •   •

This article was reprinted with permission from the ISUG Journal.

More Stories By Jayaram Krishnaswamy

Jayaram Krishnaswamy is a technical writer, mostly writing articles that are related to the web and databases. He is the author of SQL Server Integration Services published by Packt Publishers in the UK. His book, 'Learn SQL Server Reporting Services 2008' was also published by Packt Publishers Inc, Birmingham. 3. "Microsoft SQL Azure Enterprise Application Development" (Dec 2010) was published by Packt Publishing Inc. 4. "Microsoft Visual Studio LightSwitch Business Application Development [Paperback] "(2011) was published by Packt Publishing Inc. 5. "Learning SQL Server Reporting Services 2012 [Paperback]" (June 2013) was Published by Packt Publishing Inc. Visit his blogs at: http://hodentek.blogspot.com http://hodentekHelp.blogspot.com http://hodnetekMSSS.blogspot.com http://hodnetekMobile.blogspot.com He writes articles on several topics to many sites.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.