Archive for August, 2011

Deploying asp.net web application in a new server like w2k3, ensure following steps incase if you see HTTP Error 404 – File or directory not found error …

1. Allow asp.net v2.0.50727 under Web Service Extensions section in IIS

2. Run aspnet_regiis -i @ command prompt from the respective (32-bit/64-bit) frameworks directory

3. Set proper asp.net version at the virtual directory level

http://msdn.microsoft.com/en-us/library/ms178473.aspx – IIS 5.0/IIS 6.0

http://msdn.microsoft.com/en-us/library/bb470252.aspx – IIS 7.0

String.prototype.format = function() {
    var args = arguments;
    return this.replace(/{(\d+)}/g, function(match, number) {
        return typeof (args[number] != 'undefined') ? args[number] : '{' + number + '}';
    });
};
Extending javascript global object is not a good idea. This can be defined as a utility funciton with in your application's global js object/file.

Extending  Reference - http://stackoverflow.com/questions/610406/javascript-equivalent-to-printf-string-format

http://www.asp.net/data-access/tutorials/efficiently-paging-through-large-amounts-of-data-cshttp://stackoverflow.com/questions/548475/efficient-way-to-implement-paging

http://msdn.microsoft.com/en-us/library/ms178472.aspx

http://msdn.microsoft.com/en-us/library/dct97kc3.aspx

Achieve pagination directly in SQL Server using different approaches ...

1. ROW_NUMBER() approach with inner join
SELECT ees.* FROM saleslt.customer ees
JOIN (SELECT customerid, rownum =ROW_NUMBER() over (ORDER BY customerid) FROM saleslt.customer ) eelist
ON eelist.customerid = ees.customerid
WHERE eelist.rownum BETWEEN 21 AND 30

2. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 1
declare @rowsPerPage int, @pageNo int
SET @rowsPerPage = 10
SET @pageNo = 59
begin
WITH SQLPaging
AS
(
SELECT TOP(@rowsPerPage * @pageNo) ResultNum = ROW_NUMBER() OVER (ORDER BY customerid), * FROM saleslt.customer
)
SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNo - 1) * @rowsPerPage)
end

3. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 2
WITH CTE_Customers
AS
(
SELECT CEILING(ROW_NUMBER() OVER (ORDER BY customerid ASC))/10 AS PageNumber, * FROM saleslt.Customer
)
SELECT * FROM CTE_Customers WHERE PageNumber = 10

4. Using sub query
SELECT * FROM
   (SELECT TOP 10 * FROM
     (SELECT TOP 30 * FROM saleslt.customer order by CustomerID asc) AS T2
	ORDER BY customerid DESC) T3
ORDER BY customerid ASC

5/6. Generalizing sub query approach using dynamic query in stroedproc, temporary table approach using dynamic query in storedproc
http://www.beansoftware.com/ASP.NET-Tutorials/Paging-Stored-Procedures.aspx

7. Cursor approach can be seen
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

8. ROWCOUNT approach
DECLARE @Sort int /* the type of the sorting column */
SET ROWCOUNT 21
SELECT @Sort = CustomerID FROM saleslt.customer  ORDER BY CustomerID
SET ROWCOUNT 10
SELECT * FROM saleslt.customer WHERE CustomerID >= @Sort ORDER BY CustomerID
Other References
Getting department wise top salaried employee records ...
DECLARE @EMPLOYEE TABLE (
EmployeeId INT,
DepartmentId INT,
EmpSal MONEY
)
INSERT INTO @EMPLOYEE (EmployeeId,DepartmentId,EmpSal)
SELECT 100, 1, 25000 UNION ALL
SELECT 101, 1, 35000 UNION ALL
SELECT 102, 1, 20000 UNION ALL
SELECT 103, 2, 27000 UNION ALL
SELECT 104, 2, 37000 UNION ALL
SELECT 105, 2, 26000

SELECT  departmentid, employeeid, empsal FROM
(SELECT departmentid, employeeid, empsal, DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY empsal desc)  Salrank FROM @EMPLOYEE) EmployeeDataWithRank
WHERE salrank = 1;