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
.gif) 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
.gif) 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="https://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>
|