LANGUAGES: VB .NET
ASP.NET VERSIONS: 1.0 | 1.1
Use Web Matrix for DB Admin Tasks
Manage your database structures with this free tool.
By Wayne S. Freeze
One of the problems with using an MSDE database or a Jet (Access) database in an ASP.NET application is that neither database system includes database-administration tools. This means you need to acquire a SQL Server license to use Enterprise Manager with an MSDE database, or purchase Access to design Jet databases.
Version 0.6 of ASP.NET Web Matrix now includes basic tools that let you design database tables for Jet and MSDE databases. You also can edit stored procedures in MSDE. Of course, because MSDE shares the same code base as SQL Server, you also can use ASP.NET Web Matrix to create and edit tables and stored procedures in SQL Server.
Connect to a Database
To manipulate database objects, you need to establish a connection to the database you wish to use. This is done in Web Matrix by creating a new Data Project. You have a choice of creating an Access Database project or a SQL Server/MSDE Database project.
Once you choose a project, you're prompted to enter database connection information. If you choose an Access Database project, you'll be prompted for the name of the database file; if you choose a SQL Server/MSDE Database project, you'll be prompted to enter the information for a connection string. Both projects also give you the option to create a new database, which can be helpful if you are working with an Access database.
Once you're connected to the database, the objects in the database are displayed in the Data Workspace window (see Figure 1). Note that you can display information from multiple databases in the data workspace, which can include both Access and MSDE databases.
Figure 1. Information about the objects in a database is displayed in the Data Workspace window, and selecting a column in the table displays the column's properties in the column property window.
Once you connect to a database, this information is persisted from one Web Matrix session to the next until you remove the connection explicitly.
Work With Tables and Stored Procedures
Web Matrix lets you create new tables and change the structure of existing tables in your database. When creating a new table, you're be prompted for the name of the table. The new table is created with a single column named Column0. You can change the name of the column or any of its characteristics using the column property window (see Figure 1).
Note that in order to change the structure of an existing table, the table must be empty. But you can still view the table's structure using the editing tools.
You can switch from table design view to editing the contents of the data by clicking on the Data tab at the bottom of the main window. The data is displayed using a datagrid. To edit the contents of the table, it must have a primary key. But even if the table lacks a primary key, you can still view its contents.
In addition to modifying existing data, you also can insert and delete rows from the table. To delete a row, select the row's prefix and press the Delete key. To add a row, simply move to the bottom of the table and enter the new row. The new row must be compatible with all the constraints that could exist in the table even though you might not be able to view them in design mode.
If you're connected to an MSDE or SQL Server database, you also can create and edit stored procedures. Although the editing facilities are rather basic, you can check the syntax of your stored procedure by pressing the Save button. If the syntax is correct, the stored procedure will be saved to the database; otherwise, a message box containing the error is displayed.
Drag and Drop Tables
One nice feature of ASP.NET Web Matrix is its ability to drag and drop a table onto a Web page design surface (see Figure 2). This operation places two special Web Matrix controls on the Web page. The first control is either a SqlDataSourceControl or an AccessDataSourceControl, depending on the database you're using. This control retrieves from the table using a simple Select statement and the connection information you entered
Figure 2. Dragging a table on the Web page's design surface creates two controls - a data-source control and a datagrid control.
The other control is an MxDataGrid control, which adds some features to the standard datagrid control such as column sorting and paging. These features mean no additional code is required to create a simple program that displays the information from the table.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.