Rodel E. Dagumampan : stored procedures

SqlServer: Return Random Records in T-Sql, the GUID way.

rdagumampan — Wed, 20 Jun 2007 15:17:00 GMT

In one of the website I have developed, we feature random products displayed on a webpart. This can be done several ways either of the code behind or via t-sql but I think the simplest way to do it is via t-sql with the help of Guid columns.

Globally Unique Identifier or GUID is a randomly generated guaranteed unique string originally used to generate reference numbers and keys in softwares. In Sql Server, it can be generated using the NewId() function.

Given GUID is random, adding a GUID column to the original resultset and use it as the sort column surely yields a random result. Sample script below:

1 -->

2 IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'Product')

3 DROP TABLE Product;

4 GO

6 --create the test product table

7 CREATE TABLE Product (

8 IdProduct int PRIMARY KEY IDENTITY(1001,1),

9 ProductName nvarchar(255),

10 Price decimal(18,2),

11 AverageRating int,

12 ItemViewed int,

13 ItemSold int,

14 SortOrder int,

15 DateCreated smalldatetime,

16 LastUpdated smalldatetime)

17 GO

19 --initialize table with test data

20 INSERT INTO Product VALUES('Item 1', 10.00, 1, 99, 23, 1000, GETDATE(), GETDATE());

21 INSERT INTO Product VALUES('Item 2', 21.00, 2, 97, 56, 1002, GETDATE(), GETDATE());

22 INSERT INTO Product VALUES('Item 3', 11.00, 3, 95, 23, 1001, GETDATE(), GETDATE());

23 INSERT INTO Product VALUES('Item 4', 22.00, 4, 93, 65, 1000, GETDATE(), GETDATE());

24 INSERT INTO Product VALUES('Item 5', 12.00, 5, 91, 54, 1000, GETDATE(), GETDATE());

25 INSERT INTO Product VALUES('Item 6', 23.00, 6, 89, 12, 1006, GETDATE(), GETDATE());

26 INSERT INTO Product VALUES('Item 7', 13.00, 8, 87, 89, 1050, GETDATE(), GETDATE());

27 INSERT INTO Product VALUES('Item 8', 24.00, 9, 85, 21, 1021, GETDATE(), GETDATE());

28 INSERT INTO Product VALUES('Item 9', 14.00, 1, 98, 67, 1020, GETDATE(), GETDATE());

29 INSERT INTO Product VALUES('Item 10', 25.00, 3, 99, 78, 1001, GETDATE(), GETDATE());

32 --create the stored procedures

33 IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_query_all_featured_product]') and type IN (N'P', N'PC'))

34 DROP PROCEDURE [dbo].[usp_query_all_featured_product];

35 GO

37 /*------------------------------------------------------------------------------

38 // <procedureInformation>

39 // procedureName : [dbo].[usp_query_all_featured_product]

40 // description : get random products from a table

41 // createdBy : rdagumampan

42 // createdWhen : 06-20-2007 02:08 AM

43 //

44 // modification history

45 // date Modified remarks

46 // <procedureInformation>

47 */------------------------------------------------------------------------------

49 CREATE PROC [dbo].[usp_query_all_featured_product]

50 @pageIndex [int] = 1, -- current page

51 @pageSize [int] = 999, -- number of rows per page

52 @totalRows [int] OUTPUT -- total number of rows

53 AS

54 BEGIN

56 ----xx begin unit testing paramters

57 -- DECLARE @returnTop int;

58 -- SELECT @returnTop = 5;

59 ----xx end unit testing parameters

60 --perform attempt to retrieve all records

61 WITH result AS (

62 SELECT

63 ROW_NUMBER() OVER (ORDER BY NewId() ASC) AS RowNumber,

64 prod.IdProduct,

65 prod.ProductName,

66 prod.Price,

67 prod.AverageRating,

68 prod.ItemViewed,

69 prod.ItemSold,

70 prod.SortOrder,

71 prod.DateCreated,

72 prod.LastUpdated

73 FROM Product prod )

74 SELECT

75 prod.IdProduct,

76 prod.ProductName,

77 prod.Price,

78 prod.AverageRating,

79 prod.ItemViewed,

80 prod.ItemSold,

81 prod.SortOrder,

82 prod.DateCreated,

83 prod.LastUpdated

84 FROM result prod

85 WHERE prod.RowNumber BETWEEN

86 (@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize

88 SELECT @totalRows = COUNT(IdProduct) FROM Product;

89 END

91 GO

93 --execute stored product for testing

94 DECLARE @totalRows INT;

95 EXEC [usp_query_all_featured_product] @pageIndex = 1, @pageSize = 5, @totalRows = @totalRows ;

DISCLAIMER: Use this script at your own risk. Never execute this script on a production database.

.NET: Advanced Data Paging with Sorting In SqlServer 2005

rdagumampan — Mon, 15 Jan 2007 23:59:00 GMT

dwarvend, a former teammate in my frist company once asked me about data paging large resulsets in ASP.NET. Data paging in SQL Server 2005 is relatively easy with new RowNumber feature but supporting dynamic sorting is little tricky. I have this executable to script to demonstrate this sorting.

--check if table already exists

IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'Product')

DROP TABLE Product;

--create the test product table

CREATE TABLE Product (

IdProduct int PRIMARY KEY IDENTITY(1,1000),

ProductName nvarchar(255),

Price decimal(18,2),

AverageRating int,

ItemViewed int,

ItemSold int,

SortOrder int,

DateCreated smalldatetime,

LastUpdated smalldatetime)

--initialize table with test data

INSERT INTO Product VALUES('Item 1', 10.00, 1, 99, 23, 1000, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 2', 21.00, 2, 97, 56, 1002, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 3', 11.00, 3, 95, 23, 1001, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 4', 22.00, 4, 93, 65, 1000, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 5', 12.00, 5, 91, 54, 1000, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 6', 23.00, 6, 89, 12, 1006, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 7', 13.00, 8, 87, 89, 1050, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 8', 24.00, 9, 85, 21, 1021, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 9', 14.00, 1, 98, 67, 1020, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 10', 25.00, 3, 99, 78, 1001, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 11', 26.00, 4, 96, 43, 1009, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 12', 28.00, 5, 94, 76, 1008, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 13', 16.00, 6, 92, 72, 1007, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 14', 30.00, 7, 90, 62, 1009, GETDATE(), GETDATE());

INSERT INTO Product VALUES('Item 15', 17.00, 2, 88, 25, 1010, GETDATE(), GETDATE());

--create the stored procedures

IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_query_all_product]') and type IN (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_query_all_product];

/*------------------------------------------------------------------------------

// <auto-generated/>

// <procedureInformation>

// procedureName:[dbo].[usp_query_all_product]

// description:

// createdBy:rdagumampan

// createdWhen: 01-16-2007 02:08:04

// modification history

// date Modified remarks

// <procedureInformation>

*/------------------------------------------------------------------------------

CREATE PROC [dbo].[usp_query_all_product]

@sortColumn nvarchar(32) = 'ItemSold', -- column used for sorting

@sortDirection nvarchar(32) = 'DESC', -- column sorting behavious DESC | ASC

@pageIndex [int] = 1, -- page index

@pageSize [int] = 999, -- number of rows returned per page

@totalRows [int] OUTPUT -- number rows of all pages

BEGIN

----xx begin unit testing paramters

----xx end unit testing parameters

--perform attempt to retrieve all records

WITH result AS (

SELECT

ROW_NUMBER() OVER (ORDER BY prod.SortOrder DESC) AS row_number,

prod.IdProduct,

prod.ProductName,

prod.Price,

prod.AverageRating,

prod.ItemViewed,

prod.ItemSold,

prod.SortOrder,

prod.DateCreated,

prod.LastUpdated

FROM Product prod )

SELECT

prod.IdProduct,

prod.ProductName,

prod.Price,

prod.AverageRating,

prod.ItemViewed,

prod.ItemSold,

prod.SortOrder,

prod.DateCreated,

prod.LastUpdated

FROM result prod

WHERE row_number BETWEEN

(@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize

ORDER BY

CASE UPPER(@sortDirection)

WHEN 'DESC' THEN

CASE @sortColumn

WHEN 'SortOrder' THEN prod.SortOrder

WHEN 'LastUpdated' THEN prod.LastUpdated

WHEN 'Price' THEN prod.Price

WHEN 'AverageRating' THEN prod.AverageRating

WHEN 'ItemSold' THEN prod.ItemSold

END

DESC,

CASE UPPER(@sortDirection)

WHEN 'ASC' THEN

CASE @sortColumn

WHEN 'SortOrder' THEN prod.SortOrder

WHEN 'LastUpdated' THEN prod.LastUpdated

WHEN 'Price' THEN prod.Price

WHEN 'AverageRating' THEN prod.AverageRating

WHEN 'ItemSold' THEN prod.ItemSold

END

ASC;

--get total number of results

SELECT @totalRows = COUNT(prod.IdProduct) FROM Product prod

END

--execute stored product for testing

DECLARE @totalRows INT;

EXEC [usp_query_all_product]

@sortColumn = 'ItemSold', -- SortOrder | LastUpdated | Price | AverageRating | ItemSold

@sortDirection ='ASC', -- ASC DESC

@pageIndex= 1,

@pageSize=5,

@totalRows = @totalRows OUTPUT;

SELECT @totalRows;

DISCLAIMER: Use this script at your own risk. Never execute this script on a production database.

Share this post: Email it! | bookmark it! | digg it! | reddit!| kick it!

L10N: Autosize property of labels on windows forms

rdagumampan — Tue, 07 Nov 2006 17:24:00 GMT

Lately, I was wondering why the Autosize property of label on windows forms is set to true, this is annoying and tiring if you love using windows forms designer(yucks! kidding ), because you often want a properly aligned controls and fixed with labels on data entry textboxes.But not until i want to loicalize these labels I realized that this is very helpful on making labels display localized text that extends more than the default text. Most of the time, English text is shorter than dannish, finnish, or scadinavian translations, sometimes its the other way around.

HTH!

L10N: The world is flat, start localization on your database

rdagumampan — Tue, 07 Nov 2006 17:00:00 GMT

When you are developing software product to be catered to diverse customers, it must be a standard to localized your software. With .NET this has never been easy, on .NET 2.0 its even easier with striongly typed resource files. But localizing application must be part of the architectture and should not be done later when product is about to market or when the market demands it to be.

The are two main areas to be considered first before you kick it off,

1. User Interface
These are the buttons, the labels, the error and messages we see while using the application. It is stored on a resource file, loaded at runtime based on specific culture. Localizing the interface can be done by using resource files and special binding features on .NET. Several articles discussing UI localization are available the net.

2. Data
These are the records we stored on the database, the lookup tables and listings. To have a completely localized application, the data must be considered, the datatypes, the design. Very few discuss this topic on net.

To localize a data-intensive application we must start with the architectture where data will be placed; the database. But it is not as simple as we thought, it isnt as simple as using nchar, nvarchar, ntext, and unicode-supported datatypes. The table's design has to be multilingual capable also, I have sample structure of a non-multiligual and multiligual table design.

Real-world application
Here I have an Advertisement table, the scenario is you have an ecommerce application available to customers of different countries, it must support limited languages and user must explicitly select the language.

Non-Localizable
Without localization in mind we will simply design the table this way,

Localizable
Making the table supports multi-languages, we design it this way.

The Fallback Mechanism
The database have to handle event where the application requested for localized records on a specific country that does not exists. In .NET, fallback is handled by returning the neutral resource file. When you request for en-us and en-us resource file does not exisits, it will return you the neutral en resource file.

In my example tables above, if user request for en-PH and en-PH does not exists, I will check id there is en, if en still does not exists, I will return the default text on the Advertisement table. The application is blind how it is being done, it just passed the user locale and the stored procedures returns the localized record. the script goes something lile this:

DECLARE @culture nvarchar(8), @idNeutralCulture int, @idSpecificCulture int;
SELECT @culture = 'en-ph'
SELECT @idSpecificCulture = cul.IdCulture FROM Culture cul WHERE cul.Culture = @culture;
SELECT @idNeutralCulture = cul.IdCulture FROM Culture cul WHERE cul.Culture = LEFT(@culture,2);

--check, specific culture exist
IF EXISTS(SELECT 1 FROM AdvertisementText adtext WHERE adtext.IdCulture = @idSpecificCulture)
BEGIN
  SELECT
      ad.IdAdvertisement, ad.IdProvider
      ,ad.IdOrientation, adtext.Text, adtext.HtmlTextDescription
      ,adtext.PlainTextDescription, ad.DateWhenToStart, ad.DateWhenToEnd
  FROM Advertisement ad
  INNER JOIN AdvertisementText adtext ON ad.IdAdvertisement = adtext.IdAdvertisementText AND adtext.IdCulture;

END
--check, neurtal culture exist
ELSE IF EXISTS(SELECT 1 FROM AdvertisementText adtext WHERE adtext.IdCulture = @idNeutralCulture)
BEGIN
  SELECT
      ad.IdAdvertisement, ad.IdProvider
      ,ad.IdOrientation, adtext.Text, adtext.HtmlTextDescription
      ,adtext.PlainTextDescription, ad.DateWhenToStart, ad.DateWhenToEnd
  FROM Advertisement ad
  INNER JOIN AdvertisementText adtext ON ad.IdAdvertisement = adtext.IdAdvertisementText AND adtext.IdCulture;  
END
--else, return the default text descriptions
ELSE
BEGIN
  SELECT
      ad.IdAdvertisement, ad.IdProvider
      ,ad.IdOrientation, ad.Text, ad.HtmlTextDescription
      ,ad.PlainTextDescription, ad.DateWhenToStart, ad.DateWhenToEnd
  FROM Advertisement ad
END

scriptedby:rodeldagumampan
11.08.2006.04AM

This is part my blog series on my experiences making a multi-lingual application. Keep posted and raise questions if you have as I am also a newbie on this and I believe I learned a lot from other developers ;). HTH, happy coding!

Share this post: Email it! | bookmark it! | digg it! | reddit!| kick it!