Archive for April, 2010

Efficient way to do Paging in SQL Server

I was going through SQLServerCentral.com and found this pretty nice article to do paging in an efficient manner. So thought of sharing with you all and ofcourse it is a note to myself 🙂


WITH Keys
AS
(
 SELECT
 TOP (@PageNumber * @PageSize)
 rn = ROW_NUMBER() OVER (ORDER BY P1.Post_ID ASC)
 P1.Post_ID
 FROM
 dbo.Post P1
 ORDER BY
 P1.Post_ID ASC
),
SelectedKeys AS
(
 SELECT
 Top(@PageSize)
 SK.rn,
 SK.Post_ID
 FROM
 Keys SK
 WHERE
 SK.rn >((@PageNumber -1 ) * @PageSize)
 ORDER BY
 SK.Post_ID ASC
)
SELECT
 SK.rn,
 P2.Post_ID,
 P2.Thread_ID,
 P2.Member_ID,
 P2.Created_Date,
 P2.Title,
 P2.Body
FROM
 SelectedKeys SK
JOIN
 dbo.Post P2
ON
 P2.Post_ID = SK.Post_ID
ORDER BY
 SK.Post_ID ASC

Happy Programming!!!

Cheers,

Raja

Advertisements

Getting the controlname which caused postback

I am working on a site in which I use a lot of JQuery but there are some pages which I had to use Server Controls and there was a need to get which control caused the postback.  After a bit of searching I found this nice method which did the trick.


private string  getPostBackControlName()
{
 Control control = null;
 //first we will check the "__EVENTTARGET" because if post back made by       the controls
 //which used "_doPostBack" function also available in Request.Form collection.
 string ctrlname = Page.Request.Params["__EVENTTARGET"];
 if (ctrlname != null && ctrlname != String.Empty)
 {
 control = Page.FindControl(ctrlname);
 }
 // if __EVENTTARGET is null, the control is a button type and we need to
 // iterate over the form collection to find it
 else
 {
 string ctrlStr = String.Empty;
 Control c = null;
 foreach (string ctl in Page.Request.Form)
 {
 //handle ImageButton they having an additional "quasi-property" in their Id which identifies
 //mouse x and y coordinates
 if (ctl.EndsWith(".x") || ctl.EndsWith(".y"))
 {
 ctrlStr = ctl.Substring(0, ctl.Length - 2);
 c = Page.FindControl(ctrlStr);
 }
 else
 {
 c = Page.FindControl(ctl);
 }
 if (c is System.Web.UI.WebControls.Button ||
 c is System.Web.UI.WebControls.ImageButton)
 {
 control = c;
 break;
 }
 }
 }
 return control.ID;
}

Happy Programming!!!

Cheers,

Raja

Getting column description in SQL Server 2005

We are working on code generation and thought it would be neat to pull information from description (where we intend to put validations for ex. DOB – No future date).  The following query did that trick.


SELECT
 [Table Name] = i_s.TABLE_NAME,
 [Column Name] = i_s.COLUMN_NAME,
 [Description] = s.value
FROM
 INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
 sys.extended_properties s
ON
 s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
 AND s.minor_id = i_s.ORDINAL_POSITION
 AND s.name = 'MS_Description'
WHERE
 OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
 --AND i_s.TABLE_NAME = 'table_name'
ORDER BY
 i_s.TABLE_NAME, i_s.ORDINAL_POSITION

Hope this helps someone.

Happy Programming!!!

Cheers,

Raja