Thursday, March 27, 2008

Ranking Functions : ROW_NUMBER

One of the features that was included in SQL Server 2005 were ranking functions and I bumped into one of these last week, The ROW_NUMBER().
This function allows you to provide a sequential integer value to the results of your query. This comes in handy when you want to rank your data according to some criteria.
Let me throw an example…I have a table that has a set of student marls, I can use a query shown below to actually rank the students according to there marks.

SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Rank,
StudentName, Score
FROM Students
ORDER BY StudentName.

In the OVER() clause you can specify multiple order-by clauses.
This function also as its use when it comes to pagination of data. Take a look at the query below.

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, StundentName) AS rownum,
StudentName, Score
FROM Students) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, StudentName.

This query fetches the rows between 4 to 6.

You can find more about ranking functions here.

Wednesday, March 26, 2008

IIS 7 admin pack

One of the core features of IIS 7 is that developers can write .Net modules and plug it in and extend the web server’s functionalities.

This also meant the MS can release future pack to easily extend IIS features.

And last week MS released a technical preview for the IIS 7 admin module; this includes some cool features that web developers can benefit. Some of the new features include:

1)       Built in report visualization of IIS log reporting.

2)       A configuration editor that provides control over editing configuration files (web.config).

3)       Built-in SQL Server database management, including the ability to create, delete, and edit tables and indexes, create/edit SPROCs and execute custom queries - which means you can use the module to remotely manage your hosted applications

4)       And more…

Monday, March 24, 2008

Wrestle Mania 24

My favorite sport is wrestling, and really I don’t care if it’s real or not…as long as I enjoy it. Just like the world cup in cricket, in WWE it’s Wrestle Mania, and this time the event is taking place on the 30th of this month.

There is going to be some real matches this time and some of the ones that I will not try to miss are:

Batista vs Umaga

Edge vs The Undertaker for the World Heavy Weight Championship

John Cena vs Triple H vs Randy Orton for the WWE Championship

You can find more information on WWE

Wednesday, March 19, 2008

Tech-talk on LINQ

I presented a Tech-talk to my fellow Virtusan’s on LINQ last Monday. Around 25 people came in and in no time the place was turned it to a question base. Well, fortunately I was prepared and it was great answering those questions. Some of the areas I covered were on query expression, LINQ to objects and LINQ to SQL. The presentation was planned for 1 hour but it took an additional 20 minutes for me to finish but still there was something more for me to tell them.

Today I got the rating and the comments from the people who attended and it was 4.25 out of 5, not bad for the first Tech-talk. Anyway most of the comments indicated that they wanted to learn more about LINQ and at the end of the day I was happy that I was able to do something that other people really like.

 

Friday, March 14, 2008

Weekend at Ahungalla

This weekend the the whole account here at Virtusa are leaving for "Heritance" at Ahungalla to take a day off from the "busy" work life. It seems that there is going to be a quiet a lot of fun planned.
However I will not be able to join them, as I got a family function to attend to...ye ..bad luck.
Last time we went on our quartely project trip, it was fun though...thought of posting a pic out of that trip. Belive me I was not drinking !!!



Thursday, March 13, 2008

One-to-Many mapping in LINQ

Thought of posting about how you can map a simple one-many reletionship in LINQ.
Lets take for an example the relationship between a country and its cities, which is one to many, one country can have many cities, If I define a class called Country and City and then ofcourse two table called Country and City,
I can map the reletionship in a simple way that looks like this

[Table]
class Country
{
[Column(IsPrimaryKey=true)]
public int CountryId{get; set;}

[Colum]
public string CountryName{get; set;}

[Association(OtherKey="CountryId")]
public EnitySet Cities{get; set;}
}

I guess the example explains it all. The Association attribute on the cities property actually
iniitiates this reletionship and you can see the return type is a EntitySet of type City.
The OtherKey property of the Association attribute is used to provide the property name on the
City class which should be comapred with the CountryId in the Country class.
The names of all the names of types in the class above exactly map to the names in the database i.e
there is a Country table and a CountryId column in the table.
Make sure to set the IsPrimaryKey=true to the correct field.

Tuesday, March 11, 2008

Automatic Properties

I was writing a piece of test code in VS2008 and the prop code snippet want just generate the private field, did some goggling around and found this out.
Well, you may remember in the days of VS2003 when you had to write your private fields and then write the property getter and setter for it, that was cumbersome, I remember when it became to hard for me to write all these properties and I had to write a tool to generate the properties (not a very cool tool though). What’s worse some properties only set the passed parameter into the private field and return it in the getter, like this

private string studenName_;

public string StudentName

{

get {return studentName_;}

set {studentName_ = value;}

}

In .Net 3.5 , through a feature called Automatic properties you can write the above code as

public string StudentName { get; set; } and

at compile time the compiler will emite code to construct a privet field.

Pre-Allocated

Not much of work these days, the truth is there is no work at all. I already blogged about the project I am currently allocated being closed off at the end of March, however, I am “sort” of pre-allocated to another project again from the same client I was working on earlier Surprisingly, the project is called “Sorcerer”.

This one is a bit on the domain and there seems to be new modules coming in for development, but again this is again on .NET 1.1, but I agreed on it for the only reason that I can’t still figure out.

Anyway, this is supposed to be going on until July or August, but I doubt it…

And again I am preparing for a tech-talk that I am going to present on LINQ next week, so I am a bit hooked up on the presentations and some more reading on LINQ. So for the moment I have run out of work but I guess they are not planning to keep me in this state for long.

Tuesday, March 4, 2008

LINQ to SQL

This is an abstract of LINQ to SQL; this provides a better way to linq your entites class to the database tables. For example lets say you have

a table called “tbl_customer” with fields “customer_name” and “customer_id”, you would map your entity class “Customer” as follows

[Table(Name=”tbl_customer”)]

public class Customer

{

[Column(Name=”customer_id”)] public int CustomerId;

[Column(Name=”customer_name”)] public string CustomerName

}

You can query you database by making use of the DataContext class, like this…

DataContext db = new DataContext(databaseConnectinString);

Table<Customer> customers = db.GetTable<Customer>();

var query = from cust in customers

where cust.CustomerId = 12

select cust;

Simple right?

This is a simple snippet of what you can do, but LINQ to SQl provides more flexibility, like sql type conversions, complied queries etc.

Some of you would not like the mapping that done in code…I don’t either, you can use xml mapping files to do this mapping. I will post something on this soon.