The Code Consulting: 2010

Tuesday, December 21, 2010

ASP.NET Tips

//Adding a namespace in Designer view

<%@ Import Namespace="Sluice.GlobalClass" %>

//Adding a C# code in Design View

<%#SessionHelper.BusinessObjectsInfo.Permission.CanBasecampIntegration%>

Custom Validator With ClientValidation Property

//Check whether the either one of text box or drop down list is selected using JQuery */

//Step 1 - Javascript Code

function CheckProjectEntered(source, arguments) {

try {

var ddlProject = $("select[id*='ddlProject']");

var txtProjectTitle = $("input[id*='txtProjectTitle']");

if ($(ddlProject).get(0).selectedIndex == 0 && $(txtProjectTitle).val().length == 0)

arguments.IsValid = false;

else

arguments.IsValid = true;

} catch (e) {

}

//Step 2:

< id="vsProjects" runat="server" cssclass="error">

//Step 3:

//DropDownList Control

< id="ddlProject" runat="server" width="200px" tabindex="1">

< / asp:DropDownList>

< id="cvProjectTypeId" runat="server" display="None" clientvalidationfunction="CheckProjectEntered" errormessage="Select or Enter a Project name!" text=" " validateemptytext="True">< / asp:CustomValidator >

//Step 4:

//TextBox

< id="txtProjectTitle" runat="server" width="200px" tabindex="2" maxlength="20">< / asp : TextBox >

< id="cvCreateProject" runat="server" display="None" clientvalidationfunction="CheckProjectEntered" errormessage="Select or Enter a Project name!" text=" " validationgroup="ProjectExport" validateemptytext="True"> < / asp:CustomValidator >

//Step 5:

< id="btnExport" runat="server" text="Export My Project" cssclass="button" causesvalidation="true" tabindex="3">

< id="btnCancel" runat="server" text="Cancel" cssclass="button" causesvalidation="false" onclientclick="$find('mpeEnterProject').hide();" tabindex="4">

//Key points to note

1. The controltovalidate property should not be used.

2. CausesValidation should be true for the control which is going to be validation and should be false for the control which has the cancel option.

Friday, December 17, 2010

Use Dynamic SQL to Improve Query Performance & Avoiding Sql Injections, sp_depends

/*This article shows an example of a poor performing query and how to make it perform better using Dynamic SQL. It also demonstrates a way of coding dynamic SQL so it can easily be debugged. When we write dynamic SQL within a stored procedure not only do we have to give the user EXECUTE rights to the procedure but also specific access to the tables in the dynamic SQL.

/*A sample of Poor performance query */

It is a stored procedure that was taking a long time to execute. The purpose of the stored procedure is to return rows that match a given set of optional inputs. The way this is achieved using non-dynamic SQL is for the query to test the value of the parameter for NULL or a column equal to the value of the parameter. All columns on the table are defined to be NOT NULL and the passing in of a parameter with a NULL value logically means that we don't care what the value is. You might think that SQL Server would short-circuit the query when it sees that the parameter is null and not bother to look at the values on the table but it actually does a full table scan instead.

USE [AdventureWorks]

CREATE PROCEDURE SearchProducts

(

@Name nvarchar(50) = NULL,

@Color nvarchar(15) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

BEGIN

SELECT

[Name],

[Color],

[ListPrice],

[ModifiedDate]

FROM

[Production].[Product]

WHERE

([Name] LIKE @Name OR @Name IS NULL)

AND

([Colour] LIKE @Color OR @Color IS NULL)

AND

([ListPrice] >= @ListPriceMin OR @ListPriceMin IS NULL)

AND

([ListPrice] <= @ListPriceMax OR @ListPriceMax IS NULL) AND (ModifiedDate >= @ModifiedDateMin OR @ModifiedDateMin IS NULL)

AND

(ModifiedDate <= @ModifiedDateMax OR @ModifiedDateMax IS NULL) END GO --Well Performing but open to SQL Injection Dynamic SQL

USE [AdventureWorks]

/**********************************************************************

-- Sample:

-- EXEC [SearchProducts_SQLInjectable] 'W%', 'SH%'

-- Injected SQL

-- EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

********************************************************************** /

CREATE PROCEDURE [dbo].[SearchProducts_SQLInjectable]

(

@Name nvarchar(62) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ ' WHERE 1 = 1' -- Always true

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE ''' + @ProductNumber + ''''

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= ' + Cast(@ListPriceMin as varchar(30))

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= ' + Cast(@ListPriceMax as varchar(30)) IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= ' + Convert(varchar(30), @ModifiedDateMin, 121)

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= ' + Convert(varchar(30), @ModifiedDateMax, 121) IF @Name IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + @Name + '''' SET @sSql = @sSql + @sWhereClause EXEC (@sSql) END GO

/*
Constructing SQL this way is not good, it leaves the procedure open to SQL Injection; little Bobby Tables might enter his name! Also, it does not allow the optimiser to reuse the execution plan. An example of how SQL Injection can be performed is given in the comments below:

EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

/*
A slightly better way to construct the WHERE clause and provide a little more protection is to double up the quotes in each parameter by calling SQL Server's Replace function e.g.:

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + Replace(@Name, '''', '''''') + ''''

This above query is still not optimal as it still does not allow SQL Server to reuse the query when different values of @Name are passed in to the procedure, though it should prevent most forms of SQL injection.

/* sp_depends is used to find out which procedures use which tables then this stored procedure will be listed

EXEC sp_depends 'Production.Product'

/*The above lists out the all the stored procedure which uses this table*/

Using Parameterised Dynamic SQL to avoid SQL Injection along with the debug option

USE [AdventureWorks]

-- **********************************************************************

-- Sample CALL:-- EXEC [SearchProducts_SQLNonInjectable] 'W%'

-- **********************************************************************

CREATE PROCEDURE [dbo].[SearchProducts_SQLNonInjectable]

(

@Name nvarchar(50) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL,

@Debug bit = 0 -- When set to 1 just prints SQL and does not execute it

)

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @ParmDefinition nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ 'WHERE 1 = 1' -- Always true

IF @Name IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE @Name'

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE @ProductNumber'

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= @ListPriceMin'

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= @ListPriceMax' IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= @ModifiedDateMin'

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= @ModifiedDateMax ' SET @ParmDefinition = '@Name nvarchar(50),' + @NewLine + ' @ProductNumber nvarchar(25),' + @NewLine + ' @ListPriceMin money,' + @NewLine + ' @ListPriceMax money,' + @NewLine + ' @ModifiedDateMin datetime,' + @NewLine + ' @ModifiedDateMax datetime' SET @sSql = @sSql + @sWhereClause IF @Debug = 0 EXEC sp_executesql @sSql, @ParmDefinition, @Name=@Name, @ProductNumber=@ProductNumber, @ListPriceMin=@ListPriceMin,

@ListPriceMax=@ListPriceMax,

@ModifiedDateMin=@ModifiedDateMin, @ModifiedDateMax=@ModifiedDateMax

ELSE

BEGIN

PRINT 'DECLARE ' + @ParmDefinition

PRINT ''

'SET @Name = ' + IsNull('''' + @Name + '''','NULL')

PRINT 'SET @ProductNumber = ' + IsNull('''' + @ProductNumber + '''','NULL')

PRINT 'SET @ListPriceMin = ' + IsNull(Cast(@ListPriceMin AS varchar(30)),'NULL')

PRINT 'SET @ListPriceMax = ' + IsNull(Cast(@ListPriceMax AS varchar(30)),'NULL')

PRINT 'SET @ModifiedDateMin = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMin, 121) + '''','NULL')

PRINT 'SET @ModifiedDateMax = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMax, 121) + '''','NULL')

PRINT ''

PRINT @sSql

END

--END IF

//It is setted so as to get displayed in the sp_depends stored procedure.

-- Make sure this procedure is detected with sp_depends on table [Production].

--[Product]

IF 1 = 2 -- Always false

BEGIN

SELECT TOP(0) 0 FROM [Production].[Product]

END

--END IF

END

Thursday, December 16, 2010

Linking to the Previous Row

/*The client would like to see a report showing the Item name, the old price, the new price and the date range for which the new price was applied. This type of question has often given headaches to sql developers, as it usually involved messy subqueries using the max / min functions. Some DBAs prefer to store both the start date and end date in the table, but this too causes problems, as it is difficult to keep subsequent records in synch, especially when a modification occurs.*/

//Input:

//Output:

declare @Items table
(ID int, Name VARCHAR(1000))

//Sample Data
insert into @Items(ID,Name) values(1,'Vaccum Cleaner');
insert into @Items(ID,Name)values(2,'Washing Machine');
insert into @Items(ID,Name)values(3,'Tooth Brush');

//Price History Table Structure
declare @PriceHistory table(
ItemId int,
PriceStartDate datetime,
Price money
)

//Price History Table Data
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'03/01/2004',250.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'06/15/2005',219.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'01/03/2007',189.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(1,'02/03/2007',200.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'07/12/2006',650.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(2,'01/03/2007',550.00)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2005',1.99)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2006',1.79)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2007',1.59)
insert into @PriceHistory(ItemId,PriceStartDate,Price) values(3,'01/01/2008',1.49);

//Query to deliver the desired output.
WITH PriceCompare AS (
SELECT i.ID, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (Partition BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM @Items i INNER JOIN @PriceHistory ph
ON i.ID = ph.ItemId)

SELECT currow.ItemId, prevrow.Price AS PreviousPrice, currow.Price AS CurrentPrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1
AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1
AND currow.ItemId = prevrow.ItemId

Wednesday, December 15, 2010

STR - for displaying the rounded decimal number

/* ROUND(expression,length )
* It can only contain the maximum range of decimal value
* Eg: 9223372.14554 -> 9223372.15

**********************************************************/

DECLARE @Number FLOAT
SET @Number = 92233720.14554
SELECT ROUND(@Number,2)

Tuesday, December 14, 2010

Generating Subtotals using Subtotal(in a single query),Grouping

//GROUPING
/* The GROUPING function is used as an additional column in the query, and this function will render the output value as '1' for the grand total row and '0' for the rest If we omit the ROLL UP the GROUPING function will not produce the desired output.

The ROLLUP operator is useful in generating reports that contain subtotals and totals.

//Sample Data

SELECT

CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item,

CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color,

SUM(Quantity) AS QtySum

FROM Inventory GROUP BY Item, Color WITH ROLLUP

/*Individual investment record
* A separate row for the total amount invested and total broker commission *amount under each scheme
* A separate row for the total amount invested and total broker commission *amount under each product
*A single row for the total amount invested and total broker commission amount *under all products.
*************************************************************************************/
//Method 1:
//Create a Sample Table and Data

DECLARE @GroupTest TABLE
(
Product_Name varchar(100),
Scheme_Name varchar(100),
Invest_Amount numeric(18,2),
Broker_Commission numeric(18,2),
Issue_Date datetime
)

declare @cnt int, @idx int, @index int
set @cnt = 1
set @idx = 1
set @index = 1

while @cnt <=100 begin insert into @GroupTest values ( 'Product'+CAST(@idx AS VARCHAR), 'Scheme'+CAST(@index AS VARCHAR), ((@cnt * @idx / @index)* 1524.15),((@cnt * @idx / @index)* 124.32), GETDATE() ) if @cnt % 10 = 0 set @idx = @idx + 1 set @index = @index + 1 if @cnt % 5 = 0 set @index = 1 set @cnt = @cnt + 1 end //Individual investment record
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM
@GroupTest ORDER BY Product_Name,Scheme_Name,Comment

/*A separate row for the total amount invested and total broker commission amount under each scheme */

SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total'
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
ORDER BY Product_Name,Scheme_Name, Comment

/* A separate row for the total amount invested and total broker commission *amount under each product */
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
ORDER BY Product_Name, Scheme_Name, Comment

/*A single row for the total amount invested and total broker commission amount under all products. */
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment

/*Desired Output*/
SELECT
Product_Name,
Scheme_Name,
Invest_Amount,
broker_commission,
'0 Detail' AS Comment
FROM @GroupTest
UNION ALL
SELECT
Product_Name,
Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'1 Scheme_Total' as Comment
FROM @GroupTest
GROUP BY Product_Name,Scheme_Name
UNION ALL
SELECT
Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'2 Product_Total' AS Comment
FROM @GroupTest
GROUP BY Product_Name
UNION ALL
SELECT
'ZProductTotal' As Product_Name,
'ZTotal' AS Scheme_Name,
SUM(Invest_Amount) AS 'Invest_Amount',
SUM(broker_commission) AS 'Broker_Commission',
'3 Grand_Total' AS Comment
FROM @GroupTest
ORDER BY Product_Name,Scheme_Name, Comment

// Method 2:

// Using Grouping function

SELECT

Product_Name,

Scheme_Name,

Invest_Amount,

broker_commission,

-1 AS GrandTotal

FROM @GroupTest

UNION ALL

SELECT

ISNULL(Product_Name,'ZProductTotal'),

ISNULL(Scheme_Name,'ZTotal'),

SUM(Invest_Amount) AS 'Invest_Amount',

SUM(broker_commission) AS 'Broker_Commission',

GROUPING(Product_Name) 'GrandTotal'

FROM @GroupTest

GROUP BY Product_Name,Scheme_Name

WITH ROLLUP ORDER BY Product_Name,Scheme_Name, GrandTotal

//Fig 1. With Grouping // Fig 2. With grouping and without ISNULL function

Using Ranking Functions to Deduplicate Data

// Input : A A B B C D D E
// Output: A B C D E

// Create a sample test data

declare @AlphaList table (AlphaKey char);

insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('A');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('B');
insert into @AlphaList(AlphaKey) values ('C');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('D');
insert into @AlphaList(AlphaKey) values ('E');

select AlphaKey from @AlphaList order by 1;

//Returns the output   : 1 A  2 A  3 B  4 B  5 C  6 D  7 D  8 E
select
ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;
//Returns the output   : 1 1 A  1 2 A   3 3 B   3 4 B  5 5 C  6 6 D  7 7 D  8 8 E
select
RANK() over (order by AlphaKey) as Rank, ROW_NUMBER() over (order by AlphaKey) as RowNumber, AlphaKeyfrom @AlphaList;

Monday, December 13, 2010

Usage of ISNUMERIC and how to create ISALLDIGITS functionality

/* Usage of ISNUMERIC */

/*
checks whether a number is Digits, Decimal, floating point, number, money
*/

// A point of summary

 SELECT [Ascii Code]        = STR(Number),
[Ascii Character]   = CHAR(Number),
[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
FROM Master.dbo.spt_ValuesWHERE Type = 'P'
AND Number BETWEEN 0 AND 255
AND ISNUMERIC(CHAR(Number)) = 1

//Results

SELECT ISNUMERIC(',') // returns 1 since the decimal contains ','
SELECT ISNUMERIC('$') // returns 1 since the money contain '$'
SELECT ISNUMERIC('€') // returns 1 since the money contain '€'
SELECT ISNUMERIC('£') // returns 1 since the money contain '£'
SELECT ISNUMERIC('¥') // returns 1 since the money contain '¥'

// so ISNUMERIC is not a permanent solution for checking ISALLDIGITS
// ISALLDIGITS Snippet

CREATE FUNCTION dbo.IsAllDigits/********************************************************************
Purpose:
This function will return a 1 if the string parameter contains only
numeric digits and will return a 0 in all other cases.  Use it in
a FROM clause along with CROSS APPLY when used against a table.

--Siva Shunmugam
********************************************************************/
(@MyString VARCHAR(8000))
RETURNS TABLE AS
RETURN (
SELECT CASE
WHEN @MyString NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END AS IsAllDigits

Sunday, December 12, 2010

Sorting Months by Month Number.

/* Create a sample test record */

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

       DROP TABLE #MyHead





 SELECT TOP (1000000)

       SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),

       SomeAmount   = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)

  INTO #MyHead

  FROM master.sys.all_columns ac1

 CROSS JOIN master.sys.all_columns ac2

;



/* Add a clustered index to the table just to keep it from being a heap

    -- and to help with speed a bit by sorting the data.*/

CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime

    ON #MyHead (SomeDateTime)

;





/* This doesnt sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

;



/* The below sorts based on the Month Number */



 SELECT [Month] = DATENAME(mm,SomeDateTime),

       Amount  = SUM(SomeAmount)

  FROM #MyHead

 WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'

 GROUP BY DATENAME(mm,SomeDateTime)

 ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)

;

Saturday, December 11, 2010

Generating the top X rows from each group.

FYI: CROSS Apply works better than Filter in terms of performance

/* To display the records using Filter */
/* Create a temporary table */

CREATE TABLE #Runners
(
Runner integer NOT NULL,
Time integer NOT NULL,
Age integer NOT NULL
)

/* Insert sample records into the table*/

INSERT INTO #Runners

SELECT 1 , 10 , 20

UNION ALL

SELECT 2 , 15 , 20

UNION ALL

SELECT 3 , 11 , 20

UNION ALL

SELECT 4 , 12 , 30

UNION ALL

SELECT 5 , 18 , 30

UNION ALL

SELECT 6 , 9 , 40

UNION ALL

SELECT 7 , 16 , 40

UNION ALL

SELECT 8 , 13 , 30

UNION ALL

SELECT 9 , 13 , 30;

/* To retrieve the top X from each group using filter */

/* If both the time has the same time then the row number will be different for each same row */

with cteRunners
as
(
select * ,row_number() over (partition by Age order by Time ) as RowN
from #Runners
)

Select * from cteRunners
where RowN <=2
order by Age,Rown;


/* To give same row number for the same time present use DENSE_RANK */

with cteRunners 
as
( 
select *,DENSE_RANK() over (partition by Age order by Time ) as RowN 
from
#Runners
) 

Select  * from cteRunners 
where  RowN <=2 
order by Age,Rown 


/* To drop the temporary table */DROP  TABLE #Runners/* Using CROSS APPLY */

IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL drop table #RunnersBig

/* Create a table and enter the millions of sample data for checking the performance */
Create Table #RunnersBig
(
RunnerId integer identity ,
Time integer not null,
Age integer not null
)
go

insert into #runnersbig ( Time , Age )
select top 1000000 ABS ( checksum ( newid ()))% 1000 ,
ABS ( checksum ( newid ()))% 99
from sys . columns a cross join sys . columns b cross join sys . columns c
go
/* Create a Index */
create index idxrunnersbig on #runnersbig ( age , time ) include ( runnerid );

with cteN
as
(
select distinct Age number from #RunnersBig
)
Select *
from cteN cross apply ( Select top ( 2 ) * from #RunnersBig where #RunnersBig . Age = cteN . number order by Time ) as runners
order by cteN . number , runners . Time

Friday, July 30, 2010

Retrieving the checked elements from the RADGrid

foreach (GridDataItem dataitem in radViewUsers.Items)

{

CheckBox chkbox = (CheckBox) dataitem.FindControl("cbSelect");

if (chkbox.Checked)

{

userName = dataitem["UserName"].Text;

Email = dataitem["Email"].Text;

}

Thursday, July 29, 2010

Button click event not firing in RadTabStrip ?

Tuesday, February 2, 2010

Calender popup

Page Load
-----------
btnProjectStartDt.Attributes.Add("onclick", "javascript:popUpCalendar(this,txtProjectStartDt,'dd/mm/yyyy');return false;");

filling the dataset in the report

Filling the dataset in the report
--------------------------------
string constr = ConfigurationManager.ConnectionStrings["Reports"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("CrossTab", con);
ReportDocument rpt = new ReportDocument();

SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

try
{

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectStartDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));
cmd.Parameters.Add("@Enddate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectEndDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));

adap.Fill(ds, "Orders");

if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
rpt.Load(Server.MapPath("CR.rpt"));
rpt.SetDataSource(ds.Tables[0]);

crvCrossTab.ReportSource = rpt;
crvCrossTab.DataBind();
crvCrossTab.Visible = true;
}
else
{
Response.Write("No Record Exist");
crvCrossTab.Visible = false;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();

con = null;

}

Displaying the data in GridView

string constr = ConfigurationManager.ConnectionStrings["Reports"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("Matrix", con);

SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

try
{

cmd.CommandType = CommandType.StoredProcedure;

CultureInfo ci = new CultureInfo("en-US");
cmd.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectStartDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));
cmd.Parameters.Add("@Enddate", SqlDbType.VarChar).Value = DateTime.Parse(txtProjectEndDt.Text, new CultureInfo("en-GB")).ToString("d", new CultureInfo("en-US"));

adap.Fill(ds, "Orders");

if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
gvCrossTab.DataSource = ds.Tables[0];
gvCrossTab.PageSize =10;
gvCrossTab.DataBind();
}
else
{
Response.Write("No Record Exist");
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();

con = null;
}

The Code Consulting

Tuesday, December 21, 2010

ASP.NET Tips

Custom Validator With ClientValidation Property

Friday, December 17, 2010

Use Dynamic SQL to Improve Query Performance & Avoiding Sql Injections, sp_depends

Thursday, December 16, 2010

Linking to the Previous Row

Wednesday, December 15, 2010

STR - for displaying the rounded decimal number

Tuesday, December 14, 2010

Generating Subtotals using Subtotal(in a single query),Grouping

Using Ranking Functions to Deduplicate Data

Monday, December 13, 2010

Usage of ISNUMERIC and how to create ISALLDIGITS functionality

Sunday, December 12, 2010

Sorting Months by Month Number.

Saturday, December 11, 2010

Generating the top X rows from each group.

Friday, July 30, 2010

Retrieving the checked elements from the RADGrid

Thursday, July 29, 2010

Button click event not firing in RadTabStrip ?

Tuesday, February 2, 2010

Calender popup

filling the dataset in the report

Displaying the data in GridView

Search This Blog

ABOUT SIVA SHUNMUGAM

DISCLAIMER

Topics

Followers

Stats

Blog Archive

siva shunmugam's corner

State true or false. EXISTS, SOME, ANY are operators in SQL.

Tuesday, December 21, 2010

Friday, December 17, 2010

Thursday, December 16, 2010

Wednesday, December 15, 2010

Tuesday, December 14, 2010

Monday, December 13, 2010

Sunday, December 12, 2010

Saturday, December 11, 2010

Friday, July 30, 2010

Thursday, July 29, 2010

Tuesday, February 2, 2010

Search This Blog

ABOUT SIVA SHUNMUGAM

DISCLAIMER

Subscribe To

Topics

Followers

Stats

Blog Archive

siva shunmugam's corner

State true or false. EXISTS, SOME, ANY are operators in SQL.