SQL Server 2005 Compact Edition Data Access with the SqlCeResultSet and Visual C#.NET

United States (English)

Home Library Learn Samples Downloads Support Community Forums

SQL Server 2005 Compact Edition Technical Articles

Microsoft Corporation

January 2007

Applies to:
Microsoft Visual Studio 2005
Microsoft SQL Server 2005 Compact Edition

Summary: In this tutorial, you will learn how to use the SqlCeResultSet, a powerful cursor-based data-access implementation, to access data from SQL Server 2005 Compact Edition. In this tutorial, you will use Visual C#.NET. There is another tutorial that shows how to use Visual Basic.NET to do the same thing. (17 printed pages)

Tutorial Objective
Conclusion

Tutorial requirements

The following applications are required to run this tutorial:

Microsoft Windows XP Professional
Visual Studio 2005
This tutorial requires Visual Studio 2005 Standard, Professional, or Team System Edition. It will not work with any of the Express Editions. If you do not have the correct edition of Visual Studio 2005, find out how you can acquire it from the Visual Studio 2005 Developer Center.
Visual Studio 2005 Service Pack 1
SQL Server 2005 Compact Edition

Credentials used

None

Level

200

Estimated time to complete this tutorial

30 minutes

Tutorial Objective

In this tutorial, you will learn how to use the SqlCeResultSet, a powerful cursor-based data-access implementation, to access data from SQL Server 2005 Compact Edition. In this tutorial, you will use Visual C#.NET. There is another tutorial that shows how to use Visual Basic.NET to do the same thing.

Note SQL Server Compact Edition is a versatile database technology that works across all Windows platforms and is an evolution of the Windows Mobile database technology known as SQL Server Mobile Edition (SQL Mobile). Currently, some Visual Studio 2005 and SQL Server Management Studio 2005 dialogs still refer to the product as SQL Mobile; therefore, as you perform the steps in this tutorial, if a step instructs you to select SQL Server Compact Edition, but your user interface displays SQL Server Mobile instead, you should select SQL Server Mobile. The naming difference is a short-term issue and will be changed in the near future with Visual Studio 2005 SP1 and SQL Server 2005 SP2. Whether referred to as SQL Mobile or SQL Server Compact Edition, the technology and usage is the same.

In this tutorial, you will perform the following exercise:

Using the SqlCeResultSet to access and update SQL Server 2005 Compact Edition data

Using the SqlCeResultSet to Access and Update SQL Server 2005 Compact Edition Data

In this exercise, you will learn how to use the SqlCeResultSet, a powerful cursor-based data-access implementation to access data from SQL Server 2005 Compact Edition.

To create a project

Start Visual Studio 2005 by clicking Start | All Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
Click File | New | Project to create a new Windows Forms application.
In the New Project dialog box under Project types, browse to Visual C# | Windows.
Note Depending on your Visual Studio configuration, Visual C# might appear under Other Languages.
In the Templates box, select Windows Application.
Change the Name to ResultSetDemo, and then click OK, as shown in Figure 1.
Note You can create the project in the Location of your choice.
Figure 1. The New Project dialog box
Visual Studio creates a new project and opens Form1 in the form designer. Now, you will reference a sample SQL Server Compact Edition database and set up the form to display and allow modification of the results of a data cursor using the SqlCeResultSet.
Note A cursor refers to a real-time view of one or more rows from a database, potentially allowing positioning and editing, depending on options set at the time the cursor is created.

To set up the project form for data binding

In Visual Studio, click View | Toolbox to open the form designer toolbox.
In the toolbox, expand Data if it is not already expanded.
The data-related controls are displayed, as shown in Figure 2. You will be using the BindingSource and DataGridView controls.
Figure 2. The Data controls in the toolbox
Double-click the BindingSource control to add bindingSource1 to the form designer's component tray, as shown in Figure 3. This control will be bound to a cursor over a SQL Server Compact Edition database.
Figure 3. The bindingSource1 control in the component tray
In the toolbox, double-click the DataGridView control to add dataGridView1 to the form. Figure 4 shows the newly added DataGridView control. This control will display the results of the data-bound cursor.
Figure 4. The dataGridView1 control
The DataGridView control allows you to choose your data source in a number of ways. One of these ways is to use the smart-tags menu.
If the smart-tags menu is not already visible, click the smart-tags arrow button at the top right of the dataGridView1 control to display the smart-tags menu.
In the smart-tags menu, click the Choose Data Source drop-down button, and then click bindingSource1, as shown in Figure 5. This sets the data source of the DataGridView control to the BindingSource control that you just created.
Figure 5. The dataGridView1 control's smart-tags menu, showing the Choose Data Source options
Still in the smart-tags menu, click Dock in parent container to cause the control to fill the entire form, even when the form is resized at run time, as shown in Figure 6.
Figure 6. The docked dataGridView1 control
Now, you are ready to add the data to the form. You will use the Northwind sample database that ships with SQL Server Compact Edition.

To set up the form for data binding

In the Visual Studio Solution Explorer, right-click the ResultSetDemo project, and then click Add | Existing Item.
In the Add Existing Item - ResultSetDemo dialog box, under Files of type, select All Files (*.*).
Navigate to C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0, select Northwind.sdf, and then click Add, as shown in Figure 7.
Note As an alternative to the previously mentioned steps, you can add the Northwind.sdf file to your Visual Studio project by using Windows Explorer to navigate to the location of the database file, and then dragging the Northwind.sdf file from Windows Explorer to the Visual Studio Solution Explorer.
Figure 7. Adding the Northwind.sdf SQL Server Compact Edition database file
Visual Studio adds a copy of the Northwind.sdf file to your project. When you add a data source to a project, Visual Studio assumes you want to use a typed DataSet to show the data in your database, so the Data Source Configuration Wizard appears.
Because you will be using the SqlCeResultSet instead of a typed DataSet, click Cancel to close the Data Source Configuration Wizard.
Now, you must add a reference to the ADO.NET provider for SQL Server Compact Edition.
Note If you had chosen to use the Data Source Configuration Wizard and typed DataSets, Visual Studio would have added this reference for you.
In the Visual Studio Solution Explorer, right-click the ResultSetDemo project, and then click Add Reference.
On the .NET tab of the Add Reference dialog box, locate and click System.Data.SqlServerCe, and then click OK, as shown in Figure 8.
Figure 8. Adding the System.Data.SqlServerCe project reference
Visual Studio adds a reference to the System.Data.SqlServerCe assembly. Now, you will add the code necessary to connect to and load the data from the SQL Server Compact Edition database.

To add data binding code to the form

In Visual Studio, in the Form1 form designer, double-click the form's title bar to open the Form1 class's code view and create the Form1_Load event handler, as shown in Figure 9.

Figure 9. The Form1 class in code view (Click on the picture for a larger image)
Because the SqlCeResultSet is a cursor-based implementation, there is no cached data in memory. As you are executing a query, the result of that query is a real-time view over the actual data in the database. Therefore, as you scroll through the grid, you are actively looking at data in the database, which means that you must maintain an active connection. Because the SQL Server Compact Edition database is part of the application and is not meant to be shared with thousands of other users connecting to your machine at the same time, you can open a connection at the form scope and leave that connection open the entire time the form is open.
At the top of the file, locate the using statements and add another using statement for the System.Data.SqlServerCe namespace whose assembly you referenced in the last task, as shown in the following code example.
```
using System.Data.SqlServerCe;
```
This allows you to reference members of the namespace in your code without fully qualifying them with the prefix System.Data.SqlServerCe.
Inside the Form1 class declaration, but outside any other members, declare a private variable of type SqlCeConnection named _conn, as shown in the following code example.
```
private SqlCeConnection _conn;
```
Note If you are viewing the Visual Basic.NET screen cast that corresponds to this tutorial, you will notice that the video instructs you to create the Form1 class constructor. There is no need to create the class constructor in this tutorial because Visual Studio creates C# class files with the class constructor already defined.
Now, locate the public Form1() constructor and, inside it, after the InitializeComponent call that Visual Studio has put in place, create a new instance of the SqlCeConnection class and assign it to the _conn variable, as shown in the following code example.
```
    _conn = new SqlCeConnection();
```
To specify the path to the SQL Server Compact Edition database file, you can set the SqlCeConnection class's ConnectionString property or just pass the connection string to the SqlCeConnection constructor. In this case, you will use the constructor.
The SQL Server Compact Edition connection string format is similar to that of SQL Server, but in SQL Server Compact Edition, a database is simply a file, so instead of specifying a database server and instance, you just need to specify the file path using either an absolute path or a relative path that is relative to the application's directory. By default, Visual Studio places the database file in the same directory as the application; therefore, you could just specify the local file name without a path. However, when deploying the application by way of ClickOnce, the database file is installed to a directory other than the application. To make managing application deployment easier, SQL Server Compact Edition provides the DataDirectory substitution string that automatically provides the appropriate data directory for your application.
Inside the parentheses of the SqlCeConnection instance that you just assigned to the _conn variable, type the connection string @"Data Source = |DataDirectory|\Northwind.sdf", so that the line of code that you just typed now looks like the following code example.
```
    _conn = new SqlCeConnection(@"Data Source = 
|DataDirectory|\Northwind.sdf");
```
The pipe symbols (|) around the word DataDirectory tell the SQL Server Compact Edition runtime to insert the application's data directory as the first part of the database file path. Northwind.sdf is the name of the SQL Server Compact Edition database file that you added to your project.
Note You could also specify a password in the connection string, but this sample database is not password protected, so you don't need to specify a password in this case.
Because the data-binding source is not strongly typed, the data grid does not know the layout of the data until run time. The DataGridView control's default behavior is to have its columns configured at design time, but you can use its AutoGenerateColumns property to automatically generate the appropriate columns at run time based on the data source that is assigned to the control.
After the line you just added and modified that assigns to the _conn variable, set the dataGridView1 control's AutoGenerateColumns property to true, as shown in the following code example.
```
    this.dataGridView1.AutoGenerateColumns = true;
```
Note The AutoGenerateColumns property is not available at design time in the property pane, so you must set its value in code.
To execute a command against the SQL Server Compact Edition database, you must create and configure a SqlCeCommand object.
Locate the private Form1_Load event handler. It is currently empty.
Inside the Form1_Load event handler, create a new instance of the SqlCeCommand class and assign it to a variable named cmd, as shown in the following code example.
```
    SqlCeCommand cmd = new SqlCeCommand();
```
Next, assign the _conn variable that you defined above to the Connection property of the cmd object.
```
    cmd.Connection = _conn;
```
Now, you must assign to the cmd object's CommandText property a Transact-SQL (T-SQL) SELECT statement that retrieves the records from the Employees table in the database. Because you might not be familiar with the structure of the database and its tables, you can use the Visual Studio Server Explorer and Query Designer to view the database schema and even generate T-SQL statements.
Before launching the Query Designer, prepare a line of code for the SELECT statement by assigning an empty string to the cmd object's CommandText property, as shown in the following code example.
```
    cmd.CommandText = "";
```
Now, you will use the Query Designer to generate the SELECT statement that you will paste into the empty quotation marks in the code you just typed.
In Solution Explorer, double-click Northwind.sdf.
Visual Studio adds a database connection and displays it in the Server Explorer, showing the Northwind.sdf database and its contents, as shown in Figure 10.
Figure 10. The Northwind.sdf SQL Server Compact Edition database in the Visual Studio Solution Explorer
In Server Explorer, expand Northwind.sdf | Tables | Employees to view the Employees table's schema.
Now, right-click on Employees, and then click New Query to open the Query Designer.
Add the Employees table to the Query Designer by clicking Employees in the Add Table dialog box, and then clicking Add, as shown in Figure 11.
Figure 11. Adding the Employees table to the query
Click Close.
In order to make the result set updateable, you must include the table's primary key column, Employee ID, in the query.
Check the box next to Employee ID, as shown in Figure 12.

Figure 12. Adding the Employee ID column to the query (Click on the picture for a larger image)
The [Employee ID] column is added to the criteria pane and the SQL pane.
Now, add the Last Name, First Name and Photo columns by checking their boxes in the same way that you did in the previous step for the Employee ID column. You might need to scroll down in the Employees table to locate the Photo column.
To execute and test the query, click Query Designer | Execute SQL on the Visual Studio menu.
The Results pane displays the selected data, as shown in Figure 13.

Figure 13. Executing the query (Click on the picture for a larger image)
Highlight the SQL statement by right-clicking anywhere in the SQL pane of the Query Designer and then clicking Select All on the pop-up context menu, as shown in Figure 14.

Figure 14. Selecting the query text (Click on the picture for a larger image)
Right-click the text that is now highlighted, and then click Copy on the pop-up context menu to copy the entire SELECT statement to the clipboard.
Click File | Close to close the Query Designer and return to the Form1 class code view.
Back in the Form1_Open event handler, on the last line of code that you typed to assign the cmd object's CommandText property an empty string, right-click directly between the two quotation marks, and then press Paste in the pop-up context menu that appears.
Delete any extra white space, including the line break, which was pasted from the Query Designer so the SELECT statement appears all on one line, as shown in the following code example.
```
    cmd.CommandText = "SELECT [Employee ID], [Last Name], [First 
Name], Photo FROM Employees";
```
Next, you must execute the command so you can get the results of the query. Before you can execute a SqlCeCommand object, you must open its connection.
Open the _conn object, as shown in the following code example.
```
    _conn.Open();
```
In order to open a direct result set over the database, you must create an instance of the SqlCeResultSet class.
Declare a variable of type SqlCeResultSet named resultSet and assign to it the results of the query you designed by calling the cmd object's ExecuteResultSet method. In order to allow movement back and forth in the result set and to allow updates to the data, use the bitwise or operator (|) to pass the ResultSetOptions.Scrollable and ResultSetOptions.Updatable options to the ExecuteResultSet method, as shown in the following code example.
```
    SqlCeResultSet resultSet = 
cmd.ExecuteResultSet(ResultSetOptions.Scrollable | 
ResultSetOptions.Updatable);
```

Finally, bind the data to the grid by assigning the resultSet to the bindingSource1 control's DataSource property, as shown in the following code example.

    this.bindingSource1.DataSource = resultSet;

The Form1 class code should now look like the following code example. Note that you are never explicitly closing the database connection, because you must stay connected to the database as long as the form is open. When the form is closed, the connection will be destroyed by the .NET runtime.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace ResultSetDemo
{
    public partial class Form1 : Form
    {
        private SqlCeConnection _conn;

        public Form1()
        {
            InitializeComponent();
            _conn = new SqlCeConnection(@"Data Source = 
|DataDirectory|\Northwind.sdf");
            this.dataGridView1.AutoGenerateColumns = true;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = _conn;
            cmd.CommandText = "SELECT [Employee ID], [Last Name], 
[First Name], Photo FROM Employees";
            _conn.Open();
            SqlCeResultSet resultSet = 
cmd.ExecuteResultSet(ResultSetOptions.Scrollable | 
ResultSetOptions.Updatable);
            this.bindingSource1.DataSource = resultSet;
        }
    }
}

To test the application

In Visual Studio, click Debug | Start Debugging to run your application.
The application appears with data, including images, from the SQL Server Compact Edition database in the form's grid. You can resize the form and grid rows to see the data, as shown in Figure 15.
Figure 15. Viewing the SQL Server Compact Edition data in the Windows Forms application
Click in the Last Name column of row 2 on the text Fuller.
Change the last name by typing a new value in the cell, and then press TAB to move to the next cell.
The change is saved to the result set row buffer and will be saved to the database when you move to another row or close the form.
Close the form to stop the application.
To see that the data has been saved to the database, once again, click Debug | Start Debugging to run your application.
Notice that the application appears with the that data you changed, as shown in Figure 16, demonstrating that the data was saved to the SQL Server Compact Edition database file.
Figure 16. The modified SQL Server Compact Edition data
Close the form to stop the application and the Visual Studio debugger.
The Northwind.sdf database file that has been updated is not the one you added to your project, but a copy of the file that Visual Studio made when building your application for debugging purposes. You will see in the next task that the state of the original copy of the database you added to the project has been preserved, which can be useful when testing your application.

To understand where the modified data is stored

At the top of the Visual Studio Solution Explorer, click the Show All Files button to reveal all of the folders and files in your project directory.
Note You might need to hover your mouse pointer over the buttons to see their names. The button is outlined in Figure 17.
In the Visual Studio Solution Explorer, locate the ResultSetDemo project node. Under the ResultSetDemo project node, expand the bin and Debug folders to reveal a copy of the Northwind.sdf file among other files, as shown in Figure 17.
Figure 17. The Northwind.sdf file copy for the Debug build
In Solution Explorer, locate and double-click the original copy of Northwind.sdf to open it in Server Explorer. Be sure to double-click the original copy, and not the hidden one that you just revealed.
In Server Explorer, expand Data Connections | Northwind.sdf | Tables, if necessary.
Right-click the Employees table, and then click Open, as shown in Figure 18.
Figure 18. Opening the original Employees table
Notice that the data is not changed, but it still contains the original values, as shown in Figure 19.
Figure 19. The original Employees data
Click File | Close to close the table.
In Solution Explorer, now locate and double-click the previously hidden copy of Northwind.sdf to open it in Server Explorer. Be sure to double-click the copy under the Debug folder, and not the original one.
In Server Explorer, expand Data Connections | Northwind.sdf1 | Tables, if necessary.
Right-click the Employees table, and then click Open.
Notice that this is the data that you changed, as shown in Figure 20.
Figure 20. The modified Employees data
Click File | Close to close the table.
Because Visual Studio keeps a copy of the database, any data you modify when testing the application can be discarded by deleting the file in the Debug directory. You can change this behavior by setting a property of the Northwind.sdf file that you added to your project.
In Solution Explorer, right-click the original Northwind.sdf file that you added to the project, and then click Properties to view the file's properties, as shown in Figure 21.
Figure 21. The Northwind.sdf file properties
The Copy to Output Directory property controls Visual Studio's action regarding the file when building the project. By default, the Copy if newer setting indicates that Visual Studio should copy the file if the modified date of the file in the project folder is more recent than the copy in the build directory, in this case the Debug directory. You could also choose Do not copy or Copy always, which are self-explanatory.

Conclusion

In this tutorial, you performed the following exercise:

Using the SqlCeResultSet to access and update SQL Server 2005 Compact Edition data

In this tutorial, you learned how to use the SqlCeResultSet, a powerful cursor-based implementation that allows you to simplify data access to SQL Server 2005 Compact Edition databases. With the SqlCeResultSet, you do not have to be concerned with the complexities of DataSets and TableAdapters, explicitly controlling edit states, validation and updates, and there is no copy of the data in memory other than the single row that is being buffered at any given time. Depending on your scenario, you might need the complexities of the DataSet, so that you can buffer more of the data and perform broader validation than on individual columns; but, if not, the SqlCeResultset can be a quick and useful means to accessing and updating your application's data.

Contents