Databases to Go

Use SQL Server CE to extend your database applications to mobile devices.

asp:feature

LANGUAGES: VB .NET

TECHNOLOGIES: Mobile Development | SQL Server CE | Visual Studio .NET 2003

 

Databases to Go

Use SQL Server CE to extend your database applications to mobile devices.

 

By Tom Rizzo

 

Although there is a major move toward wireless connectivity for applications, the boom of wireless, anywhere connectivity is still a ways off. But while you wait for the wireless revolution to happen, you need to solve your customers' mobile needs today. With the increase in the power of handheld devices such as the Pocket PC and the acceptance of more than only Personal Information Manager (PIM) applications on these devices, you need to start thinking about how to move your applications into the mobile world without ubiquitous connectivity. Many advancements from Microsoft - including SQL Server CE Edition 2.0 (SQLCE), the .NET Compact Framework (.NET CF), and the Smart Device Extensions (SDE) for Visual Studio .NET - can help you move to the mobile, handheld world.

 

In this article, I will help introduce you to these new technologies and guide you in building mobile applications on the .NET platform. I'll explain how to build a mobile application using the beta of Visual Studio .NET 2003. This is a simple sales force-automation application that runs on the Pocket PC, leverages SQLCE for its database, replicates any changes between the SQLCE database and SQL Server 2000, and finally is built using .NET CF and SDE.

 

SQLCE 2.0 on the Up and Up

Before diving into this article's downloadable sample application and showing you how to build a mobile application, I'd like to explore some of the advancements in SQLCE. Some of the major advancements in SQLCE are enhancements to its core database, development tools, connectivity, and security.

 

For those who have never worked with SQLCE before, think of it as a miniature version of SQL Server that can run on Windows CE devices. SQLCE supports a subset of the features of SQL Server. However, SQLCE supports SQL grammar that is compatible with SQL Server. This means all the Data Definition Language (DDL) and Data Manipulation Language (DML) grammar you are used to writing for your SQL Server applications will work with SQLCE. Plus, SQLCE supports multicolumn indexes, referential integrity, joins, Unicode, transactions, and many SQL Server data types.

 

Previously, if you wanted to build mobile applications, you had to use embedded Visual C++ or embedded Visual Basic. These tools are useful, but they are not as powerful as their full Visual Studio cousins. Plus, they were hosted in a completely different development environment from the core developer studio. With SDE, you have the mobile development environment integrated directly into the core Visual Studio environment. Figure 1 shows the dialog box for creating a new SDE application directly in the Visual Studio environment.

 


Figure 1. Because SDE is integrated into Visual Studio .NET 2003, you can create a mobile application as easily as you can create a Windows or Web application.

 

By integrating with .NET CF, you can leverage ADO.NET and .NET's new data provider for SQLCE as the primary data-access method for your mobile applications. Also, SQLCE includes a native provider for ADO.NET, which lets mobile devices access SQL Server directly. This allows you to bypass the local SQLCE database if you want to access SQL Server directly in a fully connected application.

 

Finally, SQLCE includes an interactive SQL tool (isqlw) on the device itself. Figure 2 shows the new isqlw tool on a Pocket PC. This tool allows you to create queries using a subset of the functionality that the SQL Server 2000 Query Analyzer offers.

 


Figure 2. The Query Analyzer tool for mobile devices makes managing mobile databases easier. You can look at the databases contained on the device and also generate dynamic SQL statements to query those databases through this graphical tool.

 

SQLCE supports two types of connections: remote data access (RDA) and merge replication. RDA provides connectivity to versions of SQL Server released prior to SQL Server 2000. RDA is not as powerful as merge replication, but you can use it with non-SQL Server 2000 servers. Merge replication provides richer functionality for conflict resolution than RDA, but it requires your back end to be a SQL Server 2000 system.

 

No matter which replication scheme you use, all replication is performed through the HTTP protocol. This allows you to leverage the security of Internet Information Server (IIS) for both authentication and encryption through Secure Sockets Layer (SSL). In addition, using HTTP allows easier access through firewalls.

 

Beyond the IIS security capabilities SQLCE supports, it also supports 128-bit encryption of the database files on the device as well as password protection of the databases. This type of security can help protect the data on the device if it is lost or stolen.

 

In this article's downloadable sample application, the company has a data warehouse that uses SQL Server as its data source. The company also has a disconnected sales force that retrieves sales data from the data warehouse. The sales team can't use ASP.NET because its members use Pocket PCs in a disconnected state while making sales calls. I'll explain how to build a solution using .NET CF, SDE, and SQLCE 2.0 that will allow the sales force to work in a disconnected manner and still replicate data back to the data warehouse.

 

The key thing about this application is it extends the built-in Northwind CE sample application included with Visual Studio .NET 2003. In the sample, you'll add some new functionality as well as look at some of the application's existing functionality.

 

The Sales Force Mobile Application

The first thing you need to do when building the sample is to build the application's user interface. Because SDE includes a graphical forms designer, you can leverage the drag-and-drop capabilities of Visual Studio .NET to build forms. Once you complete the user interface and controls, you end up with an application as you see in Figure 3.

 

' This function synchronizes the device

' database with the server database.

Private Sub ReplSync()

    Dim replNorthwind As New SqlCeReplication

    Dim changed As Boolean = False

 

    If radioButtonSQLAuth.Checked Then

        replNorthwind.PublisherSecurityMode = &_

           SecurityType.DBAuthentication

    Else

        replNorthwind.PublisherSecurityMode = &_

           SecurityType.NTAuthentication

    End If

 

    ' Set the Subscriber properties.

   replNorthwind.SubscriberConnectionString =

   dataNorthwind.LocalConnString

   replNorthwind.Subscriber = textBoxSubscriber.Text

 

    Try

        If dataNorthwind.NorthwindConnection.State &_

         = ConnectionState.Open Then &_

         dataNorthwind.NorthwindConnection.Close()

     If init Then

            ' Create the Local SSCE Database subscription.

             replNorthwind.AddSubscription &_

             (AddOption.CreateDatabase)

        End If

 

        ' Synchronize to the server database to populate

        ' the Subscription.

        replNorthwind.Synchronize()

 

        ' Display the synchronization results.

        ' Shown in full code.

 

    Catch e As SqlCeException

        ' Error handling mechanism is shown in full code.

    End Try

 

    Try

        ' Open the connection to the Northwind database.

        dataNorthwind.NorthwindConnection.Open()

 

    Catch e As SqlCeException

        NorthwindData.ShowErrors(e)

    Catch e As Exception

        MessageBox.Show("Save connection information" &_

        & e.Message, "Northwind")

    End Try

End Sub 'ReplSync

Figure 3. The ReplSync function synchronizes the device database with the server database.

 

Before you can do anything with SQLCE databases, you need to get the database replicated to the local device. This is where you will see the power and ease of SQLCE in action. Rather than require you to write a whole bunch of code, SQLCE includes simple methods that help you replicate your database from your server down to your local devices. The Synchronize method does all the heavy lifting to get the data from the server. As the developer, all you need to do is fill in the properties on the SqlCeReplication object as shown in Figure 3. (You can download the entire code file in the sample application.)

 

The replication between the device and server can use the merge-replication features of SQL Server. So if there are changes made on the local device and there are different changes on the server, you either can use the built-in conflict resolvers (for example, last writer wins), or you can build your own custom conflict resolvers as COM objects. By having built-in and also extensible conflict resolvers, you have the flexibility to decide how to handle any conflicts.

 

If any errors occur during replication, or, for that matter, during your SQLCE applications, you can leverage the SqlCeException object, which will be populated with any errors that occur.

 

Query the Local Database

Once you replicate data between a local-device database and a SQL Server database, you can query the local database for information. One of the great things about SQLCE and .NET CF is they include ADO.NET. So if you're used to programming ADO.NET in your ASP.NET applications, programming ADO.NET for your mobile applications is simple. Figure 4 shows you how to leverage ADO.NET to fill in the forms in the sample application.

 

Private Sub RefreshProducts()

    Dim cmdProducts As SqlCeCommand = Nothing

    Dim drProducts As SqlCeDataReader = Nothing

    Dim cnNorthwind As SqlCeConnection _

    = NorthwindData.GetInstance().NorthwindConnection

 

    Dim listViewItem As ListViewItem = Nothing

 

     If listViewOrders.SelectedIndices.Count = 0 Then Return

 

    item = listViewOrders.FocusedItem.Text

    If item.Length = 0 Then Return

 

    sql = "SELECT P.ProductName, O.UnitPrice, " & _

          "O.Quantity, O.Discount " & _

          "FROM ""Order Details"" AS O " & _

          "INNER JOIN Products AS P " & _

          "ON O.ProductID = P.ProductID " & _

          "WHERE OrderID = " & item & " " & _

          "ORDER BY P.ProductName"

 

    Try

        cmdProducts = New SqlCeCommand(sql, cnNorthwind)

         drProducts = cmdProducts.ExecuteReader()

        fieldCount = drProducts.FieldCount

 

        While drProducts.Read()

            listViewItem = New ListViewItem("")

            Dim i As Integer = 0

            While i << fieldCount

                If Not drProducts.IsDBNull(i) Then

                   item = drProducts.GetValue(i).ToString()

                Else

                   item = ""

                End If

 

                If i = 0 Then

                   listViewItem.Text = item

                 Else

                   listViewItem.SubItems.Add(item)

                End If

 

                i = i + 1

            End While

 

            listViewProducts.Items.Add(listViewItem)

        End While

 

    Catch e As SqlCeException

        ' Error handling mechanism

        NorthwindData.ShowErrors(e)

        Return

    Catch e As Exception

        ' Error handling mechanism

        MessageBox.Show(e.Message, "Northwind")

        Return

    Finally

        drProducts.Close()

    End Try

End Sub 'RefreshProducts

Figure 4. Because SQLCE and .NET CF support ADO.NET natively, you can leverage your ADO.NET skills to query a SQLCE database using the same syntax and methods as you would to query a full SQL Server system.

 

The code in Figure 4 creates a SqlCeConnection object to connect to a SQLCE database. Next, the code creates the SQL command it needs to pass to SQLCE. SQLCE supports many of the same features that SQL Server supports, including joins. Next, the code creates a SqlCeCommand object to execute a SQL query and returns the data into a SQLCE DataReader object that it uses to populate the controls on the forms.

 

Secure and Deploy

Security is a major concern when dealing with mobile applications. Both security over the wire and on the device itself are important. With SQLCE, security over the wire is implemented either by your wireless network provider or by leveraging the SSL connecting your device and servers. SQLCE leverages IIS or SQL security for authentication, and you can set up your SQLCE environments similar to the standard way you would set up any Web application that would require intranet or extranet connectivity. For most applications, you should leverage Windows security because it is integrated and does not require you to maintain passwords between the users' Windows accounts and their SQL accounts. Also, Windows security can lock out accounts and do password expiration as well as a host of other features.

 

In the sample application, the synchronization form asks you the type of security you are using, whether it's Windows authentication or SQL authentication. Depending on what you select as your replication security, SQLCE uses that method of authentication. As a developer, you don't need to do anything to figure out the authentication protocols and methods. Instead, you can fill in a few properties on the SqlCeReplication object and have SQLCE perform the necessary authentication.

 

Beyond authentication, you might want to secure the database on the device. With SQLCE, you can password-protect the database, although this does not prevent the device from reading the database file. For this reason, you also should encrypt the database. An encrypted database stops the device from reading the file and also requires that you password-protect the database. The only gotcha with encryption is if your user forgets his or her password, the data essentially is lost from the database because you must reverse-engineer the password to decrypt it.

 

The great thing about SDE and SQLCE is they make deploying mobile applications much easier. In Visual Studio .NET 2003, two CAB (CABinet) files are added to the project when you add a reference to System.Data.SqlServerCE and build the application. These two CAB files include the necessary files for SQLCE. Furthermore, .NET CF also is deployed to the device.

 

When you are ready to deploy the application in production mode, all you need to do is create the CAB files in Visual Studio .NET 2003. The CAB files you create are self-extracting, so you can deploy them to the device easily. You also can customize the installation of the CAB files using tools included with Visual Studio. Depending on your application's needs, you either can use the simplicity of the built-in tools or build your own customized installation.

 

.NET makes mobile applications easier to build and deploy and allows developers to use the tools and technologies they are used to using. There is no longer a disconnect between the mobile world and the connected world. Visual Studio .NET 2003, .NET CF, SDE, and SQLCE 2.0 combine the best of the mobile world with the best of the connected world. A few gotchas still exist, but as long as you know what they are, building mobile applications got a whole lot easier and faster. Download the beta version of Visual Studio .NET 2003 and try it for yourself.

 

The sample code in this article is available for download.

 

Tom Rizzo is a group product manager in the Microsoft .NET Enterprise Servers Group. He also is author of Programming Microsoft Outlook and Exchange (Microsoft Press). E-mail Tom at mailto:[email protected].

 

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish