This Issue Sponsored By
DEVCONNECTIONS: WIN A HARLEY MOTORCYCLE
In This Issue
Developer .NET Perspectives
Resource
New and Improved
Developer .NET Perspectives
When you're developing the back end of a mobile application, you can leverage Microsoft SQL Server 2000 Windows CE Edition as the engine for persisting and eventually transferring the application's data to a desktop PC or central database server. SQL Server CE is a terrific tool. However, it has certain limitations and isn't well documented, especially in the area of replication.
SQL Server CE is an enabling technology for Windows .NET Compact Framework applications. When you use Visual Studio .NET 2003 to test an application that references the SQL Server CE assembly, the IDE automatically installs SQL Server CE as part of the test deployment. As a result, you probably won't need to download SQL Server CE. However, if you need to install it, you can download it for free from http://www.microsoft.com/sql/ce/downloads/ce20.asp. Note that the Web page says you must have Microsoft eMbedded Visual Tools (EVT) 3.0. If you installed the Compact Framework, you've already met this requirement. Like all Compact Framework applications, SQL Server CE installs by copying the .cab file to your handheld device.
SQL Server CE provides you with a familiar way of saving application data. You can leverage T-SQL statements and ADO.NET to save and retrieve data. However, SQL Server CE doesn't support stored procedures. At first, this limitation might seem major--after all, a key security and performance recommendation is that you should always use stored procedures. Although I hope the ability to use stored procedures will be available in a future version of SQL Server CE, working around this limitation is reasonably simple. You can transition your stored procedures to dynamic SQL in three steps:
SQL Server CE provides support for most T-SQL commands. Two exceptions with which I'm familiar are the TOP command and the use of square brackets (\[\]). Including either item in your dynamic SQL statement causes an unexplained SQL Server CE error. I'm not aware of any documentation about these (or other) limitations.
The main advantage of using SQL Server CE is that you can configure it to automatically replicate data back to your central database server. SQL Server CE replication, in conjunction with filters, is a powerful tool that lets a group of users work disconnected, then update their changes to a central data store. The key to managing replication is to set up filters based on user identity or another database value. For example, one of my clients gives its truck drivers handheld devices to track deliveries and capture signatures. The client prepared defined routes for its drivers, and I based several filters on those routes. Now when the drivers replicate their data to the data store, SQL Server 2000 screens the data based on the route identity. Instead of trying to copy a 10GB or 20GB database to the handheld device, each driver copies a 10MB database that only contains information about his or her customers and products.
You should use a high-speed connection to handle the initial replication of a large central database because of the volume of data to be transferred. The initial synchronization typically requires the transfer of the entire database, which can easily be 10MB to 12MB in a business environment. After the initial data has been set up on the mobile device, you can use a traditional dial-up connection to handle subsequent replications.
Another advantage of using SQL Server CE involves security. SQL Server CE replication is managed through Microsoft IIS. Thus, IIS provides the outermost security boundary. You simply collect the credentials when you're ready to synchronize your data. Then, as part of the synchronization call, you pass the current credentials and replication to IIS.
The real challenge in using SQL Server CE is setting up replication. I'm not going to go into the details of how to configure replication, but I'll describe the overall process and provide an important debugging tip.
Before configuring IIS as part of the replication process, you should set up replication on your database server. If you've never set up SQL Server CE replication, I recommend that you first install SQL Server 2000 on your development Web server. Note that this recommendation isn't consistent with the typical guideline that recommends you run SQL Server and IIS on separate servers. However, in this case, installing SQL Server 2000 on your development Web server is recommended because setting up SQL Server CE replication can be difficult. This configuration, though, is for development purposes only. When you're ready to deploy your application, you shouldn't install SQL Server 2000 on your IIS server.
After you've configured replication on your database server, you need to set up anonymous replication from the server to a directory local to IIS. After this setup is complete, you're ready to configure your IIS server. Start by creating a new directory in your default Web site, then select an authentication method. For testing and configuration, I recommend Basic Authentication.
The next step is to add the server components associated with SQL Server CE. These components are available at http://www.microsoft.com/downloads/details.aspx?familyid=b974ba7a-a9fa-49d1-bbb7-2bee43cf64d7&displaylang=en. One quick note related to running the installation and configuration wizard for the CE server components: When the configuration wizard prompts you with a message that says the sscesa20.dll isn't installed in your virtual directory and offers to copy it, click Yes. You'll need to reference this DLL in the URL used for replication.
After you've set up and configured the system, you need to debug it. I want to impart one key debugging tip. If your replication code throws an error, you'll get an error message. However, in most cases, these messages aren't very informative because you have at least three layers involved in the replication process: the mobile device layer, the IIS layer, and the SQL Server 2000 layer. To debug the problem, I recommend that you follow these steps:
SQL Server CE is an excellent tool. However, you'll want to allocate about a week to work through its initial setup. After you're familiar with working with SQL Server CE and the Compact Framework, you'll find you have the same power and flexibility in writing mobile applications that you have for writing desktop applications.
Sponsor: DEVCONNECTIONS: WIN A HARLEY MOTORCYCLE
Dev Connections conference and expo will be held April 18 - 21. Back by popular demand are concurrently running events Microsoft ASP.NET Connections, Visual Studio Connections, and SQL Server Magazine Connections. Details about workshops, sessions, and speakers are online, including the exclusive Microsoft Day on "Yukon" and "Whidbey". Register early and receive access to all three conferences for one price, and a chance to win a Harley motorcycle. Go online or call 800-438-6720 or 203-268-3204.
Announcements
(brought to you by SQL Server Magazine)
Optimize your skills at Tech Ed 2004 -- May 23-28, 2004 in San Diego, CA -- the definitive Microsoft conference for building, deploying, securing and managing connected solutions. Explore Microsoft's latest developer technologies. Network and make lasting connections with peers. Sharpen your skills on products such as Visual Studio .NET and the .NET Framework. Register now.
Get the most out of Microsoft's software and technology at Microsoft's premier European conference for building, deploying, securing and managing connected solutions. Benefit from 400+ sessions packed with technical content covering Microsoft Visual Studio .NET 2003, Windows Server 2003, Exchange Server 2003, SQL Server 2000, and more. Register now and save 300 Euros.
Resource
Novice forum member Jason is looking for a book about how to use ADO.NET to program Microsoft SQL Server 2000 databases. Although he's experienced with SQL Server, programming against databases is new for him. He'll be using Visual Basic .NET 2003. If you have any recommendations, go to the following URL:
Events Central
(A complete Web and live events directory brought to you by Windows & .NET Magazine: http://www.winnetmag.com/events )
Now in its 9th year, Infosecurity Europe is Europe's number one IT Security Exhibition. The event brings together professionals interested in IT Security from around the globe with suppliers of security hardware, software and consultancy services. Grand Hall at Olympia from 27th to the 29th April 2004. Visitors not registered by 22nd April will be charged a 20 \[pounds sterling\] entrance fee. Visit:
New and Improved
by Shauna Rumbaugh, products@sqlmag.com
O'Reilly & Associates released Niel M. Bornstein's .NET and XML, an in-depth tutorial for intermediate to advanced developers who want to understand how Microsoft .NET and XML work together and learn how to combine them effectively. Topics covered include reading and writing XML, reading and writing non-XML formats, manipulating XML with the Document Object Model (DOM), navigating XML with the XML Path Language (XPath), transforming XML with Extensible Style Language Transformations (XSLT), constraining XML with schemas, performing Simple Object Access Protocol (SOAP) and XML serializations, using XML with Web services, and using ADO.NET with XML in databases. The second part of .NET and XML is a complete reference to the XML-related namespaces in the Windows .NET Framework. The 464-page book contains reusable code examples and costs $39.95. For more information, contact O'Reilly & Associates at 707-827-7000, 800-998-9938, or on the Web.
Sponsored Links
Quest Software, Inc.
DB Ghost for SQL Server
Contact Us
This email newsletter is brought to you by Windows & .NET Magazine,
the leading publication for IT professionals deploying Windows and
related technologies. Subscribe today.
View the Windows & .NET Magazine Privacy policy at
Windows & .NET Magazine, a division of Penton Media, Inc.
221 East 29th Street, Loveland, CO 80538
Attention: Customer Service Department
Copyright 2004, Penton Media, Inc. All Rights Reserved.