Archive for 2009

How to build a Date Dimension Table for OLAP

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 hard coded logic for my specific company’s fiscal year (April – March), adjust accordingly.

-- =============================================
-- 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

jQuery AssignEnterKey Plug-in

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" };
      var options = $.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…

Using jQuery Autocomplete Plugin with ASMX & XML

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 auto-complete 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 separated 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 browser specific parsers.

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

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 data source.

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

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

<?xml version="1.0" encoding="utf-8" ?>
<CUSTOMERS>
    <CUSTOMER>Albert</CUSTOMER>
    <CUSTOMER>Allen</CUSTOMER>
    <CUSTOMER>Brandy</CUSTOMER>
    <CUSTOMER>Brink</CUSTOMER>
    <CUSTOMER>Cathy</CUSTOMER>
    <CUSTOMER>Candy</CUSTOMER>
    <CUSTOMER>Crystal</CUSTOMER>
    <CUSTOMER>Deon</CUSTOMER>
    <CUSTOMER>Dirk</CUSTOMER>
    <CUSTOMER>Erie</CUSTOMER>
    <CUSTOMER>Eve</CUSTOMER>
    <CUSTOMER>Francis</CUSTOMER>
    <CUSTOMER>Gina</CUSTOMER>
    <CUSTOMER>Harry</CUSTOMER>
    <CUSTOMER>Ingrid</CUSTOMER>
    <CUSTOMER>Janice</CUSTOMER>
    <CUSTOMER>Kelly</CUSTOMER>
    <CUSTOMER>Kylee</CUSTOMER>
    <CUSTOMER>Nathan</CUSTOMER>
    <CUSTOMER>Zachary</CUSTOMER>
</CUSTOMERS>

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

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

Here is a complete ASP.NET 2.0 Web Application 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 auto-complete 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 solution. -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.

<!-- Added WebServices Section -->
<webServices>
	<protocols>
		<add name="HttpGet"/>
		<add name="HttpPost"/>
	</protocols>
</webServices>

jQuery REST Demo Application

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 web page 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 Application w/.NET 3.5 SP1 Framework)

  1. Add New Item – Web Service
  2. Un-comment “[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 Application.

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 traffic (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.

Merge and Combine PDF’s in C#

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 aggregating their PDF invoices into a single 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 &amp;lt; count; idx++)
			{
				PdfPage page = inputDocument.Pages[idx];
				customerPdf.AddPage(page);
			}
		}

	}