RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


A Lap Around SQL Server 2005 Compact Edition : Page 3

With a new name, new capabilities, and a new focus, the product formerly called SQL Server Mobile Edition expands its reach to small-footprint desktop applications—and it's free!

Building the Sample Application
Download the sample code for this article to follow along with the process of writing code to access SQL Server Compact as described in the rest of this article.

Author's Note: This sample application uses a database file named DevXSample.sdf located in the c:\SQLServerServerEverywhere\ directory. For demonstration purposes, the path and filename are hard-coded in the c_strDatabasePath constant. If you created a different database in a different path, you'll need to adjust the name and path accordingly.

Writing the Code
In any project that uses SQL Server Compact, you must set a reference to the assembly containing its ADO.NET data provider implementation. For this application, add a reference in your project to the System.Data.SqlServerCe assembly. In VB.NET, include an Imports statement (uses in C#) to include the System.Data.SqlServerCe namespace in your source.

For demonstration purposes, this application has a set of buttons (see Figure 7) that perform various database activities such as creating a database, creating, initializing, and dropping a table, and retrieving data.

Figure 7. The Sample Application: The sample application demonstrates how to use several useful functions of SQL Server Compact.
First, create the sample database file on disk by clicking the "Create Database" button. The code for the button Click event creates an instance of the SqlCeEngine class, passing a connection string to the class constructor, and then calls its CreateDatabase method. The connection string contains the database location, filename, and password to use when creating the new database file.

   objEngine = New SqlCeEngine(c_strConnectionString)
After creating the database, click the "Create Table" button to create the Products table. The application creates the table using an ADO.NET Command object (implemented by the SqlServerCe data provider) to execute standard SQL. The code below demonstrates the process of creating the table.

   strSQL = "CREATE TABLE Products "
   strSQL += "  ("
   strSQL += "     prID int NOT NULL, "
   strSQL += "     prName nvarchar(100) NOT NULL, "
   strSQL += "     prDescription nvarchar(1000) NOT NULL, "
   strSQL += "     prBasePrice money NOT NULL"
   strSQL += "  )"
   objConn = New SqlCeConnection(c_strConnectionString)
   objComm = New SqlCeCommand(strSQL, objConn)
To insert the sample records into the Products table, click the "Initialize Table" button. This button calls the code shown in Listing 1. It uses a series of standard ADO.NET Command objects to execute SQL insert statements. Initializing the Products table inserts five product records into the table.

'Insert the demo products into the Products table

   Private Sub btnInitializeTables_Click( _
      ByVal sender As System.Object, _
      ByVal e As System.EventArgs) _
      Handles btnInitializeTables.Click
      Dim objConn As SqlCeConnection
      Dim objComm As SqlCeCommand
      Dim strSQL As String
      objConn = New SqlCeConnection(c_strConnectionString)
      '---- first delete any existing products
      strSQL = "DELETE FROM Products"
      objComm = New SqlCeCommand(strSQL, objConn)
      '---- insert a row into the Products table
      strSQL = "INSERT INTO Products "
      strSQL += "(prID, prName, prDescription, prBasePrice) "
      strSQL += "VALUES "
      strSQL += "(1, 'Shoes', 'Leather Shoes', 10.00)"
      objComm = New SqlCeCommand(strSQL, objConn)
      ' insert more rows here
      ' clean up
      MessageBox.Show( _
          "The Products table was initialized successfully.")
   End Sub
To verify the correct insertion of the product records, click the "Refresh" button below the data grid (see Figure 7). The button calls a method named PopulateGrid, which reads the data from the Products table into a dataset, and then binds the dataset to the data grid on the form.

   'Populate the datagrid
   Private Sub PopulateGrid()
      Dim objAdapter As SqlCeDataAdapter
      Dim objDataset As DataSet
      Dim strSQL As String
      strSQL = "SELECT prID as [ID], prName AS [Name], _
         prDescription as [Description], "
      strSQL += "prBasePrice as [Base Price] FROM Products ORDER BY prName"
      objAdapter = New SqlCeDataAdapter(strSQL, c_strConnectionString)
      objDataset = New DataSet()
      objAdapter.Fill(objDataset, "Products")
      dgvProducts.DataSource = objDataset.Tables("Products")
   End Sub
It's important to reiterate that all the code in this article is standard ADO.NET with no special considerations given for SQL Server Compact. However, in practice, if you make frequent use of stored procedures, you are no doubt accustomed to the technique of repeatedly executing a command and simply changing the values of the parameters associated with the command. Since stored procedures are not supported, you will need to develop an alternate method of repeatedly inserting different string values into a SQL statement.

The sample application includes other functions to manipulate the data, such as changing all the product prices by a given percentage and counting the rows in the Products table. These are purposefully simplistic to demonstrate common ways that developers typically access data and modify it in a real-world application. Because they are similar to code listings already described, I won't discuss them individually. You can view and test them by downloading the sample project.

As you begin using SQL Server Compact, you fill find that writing software to use it is remarkably similar to using the larger server versions of SQL Server. This is due in large part to the ADO.NET data provider framework, which supports similar functions against a wide range of data providers, making the task of writing software to access these data providers nearly identical across all SQL Server platforms.

You will also notice some differences. The data types available in SQL Server Compact are a subset of what is available in richer versions of SQL Server. Further, because the current version does not support stored procedures, you will find you'll need to break up and simplify complex SQL processes typically implemented in stored procedures. However, the SQL Server Compact platform was built for the express purpose of bringing your data everywhere you go—not for implementing enterprise applications on mobile phones.

For more information about SQL Server Compact and to keep up-to-date with updates to the product, visit the following sites.

Michael S. Jones is the Director of Systems Architecture for Passport Health Communications, Inc., a national healthcare technology provider connecting hospitals, physician clinics and outpatient centers with payer and patient information to facilitate and improve their revenue cycle process. Michael, his wife, and three children live in Franklin, Tennessee where he spends his spare time reading and enjoying time with his family outdoors. .
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date