Data Services: Get ready for Microsoft’s new project called Astoria

Today’s programming of web has been changed dramatically with the advancement in technologies like AJAX, RSS, ATOM and Mehsups applications. Some of these are protocols, some are data formats and some are just the aggregators to get data from somewhere and add value on top of it in one way or other.
Microsoft is working on a project code name called “Astoria“. This project aims to provide pure Data Services. Pure Data Services means you can make your data accessible on the web without specifically creating different web services for different type of requests.
It is actually a concept of exposing data totally independent of format in a uniform protocol.

Suppose you have created the data service for your database NorthWind then a user can get the data directly by accessing URI and can navigate to the data hierarchy by adding predicates in web request.

For example (From Microsoft document here):
You want to retrieve the list of customers it will become
http://myserver/data.svc/Customers

Now you want a specific customer of id ALFKI, you will write
http://myserver/data.svc/Customers[ALFKI]

Server will return data in XML,JSON or any other supported format based on user’s request

In this way you can build widgets, meshups,tickers, Flash graphs etc. where UI will natively communicate with data.

This project is in very early stages and you can get more information from here
Astoria FAQ

Validating Inputs and Finding Patterns with PatIndex

PatIndex is one of most powerful functions of TSQL but often developers ignore it firstly because of the availability of CharIndex and secondly, the difficulty of creating patterns for PatIndex to search. Developers try to solve some typical problems faced by today’s web application by developing their own workarounds instead of using PatIndex.
Today, I will share some very useful tricks which can help you to make sure that your input string is according to the pattern you want like all numeric, all letters, starts with letter and end with number etc.

Following are the TSQL scripts which you can use to solve these type of problems. You can convert them to user defined functions as per your requirements:

—Checks that input only contains numbers

if PatIndex(‘%[^0-9]%’,‘11′) > 0
Begin
Print ‘Not all numbers’
End
Else
Begin
Print ‘All numbers’
End

—Checks that input only contains letters
if PatIndex(‘%[^a-z]%’,‘aaaaa’) > 0
Begin
Print ‘Not all letters’
End
Else
Begin
Print ‘All letters’
End

–Checking for mixed input
If PatIndex(‘%[^0-9][0-9]%’,‘abc’) > 0
Begin
Print ‘Alpha numeric data’
End
Else
Begin
Print ‘Either all numbers or all letters’
End

–Checks that value must start with a letter and a number
If PatIndex(‘[^0-9][0-9]%’,‘A1anamwar11′) > 0
Begin
Print ‘Starts with a letter and a number’
End
Else
Begin
Print ‘Does not start with a letter and a number’
End

–Checks that value must End with a letter and a number
If PatIndex(‘%[^0-9][0-9]‘,‘A1anamwar11a1′) > 0
Begin
Print ‘Ends with a letter and a number’
End
Else
Begin
Print ‘Does not End with a letter and a number’
End

–Checks that value must Start with a letter and Ends with a number
If PatIndex(‘[^0-9]%[0-9]‘,‘namwar1′) > 0
Begin
Print ‘Starts with a letter and ends with a number’
End
Else
Begin
Print ‘Does not start with a letter and ends with a number’
End

Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’
End
Else
Begin
–Column already exists. leave the table as it is.
Print ‘Column already exists.’
End
Go

Drop table testTable
Go