How to use the Databases Workspace in WebMatrix
The WebMatrix Databases workspace makes data manipulation simple. It allows you to manage MySQL, SQL Server, and SQL Server Compact databases through the same interface—you don't have to learn a different tool for each database type. Here is a list of the main database tasks that you can perform with WebMatrix. The list is followed by step-by-step instructions for each task.
1. Access your site’s database
2. Create new databases
3. Connect to existing databases
4. Create new tables, views, and stored procedures
5. Execute queries and copy results
6. Migrate a SQL Server Compact database to a SQL Server database

Access your site’s database

After installing your new site, you can examine its database by changing to the Databases workspace:
clip_image001h
The tree on the left contains the database that was created for your site during the installation:
clip_image002
Expand the database icon to see the database's tables, views, and stored procedures:
clip_image003

Create New Databases

To create a database, right-click the Site node in the left pane, and then select the type of database you want to create:
clip_image004
Alternatively, you can use the New Database button in the ribbon. The button is split into an upper and lower half. To create a database of the same type as the existing one your site, click the upper half of the button; to choose from different database types, click the lower half:
clip_image005
After the database is created, a new database icon appears for it under the Site node.
When you create a database, a new database dialog is displayed. Enter the database name, server, and a set of user credentials that has permissions to create databases on the specific server.
clip_image006

Connect to existing databases

To connect to an existing database, right-click the Site node in the tree, and then select the type of database you want to connect to:
clip_image004[1]
Alternatively, you can select the New button in the connections group on the ribbon. The button is split into an upper and lower half. To create a connection of the same type as the current one, click the upper half; to choose from different connection types, click the lower half:
clip_image007
After the connection is created, a new connection icon appears under the Site node.
When you create a connection, a new connection dialog is displayed. Enter the database name, server, and a set of user credentials that has permissions to connect to a database on the specific server.
DB_SQLServerConnection

Create a Table

To create a table, first click the database in which you want to create a table. Then, on the Home tab of the ribbon, click the New Table button:
clip_image009
Alternatively, right-click the Tables node for the database you chose. From the shortcut menu, choose New Table:
clip_image010
In both cases, the new table will be displayed in its definition view. At this point, you can start creating columns for your table:
clip_image011

Create a View

To create a view, select the Views node within a database. In the ribbon, a View contextual tab appears. Click the View tab, and then click New View:
clip_image012
Alternatively, you can right-click the Views node under a database and select New View:
clip_image013
With both methods, a view editor will be displayed in which you can create the query that will define your view. When you are ready, you can execute the query to create the view.
clip_image014

Create a Stored Procedure

To create a stored procedure, select the Stored Procedures node within a database. In the ribbon, a Stored Procedure contextual tab appears. Click the Stored Procedure tab, and then click New Stored Procedure:
clip_image015
Alternatively, you can right-click the Stored Procedures node under a database and select New Stored Procedure:
clip_image016
In both cases, a stored procedure editor will be displayed in which you can define the query for your stored procedure. When you are ready, you can execute the query to create the stored procedure.
clip_image017

Execute Queries and Copy Results

To execute a query, first select the target database or any node under it. This enables the New Query button on the Home ribbon. Click the New Query button.
clip_image018
After you click the New Query button, the query editor is displayed. Type your query, and then execute it by clicking the Execute button on the ribbon:
clip_image019
The query results will be displayed in a grid:
clip_image020
If you copy data from the results view (or a table’s data view), WebMatrix puts it on the clipboard in a tabular format, which makes it easy to parse or format in other applications. As you navigate in the data list, WebMatrix highlights the active row and column for you.
1
Dashboard
3
-1
-1
NULL
False
-1
5/4/2011 9:42:09 AM
-1
5/4/2011 9:42:09 AM
2
Dashboard
1
37
-1
NULL
False
-1
5/4/2011 9:42:09 AM
-1
5/4/2011 9:42:09 AM
3
Dashboard
2
37
350
NULL
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM
4
Extensions
3
-1
-1
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM
5
Languages
3
-1
-1
NULL
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM
6
Skins
3
-1
-1
NULL
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM
7
Skin Designer
3
-1
-1
NULL
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM
8
WhatsNew
3
-1
-1
False
-1
5/4/2011 9:42:10 AM
-1
5/4/2011 9:42:10 AM

Migrate SQL Server Compact Database to SQL Server

To convert a database from SQL Server Compact to SQL, select the SQL Server Compact Database, and then click the Migrate button:
clip_image021
The Migrate Database dialog is displayed. Choose the server, the database name, and provide a set of credentials that has sufficient permissions to create a database. Press OK. When the migration completes, a notification band at the bottom will display the result of the operation:
clip_image022

You can discuss this article using the adjacent Facebook talkback.

For technical questions please visit our discussion forums, where we have a vibrant community of developers like you, as well as Microsoft engineers who are ready to answer your questions!