Search content within the blog

Friday, June 3, 2011

SQL Concepts with examples

VIEWS
--------
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No


INDEX
------
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)


CURSORS
---------
Declare @name varchar(32)
declare cursorname cursor read_only for
select name from Ashwin

open cursorname
fetch NEXT from cursorname into @name
while(@@Fetch_status=0)
begin
print @name
fetch next from cursorname into @name
end
close cursorname
deallocate cursorname

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

Triggers
---------
Create TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserrted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color+'altered', Material+'altered'
FROM inserted
END

CREATE TRIGGER [trglogin]
ON [dbo].[tbl_Login]
FOR UPDATE
AS
BEGIN
if((UPDATE(col1))
begin
print 'col1 is updated..'
end
if((UPDATE(col2))
begin
print 'col2 is updated..'
end
end




User defined functions
--------------------------
Create Function dbo.Factorial (@number int)
Returns int
As
BEGIN
Declare @finalResult int
If @number < 2
Select @finalResult = @number
Else
Select @finalResult = @number * dbo.factorial(@number - 1)

Return @finalResult
END
GO
Select dbo.Factorial (8)


Create Function dbo.TitleTable (@title_id Varchar(6))
Returns @ReturnTable Table (title_id Varchar(6),
title Varchar(80),
type Char(12))
As
BEGIN
Insert @ReturnTable
Select title_id, title, type From titles where title_id=@title_id
order by 1
Return
End
GO
Select * from dbo.TitleTable('BU1032')

C# 3.0 Features: Object Initializers

C# 3.0 is just around the corner so I thought I'd start writing about a few of the features that it exposes and provide quick and concise examples of how they can be used. Many of the new features rely on the compiler to generate code that you would have had to write in the past. This can result in significantly less code compared to C# 2.0 syntax in many cases. Here's a list of some of the key features available in C# 3.0:

* Object Initializers
* Anonymous Types
* Automatic Properties
* Extension Methods
* Lambda Expressions
* LINQ
* Collection Initializers

In this post I'll discuss the fundamentals of object initializers in C# 3.0. To get started, let's look at the standard way of initializing an object with data in C# 2.0 using constructors. The following example creates a Person object and passes three values to its constructor.

Person p = new Person("John", "Doe", "602-123-1234");

As mentioned, C# 3.0 now supports the concept of "object initializers" which means you can easily assign data to specific properties in a type with having to create an explicit constructor (you can of course still create constructors as well). The standard C# { and } brackets are used to create object initializers. Here's an example of using an object initializer to assign property data to a Person type. It's nice because doing the same thing without using a constructor in C# 2.0 would have resulted in around 5 lines of code which is too many for something simple like property value assignments.

Person p = new Person() {FirstName="John",LastName="Doe",Phone="602-123-1234",City="Phoenix"};

If the Person type defines a sub object as a property you can even use object initializers to create the sub object. Here's an example of defining an Address object:

Person p = new Person()
{
FirstName = "John",
LastName = "Doe",
Address = new Address()
{
Street = "1234 St.",
City = "Phoenix"
}
};


If you've used JavaScript Object Notation (normally referred to as JSON) in AJAX or other client-side applications, you'll recognize this style of syntax. It's simple, compact, and easy to use. Although I formatted the example above onto multiple lines to maximize readability, it could certainly be compacted quite a bit if desired.