SQL Server 2008 – Table Valued Parameters

Here is a great article that explains how to use TVP’s via .Net and a stored procedure

XSL/T Transform from String to String

The following example transforms the supplied xml string, using XSL/T and outputs to another string

using (StringReader rdr = new StringReader(selectedLine.AdditionalInformation))
{
    XPathDocument doc = new XPathDocument(rdr);

    using (StringWriter writer = new StringWriter())
    {

        transformer.Transform(doc, null, writer);
        titleLabel.Attributes.Add("onmouseover", string.Format(@"showDiv(""{0}"")", writer.ToString()));
    }
}


The XSL/T loaded into the transformer variable is as follows




    

        
<table class='panelTable'></table>
        
        
<tr><td colspan='2' class='TitlePanelHeader'></td></tr>
        
        
<tr><td></td><td></td></tr>
        


And a sample xml string is as follows:



	
	
	
	
	
	
	
	
	
	
	


The result from the transformation is as follows:

Body Rides
Additional answer code
Additional answer date
Answer code
TOS
Answer date
AuthorRichard Laymon
Delivery toCompany Name, Address Line 1, AddressLine 2
Expected delivery09/08/2009
Held orders
Ordered on20/07/2009
Print runs


The code to build up the xml within the object is below, note that the values are HtmlEncoded to ensure that there are no problems in the web page.

public string AdditionalInformation
{
    get
    {
        StringBuilder builder = new StringBuilder();
        builder.Append(string.Format(CultureInfo.CurrentCulture, 
                                        @"", 
                                        HttpUtility.HtmlEncode(this.Title)));
        AddLabelValue(builder, "Account number", ParentPurchaseOrder.AccountNumber);
        AddLabelValue(builder, "Additional answer code", this.AdditionalAnswerCode);
        AddLabelValue(builder, "Additional answer date", 
            this.AdditionalAnswerDate.Year != 1 ? this.AdditionalAnswerDate.ToShortDateString() : "" );
        AddLabelValue(builder, "Answer code", this.AnswerCode);
        AddLabelValue(builder, "Answer date",
            this.AnswerDate.Year != 1 ? this.AnswerDate.ToShortDateString() : "");
        AddLabelValue(builder,"Author", this.Author);
        AddLabelValue(builder, "Delivery to",
            ParentPurchaseOrder.DeliveryName + ", " + ParentPurchaseOrder.Address1 + ", " +
                          ParentPurchaseOrder.Address2 + ", " +
                          ParentPurchaseOrder.Address3 + ", " +
                          ParentPurchaseOrder.Address4 + ", " +
                          ParentPurchaseOrder.Address5 + ", " +
                          ParentPurchaseOrder.Postcode);

        AddLabelValue(builder, "Expected delivery",
ParentPurchaseOrder.ExpectedDeliveryDate.ToShortDateString());
        AddLabelValue(builder, "Held orders", this.HeldOrders);
        AddLabelValue(builder, "Ordered on", ParentPurchaseOrder.OrderedOn.ToShortDateString());
        AddLabelValue(builder, "Print runs", this.PrintRuns);
                builder.Append("");

        return builder.ToString();

    }
}

private void AddLabelValue(StringBuilder builder, string label, string value)
{
      builder.Append(string.Format(CultureInfo.CurrentCulture,
                        @"",
                         label, HttpUtility.HtmlEncode(value)));
}

Iterate a Dictionary

Following code iterates through each entry in a dictory

For Each keyPair As KeyValuePair(Of String, Int32) In aDepot.PostcodeCounters
    builder.Append("<tr><td>")
    builder.Append(keyPair.Key)
    builder.Append("</td><td align=""right"">")
    builder.Append(keyPair.Value)
    builder.Append("</td></tr>")
Next

Creating Event Handler Signatures

In Visual Studio 2008 you’ve lost the abillity to click on the drop down in the code behind and see all the event signatures that are available but haven’t handled yet. To get the signature you are after without using the properites in the desinger you can type…


this.

As you hit the . all methods and events should appear, pick the event you after e.g. Load

Then add type += after the event name and press TAB TAB, the event is automatically generated. ASP.Net auto wires the events with the correct name and signature so you should be able to delete the line of code that binds the event.

Check out this article for more info.

VB.Net – ControlChars

If you need special characters in your VB.Net use the ControlChars class, it has definitions for Lf, Crlf, etc.

See MSDN for more details

DateDiff – Subtract

In my VB.Net code I’ve always used the DateDiff function to determine the number of days between 2 dates. Today I was playing around working out which order to put dates in and discovered that the Date object as a method called Subtract. This returns a TimeSpan then its just a case of using the Days property

startDate.Subtract(now).Days

Note that if startDate is before todays date this will return a negative value

>? now.subtract(cdate("1 Jan 2009")).days
1
>? cdate("1 Jan 2009").subtract(now).days
-1
>? now.subtract(cdate("1 Jan 2008")).days
367

Forcing Data Types on literal constants

The following type characters can be used to force a literal value to be a certain data type


"a"c 'Char
#1/1/1900# 'Date
0D 'Decimal
0@ 'Decimal
0.0R 'Double
0.0# 'Double
0I 'Integer
0% 'Integer
0L 'Long
0& 'Long
0S 'Short
0.0F 'Single
0.0! 'Single

See MSDN for more details

TryCast – better than DirectCast?

In VB.Net Framework V2.0 a new command was introduced called TryCast It works the same as DirectCast but if the cast fails instead of throwing an exception the object is set to Nothing

Take a look at MSDN for more details.

PrintPreviewDialog – PrintDialog

Ok so this might be an obvious one but it got me stuck for a little while so thought I should post it. When you use the PrintPreviewDialog the documents Print method is automatically called when the dialog is shown. But using the PrintDialog the Print method of the associated PrintDocument is not called when the user clicks the Print button on the dialog, you have to check that OK was returned as the dialogs result!