Rapid Application Development Using SQL Server 2005 Compact Edition and Visual Basic.NET
Microsoft Visual Studio 2005
Microsoft SQL Server 2005 Compact Edition
Summary: In this tutorial, you will learn how to leverage the design capabilities integrated into Visual Studio 2005 in order to rapidly develop applications that use data stored in a SQL Server 2005 Compact Edition database. You will be using Visual Basic.NET in this tutorial. There is another tutorial that shows how to use Visual C#.NET to perform the same task. (21 printed pages)
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 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.
- Visual Studio 2005 Service Pack 1
- SQL Server 2005 Compact Edition
Estimated time to complete this tutorial
In this tutorial, you will learn how to leverage the design capabilities integrated into Visual Studio 2005 in order to rapidly develop applications that use data stored in a SQL Server 2005 Compact Edition database. You will be using Visual Basic.NET in this tutorial. There is another tutorial that shows how to use Visual C#.NET to perform the same task.
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 exercises:
- Use the Visual Studio 2005 Designer to build an application that allows the user to access and update SQL Server 2005 Compact Edition data
- View, create, and administrate SQL Server 2005 Compact Edition data from within Visual Studio 2005
Exercise 1: Using the Visual Studio 2005 Designer to Build a Form that Allows the User to Access and Update SQL Server 2005 Compact Edition Data
In this exercise, you will learn how to use the Visual Studio 2005 Designer to build a form that allows the user to access and update SQL Server Compact Edition data.
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 Basic | Windows.
Note Depending on your Visual Studio configuration, Visual Basic might appear under Other Languages.
- In the Templates box, select Windows Application.
- Change the Name to RADDataDemo, as shown in Figure 1, and then click OK.
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 set up the form to display and update data from an existing SQL Server Compact Edition database.
To set up a SQL Server Compact Edition database as a data source
Note Visual Studio's Data Source Configuration Wizard allows you to add a data source to your project. You can choose from various data sources, including SQL Server and SQL Server Compact Edition, web services or in-memory objects. In this lab, you will choose the SQL Server Compact Edition Northwind example database as your application's data source. In most cases, you use the data source to bind data to the application's user interface.
- In Visual Studio, click Data | Show Data Sources to open the Data Sources pane.
- Within the Data Sources pane, click the Add New Data Source button located at the top-left of the Data Sources pane, as shown in Figure 2.
The Data Source Configuration Wizard appears.
Figure 2. Clicking Add New Data Source
- Select Database as the data source type and click Next, as shown in Figure 3.
Figure 3. Choosing a data source type in the Data Source Configuration Wizard
- On the Choose Your Data Connection page, click New Connection.
Note If you had previously created a connection string to the desired database then it would be available to choose from the drop-down box and you would not have to create a new connection.
- On the Choose Data Source dialog box, select Microsoft SQL Server 2005 Compact Edition and then click Continue, as shown in Figure 4.
Figure 4. Changing the data source
- On the Add Connection dialog box, be sure the My Computer option is selected as the Data Source, as shown in Figure 5. This allows you to select a database file from your computer's hard drive as a data source.
- Still on the Add Connection dialog box, click Browse to open the file-selection dialog box.
Figure 5. Adding a connection
- Select the file C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf in the file-selection dialog box and click Open to select the database.
- On the Add Connection dialog, click Test Connection to verify that the database connection is setup properly. If the database file exists and SQL Server Compact Edition has been properly installed, then a dialog box informs you that the connection succeeded.
- Click OK to dismiss the Test Connection Succeeded dialog box.
- Click OK on the Add Connection dialog box now that you have successfully tested the connection.
- You can now expand the Connection string on the Choose Your Data Connection dialog box to view the connection string that has been generated, as shown in Figure 6.
Figure 6. Choose Your Data Connection dialog box, displaying the selected connection string
- Click Next on the Choose Your Data Connection dialog box.
- A prompt appears asking whether or not you would like to copy the local data file into your project and modify the connection accordingly, as shown in Figure 7. Click Yes to copy the local data file into the project.
Figure 7. Prompt regarding copying of local data file to the current project
- When you are prompted to save the connection string, be sure the Yes, save the connection as check box is checked. Leave the connection string named NorthwindConnectionString, and then click Next, as shown in Figure 8. It is always a good idea to save any connection strings in an application's configuration file to make it easy to reference different copies of database files without rebuilding and redeploying the application.
Figure 8. Save the Connection String dialog box
- On the Choose Your Database Objects page, expand Tables and check the Customers, Employees, Orders, and Shippers tables to include them in the dataset, as shown in Figure 9.
Figure 9. Choose Your Database Objects dialog box
- Leave the default DataSet name of NorthwindDataSet, and then click Finish, as shown in Figure 9.
Visual Studio creates the dataset, which then becomes visible in the Data Sources pane.
To create form elements from a table in a dataset
Note Using Visual Studio, you can specify the default controls to associate with each column in a database table and then simply drag and drop the table columns or even the entire table onto a form to create and set up data-bound controls easily. You will be making use of these features to build a form that users can use to view and modify database values.
- In the Visual Studio Data Sources pane, expand the NorthwindDataSet to display all of the tables contained in the DataSet if the tables aren't already visible.
- Expand the Orders table to display the list of table columns.
- Select the drop-down on Orders and choose Details, as shown in Figure 10.
Choosing Details will generate individual controls for each column in the table when the table is placed on a form.
Figure 10. Changing the view of the Orders table to Details
- Change the default control for the Order ID column to a Label by clicking the drop-down box on Order ID and selecting Label. In a similar fashion change the default controls for the Customer ID, Employee ID, and Ship Via columns to ComboBox.
- Drag and drop the Orders table onto the form.
Visual Studio creates controls for each of the columns in the table, a database navigation control, and all necessary data bindings used to link these controls to the table. This results in the form shown in Figure 11.
Figure 11. The form and component tray after dragging the Orders table onto the form
- Drag the Customers table onto the Customer ID ComboBox in the form. This causes Visual Studio to create a binding between the Customers table and the ComboBox so that the selections listed in the ComboBox drop-down box are automatically populated with the contents of the Customers table. Rather then displaying the actual customer numbers, the drop-down box displays the last name of each customer. The customer last name is displayed because Visual Studio automatically modifies the data bindings so that the display value is set to the first string column in the table that is not part of the primary key. In the case of the Customers table, the first string column that is not part of the primary key is the Last Name column.
- Using the same technique as the previous step, create the data bindings to populate the Employee ID ComboBox drop-down box and Ship Via ComboBox drop-down box with the contents of the Employees table and the Shippers table respectively.
- Enlarge the form on the right side, so that there is room for you to place a PictureBox control.
- Click View | Toolbox to open the Toolbox pane and expand Common Controls.
- Drag the PictureBox control from the Toolbox to the empty area on the right side of the form.
- Click the smart-tags arrow button at the top right of the PictureBox to display the smart-tags menu. On the smart-tags menu, change the Size Mode property to AutoSize, as shown in Figure 12. When you set the Size Mode property to AutoSize the PictureBox automatically resizes based on the size of the image displayed.
Figure 12. Selecting PictureBox Size Mode (Click on the picture for a larger image)
- In the Data Sources pane, expand the Employees table and drag the Photo column onto the PictureBox control that you just created. Visual Studio then automatically data binds the PictureBox to the Photo column.
Note You could have created the data bound PictureBox by dragging the Photo column directly from the Data Sources pane onto the form just as you did for the other columns in the Orders table. The alternate technique of first placing the PictureBox on the form and then dragging the Photo column from the Data Sources pane onto the PictureBox is included in this tutorial to demonstrate that you can first layout the controls on a form and then associate the data bindings afterwards.
To test the application
- In Visual Studio, click Debug | Start Debugging.
When the application appears, you should see the form fully populated with data and the photo of the employee associated with the order, as shown in Figure 13.
Figure 13. The application running
- Change the value in the Ship Country field from Italy to Italia and click the Save Data icon. This will save your changes to the database.
- Close the form to exit the application.
- Run the application again by clicking Debug | Start Debugging in Visual Studio.
- Notice that the value for Ship Country is now Italia indicating that the changed data was properly saved to the database.
You can also use the navigation controls at the top of the form to browse through database entries, add new database entries, and delete database entries.
- Close the form to exit the application.
Exercise 2: Viewing, Creating, and Administrating SQL Server 2005 Compact Edition Data from Within Visual Studio 2005
In this exercise, you will learn how to use Visual Studio 2005 to view and modify data in an existing SQL Server 2005 Compact Edition database and how to create and manipulate a new SQL Server 2005 Compact Edition database.
To view data in an existing data connection
- In Visual Studio, click View | Server Explorer.
The Server Explorer pane is displayed, as shown in Figure 14. The Northwind.sdf data connection that you created previously in this tutorial should be available for you to browse. Other data connections that you previously created might also be available.
Figure 14. The Server Explorer pane showing existing data connections
- In the Server Explorer pane under Data Connections, expand Northwind.sdf, and then expand Tables to view all of the tables in the Northwind.sdf database.
- Using the Server Explorer, you can view the contents of any of the tables in the database. To view the contents of the Orders table, right-click on the Orders table and select Open.
The data in the Orders table is now displayed in a table format, as shown in Figure 15. The table view maintains an active connection to the database; therefore, you can both view and modify the data in the Orders table.
Figure 15. Table view of the data in the Orders table (Click on the picture for a larger image)
When you execute your application from within Visual Studio, Visual Studio makes a copy of the project database file in the project output directory (normally the bin\Debug subdirectory) which is the copy of the database file that the application uses. By using a separate copy of the database file, you can modify the copy of the database file as much as you would like during application testing without modifying the original version of the database.
The Northwind.sdf connection in Server Explorer that you created earlier in this tutorial references the original Northwind.sdf database file not the copy in the output directory; therefore, any changes the application makes to the database are not visible in this view. To view the database file that the application is using you must create a new Server Explorer connection that references the database file in the output directory.
By default, Visual Studio copies the original database file to the output directory only if the database file does not already exist in the output directory or if the original database file has been updated more recently then the copy in the output directory. If you would like Visual Studio to copy the original database file to the output directory every time you start your application, right-click on the database file in the Solution Explorer, choose Properties and then set the Copy To Output Directory property to Copy Always.
- Close the Orders table window.
To create a new database
- In the Server Explorer pane, right-click Data Connections and click Add Connection... to display the Add Connection dialog box.
- In the Add Connection dialog box, if the Data Source field is not already set to Microsoft SQL Server Compact Edition, then click Change... and change the value to Microsoft SQL Server Compact Edition.
- In the Add Connection dialog box, be sure the My Computer option is selected as the Data Source. This allows you to select a database file from your computer's hard drive as a data source.
- Still in the Add Connection dialog box, click Create.
The Create New SQL Server Compact Edition Database window appears, as shown in Figure 16.
Figure 16. The Create New SQL Server Compact Edition Database dialog box
- Using the directory of your choice, enter the fully qualified database file name with the database file itself named as NewDatabase.sdf. For example, to create the database file in a folder named C:\MyProjects, enter the database file name as C:\MyProjects\NewDatabase.sdf.
Note SQL Server Compact Edition does not require that you give the file name an extension of .sdf. However, you are encouraged to use the .sdf extension always when creating SQL Server Compact Edition files, because all of the tools expect the SQL Server Compact Edition database files to have this extension.
- Check Encrypt in order to add security to the database.
- In order to create an encrypted database, you must provide a password for the database. Type the word password into both the New Password and Confirm Password fields.
- Click OK.
As shown in Figure 17, a dialog box indicating that the password chosen does not meet minimum security requirements is displayed. For the purposes of this tutorial, you can leave the database password as password.
Figure 17. The password minimum-security-requirements warning dialog box (Click on the picture for a larger image)
- Click Yes to continue using the selected password.
- The Add Connection dialog box should now appear, as shown in Figure 18. Click OK to create the database connection.
Visual Studio creates a connection to the new database that will now become visible in the Server Explorer pane.
Figure 18. Adding a connection to a new password-protected database
To create tables in a database
- In the Server Explorer pane, expand the NewDatabase.sdf data connection you just created.
- Right-click on Tables and select Create Table.
The New Table window is displayed.
- Type Customer in the Name field to set the name of the new table.
- Add a column by clicking in the cell under the heading Column Name and typing the column name. Name this column Id.
- Now, set the remaining descriptors for the column: Set the Data Type to int, Allow Nulls to No, Unique to Yes, and Primary Key to Yes, as shown in Figure 19.
The Id column will be used as a primary key field.
Note For a table with a multi-part primary key, set the Primary Key value to Yes for each of the columns that composes the primary key.
- Add a column named Name and leave its associated values as the defaults, as shown in Figure 19.
Figure 19. Adding columns to a new table
- Click OK to create the table.
Visual Studio creates the Customer table. The Customer table is now visible in the Server Explorer pane, as shown in Figure 20.
Figure 20. The newly created Customer table
- Right-click on the Customer table and select Open.
An empty table listing the data in the Customer table is displayed.
- Add a record to the Customer table with 101 as the Id and Fred as the Name.
- Add another record to the Customer table with 102 as the Id and Barney as the Name.
You have now successfully added two records to the Customer table, as shown in Figure 21.
Figure 21. The Customer table containing new entries
- Close the view of the Customer table by clicking the X in the top right-hand corner of the view. The data for each row was saved to the Customer table when you moved the cursor to the next row; therefore, there is no need to explicitly save the table before closing the view.
Creating a new query
- In the Server Explorer pane, expand the data connection for NewDatabase.sdf if it is not already expanded.
- Right-click on Tables and select New Query.
The Add Table dialog box is displayed listing the tables that are available in the current database. In the case of this database, the only table that is available is the Customer table.
- Select the Customer table and click Add to make it part of the query. The Query Designer displays the Customer table listing the table columns.
- Click Close to close the Add Table dialog box.
- Check the box to the left of the Id and Name columns in the Customer table to indicate that the query will return these columns.
- Notice that the Query Builder displays the query text that corresponds to the table and columns you have selected. In some cases it is easier to simply type part of the query text directly rather then always use the Query Designers graphical interface. Modify the query text to have the clause WHERE Name LIKE @name at the end, as shown in Figure 22.
As you can see, the Query Designer supports SQL parameters such as @name. The Query Designer automatically prompts you to enter the parameter values when you execute the query.
Figure 22. Query created to select data from the Customer database
- Run the query by clicking Query Designer | Execute SQL in Visual Studio.
A prompt requesting the value of the @name parameter is displayed.
- Enter the text f% as the value of the @name parameter so that the query returns all records where the name field begins with the letter f. Click OK to see the query results.
The query results are then displayed, as shown in Figure 23.
Figure 23. Query results being displayed
- Close the query designer window by clicking the X in the top right-hand corner of the window.
Performing database maintenance
The goal of this last section is to simply make you aware of the maintenance features that are available for SQL Server Compact Edition databases. All of the maintenance features you see in this section are also available programmatically through the System.Data.SqlServerCe.SqlCeEngine class.
- In the Server Explorer pane, right-click on the data connection for NewDatabase.sdf and select Database Properties.
The Database Properties window is displayed, as shown in Figure 24.
Figure 24. The Database Properties window
- Notice that the Database Properties window opens to the General page which displays statistics regarding the connected database.
- Select the Shrink and Repair page to display options for compacting and repairing the database.
- Select the Set Password page to display a page that allows you to change the database password.
- Click Cancel to close the Database Properties window.
In this tutorial, you performed the following exercises:
- Using the Visual Studio 2005 Designer to build an application that allows the user to access and update SQL Server 2005 Compact Edition data
- Viewing, creating, and administrating SQL Server 2005 Compact Edition data from within Visual Studio 2005
In this tutorial, you learned how to leverage the design capabilities integrated into Visual Studio 2005 in order to rapidly develop applications that use data stored in a SQL Server 2005 Compact Edition database.