The Data Explorer

In the top right of the Web Matrix window is a section with three tabs named Workspace, Data, and Open Items. You won't be surprised to know that the Data tab is where we're heading, so click on it now and you should see something like the following:

At the moment, the window is empty, except for a row of icons across the top. We'll see what all these do later on. Before we do, we need to make sure that you have everything set up ready to go.

Which Database Engine can I use?

The Data Explorer in Web Matrix can be used to work with either:

• The full installation of Microsoft SQL Server from version 7.0 upwards, and any of the editions such as the Developer Edition, Personal Edition, and so on.

• The Microsoft Data Engine (MSDE). This is a cut-down version of SQL Server, designed for desktop use rather than as an enterprise-level database.

You can always tell if the full version of SQL Server installed (if, for some reason, you don't remember installing it), by looking for the entry named Microsoft SQL Server in your Start menu. It's not so easy to tell if MSDE is installed. If you followed the setup instructions in Chapter 1 for installing .NET, Web Matrix, and the sample files then all should be well. If you intend to use MSDE, and you haven't followed the steps described in Chapter 1, then you need to head back and follow the steps described in the section titled Installing the Database Server Software in Chapter 1.

Connecting to a Database

OK, so we know that you've got a database server ready to work with, so it's time to see what we can do with the Web Matrix Data Explorer. The Try It Out that follows shows you how easy it is to connect to a database and view the contents. It uses one of the sample databases provided with SQL Server and MSDE (the pubs database), which can be installed on MSDE by following the steps described in the installation instructions in Chapter 1.

Try It Out—Connecting to a Database

1. Open Web Matrix if it's not already running, and create a new page called DBConnection.aspx.

2. Go to the Data Explorer window at the top right of the screen (click the Data tab) and then click the left-hand New Connection icon – the one that shows a database (the yellow cylindrical blob) with a wire and plug coming out of it. This opens the Connect to Database dialog:

   

3. Now you need to tell Web Matrix what database server to use. The default entry in the Server text box is localhost (as you can see in the screenshot above), which will connect to a local SQL Server database engine. If you are using the default named instance of MSDE, which was installed with the .NET Framework samples, you must change this entry to (local)\NetSDK. This is the instance name of the MSDE server on this (local) machine, as explained in Chapter 1 when you first installed MSDE.

4. If you are using MSDE, leave the option buttons in the center of the dialog set to the default of Windows authentication. This should also work if you are using SQL Server, providing that it runs under a system account.

   Note

   If you have problems connecting when using SQL Server (not MSDE), select SQL Server authentication and enter the user name and password for the sa (system administrator) account, or another account that has full administrative privileges for the database. However, the sa account should only ever be used for testing and trouble-shooting, and must never be used in the release version of any application.

5. Next, you must select the database you want to use. Later we'll create a new database, but for the time being we'll connect to an existing one. Click the down arrow of the Database: listbox, and, after a few seconds of furious disk activity, you will see a list of all the databases on the server you specified. Select the entry for pubs (the sample "publishers" database provided with SQL Server and MSDE):

   

6. Click OK, and you'll see that now the Data Explorer window shows the contents of the database. The top entry in the window is the database name preceded by the name of the server – in the following screenshot you can see that this is (local)\netsdk.pubs. Underneath this are collapsible lists showing the Tables and Stored Procedures within the pubs database. Double-click one of the table names, and the Edit Table window opens to show the contents of that table:

   

7. Close this window, and go back to the Data Explorer window. Expand the Stored Procedures group, and double-click on one of the entries in the list of stored procedures. The Edit Stored Procedure window opens, showing the stored procedure:

   

8. Another way to open either the Edit Table or Edit Stored Procedure dialog is to use the Edit icon on the toolbar within the Data Explorer window (the third one from the left). Select a table or stored procedure in the lists in the Data Explorer window and click this icon:

   

Stored Procedures and SQL Statements Explained

There are two main ways of working directly with databases using the tools available to that database. The first one is to use SQL statements. SQL (Structured Query Language) statements can be used for administrating our database. For example, we can select, add, delete, and sort information in the database, as well as administering database security, and much more. They are a way of administering the database by hand. Web Matrix has some great features that we'll learn about in this chapter that automate a lot of database processes, such as creating new databases, and adding tables, which would otherwise be a bit tricky to do by hand-coding SQL statements. An example of a simple SQL statement that will return a list of publishers from the Pubs database, similar to the list we saw in the example, is as follows:

 SELECT * From Publishers 

We'll learn more about SQL statements later in the chapter, as they will also help when it comes to working with databases in our websites.

The second method of working with a database is to use stored procedures. Stored procedures are a set of database instructions, rather like a mini-program, that are executed by the database to perform some specific task or tasks more efficiently than directly executing SQL statements. If you look at the code in the previous Try It Out, you can see that stored procedures use SQL statements to perform operations on the database (in our case, a SELECT statement). However, these statements are stored within the database, which means that the database can compile and optimize the statements to get the best performance.

Stored procedures also provide better security than using SQL statements directly. They "hide" the table and column names, because you only have to know the stored procedure name and what parameters it requires. Parameters are used to pass values into the stored procedure, and sometimes to receive values from it as well.

Connecting to More Than One Database

The Data Explorer window can connect to more than one database at a time. This is useful if the data you need to build your pages is not all in the same database. If you want to try this, go back to Step 2 and select a different database, repeating the process until you have all the connections you want. If you have both SQL Server and MSDE installed on the same machine, you can use this dialog to connect to databases from both simply by changing the local server name at the top of the dialog. You can also connect to SQL Server databases on other machines on your network by entering the name of the server instead of localhost. In this screenshot you can see that we've connected to three databases:

The first is the Northwind sample database in a local SQL Server database server. The second is the pubs database in the local instance of MSDE, and the third is the pubs database in a SQL Server database server that is running on a machine named delboy.

Displaying Data from a Database

Now that you know how to connect to a database, and what the Data Explorer window can do, we'll see how easy Web Matrix makes it to display some data. In the next chapter, we'll be looking at this topic in depth, but you'll be surprised just how quickly you can build a web page that extracts data from a database and builds an attractive display in a browser.

Try It Out—Displaying Data from a Database

1. Connect to the pubs database as demonstrated in the previous Try It Out.

2. Open the list of tables in the database, select the Publishers table by left-clicking on it, then left-click and drag it onto an empty ASP.NET page:

   

3. Web Matrix automatically adds two controls to the page for you – at the top is a SqlDataSourceControl, and below it a DataGrid. These two controls work together to display the data in the table you selected. We'll look at these two controls (and others) in more detail in the next chapter:

   

4. Now click the Start icon on the main Web Matrix toolbar to run the page. The page is then displayed, and you can see the values in the table shown within a grid – all with one simple drag and drop action! If you want to see that code that has been generated, just open the All tab in WebMatrix:

   

   Note

   The MxDataGrid control that is used in this page is a clever little beast. Notice that the column headings are hyperlinks, and that clicking one automatically sorts the rows on the values in that column. The grid also automatically divides the output into “pages” containing ten rows, and displays a list of the pages as hyperlinks at the bottom of the grid. Our table only has eight rows, and so there is only one page. Repeat this Try It Out with the titles table in the pubs database to see paging in action.

5. Before you finish this Try It Out, you can disconnect from the pubs database, as we aren't going to be using it again for a while. You don't actually need to do this, but it does help to reduce clutter in the Data window and free up a connection for other users if you are using a remote database. Select the top entry in the list in the Data Explorer window (the server and database name), and the icon on far right of the toolbar becomes available. Click this to close the connection to the database, and the Data Explorer window is cleared as well:

   

   Important

   Take care not to click the Delete icon (to the left of the Disconnect button) when you only want to disconnect from a database. A confirmation dialog is displayed to warn you that the complete database will be deleted, but it's easy to click OK without actually reading the warning!

Other Icons in the Toolbar

There are four icons on the toolbar in the Data Explorer window that we haven't used yet. We've looked at the first and the third icons so far, for creating connections, and editing information. We'll be using some of the other icons later on, but let's take a look at what they do now:

The second icon displayed in the Data Explorer is used to create a new table or stored procedure in the currently selected database. We'll use this to create a new table in the next Try It Out:
The fourth icon allows us to query the contents of one or more tables in the database. You'll see this towards the end of the chapter:
The fifth icon simply refreshes the lists in the Data Explorer window. To use it you must first select the entry named Tables (to refresh the list of tables), or Stored Procedures (you guessed it – to refresh the list of stored procedures!):
The sixth icon deletes whatever is currently selected in the lists in the Data Explorer window. You can select a table or a stored procedure, or the complete database (by clicking on the entry for the server and database name):