Mini-Tweek: customErrors in ASP.NET

Bill Bosacker — Sun, 05 Jul 2009 02:38:00 GMT

As of the latest .NET Service Pack (3.5 SP1, 3.0 SP2, 2.0 SP2) there is a new attribute for the <customErrors> element of your web.config, redirectMode . It has 2 settings, ResponseRedirect (default, legacy functionality), and ResponseRewrite (new...(read more)

ASP.NET Tweeks (Part 1): PageBase

Bill Bosacker — Thu, 02 Jul 2009 19:30:00 GMT

Modularity is the heart of any application. This is the first of a multi-part part series of Tweeks that you can use while writing a modular ASP.NET application. The first module on the list is the use of a PageBase object. The PageBase class derives...(read more)

.NET Tweek (June 24, 2009)

Bill Bosacker — Wed, 24 Jun 2009 07:00:00 GMT

How To: Extract ASP.NET Membership profile properties in SQL Server 2008 T-SQL

Man, that title is a mouthful, but it works. There are other posts on this that work with all versions of SQL Server, but this is my take with a bonus. I have created a second function for the extraction of binary values, for those that need them. This code uses some new functionalities of SQL Server 2008 and will not work as written on previous versions of SQL Server. Also, all of the other posts out there are not 100% compiant with the format. They don't convert a value with a length of -1 to a NULL. Here is the script to return strings:

/****** Object: UserDefinedFunction [dbo].[fn_GetProfileStringElement] ******/
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_GetProfileStringElement]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn_GetProfileStringElement];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER OFF;
GO
/******************************************************************************
** Schema: dbo
** Name: fn_GetProfileStringElement
** Desc:
**
** Auth: William Bosacker
** Date: 06/22/2009
**
** Parameters:
** ---------------------------------------------------------------------------
**
**
** Returns:
** ---------------------------------------------------------------------------
**
******************************************************************************/

CREATE FUNCTION [dbo].[fn_GetProfileStringElement] (
	@PropertyName nvarchar(256),
	@PropertyNames nvarchar(max),
	@PropertyValuesString nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @SearchString nvarchar(259);
	DECLARE @LeftPointer bigint;
	DECLARE @RightPointer bigint;
	DECLARE @ValueStart bigint;
	DECLARE @ValueLength bigint;


	IF @PropertyName IS NULL OR LEN(@PropertyName) = 0 
		OR @PropertyNames IS NULL OR LEN(@PropertyNames) = 0 
		OR @PropertyValuesString IS NULL OR LEN(@PropertyValuesString) = 0 
	BEGIN
		RETURN NULL;
	END;


	SET @SearchString = @PropertyName + ':S:';
	SET @LeftPointer = CHARINDEX(@SearchString, @PropertyNames, 0);

	IF @LeftPointer != 0
	BEGIN
		-- Find the starting position of the value.
		SET @LeftPointer = @LeftPointer + LEN(@SearchString);
		SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
		SET @ValueStart = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);

		-- Find the length of the value.
		SET @LeftPointer = @RightPointer + 1;
		SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
		SET @ValueLength = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);

		IF @ValueLength = -1
		BEGIN
			RETURN NULL;
		END;

		RETURN SUBSTRING(@PropertyValuesString, @ValueStart + 1, @ValueLength);
	END;


	RETURN NULL;
END;
GO

Here is the script to return binary values:

/****** Object: UserDefinedFunction [dbo].[fn_GetProfileBinaryElement] ******/
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_GetProfileBinaryElement]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[fn_GetProfileBinaryElement];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER OFF;
GO
/******************************************************************************
** Schema: dbo
** Name: fn_GetProfileBinaryElement
** Desc:
**
** Auth: William Bosacker
** Date: 06/22/2009
**
** Parameters:
** ---------------------------------------------------------------------------
**
**
** Returns:
** ---------------------------------------------------------------------------
**
******************************************************************************/

CREATE FUNCTION [dbo].[fn_GetProfileBinaryElement] (
	@PropertyName nvarchar(256),
	@PropertyNames nvarchar(max),
	@PropertyValuesBinary varbinary(max)
)
RETURNS varbinary(max)
AS
BEGIN
	DECLARE @SearchString nvarchar(259);
	DECLARE @LeftPointer bigint;
	DECLARE @RightPointer bigint;
	DECLARE @ValueStart bigint;
	DECLARE @ValueLength bigint;


	IF @PropertyName IS NULL OR LEN(@PropertyName) = 0 
		OR @PropertyNames IS NULL OR LEN(@PropertyNames) = 0 
		OR @PropertyValuesBinary IS NULL OR LEN(@PropertyValuesBinary) = 0 
	BEGIN
		RETURN NULL;
	END;


	SET @SearchString = @PropertyName + ':B:';
	SET @LeftPointer = CHARINDEX(@SearchString, @PropertyNames, 0);

	IF @LeftPointer != 0
	BEGIN
		-- Find the starting position of the value.
		SET @LeftPointer = @LeftPointer + LEN(@SearchString);
		SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
		SET @ValueStart = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);

		-- Find the length of the value.
		SET @LeftPointer = @RightPointer + 1;
		SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
		SET @ValueLength = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);

		IF @ValueLength = -1
		BEGIN
			RETURN NULL;
		END;

		RETURN SUBSTRING(@PropertyValuesBinary, @ValueStart + 1, @ValueLength);
	END;


	RETURN NULL;
	END;
GO

And last, but not least, here is a sample of how to use the functions:

SELECT
	U.[Username],
	[dbo].[fn_GetProfileStringElement]('FirstName', P.[PropertyNames], P.[PropertyValuesString]) [FirstName],
	[dbo].[fn_GetProfileStringElement]('LastName', P.[PropertyNames], P.[PropertyValuesString]) [LastName],
	[dbo].[fn_GetProfileBinaryElement]('Avatar', P.[PropertyNames], P.[PropertyValuesBinary]) [Avatar]
FROM
	[dbo].[aspnet_Users] U
	JOIN [dbo].[aspnet_Profile] P ON P.[UserId] = U.[UserId]

Posting code in this blog takes a lot of work, and it is possible that I screwed it up. Please post a comment if you find a problem with the code.

JawberDoo.com: The story begins...

Bill Bosacker — Sat, 06 Jun 2009 07:00:00 GMT

Welcome to the first post of this new series. This has been a busy week. We've done quite a bit of work on the BeHeard.com site, primarily with the "Search" and "AutoComplete". The detail pages aren't ready yet, but they are...(read more)