SQL Anywhere Studio 9 and Microsoft .NET

---

A whitepaper from iAnywhere Solutions, Inc., a subsidiary of Sybase, Inc.

Contents Introduction and Overview Making Data Available with ADO.NET Making Data Available with Web Services Taking Data Outside the Data Center with Synchronization Taking Data Outside the Data Center with Messaging Conclusion Legal Notice

^  Introduction and Overview

Software version  This paper was written for SQL Anywhere Studio version 9.0.2.

SQL Anywhere and Microsoft .NET are natural partners when it comes to building data-centric applications that extend the reach of business information.

SQL Anywhere is software that extends the reach of information to anywhere business takes place. With over 9 million deployed seats, it is a proven solution for data management, data synchronization, and messaging challenges.

Microsoft .NET is software that connects information, people, systems, and devices. It provides a common language runtime for providing run-time services, and class libraries that enable developers to interact with data.

.NET developers can add the powerful capabilities of SQL Anywhere to their applications by using .NET programming interfaces and web services. This paper introduces the combination of SQL Anywhere and .NET: it sketches the main features of both technologies and describes how SQL Anywhere builds on Microsoft .NET to provide a unique foundation for computing in server, desktop, mobile, embedded, and remote office environments.

A sketch of Microsoft .NET 

Microsoft .NET is a platform that allows applications to communicate and share data over Internet protocols, regardless of operating system, device, or programming language.

The .NET Framework is the programming model for the .NET environment, which is designed to simplify application development in distributed environments. There are two main components to the .NET Framework:

• Common Language Runtime        A managed, protected application execution environment. The runtime manages code when it is executed and handles tasks such as memory and thread management. A virtual machine executes the supported .NET languages, including C#, Visual Basic, and C++.

• Framework Class Libraries       A library of common classes that is available across all supported languages. Before .NET, there were different Windows APIs for each language. The ADO.NET classes for data access and the web service classes are particularly important for SQL Anywhere users.

The Microsoft .NET Compact Framework is a subset of the .NET Framework for smart devices. It delivers managed code for XML Web services to these devices, and also allows you to run secure applications on Microsoft-supported devices such as personal digital assistants (PDAs) and mobile phones.

A sketch of SQL Anywhere 

SQL Anywhere Studio is a data management and synchronization solution for server, desktop, mobile, embedded, and remote office environments. The key attributes of SQL Anywhere—small footprint, enterprise features, high performance out of the box, and ease of use—have made it the first choice for thousands of companies whose applications run outside the data center.

There are several components to SQL Anywhere:

• Adaptive Server Anywhere    A database management system that scales from handheld devices to enterprise-scale installations, Adaptive Server Anywhere provides high out-of-the-box performance together with ease of use for developers and low or even zero administration.

• UltraLite    A database management system built specifically for mobile devices running Windows CE, Palm OS, or Windows XP, UltraLite provides a combination of performance and security while requiring very limited resources.

• MobiLink    A data synchronization technology that lets you share data between many Adaptive Server Anywhere or UltraLite databases and your back-office database, whether it be Sybase, Microsoft, Oracle, or IBM.

• QAnywhere messaging    A messaging platform for mobile and wireless clients as well as desktop and laptop clients, QAnywhere provides application-to-application messaging as well as notifications to end users.

SQL Anywhere Studio .NET integration 

SQL Anywhere Studio makes the most of the services provided by .NET to let developers add its capabilities to their applications.

• Making data available through .NET    SQL Anywhere implements ADO.NET namespaces that provide an interface to relational data.

  SQL Anywhere also provides web services, used by Microsoft .NET and other enterprise integration platforms as an XML-based mechanism for distributed access to information over Internet protocols.

• Moving data to where it is needed    SQL Anywhere provides MobiLink data synchronization for "always available" data access in occasionally connected environments. Synchronization logic can be implemented using .NET programming languages.

  SQL Anywhere databases can act as web service clients, meaning that they can collect and integrate information from many locations.

  SQL Anywhere includes QAnywhere messaging software that provides a .NET interface to application-to-application messaging for mobile computing.

The following sections outline each of the integration points between SQL Anywhere and Microsoft .NET. The paper is an overview of what is possible; for information on how to use these facilities, see the product documentation at http://www.ianywhere.com/developer/product_manuals/index.html.

^  Making Data Available with ADO.NET

This section describes how SQL Anywhere makes data available through ADO.NET.

A glance at ADO.NET 

ADO.NET is the latest data access API from Microsoft in the line of ODBC, DAO, RDO, OLE DB, and ADO. It is the preferred data access component for the Microsoft .NET Framework. .NET data providers provide access to data stores so that applications can retrieve and modify data from the data source. In ADO.NET, data providers (also called managed providers) are used to facilitate the integration of data with .NET applications.

Both the Adaptive Server Anywhere and UltraLite ADO.NET providers implement the following classes, which are the standard objects for database manipulation and management:

• Connection        connects to a data source

• Command        executes a command (SQL statements or stored procedure) against a data source

• DataReader        provides forward-only, read-only access to results of a command

• DataAdapter        fills a DataSet and handles updates to data

• Parameters        passes parameters to a Command object

• Transaction        provides COMMIT and ROLLBACK functionality

• Error, Exception        handles and collects error and/or warning messages

The Adaptive Server Anywhere ADO.NET Provider 

SQL Anywhere includes an ADO.NET provider for the Adaptive Server Anywhere database, which implements the iAnywhere.Data.AsaClient namespace. This provider is a native interface to the database, meaning that there is no intermediate bridging technology.

With the ADO.NET provider you can develop applications in any of the supported .NET languages using an interface based on the standard ADO.NET classes. You can deploy your applications to Windows computers with the .NET Framework or to Windows CE devices with the .NET Compact Framework.

The Adaptive Server Anywhere .NET Data Provider supports connection pooling, which allows your application to reuse existing connections rather than repeatedly creating a new connection to the database.

The UltraLite ADO.NET Provider 

The UltraLite ADO.NET Data Provider implements the iAnywhere.Data.UltraLite namespace. It makes the ADO.NET programming model available for UltraLite database access. You can deploy your applications to Windows computers or Windows CE devices with the .NET Compact Framework.

ADO.NET in a disconnected computing environment 

One of the primary objects in ADO.NET is the DataSet. The DataSet is a disconnected store for data retrieved from a database. It is a collection of DataTable, DataRow, DataColumn, and DataRelation objects. The DataSet is a generic object provided by Microsoft as part of the ADO.NET architecture. Using a provider's DataAdapter, you can fill a DataSet, modify the data in the DataSet, and then apply the changes to the database through the DataAdapter. The DataSet is independent of any managed provider or database driver and can be used to read and write either data or schema information in XML.

While both the Adaptive Server Anywhere and UltraLite ADO.NET providers support DataSets, they also provide a more robust and scalable mechanism for working with data offline. In this model, the database itself is the offline component, so that transactions are guaranteed to be atomic and so that other database characteristics such as referential integrity checking and correct recovery in the event of system failure are guaranteed. Changes you make can then be synchronized to a central consolidated database using MobiLink, the two-way data synchronization technology that is part of SQL Anywhere Studio. MobiLink can support thousands of remote databases and provides flexible conflict resolution capabilities and data partitioning among databases.

^  Making Data Available with Web Services

The Web Services architecture is an XML-based model for making logic and data available over standard Internet protocols. It complements ADO.NET as a data access architecture.

Adaptive Server Anywhere provides both the ability to build web services in the database and the ability to access external web services from the database. These web services features are included in the base product, and do not need add-on packages.

A sketch of the Web Services architecture 

A web service is application logic or data that can be accessed over networks by applications in different locations using a set of underlying standard Web protocols. Web service applications commonly communicate over HTTP or HTTPS. The requests and responses are formulated as messages using the Simple Object Access protocol (SOAP). A SOAP message is an XML document that provides an envelope in which specific information, also in XML form, can be contained. Web services are designed to be platform and language independent.

Web services describe themselves to potential consumers using another XML-based language, Web Services Description Language (WSDL). Web services can be located using Universal Description Discovery and Integration (UDDI), which is a registry for Web services. Neither WSDL nor UDDI are required for a web service, but they make it easier for programs to find (using UDDI) and use (using the WSDL information) a web service.

Making data available as XML 

When Adaptive Server Anywhere responds to a web service request, it does so by exposing result sets as XML. The result set can then be delivered inside a SOAP envelope. The ability of Adaptive Server Anywhere to return the result sets of SELECT statements and stored procedures as XML provides a foundation for web services.

The Adaptive Server Anywhere SELECT statement provides a FOR XML clause, which is a flexible and powerful mechanism for describing how to format the results of a query into XML. You can either take advantage of simple default behavior or use a customizable set of options that let you format the document exactly as you need.

In addition to exposing query results as XML, Adaptive Server Anywhere provides several other capabilities that let you work with data as XML:

• You can store data in the database using an XML data type.

• You can use the OPENXML function together with XPath expressions to generate a standard SQL result set from pieces of an XML document.

• You can import and export data as XML in bulk operations.

UltraLite databases provide a more restricted set of XML operations. The schema of an UltraLite database together with data from the database can be unloaded using the ulunload command line utility.

Adaptive Server Anywhere web services 

Adaptive Server Anywhere integrates an HTTP server and a SOAP request manager into the database server. This allows you to send SOAP requests to Adaptive Server Anywhere via HTTP, and then Adaptive Server Anywhere returns a response to the requesting HTTP client.

You can expose stored procedures or queries in your database as web services. As a very simple example, the following statement creates a web service that provides a list of the system tables:

CREATE SERVICE mytablesTYPE 'XML'AUTHORIZATION OFFUSER DBAAS SELECT * FROM SYSTABLE

The result is a single string containing an XML document that has a single element for each row of the result set, with values specified as attributes. Extensions to this very simple syntax ensure that issues of security, permissions, and formatting can be addressed.

Adaptive Server Anywhere provides several types of service in addition to the plain XML type in the example. SOAP services return result sets as SOAP responses. DISH services (Determine SOAP Handler) generate a WSDL for each of the services in a group. HTML returns an HTML document, and RAW allows you to completely customize the output.

Accessing web services from Adaptive Server Anywhere 

Adaptive Server Anywhere has a flexible stored procedure language which, together with triggers, events, Java stored procedures, and schedules lets you add business logic right in the database. The fact that Adaptive Server Anywhere databases can be copied from machine to machine lets you deploy your logic right along with the data.

This ability to encode logic in the database has been extended to web services. You can include a call to an external web service in a stored procedure definition, so that it can be invoked by client applications. For example, when a client application calls the following stored procedure it returns a document from the example.com:

CREATE PROCEDURE MyOperation (a INTEGER, b CHAR(128) )URL 'http://www.example.com/webservice'TYPE 'SOAP:DOC'

^  Taking Data Outside the Data Center with Synchronization

MobiLink is a session-based synchronization system that allows two-way synchronization between a main database, called the consolidated database, and many remote databases. Together with Adaptive Server Anywhere and UltraLite, it provides a powerful architecture for offline computing that links remote databases to a central consolidated database.

MobiLink synchronization logic is written as a set of synchronization scripts. Although these can be written as stored procedures in the SQL language of the consolidated database, or in Java, they can also be written in one of the .NET programming languages, such as C# or Visual Basic .NET using the iAnywhere.MobiLink.Script namespace.

Using .NET synchronization logic allows you to perform operations across database platforms, and provides portability across RDBMSs. With .NET synchronization logic, you can use MobiLink to access data from application servers, Web servers, and files. You can use iAnywhere classes in your synchronization logic to access data on the consolidated database's synchronization connection. For example, you can write a .NET script to use an external server to validate a user ID and password in the server's authenticate_user event.

Scripts also allow you to access and manipulate uploaded data in the consolidated database before it is committed. For example, you could reject a change before it is committed so that other remotes would not receive it. If you use an external program to access the data on the consolidated database, you cannot view or undo the update until it has been committed.

^  Taking Data Outside the Data Center with Messaging

Recent versions of SQL Anywhere Studio have included QAnywhere, a platform for building message-based applications. Message-based applications are a practical choice for mobile computing, where handheld or other remote devices may be disconnected for part of the time.

QAnywhere implements the iAnywhere.QAnywhere.Client namespace for building message driven applications on Windows CE devices. The interface allows synchronous and asynchronous sending and receiving of messages, including a transactional messaging feature for situations where transactional integrity is essential.

Together with data synchronization and web services, QAnywhere extends the flexibility and scope of SQL Anywhere to an even broader range of application architectures. The close integration of all these technologies with .NET provides Microsoft .NET developers with a powerful set of tools for mobile and database computing.

^  Conclusion

SQL Anywhere Studio provides a wide range of .NET support.

The ADO.NET interface is fully supported in both Adaptive Server Anywhere and UltraLite databases. Adaptive Server Anywhere can act both as a provider of web services and can access external web services and make them available to client applications. You can import, export, and store XML in your Adaptive Server Anywhere database and make existing data available in XML form.

MobiLink gives you full access to the functionality of the .NET Common Runtime Languages by allowing you to write your synchronization scripts in any of the supported .NET languages, and QAnywhere brings messaging to desktop and mobile devices with a .NET programming interface.

^  Legal Notice

Copyright © 2005 iAnywhere Solutions, Inc. All rights reserved. Sybase, the Sybase logo, iAnywhere Solutions, the iAnywhere Solutions logo, Adaptive Server, MobiLink, and SQL Anywhere are trademarks of Sybase, Inc. or its subsidiaries. All other trademarks are property of their respective owners.

The information, advice, recommendations, software, documentation, data, services, logos, trademarks, artwork, text, pictures, and other materials (collectively, "Materials") contained in this document are owned by Sybase, Inc. and/or its suppliers and are protected by copyright and trademark laws and international treaties. Any such Materials may also be the subject of other intellectual property rights of Sybase and/or its suppliers all of which rights are reserved by Sybase and its suppliers.

Nothing in the Materials shall be construed as conferring any license in any Sybase intellectual property or modifying any existing license agreement.

The Materials are provided "AS IS", without warranties of any kind. SYBASE EXPRESSLY DISCLAIMS ALL REPRESENTATIONS AND WARRANTIES RELATING TO THE MATERIALS, INCLUDING WITHOUT LIMITATION, ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT. Sybase makes no warranty, representation, or guaranty as to the content, sequence, accuracy, timeliness, or completeness of the Materials or that the Materials may be relied upon for any reason.

Sybase makes no warranty, representation or guaranty that the Materials will be uninterrupted or error free or that any defects can be corrected. For purposes of this section, 'Sybase' shall include Sybase, Inc., and its divisions, subsidiaries, successors, parent companies, and their employees, partners, principals, agents and representatives, and any third-party providers or sources of Materials.

^  Contact Us

iAnywhere Solutions Worldwide Headquarters    One Sybase Drive, Dublin, CA, 94568 USA

Phone    1-800-801-2069 (in US and Canada)

Fax    1-519-747-4971

World Wide Web     http://www.sybase.com/ianywhere/

E-mail     contact.us@ianywhere.com

Copyright © 2005 iAnywhere Solutions, Inc. All rights reserved.