Fransiscus Setiawan

This is very important for the most web developers. This stored procedure accept parameter of page index and page size. By using this paging, you don’t need to store everything in your viewstate which in the end will cause performance suffer. This will load up the necessary result based on the page index and page size and it uses row number to put the number in each row.

Datagrid needs to use custom paging = true as well as virtualitem count to produce the number for paging.

Datagrid in ASPX Page

Code Behind

//function to do the paging itself
  private void LoadContent(int pageIndex)
    {
        _presenter.GetContentSubmissionSelect(pendingStatusID, pageIndex, PAGE_SIZE);

        if (GetContentSubmissionSelect.Rows.Count > 0)
        {
            lblNoContent.Visible = false;

            dgTaskList.Visible = true;
            dgTaskList.AllowCustomPaging = true;
            dgTaskList.AllowPaging = true;
            dgTaskList.PageSize = PAGE_SIZE;
            _presenter.GetContentSubmissionCount(pendingStatusID);

            dgTaskList.VirtualItemCount = Convert.ToInt32(GetContentSubmissionCount.Rows[0][0]);
            dgTaskList.DataSource = GetContentSubmissionSelect;
            dgTaskList.CurrentPageIndex = pageIndex;
            dgTaskList.DataBind();
        }
        else
        {
            dgTaskList.Visible = false;
            lblNoContent.Visible = true;
        }
    }

//handle the page index changed
    protected void dgTaskList_PageIndexChanged(object sender, DataGridPageChangedEventArgs e)
    {
        LoadContent(e.NewPageIndex);
    }

SQL Stored Procedure
We need to have two query where one to get the number of records and the other one is to get the actual result it self
To get the query/actual result

CREATE PROCEDURE uspContentSubmissionSelect
(
	@statusID int = NULL,
	@PageIndex int,
	@PageSize int
)
AS
	SET NOCOUNT ON;
SELECT
	o.Id, o.ContentSubmission_id, o.AppointmentId,
	o.Title, o.StatusName
FROM
	(
		SELECT
			r.ID, r.contentSubmission_id, r.AppointmentId,
			r.Title, s.StatusName,
			ROW_NUMBER() OVER (ORDER BY r.ID DESC) as RowNumber
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.Id = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))
	) o
WHERE
	o.RowNumber BETWEEN (@PageIndex * @PageSize + 1) AND ((@PageIndex * @PageSize) + @PageSize)
ORDER BY RowNumber
GO

To get the record count

CREATE PROCEDURE [dbo].[uspContentSubmissionCount]
(
	@statusID int = NULL
)
AS
	SET NOCOUNT ON;

		SELECT
			COUNT(*) AS TotalRow
		FROM
			MyInternet.ContentSubmissionRef r
		INNER JOIN
			MyInternet.Status s
		ON
			s.ID = r.StatusID
		AND
			((@StatusID IS NULL) OR (s.ID = @statusID))

I’ve got a question this morning about how to find a particular query in a huge list of stored procedure. It’s not that hard, what you need to do is to use SYS object and sys.procedures basically contains all the stored procedure under the active database that you are using. This is the query that you can use to find a keyword product in any stored procedure

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%product%'
    AND ROUTINE_TYPE='PROCEDURE'

And the query below is used to find the stored procedure name that match with what you are looking for

SELECT *
FROM sys.procedures where OBJECT_DEFINITION(object_id) LIKE '%product%'

UPDATE:
How to find all the triggers on your Database

SELECT tbl1.[name] TableName, tbl0.[name] TriggerName,
CASE
WHEN tbl1.deltrig = tbl0.id  THEN 'OnDelete'
WHEN tbl1.instrig = tbl0.id THEN 'OnInsert'
WHEN tbl1.updtrig = tbl0.id THEN 'OnUpdate'
END 'Operation Type', tbl1.*,tbl0.*
FROM sysobjects tbl0 JOIN sysobjects tbl1 ON tbl0.parent_obj = tbl1.[id] WHERE tbl0.xtype='TR'

I tried to create an average in sql server by dividing two figures. Once I got the error of “Divide by zero error occured”, this is caused by dividing the figure by null value, to handle the error we can use “NULLIF”

DECLARE @ThisMonthAvg decimal(8,2)
SET @ThisMonthAvg = @ThisMonthPlayers / NULLIF(@ThisMonthEvents, 0)

DECLARE @LastMonthAvg decimal(8,2)
SET @LastMonthAvg = @LastMonthPlayers / NULLIF(@LastMonthEvents, 0)

DECLARE @ThisYearAvg decimal(8,2)
SET @ThisYearAvg = @ThisYearPlayers / NULLIF(@ThisYearEvents, 0)

DECLARE @LastYearAvg decimal(8,2)
SET @LastYearAvg = @LastYearPlayers / NULLIF(@LastYearEvents, 0)

My friend has been asked to optimize the SQL query for a forum application. Basically the Query is used to list the thread as well as the last person who posted on the forum. He and me tried to optimize that and we can fasten up the query up to only 13 secs which is not really well . then the sql guru is coming to help him and show him the better way in writing the query and it performs only 1 sec. I thought it’s gonna be a good lesson to be shared it on my blog. There you go:

--Non Optimized Version
SELECT
	ForumTopic.ForumTopicID, ForumTopic.Title, Members.Username AS MemberName,
	ForumTopic.Views, ForumTopic.Replies,
	(
		SELECT TOP 1
			ForumPost.EnteredDate
		FROM
			ForumPost
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostEnteredDate,
	(
		SELECT TOP 1
			Members.Username
		FROM
			ForumPost
		INNER JOIN
			Members
		ON
			ForumPost.EnteredBy = Members.MemberID
		WHERE
			ForumPost.ForumTopicID = ForumTopic.ForumTopicID
		ORDER BY
			ForumPost.ForumPostID DESC
	) AS LastPostMemberName
FROM
	ForumTopic
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
WHERE
	ForumID = @ForumID
AND
	ForumTopic.Valid = 1
ORDER BY
	LastPostEnteredDate DESC

--optimized version
SELECT
	ForumTopic.ForumTopicID,
	ForumTopic.Title,
	Members.UserName as MemberName,
	ForumTopic.Views,
	ForumTopic.Replies,
	ForumPost.EnteredDate as LastPostEnteredDate,
	Members1.Username as LastPostMemberName
FROM
	(
		SELECT
			ForumTopicID, MAX(ForumPostID) AS ForumPostID
		FROM
			ForumPost
		WHERE
			ForumTopicID IN (
				SELECT
					ForumTopicID
				FROM
					ForumTopic
				WHERE
					ForumTopic.ForumID = @ForumID
				AND
					ForumTopic.Valid = 1)
		GROUP BY
			ForumTopicID
	) RecentPost
INNER JOIN
	ForumPost
ON
	ForumPost.ForumPostID = RecentPost.ForumPostID
INNER JOIN
	ForumTopic
ON
	ForumTopic.ForumTopicID = RecentPost.ForumTopicID
INNER JOIN
	Members
ON
	ForumTopic.EnteredBy = Members.MemberID
INNER JOIN
	Members Members1
ON
	ForumPost.EnteredBy = Members1.MemberID
ORDER BY
	ForumPost.EnteredDate DESC

How to do a recursive query in SQL Server 2005, Recursive in the terms of you got a table where it has foreign key to the other record on the same table. Assume a table employee where a record on that table can be an employee or a manager which is an employee as well, or let’s call it when you have nested structure within one table, call it as a tree structure. I’ve googled and found the way in doing it so I believe it’s worthy for me to share with everyone

Table Schema

How to get the whole structure by using one SQL query, we can utilize CTE(Common table expression) and using UNION ALL to do the recursive and do the INNER JOIN with the CTE it self

--get all contentPageID for the page and its children
WITH dynamicPages(contentPage_id, parentContent_id, contentPageName) AS
	(
		SELECT
			c.contentPage_id, c.parentContent_id, c.ContentPageName
		FROM
			tblContentPage c
		WHERE
			c.contentPage_id = @contentPage_id
		UNION ALL
		SELECT
			c.contentPage_id, c.parentContent_id, c.contentPageName
		FROM
			tblContentPage c
		INNER JOIN
			dynamicPages b
		ON
			c.parentContent_id = b.contentPage_id
	)
SELECT * FROM dynamicPages

this is only a simple trigger and table to monitor the activity on an update activity to “members” table. it tracks the date when the update query is being executed and who execute it, it will be quite useful for a database that has many applications accessing it with different SQL User

Holding table :

CREATE TABLE [dbo].[MemberUpdateTracking](
	[MemberTrackingID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NULL,
	[BeforeMemberCode] [varchar](50) NULL,
	[BeforeFirstName] [varchar](255) NULL,
	[BeforeSurname] [varchar](255) NULL,
	[BeforeUserName] [varchar](255) NULL,
	[BeforePassword] [varchar](255) NULL,
	[BeforeEmailAddress] [varchar](255) NULL,
	[AfterMemberCode] [varchar](50) NULL,
	[AfterFirstName] [varchar](255) NULL,
	[AfterSurname] [varchar](255) NULL,
	[AfterUserName] [varchar](255) NULL,
	[AfterPassword] [varchar](255) NULL,
	[AfterEmailAddress] [varchar](255) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[SQLUser] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MemberUpdateTracking] PRIMARY KEY CLUSTERED
(
	[MemberTrackingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Triggers:

CREATE TRIGGER [dbo].[tr_Member_UPDATE]
	ON [dbo].[Members]
	FOR UPDATE
AS
BEGIN
	SET	NOCOUNT ON

	DECLARE @MemberID			int
	DECLARE @AfterFirstName		varchar(255)
	DECLARE @AfterSurname		varchar(255)
	DECLARE @AfterMemberCode	varchar(50)
	DECLARE @AfterUserName		varchar(255)
	DECLARE @AfterPassword		varchar(255)
	DECLARE @AfterEmailAddress	varchar(255)

	DECLARE @BeforeFirstName	varchar(255)
	DECLARE @BeforeSurname		varchar(255)
	DECLARE @BeforeMemberCode	varchar(50)
	DECLARE @BeforeUserName		varchar(255)
	DECLARE @BeforePassword		varchar(255)
	DECLARE @BeforeEmailAddress	varchar(255)


	--get information what will been inserted
	SELECT @MemberID = MemberID,
		   @AfterFirstName = FirstName,
		   @AfterSurname = Surname,
		   @AfterUserName = Username,
		   @AfterPassword = [Password],
		   @AfterMemberCode = MemberCode,
		   @AfterEmailAddress = EmailAddress
	FROM
		Inserted

	--only need for strange behaviour issue that we had before otherwise not necessary
	IF (@AfterFirstName = '' OR @AfterSurname = '' OR @AfterFirstName IS NULL OR @AfterSurname IS NULL)
	BEGIN
		--get the original information
		SELECT  @BeforeFirstName = FirstName,
				@BeforeSurname = Surname,
				@BeforeUserName = Username,
				@BeforePassword = [Password],
				@BeforeMemberCode = MemberCode,
				@BeforeEmailAddress = EmailAddress
		FROM
			Deleted

		--track the changes
		INSERT INTO MemberUpdateTracking(MemberID, BeforeMemberCode, BeforeFirstName,
					BeforeSurname, BeforeUserName, BeforePassword, BeforeEmailAddress,
					AfterMemberCode, AfterFirstName, AfterSurname, AfterUserName, AfterPassword,
					AfterEmailAddress, ModifiedDate, SQLUser)
		VALUES
			(@MemberID, @BeforeMemberCode, @BeforeFirstName, @BeforeSurname,
					@BeforeUserName, @BeforePassword, @BeforeEmailAddress, @AfterMemberCode,
					@AfterFirstName, @AfterSurname,
					@AfterUserName, @AfterPassword, @AfterEmailAddress,
					GETDATE(), SYSTEM_USER)
	END
END

I was having a problem when adding a stored procedure into table adapter. The error was “Invalid object” of my temporary table name inside stored procedure.

It’s a bit strange since what i thought the table adapter only care about the results being returned but what it is actually checking up the existence of the table it self. FMTONLY is used to “Returns only metadata to the client. Can be used to test the format of the response without actually running the query”.

--uncomment this to regenerate it in table adapter
--comment it once you are done with generating the table adapter
SET FMTONLY OFF