RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


A Lap Around SQL Server 2005 Compact Edition : Page 2

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!

Connecting with Server Explorer
Open Visual Studio and open the Server Explorer pane. Click the "Connect to Database" button as shown in Figure 1.

Figure 1. Connecting Via Server Explorer: Click the "Connect to Database" button in Visual Studio's Server Explorer.
In the "Choose Data Source" window that appears, select "Microsoft SQL Server Mobile Edition" from the list of data. In the "Add Connection" window that appears you are given two options:

  1. Create a new data file,
  2. Browse for an existing database file.
In this case, you're starting from scratch, so click the "Create..." button to create a new database file (see Figure 2).

You can browse for a location for the database and give it a name of your choosing. Note that you have the option to encrypt the data by checking the Encrypt checkbox. Finally, you must supply a single password used to secure the contents of the database. All applications that access the database will use the same password—SQL Server Compact does not support the creation of users within the database. When you've supplied all the information, click OK to create the database file.

When you've completed the database creation process, you'll be returned to the "Add Connection" window, with the database filename and password fields now populated (see Figure 3)

Figure 2. Database Creation Dialog: In this window, choose a location, filename and password for the new database file.
Figure 3. Adding a Connection: After creating a database, the "Add Connection" window now shows the location of the database file and the password you configured for the new database.
If you choose to select an existing database file instead, you'll have to fill in the Password field manually. Click the "Test Connection" button to verify the location of the database and the password. In this case, because the information was populated as a result of creating a new database, the test should be successful.

Figure 4. The Advanced Properties Window: This window shows the connection string you should use when writing code to connect to the database. Copy and paste this connection string into your project.
You may have noticed the "Advanced..." button in the "Add Connection" dialog, which is useful because it provides the connection string you will use later when writing code to connect to the database. Figure 4 shows the "Advanced Properties" window with the connection string selected.

Finally, click the "OK" button to open the database in Visual Studio's Server Explorer. Figure 6 shows the Server Explorer with the newly created DevXSample database open. Using the Server Explorer, you can create and manage tables, edit data within the tables, etc. Because the tools available for a SQL Server Compact database act so similar to the other standard SQL Server tools for managing tables and data, I won't belabor that topic.

Remember, there are two ways to connect to a SQL Server Compact database, using Visual Studio Server Explorer, as just discussed, and using SQL Server 2005's Management Studio. Note that the Management studio is not shipped with SQL Server Compact.

Connect Using SQL Server Management Studio
The other way to connect to SQLce databases is through SQL Server 2005's SQL Server Management Studio. Open SQL Server Management Studio and select "SQL Server Mobile..." from the Connect menu, as shown in Figure 5.

Figure 5. Connecting with SQL Server Management Studio: Select "SQL Server Mobile..." from the Connect menu within Microsoft SQL Server Management Studio.
Figure 6. Connected Database: The figure shows the Management Studio connected to the DevXSample database.
This example browses for the new database file created in the previous section using the Visual Studio Server Explorer. Select "Browse for More..." from the "Database File" dropdown. Find the database file you want to open, and enter the password. When you click "Connect" SQL Server Management Studio will open the file for modification, as shown in Figure 6.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date