Super Simple DB Development

StartingLine

LANGUAGES: VB

TECHNOLOGIES: WebMatrix | SQL Server

Super Simple DB Development

Use Web Matrix's database tools to rev up your databaseapplications.

By Wayne S. Freeze

If you haven't had a chance to try out the ASP.NET WebMatrix Project yet, you might assume it's merely a lightweight code editor forbuilding ASP.NET applications. Web Matrix also includes severaldatabase-oriented features, however, which can make a big difference whenbuilding database applications. In this article, I'll explore how to use WebMatrix's database-design tools to create and edit SQL Server and Microsoft DataEngine (MSDE) databases and how to use Web Matrix's graphical tools to createdata-bound user interfaces quickly.

Connect to a Database

Most Web-based applications today require ahigh-performance database server such as Microsoft's SQL Server to hold theapplication's data. Yet, it might not be cost-effective to give each developera copy of SQL Server to run on his or her local computer. Frequently, developersinstall a copy of MSDE, which essentially is a version of SQL Server optimized(or restricted) to run in a desktop environment. This allows developers tocreate and test their applications on their development computers, knowing theapplications will run without change on production computers using SQL Server.

One of the downsides of using MSDE for databasedevelopment is it doesn't include any design tools. Visual Studio EnterpriseArchitect Edition has a rich collection of database-design tools, but noteveryone can afford its high price tag. Enter ASP.NET Web Matrix.

Web Matrix includes basic tools that allow you to view andedit database structures as well as view the database's contents. You accessthese tools from the Data window located above the Properties window.

To access a database, you first must establish aconnection using the New Connection button. Thisdisplays 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 databasehyperlink to display another dialog box that prompts you for the name of yournew database. Once you've established a connection to your database, the Datapane displays a hierarchical list of the database's tables and storedprocedures (see Figure 1).

Figure 1. You can access the collection of tables and stored proceduresthrough 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 topof the Data pane. If you want to see the design of an existing table, selectthe table from the list of tables and click on the Editbutton. This displays the Edit Table pop-up window, which lists each row fromthe table in a data grid. At the bottom of the window is a hyperlink thatallows you to edit or view the table's design. If the table is empty, the EditTable Design window will be displayed; otherwise, the View Table Design windowis displayed.

The Create New Table, Edit Table Design, and View TableDesign windows are basically the same, differing only in which fields you canchange. In the Create New Table window, you can change the name of the tableand edit the column definitions. In Edit Table Design (see Figure 2), you can'tchange the name of the table, but you can edit the column definitions. If thetable 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'sstructure, 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 andcreating a new replacement table. This means you can't make changes to a tablethat contains data. Also, if you have any indexes on the table or otherattributes (such as a description of a column entered using the SQL Servertools), they will be lost. Web Matrix also requires you to specify a primarykey 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 statementssuch as Select Into and Insert Select.

View and Edit Data

Being able to design a table is useful, but being able toview and edit the data stored in a table is even better. Web Matrix providestwo crucial tools. The Edit Table window displays the data from a table using adata grid (see Figure 3). This allows you to view each row and column in thetable and change the data if desired. You also can insert new rows or deleteexisting ones. This is useful when debugging the parts of your application thatupdate 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 thedata in a table.

Although editing a table is useful, you might want to runother types of queries. Clicking on the Querybutton displays the Data Query Tool. This tool allows you to enter a SQL Select statement and view itsresults 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 acomplicated query involving several joins, this tool can be invaluable.

Clicking on a column header in either the Edit Tablewindow or the Data Query Tool sorts the data in the grid by column in ascendingorder. Clicking on the column header a second time sorts the grid by thatcolumn in descending order.

Web Matrix also lets you view and edit stored procedures.To create a new stored procedure, select the StoredProcedures node and press the New Itembutton. To edit an existing stored procedure, select the stored procedure'sname and click on the Edit button. In either case, apop-up window appears and allows you to edit the stored procedure. After youmake your changes, simply click on the OK buttonto save the stored procedure to the database.

Drag-and-Drop DB Development

ASP.NET Web Matrix includes an interesting feature thatallows you to drag and drop a table onto your application's Design or HTML viewto create an instant database application (see Figure 4). This databaseapplication displays all the information from the table as a datagrid. Theautomatically generated datagrid supports pagination and sorting.

Figure 4. You can create a datagrid application by dragging and droppinga table into Web Matrix's Design view.

The code generated by the drag-and-drop operation consistsof two special Web controls. The first is SqlDataSourceControl, whichcontains information about how to access, retrieve, and update informationstored in the database. It combines the functions of the SqlConnection, SqlDataAdapter,and DataSet classes to create a bindable data source, coupled with methodsthat allow you to insert, update, and delete data in the database.

The other control is MxDataGrid, which is based onthe DataGrid control but contains properties that allow it to interactwith the SqlDataSourceControl to retrieve and page through the data aswell sort the data based on the selected column.

Program With Templates

Although the drag-and-drop technique to display a table'scontents is interesting, it is by no means a complete application. But it doesdemonstrate some of the thought Web Matrix's creators put into this product.This attention to detail also shows up in the templates supplied with theproduct.

You can choose from seven different types of data-centricapplications, including Simple Data Report, Filtered Data Report, Data Reportwith Paging, Data Report with Paging and Sorting, Master-Detail Grids, EditableData Grid, and Simple Stored Procedure. Each of these templates uses standardASP.NET controls such as DataGrid and the various classes found in System.Dataand System.Data.SqlClient, rather than the custom controls used by thedrag-and-drop table approach.

Before you can use the templates, you need to review thegenerated code and adjust it to fit your situation. You'll need to changethings such as the connection string used to connect to the database and theSQL commands used to interact with the database. The code used in each templateis heavily commented, and each change you make is identified by a commentcontaining the keyword TODO:.

Build Code With Code Builders

Templates provide a great starting point for a Web pagethat accesses the database, but sometimes you already have a Web page for whichyou 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 beginsby prompting you for the same connection information the Data window uses. Thisinformation generates a connection-string variable used to initialize a SqlConnectionobject.

Then, each wizard's actions vary depending on the specificneeds for the statement. The Insert Data method code builder prompts you for alist of fields to use in the statement. You have the option to assign a defaultvalue for each column.

When the wizard completes, it generates a function with astrongly typed parameter for each column that doesn't have a default value.Inside the function, a variable containing the Insert statement iscreated along with a SqlCommand object that executes the command. Eachcolumn in the table has a corresponding SqlParameter object, which isassigned either the default value you specified or the value from thecorresponding parameter to the function. Then, the function executes the SqlCommandand returns the number of rows affected.

The Select Data method code builder prompts you for allthe information needed to build the Select statement. It prompts you tochoose the columns to be returned, or you can choose * (asterisk), whichreturns all columns. This wizard also includes a tool that helps you build the Whereclause. You can specify filter criteria that reference a parameter to thefunction, or you can specify the information necessary to perform a joinoperation with another table.

Once the wizard gathers enough information to build your Selectstatement, it prompts you to test your query. This is a great way to verifythat the Select statement returns the information you want. If youspecify any filter criteria, you'll be prompted to enter sample values for thetest. If you don't get the results you expect, you can go back and change theinformation used to create the Select statement.

Finally, the wizard asks you if you want the function toreturn a DataSet object or a DataReader object. Depending on thetype of object you choose, the wizard generates code that creates a DataAdapterto fill a DataSet that is returned to the calling program, or the wizardcreates and returns a SqlDataReader object.

Before each wizard ends, you are prompted for the name toassociate with the routine. If you do not specify the method name, a defaultname is used, which can cause problems if you use the same wizard more thanonce inside a single class.

The wizards are designed to create standalone methods, butyou might want to consolidate some of information in each method. Specifically,you could delete each copy of the connection-string information and replace itwith a single class-level variable. This means if you must change theconnection string down the road, you won't need to modify the copy in eachroutine.

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 iscomplete and makes this a straightforward task. Your assignment is to use theEditable Data Grid template to create a Web page you can use to edit thecontents of a table. Because there are exactly three TODOs out of the nearly 300 lines of code, you shouldn't use thepubs database referenced by the template.

You can download the solutionto this assignment, along with the data I used for the database.

The sample code in thisarticle is available for download.

Wayne S. Freeze isa full-time author with more than a dozen books to his credit, including Windows Game Programmingwith Visual Basic and DirectX (Que), and Unlocking OLAP with SQL Server and Excel 2000 (HungryMinds). He has more than 25 years of development experience and a master'sdegree in management information systems, as well as degrees in computerscience and engineering. Visit his Web site at http://www.JustPC.comor e-mail him at mailto:[email protected].

Tell us what you think! Please send any comments aboutthis article to [email protected] include the article title and author.