Knowledgebase
Connecting to a SQL Server or MySQL Database in WebMatrix
Connecting to a SQL Server or MySQL Database in WebMatrix
WebMatrix includes SQL Server Compact, which is a lightweight version of Microsoft SQL Server that lets you create databases for your websites. When you create a database, it's added as an .sdf file in the App_Data folder of your website. Using SQL Server Compact is convenient, because the database is deployed simply by publishing or copying the .sdf file.
However, in some cases, you might want to use an existing database as part of your website. This is often required if you're using SQL Server (as opposed to SQL Server Compact) or MySQL. You can work with existing databases created in SQL Server, SQL Server Express, or MySQL. To add an existing database, you need to create a connection to the database.
Making the Connection
In order to connect to a SQL Server or MySQL database, you need the following:
A running instance of SQL Server 2005 or SQL Server 2008 (or the Express editions), or of MySQL versions 5 or 6. The database server can be installed on your computer or can be running on another computer.
The name of the database.
Credentials for the database. For SQL Server, this could either be the user you are logged in as (Windows authentication) or a database-specific user name and password (database authentication), depending on how the database is configured.
To connect to the database, follow these steps:
In WebMatrix, open the website you want to work with.
Click the Databases workspace.
In the Database group of the ribbon, click New Connection. The Connect to a Database dialog box is displayed.
Give the connection a name. This can be anything, but it's helpful to use a name that identifies the database you're connecting to (for example, SalesDataConnection).
Select the type of database you want to connect to (SQL Server or MySQL). Choose SQL Server if you're using either SQL Server or SQL Server Express.
Fill in the remaining fields with the information for the database.
If you want to add the database to your site, select Add to web.config. This applies to any type of site, including ASP.NET and PHP. Adding the database to the site makes it available for publishing later. When you add the database to your site, an entry for the database is put into the site's web.config file. (If you don't already have a web.config file, it's added automatically.)
If you just want to connect to a database to make changes to it and you're not intending to publish the database with your site, leave this option unselected.
If you're working with a SQL Server Express database, the completed dialog box will look similar to the following:
If you're working with a MySQL database, the dialog box might look like the following:
Click OK.
If the connection is successful, you see the database in the Databases workspace just like you see databases that you create using SQL Server Compact:
(If you don't select the Add to web.config option, the database will show up under the Other Connections node.)
Sidebar: Storing Connection Information
If you're using SQL Server Compact and your data is in a .sdf file in the App_Data folder, you don't need to do anything to work with the database in WebMatrix. WebMatrix automatically makes available (connects to) any .sdf database files in the App_Data folder of the current website. However, when you create a connection to a SQL Server or MySQL database, you need to provide connection information that tells WebMatrix where to find the database.
The information that you provide in order to make the connection is stored in a connection string. This is just a list of settings that might look like the following example:
Server=.\SQLEXPRESS;Database=TestDatabase
In this example, the server name (the name of the SQL Server instance) is .\SQLEXPRESS and the name of the database to connect to is TestDatabase.
A connection string for a MySQL database might look like the following:
Server=localhost;Database=Test;Uid=root;Pwd=password
When WebMatrix needs to connect to the database, it invokes the appropriate provider, which is the program that actually communicates with the database. (There are different providers for SQL Server and for MySQL.) WebMatrix just passes the connection string information to the provider, which then handles all the actual low-level database communications.
WebMatrix gives you the option to save connection information in the site's web.config file. This is useful if the connection information is an integral part of your website — for example, you're not just connecting to the database in order to do some maintenance. It's also useful to be able to copy the connection information to a remote server when you publish the site.
The web.config file is an XML file that contains an element for connection strings. If you've created connections to both a SQL Server Express database and a MySQL database (as in the examples earlier), the web.config file will look something like the following:
Each connection string appears as an element that contains the connection string itself as an attribute. Each element also contains a providerName attribute, which is how WebMatrix knows what type of database (SQL Server or MySQL) you want to use.
