- Create a SQL Mobile database (part 1)

I recently conducted a small poll and it turns out that SQL Mobile is the most requested topic (see ongoing voting results here). So in this tutorial, we will cover how to create a SQL Mobile database for the PDA or emulator.

Having seen the SQL Server Express (mentioned here), let's now look at SQL Mobile.

Being the successor of SQL Server CE 2.0, SQL Mobile is Microsoft's lightweight database server that has a small enough footprint (~1.5MB) to fit and run smoothly in a Pocket PC device. It is included in Visual Studio .NET 2005, so you don't have to install anything as a developer. For end users, SQL Mobile can be installed on the Pocket PC 2003, Windows Mobile 5.0, Smartphone 5.0 as well as Windows CE 5.0 platforms. Word has it that SQL Mobile will be pre-installed in the ROM of all new Windows Mobile devices, just like Compact Framework 2.0.

In terms of features, SQL Mobile offers transactional support, multi-user access, as well as synchronization and replication with back-end servers. In terms of competition, I'm not too sure if these few players are still fighting close: Sybase SQL Anywhere, Oracle Lite and IBM DB2 Everyplace.

Alright, let's dive in to create our very first SQL Mobile database.

1. Start Visual Studio and create a new smart device project.
2. Click Data in the menu and select "Add New Data Source..." to see the following dialog window.




3. Select the Database icon (to indicate that you are creating a database) and click Next.
4. In the next screen, click "New Connection..." to create a new database.
5. In the the pop-up "Add Connection" window, click "Change..." to select a data source type. You should see the following:




6. Select "Microsoft SQL Server Mobile Edition" as the Data Source and leave the Data provider as ".NET Framework Data Provider for SQL Server Mobile Edition". Click OK.
7. Back at the "Add Connection" dialog, leave "My Computer" radiobutton selected, and then click "Create" to specify the filename of the SQL Mobile database. Enter "C:\FirstDB.sdf" for the filename and leave the other fields as they are (you can also specify the password here, but we leave it empty for convenience).




8. Click OK. If prompted on the empty password, click Yes.
9. You may now click "Test Connection" to check if the database is in order.
10. Click OK. Back at the first dialog window, click Next, and click Yes to add the database file to your project. In the next screen, click Cancel as we are not using generated DataSets in this tutorial.
11. You will notice that the FirstDB.sdf has been added to your project. Click on it and press F4 to check that its "Copy to Output Directory" property is "Copy if newer". This means that the file will be copied to the emulator if the emulator does not have a copy or have an older copy.
12. Now, from the menu, click View-> Server Explorer. And in the Server Explorer pane, click the "Connect to Database" icon (2nd icon from the right; or move your mouse over each icon to see the tooltip).
13. Click Browse and go to your project folder and select the FirstDB.sdf file. (Note that you can also create a database here, but care must be taken to add the created file to your project.)
14. Test the connection and click OK.
15. In the Server Explorer pane, expand the tree, right-click on Tables and select "Create Table". Enter the table name and create 2 columns for it, as shown below. Click on the image below to zoom in. (Note that the custid column has Identity set to true, so that this column's value for each record is auto-generated.)




16. Click OK.
17. Finally, run your program.
18. There's nothing interesting in your form. However, open File Explorer and browse to the folder which contains the deployed files (at \Program Files\yourProjName). You should see a file with the name "FirstDB" there. The .sdf extension is not displayed. Click on this file to open it with Query Analyzer. Query Analyzer allows you to explore and manage SQL Mobile databases from within the PDA environment.
19. In Query Analyzer, expand the tree under the Objects tab. You should see this:




20. That's it! You have created a simple SQL Mobile database and transferred it to the emulator/device. Part 2, coming soon, will cover how to create a SQL Mobile database programmatically.

Categories: [Data Access_] Tags: [netcf2] [Compact Framework] [SQL Server Mobile]