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 Right Environment to Support AI, Machine Learning and Deep Learning

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) objEngine.CreateDatabase()

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) objConn.Open() objComm = New SqlCeCommand(strSQL, objConn) objComm.ExecuteNonQuery()

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) objConn.Open() '---- first delete any existing products strSQL = "DELETE FROM Products" objComm = New SqlCeCommand(strSQL, objConn) objComm.ExecuteNonQuery() '---- 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) objComm.ExecuteNonQuery() ' insert more rows here ... ' clean up objConn.Close() 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. .
Thanks for your registration, follow us on our social networks to keep up-to-date