I use a lot of queries to drive reports and interactive search tools and the most common requirement of all my queries is optional parameters.  I’ve used a ton of different methods to pull this off in the past, but I primarily use one approach when I build stored procedures.

-- //////////////////////////////////////////
-- Stored Procedure - Optional Params (NEW WAY)
-- //////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	SELECT * FROM SALES
	WHERE (@OrderNumber IS NULL OR @OrderNumber = '' OR ORDERNUMBER = @OrderNumber)
		AND (@TrackingNumber IS NULL OR @TrackingNumber = '' OR TRACKINGNUMBER = @TrackingNumber)

-- //////////////////////////////////////////////////
-- Stored Procedure - Optional Params - Dynamic SQL (OLD WAY)
-- //////////////////////////////////////////////////
CREATE PROCEDURE GetOrders
	@OrderNumber VARCHAR(10),
	@TrackingNumber VARCHAR(10)
AS
	DECLARE @STRSQL VARCHAR(200)
	SET @STRSQL = 'SELECT * FROM SALES WHERE 1=1 '

	IF @ROBINCODE <> ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND ORDERNUMBER = ''' + @OrderNumber + ''''
	END

	IF @DELTOCODE <> ''
	BEGIN
		SET @STRSQL = @STRSQL + ' AND TRACKINGNUMBER = ''' + @TrackingNumber + ''''
	END

EXECUTE(@STRSQL)

As you can see, the “New Way” has a lot less code and does not use dynamic sql to generate new SQL statements on the fly.  In addition to checking for NULL parameter values, I also check for empty strings.  This is a must, since many of the tools pass empty string when a value is not selected.

HTTrack website copier is awesome and FREE!  I’ve been using it for a few months and I’ve already saved myself and my team members hundreds of hours by downloading full working copies of various websites locally.

Here are a few examples of websites I download.

1. A client was having problems with their hosting company who refused to give them access to their source code (developed by a 3rd party) to their CF website.  After weeks of trying to solve the problem, they decided to switch hosting companies.  During their migration to a hosting company and a new CMS solution,  they wanted to keep a copy of the old site online.  Regardless of the engine running the website, HTTrack was able to download a working copy of the site to HTML, minus the dynamic features (Search, CMS Admin, etc…) in a few minutes.
2. We inherited a old web server at working running Plone 2.0.4.  The machine was running on old hardware that was failing (2/3 dead HDD, broken RAID 1) and Zope was randomly crashing.  After spending 3 days trying to get the machine stable, I decide to convert the site to HTML and put on one of our newer “stable” web servers.  The process was a little mess, since our plone instance pointed to folders paths vs. actual files (/index_html vs /index.hmtl) the final static site had a lot of weird and randomly directed links!  The final static HTML site probably took ~8 solid hours of HTML clean-up, but there was also a lot of bad/obsolete content in the existing portal that made this process painful!
3. I have a few websites I like to use as samples/demo that show better as interactive!  Using HTTrack and controlling the depth of link following/downloading (like first page only), I can download an offline working copy of a sites main page!  This is a great way to demo a feature/capability/competitor website…

A few other good things to know, a couple sites I’ve downloaded require you to login first.  This isn’t an issue with HTTrack, since you can build your own cookies to apply to your capture.  It was a bit tricky getting the cookie format correct (Used Netscape Cookie Format), but after a few minutes I was able to take the cookie data from IE/FireFox and have a perfect working cookie for HTTrack to use!

Once again, this is FREE utility that is handy to have around!

HTTrack WEBSITE COPIER

I’ve been using SubSonic’s built-in RESTHandler for some basic AJAX features in my portal at work.  This is a really nice extra feature, and provides a quick way to pull xml/json/rss from your DAL.  I noticed that a few features based on this handler had stopped working a few weeks ago.  I found that SubSonic’s RESTHandler had no way to pass in the provider name as part of the url.  I currently have 5 different providers (databases) setup for SubSonic, so I needed a way to point to the various providers when calling a URL.

Edit the following file “SubSonic/HttpHandlers/RESTfullUrl.cs”

1. Add new property to access “ProviderName”

private string _providerName = String.Empty;
public string ProviderName
{
get { return _providerName; }
set { _providerName = value; }
}

2. Modify ParseURL()

private void ParseUrl()
{
//work backwards up the URL
//first thing, check for a query string and strip it
string workingUrl = _rawUrl;

if(workingUrl.Contains("?"))
{
//strip off the query string - it'll be used later
workingUrl = Strings.Chop(workingUrl, "?");

}

string[] bits = workingUrl.Split(new[] {'/'}, StringSplitOptions.RemoveEmptyEntries);
//the URL should be split out into
//protocol ("http");
//domain ("foo.com");
//service directory ("services");
//provider name ("northwinds")
//table or sp ("products")
//command.format ("list.xml")

//the index of each item within the URL
int commandIndex = bits.Length - 1;
int tableSPIndex = bits.Length - 2;
int providerIndex = bits.Length - 3;

//now check to see if the last item on the URL is a key, or
if(IsNonKeyCommand(workingUrl))
{
//the last item is the command - list/show/etc
//set it
//the last item is a key, set it, and then the command
_key = Path.GetFileNameWithoutExtension(bits[bits.Length - 1]);
commandIndex--;
tableSPIndex--;
providerIndex--;
}

//the command is the next item up the chain
_restCommand = DecideCommand(Path.GetFileNameWithoutExtension(bits[commandIndex]));

//evaluate the command
if(_restCommand == RESTCommand.Exec)
_spName = bits[tableSPIndex];
else
_tableName = bits[tableSPIndex];

_providerName = bits[providerIndex];

ParseQueryString();
}

Edit the following file “SubSonic/HttpHandlers/RESTHandler.cs”

3. Update GenerateReturnSet(),  line #286.

//before
q = new Query(_url.TableName);
//after
q = new Query(_url.TableName,_url.ProviderName);

Here is an example using the new syntax that supports provider name.

http://localhost/services/northwinds/customers/list.xml

OLAP Date Dimension Table Script

This query will build a table called “OLAP_DATE_DIMENSION”, based upon a starting and ending date. As you can see all of the work is done by the “DATENAME” and “DATEPART” functions in MS SQL Server. If you want information on using these, Query Analyzer for MS SQL Server 2000 is great! I’m not sure what happened with MS, but post MS SQL Server the built in help documents suck and your better of using google.

** This query has a little hardcoded logic for my specific company’s fiscal year (April – March), adjust acordingly.

- Zach Hunter

-- =============================================
-- Build Dimension Date Table
-- =============================================
DECLARE @StartDate as smalldatetime, @EndDate as smalldatetime

SET @StartDate = '04/01/2010'
SET @EndDate = '03/31/2011'

BEGIN
SELECT
DATEPART(dy, @StartDate) as DAY_OF_YEAR,
CASE
WHEN DATENAME(qq,@StartDate)-1=0 THEN
4
ELSE
DATENAME(qq,@StartDate)-1
END AS FISCAL_PERIOD,
DATENAME(m,@StartDate) AS MONTH_DESC,
DATEPART(m,@StartDate) AS MONTH_NUM,
DATEPART(qq,@StartDate) AS QUARTER_NUM,
CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
DATEPART(yy,@StartDate) AS YEAR_NUM,
DATEPART(d,@StartDate) AS DAY_OF_MONTH,
CASE
WHEN DATEPART(m,@StartDate)< 4 THEN
DATENAME(yy,@StartDate)-1
ELSE
DATENAME(yy,@StartDate)
END AS FISCAL_YEAR,
CASE
WHEN DATEPART(m,@StartDate)>3 THEN
DATEPART(m,@StartDate)-3
ELSE
12-(3-DATEPART(m,@StartDate))
END AS FISCAL_MONTH
INTO OLAP_DATE_DIMENSION

SELECT @StartDate = @StartDate + 1

END

WHILE (@StartDate <= @EndDate)
    BEGIN
     BEGIN
     INSERT INTO OLAP_DATE_DIMENSION SELECT
     DATEPART(dy, @StartDate) as DAY_OF_YEAR,
     CASE
     WHEN DATENAME(qq,@StartDate)-1=0 THEN
     4
     ELSE
     DATENAME(qq,@StartDate)-1
     END AS FISCAL_PERIOD,
     DATENAME(m,@StartDate) AS MONTH_DESC,
     DATEPART(m,@StartDate) AS MONTH_NUM,
     DATEPART(qq,@StartDate) AS QUARTER_NUM,
         CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
     REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
     DATEPART(yy,@StartDate) AS YEAR_NUM,
     DATEPART(d,@StartDate) AS DAY_OF_MONTH,
     CASE
     WHEN DATEPART(m,@StartDate)< 4 THEN
     DATENAME(yy,@StartDate)-1
     ELSE
     DATENAME(yy,@StartDate)
     END AS FISCAL_YEAR,
     CASE
     WHEN DATEPART(m,@StartDate)>3 THEN
     DATEPART(m,@StartDate)-3
     ELSE
     12-(3-DATEPART(m,@StartDate))
     END AS FISCAL_MONTH
     END
     SELECT @StartDate = @StartDate + 1
    END

A few days ago, I was trying to figure out what to do about a user control that had multiple fieldsets that needed different default buttons triggered when the enter key was pressed on the page…  Too bad the first button on the page is the default, by default! Since there was ~4 different forms that each had their own button, I came up with a hard-coded solution that solved the problem. Somebody suggested I make a jQuery Plug-In, so I tweaked the code a bit more and came up with the following plug-in below (I don’t think this qualifies as anything amazing, but it for the simpleton like me, it has a place).

A few examples on how to set the default to a field/form/etc…

1. You have 2+ fieldsets with their own button to perform an action: $(“FIELDSET”).AssignEnterKey();

2. A div called Search that provides site wide search: $(“#Search”).AssignEnterKey();

3. Multiple look ups on one page that have the class name Lookup: $(“.Lookup”).AssignEnterKey();

/*
* jQuery AssignEnterKey Plug-in *
* Summary This plug-in will attach a click/blur event to all input elements
 inside a parent container, the events will add and remove a class to the
 parent container that is used to associate the enter key with the a
 button or submit button.

* * Notes Using the options, you can target a different controls for both
 triggering the focus and the target to be executed.
* * Example $("FIELDSET").AssignEnterKey();
* * Developed by Zachary.Hunter@gmail.com * Date 2009-05-29 23:05 

*/

(function($) {
   $.fn.AssignEnterKey = function(options) {
      var defaults = {
         triggerExp : ":input", targetExp : ":submit,:button" }; varoptions = $.extend(defaults, options); $(document).keypress(function(e) {
         if ((e.which && e.which == 13) || (e.keyCode && e.keyCode == 13)) {
            $(".EnterKeyAssigned").find(options.targetExp) .click(); return false; }
         }
      ); return this.each(function() {
         var obj = $(this); obj.find(options.triggerExp) .click(function() {
            obj.addClass("EnterKeyAssigned"); }
         ).blur(function() {
            obj.removeClass("EnterKeyAssigned"); }
         ); }
      ); }; }
)(jQuery);

I think this came out much more usable than my original code. I setup two parameters you can over-ride so it can look for specific trigger or target controls.

Overall, this is very basic plug-in and I can already see a few things that should be addressed (What to do when nothing is selected?  What to do when you have multiple buttons in a container?).  I think I’ll add a few more defaults options to address these.  I’m very happy with what I got, since this was my first attempt at developing a jQuery plug-in…

Feedback is always appreciated!!!

[EDIT 5/19] by Zach : I have submitted this to the jQuery website as a plug-in. Hopefully somebody else finds this useful…

I’m really surprised that the jQuery auto complete plug-in does not support XML by default. Almost all web services have the option to transmit XML, that it should be a requirement that every Ajax plug-in or widget consumes XML. If your using ASP.NET 2.0 and you want to use the jQuery autocomplete plug-in, you’ll notice this isn’t an easy task.  Since the default plug-in requires a flat file with a list of values seperated by new row “\r”.  To support this natively, you can use a ASHX handler, but I wanted to use my existing web services.  To get around the problem, I created a “parseXML” function and added an if statement in the $ajax results function.

The change really came down to adding an if statement for dataType = “xml” and added a new property datakey to support the parsing of the xml file.  This way, I can parse ASMX 2.0 Web Service calls or static XML files.

[UPDATE] There is a catch to “STATIC” xml data, the default plug-in is based on the assumption that your results (aka: XML data) will only contain filtered results.  If your data contains all possible results, you’ll need to filter the data… See comments for instructions!  The XML parsing is done using jQuery built-in XML parser, so you don’t have to worry about dealing with broswer specific parsers.

Here is the modified request function and the new parseXML fuction that I added to the “jQuery.autocomplete.js” plugin:

        function request(term, success, failure) {
            if (!options.matchCase)
                term = term.toLowerCase();
            var data = cache.load(term);
            // recieve the cached data
            if (data && data.length) {
                success(term, data);
                // if an AJAX url has been supplied, try loading the data now
            } else if ((typeof options.url == "string") && (options.url.length > 0)) {

                var extraParams = {
                    timestamp: +new Date()
                };
                $.each(options.extraParams, function(key, param) {
                    extraParams[key] = typeof param == "function" ? param() : param;
                });

                $.ajax({
                    // try to leverage ajaxQueue plugin to abort previous requests
                    mode: "abort",
                    // limit abortion to this input
                    // port: "autocomplete" + input.name,
                    dataType: options.dataType,
                    url: options.url,
                    data: $.extend({
                        q: lastWord(term),
                        limit: options.max
                    }, extraParams),
                    success: function(data) {
                        var parsed = [];

                        // Added Logic by Zach
                        // If dataType = "XML" use the parseXML fuction
                        if (options.dataType == "xml") {
                            parsed = parseXML(data);
                        }
                        else {
                            parsed = options.parse && options.parse(data) || parse(data);
                        }
                        cache.add(term, parsed);
                        success(term, parsed);
                    }
                });
            } else {
                // if we have a failure, we need to empty the list -- this prevents the the [TAB] key from selecting the last successful match
                select.emptyList();
                failure(term);
            }
        };

        // Added Logic by Zach
        // Added XML Parse Function
        function parseXML(xml) {
            var parsed = [];

            $(xml).find(options.datakey).each(function() {
                parsed[parsed.length] = {
                    data: [$(this).text()],
                    value: $(this).text(),
                    result: options.formatResult && options.formatResult($(this).text(), [$(this).text()]) || [$(this).text()]
                };
            });
            return parsed;
        }

In order to use this new functionality, you’ll need to add the following options when you bind to a XML datasource.

    $(document).ready(function() {
        $("#customerCode").autocomplete("Customers.xml", { dataType: "xml", datakey: "customer" });
    });

Here is the sample XML file used in the auto complete instance above.

    Amy
    Brian
    Charlie
    Zach
    Zachary

The only requirement for your ASMX web service, is your method signature must accept a string paramters called “q”.  You can add additional parameters on your method, by default the autocomplete sends (q,limit,timestamp) but only “q” is required.

Here is a link to the offical jQuery autocomplete plug-in.

Here is a complete ASP.NET 2.0 Web Applicaiton sample with the modified auto complete plug-in jQueryAutocompleteASMX.zip

[EDIT 5/14] by Zach :

        $(document).ready(function() {
            $("#textboxCustomer").autocomplete("/Autocomplete.asmx/GetCustomers", {
                parse: function(data) {
                    var parsed = [];

                    $(data).find("string").each(function() {
                        parsed[parsed.length] = {
                            data: [$(this).text()],
                            value: $(this).text(),
                            result: [$(this).text()]
                        };
                    });
                    return parsed;
                },
                dataType: "xml", datakey: "string", max: 5
            });
        });

Using the function above, you can replace the built-in autocomplete parse function with a dynamic parser.  The only requirement is returning string array, which is the same object returned by the default function.  I think the original example is useful if you are using the jQuery plug-in with ASP.NET and .NET Web Services, but if I was only going to use the plug-in a few times… I’d use this soltuion.  -Zach

[EDIT 5/19] by Zach:
Don’t forget the following lines should be added to your web.config under “<system.web>” to support GET & POST requests for your web services.

Over the past few weeks I’ve been trying to learn how WCF can be used for REST.  I found lots of information, but I also found lots of broken and incomplete demonstrations.  Being new to WCF/REST, I really needed a working demo to see what I was doing wrong in my setup.  After using the mighty Google for a few days and reading a ton of blog posts, I was able to piece together a working solution.  I then played for a few days to make sure I could consistently reproduce working code and decided to build a working sample to show how to use WCF/ASMX services with ASP.NET 3.5 SP1 and jQuery.  This demo was designed to give people the basic instructions on what to setup to be able to send/receiving data from the browser using jQuery with the server.

Demo 1-4 shows you the ASMX and the WCF equivalents for doing the basics (Get String, Get Array, Send String, Send Array), and Demo 5 shows you how to use a WCF Data Contract with a Wrapped/Bare message format.  To make things easier to see, I log all JSON requests/results to the webpage for inspection.  This also allows you to see how requests are non-blocking and can be received out of order depending on processing time.

Demo Screen Shot

jQuery & JSON using ASP.NET

Download Demo Applicaiton

How to add a ASMX Web Service (ASP.NET Web Applicaiton w/.NET 3.5 SP1 Framework)

1. Add New Item – Web Service
2. Uncomment “[ScriptService]“

That’s it, your done.  To access your web service via jQuery, use the following syntax.

    $.ajax({
        type: "POST",
        url: "WebService1.ASMX/HelloWorld",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        data: "{}",
        success: function(res) {
        // Do your work here.
        // Remember, the results for a ASMX Web Service are wrapped
        // within the key "d" by default. e.g. {"d" : "Hello World"}
        }
    });

How to add a WCF Service (ASP.NET Web Applicaiton w/.NET 3.5 SP1 Framework)

1. Add New Item – WCF Service
2. Right-click on the service and choose “View Markup”.
3. Add the following line to the ServiceHost declaration: Factory=”System.ServiceModel.Activation.WebServiceHostFactory”
4. Add a reference to the project for “System.ServiceModel.Web”.
5. Open the interface associated with the service (e.g. IService1.cs) and add the following using statement “using System.ServiceModel.Web;”.
6. Inside the interface, set the attributes on the method to support JSON & POST or GET request types.

7. [OperationContract]
   [WebInvoke( Method = "POST", ResponseFormat = WebMessageFormat.Json)]
   

8. Open the web.config and comment out the following lines:

  <!--<behaviors>
   <endpointBehaviors>
    <behavior name="Web.Service1AspNetAjaxBehavior">
     <enableWebScript />
    </behavior>
   </endpointBehaviors>
  </behaviors>
  <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
  <services>
   <service name="Web.Service1">
    <endpoint address="" behaviorConfiguration="Web.Service1AspNetAjaxBehavior"
     binding="webHttpBinding" contract="Web.Service1" />
   </service>
  </services>-->

That’s it, your done.  To access your web service via jQuery, use the following syntax.

    $.ajax({
        type: "POST",
        url: "Service1.svc/DoWork",
        contentType: "application/json",
        dataType: "json",
        success: function(res) {
        // Do you work here.
        }
    });

I found a lot of blog posts pointing to WCF Service Factory projects, which require a completely different setup.  The sample above is for adding a WCF Service to a Web Applicaiton.

While debugging this, I used Firebug for Firefox and Fiddler to see the ajax & JSON data fired between the client and server.  When you use fiddler to debug a local website, you need to add a “.” after the domain name to cause fiddler to catch the traffice (e.g. http://localhost.:1234/WebSite1).  Hopefully the demo compiles and runs without problems, so you can see a quick 1-2-3 on how to get WCF up and running quickly!

I’m by far no expert on these technologies, so if anybody has suggestions on what I could be to make this demo better… Let me know!  Hopefully seeing a working demo will help somebody else get a jump start on using jQuery to consume REST services.

I was working on a email invoicing application a few days ago, and I needed a way to consolidate multiple PDF invoices (approximately ~50-200 invoice p/week).  I wanted something that was free and worked with C#, I found PdfSharp and after looking at the demo’s I came up with a quick solution.  The process is really simple and can be summarized as (Create a new empty PDF, open each existing PDF and copy their pages into the new PDF).  Here is a code snippet of how I combined the PDF files, using a DataReader that has the source URL of each invoice:

	// ********************************************************************************************* //
	// This is just a simplified excerpt, I was looping over all the invoices from the previous week
	// by customer, and aggergrating their PDF invoices into a signle document.  In addition to the
	// generating the PDF, I used the DataReader to also generate a CSV file of their invoice details.
	// ********************************************************************************************* //

	// Combined PDF
	var customerPdf = new PdfDocument();

	// Hold copy of last document
	string lastDocumentUrl = string.Empty;

	while (dr.Read())
	{
		// Get current document
		string documentUrl = dr["InvoiceURL"].ToString();

		// Check if last document added is the same as current document
		if (lastDocumentUrl != documentUrl)
		{
			// Set the last document equal to the current document
			lastDocumentUrl = documentUrl;

			// Get the current file (e.g. \\\\\.pdf)
			string filePath = documentUrl;

			// Read in the existing document
			PdfDocument inputDocument = PdfReader.Open(filePath, PdfDocumentOpenMode.Import);

			// Get # of pages
			var count = inputDocument.PageCount;

			// Add each page to "Combined PDF"
			for (int idx = 0; idx < count; idx++)
			{
				PdfPage page = inputDocument.Pages[idx];
				customerPdf.AddPage(page);
			}
		}

	}

I love to dabble in new things, about 3 months ago I started helping a friend fix up their Cold Fusion 7 website.  I noticed a bunch of things I consider important missing from the site and was curious about the user demographics/stats (who are they, what browser are they using, what is the max resolution we can use on the site, etc…).  After talking a bit, I decided to implement the following items to gather the data:

1. Implement AWSTAT to monitor the website logs.
2. Implement a custom internal stat tracker to gather info about users.

Here are the fields we required:

1. User Name
2. Browser Type & Version (e.g. IE5, IE6, IE7, FF3, Chrome ???)
3. Operating System  (e.g. Linux, Max, Windows)
4. Default Language Supported (e.g. en, jp ) 
5. Screen Resolution (e.g. monitor resolution 1024×768, 1280×1024, or 800×600)
6. Screen Colors (e.g. 16bit, 24bit, 32bit)
7. IP Address
8. URL (with only the first query string parameter, since he uses FuseBox)
9. Date
10. Time

Some of the variables in our list are not available in Cold Fusion, they are browser based data that the DOM has available via JavaScript.  To get the data to the server I created a a page called “count.cfm” to accept a query string parameters with the data from the client.  This is just JS 101, as far as I know there is no other solution to getting client side information about the user to the server.  There are a lot more stats you can obtain from the client, to find them look at the JavaScript “screen” and “navigator” objects .  I started to stub out a little extra in my code below (e.g. Navigator.javaEnabled ), if you want to use this data you’ll need to add the logic to add the data in a GET request to “count.cfm”.

    var file = '/fusebox/count.cfm';
    w = screen.width;
    h = screen.height;
    v = navigator.appName;
    j = navigator.javaEnabled();

    if (v != 'Netscape') {
        c = screen.colorDepth;
    }
    else {
        c = screen.pixelDepth;
    }

    info = 'res=' + w + 'x' + h + '&js=' + j + '&col=' + c;

    document.write('');

After the data is received in the “count.cfm” page, I normalize the data by replace some of the data with foreign keys.  This keeps the stat log normalized and decreases the table size.

function LogStats()
{
if(isDefined("session.userid"))
{
    UserId = session.userid;
    UserName = session.username;
    AuthorityLevel = session.authoritylevel;
}
else
{
    UserName = "Anonymous";
    UserId = "";
    AuthorityLevel = "";
}

OS = getOs(CGI.HTTP_USER_AGENT);
BrowserType = getBrowser(CGI.HTTP_USER_AGENT);

if(isDefined("CGI.HTTP_ACCEPT_LANGUAGE"))
{
    Language = CGI.HTTP_ACCEPT_LANGUAGE;

    if(Language.IndexOf(";") GT 0)
    {
        Language = Mid(Source, 1, Source.IndexOf(";"));
    }
}
else
{
    Language = "(Unknown)";
}

IP = CGI.REMOTE_ADDR;
LogDate = DateFormat(Now(),"mm/dd/yyyy");
LogTime = TimeFormat(Now(), "hh:mm");

if(isDefined("CGI.HTTP_REFERER"))
{
    Source = CGI.HTTP_REFERER;

    if(Source.IndexOf("&") GT 0)
    {
        Source = Mid(Source, 1, Source.IndexOf("&"));
    }
}
else
{
    Source = "(Home Page)";
}

if(isDefined("CGI.HTTP_REFERER"))
{
    Referrer = CGI.HTTP_REFERER;
}
else
{
    Referrer = "";
}

if(isDefined("url.res"))
{
    Resolution = url.res;
}
else
{
    Resolution = "(Unknown)";
}

if(isDefined("url.js"))
{
    JavaScriptEnabled = url.js;
}
else
{
    JavaScriptEnabled = "(Unknown)";
}

if(isDefined("url.col"))
{
    Colors = url.col;
}
else
{
    Colors = "(Unknown)";
}

IdOs = GetIdOS(OS);
IdColors = GetIdColors(Colors);
IdRes = GetIdRes(Resolution);
IdBrowser = GetIdBrowser(BrowserType);
IdLang = GetIdLang(Language);
IdPath =  GetIdPath(Source);

Insert = "SET NOCOUNT ON;
          INSERT INTO tbl_Stats_Stats
          (username, [date], [time], ip, osid, colorid, browserid, resid, pathid,languageid)
          VALUES
          ('#UserName#', '#LogDate#', '#LogTime#', '#IP#', #IdOs#, #IdColors#, #IdBrowser#, #IdRes#, #IdPath#, #IdLang#);
          SELECT @@identity as LanguageId from tbl_stats_Languages";
InsertResults = cfquery(dsn="",sqlstring=Insert);
return InsertResults.LanguageId;
}

This probably took about 2 hours to throw together.  I’m sure there is more than can be done to optimize the code but this way my first time programming with Cold Fusion.  Additionally, since my script is targeted for an intranet application, I didn’t implement parameter validation which is a must for external websites!  The largest amount of work was building  the logic to parse the USER_AGENT so I could determine the browser and OS.  These are very important, since our goal was to determine how we could redesign without negatively impacting the users.

This was deployed by putting the JS into the footer template (footer.cfm).  The database script is included in the source code download, along with a data reset script.

Hopefully the logic or code can help you solve your problem.

Cold Fusion Web User Statistics

This logic can be used to give you a SINGLE SIGN-ON / AUTOLOGIN solution with ASP.NET 2.0 or higher using a combination of authentication providers on IIS 5.1 (Windows 2000).  The demo is using C# and does require a network administrator account to make the magic work.  I’ve tried using other accounts, but was only able to get this to work with a “Domain Administrator” account.

I tried so many solutions (applicaiton sub folder using Windows auth, web service, every possible IIS 5.1 settings, WMI query) and nothing worked.  After trying to figure this out for a few weeks, I realized that I had a single sign-on solution being used for our SonicWall content filtering system appliances.  I looked into how this was being done and I decided to put the same logic in the page_load event of my login control.

After an hour of playing around, I had a working ASP.NET single sign-on solution.  Using impersonation, I create a thread that uses a domain administrator account (this is a requirement, which is also required by the SonicWall solution) to perform a remote call to the client workstation to obtain the currently logged on user(s).  The magic is done by using “netapi32.dll”, by passing the NetWkstaUserEnum method a hostname your able to obtain an array of users logged into the computer.

protected void Page_Load(object sender, EventArgs e)
{
     if (!IsPostBack) { // Attempt Autologin
         string account = String.Empty;
         string domain = String.Empty;
         string user = String.Empty;
         string email = String.Empty;

         try
         {

             ImpersonateUser iu = new ImpersonateUser();
             if (iu.impersonateValidUser("", "", ""))
             {

                 NetWorkstationUserEnum nws = new NetWorkstationUserEnum();
                 string host = nws.DNSLookup(Request.UserHostAddress);

                 string[] users = nws.ScanHost(host);

                 if (nws.ScanHost(host).Length > 0)
                 {
                     account = users[users.Length - 1];
                     domain = account.Substring(0, account.IndexOf("\\"));
                     user = account.Substring(account.IndexOf("\\") + 1, account.Length - account.IndexOf("\\") - 1);
                 }

                 iu.undoImpersonation();
             }
         }
         catch (Exception logex)
         {
             Log.Debug("Autologin Failure: " + logex.Message);
         }

         if (!String.IsNullOrEmpty(account))
         {
             Log.Info("Account: " + account);

             if (domain.ToUpper() == "")
             {
                 email = user.ToLower() + "@";
                 String strRole = AssignRoles(email);

                 if (!String.IsNullOrEmpty(strRole))
                 {
                     FormsAuthentication.Initialize();

                     //The AddMinutes determines how long the user will be logged in after leaving
                     //the site if he doesn't log off.
                     FormsAuthenticationTicket fat = new FormsAuthenticationTicket(1,
                         email, DateTime.Now,
                         DateTime.Now.AddDays(7), true, strRole,
                         FormsAuthentication.FormsCookiePath);

                     HttpCookie ck;
                     ck = new HttpCookie(FormsAuthentication.FormsCookieName, FormsAuthentication.Encrypt(fat));
                     ck.Expires = fat.Expiration;
                     ck.Path = FormsAuthentication.FormsCookiePath;

                     Response.Cookies.Add(ck);
                     Response.Redirect(FormsAuthentication.GetRedirectUrl(email, true));
                 }
                 else
                 {
                     Log.Info("Unable to create FAT, user does not have any roles assigned.");
                 }
             }
         }

     }
}

I always take the last user in the list, since the machine account “domain\machinename$” is always first.  I’ve not had it return the wrong person yet.  Even if a user is not automatically logged in, they can still manually enter their login/password to access the site.

** On my development machine, when I checked the array results I would see a bunch of accounts that were generated each time I ran VS.  Each time I ran my demo applicaiton, I’d see a different account like (ASPNET) as the last user.  I think this has something to do with the thread VS users for the built-in webserver.

Too keep my original Forms Authentication solution in place, I parse the account (e.g. DOMAIN\User => User) and I check the Forms authentication database for the user’s name, skipping the password check.  If they exist, I creates a FormsAuthenticationTicket and the process is complete.

** This code is part of a C#, ASP.NET Website Project.  I’ve tested this code on a few websites all using C# and this does work.  The cavaet of requiring a high-security account is a pain but it works.  If anybody knows of another way to pull this off, let me know…

By default, the login control is only displayed when a user is not authenticated.  This is becaus the auto login logic is hooked onto the modules load event. 

This solution is currently working on Windows 2000 with IIS 5.1, for a portal developed with ASP.NET 2.0 and C#.  The portal uses forms authentication but existing on an internal domain.  The site has a a few hundred users, some are remote agents that connect via VPN and are not part of the domain.

This has made a huge difference, since we now set the company default webpage to the site and key that they’ll be able to see everything since we’ve automatically logged them in.  The first day I made this change, I got 3 emails about the some major changes users saw done to the site.  I found this comment really funny, since we didn’t make any changes to the content… it just so happened that this was the first time they had ever logged in!

Good References for the soltuion:

http://www.codeproject.com/KB/IP/LoggedOnUsersPart2.aspx (had a good note about security)

Other Notes:

I found another soltuion saying that a WMI Query should be able to obtain the same data, but when I tried this using impersontation it did not work.  I read that WMI queries, even when run via Impersation, are limited by the ASPNET WP thread.  I can’t explan the details but I did find two posts about this and took them at face value. 

You can download the C# ASP.NET code here.  This is an excerpt from my project but it’s working code.  If anybody else has a working soltuion that does not require a domain admin acount, let me know.  Having a Single Sign-On (“SSO”) solution for an ASP.NET intranet site is awesome!

FormsWindowsMixxed_Source.zip