Linq To SQL Visual Studio Visualizer

LINQ to SQL Debug Visualizer
I found the coolest tool for linq on Scott Guthrie's blog. A plugin that will show you the sql that is generated by a linq to sql statement if you hover over it in debug mode. WOW does this make life easier!

Download here.

Scott shows you how to use it here.

Free ASP.NET MVC eBook PDF

This is actually pretty cool. The ASP.NET MVC 1.0 book has gone into publishing and you can download the first chapter (All 195 pages of it) for free. The goes over the creation of an MVC project called Nerdinner which is actually available for free here. If you are at all interested in ASP.NET MVC you will want to check it out. 

Cheers!

SQL Stored Procedure Example

For you guys over in Yakima who want to see some more SQL I thought I’d whip up a quick example of a pretty typical stored procedure that could have been used on my Digital Gamer Zone website. Here is the relevant diagram for the SQL I will be writing.

For this example I want to create a stored procedure that will return the ASIN and Title of all the games associated with a particular UserId. The ASIN is actually Amazon Standard Identification Number. If you have gone to http://digitalgamerzone.com/ , registered and gone to the “My Games” tab on you will find a huge library of games from which to drag and drop Into your personal Library. Now I sure as heck did not download those by hand. I created a small app that would download them for me after hitting an Amazon web service to find all the Xbox games that Amazon sells. If it’s good enough for Amazon, it’s good enough for me.

USE [MyGameSiteDB]
GO

SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
Create PROCEDURE [dbo].[GetUserGames] (
@UserID NVARCHAR(36)
)
AS
BEGIN

Select Distinct g.ASIN,
g.Title
FROM MyGames mg
Join Games g on mg.ASIN = g.ASIN
Where
mg.userid=@UserId

End

This example is ridiculously simple but at least it’s real. Who want s to see another Select * from Employees example? Not me!

Also, though my score is currently low. I will pwn you on Halo 3! That’s right, I said it!

My Woot! Google gadget

My Woot! Google gadget

Though I have known about woot for a while now I have recently started tracking it on a daily basis and decided I needed a woot gadget for my google home. A look at the API reveals that google gadgets are quite easy to create, as you can see in the "Hello World" example below.

<?xml version="1.0" encoding="UTF-8" ?>

<Module>

<ModulePrefs title="hello world example" />

<Content type="html">

<![CDATA[

Hello, world!

]]>

</Content>

</Module>

Inside The CDATA section you can put whatever html you want. I want to put html containing the url of the woot main image, the title and the price. These are all easily scraped from the woot website. I used the HTML Agility pack found on codeplex

http://www.codeplex.com/htmlagilitypack

Using the HTML Agility pack allows you parse HTML documents using XPATH. This is an amazing intuitive mechanism for scraping and allowed me to get what I wanted in just a few minutes.

Code to retrieve the title, image path, and price listed below

HtmlWeb hw = new HtmlWeb();

string url = @"http://www.woot.com";

HtmlDocument doc = hw.Load(url);

HtmlNode ImageNode = doc.DocumentNode.SelectSingleNode("//img[@class='salePic']");

HtmlNode PriceNode = doc.DocumentNode.SelectSingleNode("//span[@id='PriceSpan']");

HtmlNode TitleNode = doc.DocumentNode.SelectSingleNode("//h3[@id='TitleHeader']");

string imgSrc = ImageNode.Attributes["src"].Value;

string priceSpan = PriceNode.InnerText;

string titleText = TitleNode.InnerText;

As you can see, using the HTML Agility pack takes all the pain out of screen scraping a website. Just write the out put to the CDATA section and you are done. I just did this in an aspx page by removing all the standard mark up and replacing it with the xml surrounding CDATA.

%@ Page Language="C#" AutoEventWireup="true" CodeBehind="wootGadget.aspx.cs" Inherits="MyGames.Gadgets.wootGadget" %>

<?xml version="1.0" encoding="UTF-8"?>

<Module>

<ModulePrefs title="woot!" />

<Content type="html"><![CDATA[

<% GetWoot(); %>

]]></Content>

</Module>

The code behind.

sing System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using HtmlAgilityPack;

namespace MyGames.Gadgets

{

public partial class wootGadget : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

public void GetWoot()

{

HtmlWeb hw = new HtmlWeb();

string url = @"http://www.woot.com";

HtmlDocument doc = hw.Load(url);

//HtmlNode node = doc.DocumentNode.SelectSingleNode("/html/body/div[position()=1]/div[position()=1]");

HtmlNode ImageNode = doc.DocumentNode.SelectSingleNode("//img[@class='salePic']");

HtmlNode PriceNode = doc.DocumentNode.SelectSingleNode("//span[@id='PriceSpan']");

HtmlNode TitleNode = doc.DocumentNode.SelectSingleNode("//h3[@id='TitleHeader']");

string imgSrc = ImageNode.Attributes["src"].Value;

string priceSpan = PriceNode.InnerText;

string titleText = TitleNode.InnerText;

Response.Write("<span><strong>woot!</strong></span><br />");

Response.Write("<a href='http://www.woot.com/' target='_blank' ><img src='" + imgSrc + "' height='150' style='border:none;float:left;' /></a>");

Response.Write("<span style='font-size:.8em;'>" + titleText + "</span><br />");

Response.Write("<span><strong>" + priceSpan + "</strong></span>");

}

}

}

Log4net .NET Logging

Log4net makes logging in .NET easy. For myself I can say that using logging makes me a far more effective developer. Plus you can turn up interesting stuff :P

3 steps to get log4net

1.) Add the log4net.dll assembly to your project
2.) Modify a *.config file to include an appender
3.) Add code to load the config

1.) Is self explanatory



2.)

<?xml version="1.0"?><configuration>
<configSections> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net"/>
</configSections>
<appSettings> <add key="log4net.Internal.Debug" value="true" /> </appSettings>
<log4net>
<root>
<level value="DEBUG" />
<appender-ref ref="LogFileAppender" /> <appender-ref ref="ConsoleAppender" />
</root>
<appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender" >
<param name="File" value="logs/log2.txt" />
<param name="AppendToFile" value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="10MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
</layout>
</appender>
</log4net>
</configuration>

3.) In assembly.info add
[assembly: log4net.Config.XmlConfigurator(Watch = true)]
or
Log4net.Config.XmlConfigurator.Configure()

SQL Cursor Example

I am rarely required to do anything very difficult in SQL, and really SQL doesn’t get THAT difficult so this is maybe the second time I have used a cursor. What follows is a very basic SQL cursor example that I used earlier today. If it mattered more and I had more time I probably would have taken a look at doing this without using a cursor as cursors are non-performant. None-the-less it was a fun exercise. Another fine example,including the non-cursor implementation, can be seen here http://www.sql-server-performance.com/articles/per/operations_no_cursors_p1.aspx

DECLARE @customerId int,

@firstName nvarchar(255)

DECLARE Customer CURSOR FOR

SELECT customerId,

firstName,

FROM CustomerTable

OPEN Customer

FETCH Customer INTO @customerId,

@firstName

WHILE @@Fetch_Status = 0

BEGIN

-- row by row operations

Print

FETCH Customer INTO @customerId,

@firstName,

@vchCustomerName

END

CLOSE Customer

DEALLOCATE Customer

Go

Why We Use Properties in .NET

Properties are extremely useful in .NET for debugging, validation, raising an event when data is changed and changing data in other fields.

Let's look at an example.


public class Email
{
private string emailAddress;

public string EmailAddress
{
get
{
return emailAddress;
}
set
{
if (isValidEmail(value))
{
emailAddress = value;
}
}
}

public bool isValidEmail(string inputEmail)
{
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
Regex re = new Regex(strRegex);
if (re.IsMatch(inputEmail))
return (true);
else
{
return (false);
//and possibly log error
}
}
}

In this example we have a private field named emailAddress and a Property named EmailAddress. This is useful we want to validate the email address before setting it. We simply test the value using isValidEmail() in the property setter. This allows us to omit this code from our procedural code(where we don't want to see it anyway) .

The Downside is that it does create some overhead and it is more typing. Hardly a sacrifice in todays world.

Properties are quite simply a more robust and future proof way to handle data. It is not necessary to use properties by any means but if there is any question as to the validity of data being passed in or if it is likely that the library will evolve then it is a good idea to use properties.