StartingLine
LANGUAGES: VB
TECHNOLOGIES: Web Matrix | SQL Server
Super Simple DB Development
Use Web Matrix's database tools to rev up your database applications.
By Wayne S. Freeze
If you haven't had a chance to try out the ASP.NET Web Matrix Project yet, you might assume it's merely a lightweight code editor for building ASP.NET applications. Web Matrix also includes several database-oriented features, however, which can make a big difference when building database applications. In this article, I'll explore how to use Web Matrix's database-design tools to create and edit SQL Server and Microsoft Data Engine (MSDE) databases and how to use Web Matrix's graphical tools to create data-bound user interfaces quickly.
Connect to a Database
Most Web-based applications today require a high-performance database server such as Microsoft's SQL Server to hold the application's data. Yet, it might not be cost-effective to give each developer a copy of SQL Server to run on his or her local computer. Frequently, developers install a copy of MSDE, which essentially is a version of SQL Server optimized (or restricted) to run in a desktop environment. This allows developers to create and test their applications on their development computers, knowing the applications will run without change on production computers using SQL Server.
One of the downsides of using MSDE for database development is it doesn't include any design tools. Visual Studio Enterprise Architect Edition has a rich collection of database-design tools, but not everyone can afford its high price tag. Enter ASP.NET Web Matrix.
Web Matrix includes basic tools that allow you to view and edit database structures as well as view the database's contents. You access these tools from the Data window located above the Properties window.
To access a database, you first must establish a connection using the New Connection button. This displays a dialog box that prompts you for the name of the database server, authentication information, and the name of the database you wish to access. You also can click on the Create a new database hyperlink to display another dialog box that prompts you for the name of your new database. Once you've established a connection to your database, the Data pane displays a hierarchical list of the database's tables and stored procedures (see Figure 1).
Figure 1. You can access the collection of tables and stored procedures
through the Workspace window's Data tab.
Design and View Tables
You can create a new table by selecting the Tables node and pressing the New Item button located in the toolbar at the top of the Data pane. If you want to see the design of an existing table, select the table from the list of tables and click on the Edit button. This displays the Edit Table pop-up window, which lists each row from the table in a data grid. At the bottom of the window is a hyperlink that allows you to edit or view the table's design. If the table is empty, the Edit Table Design window will be displayed; otherwise, the View Table Design window is displayed.
The Create New Table, Edit Table Design, and View Table Design windows are basically the same, differing only in which fields you can change. In the Create New Table window, you can change the name of the table and edit the column definitions. In Edit Table Design (see Figure 2), you can't change the name of the table, but you can edit the column definitions. If the table contains data, you can't make changes to the table's structure.
Figure 2. Using the Edit Table Design window, you can view the table's
structure, add and delete columns, and change the definition of each column.
Web Matrix's design tools have several limitations. First, Web Matrix changes a table's structure by dropping the existing table and creating a new replacement table. This means you can't make changes to a table that contains data. Also, if you have any indexes on the table or other attributes (such as a description of a column entered using the SQL Server tools), they will be lost. Web Matrix also requires you to specify a primary key for the table before you can save it. These aren't major limitations, however, and you can work around them easily by using simple SQL statements such as Select Into and Insert Select.
View and Edit Data
Being able to design a table is useful, but being able to view and edit the data stored in a table is even better. Web Matrix provides two crucial tools. The Edit Table window displays the data from a table using a data grid (see Figure 3). This allows you to view each row and column in the table and change the data if desired. You also can insert new rows or delete existing ones. This is useful when debugging the parts of your application that update data because you can reverse your updates easily and try it again.
Figure 3. Web Matrix's Edit Table tool makes quick work of editing the
data in a table.
Although editing a table is useful, you might want to run other types of queries. Clicking on the Query button displays the Data Query Tool. This tool allows you to enter a SQL Select statement and view its results displayed in a datagrid. Because this is a general-purpose query tool, you can't update the results as in the Edit Table window. But if you have a complicated query involving several joins, this tool can be invaluable.
Clicking on a column header in either the Edit Table window or the Data Query Tool sorts the data in the grid by column in ascending order. Clicking on the column header a second time sorts the grid by that column in descending order.
Web Matrix also lets you view and edit stored procedures. To create a new stored procedure, select the Stored Procedures node and press the New Item button. To edit an existing stored procedure, select the stored procedure's name and click on the Edit button. In either case, a pop-up window appears and allows you to edit the stored procedure. After you make your changes, simply click on the OK button to save the stored procedure to the database.
Drag-and-Drop DB Development
ASP.NET Web Matrix includes an interesting feature that allows you to drag and drop a table onto your application's Design or HTML view to create an instant database application (see Figure 4). This database application displays all the information from the table as a datagrid. The automatically generated datagrid supports pagination and sorting.
Figure 4. You can create a datagrid application by dragging and dropping
a table into Web Matrix's Design view.
The code generated by the drag-and-drop operation consists of two special Web controls. The first is SqlDataSourceControl, which contains information about how to access, retrieve, and update information stored in the database. It combines the functions of the SqlConnection, SqlDataAdapter, and DataSet classes to create a bindable data source, coupled with methods that allow you to insert, update, and delete data in the database.
The other control is MxDataGrid, which is based on the DataGrid control but contains properties that allow it to interact with the SqlDataSourceControl to retrieve and page through the data as well sort the data based on the selected column.
Program With Templates
Although the drag-and-drop technique to display a table's contents is interesting, it is by no means a complete application. But it does demonstrate some of the thought Web Matrix's creators put into this product. This attention to detail also shows up in the templates supplied with the product.
You can choose from seven different types of data-centric applications, including Simple Data Report, Filtered Data Report, Data Report with Paging, Data Report with Paging and Sorting, Master-Detail Grids, Editable Data Grid, and Simple Stored Procedure. Each of these templates uses standard ASP.NET controls such as DataGrid and the various classes found in System.Data and System.Data.SqlClient, rather than the custom controls used by the drag-and-drop table approach.
Before you can use the templates, you need to review the generated code and adjust it to fit your situation. You'll need to change things such as the connection string used to connect to the database and the SQL commands used to interact with the database. The code used in each template is heavily commented, and each change you make is identified by a comment containing the keyword TODO:.
Build Code With Code Builders
Templates provide a great starting point for a Web page that accesses the database, but sometimes you already have a Web page for which you need a little database code. Web Matrix has a facility named Code Builders - wizards that build subroutines to perform a specific task.
Web Matrix includes Code Builders that generate Select, Insert, Update, and Delete statements. Each wizard begins by prompting you for the same connection information the Data window uses. This information generates a connection-string variable used to initialize a SqlConnection object.
Then, each wizard's actions vary depending on the specific needs for the statement. The Insert Data method code builder prompts you for a list of fields to use in the statement. You have the option to assign a default value for each column.
When the wizard completes, it generates a function with a strongly typed parameter for each column that doesn't have a default value. Inside the function, a variable containing the Insert statement is created along with a SqlCommand object that executes the command. Each column in the table has a corresponding SqlParameter object, which is assigned either the default value you specified or the value from the corresponding parameter to the function. Then, the function executes the SqlCommand and returns the number of rows affected.
The Select Data method code builder prompts you for all the information needed to build the Select statement. It prompts you to choose the columns to be returned, or you can choose * (asterisk), which returns all columns. This wizard also includes a tool that helps you build the Where clause. You can specify filter criteria that reference a parameter to the function, or you can specify the information necessary to perform a join operation with another table.
Once the wizard gathers enough information to build your Select statement, it prompts you to test your query. This is a great way to verify that the Select statement returns the information you want. If you specify any filter criteria, you'll be prompted to enter sample values for the test. If you don't get the results you expect, you can go back and change the information used to create the Select statement.
Finally, the wizard asks you if you want the function to return a DataSet object or a DataReader object. Depending on the type of object you choose, the wizard generates code that creates a DataAdapter to fill a DataSet that is returned to the calling program, or the wizard creates and returns a SqlDataReader object.
Before each wizard ends, you are prompted for the name to associate with the routine. If you do not specify the method name, a default name is used, which can cause problems if you use the same wizard more than once inside a single class.
The wizards are designed to create standalone methods, but you might want to consolidate some of information in each method. Specifically, you could delete each copy of the connection-string information and replace it with a single class-level variable. This means if you must change the connection string down the road, you won't need to modify the copy in each routine.
Your Assignment
Some tasks are somewhat difficult to implement in ASP.NET. One such task is building an editable data grid. The Web Matrix template is complete and makes this a straightforward task. Your assignment is to use the Editable Data Grid template to create a Web page you can use to edit the contents of a table. Because there are exactly three TODOs out of the nearly 300 lines of code, you shouldn't use the pubs database referenced by the template.
You can download the solution to this assignment, along with the data I used for the database.
The sample code in this article is available for download.
Wayne S. Freeze is a full-time author with more than a dozen books 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 development experience and a master's degree in management information systems, as well as degrees in computer science and engineering. Visit his Web site at http://www.JustPC.com or e-mail him at mailto:[email protected].
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
