Friday, March 18, 2011

Authentication vs. Authorization

I was asked this question at one of my job interviews. So I decided to look it up and settle it once and for all.

Authentication is the mechanism where the system may securely identify their users. Basically the system asks:

* Who is the user?
* Is the user really who he/she represents himself to be?

The answers to these questions are unique information known only between the user and the system. Examples include password, finger print, retinal scan...etc.

Authorization is the mechanism where the system determines the level of access to grant an authenticated user. In a nut shell, the system asks:

* Is user X authorized to access resource R?
* Is user X authorized to perform operation P?
* Is user X authorized to perform operation P on resource R?

Monday, March 14, 2011

CSS - when to use class, id?

Use IDs when there is only one occurrence per page (eg. menu, content pane, banner). Use classes when there are one or more occurrences per page.

Wednesday, March 9, 2011

Query to generate table with all dates between date range

This crazy query basically creates 2 temporarily tables:
1. table with all dates between date range
2. table with data we want

These 2 table are then joined to give the number of requests made group by day.

here it goes...

SELECT CONVERT(varchar(10), derivedtbl_1.date, 101) AS date,
derivedtbl_2.count

FROM (SELECT DISTINCT TOP (100) PERCENT DATEADD(dd, Days.Row, DATEADD(mm, Months.Row, DATEADD(yy, Years.Row, @startDate))) AS date
FROM (SELECT 0 AS Row
UNION ALL
SELECT 1 AS Expr1
UNION ALL
SELECT 2 AS Expr1
UNION ALL
SELECT 3 AS Expr1
UNION ALL
SELECT 4 AS Expr1
UNION ALL
SELECT 5 AS Expr1
UNION ALL
SELECT 6 AS Expr1
UNION ALL
SELECT 7 AS Expr1
UNION ALL
SELECT 8 AS Expr1
UNION ALL
SELECT 9 AS Expr1
UNION ALL
SELECT 10 AS Expr1
UNION ALL
SELECT 11 AS Expr1
UNION ALL
SELECT 12 AS Expr1
UNION ALL
SELECT 13 AS Expr1
UNION ALL
SELECT 14 AS Expr1
UNION ALL
SELECT 15 AS Expr1
UNION ALL
SELECT 16 AS Expr1
UNION ALL
SELECT 17 AS Expr1
UNION ALL
SELECT 18 AS Expr1
UNION ALL
SELECT 19 AS Expr1
UNION ALL
SELECT 20 AS Expr1) AS Years INNER JOIN
(SELECT 0 AS Row
UNION ALL
SELECT 1 AS Expr1
UNION ALL
SELECT 2 AS Expr1
UNION ALL
SELECT 3 AS Expr1
UNION ALL
SELECT 4 AS Expr1
UNION ALL
SELECT 5 AS Expr1
UNION ALL
SELECT 6 AS Expr1
UNION ALL
SELECT 7 AS Expr1
UNION ALL
SELECT 8 AS Expr1
UNION ALL
SELECT 9 AS Expr1
UNION ALL
SELECT 10 AS Expr1
UNION ALL
SELECT 11 AS Expr1) AS Months ON DATEADD(mm, Months.Row, DATEADD(yy, Years.Row, @startDate)) <= @endDate INNER JOIN
(SELECT 0 AS Row
UNION ALL
SELECT 1 AS Expr1
UNION ALL
SELECT 2 AS Expr1
UNION ALL
SELECT 3 AS Expr1
UNION ALL
SELECT 4 AS Expr1
UNION ALL
SELECT 5 AS Expr1
UNION ALL
SELECT 6 AS Expr1
UNION ALL
SELECT 7 AS Expr1
UNION ALL
SELECT 8 AS Expr1
UNION ALL
SELECT 9 AS Expr1
UNION ALL
SELECT 10 AS Expr1
UNION ALL
SELECT 11 AS Expr1
UNION ALL
SELECT 12 AS Expr1
UNION ALL
SELECT 13 AS Expr1
UNION ALL
SELECT 14 AS Expr1
UNION ALL
SELECT 15 AS Expr1
UNION ALL
SELECT 16 AS Expr1
UNION ALL
SELECT 17 AS Expr1
UNION ALL
SELECT 18 AS Expr1
UNION ALL
SELECT 19 AS Expr1
UNION ALL
SELECT 20 AS Expr1
UNION ALL
SELECT 21 AS Expr1
UNION ALL
SELECT 22 AS Expr1
UNION ALL
SELECT 23 AS Expr1
UNION ALL
SELECT 24 AS Expr1
UNION ALL
SELECT 25 AS Expr1
UNION ALL
SELECT 26 AS Expr1
UNION ALL
SELECT 27 AS Expr1
UNION ALL
SELECT 28 AS Expr1
UNION ALL
SELECT 29 AS Expr1
UNION ALL
SELECT 30 AS Expr1) AS Days
ON DATEADD(dd, Days.Row, DATEADD(mm, Months.Row, DATEADD(yy, Years.Row, @startDate))) <= @endDate
WHERE (DATEADD(yy, Years.Row, @startDate) <= @endDate)
ORDER BY date) AS derivedtbl_1

LEFT OUTER JOIN

(SELECT COUNT(*) AS count, CONVERT(varchar(10), b.DateRequested, 101) AS date

FROM [TDBK-SQLDA-CX13.P-TDBFG.COM\CX13].[IS].dbo.T_Team101_Request_Assistance_Group a INNER JOIN
[TDBK-SQLDA-CX13.P-TDBFG.COM\CX13].[IS].dbo.T_Team101_Request_Assistance b ON a.RequestID = b.id INNER JOIN
[TDBK-SQLDA-CX13.P-TDBFG.COM\CX13].[IS].dbo.T_C_Team101_Groups c ON a.Team101ID = c.id

WHERE (b.DateRequested <= DATEADD(day, 1, @endDate)) AND
(b.DateRequested >= @startDate) AND
(c.GroupName = @group)

GROUP BY b.DateRequested) AS derivedtbl_2

ON CONVERT(varchar(10), derivedtbl_1.date, 101) = CONVERT(varchar(10), derivedtbl_2.date, 101)

Thursday, February 24, 2011

Reportviewer print - blank pages

If a blank page is inserted after printing each page of a report (created with the Reportviewer control in Visual Studio 2010), narrow the width of the report in .rdlc file.

Friday, February 18, 2011

Learning stored procedures

Advantages of sp:
- capable of handling very complex queries
- utilizes resources of the db server not the app server
- convenient for moving app from platfrom to platform - business logic stays with the db

Sp structure:
CREATE PROCEDURE [PROCEDURE NAME]

/*
variables:
@variableName type
*/

AS

/*
SQL statements
*/

Wednesday, February 16, 2011

What is ADO .NET Entity Framework

- a set of data access APIs
- targeting ADO .NET 4.0
- EFv4 (2nd edition) shipped with .NET 4.0 and VS 2010
- Entity Data Model is treated indepenently from its underlying datastore

WHAT IS LINQ??

- stands for Language Integrated Query
- able to query objects, ADO .NET (dataset, database, entities), XML

LINQ - http://msdn.microsoft.com/en-us/magazine/cc337893.aspx

LINQ to Entities - http://www.linqdev.com/PublicPortal/publicportal/linq-to-entities.aspx