Creating a Database Project in VS 2005

It's a convenient focal point from which you can create, edit, test and deploy database scripts, all of which are under source control. This makes it an excellent tool for team based development. The Visual Studio IDE supports the project by providing its usual wizardry for data connections, a powerful query editor and various kinds of templates. This may be the most appropriate vehicle for integrating front end to back end in module-based applications. This basic tutorial shows you how such a project can be created to generate a SQL select query and execute it against a Microsoft Access database on a network node.

Creating a Database Project and establishing a database reference

With the VS Studio running click on File --> New Project. Expand the node Other Project Types by clicking on the + sign, and from the revealed items on the right, click on Database project as shown. It automatically points to Project1 for the project name; you change it to something of your choice. Herein it is called TestOledb. The project name and solution name are the same, and you have the option to create or otherwise handle the directory structure. The project file will be created by default at C:Documents and SettingsJayMy DocumentsVisual Studio 2005Projects (it will be different in yours), but can be changed to a different location.

You click OK to the above screen, which opens up the window Add Database Reference shown in the next picture, showing all the available database references. There are two references here: SQL 2005 server's Northwind database and the HR database on Oracle XE.

Creating a new reference database

Instead of using the existing references, let's add a new reference. The new reference will be working with a Microsoft Access database on a remote server. The database MDB file is on another machine, XPHTEK. Click on the Add New Reference... button. This opens up the window shown next, with SQLClient as the default datasource. SQL Client is good for SQL 2005 server, but is not for an MS Access Jet Database.

Click on the Change... button. This opens up the  window Change Data Source with MS SQL Server as the default. Change it to Microsoft Access Database File and click OK.

The wizard then opens the New Database Reference window, where you need to locate your mdb file. Click on Browse to locate the mdb file. It first opens the C:My Documents folder.

You navigate through your folders on your machine and locate the shared drive which points to the nwind.mdb file on a network share as shown.

Click on the button Open which takes you back to the New Database Reference window, now showing the Database File Name: C:HtekSharenwind.mdb. You may test the connection as no password is needed. Click OK and you will have created a new reference; the Add Database Reference window shows up again, as shown, and now includes the newly added reference.

Click OK to this. The TestoleDb project will now appear in the solution explorer as shown. It also shows the folder structure. It has three folders, Change Scripts, Create Scripts, Queries and the database reference you added recently to the Database References directory.

Creating a new query

Highlight the Queries node and right click to reveal the drop-down. From the drop down choose Add New Item... . You may also add an existing script, add a query, and so on. For now, we will be creating a query for the referenced database.

This opens up the templates window, Add New Item, with Database query in the Visual Studio installed templates collection. The default is Query1.dtq; we can change it to something different, details.dtq for example, and click on the button Add.

Several things happen immediately. Your Project explorer has the details.dtq added to the Queries folder as shown.

The Query Editor window with four panes, all empty, will be open.

The Add Table window will be in view with all the tables available from the referenced database, as shown in the next picture. In the Add Table dialog you can hold down either the Shift Key or the Ctrl key and choose multiple tables to be sent to the design pane. Here three tables have been highlighted; they will be used in the design of the query.

When you click on the Add button in the above window, the selected tables will appear in the design pane of the editor as shown, and a minimal (but incomplete) SQL statement will be generated, showing the joins made based on existing relationships. At this point no columns have been included in the query (right now it is not a query, but a template). You may now close the Add Table window.

SELECT     
FROM   (([Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID) INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID)

The many-to-many relationship between Orders and Products are related to each other through the Order-Details table, which has a one-to-many relationship with both tables. Let's now pick up a few columns to be added to the query: OrderID, OrderDate, UnitPrice, Quantity, and ProductName from the three tables. We do this by placing a check mark in the check boxes by the side of the column names in each of these tables as shown. Automatically the Criteria pane changes, and the SQL statement takes in all those columns that were added with proper table reference as shown.

The Query Editor

The Query Editor has four panes. The default view shows all the panes to the user. If you right click on an empty area in any pane you will bring up a context-sensitive drop-down where several types of actions may be performed. For example the next screen shows the various panes from one such drop-down. The four panes are Diagram, Criteria, SQL, and Results.

In the diagram pane you will be bringing in the tables and establishing relationships that you may want to be included in the query. You may also choose the columns from the tables to be included in the query results.

In the criteria pane you will be arranging for the sorting of the columns and their order, the visibility or otherwise of the columns that are included in the query, and any criteria that each of the columns need to satisfy. In the present example neither sorting (other than default) nor criteria were applied.

In the SQL pane you may verify the SQL syntax, and you can make changes to the SQL statement for testing what if scenarios, before you make a  final decision.

The results pane will show the result of the query in a tabulated view. It also shows how many rows were returned by the query. You also have the option of navigating through the results using Next, Previous, First and Last arrows.

In this drop-down you may also change the type of query (currently it is a Select query) you want as shown in the next picture. The various choices that are supported by the provider are shown.

You will see that in the SQL Pane you can verify as well as execute the SQL statement in view in that pane. In any of the panes you may execute the query. Additionally you also have a menu bar for some of the the database related activities as shown.

Running the query and viewing results

You may now execute the query from any of the panes, or from the menu bar at the editor. You will see the results as shown in the next picture. This query yielded 2155 rows of data with the first few rows in view.

You may now save the query by going to File-->Savequeriesdetails.dtq or Ctrl+S as shown in the next picture. You may also save it under another name.

If you now highlight the details.dtq query in the project explorer you can also see its properties as shown in the next picture. It shows the Query Editor-related information such as whether all columns were included, any parameters used, or any distinct value sought, and so forth.

Summary

This tutorial described the steps involved in creating a database project using the templates available in the Visual Studio 2005 IDE. The database project describes connecting to a networked Microsoft Access database and designing and evaluating the query which may be consumed by any other application created using the IDE.