Figures
© 2004 Microsoft Corporation. All rights reserved.
Figure 3 Project Configuration
<?xml version="1.0" encoding="UTF-8"?>
<project name="First Project" icon="project1.gif">
  <select name="Status">
      <option name="Active" />
      <option name="Fixed" />
      <option name="Closed" />
    </select>
    <select name="Browser>
    <option name="All" />
    <option name="IE6.0" />
      <option name="IE5.5" />
      <option name="IE5.0" />
      <option name="NS6.0" />
      <option name="NS4.7" />
    </select>
    <select name="Platform">
    <option name="All" />
      <option name="Windows" />
      <option name="Mac" />
    </select>
    <textbox name="Version" />
</project>
Figure 5 Database Schema

Figure 5 Database Schema Figure 6 The SetCommand Method
// set up the stored procedure in the SqlCommand
public void SetCommand (string sql, SqlDataAdapter dataAdapter, 
   SqlCommandType sqlCMDType)
{
this.m_sqlCMD.CommandText = sql;
    this.m_sqlCMD.CommandType = CommandType.StoredProcedure;

    switch (sqlCMDType)
    {
        case SqlCommandType.Select:
            dataAdapter.SelectCommand = this.m_sqlCMD;
            break;

        case SqlCommandType.Insert:
            dataAdapter.InsertCommand = this.m_sqlCMD;
            break;

        default:
            break;
    }

}
Figure 7 Creating a SelectCommand Using SqlStoredProc
// create connection
SqlConnection listConn = new SqlConnection("server=(local);database=incident;
                                           Trusted_Connection=yes");

// create data adapter
SqlDataAdapter listDA = new SqlDataAdapter();

// create stored procedure helper object
SqlStoredProc listSqlProc = new SqlStoredProc (listConn);

// set up the stored procedure and the parameters
listSqlProc.SetCommand ("GetIncidentList", listDA,   
   SqlCommandType.Select);
listSqlProc.AddParam ("@ProjectId", SqlDbType.Int, 4, projid);
Figure 8 GetAuditTrail
CREATE PROCEDURE GetAuditTrail
     @ProjectId INT,
    @IncidentId INT
AS

SELECT    project.projectid, project.projectname, project.icon 
FROM      project 
WHERE     projectid = @ProjectId

SELECT    incident.incidentid, incident.projectid, incident.openedby, 
          incident.openeddate 
FROM      incident
JOIN      project ON incident.projectid = project.projectid
WHERE     incident.incidentid = @IncidentId

•••

SELECT    options.optionname, options.fieldid 
FROM      options
JOIN      project_fields ON options.fieldid = project_fields.fieldid 
WHERE     project_fields.projectid = @ProjectId
Figure 9 XML Data Generated by DataSet Object
<NewDataSet>
  <Project>
    <projectid>1</projectid>
      <projectname>First Project</projectname>
      <icon>project1.gif</icon>
      <Incident>
        <incidentid>1</incidentid>
        <projectid>1</projectid>
        <openedby>Ian</openedby>
        <openeddate>2001-11-14T00:19:41.5270000-00:00</openeddate>
      <Audit>
        <auditid>1</auditid>
        <incidentid>1</incidentid>
        <caption>No ad banner on login page</caption>
        <description>The ad banner is missing on the page   logon01.htm</
         description>
        <submitteddate>2001-08-14T15:02:16.0000000+01:00</submitteddate>
        <submittedby>Ian</submittedby>
        <Extra>
          <fieldname>Status</fieldname>
          <fieldvalue>Active</fieldvalue>
          <auditid>1</auditid>
        </Extra>
        •••
      </Audit>
    </Incident>
    <OptionDef>
      <projectid>1</projectid>
      <fieldname>Status</fieldname>
      <fieldtype>select</fieldtype>
      <fieldid>1</fieldid>
      •••
    </OptionDef>
•••
  </Project>
</NewDataSet>
Figure 10 Web Forms Layout

Figure 10 MTS Figure 11 Display Audit History List
<xsl:for-each select="//Incident/Audit">
<tr><td>
<xsl:value-of select="substring(submitteddate,1,19)"/></td>
<td>
<xsl:value-of select="submittedby"/>
</td>
<td>
<xsl:value-of select="description"/>
</td>
</tr>
</xsl:for-each>
Figure 12 Display Controls on Bug Form
<xsl:template match="OptionDef">
    <xsl:if test="fieldtype ='select'">
        <table>
          <tr><td><xsl:value-of select="fieldname"/></td></tr>
          <tr><td>
          <select name="{fieldname}" width="160" style="width:160">
•••
            </td></tr>
        </table>
    </xsl:if>

    <xsl:if test="fieldtype ='textbox'">
        <table>
        <tr><td><xsl:value-of select="fieldname"/></td></tr>
•••
        </table>
    </xsl:if>
</xsl:template>    
Figure 13 Adding a New Row
// insert new audit rows

// create data adapter
SqlDataAdapter auditDA = new SqlDataAdapter();

// create stored procedure helper object for SelectCommand
SqlStoredProc auditSelectSqlProc = new SqlStoredProc (updateConn);

// set up the stored procedure and the parameters for the SelectCommand
auditSelectSqlProc.SetCommand ("GetAudit", auditDA, 
  SqlCommandType.Select);
auditSelectSqlProc.AddParam ("@IncidentId", SqlDbType.Int, 4, -1);

// create stored procedure helper object for InsertCommand
SqlStoredProc auditInsertSqlProc = new SqlStoredProc (updateConn);

// set up the stored procedure and the parameters for the InsertCommand
auditInsertSqlProc.SetCommand ("InsertAudit", updateDA, 
  SqlCommandType.Insert);

auditInsertSqlProc.AddParam ("@incidentid", SqlDbType.Int, 4, 
  ParameterDirection.Input, "incidentid");
auditInsertSqlProc.AddParam ("@caption", SqlDbType.VarChar, 200, 
  ParameterDirection.Input, "caption");
auditInsertSqlProc.AddParam ("@description", SqlDbType.VarChar, 8000, 
  ParameterDirection.Input, "description");
auditInsertSqlProc.AddParam ("@submitteddate", SqlDbType.DateTime, 8, 
  ParameterDirection.Input, "submitteddate");
auditInsertSqlProc.AddParam ("@submittedby", SqlDbType.VarChar, 50, 
  ParameterDirection.Input, "incidentsubmittedby");
auditInsertSqlProc.AddParam ("@auditid", SqlDbType.Int, 4, 
  ParameterDirection.Output, "auditid");

// map the table name and populate the data set
auditDA.TableMappings.Add ("Table", "Audit");
auditDA.Fill(ds);

// add a new data row and fill the columns with the form values
DataRow newAuditRow = ds.Tables["audit"].NewRow();

newAuditRow ["incidentid"] = idIncident; //Request.Form["incid"];
newAuditRow ["caption"] = Request.Form["caption"];
newAuditRow ["description"] = Request.Form["description"];
newAuditRow ["submitteddate"] = DateTime.Now;
newAuditRow ["submittedby"] = "fred";
ds.Tables["audit"].Rows.Add (newAuditRow);

// Insert the new row into the database
myDataAdapter.Update (ds, "audit");
Figure 15 Add Project-specific Field Values to DataSet
DataRow newExtraRow;

foreach (String s in Request.Form)
{
    // if the field is not a mandatory field, create new Extra row
if (s != "projid" && s != "incid" && s != "Caption" && s != "Description" 
    && s != "SubmittedBy")
    {
        newExtraRow = ds.Tables["extra"].NewRow();
        newExtraRow["fieldname"] = s;
        newExtraRow["fieldvalue"] = Request.Form[s];
        newExtraRow["auditid"] = newAuditRow["auditid"];
        ds.Tables["extra"].Rows.Add (newExtraRow);
    }
}
Figure 16 Stored Procedure for Generating the Bug List
CREATE PROCEDURE GetIncidentList
     @ProjectId INT
AS

SELECT    projectid, projectname, icon
FROM      Project 
WHERE     projectid = @ProjectId

SELECT    incident.incidentid, incident.projectid, incident.openedby, 
          incident.openeddate 
FROM      incident
JOIN      project ON incident.projectid = project.projectid
WHERE     incident.projectid = @ProjectId

SELECT    audit.auditid, audit.incidentid, audit.submitteddate, 
          audit.caption, audit.description 
FROM      audit
JOIN      incident ON audit.incidentid=incident.incidentid
WHERE     incident.projectid= @ProjectId
GO
Figure 17 Excerpt from list.xsl
<xsl:choose>
    <xsl:when test="$sort = 'caption'">
        <xsl:apply-templates select="//Incident">
            <xsl:sort select="Audit[last()]/caption" />
        </xsl:apply-templates>
    </xsl:when>
    <xsl:when test="$sort = 'openedby'">
        <xsl:apply-templates select="//Incident">
            <xsl:sort select="openedby" />
        </xsl:apply-templates>
    </xsl:when>
    <xsl:when test="$sort = 'submitteddate'">
        <xsl:apply-templates select="//Incident">

            <xsl:sort select="Audit[last()]/submitteddate" />
        </xsl:apply-templates>
    </xsl:when>
    <xsl:otherwise>
        <xsl:apply-templates select="//Incident">
            <xsl:sort select="Audit[last()]/incidentid" data-
             type='number'/>
        </xsl:apply-templates>
    </xsl:otherwise>
</xsl:choose>
Figure 18 Project Config to DataSet
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  version="1.0">
<xsl:output method="xml" indent="yes" encoding="ISO-8859-1" />

<xsl:template match="/">
  <Project>
  <xsl:apply-templates />
  </Project>
</xsl:template>

<xsl:template match="project">
  <projectname><xsl:value-of select="@name"/></projectname>
  <icon><xsl:value-of select="@icon"/></icon>
  <xsl:apply-templates />
</xsl:template>

<xsl:template match="select">
  <OptionDef>
    <fieldname><xsl:value-of select="@name"/></fieldname>
    <fieldtype>select</fieldtype>
    <xsl:for-each select="option">
      <Option>
      <optionname><xsl:value-of select="@name"/></optionname>
      </Option>
    </xsl:for-each>
  </OptionDef>
</xsl:template>

<xsl:template match="textbox">
  <OptionDef>
    <fieldname><xsl:value-of select="@name"/></fieldname>
    <fieldtype>textbox</fieldtype>

    <xsl:for-each select="option">
      <Option>
      <optionname><xsl:value-of select="@name"/></optionname>
      </Option>
    </xsl:for-each>
  </OptionDef>
</xsl:template>

</xsl:stylesheet>
Page view tracker