- Using SQL Server Management Studio
- Using the Query Analyzer
- Using Visual Studio
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Visual Studio
SQL Server Compact Edition database is fully integrated with Visual Studio 2005. The integration enables rapid development of SQL Server Compact Edition applications. In this section you will create a Visual Studio project. Once the project is created, you will then explore the user interface of Visual Studio.
Creating a Smart Device Project
One primary goal of Visual Studio integration with SQL Server Compact Edition is to provide a single development environment where a developer can create a database, design forms, and write code to access and manipulate the database. Using Visual Studio 2005, you will do the following:
- Connect to an SQL Server Compact Edition database on a desktop or on a device.
- Manage an existing database or create a new database.
- Design SQL Queries with Query Designer.
- Automatically deploy SQL Server Compact Edition and .NET Compact Framework binaries onto a device.
The following example demonstrates the Visual Studio 2005 features that help the development of a Mobile devices application using SQL Server Compact Edition.
- Start Visual Studio. Click on File | New Project
- The New Project dialog starts. The dialog allows you to choose project types—Visual Basic, Visual C#, Visual J#, Visual C++, etc. In the example shown in Figure 3.38, Visual C# node is selected.
Figure 3.38 New Project
- You can build Windows, Smart Device, Database, or Starter Kits projects. In this example, select Smart Device. The Smart Device project can be built for Pocket PC 2003, Smartphone 2003, Windows CE 5.0, Windows Mobile 5.0 Pocket PC, Windows Mobile 5.0 Smartphone, Windows Mobile 6.0 Professional Edition, and Windows Mobile 6.0 Standard Edition. Select Windows Mobile Platform.
- Select Device Application from the templates.
- Specify the project name and click OK to create the project.
)
You will get a blank form. Using the Tool box you can add various controls to the form. In Chapter 7, Programming with ADO.NET, you will create examples and build a user interface for an SQL Server Compact Edition based application.
Setting Up References
Every project has a reference file that contains the run-time requirement of application for which the project is built. You should add a reference to SqlServerCe dll in your C# or VB.NET program. To add a reference, right click on References in Solution Explorer and choose Add Reference. The Add Reference dialog box shown in Figure 3.39 allows you to browse and add components. Under the .NET tab, select System.Data.SqlServerCe dll and click OK.
)
Figure 3.39 Add Reference Dialog Box
Once you have set up the reference, you need to add System.Data.SqlServerCe namespace in your program as shown in Listing 3.1.
Listing 3.1 Include namespace C#.NET
using System.Data.SqlServerCe;
Listing 3.1 Include namespace VB.NET
Imports System.Data.SqlServerCe
Using Data Sources
Visual Studio 2005 provides great features for connecting to databases. In this section you will learn Visual Studio features that assist in developing applications using the SQL Server Compact Edition database. Using the Data Sources dialog box, you can create new data sources and view existing SQL Server Compact Edition data sources. Within the Data Source window you can add a new data source, edit an existing dataset, and configure a dataset with Wizard.
The Data Source Window
You can view data sources by clicking on Data | Show Data Sources options. The Data Source window is a central place to see the data available for a project. You can drag objects from a Data Source window onto forms to create data-bound controls.
Configuration Wizard
Data Source Configuration Wizard helps manage the connection to Data Source. Using the Data Source Configuration Wizard, you can create and edit data sources. You can create a dataset consisting of one or more SQL Server Compact Edition tables. To connect to a data source click on Data | Add New Data Source. In Visual Studio you can create Data source from databases, Web Services, or Objects. You will get a dialog box similar to the one shown in Figure 3.40.
)
Figure 3.40 Choose a Data Source Type
Click on Database and then click on the Next Button. The next dialog box as shown in Figure 3.41 allows you to choose the connection and connection string.
)
Figure 3.41 Choose Data Connection
Click on New Connection. The Add Connection dialog box shown in Figure 3.42 allows you to connect to a selected data source. You can also click on the Change button to choose a different data source. For SQL Server Compact Edition you will choose .NET Framework Data Provider.
)
Figure 3.42 Add Connection Dialog Box
You can click on browse and select a different database file or create a new SQL Server Compact Edition database file by clicking on the Create button.
The password field allows you to specify the password for a password protected SQL Server Compact Edition database.
You can click on the Test Connection button to verify that connection to the database works.
Click the OK button.
The next dialog box shown in Figure 3.43 allows you to choose from the available objects that are displayed in a tree structure. After selecting the objects, you should provide a name for the dataset.
)
Figure 3.43 Choose Database Objects
On clicking Finish, a dataset schema will be added to Solution Explorer.
Adding SQL Server CE to a Visual Studio Project
You can also add an SQL Server CE database the same way you add a C# or VB class. In Solution Explorer right click on Add | Existing Item as shown in Figure 3.44. You can also use the Project | Add Existing Item menu option.
)
Figure 3.44 Adding SQL Server CE Database to a Project
In the Add Existing Item dialog box, select the Files of Type Microsoft SQL Server 2005 Compact Edition database file.
Browse to a specific file that you want to add to the project as shown in Figure 3.45. You will notice that dataset schema file is added to your project as shown in Figure 3.46.
)
Figure 3.45 Add Database to a Project
)
Figure 3.46 Data Set Schema in Solution Explorer
By default Visual Studio set the Copy to Output Directory property to Copy if Newer and Build Action property value to Content.
You can choose one of three values for Copy to Output Directory.
- The Copy if Newer value copies the database file from the project folder to bin (output folder) if the database file in the project folder is newer.
- The Copy Always value always copies the database file from the project folder to a bin folder.
- The Do Not Copy value does not copy the database file. You should copy the database yourself.
You can choose one of following values for the Build Action property:
- Compile value to compile the element.
- Content to include the element as content.
- Embedded Resource to treat the element as embedded resource.
- Do nothing with the element.
The SQL Server CE database should be included as content in a project. Visual Studio 2005 does this automatically for you.
Deploying Your Smart Device Application Using Visual Studio
Once you have built the application using Visual Studio, you will then want to execute it. For a desktop-based application you can run the executable from a command prompt or using the menu option Debug | Start without debugging.
For a device-based application you need to transfer the executable from a developer machine to a device. Visual Studio allows you to deploy the application directly on a device or on an emulator. You can use the following steps to deploy a smart device application onto a device or an emulator. You can also choose the device or emulator where you wish to deploy the application.
Emulators are virtual devices. Using Emulators available with Visual Studio 2005 you can develop and test the application on an emulator and you do not need a physical device.
- Click Debug | Start without debugging.
- The Deploy dialog box shown in Figure 3.47 is displayed. Choose whether to deploy an application on a device or on available emulators.
Figure 3.47 Deploy Dialog Box
- Select the device or emulator.
- You can uncheck the button "Show me this dialog each time I deploy the application."
- Click on the Deploy button.
)
If you opt not to get the Deploy dialog box, you will still choose whether to deploy on a device or on an emulator. From the Tool bar you can choose the option where to deploy your application. All the options in the Deploy dialog box are available in the Deployment Device combo box shown in Figure 3.48.
)
Figure 3.48 Deployment Device Combo Box
Deploying Multiple Times
When you develop a program, it is likely that you will need to improve it over many iterations. You will deploy the application on a device or on an emulator, test it, make changes in the program, and then deploy it again. By default when you click X on a .NET Compact Framework application, it does not close. It simply becomes minimized and keeps on running in the background. As one instance of the application is already running and you are trying to deploy another instance, you will receive an error. To remove the error you need to close the already running instance of the application. You can go to running programs and close the application.
In most of the examples mentioned in this book, the Minimize Box property is set to false. When you set this property to false, an OK button appears on the right corner of the main form of application instead of an X mark. Clicking on the OK button closes the application instead of minimizing it. You can click the OK button and close the application. Once the application is closed, you can modify the application and deploy the application again. You do not need to go to the running program section to stop the previous instance of the application.
Building a CAB File
Earlier versions of Visual Studio did not have support for building an installer project for .NET Compact Framework applications. Using Visual Studio 2005, you can build a Deployment project for Smart devices. Using Smart Device Cab Project you can drag and drop files, create folders, amend registry settings, and easily build the cabinet file for installation.
Cab files or Cabinet files are used to packages dll and executables. To build a CAB file for your application you should add a Smart Device CAB project to your solution. In the following steps you will learn how to create a Smart Device Cab project to install smart device applications.
- Click on File | Add | New Project.
- In the Add New Project dialog box, choose the Project type as Other Project Type and under Other Project type choose Setup and Deployment project.
- In the template section, select the Smart Device CAB Project.
- Specify the name of the project. In the example shown in Figure 3.49, the name SmartDeviceCab1 is used.
Figure 3.49 Setup and Deployment Project
- Click OK.
- Select the SmartDeviceCAB1 project.
- Click on the View | Properties Window.
- Change the ProductName as shown in Figure 3.50. In this example the name MyCAB is used.
Figure 3.50 Properties of a CAB Project
- To add the executable to the CAB file, open a file system editor. If it is not already open, right click on SmartDeviceCAB1 project, and click on View | File System option.
- In the File System Editor, click on Application Folder.
- Click on Action | Add | Project Output menu option as shown in Figure 3.51.
Figure 3.51 Project Output
- In the Add Project Output Group, click on Primary Output as shown in Figure 3.52 and click OK.
Figure 3.52 Add Primary Out
- Click on Build | BuildsmartDeviceCab1. SmartDeviceCab1 is name of my deployment project. You may get a different menu option based on your project name.
- Once you build the CAB file, you can find it under SmartDeviceCab1\Release folder.
)
)
)
)
You can copy the CAB file to a device or emulator. Just by clicking the file, the CAB file gets deployed on the device.
Changing Target Platform
In Visual Studio it is possible for you to change the target platform For example, you can change the Windows Mobile 5.0 Pocket PC platform to Windows Mobile 5.0 Smartphone or Windows Mobile 6.0 platforms by simply following these steps:
- Click on the Project | Choose Target Platform menu option.
- You will get a Change Target Platform dialog box as shown in Figure 3.53.
Figure 3.53 Change Target Platform
- Choose the New Platform in the Change to pop-up box.
- Click OK after selecting the new platform.
- A message box similar to one shown in Figure 3.54 is displayed telling you that the project will be closed and then reopened. Click Yes to continue.
)
)
Figure 3.54 Information Message
Visual Studio 2005 and SQL Server Compact Edition integration have a number of features to develop, build, and deploy .NET Compact Framework applications. The Data Source window displays the data object for all types including the SQL Server Compact Edition data object. Data Source Configuration Wizard enables you to add the SQL Server CE database into a project.
Visual Studio 2005 has also added support for building an installer project for .NET Compact Framework based applications.
Creating a Desktop Project
One of the main differences between SQL Server Compact Edition 3.1 and SQL Server Mobile Edition 3.0 is desktop support. Now you can use SQL Server Compact Edition to build a desktop-based application. Desktop support also enables you to develop applications in which desktop, laptop, and mobile devices can use the same database.
To use SQL Server Compact Edition in a desktop, you will create a Windows based project and add a reference to SqlServerCe dll installed for desktop. Follow these steps to create a Windows project using SQL Server Compact Edition
- Start Visual Studio. Click on File | New Project as shown in Figure 3.55.
Figure 3.55 Desktop Project
- The New Project dialog box appears. The dialog box allows you to choose project types—Visual Basic, Visual C#, Visual J#, Visual C++, etc. In this example, the Visual C# node is selected as shown in Figure 3.55.
- Select Windows Application from the templates.
- Specify the name of the project and click OK to create the project. You will get a blank form. Using the Tool box you can add various controls to the form.
- To use SQL Server Compact Edition you should add a reference to SqlServerCe dll. In Solution Explorer, click on References. Right click and choose Add References.
- In the Add Reference dialog box, click on the Browse button.
- Browse to locate where you have installed SQL Server Compact Edition. Earlier you learned that SQL Server Compact Edition for desktop gets installed at <Disk>:\Program Files\Microsoft SQL Server Compact Edition\v3.1.
- Select System.Data.SqlServerCe.dll and click the OK button as shown in Figure 3.56.
Figure 3.56 Add Reference to SQLServerCe Namespace
)
)
Once you have set up the reference, you need to add System.Data.SqlServerCe namespace in your program. At the top of the code include the SQLServerCe namespace as shown in Listing 3.2
Listing 3.2 Include namespace C#.NET
using System.Data.SqlServerCe;
Listing 3.2 Include namespace VB.NET
Imports System.Data.SqlServerCe
Now you can start writing code for a desktop application that uses SQL Server CE as a local store.
Using DataDirectory
You have learned how to specify the full path of an SQL Server Compact Edition database file. Using the DataDirectory feature you can specify the path of database in DataDirectory. You can set the DataDirectory property using Application Domain as shown in Listing 3.3.
With the DataDirectory feature you do not need to hardcode the full path of the database in connection string.
Listing 3.3 DataDirectory C#.NET
AppDomain.CurrentDomain.SetData ("DataDirectory",@"C:\Database");
Listing 3.3 DataDirectory VB.NET
AppDomain.CurrentDomain.SetData("DataDirectory", "C:\Database")
Instead of specifying the full path in a connection string, you will specify DataDirectory enclosed in | (pipe) symbol and the SQL Server Compact Edition filename as shown in Listing 3.4.
Listing 3.4 DataDirectory C#.NET
myConn = new SqlCeConnection
("Data Source=|DataDirectory|\\Email.sdf");
myConn.Open();
Listing 3.4 DataDirectory VB.NET
myConn = New SqlCeConnection _
("Data Source=|DataDirectory|\\Email.sdf")
myConn.Open()
Connecting to an Emulator
The device emulator is a desktop-based application that emulates the behavior of Windows Mobile or Windows CE based hardware. An emulator emulates an ARM processor. You can configure various properties of the emulator such as ROM size, orientation, and so on. Similar to a device, a partnership can now be created between ActiveSync and an emulator. The ActiveSync partnership is needed in applications that synchronize data between a desktop and a device.
There are three ways to connect to a device or an emulator:
- Develop a device project: Build and Deploy solution. A dialog box will appear where you can choose to develop or deploy the solution as shown in Figure 3.57.
Figure 3.57 Not Cradled Emulator
- Click on the Tools | Connect to Device option. A dialog box similar to the one shown in Figure 3.57 will appear where you can choose the platform, the device/emulator, and select Connect.
- Click on Tools | Device Emulator Manager. A Device Emulator Manager dialog box appears showing a list of emulators. Select an emulator, right click, and select Connect.
)
The connected emulator is shown with a green arrow.
Once the emulator is connected, you can deploy an application and execute the application on an emulator. A connected, but not cradled, emulator is shown in Figure 3.57
Cradling the Emulator
When the emulator starts, it acts as a device that is not cradled. A connected emulator can be used to execute an application locally on an emulator. If you need to synchronize data between a device and a server, the device needs to be cradled. Similar to a device, an emulator needs to be cradled. To cradle an emulator, you will choose the Cradle option in the Device Emulator Manager.
- Select the emulator in Device Emulator Manager, right click, and select Cradle.
- After the emulator is cradled, ActiveSync detects it, and the ActiveSync icon in the system tray will become green. You can also double click to start ActiveSync if it does not start automatically. Once the ActiveSync connection is established, it may display a dialog box to synchronize files. Click Cancel to establish a guest partnership between the emulator and the desktop as shown in Figure 3.58.
)
Figure 3.58 Cradled Emulator
Configuring ActiveSync for Emulator
The DMA option must be selected in the ActiveSync connection settings as follows:
- Click on the File | Connection Settings menu option.
- In the Connection Settings dialog box, check the box next to the "Allow Connection to one of following" option. Select DMA from the drop down box to enable DMA connections as shown in Figure 3.59.
)
Figure 3.59 DMA Settings
Visual Studio 2005 supports the Direct Memory Access (DMA) transport channel to communicate with a device emulator. DMA is faster than TCP/IP and does not require network connectivity. DMA is also faster and more robust than transmitting over a network stack as it provides a direct communication between two Windows processes– Visual Studio and emulator.
Using Windows Mobile 6.0
Microsoft released Windows Mobile 6.0 SDK in the beginning of 2007. Windows Mobile 6.0 is built on top of Windows Mobile 5.0. The taxonomy is changed between Windows Mobile 5.0 and Windows Mobile 6.0
Windows Mobile 5.0 differentiates devices such as Windows Mobile Pocket PC and Windows Mobile Smartphone. Pocket PCs and Pocket PCs with the phone edition have touch screen capability and accept screen input whereas Smartphone does not.
Windows Mobile 6.0 SDKs does not target specific devices. Windows Mobile 6.0 has the following two SDK:
- Windows Mobile Professional Edition targets the devices that have touch screen capability.
- Windows Mobile Standard Edition targets devices that do not have touch screen capability.
Once you install the Windows Mobile 6.0 SDKs, you will be able to develop corresponding project types in Visual Studio. Windows Mobile SDKs are installed in the same fashion as Windows Mobile 5.0 SDK.
New Features in Windows Mobile 6.0 SDK
Windows Mobile 6.0 devices include SQL Server Compact Edition and .NET Compact Framework in the ROM. Having these products in ROM have many benefits including:
- More remaining space available in RAM as .NET CF and SQL Server Compact Edition do not occupy RAM space.
- The reduction of the overall size of the installation package as it only contains the application component.
- A reduction in deployment effort and support level required for end users.
Windows Mobile 6.0 SDK provides Cellular Emulator. Using Cellular Emulator, you can test your application in various cellular communication situations from an emulator.
Using an emulator, you can also set the profile of your phone to headset, speaker phone, or car kit. The phone profile options and cellular emulator options in Emulator allow you to test your application in various real life scenarios.
Windows Mobile 6.0 provides a code signing utility, CABSignTool that signs together the Cab files and all of its constituent executables.
Windows Mobile 6.0 provides a Hopper unity to provide stress testing of mobile applications.
Windows Mobile 6 SDK provides Security Configuration Manager to configure the emulator with various security policies. Using these security policies, you can test the application with various security settings.
Windows Mobile 6.0 provides features and utilities to enhance the developer's experience. The inclusion of .NET CF and SQL Server compact Edition in ROM simplifies deployment. Device Emulator 2.0 included in Windows Mobile 6.0 allows you to test applications in real life scenarios without requiring a physical device.
You should consult the Windows Mobile documentation to get more details about Windows Mobile features.