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 Basic.NET. There is another tutorial that shows how to use Visual C#.NET to do the same thing. (17 printed pages)
The following applications are required to run this tutorial:
This tutorial requires Visual Studio 2005 Standard, Professional, or Team System Editions. 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.
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 Basic.NET. There is another tutorial that shows how to use Visual C#.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 dialog boxes 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:
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.
Note Depending on your Visual Studio configuration, Visual Basic might appear under Other Languages.
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.
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
Figure 3. The BindingSource1 control in the component tray
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.
Figure 5. The DataGridView1 control's smart-tags menu, showing the Choose Data Source options
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.
Note As an alternative to the above 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.
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.
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.
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.
This allows you to reference members of the namespace in your code without fully qualifying them with the prefix System.Data.SqlServerCe.
Private _conn As SqlCeConnection
Visual Studio fills in the details of the constructor for you, which should look like the following code example.
Public Sub New() ' This call is required by the Windows Form Designer. InitializeComponent() ' Add any initialization after the InitializeComponent() call. End Sub
_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 application deployment easier, SQL Server Compact Edition provides the DataDirectory substitution string that automatically provides the appropriate data directory for your application.
_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.
Me.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.
Dim cmd As New SqlCeCommand()
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.
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.
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
Figure 11. Adding the Employees table to the query
In order to make the result set updateable, you must include the table's primary key column, Employee ID, in the query.
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.
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)
Figure 14. Selecting the query text (Click on the picture for a larger image)
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.
In order to open a direct result set over the database, you must create an instance of the SqlCeResultSet class.
Dim resultSet As SqlCeResultSet
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable)
Me.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.
Imports System.Data.SqlServerCe Public Class Form1 Private _conn As SqlCeConnection Public Sub New() ' This call is required by the Windows Form Designer. InitializeComponent() _conn = New SqlCeConnection("Data Source = |DataDirectory|\Northwind.sdf") Me.DataGridView1.AutoGenerateColumns = True End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cmd As New SqlCeCommand() cmd.Connection = _conn cmd.CommandText = "SELECT [Employee ID], [Last Name], [First Name], Photo FROM Employees" _conn.Open() Dim resultSet As SqlCeResultSet resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable) Me.BindingSource1.DataSource = resultSet End Sub End Class
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
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.
Notice that the application appears with the data that 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
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.
Note You might need to hover your mouse pointer over the buttons to see their names. The button is outlined in Figure 17.
Figure 17. The Northwind.sdf file copy for the Debug build
Figure 18. Opening the original Employees table
Figure 19. The original Employees data
Figure 20. The modified Employees data
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.
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.
In this tutorial, you performed the following exercise:
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.