Rydal's .Net Clr

Concatenating multiple rows into single column in SQL Server

Rydal Williams — Tue, 29 Sep 2009 19:44:04 GMT

Sometimes you need to return a single column that contains values that are comma separated or pipe separated or what ever floats your boat from a multiple row result set, a perfect example is returning the category id’s for a product that could be in one of more categories but you don’t want to return multiple rows with duplicate information.

There are two ways of doing this or two that I know of – meaning simple enough.

1. The COALESCE method, which is probably the most common. This method works by tricking the coalesce function into doing something else, COALESCE is very similar to ISNULL except it can take in more than 2 arguments.

select @results = COALESCE(cast(ProductCategoryId as varchar)+',','')
    from ProductCategory where ProductId = 24

2. Using the FOR XML PATH expression which works very well and its probably my preferred method because it takes order into consideration. If you use the FOR XML PATH without specifying the column name, you get concatenation – cool trick huh?

select cast(ProductCategoryId as varchar) + ',' from Productcategory
            where ProductId = 24 for xml path('')

Have fun!!!

Using web.config for SMTP settings

Rydal Williams — Wed, 19 Aug 2009 02:02:33 GMT

In the old days, we have to manually create custom configuration sections if we want to do cool stuff with app/web.config or simply just put the values in the appSettings section. However, there are a few config options that are out there that most developers don’t know about, a perfect example is the mailSettings config section which allows you to setup your mail settings in your configuration file and simply call the SMTPClient to send an email and by doing so, changing settings can be done on the fly and oh’ if you are worried about security, don’t forget you can definitely encrypt your configuration file if you need to.

<system.net>
    <mailSettings>
        <smtp deliveryMethod="Network" from="name@domain.com">
            <network host="smtp.mail.com" 
                     userName="name@domain.com" 
                     password="blog.dotnetclr.com" port="25"/>
        </smtp>
    </mailSettings>
</system.net>

And here is the sample C# code that will use the above configuration settings

public void SendBy(string to, string subject, string body)
{
    var mailMessage = new System.Net.Mail.MailMessage();
    mailMessage.To.Add(to);
    mailMessage.Subject = subject;
    mailMessage.Body = body;

    var smtpClient = new SmtpClient();
    smtpClient.EnableSsl = true;
    smtpClient.Send(mailMessage);
}

Refresh SQL Server 2008 Intellisense

Rydal Williams — Fri, 14 Aug 2009 15:58:34 GMT

If you are are using Microsoft SQL Server 2008 Management Studio, you would have noticed that it does have intellisense built in and very addictive if I must add. Anyway, problem here is SQL Server caches schema’s, therefore, when you make a schema change such as adding a new column to a table, intellisense doesn’t pick it up immediate. To quickly refresh the cache strike (CTRL+Shift+R) and that should do it. Viola!

A simple way to resize an image

Rydal Williams — Tue, 28 Jul 2009 14:13:14 GMT

Here is a very simple way to resize an image using a bitmap as a canvas and the graphics object as a the resizing tool. Don’t forget to reference System.Drawing.

/// <summary>
/// Resizes an image from a source file to a destination file
/// the destination file will contain the exact image at the size specified
/// </summary>
/// <param name="sourceImagePath">the source image file that needs to be resized</param>
/// <param name="destinationImagePath">the destination path that the source 
/// image file needs to be resized to</param>
/// <param name="width">the new width</param>
/// <param name="height">the new height</param>
static void ResizeImage(string sourceImagePath, 
    string destinationImagePath, int width, int height)
{
    // Create an image object of the original image
    System.Drawing.Image 
        originalImage = System.Drawing.Image.FromFile(sourceImagePath, true);     

    // Create a new bitmap that we will use as a canvas to drawn on the new resized imaged
    Bitmap newBitmap = new Bitmap(originalImage, new System.Drawing.Size(width, height));

    // Using the graphics object draw the orginal image on the canvas that was created above
    using (Graphics g = Graphics.FromImage(newBitmap))
    {
        g.InterpolationMode = 
            System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
        g.DrawImage(originalImage, 0, 0, width, height);
    }

    // Save the new resize image to a new path
    ((Image)newBitmap).Save(destinationImagePath);
}

Write out page load times using an HttpModule and the stopwatch class

Rydal Williams — Fri, 24 Jul 2009 17:40:21 GMT

One of the most efficient and non-intrusive ways you can implement an intercepting procedure is via an HttpModule. An HttpModule simply receives every request going to or coming from IIS and it allows you to manipulate the content as you see fit and pass it along. I wanted to spit out the time it takes from receiving a request to writing the response on one or more web pages and there was no other better way of doing it except writing an HttpModule, it toke me all but 10mins. Here is the code, feel free to blow it up.

class PageLoadTime: System.Web.IHttpModule
{
    #region IHttpModule Members

    public void Dispose()
    {
        // nothing to dispose off
    }

    public void Init(System.Web.HttpApplication context)
    {
        context.BeginRequest += new EventHandler(context_BeginRequest);
        context.EndRequest += new EventHandler(context_EndRequest);
    }

    bool IsReady(System.Web.HttpContext ctx)
    {
        return (ctx.IsDebuggingEnabled 
            && ctx.Request.IsLocal 
            && ctx.Response.ContentType.Equals("text/html"));
    }

    void context_BeginRequest(object sender, EventArgs e)
    {
        if (IsReady(HttpContext.Current))
        {
            Stopwatch sw = new Stopwatch();
            HttpContext.Current.Items.Add("PageLoadTimeStopWatch", sw);
            sw.Start();
        }
    }

    void context_EndRequest(object sender, EventArgs e)
    {
        if (IsReady(HttpContext.Current))
        {
            // stop the watch
            Stopwatch sw = (Stopwatch)HttpContext.Current.Items["PageLoadTimeStopWatch"];
            sw.Stop();

            // render the time elapsed
            string elapsedTime = string.Format(
                "<b>Page Loaded In <font color=red>{0}</font> secs</b>", 
                decimal.Round(decimal.Parse(sw.Elapsed.TotalSeconds.ToString()), 2));
            HttpContext.Current.Response.Write(string.Format("{0}", elapsedTime));
        }
    }

    #endregion
}

Share this post :

MySpace’s Performance Tracker

Rydal Williams — Wed, 01 Jul 2009 23:23:41 GMT

It seems the MySpace gurus are giving back to the community, they are now providing there IE performance tracker for free to the public.

MySpace’s Performance Tracker is a browser plug-in that help developers to improve their code performance by capturing and measuring possible bottlenecks on their web pages.

Measure the CPU hit and memory footprint of your pages as they render on the client’s browser
Review screen shots of the page while it renders
Review the rendered HTML on each point of the page’s lifecycle
Measure and show estimates of the time it takes to render each section of the page in different connection speeds
Validate the content of your page against a set of proven “best practice” rules of web development
Review downloaded files and show download time estimation on different bandwidths

MySpace’s Performance Tracker currently supports Internet Explorer 6 and up.

Download here

Convert virtual path to absolute or relative path

Rydal Williams — Mon, 08 Jun 2009 21:43:49 GMT

There are a lot of goodies embedded in the framework, problem is, there are so many namespaces that it is almost impossible to know them all, as a matter of fact I doubt that there is any one person that knows all of the framework in and out. As usual, this post is about my experience with weird code that could have been done easily using existing built functions. I’ve seen developers write 20+ lines of code to this stuff while covering every possible scenario.

convert to absolute

System.Web.VirtualPathUtility.ToAbsolute(params);

convert to relative

System.Web.VirtualPathUtility.ToAppRelative(params);

System.Web.VirtualPathUtility.MakeRelative(params);

Converting SQL DateTime to Date

Rydal Williams — Mon, 08 Jun 2009 15:09:11 GMT

Apparently, converting a SQL long datetime string to just a date seems to be a head banger for some people. therefore, I’ve decided to publish the way I do it, which I believe is easy and probably the most efficient.

convert(varchar(10),[datetimecolumnname],101)

replace [datetimecolumnname] with the column name of the actual column that is of datetime data type.