Monday, November 14, 2011

Paging using Database - Basis on Page Number and Page Size

Create Proc [dbo].[uspCustomersWithPaging_Select]
@PageNumber int,
@PageSize int

as

DECLARE @sqlStatement nvarchar(max)
Declare @ErrNo Int
SET NOCOUNT ON
Declare @RowCounter int
Declare @Rec_count int
Declare @Rec_start int, @Rec_End int
set @Rec_start=((@PageNumber-1)*@PageSize)+1
set @Rec_End = @Rec_start + @PageSize -1

Select
@RowCounter=Count(CustomerId)
From
tblCustomers
Begin
With Cte
As
(
SELECT ROW_NUMBER() OVER (order by tblCustomers.CustomerId asc ) AS RowNumber,
CONVERT(varchar(9), @RowCounter) as 'RowCounter' ,
tblCustomers.CustomerId,
Name,
Address1,
Phone,
Mobile,
isNull((Select Sum(isNull(Amount,0)) from tblCustomerCommission where tblCustomerCommission.CustomerId=tblCustomers.CustomerId),0) as 'TotalCommission'
from
tblCustomers
)

Select
RowCounter,
CustomerId,
Name,
Address1,
Phone,
Mobile,
TotalCommission
From
CTE
Where RowNumber between @Rec_start and @Rec_End
End

Wednesday, November 9, 2011

How To Handle Multiple Results by Using the DataReader

NextResult() function is used for handle m results by using the DataReader

Example:-

SqlDataReader reader = cmd.ExecuteReader();
do
{

while (reader.Read())
{
for (int field = 0; field < reader.FieldCount; field++)
{
// htmlStr.Append(reader.GetName(field).ToString());
// htmlStr.Append(reader.GetValue(field).ToString());
}
}
htmlStr.Append("

"
);
i++;
} while (reader.NextResult());


reader.Close();

Debugging in Firefox & Internet Explorer

Add Firebug, measure it and web developer toolbar for Firefox (add-ons)

Add developer toolbar for Internet Explorer

Tuesday, November 8, 2011

Aggregate Functions (MAX, MIN, SUM, COUNT) on DataTable

DataTable has a public method called Compute. We can use the aggregate functions in this method as,

StudentTable.Compute("MAX(Row_ID)", "")


public Object Compute(
string expression,
string filter
)


expression (The expression parameter requires an aggregate function. )
Type: System.String
The expression to compute.

filter (filter, determines which rows are used in the expression)
Type: System.String
The filter to limit the rows that evaluate in the expression.

Example

private void ComputeBySalesSalesID(DataSet dataSet)
{
// Presumes a DataTable named "Orders" that has a column named "Total."
DataTable table;
table = dataSet.Tables["Orders"];

// Declare an object variable.
object sumObject;
sumObject = table.Compute("Sum(Total)", "EmpID = 5");
}

Thursday, October 8, 2009

Dalhousie Khajjiar Trip (1-4 Oct 2009)


Kamal Jindal






Kamal Jindal & Anuj (My Friend)

Tuesday, July 7, 2009