Friday, April 9, 2010

Using SQL to Generate XML

To create this XML block:

<EventArguments>
<OccurrenceId>300031738</OccurrenceId>
<HoursInProcess>123</HoursInProcess>
<ContactName>McCusker &amp; Ogborne &lt;Other&gt;</ContactName>
<AccidentFormId>795</AccidentFormId>
<BusinessUnitNumber>*</BusinessUnitNumber>
</EventArguments>

You would do this:

SELECT '300031738' AS OccurrenceId,
123 AS HoursInProcess,
'McCusker & Ogborne <Other>' AS ContactName,
795 AS AccidentFormId,
'*' AS BusinessUnitNumber,
FOR XML RAW('EventArguments'), ELEMENTS

-

For more sophisticated XML, you can do sub queries. This may not make sense to include this particular table, but it shows how it would work.

SELECT '300031738' AS OccurrenceId,
123 AS HoursInProcess,
'McCusker & Ogborne <Other>' AS ContactName,
795 AS AccidentFormId,
'*' AS BusinessUnitNumber,
(SELECT TOP 5 ProcessedMessageLogId FROM ProcessedMessageLog FOR XML RAW(''), ELEMENTS, TYPE) AS Children
FOR XML RAW('EventArguments'), ELEMENTS

- Which creates the following XML:

<EventArguments>
<OccurrenceId>300031738</OccurrenceId>
<HoursInProcess>123</HoursInProcess>
<ContactName>McCusker &amp; Ogborne &lt;Other&gt;</ContactName>
<AccidentFormId>795</AccidentFormId>
<BusinessUnitNumber>*</BusinessUnitNumber>
<Children>
<ProcessedMessageLogId>3281</ProcessedMessageLogId>
<ProcessedMessageLogId>3282</ProcessedMessageLogId>
<ProcessedMessageLogId>3283</ProcessedMessageLogId>
<ProcessedMessageLogId>3284</ProcessedMessageLogId>
<ProcessedMessageLogId>3285</ProcessedMessageLogId>
</Children>
</EventArguments>

Thursday, April 8, 2010

Confirming Delete in a GridView

I tried a bunch of ways but this was the only way to get this to work reliably. The problem is that the ButtonFields in the GridView don’t have an OnClientClick event the way that every other button does. To remedy this, you have to attach the ClientClick handler when the grid is being generated.

So in your Columns collection of the GridView, you will have a button field like this:

<asp:ButtonField ButtonType="Link" CommandName="Clear" Text="Clear" />

Then in the code behind you need to do this:

protected void grdCourses_RowDataBound(object sender , GridViewRowEventArgs e)
{
if (e.Row.RowType != DataControlRowType.DataRow) return;

int lastCellIndex = e.Row.Cells.Count - 1;
LinkButton clearButton = (LinkButton)e.Row.Cells[lastCellIndex].Controls[0];
clearButton.OnClientClick =
"if (!window.confirm('Are you sure you want to clear this score?')) return false;";
}