Navigation

RSS: Matt Pavey RSS Feed

Thursday, September 20, 2007 @ 12:57 pm,SQL Server,Matt Pavey

I ran into a situation earlier today where I wanted to use a table-value function, which essentially is just a function that returns a table. But in my case I wanted to take that table and have it joined to another table to produce the desired results.
 
I had never really had to do something like that before and I quickly found out that you can't do a standard JOIN or subquery on a table-value function if you are also trying to pass it a value derived from the table you are joining on.
 
For example, this query will work:
 
select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(@RecordID) m on d.RecordID = m.RecordID
 
However, this query will not work:
 
select  d.RecordID,
          d.StudyID,
          d.TrackingID
from    MyTable d
join     dbo.MyFunction(d.RecordID) m on d.RecordID = m.RecordID
 
I realize I could have accomplished what I wanted by eliminating the function all together and having a subquery to do this all, but the point wasn't to just get it working, it was to get it working and have this helper function (MyFunction) available to centralize some logic that is used in multiple places.
 
Luckily in SQL Server 2005 there is a CROSS APPLY clause that makes this trivial:
 
select            d.RecordID,
                    d.StudyID,
                    d.TrackingID
from             MyTable d
cross apply    dbo.MyFunction(d.RecordID) m
 
The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side if the table-valued-function returns rows.
 
In my particular example the query returned the data that I needed and I was able to keep my logic centralized.
 
These articles were helpful as I was testing and learning about the CROSS APPLY clause:
 
 
As with any SQL queries, you should test performance accordingly and determine if this particular solution is the best approach or if something else works better. I experimented with a couple other solutions; however, none of them performed any better and they didn't allow me to re-use them easily, which is why I decided the CROSS APPLY was worth using.

Sunday, September 16, 2007 @ 9:28 am,VB.Net,Matt Pavey

On several occassions I work with XML data and sometimes binding it to one of the standard ASP.Net controls doesn't quite give you the flexibility necessary to display the data how you need to, especially when dealing with hierarchical data more than a couple of levels deep.

You could always load the XML data and parse through it and build your controls manually; however, sometimes that is hard to do for very complex hierarchies.

In some cases a better solution is XSL.

XSL is a family of recommendations for defining XML document transformation and presentation.

XSL Transformations (XSLT)
A language for transforming XML

XML Path Language (XPath)
An expression language used by XSLT to access or refer to parts of an XML document. (XPath is also used by the XML Linking specification)

XSL Formatting Objects (XSL-FO)
An XML vocabulary for specifying formatting semantics

If you don't use XSL or know much about it and are wanting to learn I highly recommend Michael Kay's XSLT 2nd Edition Programmer's Reference by WROX Books. In my opinion Michael is one of the leading experts on the subject and you'll consistently see him posting on online forums about what XSL is and the proper way to use it, etc.

Over the course of several years I've used XSL in a number of ways such as re-transforming XML data from one format to another, advanced sorting capabilities, or displaying hierarchical data in a very specific format. The powerful capabilities of this language are too large in number to begin to outline or describe in this article; however, one thing that I would like to mention is how you can actually implement an XSL transformation in your code very easily, and in a generic fashion that can be utilized throughout your projects with minimal effort.
 
You'll find the Xsl class and Functions class that I use in several of my projects at the following links:
 
 
 
The purpose of this class is to centralize many of the things I have come across while using XSL and have them easily accessible and configurable in a reusable class.
 
This class will let you transform XML data in a file or XML data passed in directly as a string. It could easily be modified to accept the XML as an XMLDocument or other format if needed.
 
Another interesting thing I want to point out is that the TransformXml function is overloaded to accept either the path of the XSL file or a custom XSL configuration object (XslConfiguration).
 
Sometimes your code might need to be as simple as passing in XML data and an XSL path and getting back the results:
 
Using Xsl As New Utilities.Xsl()
     News.Text = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, Server.MapPath("~/Test.xsl"))
End Using
 
However sometimes you have more complicated requirements, thus the overloaded version of the TransformXml function can take an XslConfiguration object as a parameter. This allows you to define a number of other things besides the XSL path, including parameters that the XSL transformation needs and an ExtensionObject if you need to execute custom functions from within the transformation.
 
Here's a similar example using the XslConfiguration object and only specifying an XSL file, no advanced configuration settings:

Using Xsl As New Utilities.Xsl()
     Using XslConfig As New Utilities.Xsl.XslConfiguration(Server.MapPath("~/Test.xsl"))
          HtmlData = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, XslConfig)
     End Using
End Using

And here's another example using the XslConfiguration object with some of the more advanced settings:

Using Xsl As New Utilities.Xsl()
     Using XslConfig As New Utilities.Xsl.XslConfiguration()
          'xsl file path
          XslConfig.XslFile = XslFile

          'extension object with a function that will be accessible in our transformation
          XslConfig.ExtensionNameSpace = "urn:ext"
          XslConfig.ExtensionObject = New ExtensionObject

          'parameters required in the xsl transformation
          XslConfig.SetXslParameterNames("Parameter1Name", "Parameter2Name")
          XslConfig.SetXslParameterValues("Parameter1Value", "Parameter2Value")

          'transform the data
          HtmlData = Xsl.TransformXml(Utilities.Xsl.XmlTypes.XmlData, XmlData, XslConfig)

          'check for errors
          If Xsl.ErrorDescription <> String.Empty Then
               HtmlData = xsl.ErrorDescription
          End If
     End Using
End Using
 
Of course for the previous code to work you to define the ExtensionObject class or whatever you chose to name your class, for example:

Private Class ExtensionObject
     Public Function IsImageAvailable(ByVal Image As String) As String
          Return File.Exists(HttpContext.Current.Server.MapPath("~/Images/" & Image))
     End Function
End Class
 
The actual XSL transformation code could look something like:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ext="urn:ext" version="1.0">
     <xsl:output media-type="html" omit-xml-declaration="yes"/>

     <xsl:param name="Parameter1Name" />
     <xsl:param name="Parameter2Name" />

     <xsl:template match="/">
          Parameter1Name: <xsl:value-of select="$Parameter1Name"/><br/>
          Parameter2Name: <xsl:value-of select="$Parameter2Name"/><br/>

          <xsl:apply-templates select="//Row">
               <xsl:sort select="LastName" data-type="text" order="ascending" />
               <xsl:sort select="FirstName" data-type="text" order="ascending" />
          </xsl:apply-templates>
     </xsl:template>
 
     <xsl:template match="Row">
          <xsl:variable name="ShowImage" select="ext:IsImageAvailable(Image)"/>
         
          <xsl:choose>
               <xsl:when test="$ShowImage='True'">
                    <img src="Images/{Image}" />
               </xsl:when>
               <xsl:otherwise>
                    <img src="Images/ImageNotAvailable.jpg" />
               </xsl:otherwise>
          </xsl:choose>
     </xsl:template>
</xsl:stylesheet>
 
Notice how we have our xmlns:ext attribute declared in the xsl:stylesheet node. That is what ties our extension namespace back to what we specified in the code-behind:

XslConfig.ExtensionNameSpace = "urn:ext"

And we've defined our 2 parameters that we passed in.
 
It also demonstrates how we can call our custom function from our extension object, like so:
 
<xsl:variable name="ShowImage" select="ext:IsImageAvailable(Image)"/>
 
That's all there is to having a custom XSL class that can be reused for a lot of different scenarios!

Sunday, September 16, 2007 @ 8:02 am,VB.Net,Matt Pavey

Here's a simple class that I use regularly to configure and send e-mail messages using the System.Net.Mail assembly.
 
You'll find the Email class and Functions class that I use in several of my projects at the following links:
 
 

An example of using this code might look something like this:

Using xmail As New Utilities.Email()
     xmail.SMTPServer = Configuration.Email.SMTP_Server
     xmail.SMTPServerPort = Configuration.Email.SMTP_ServerPort
     xmail.SMTPConnectionTimeOut = Configuration.Email.SMTP_ConnectionTimeOut
     xmail.SMTPDeliveryMethod = Configuration.Email.SMTP_DeliveryMethod
     xmail.SMTPUseSSL = Configuration.Email.SMTP_UseSSL
     xmail.SMTPUserName = Configuration.Email.SMTP_UserName
     xmail.SMTPPassword = Configuration.Email.SMTP_Password

     xmail.AddSender(FromAddress, FromName)

     For Each EmailAddress As String In ToAddress.Split(";")
          If EmailAddress.Trim.ToString <> String.Empty Then
               xmail.AddRecipient(EmailAddress)
          End If
     Next

     xmail.Subject = Subject
     xmail.IsBodyHtml = True
     xmail.Body = EmailBody
     xmail.Send()
End Using

Because our Email class Implements IDisposable we are able to use the shorthand Using/End Using block.

Saturday, September 15, 2007 @ 6:47 pm,ASP.Net,Matt Pavey

Here's a great article on master pages.
 

Saturday, September 15, 2007 @ 6:27 pm,SQL Server,Matt Pavey

Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to execute a Transact-SQL statement that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Saturday, September 15, 2007 @ 12:19 pm,ASP.Net,Matt Pavey

Enter Key functionality is handled a little differently when it comes to ASP.NET. 
 
If there is a single button then it is straight forward because that button is the only button and thus the default button; however, if there are two or more buttons then it uses the first button as the default button.
 
ASP.NET 2.0 introduces a work around for this by simply specifying the defaultbutton property to the ID of the <asp:Button> whose event you want to fire.
 
The defaultbutton property can be specified in the <form> tag as well as in the <asp:panel> tag. The form level setting is overridden when specified at the panel level, for those controls that are inside the panel.
 
If for some reason handling this at the form level or panel level doesn't fit with how you have things setup, you can still use some simple JavaScript techniques to accomplish this.
 
Take this JavaScript function for example, which also utilizes another custom FindControl function.
 
function Submit(e, ButtonName)
{
     var KeyCode = (window.event) ? event.keyCode : e.which;
     var KeyChar = (window.event) ? String.fromCharCode(event.keyCode) : String.fromCharCode(e.which);

     if (KeyCode == 13)
     {
          var btnSubmit = FindControl(ButtonName, "input");
         
          if(btnSubmit != null)
          {
               btnSubmit.click();
          }
 
          if (window.event)
          {
               window.event.returnValue = false;
          }
          else
          {
               e.preventDefault();
          }
     }
}
 
Now for any control on your page you can specify which button you want "clicked" when the ENTER key is pressed while that control has focus like so:
 
<asp:TextBox ID="txtTest" onkeypress="Submit(event, 'btnSubmit')" runat="server" />
 
This has been tested in IE and Firefox.

Saturday, September 15, 2007 @ 11:16 am,HTML,Matt Pavey

There have been a few times where I've had to to some tricks to keep a UI from looking "awkward" based on dynamically generated data. For example, if you have a left panel navigation area with a DropDownList in it and the data for the drop down is dynamically generated from records in a database then you probably have encountered issues with the DropDownList automatically resizing itself to account for longer data.
 
One simple fix is to limit the number of characters a user is able to enter for those fields you display in a drop down list; however, that usually isn't practical.
 
Another solution is to simply hard code the width of the drop down list to a specific size. In this case if you have a value that is longer than the size of the drop down you basically just can't see the entire value. Depending on the client this may or may not be ok.
 
Yet another solution would be to find the maximum size that you want to display in the drop down list, and if any values exceed that number, remove those characters and replace with a "...". Again, this is a preference thing and the client may not like the way certain values end up looking.
 
I've got a few situations where using a DIV and some style properties allowed me to keep a DropDownList at a specific width, so it never exceeds the size of the left panel even if there are extremely long values entered; however, upon clicking the drop down list it extends appropriately to show the entire size of the drop down list.

Here's an example of what the code looks like for my particular example:
 
<div style="OVERFLOW: hidden; position:relative; overflow-x: hidden; overflow-y: fixed; fixed; visibility:visible; width:179px">
     <WebControls:DropDownListCustom ID="ddlQuestionCategories" runat="server" />
</div>
 
Enjoy!

Saturday, September 15, 2007 @ 10:46 am,C-Sharp,Matt Pavey

We've all ran into the issue with using a TextArea control or a TextBox control with MultiLine set to True to allow the user to enter a long description or comments, etc. Specifically the problem is that the built in MaxLength attribute that we are so used to using for a standard TextBox does not work if you are using MultiLine=True.
 
There are a variety of ways to handle this. In some cases I've simply dropped in a line or two of JavaScript and added some onkeypress events to analyze the number of characters the user has typed in and when they reach the maximum to just stop them form entering anything else. That approach works fine, but it involves having to worry about making sure you copy the JavaScript to the right pages and worry about setting up your onkeypress event for each item you want to limit the length of, etc. When researching this I came across a better solution that is pretty simple, yet it's more generic and easier to reuse throughout your projects.
 
The idea is essentially just extending the functionality of the System.Web.UI.WebControls.TextBox control.

public class TextArea : System.Web.UI.WebControls.TextBox
{
     protected override void OnPreRender(EventArgs e)
     {
          if (MaxLength > 0 && TextMode == System.Web.UI.WebControls.TextBoxMode.MultiLine)
          {
               // add javascript handlers for paste and keypress
               Attributes.Add("onkeypress", "doKeypress(this);");
               Attributes.Add("onbeforepaste", "doBeforePaste(this);");
               Attributes.Add("onpaste", "doPaste(this);");

               // add attribute for access of maxlength property on client-side
               Attributes.Add("maxLength", MaxLength.ToString());

               // register client side include - only once per page
               if(!Page.ClientScript.IsClientScriptIncludeRegistered("TextArea"))
               {
                     Page.ClientScript.RegisterClientScriptInclude("TextArea", ResolveClientUrl("~/Common/TextArea.js"));
               }
          }

          base.OnPreRender(e);
     }
}
 
The main thing to recognize is how it's adding the onkeypress, onbeforepaste, and onpaste events to execute JavaScript functions on the client.
 
Another nice thing about this code is that you don't have to worry about putting your JavaScript code everywhere you want to use this functionality. You can simply keep it centralized in a .js file and place it in a folder with your other JavaScript files.
 
In our case we have the necessary JavaScript in TextArea.js, which is as follows:
 
// Keep user from entering more than maxLength characters
function doKeypress(control)
{
     maxLength = control.attributes["maxLength"].value;
     value = control.value;
     if(maxLength && value.length > maxLength-1)
     {
          event.returnValue = false;
          maxLength = parseInt(maxLength);
     }
}
 
// Cancel default behavior
function doBeforePaste(control)
{
     maxLength = control.attributes["maxLength"].value;
     if(maxLength)
     {
          event.returnValue = false;
     }
}
 
// Cancel default behavior and create a new paste routine
function doPaste(control)
{
     maxLength = control.attributes["maxLength"].value;
     value = control.value;
     if(maxLength)
     {
          event.returnValue = false;
          maxLength = parseInt(maxLength);
          var oTR = control.document.selection.createRange();
          var iInsertLength = maxLength - value.length + oTR.text.length;
          var sData = window.clipboardData.getData("Text").substr(0,iInsertLength);
          oTR.text = sData;
     }
}
 
Now to use it you can simply register the control on your page like so:
 
<%@ Register TagPrefix="WebControls" Namespace="Test.WebControls" Assembly="Test.WebControls" %>
 
Then you can use the control as follows:
 
<WebControls:TextArea ID="txtTest" TextMode="MultiLine" MaxLength="500" Rows="10" Columns="75" runat="server" />
 
The value you specify for the MaxLength attribute will determine the maximum number of characters the user will be able to enter.
 
Of course the user can disable JavaScript, so it's always a good idea to test for that accordingly or make sure to validate on the server side as well.

Saturday, September 15, 2007 @ 10:40 am,C-Sharp,Matt Pavey

Most of use the <asp:DropDownList> control on a regular basis, which for almost all cases works as expected; however, if you have any specific styles set for items in the drop down list you probably have noticed that they get lost on a postback. The data itself and the selected value obviously stay intact during the postback, but if you have any special background colors or font colors or any styles applied they don't get maintained on the postback.
 
I ran into this particular situation several months back because I was representing data in a drop down list in a master/detail type format. To make it visually apparent how the data was structured I show the parent category with a gray background color and I show the child categories indented with a white background color. This all looked fine initially; however, after the post back the gray background color was lost.
 
To solve the problem I utilized a custom drop down list class that simply extended the base functionality of System.Web.UI.WebControls.DropDownList
 
public class DropDownListCustom : System.Web.UI.WebControls.DropDownList
{
     protected override object SaveViewState()
     {
          // Create an object array with one element for the DropDownListList's
          // ViewState contents, and one element for each ListItem in skmCheckBoxList
          object[] state = new object[this.Items.Count + 1];
          object baseState = base.SaveViewState();

          state[0] = baseState;

          // Now, see if we even need to save the view state
          bool itemHasAttributes = false;
    
          for (int i = 0; i < this.Items.Count; i++)
         {
               if (this.Items[i].Attributes.Count > 0)
               {
                    itemHasAttributes = true;
              
                    // Create an array of the item's Attribute's keys and values
                    object[] attribKV = new object[this.Items[i].Attributes.Count * 2];

                    int k = 0;

                    foreach (string key in this.Items[i].Attributes.Keys)
                   {
                        attribKV[k++] = key;
                        attribKV[k++] = this.Items[i].Attributes[key];
                   }

                   state[i + 1] = attribKV;
               }
          }
 
          // return either baseState or state, depending on whether or not
          // any ListItems had attributes
          if (itemHasAttributes)
               return state;
          else
               return baseState;
     }
 
     protected override void LoadViewState(object savedState)
     {
          if (savedState == null) return;
 
          // see if savedState is an object or object array
          if (savedState is object[])
          {
               // we have an array of items with attributes
               object[] state = (object[])savedState;
               base.LoadViewState(state[0]); // load the base state
              
               for (int i = 1; i < state.Length; i++)
               {
                    if (state[i] != null)
                    {
                         // Load back in the attributes
                         object[] attribKV = (object[])state[i];
                         for (int k = 0; k < attribKV.Length; k += 2)
                              this.Items[i - 1].Attributes.Add(attribKV[k].ToString(), attribKV[k + 1].ToString());
                     }
                }
           }
           else
                // we have just the base state
                base.LoadViewState(savedState);
      }
}
 
Then to use it on your page you just need to register the object like so:
 
<%@ Register TagPrefix="WebControls" Namespace="Test.WebControls" Assembly="Test.WebControls" %>
 
Then you can use the DropDownListCustom object as follows:

<WebControls:DropDownListCustom ID="ddlTest" runat="server" />
 
Now you'll be able to maintain any formatting in your drop down during postbacks!

Friday, September 14, 2007 @ 6:09 pm,VB.Net,Matt Pavey

Here's another example of how to use generics to retrieve data and return it as the data-type you want to work with.
 
Public Function GetValueFromIDR(Of ItemType)(ByVal IDR As IDataReader, ByVal FieldName As String) As ItemType
     Dim ReturnData As ItemType
 
     If IDR(FieldName) IsNot Nothing Then
          If Not IsDBNull(IDR(FieldName)) Then
               ReturnData = IDR(FieldName)
          End If
     End If

     Return ReturnData
End Function
 
And here's an example of calling it:
 
Dim EventName As String = GetValueFromIDR(Of String)(IDR, "EventName")
 
The use of generics can simplify your code and avoid having to always cast values from one data-type to another.

Friday, September 14, 2007 @ 5:48 pm,VB.Net,Matt Pavey

Using multiple master pages is becoming more handy lately. When I first started using master pages I typically had 1 or 2 of them in a project and a page would only make sense to be viewable in one of those master pages. A good example is having a "public" master page and an "admin" master page.
 
But on one of my projects there is a wide variety of master pages available to provide several different "experiences" based on what the user is trying to do or what the user is viewing.
 
There is one particular scenario where I wanted to let the user see the content in a different master page (e.g. normal view vs. print view) and I wanted to control it based on a querystring parameter, so I needed to change the master page at runtime.
 
Changing the master page programatically in your code-behind file is easy to do, it's just important to know that you have to do it early enough in the page's life-cycle for it to actually work.
 
You can change the master page in the PreInit event.
 
Private Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
     MasterPageFile = "~/Template2.master"
End Sub
 
The PreInit event is new to ASP.Net 2.0. If you try to wait to change the master page in the page's Load or Init event it will be too late!
 
When changing the master page dynamically, you must make sure that all master pages have the same ID for the ContentPlaceHolder controls, so that the content page's Content controls will always match them, regardless of which master page is being used.

Friday, September 14, 2007 @ 5:40 pm,VB.Net,Matt Pavey

Here's an easy way to provide some nifty "highlight" effects to a web form to give the user a helpful visual experience.
 
I think most of us already know the concept of a BasePage and what they are used for, and in general they more times than not are helpful to use as they can provide some very basic functionality to a variety of pages with very little effort. So without getting in to setting up the base page too much, just know that you can create a BasePage in C# or VB and have it inherit from System.Web.UI.Page. In one particular case I have multiple base pages for a variety of things done throughout the site, but the base of all base pages is typically my BasePage.vb file.
 
In BasePage.vb I override the OnLoad page event which looks like:
 
Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
     'add onfocus and onblur to controls so active control has a different appearance
     Utilities.Helpers.SetInputControlsHighlight(Page, "highlight", True)
     MyBase.OnLoad(e)
End Sub
 
In my case I call a SetInputControlsHighlight function that I have in another class in my "Utilities" project, although technically you could have the function directly in the base page if you desired. The function is as follows:
 
Public Shared Sub SetInputControlsHighlight(ByVal container As Control, ByVal ClassName As String, ByVal OnlyTextBoxes As Boolean)
     For Each ctl As Control In container.Controls
          If ((OnlyTextBoxes AndAlso TypeOf ctl Is TextBox)
              Or (Not OnlyTextBoxes
                   AndAlso (TypeOf ctl Is TextBox
                   Or TypeOf ctl Is DropDownList
                   Or TypeOf ctl Is ListBox
                   Or TypeOf ctl Is CheckBox
                   Or TypeOf ctl Is RadioButton
                   Or TypeOf ctl Is RadioButtonList
                   Or TypeOf ctl Is CheckBoxList))) Then
               Dim wctl As WebControl = CType(ctl, WebControl)
               wctl.Attributes.Add("onfocus", String.Format("this.className = '{0}';", ClassName))

               wctl.Attributes.Add("onblur", "this.className = '';")
          Else
               If (ctl.Controls.Count > 0) Then
                    SetInputControlsHighlight(ctl, ClassName, OnlyTextBoxes)
               End If
          End If
     Next
End Sub

The function is pretty simple. It's setup to take the root container as a parameter and it will apply the specified class (e.g. "highlight") to the controls in that container. There is also a parameter to specify if you want the style applyed to all input controls on the form or specifically just TextBox controls.

In my case when the user clicks on an input control on the form it shows the control with a different color so it's obvious that it has the focus, then when the focus is lost it goes back to normal, etc. It can easily be modified to apply to other situations.

The original idea for this code was based the book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso. It's one of the best books I've used throughout my career. It's published by WROX, and all of their books are usually very good (in my opinion), but this one is different than most because rather than a typical reference book, it's actually a step by step solution for building an ASP.Net 2.0 website from the ground up, from listing requirements, identifying problems, designing the database, architecting the solution, implementation, all the way to deployment, etc.

Friday, September 14, 2007 @ 12:52 pm,IIS,Matt Pavey

SYMPTOMS
When you request a file from an IIS 6.0 Web server, and the file has a file name extension that is not a defined MIME type on the Web server, you receive the following error message:

HTTP Error 404 - File or directory not found.
 
CAUSE
Earlier versions of IIS include a wildcard character MIME mapping, which permits IIS to serve any file regardless of its extension. IIS 6.0 does not include this wildcard character MIME mapping and does not serve any type of extension that is not defined at the MimeMap node in the IIS metabase.
 
RESOLUTION

Friday, September 14, 2007 @ 12:36 pm,SQL Server,Matt Pavey

If you don't have a 3rd party tool available to build a list of table definitions for you, such a field names, data-types, sizes, etc. you can query syscolumns, sysobjects, and systypes directly to get this information:
 
For example to get a list of all of the tables you could use:
 
select     o.name,
             'datecreated' = o.crdate
from       sysobjects o
where     o.xtype = 'U'
and        o.status >= 0
order by  o.name
 
Or to get the detailed table definition for a specific table you could use:
 
select       c.name,
               'type' = t.name,
               'default' = (select column_default
                             from information_schema.columns
                             where table_name=o.name
                             and column_name=c.name),
               'length' = case when t.name like '%char%' then c.prec else c.length end,
               'null' = c.isnullable,
               'identity' = sign(c.status & 128),
               'pk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name
                        where tc.constraint_type = 'PRIMARY KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name),
               'fk' = (select count(1)
                        from information_schema.table_constraints tc
                        join information_schema.key_column_usage kc
                        on tc.constraint_name=kc.constraint_name
                        where tc.constraint_type = 'FOREIGN KEY'
                        and tc.table_name=o.name
                        and kc.column_name=c.name)
from         syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          o.name = 'MyTable'
order by    c.colorder
 
Besides the column name and data-type you can also get the default value, size, whether the field allows nulls, whether the field is an indentity value or not, whether the field is part of the primary key, and whether the field is part of a foreign key, etc.

Friday, September 14, 2007 @ 12:27 pm,SQL Server,Matt Pavey

Ever needed to figure out how many tables or stored procedures were going to be affected by some design change in one of your tables, like a field being renamed?
 
Aside from using a 3rd party tool, you can simply query various system tables to get this information.
 
For tables:
 
select       'TableName' = o.name,
               'ColumnName' = c.name,
               'DataType' = t.name
from        syscolumns c
join          sysobjects o on c.id = o.id
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and          c.name = 'MyFieldName'
order by    o.name
 
For stored procedures:
 
select     o.name,
             c.text
from       sysobjects o
join        syscomments c on o.id = c.id
where     o.xtype = 'P'
and        o.category = 0
and        c.text like '%MyFieldName%'
order by  o.name
 
It's easily customizable to fit your needs.
 
You can find more information on the sysobjects table here:
 

Friday, September 14, 2007 @ 11:52 am,SQL Server,Matt Pavey

So you've got a delimited list of values and you want to somehow pass those to SQL as a single parameter and use them in your query?
 
One style that would accomplish this behavior is to have your SQL statement built dynamically in one of your layers of code.
 
Another option would be to pass in the delimited list to a stored procedure and parse the string yourself with some string operations and a cursor and build a dynamic SQL statement and then use EXEC to get the results.
 
But I find myself typically wanting to use a more generic approach that has some level of reusability, hence a user defined function (UDF) to handle the split and keep the rest of the SQL statements very simple.
 
For example, let's say you have this SELECT statement:

select     ID,
             Name
from       MyTable
where     Name in ('Test1', 'Test2', 'Test3', 'Test4')
 
Nothing too exciting here. It simply returns the 4 records that we've asked for.
 
But what if we didn't know what records the user was going to ask for and we needed to make this statement more generic and parameterized.
 
Something like this would be nice; however, as you probably know, it's won't yield the results you need:
 
declare @List varchar(100)
 
set @List = 'Test1, Test2, Test3, Test4'
 
select    ID,
            Name
from      MyTable
where    Name in (@List)
 
Hence we introduce our user defined split function:
 
CREATE FUNCTION [dbo].[fnSplit]
(
     @List VARCHAR(8000),
     @Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
     ID INT IDENTITY(1,1),
     Value VARCHAR(100)
)
AS
BEGIN
     -- loop through the list
     WHILE (CHARINDEX(@Delimiter, @List) > 0)
     BEGIN
          -- add the value to the table
          INSERT INTO @Table
               (Value)
               SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1)))

          -- remove the value from the list
          Set @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + LEN(@Delimiter), LEN(@List))
     END

     -- insert remaining value from the list
     INSERT INTO @Table
          (Value)
          SELECT Value = LTRIM(RTRIM(@List))

     -- return
     RETURN
END

This function uses a simple while loop that just processes part of the delimited list at a time, removes the value, and continues processing the rest of the string until there is nothing left to process.

The final results end up being a simple table with records representing the delimited string in a tableized format.

For example:

declare @List          varchar(100)
declare @Delimiter   varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     *
from       dbo.fnSplit(@List, @Delimiter)

Let's modify our query from earlier to utilize our new split function and we'll see how it all ties together:

declare @List varchar(100)
declare @Delimiter varchar(1)

set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','

select     ID,
             Name
from       MyTable
where     Name in (select Value from dbo.fnSplit(@List, @Delimiter))

We now simply can pass in our delimited list and delimiter and utilize it in a simple SQL statement to get the results we needed.

Friday, September 14, 2007 @ 11:44 am,SQL Server,Matt Pavey

This is one of my favorite little tricks to use in SQL Server programming. I don't have to use it very often, but it's definitely a nice way to build a delimited string, considering the other options available.
 
 
Like I said, I've only had to use this in 2 or maybe 3 places, but it definitely saved some time and definitely kept the code simple and clean!
 
Basically the old approach was to return a set of records and through some layer of code build a delimited list... Or if you were a bit more savvy you might have kept it all at the database level and used some type of cursor... But with COALESCE you can create the same results in a single SELECT statement.
 
The article shows some good examples of how to utilize this.

Friday, September 14, 2007 @ 11:41 am,SQL Server,Matt Pavey

The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.
 
 
This is handy if you don't have access to any other tools for testing SQL queries, which in most cases is probably rare, but still, it's nice to have other options available just in case.

Friday, September 14, 2007 @ 11:35 am,SQL Server,Matt Pavey

Anyone who's written SQL statements has undoubtedly had to CONVERT values from one data-type to another. I do this quite regularly, specifically with converting a varchar value to a specific date format, or converting a date from one format to another.
 
By now I've got most of the different options memorized, but this MSDN reference always was a life saver in the middle of the night when I couldn't quite remember what I was after.
 
 
Enjoy!

Friday, September 14, 2007 @ 11:20 am,VB.Net,Matt Pavey

This makes life much easier when you're trying to get to controls that are themselves contained within other containers, eg, a TextBox inside a DataView or DataList.

Public Function FindControlRecursive(Of ItemType)(ByVal Ctrl As Object, ByVal id As String) As ItemType
     If String.Compare(Ctrl.ID, id, StringComparison.OrdinalIgnoreCase) = 0 AndAlso TypeOf Ctrl Is ItemType Then
          Return CType(Ctrl, ItemType)
     End If
 
     For Each c As Control In Ctrl.Controls
          Dim t As ItemType = FindControlRecursive(Of ItemType)(c, id)

          If t IsNot Nothing Then
               Return t
          End If
     Next
 
     Return Nothing
End Function

This example also demonstrates the use of Generics, which before .Net 2.0 was only available with cast typing and use of base objects, etc. There are several good articles online about generics, but here are a couple to get you started.

http://www.codeproject.com/csharp/Generics_Explained.asp

http://www.ondotnet.com/pub/a/dotnet/2005/06/20/generics.html

Friday, September 14, 2007 @ 11:18 am,C-Sharp,Matt Pavey

This makes life much easier when you're trying to get to controls that are themselves contained within other containers, eg, a TextBox inside a DataView or DataList.
 
private Control FindControlRecursive(Control root, string id)
{
    if (root.ID == id)
    {
        return root;
    }

    foreach (Control c in root.Controls)
    {
        Control t = FindControlRecursive(c, id);
        if (t != null)
        {
            return t;
        }
    }

    return null;
}

Friday, September 14, 2007 @ 11:08 am,AJAX,Matt Pavey

The ASP.NET AJAX Control Toolkit provides a set of sample controls and extenders that makes it a snap to spice up your web site with rich functionality. Everything you need to get started is inside including full source code, templates, documentation, and more!
 

Friday, September 14, 2007 @ 11:04 am,AJAX,Matt Pavey

ASP.NET AJAX is a free framework for quickly creating a new generation of more efficient and interactive Web experiences that work across all the most popular browsers.
 
With ASP.net AJAX you can:
  • Create next-generation interfaces with reusable AJAX components.
  • Enhance existing pages using powerful AJAX controls with support for all modern browsers.
  • Continue using Visual Studio 2005 to take your ASP.NET 2.0 sites to the next level.
  • Access remote services & data from the browser without tons of complicated script.
  • Enjoy the benefits of a free framework with technical support provided by Microsoft.

Wednesday, September 12, 2007 @ 2:45 pm,.Net,Matt Pavey

Here's an interesting article by Nigel Shaw about the fiercely contested debate regarding C# vs. VB.Net.
 
 
Let me say up front that I in no way agree with all of the comments or "opinions" made in the article, but I do think it's an interesting point of view. More than anything though I think he makes a great point regarding the fact that C# and VB are functionally equivalent!!!
 
Excerpt from Article:
We've seen that the cultures of VB and C# are very different. And we've seen that this is no fault of the programmers who use them. Rather this is a product of the combination of factors that collectively could be called their upbringing—business environment, target market, integrity and background of the original language developers, and a myriad other factors.
 
Excerpt from Article:
The brilliant insight Microsoft had was not to support multiple languages—if this was the case then surely it would not have bothered with J#, which is syntactically so close to C# that support for language’s sake alone would be ridiculous. The insight Microsoft had was to support multiple cultures.
 
One thing I found intersting in his article was his "In concrete terms" list regarding various statistics on C# vs VB programmers. Keeping in mind the numbers he uses are "opinions" and not actual statistics. But with that said, the one item I think is most important is:
 
A good programmer accomplishes two to ten times what an average programmer accomplishes, and causes 90% less bugs and headaches.
 
Regardless of language preference, I think the definition of a good programmer should be based on the quality of the code, performance, scalability, and long-term effectiveness of the code, not whether or not you chose VB or C#.
 
Examples of key differences between C# and VB are listed below. These are simply examples and not an exhaustive list:
  1. VB by default allows support for late binding. Although it can be turned off with Option strict, the culture is such that it’s usually left on. This leads to numerous difficulty to catch errors. C# binding is always early.
  2. VB still supports the old On error goto construct. This leads to sloppy or non-existent error handling. C# supports only the superior try…catch error handling.
  3. VB supports optional parameters. Although VB developers often list this as an advantage, it is a disadvantage because the use of optional parameters weakens the contract between the caller and the method, allowing the developer to slacken his analysis and get away with it until bugs creep in. [Note: C# param array construct is not the same as optional params]
  4. VB supports the legacy VB functions, with reference to Microsoft.VisualBasic.dll. Many of these functions are slow and they should all be avoided in favor of the .NET Framework. However many VB programmers still use them. In new VB projects, this dangerous namespace is included by default.
  5. VB allows implementation of interfaces with methods of different names, making it confusing and difficult to find the implementation. C# does not.
  6. VB supports background compilation. While this speeds the development cycle for small projects, it slows down the IDE in large projects, contributing at least in part to the culture tending to gravitate toward small projects.
  7. C# namespaces are managed in way that makes programmers aware of namespaces and their importance. VB namespaces are managed in a way that hides them from the programmers by default. Careful attention to namespace management is a fundamental tenet of strong application design and its importance cannot be overestimated.

The reason I'm posting a link to this article in the first place is because too many times when I'm researching a solution or looking at an article online I see someone post a code-snippet in VB or C# and there almost always is another post asking for the code to be converted to the other language. That of course always leads to a steamed argument about which language is better and why.

Taking the time to really understand the languages and the framework would solve most of these arguments and it would become very clear, in my opinion, that it's a matter of preference.

I come from a classic ASP and VB6 background, so naturally when .Net was evolving I made the quick transition to ASP.Net and VB.Net. I did very little to no C# programming for the first year or so. Not because I thought it was inferior, but because it was easier for me to continue down the VB.Net path and frankly most of the clients I was working with at the time had standardized on VB.Net, which made the decision even more obvious.

With that said, however, for the past few years I've became very involved with C# projects and I consider myself extremely fluent in both VB and C#.

I'm always learning new techniques each day, in both languages, so it makes it extremely rewarding to be able to be open-minded from project to project and make a seamless transition from one to the other at any given time.

My personal opinion is that if the client has standardized on a language, we as developers should honor that unless there is an obvious reason not to, and in certain circumstances I'm sure there are good reasons to make a recommendation.

I think the best thing is to be flexible and know as much as you can about both languages, as there are most certainly going to be times when you have to work on a project that isn't your language of choice.

Monday, September 10, 2007 @ 4:39 pm,SQL Server,Matt Pavey

Thought I’d share this article with everyone:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

I try to avoid temp tables whenever I can, but there are too many cases where there’s no better way to do something.

In the past I typically used #temp tables simply because that is the only thing I really knew how to use and because when I took over some previously written applications there were a lot of cases where they were used.

However, anytime I have to use a temp table now I typically use a table variable directly in the stored procedure.

Today was a great example of why only one of those would work. There was an old stored procedure that uses a #temp table and that same logic is unfortunately copied into several different places. Today I finally got fed up and created a user-defined-function (UDF) to centralize some of the logic. I found out very quickly that you can not use a #temp table in a UDF, which about made me pass out… But then I realized I could simply use a table variable which I prefer anyways.

Another nice thing about table variables is you don’t have to manually clean them up (e.g. drop table #temp).

The article explains different scenarios and recommended practices. Worth looking at if you ever do much with stored procedures or temp tables.

The opinions expressed on this website are my personal opinions
and do not represent my employer's or my clients' views in any way.