A-Z Index - Very Cool

Here is a very cool A-Z Index.

http://www.humboldt.edu/

How it was done:

http://interllectual.com/coffee/creating-an-a-z-index-with-textpattern

WeekDay part of a DateDiff SQL Statement

I've recently been working on a project where I will need to get the durration of a task using the start date, and the end date and the current date. In order to do this in "real" working time I've found this function to allow you to get the number of days between two dates, not including weekends.


CREATE FUNCTION [dbo].[DatePartWorkDays]
/***************************************************************************************
Purpose:
1. Given any valid start date and end date, this function will calculate and return
the number of workdays (Mon - Fri).
2. Given only a valid start date (end date has DEFAULT in it), this function will
return a 1 if the start date is a weekday and a 0 if not a weekday.
Usage:
1. MASTER.dbo.fn_WorkDays(@StartDate,@EndDate)
2. MASTER.dbo.fn_WorkDays(@StartDate,DEFAULT) --Always returns 1 or 0
3. MASTER.dbo.fn_WorkDays(@EndDate,@StartDate)
4. MASTER.dbo.fn_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
5. MASTER.dbo.fn_WorkDays(@EndDate,@EndDate) --Always returns 1 or 0
Notes:
1. Holidays are NOT considered.
2. Because of the way SQL Server calculates weeks and named days of the week, no
special consideration for the value of DATEFIRST is given. In other words, it
doesn't matter what DATEFIRST is set to for this function.
3. If the input dates are in the incorrect order, they will be reversed prior to any
calculations.
4. Only whole days are considered. Times are NOT used.
5. The number of workdays INCLUDES both dates
6. Inputs may be literal representations of dates, datetime datatypes, numbers that
represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
be implicitly converted to or already is a datetime datatype.
7. Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts
only whole weekends in any given date range.
8. This UDF does NOT create a tally table or sequence table to operate. Not only is
it set based, it is truly "tableless".
Error Indications:
1. If either the @StartDate or the @EndDate parameter is an invalid date, the
following error is returned...
"Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
2. If either the @StartDate or the @EndDate parameter is a string not resembling a
date, the following error is returned...
"Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string."
3. If only one parameter is passed, the following error is returned...
"Server: Msg 313, Level 16, State 2, Line 3
An insufficient number of arguments were supplied for the procedure or
function MASTER.dbo.fn_WorkDays."
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
***************************************************************************************/
--======================================================================================
-- Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDate DATETIME,
@EndDate DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)
--===== Define the output data type
RETURNS INT
AS
--======================================================================================
-- Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME
--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
RETURN NULL
--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
)
END

11 Tips for Time Management in a Web Worker World

Nice list for managing your time, directed at a Web Worker. If you’re like me, then some days when you have 1000 tasks to do and you get them all done, you have trouble figuring out exactly what you have accomplished. Here’s some tips for fixing that.

http://webworkerdaily.com/2006/12/11/11-tips-for-time-management-in-a-web-worker-world/

Form Authenticaiton with AD Backend

I’ve seen quite a few questions in forums about how to use Forms Authentication with an Active Directory back end.
While the authentication piece is fairly straight forward using active directory groups as the role management system for your application is a little trickier.
The basic authentication mechanism is an Active Directory helper class that can take a user name and password and validate that against AD.
....///

public bool IsAuthenticated(string domain, string username, string pwd)
{
string domainAndUsername = domain + @"\" + username;
DirectoryEntry entry = new DirectoryEntry(_path, domainAndUsername, pwd);
try
{
// Bind to the Active Directory Object
object obj = entry.NativeObject;
DirectorySearcher search = new DirectorySearcher(entry);
search.Filter = "(SAMAccountName=" + username + ")";
search.PropertiesToLoad.Add("cn");
SearchResult result = search.FindOne();
if (null == result)
{
return false;
}
// Update the new path to the user in the directory
_path = result.Path;
_filterAttribute = (string)result.Properties["cn"][0];

}
catch (Exception ex)
{

throw new Exception("Invalid username or password." + ex.Message.ToString());
}
return true;
}
///…

Call that from something like this.

if (true == ADAuth.IsAuthenticated(this.DomainName, userName, password))
{
status = true;
// Retrieve the user's groups
string groups = GetGroups(userName, password, true);

// Retrieve the user's first and last name;
string fname = ADAuth.GetFirstName(this.DomainName, userName, password);
string lname = ADAuth.GetLastName(this.DomainName, userName, password);


HttpContext.Current.Session["fname"] = fname;
HttpContext.Current.Session["lname"] = lname;
HttpContext.Current.Session["username"] = userName;
// Create the authetication ticket
// This is where we add a key to the authentication ticket so that we can get the groups out of the cache on the server.

FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1, userName, DateTime.Now, DateTime.Now.AddMinutes(20), false, userName + "Roles");

// Now encrypt the ticket.
string encryptedTicket = FormsAuthentication.Encrypt(authTicket);

// Create a cookie and add the encrypted ticket to the cookie as data.
HttpCookie authCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket);

// Add the cookie to the outgoing cookies collection.
HttpContext.Current.Response.Cookies.Add(authCookie);
}
}
catch (Exception ex)
{
//Should do some logging here.
throw new Exception(ex.Message);
}
return status;
}

Now in your Global.asax file, you need to generate a new idenity for the user so that we can use the AD groups to secure our application:

protected void Application_AuthenticateRequest(Object sender, EventArgs e)
{

// Extract the forms authentication cookie
string cookieName = FormsAuthentication.FormsCookieName;
HttpCookie authCookie = Context.Request.Cookies[cookieName];

if (null == authCookie)
{
// There is no authentication cookie.
return;
}
FormsAuthenticationTicket authTicket = null;
try
{
authTicket = FormsAuthentication.Decrypt(authCookie.Value);
}
catch (Exception ex)
{
// Log exception details (omitted for simplicity)
throw new Exception("Error Reading cookie");
}

if (null == authTicket)
{
// Cookie failed to decrypt.
return;
}
// When the ticket was created, the UserData property was assigned a
// pipe delimited string of group names.

String cacheGroups;
String[] groups = null;
if (Context.Cache.Get(authTicket.UserData) != null)
{
cacheGroups = (String)Context.Cache.Get(authTicket.UserData);
groups = cacheGroups.Split(new char[] { '' });
// Create an Identity object
GenericIdentity id = new GenericIdentity(authTicket.Name, "LdapAuthentication");

// This principal will flow throughout the request.
GenericPrincipal principal = new GenericPrincipal(id, groups);

// Attach the new principal object to the current HttpContext object
Context.User = principal;

}
else
{
// Response.Redirect(FormsAuthentication.LoginUrl);
}


}

Now you can set your Authenticaiton in web.config to Forms Authentication. You can also use the since your user has an identity with the roles from AD.

RDL to RDLC Conversion for ASP.NET

I wanted to share a tip that I found with using Business Intelligence Studio templates in Visual Studio 2005. We are currently working on a scaled down version of a dashboard to report on current enrollment numbers, target enrollment numbers and some other data relating to credit hours for each semester. This data is coming from Datatel via a nightly extract, and is stored in SQL where we manipulate it so we can break apart the multi-valued fields.

I was originally creating reports for the web application using Visual Studio 2005 and creating RDLC files. I am using RDLC files because we do not have SQL 2005 with Reporting Services installed anywhere, so we are currently unable to host the new reports on a Report Server. I found that after I installed SQL Server Express 2005 with Advance Features, and selected install client templates (or something like that) I had a new project type in Visual Studio for a Business Intelligence project. After some messing around I found that you can use the advanced features of the Business Intelligence studio to create your reports, but that you can easily convert them to the RDLC files that the report viewer control for ASP.NET looks for.

To do this all you have to do is create a new Business Intelligence Project in VS and either use the wizard to create the reports or create them manually. I’ve been using the wizard, as it cuts development time from a couple hours to a couple minutes. Once you have your report created you can save the file, locate the file on disk and rename it with the RDLC extension.

Next add existing item to your web application. Once you have the new RDLC file in your web application, create a dataset in your App_Code folder using the EXACT same SQL statement(s) that you used in the Business Intelligence Project. Open the RDLC file and rebind both the report data source as well as the datasource for any tables in the report to your new dataset.

Add a .aspx page to the application and drop a report viewer control on the page. Point the control at your RDLC and rebind the data sources. Once complete you will have your report which was originally designed for Report Server, available as a client report that ASP.NET pages will happily display.


Not sure if anyone else has found this out, but I wanted to bring it forward as it can help dramatically reduce development time when creating reports for web applications. Business Intelligence Studio is great as it provides a quick and easy wizard for creating fairly complex reports, and the best part is that the report viewer for asp.net has built in methods to export to Excel and PDF.


I’d be interested to hear if anyone else has other experiences working with RDL and RDLC files for asp.net.

Hello World

Console.WriteLine (

I'll be using this blog to post items releating to new emerging technologies from Microsoft as well as tips, tricks and general know how on .Net

I've already got a couple of things that I need to share so check back in a bit.

)