Dreamweaver

If you're using Dreamweaver to build ASP.NET-driven Web sites, you've undoubtedly encountered DataSets. The DataSet tag, one of Macromedia's custom tags, interacts with a database via SQL statements like SELECT, INSERT, UPDATE, DELETE. You can also use it to execute stored procedures. The DataSet is to ASP.NET what the recordset is to ASP. Except that it's better, of course.

The most obvious application of the DataSet tag is to select data from a table in your database. The resulting dataset can be displayed in a variety of ways: in a DataGrid, DropdownList, or pretty much wherever you want in your page.

Another handy, though less obvious, function of the DataSet tag is to change data in a table (e.g., insert, update, or delete). In this case, the DataSet tag doesn't need to create an actual dataset, just perform some function.

In this article you'll learn about the attributes and methods available with the DataSet tag. I assume you've already set up an ASP.NET site within Dreamweaver - using either the Visual Basic or C# ASP.NET server model. (If you're not sure how this is done, select "Getting Started and Tutorials" from the Help menu. )

What's in a Name?
The DataSet tag written by Macromedia shouldn't be confused with the DataSet class included in the .NET Framework. Sure, they share the same name, but the two are quite different. Macromedia's DataSet is a custom tag. The Microsoft DataSet is a class in the .NET Framework. Macromedia's DataSet tag actually uses the .NET DataSet class within its implementation. In this article, the term DataSet can refer either to the DataSet tag or to the set of data created by the DataSet tag (I'll try to make it crystal clear which one I mean). But DataSet will not refer to the .NET DataSet class unless it is explicitly qualified by being preceded with ".NET."

DataSet Attributes
The DataSet tag has several attributes that return commonly needed information like the number of records, the current page, etc. Even though the DataSet has a slew of attributes, only three are required: CommandText, ConnectionString, and runat. (Table 1 lists all of the DataSet attributes.)

Required attributes
CommandText
The CommandText attribute can be either a SQL statement or the name of a stored procedure. If you want to use a stored procedure, you also have to use the IsStoredProcedure attribute. Set the Boolean IsStoredProcedure attribute to true to indicate that the string in the CommandText attribute is the name of a stored procedure. It turns out that the default value of IsStoredProcedure is false, so if you just leave it off the CommandText is automatically interpreted as a SQL statement.

Here's an example of using a SQL statement for the CommandText:

CommandText=
"SELECT LastName, FirstName FROM
dbo.Employees ORDER BY LastName"

Tip: The CommandText can also contain a binding expression. For example:

CommandText='
<%# (bUseNickname) ?
"SELECT RealName, Nickname
FROM dbo.Employees ORDER BY Nickname" :
"SELECT RealName, Nickname
FROM dbo.Employees ORDER BY RealName" %>'

This ternary expression stipulates that:

• If bUseNickname is true, the SELECT statement will be ordered according to the Nickname field.
• If bUseNickname is false, the SELECT statement will be ordered according to the RealName field.

FYI: You must put quotes around the attribute's value. That is, around the entire binding expression.

You cannot break up the binding expression over multiple lines the way it's shown here. The entire binding expression has to be on one line. (You'll get a nasty runtime error otherwise.)

Parameterized SQL Statements
Certain portions of the SQL statement used in the CommandText can be parameterized. Parameterization can simplify the SQL statement, making it easier to read.

Only the value in a name/value pair within the SQL statement is legal for parameterization. Table II lists some of the name/value pairs that are candidates for parameterization.

The WHERE clause is a prime example of where a name/value pair is used. Keep in mind that the name and value need not be adjacent to each other (see the INSERT example). The ORDER BY clause cannot be parameterized since it doesn't follow the name/value paradigm.

The syntax for the parameter depends on which type of connection you are using: OLE DB or SQL Server.

Consider the following example of a SELECT statement:

SELECT *
FROM myTable
WHERE col1="one" AND col2="two"
ORDER BY col2

If you replace the values in the name/value pairs in the WHERE clause with parameters, the statement becomes:

SQL Server connection
SELECT *
FROM myTable
WHERE col1=@param1 AND col2=@param2
ORDER BY col2

• Parameters must be uniquely named and start with the @ symbol.
• SQL Server connection parameters can be in any order.

OLE DB connection

SELECT *
FROM myTable
WHERE col1=? and col2=?
ORDER BY col2

• All parameters use the ? symbol.
• With OLE DB connections, the order of the parameters is important. The placeholders used in the name/value pairs must correspond exactly to the order of the <Parameter> tags used to define the placeholders.

The parameters are defined using the <Parameter> tag. Each DataSet can have multiple <Parameter> tags defined and all must be enclosed within the <Parameters> tag.

Continuing with the example given above, here are the required <Parameter> tags to define the parameters (depending on the connection type used).

SQL Server connection

<Parameters>
<Parameter Name="@param1" Value='one'
Type="String" />
<Parameter Name="@param2" Value='two'
Type="String" />
</Parameters>

OLE DB connection

<Parameters>
<Parameter Value='one' Type="String" />
<Parameter Value='two' Type="String" />
</Parameters>

• Notice that the <Parameter> tags for the OLE DB connection appear in the same order as the parameters are used in the SELECT statement. This is very important since all the parameters share the same name (the question mark, ?).
• The data type stipulated for the parameters must match the data type of the parameter. For example, in the <Parameter> tags above, the type is "String" but if the Value of @param1 was the integer 1, then the data type would be Integer (OLE DB) or Int (SQL Server).

Table III lists the legal data types for OLE DB and SQL Server connections.

ConnectionString
The required ConnectionString parameter contains instructions for connecting to the database. You could hand-code this information, but the easier way to handle it is to create a connection. (Unless otherwise specified, the examples in this article use a SQL Server connection.)

In the DataSet dialog all you have to do is select one of the predefined connections from the Connection dropdown menu and voilá, the ConnectionString attribute is written for you (see Image I).

Here's an example of a ConnectionString:

ConnectionString=
'<%#
System.Configuration.
ConfigurationSettings.
AppSettings["MM_CONNECTION_STRING_
Northwind"] %>'

(Keep in mind that you can't put a carriage return in the middle of a binding expression. Refer to the tip on page 12 for an explanation.)

runat="server"

The DataSet tag is a server-side control; it is processed on the server before being sent to the client. The default value for the runat attribute is "client" so you must explicitly set it to "server." Dreamweaver does this for you when it creates a DataSet tag.

Optional Attributes
The DataSet's optional attributes are grouped according to their function:

• Attributes that further refine the CommandText attribute
• Post-processing attributes
• Paging attributes
• Culling attributes
• Record-counting attributes
• Computed or created attributes

Attributes That Further Refine the CommandText Attribute
CreateDataSet
This optional Boolean attribute determines whether or not a dataset is created. That might seem odd since you'd think that the DataSet tag's whole purpose is to create sets of data. But remember that you can also use the DataSet tag to insert, update, delete, etc. Turns out that nothing bad will happen when you leave the CreateDataSet attribute at its default value of true when no records are returned. But it's good practice to set it to false in these cases.

The DataSet dialog does not have a checkbox for you to specify this value. If you want to set it to false, you have to do it directly in code view.

CreateDataSet="false"

Tip: Notice that the value for this Boolean attribute is in double quotes. In order to be XHTML compliant, the values in all name/value pairs must be quoted (even if the value is a Boolean).

Expression
The Expression attribute is a particularly powerful one. It's also a Boolean whose default value is true. The value of the Expression attribute determines if the CommandText is executed.

If a DataSet tag is sitting on your page and its Expression attribute is either absent or set to true, it will execute when the page loads. But what if the purpose of the DataSet tag is to update the database based on values the user is supposed to enter on the page? Instead of running each time the page loads, what you really want is for the DataSet to execute after the page has been submitted (e.g., posted back). You can accomplish that goal with the following:

Expression='<%# IsPostBack %>'

(Of course, you'll probably want to do some error checking to make sure all the required information is available first.)

The DataSet dialog does not have a checkbox for you to specify the value of the Expression attribute. You'll have to type it directly into the code if you want its value to be anything other than the default value of true.

IsStoredProcedure
And yet another Boolean attribute. The IsStoredProcedure attribute (default is false) is used in conjunction with the CommandText attribute. If your CommandText is the name of a stored procedure, then this attribute must be true.

IsStoredProcedure="true"

I bet you can't find a place on the DataSet dialog to set the IsStored- Procedure value. That's because Dreamweaver has an entirely separate Server Behavior for DataSets that execute stored procedures (see Image II).

It's still a DataSet, but Dreamweaver has simplified matters by providing a specific Server Behavior just for DataSets that execute stored procedures (see Image III).

Notice that it looks very similar to the DataSet dialog shown in Image I, but it prompts for the name of the stored procedure and its parameters rather than asking you to select a table and columns. In addition, the Stored Procedure dialog has a checkbox labeled Returns DataSet. The default is unchecked, which means that a DataSet is not created (in other words, the CreateDataSet attribute is false).

Dreamweaver's user interface separates the DataSet and Stored Procedure server behaviors into two separate commands for convenience and clarity in the dialogs. But keep in mind that they share the underlying code that supports the DataSet tag. Read through the DreamweaverCtrls.cs file to convince yourself.

If you installed Dreamweaver MX 2004 in the default location, you will find the Dreamweaver Ctrl.cs file in C:\Program\Files\Macromdia\DreamweaverMX 2004\Configuration\ServerBehaviors\Shared\ASP.net\Sripts\Source.

TableName
The TableName attribute is the name of the table that data is drawn from (default value is theTable). When you want information about the dataset most often you'll refer to the dataset's name rather than the table name. But the attribute is here for you in case you want to get into the guts of the custom DataSet tag. (Not surprisingly, the DataSet dialog doesn't have a spot on it for you to indicate the TableName. If you want to stipulate the name of the table you'll have to make this change directly in the code.)

Post-Processing Attributes
Debug
The Debug attribute should be used during development only. The default value is false. When you set Debug to true and an exception is thrown you'll get some fairly intelligible comments from Dreamweaver to assist you in debugging the problem. You'll want to set this attribute to false (or just delete it altogether) before deploying the final version of the page so your site visitors don't get what looks like gobbledygook to them. The DataSet dialog has a checkbox for this attribute.

Also keep in mind that if you set Debug to true, the FailureURL attribute is ignored. When an exception is thrown, you'll get the debugging information instead of being redirected to the page specified by FailureURL.

Code 1 is an example of a DataSet tag with a problem with the ConnectionString attribute (it doesn't exist). Image IV shows the debugging information you'll see when you run the page.

Obviously Dreamweaver hasn't pinpointed the exact problem, but it does give you a pretty broad hint that you should look at the ConnectionString attribute.

FailureURL
The FailureURL attribute specifies the page that will display when an exception is thrown from the DataSet tag. If you have Debug="true", the FailureURL attribute is ignored. Once you have the bugs worked out, set Debug to false and create a user-friendly FailureURL page.

SuccessURL
This attribute indicates the page to display when the DataSet's Command- Text has executed successfully. For example, suppose that the purpose of your DataSet tag is to update data in the database. Once it has done that successfully the SuccessURL page will appear saying that the update was successful. If you don't set this attribute, the page simply redisplays again.

Paging Attributes
CurrentPage and PageSize
When you have CreateDataSet="true" the DataSet tag returns all the records corresponding to the SQL statement specified in the CommandText attribute. If you're working with large amounts of data the size of the resulting dataset can easily bog down the performance of your page. Since you're probably not going to display a zillion rows on the page anyway, why get all the records?

The CurrentPage and PageSize attributes are meant to be used together. They tell the DataSet tag how many rows to get (the PageSize) and from where in the table to get them (CurrentPage). Note that the PageSize is zero-based so the first page corresponds to CurrentPage="0".

Consider the following example: let's say that your CommandText's SQL statement returns 63 rows. If you specify a PageSize of 10, that means you will have seven pages. The first six pages will have 10 rows each and the seventh page will have the remaining 3 rows. If you want to see the page with the 22nd row, then the CurrentPage would be 2.

Tip: If you're creating a dataset to be used as the data source for a DataGrid, be sure to use Dreamweaver's user interface to create the DataGrid. They've built in the correspondence between the DataSet's PageSize attribute and the DataGrid's PageSize attribute.

Culling Attributes
StartRecord and MaxRecords
Another way to indicate that you want just a subset of records rather than all the records corresponding to the SQL statement specified in the CommandText attribute is to use the StartRecord and MaxRecords attributes.

StartRecord is zero-based, so if you want the 10th through 25th record, then StartRecord="9" and MaxRecords="16". That is, StartRecord indicates the first record to get and MaxRecords indicates the total number of records to get.

Tip: Regarding the usage of the PageSize/CurrentPage and StartRecord/MaxRecords attributes:

• Use either PageSize and CurrentPage or StartRecord and MaxRecords, not both pairs of attributes. If both sets of attributes are specified, PageSize and CurrentPage are used and StartRecord and MaxRecords are ignored.
• If PageSize is greater than zero StartRecord is calculated automatically as (PageSize * CurrentPage).

Record-Counting Attributes
GetRecordCount
This Boolean attribute defaults to true, meaning that the number of records in the DataSet will be calculated. If you don't need this value for your application, set the GetRecordCount to false. In fact, under certain circumstances you can improve performance with GetRecordCount=false.

RecordCountCommandText
The RecordCountCommandText attribute allows you to specify an explicit SQL statement to compute the record count. Typically, the statement is something like this:

SELECT COUNT(*)
FROM myTable
WHERE ...

Using the RecordCountCommand Text attribute is particularly important (performance-wise) when you've set PageSize to be greater than zero.

It turns out that under the hood Dreamweaver uses the DbDataAdapter.Fill method to fill the .NET DataSet object with rows from the data source. The Fill method returns the number of rows added to the .NET DataSet (e.g., the record count). If PageSize is greater than zero, the value returned from DbDataAdapter.Fill is exactly equal to the PageSize. When you're looking for the record count, that's not the number you want. That means that the MM:DataSet code has to do some extra work to compute the actual record count.

This is how Dreamweaver handles record counting when PageSize is greater than zero:

1. If defined, the SQL statement in the RecordCountCommandText attribute is used to compute the record count.
2. Lacking the RecordCountCommandText attribute, Dreamweaver next attempts to parse the string in the CommandText attribute of the DataSet tag. This method works only if the CommandText uses a SELECT statement and doesn't contain a UNION operator.
3. Last and slowest, Dreamweaver will actually loop over the records in the .NET DataSet and count them.

So you can see why specifying your own RecordCountCommandText would be useful.

One last note on the RecordCountCommandText: you can't parameterize it.

Computed or Created Attributes
RecordCount
In order to get the record count, both the CreateDataSet (otherwise you don't have a dataset to count!) and GetRecordCount attributes must be true.

The simplest calculation for the record count occurs when both PageSize and MaxRecords are zero. The RecordCount corresponds to the number of records retrieved by the CommandText.

If MaxRecords is greater than zero, the value returned in the RecordCount attribute takes the MaxRecords and StartRecord values into account. RecordCount is equal to MaxRecords so long as MaxRecords is not greater than the number of records you actually have.

If PageSize is greater than zero, the RecordCount calculation totally ignores it. See the RecordCountCommandText section above, which describes how the record count is computed when the PageSize attribute is greater than zero.

Tip: For more information on the record count (including performance aspects), search the DreamweaverCtrls.cs file for the CalculateRecordCount method and the GetRecordCount attribute.

EndRecord
This calculated value is either [(CurrentPage + 1) * PageSize] or the RecordCount (whichever is smaller). You could easily compute this value yourself when you need it, but Macromedia decided to supply it for you.

LastPage
The LastPage attribute is calculated only when the PageSize is greater than zero and GetRecordCount is true. It's zero-based and computed from the RecordCount and PageSize.

DefaultView
The data stored in the DataSet is just that: data. In order to use the data as a data source for another object (i.e., an asp:DataGrid or asp:DropdownList), you need to specify the DataSet's view. For example, to use an MM:DataSet in a DataGrid, the DataSource attribute would be:

<asp:datagrid
...
DataSource="<%# myDataSet.DefaultView %>
...
/>

The DefaultView represents all of the data retrieved by the DataSet tag and in the same order that it was retrieved. If you wanted a subset of the data and/or the data ordered differently, you would need to create a new view to associate with the DataSet or change the CommandText of the DataSet so that it gets the subset you want and/or orders it the way you want it ordered.

Tip: The MM:DataSet tag retrieves records from a single table in the database; but that's a limitation of the MM:DataSet tag, not of the .NET DataSet object. If you look at the documentation for the .NET DataSet object, you'll see that it can contain data from one or more tables. The theDS attribute of the MM:DataSet tag is equal to the DefaultView of the table 0 in the .NET dataset.

myDataSet.DefaultView <==> theDS.Tables[0].DefaultView

where:

• myDataSet is the ID of your MM:DataSet tag
• myDataSet.DefaultView is the default view of the data retrieved by the MM:DataSet tag
• theDS is the .NET dataset object returned by the MM:DatatSet tag
• theDS.Tables[0] indicates the first (and only) table within the .NET dataset object
• theDS.Tables[0].DefaultView is the default view of the first table of the .NET dataset object

Before you can define a new view, you need access to the .NET dataset. Fortunately for us, we have it with the theDS attribute.

theDS
Near the beginning of this article, I briefly mentioned that the Macromedia DataSet tag uses the .NET DataSet class. The theDS attribute is that .NET DataSet object. It holds the records obtained when the MM:DataSet CreateDataSet attribute is true. What's it good for, you ask? Well, truth is you probably won't need it on a regular basis, but theDS attribute does provide a powerful "hook" into the underlying .NET DataSet object.

Consider the following example: you have a dropdown list populated with the contents of a DataSet, but you want the first item (e.g., option tag) of the dropdown list to be "Please select one" and the option's value to be 0 (see Image V).

<select name="sizeDropdown" id="sizeDropdown">
<option value="0">Please select one</option>
<option value="1">Extra small</option>
<option value="2">Small</option>
<option value="3">Medium</option>
<option value="4">Large</option>
</select>

You can programmatically add that first option tag by utilizing the theDS attribute to add a new row to the .NET dataset.

DataSet Methods
The DataSet tag has two methods to help you access particular pieces of data within a DataSet. The methods are FieldValue and FieldValueAtIndex (see Code II).

Both of the DataSet methods return a string corresponding to the value of a named field (FieldName) within some row in the DataSet.

The FieldValue method returns the value of the named field from the first row (row 0). FieldValueAtIndex takes an additional parameter that indicates the specific row number. Note that rows are zero-indexed.

Since FieldValue acts on row 0, the following two function calls are equivalent:

DS_uidAndPwd.FieldValue("emailaddr", null)
DS_uidAndPwd.FieldValueAtIndex(0, "emailaddr", null)

To Use a Container or Not
The last argument for both DataSet methods is the name of the container. You use either the word Container or null. If the call to FieldValue or FieldValueAtIndex is within a DataGrid, DataList, or Repeater tag, then use Container. Otherwise, use null. For example, if you're calling FieldValue from the Page_Load function, you don't have a container so you should use null as the last argument.

Here's a quick example: you have a page with a DataGrid. The data source for the DataGrid is a DataSet that contains a list of e-mail addresses. Each row of DataGrid corresponds to a row in the DataSet so you have one e-mail address on each row.

The first thing you need to do in Dreamweaver MX is change the column data type from Simple Data Field to Free Form. This allows you to use an ItemTemplate. This is where you use the FieldValue("FieldName", Container) form of the method:

<asp:TemplateColumn HeaderText="emailaddr" Visible="True">
<ItemTemplate>
<%# DataSet1.FieldValue("emailad dr", Container) %>
</ItemTemplate>
</asp:TemplateColumn>

Using Container in the call to FieldValue ensures that the appropriate DataSet row is used.

Tip: Curious about the meaning of Container? Check out the .NET documentation for the Eval method of the DataBinder class. The Container used in FieldValue and FieldValueAtIndex is identical to the Container used when you call DataBinder.Eval.

On the other hand, you should use null when you want to use a value from the dataset somewhere outside of a DataGrid, DataList, or Repeater. The following example prints out a single e-mail address from the 5th row of the dataset.

<%# DataSet1.FieldValueAtIndex(4, "emailaddr", null) %>

Conclusion
Well, there you have it. All the background information you need to start using the MM:DataSet tag. In its simplest form, the DataSet tag is quite easy to use. In its more complicated forms, the DataSet tag is very flexible. Everything that the DataSet tag does could be done without using Macromedia's custom tag but why bother when they've already done the legwork for you?