SQL WHERE List Matches Any or All

I saw a cool post recently from Jon Galloway called "Passing lists to SQL Server 2005 with XML Parameters".  This is a pattern I've used several times while building the new version of Channel 9.  If you'd like to learn how to pass in lists to stored procedures, check out Jon's post.

One of the times I've used it is to search our database for all entries from two of our forums.  For this example, we'll say Techoff and Sandbox.  Once you have a temp table with the two forum ids (actually forums in our system are just tags too) you can just do a where in statement like the following:

SELECT e.* FROM Entry e INNER JOIN EntryForum ef ON e.EntryID = ef.EntryID WHERE ef.ForumID IN (SELECT ForumID FROM ForumList)

Note: This is all pseudo code to represent the basics of how we do this.  This is not the exact code.

This selects all the entries (or posts) from our database that are from the list of forums I passed into the ForumList temp table.  WHERE IN specificies that all rows be returned that match ANY of the records in my temp table.  The following statement would be equivalent and work exactly the same.

SELECT e.* FROM Entry e INNER JOIN EntryForum ef ON e.EntryID = ef.EntryID WHERE ef.ForumID = @ForumID1 OR ef.ForumID = @ForumID2

Note: In the above example, @ForumID1 and @ForumID2 have the values that were stored in the ForumList temp table in the example above that one.

This works pretty well.  The other thing we do with passing in lists though is selecting only the entries that match ALL (not ANY) of the list we pass in to the stored procedure.  The example of this is when searching by multiple tags.  So for instance, you want to search on our site for all content that contains information on WPF AND WCF.  The previous example won't work.  It would instead need to be something like this…

SELECT e.* FROM Entry e INNER JOIN EntryTag et ON e.EntryID = et.EntryID WHERE et.TagID = @TagIDWPF AND et.TagID = @TagIDWCF

Using WHERE IN, we can't do this (at least I couldn't find anything in the docs or internet searching to say otherwise).  Duncan helped figure out the idea on how to do this and here is the implentation I came up with:

DECLARE @TagCount int
DECLARE @Tags TABLE (TagID bigint)
DECLARE @Entries TABLE (EntryID bigint)

SELECT @TagCount = COUNT(*) FROM @Tags
;
WITH Entries(EntryID, MatchCount) AS
(
    SELECT
        e.EntryID,
        COUNT(DISTINCT t.TagID) AS MatchCount
    FROM
        Entry e
            INNER JOIN
        EntryTag et
            ON
                e.EntryID = et.EntryID
            INNER JOIN
        @Tags t
            ON
                et.TagID = t.TagID
    GROUP BY
        e.EntryID
)
INSERT INTO @Entries (EntryID) SELECT EntryID FROM Entries WHERE MatchCount = @TagCount

What is this code doing?  Well, first, it's doing a count on the tags that were passed in (again, from XML turned into a temp table) and storing it in a variable.  Then, it creates a Common Table Expression or CTE around a query that returns all the entries that match the tag list and how many of those tags it matches up with.  If you're not familiar with CTEs, they're basically a wrapper around a query so you can write a query against it.  Kind of like a subquery, but much more organized.  Recursive CTEs are particularly powerful and cool, but that's another blog post.  So then after creating the CTE, fill another temp table with everything from the CTE where the MatchCount equals the count of how many tags were passed in originally.  This means that the entry returned had ALL the tags passed in associated with it.  So this will now only return entries that match ALL of the tags from the list that I passed in (stored in @Tags).  I hope this helps someone.  πŸ™‚

UPDATE: Check out the first comment from Bryan.  He points out a slightly better implementation.  Thanks, Bryan!

Best Comment on Slashdot EVER!

I'm a Slashdot browser.  I've never commented on anything thing and never will.  Slashdot is 90% crap (mostly because of the comments left there).  That's my opinion and I'm sticking to it.  That said, it's good to watch what goes on there, because every once and a while there is a gem of great insight.

This isn't the kind of gem I'm referring to, but it is still a gem and TOO GOOD and TOO TRUE to pass up.  My buddy Tobin pointed me to this one…

http://apple.slashdot.org/comments.pl?sid=44091&cid=4592270

I'm not a big fan of bashing people…unless they deserve it.  πŸ™‚

Robert Fripp on Channel 9 Again

This is really great.  The way they went about making the sounds for Windows Vista is really cool.  There are some really great ideas in this video like having calm music (you have to listen to the video to hear them) in the background while installing Windows Vista.  It's sad that it didn't make it into the final product.  Maybe they'll come out with some music from this we can buy.  Check it out…

Making Windows Vista Sing: Robert Fripp and the Vista Melody

Re: ASP.NET Custom Authentication Problems

I just got a comment from an old post from over 2 years ago about some custom auth problems I had.  It's funny looking back that far to see how you were.  As with all typical developers, I look back and laugh at myself.  πŸ™‚

The question posted was if I could share my custom authentication code.  The fact of the matter is, the way I was doing authentication at the time was kind of silly and I thought I'd post how we do it now.  It's much simpler and uses everything that's already built into ASP.NET 2.0 already.  When a user logs into your site (using whatever type of authentication you want), the Context.User (same User object that shows up on the Page class, etc) is set to an IPrincipal.  Depending on what "username" you passed in for it to tack on to the cookie, you'll be ablel to access a key for looking up more detailsl about the user.  Then the same applies from the rest of my 2 year old post.  Create a BasePage class that inherits from Page and shadow the User property with your own.  Here's the code from our platform in the BasePage class.

public bool IsAuthenticated
{

get { return Request.IsAuthenticated; }
}

private string username;
public string Username
{

get

{

if (username == null)

{

if (IsAuthenticated)

username = base.User.Identity.Name;

else

username = "";

}

return username;

}
}

private bool isUserSet;
private EvNetUser user;
public new EvNetUser User
{

get

{

if (!isUserSet)

{

if (IsAuthenticated)

user = Users.Retrieve(Username);

isUserSet = true;

}

return user;

}
}

Now anywhere in our site we can say Page.User and get back an object filled with everything we need to know about the current user.  If the request is anonymous, Page.User will return null.  Hope this helps Mohammed!

How to Use Embedded JavaScript Files in ASP.NET AJAX

I'm writing up a post about creating custom client controls at the moment (well, as of a few minutes ago anyway).  It is getting pretty long and it's way past my bedtime.  So I thought in the meantime I'd post a quick little blurb about how you can use JavaScript from an assembly for your ASP.NET AJAX code and why you would want to.

It's pretty easy to setup a .js file in your web project with some code in it.  Sometimes though the code in said file is associated with say a custom client control (something that inherits from Sys.UI.Control) that is in its own assembly (not your web project).  This is how our controls are setup for our platform.  This is so we can use the same controls across Channel 9, Channel 10, etc.  The problem is now that we have code in js files, they have to be replicated across all our web projects and that's just no fun.  So instead we moved to having the js files embedded in our main class library.  The server controls register these files and they are then pulled out of the assembly and sent down to the client and cached.  Here's how to set this up yourself…

Add the following line as the last line in your js file:

if (Sys != undefined) Sys.Application.notifyScriptLoaded();

This tells ASP.NET AJAX that the file is done loading.  This is needed because all embedded js files stream down in the same "file" so the end of the file isn't necessarily the end of what's streamed down to the client.  Unlike when you just like to a js file regularly.

Now, in Visual Studio go to the properties window while your js file is selected.  Change the Build Action to Embedded Resource.  This will compile the file into the assembly as a resource.  If you open up reflector and venture through, you'll find the js file.  Now in your server control, add this line so ASP.NET knows about the resource (and what mime type to send it down as):

[assembly: WebResource("EvNet.Web.Templates.Scripts.Toolbar.js", "text/javascript")]

Now anytime you add a ScriptReference to a ScriptManager, your file will be streamed down to the client.  Just specify the resource name (This is the physical file path down to the file starting at the root of your class library with slashes replaced by periods [see below]) and the assembly the resource is in and you're done.  No need to worry about where the file is anymore.  πŸ™‚  This of course works when implementing IScriptControl.GetScriptReferences in your server control too:

public IEnumerable<ScriptReference> GetScriptReferences()
{
     return new ScriptReference[] { new ScriptReference("EvNet.Web.Templates.Scripts.Toolbar.js", "EvNet") };
}

Enjoy!

Intro to ASP.NET AJAX Custom Client Controls

So in my journey to explore our platform that runs Channel 9 and Channel 10 and soon a few more sites continues with a walkthrough of some basics on creating custom controls in ASP.NET AJAX.  More than likely if you're reading this it's because you already know about ASP.NET AJAX 1.0 or have at least heard about it.  You found this post through a search on your favorite search engine, saw it on the weblogs.asp.net home page, subscribe to my feed or someone linked to it (here's hoping).  If you have no idea what it is, well…go check out the site and come back.  πŸ™‚  That's probably the easiest way to explain it.

ASP.NET AJAX 1.0 Basics

ASP.NET AJAX has basically built on top of JavaScript to make it cross-browser compatible as a framework on top.  This makes developing things like custom client controls very similar to building classes in .NET.  There are namespaces, classes, enums, methods, fields, properties, inheritance, etc.  There is also a server control architecture to allow you to build server controls to configure and instantiate custom client controls.  I'll talk about this later.  The best way to think of ASP.NET AJAX is to think of it as a client technology.  Yes there are server aspects, but the server just acts as the middle man.  It gets requests for pages and servers and returns the appropriate data.  This is how it is today except now we can push more logic down to the client so the server doesn't have to render UI logic as much.

UpdatePanel

While this post isn't going to focus on the UpdatePanel, it's good to know about it and how it compares to custom controls.  The UpdatePanel is gold.  Plain and simple.  Build some code on your ASP.NET page, say a GridView with some data bound to it.  It works great and just does the usual postbacks.  Page through some data, yippee.  Now surround it with an UpdatePanel and watch the "magic".  Paging now without postbacks?  Glorious!  Like all great things that make our lives easier,  you can definitely overuse it.  Working with ViewState just like a normal postback would, sending it back and forth between requests, is its greatest strength and weakness.  You can still have the same problems of bloated ViewState that can make those monthly bandwidth bills for your site go up and up.  The UpdatePanel is great for quick and dirty AJAX.  If you have a simple form you want to make fancy, it is the right tool.  A good example where the UpdatePanel probably isn't the best answer to solve your problem is showing and hiding lots of divs.  Things that can easily be done through javascript should.  This saves lots of server request and makes it so you don't really need any ViewState because the work will be done on the client.

Sys.UI.Control

Sys.UI.Control inherits from Sys.UI.Component.  Just like any Windows application, a component in ASP.NET AJAX is a client side control that has no UI (like a timer) and a control has UI.  As I mentioned above, ASP.NET AJAX helps us push UI logic off of the server and onto the client.  Sys.UI.Control is a base type you can inherit from to build your control on.  It takes care of all the basics like with DOM element it's associated with for its UI and has methods like initialize and dispose.  Let's build a quick calculator control.  Hey, it wouldn't be a typical example if it were anything else but a calculator.  πŸ˜‰

Have ASP.NET AJAX 1.0 installed and create a new website using the ASP.NET AJAX-Enabled Web Site template and call it Calculator.

Visual Representation

All we should need for this is two textboxes, a dropdownlist, a button and a label to display the answer in.  Create a new Web User Control called Calculator.ascx.

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="Calculator.ascx.cs" Inherits="Calculator" EnableViewState="false" %>
Number 1:
<asp:TextBox ID="Number1TextBox" runat="server"></asp:TextBox><br />
Type: <asp:DropDownList ID="TypeDropDownList" runat="server"></asp:DropDownList><br />
Number 2: <asp:TextBox ID="Number2TextBox" runat="server"></asp:TextBox><br />
<
asp:Button ID="CalculateButton" runat="server" Text="Calculate" />
Total: <asp:Label ID="TotalLabel" runat="server" Font-Bold="true"></asp:Label>

@ Control Language="C#" AutoEventWireup="true" CodeFile="Calculator.ascx.cs" Inherits="Calculator" EnableViewState="false" %>
Number 1:
<asp:TextBox ID="Number1TextBox" runat="server"></asp:TextBox><br />
Type: <asp:DropDownList ID="TypeDropDownList" runat="server"></asp:DropDownList><br />
Number 2: <asp:TextBox ID="Number2TextBox" runat="server"></asp:TextBox><br />
<
asp:Button ID="CalculateButton" runat="server" Text="Calculate" />
Total: <asp:Label ID="TotalLabel" runat="server" Font-Bold="true"></asp:Label>

Definitely no prettying up there. Just the basics.  The calculator example may not be the best use of a custom control, but it keeps it simple so you learn what should be learned.

Client Code

First things first, we should register a namespace for our class that's going to represent the definition of our client representation of the calculator.  Create a new .js file called Calculator and add the following to it.

Type.registerNamespace("Porter.Erik");

"Porter.Erik");

Then we need to create and register the class itself.

 

blah

Web Service

blah

It's been a really interesting journey watching ASP.NET AJAX grow up.  I got to see very early builds of it (this was a few weeks before the first CTP) before I joined Microsoft when I was an ASP Insider and while the potential was there, it was still in its infancy and had a long way to go.  This was in October 2005.  Now it's all grown up and turned into a comprehensive client library, awesome integration with ASP.NET server controls and a Control Toolkit.  Things are only going to get better with full integration into Orcas.  Good times!

So far we're just using custom controls in basic places on Channel 9 like the wiki edit control, reply editor and a couple others.  We're currently using the UpdatePanel for our EntryList control because it was simple to get going.  Between the beta of Channel 9 and RTW, I will probably switch it over to being a custom control.  This will more than likely be a blog post on its own and will demonstrate some advanced techniques.

There's more code from our platform in the pipeline.  I'm not sure what to talk about next, but it may be our Virtual Path Provider setup or Templating scheme.