How to connect, secure, retrieve, and update SQL Server data
By Mark D. Sutton, Content Master Ltd.
An application written for a platform that has wireless network access can connect to, and work with, enterprise data on a central Microsoft SQL Server* 2000 database using a number of different techniques. The challenge is to decide at the design stage which technique to use, how to do so securely, and what the expected performance will be like.
This paper examines two solutions for data access to SQL Server 2000, which can be implemented on a wireless client using the Microsoft .NET* Framework:
- Connecting directly to SQL Server through an 802.11 LAN or over a virtual private network (VPN) connection on the public Internet, using the SqlClient .NET Data Provider
- Accessing SQL Server data from a specially configured Web site using SQL-XML
Mobilized Software - Mobilized software contains a set of features designed to insulate users from network dependencies by making data and applications available when the user is offline. Upon re-connection to the network, information is automatically updated through a data synchronization process. This application architecture allows users to work productively while enjoying a mobile work and life style.
802.11 LAN - Local Area Network based on the IEEE 802.11 standard for wireless networks.
Virtual Private Network (VPN) - VPN allows business to provide remote network access for their employees by encrypting and validating passwords thereby reducing the risk of security attacks. The employee in the field can virtually log in to the company network and have some if not all of the same types of capabilities as when in physically the office.
Internet Information Services (IIS) - This is the name given to the Windows Web server.
Extensible Markup Language (XML) - A standard text format for the transmission of data and its structure.
Hypertext Transfer Protocol (HTTP) - The network transmission protocol used by the Web.
Tabular Data Stream (TDS) - A proprietary, low level format, used for communication between the SqlClient .NET Data Provider and SQL Server 2000.
Connecting directly to SQL Server through an 802.11 LAN or over a VPN gives your application good security, performance, and data updates from the server will be immediate. You have to administer logins to the database, but after your application is connected, data retrieval in the SQL Server proprietary format is very fast. Therefore, if you have no need to share the data with other applications, there is no requirement for your application to work off-line, and your data updates will not frequently be in conflict with other users, then connecting directly to SQL Server is a good choice.
Accessing SQL Server data from a specially configured Web site using SQL- XML uses the ‘open’ XML format for data transfer, so the data on the Web site can be used by any application able to process the XML. However the request to the Web site and the transmission of data as XML is slower than connecting and retrieving data directly from SQL Server. Therefore, if you need to be able to access your data in XML, or have one or more other applications that can also benefit from the data on the Web site, then SQL-XML is a good choice.
With both the above techniques, you will need to plan for problems arising from multiple users trying to update the same data concurrently.
Note that using SQL-XML is an alternative to accessing data from a Web site using XML Web Services, SQL Server CE Remote Data Access, or SQL Server CE Replication. For more information about these and other data access technologies, see the white paper Data Synchronization: Which Technology?
Before selecting a data access technology for a particular application, it is important to assess possible performance and security implications. The following sections enable you to make your assessment by showing you how to implement data access, retrieval, and updates in both the SqlClient .NET Data Provider and SQL-XML.
The SqlClient .NET Data Provider
The .NET Framework contains classes in the System.Data.SqlClient namespace to allow your program direct access to a SQL Server. These classes are implemented in the SqlClient .NET Data Provider component.
You might have assumed that solutions using the SqlClient classes are limited to applications that access SQL Server running on the corporate network behind a firewall. However, providing that you can get a network connection to the SQL Server, use of the SqlClient classes can be more widespread, and the fact that the SqlClient classes are implemented in both the full .NET Framework and the .NET Compact Framework means that you can also extend their use to mobile devices.
The diagram below shows the architecture of an application that uses the SqlClient .NET Data Provider component. Note that to optimize speed, the communication between the Provider and the SQL Server is in a proprietary, low-level format called Tabular Data Stream (TDS).
The following sections examine how to use the SqlClient .NET Data Provider to make a secure connection, retrieve some data, and perform a simple update.
Making a Connection Securely
Use the SqlConnection object to make a connection and to specify security options for the login to SQL Server. For a connection to work, the device running your application must already be connected to the network on which the SQL Server resides.
There are two security systems employed by SQL Server:
- SQL Server Security – logins and passwords are created in SQL Server
- Integrated Windows Security – SQL Server accepts domain logins, SQL Servers are frequently configured to allow only Integrate d Security
The SqlConnection object has a constructor that takes a connection string argument. The connection string contains pairs of keywords and values, and at a minimum you need to give the name of your SQL Server, the name of the database you want to access, and a login and password.
To test a connection using SQL Server Security, you need a login and password for SQL Server. Ask your SQL Server Database Administrator if you don’t have one. For example:
Getting Data Quickly
Having established that you can connect to the data, you will want to retrieve some data. This can be done in a number of ways, but the fastest is using the SqlDataReader object. SqlDataReader provides a read-only, forward-only stream of data into your application. For example, you could use the data from a SqlDataReader to fill a listBox control in your application.
In this example, the SqlDataReader uses a SqlConnection object for its connection, and a SqlCommand object to contain the ‘SELECT’ SQL to execute at the SQL Server. You must open the connection before calling the SqlDataReader object’s Read method. Call the Read method in a loop, it returns ‘true’ until there is no more data left to read. The columns in each row retrieved are accessible using one of the ‘Get’ methods. For example, the following code shows the GetString method retrieving the title column of each row in the ‘titles’ table into a listBox control:
You can use the SqlDataAdapter to fill a DataSet with data retrieved from SQL Server, which it achieves using both a SqlConnection object and a SqlComand object. For example:
Note that when the SqlDataAdaptor object’s Fill method is called, it automatically opens the connection, and it also automatically closes the connection when it has finished. The last line of the above example shows a DataGrid control being bound to the first DataTable object in the filled DataSet.
For more information on the capabilities of the ADO.NET DataSet see the white paper 'Data Synchronization with the ADO.NET DataSet'.
When using a DataSet or some other data container as a local store, and making local changes to the data in it, at some point you should synchronize your changes with the SQL Server so that the server data reflects the changes your application has made to the local copy.
SqlClient offers two main ways of updating data on the SQL Server: executing a SqlCommand, and using the SqlDataAdapter object’s Update method.
First, the SqlCommand has a number of ‘Execute’ methods for different purposes depending on what you expect to be returned. You execute a plain SQL update using the UpdateNonQuery method, because you expect no retrieved data. For example:
The DisplaySqlEr rors method is the same as shown previously. Note that the connection must be open before you call ExecuteNonQuery, and to conserve resources, you must close it again as soon as you are finished.
To use the SqlDataAdaptor object to write back changes to the backend SQL Server, you must provide it with extra commands to perform update, insert, and delete operations on the server data. The extra commands have parameters to match the columns originally retrieved, and you can enter the SQL yourself, use SQL Server stored procedures, or have the SqlCommandBuilder object prepare them for you.
For further information on the SqlDataAdaptor and the SqlCommandBuilder, see the reference ‘ADO.NET Examples and Best Practices for C# Programmers’ at the end of this paper.
SQL-XML provides an alternative to connecting directly to a SQL Server using the SqlClient classes. SQL-XML uses HTTP to request data, and transmits the results as XML. The following diagram outlines the three tier architecture of a SQL-XML system:
SQL-XML uses IIS in its middle tier to receive HTTP requests, authenticate users, and respond with results. You create a specially configured Web site with the SQL-XML ISAPI component ‘SQLISAPI’ registered in it. SQLISAPI channels SQL-XML requests for data through the SQLOLEDB data access component and the SQLXML component to SQL Server. SQL Server uses the ‘FOR XML’ extension to the SQL language to format the results of a query into XML, which is then passed back to the client application.
Setting Up a SQL-XML Web site
SQL-XML ships with SQL Server 2000, so from the Start menu, you need to locate the ‘Configure SQL XML Support in IIS’ option in the Microsoft SQL Server group. This launches the ‘IIS Virtual Directory Management for SQL Server’ console from which you can create and configure SQL-XML Web sites.
Whether you are creating a Web site, or maintaining an existing one, the main SQL-XML options and properties are managed from the Properties dialog. Notice that the ‘Security’ tab specifies how you log onto the SQL Server, and the ‘Data Source’ tab specifies the name of your server, and the data table to access.
The ‘Settings’ tab includes important security settings for how the SQL-XML Web site will operate. In particular note the ‘Allow sql= .. or template=… URL queries option, and the ‘Allow template queries’ option. Generally, you should not permit URL queries because they will allow any valid SQL queries to be run against the database! The next section discusses template queries.
If you plan to use templates, then you need to create a sub-folder from the Web site’s physical folder, then give it a virtual name and the ‘template’ type, using the ‘Virtual Names’ tab.
Creating a SQL-XML Template
When you organize your SQL-XML queries into template files you control what SQL is available to run against the database. The following example is a template file called ‘titletemplate.xml’ that contain s a SQL-XML selection from the ‘titles’ table:
This paper delineates how to implement two solutions for data access to Microsoft's SQL Server 2000 to allow you to assess performance and check security. The solutions describe how to get connected, retrieve data, and perform a simple update. In addition to the text of this paper, review to the references for details of deploying each solution.
 Microsoft .NET Compact Framework Core Reference, Microsoft Press (ISBN 0-7356-1725-2)
 Microsoft .NET Framework On-line documentation, Microsoft*
 Programming Microsoft SQL Server 2000 with XML, Microsoft Press (ISBN 0-7356-1369-9)
 SQL Server 2000 Books On-line.
 ADO.NET Examples and Best Practices for sC# Programmers, a! Apress (ISBN 1-55059-012-0)
Intel's Mobilized Software Initiative
Data Synchronization: Which Technology?
Wireless Enterprise Networking With Mobile Software