.net 4.0 features list

Following are list of Major Changes in .Net 4.0

• ControlRenderingCompatabilityVersion Setting in the Web.config File 
• ClientIDMode Changes 
• ASP.NET Request Validation 
• Clean Web.Config Files
• Starter Project Templates
• Multi-targeting
• Multiple Monitor Support
• New Code Focused Web Profile Option
• HTML / ASP.NET / JavaScript Code Snippets
• Auto-Start ASP.NET Applications
• URL Routing with ASP.NET 4 Web Forms
• Searching and Navigating Code in VS 2010
• VS 2010 Code Intellisense Improvements
• WPF 4
• Add Reference Dialog Improvements
• SEO Improvements with ASP.NET 4
• Output Cache Extensibility with ASP.NET 4
• Built-in Charting Controls for ASP.NET and Windows Forms
• Cleaner HTML Markup with ASP.NET 4 - Client IDs
• Optional Parameters and Named Arguments in C# 4 - and a cool scenarios with ASP.NET MVC 2
• Automatic Properties, Collection Initializers and Implicit Line Continuation Support with VB 2010
• New <%: %> Syntax for HTML Encoding Output using ASP.NET 4
• JavaScript Intellisense Improvements with VS 2010

Get Random Records from SQL Server

How to get random records from SQL Server based on Where Clause.
Let's say you have a database of customers and you initiated a contest wherein you determine the winners by randomly selecting 5 customers from your database.  How would you go about selecting 5 random records from your customers table?

In SQL Server, it is quite easy to do this thanks to the NEWID() system function.  The NEWID() system function creates a unique value of type uniqueidentifier.  There's no need to add a new column to your table just to have the ability of randomly selecting records from your table.  All that needs to be done is include the NEWID() system function in the ORDER BY clause when doing your SELECT statement. 

Syntax

Select Top [No. of Random Records you want]  *
from
(
Select  Col1, Col2
from ActualTableName
where
Col1 Like '%SearchWord%'
) VirtualTable
ORDER BY NEWID()
Note: VirtualTable is table that doesn't exist in database, it is just a placeholder name.


Example
Select Top 1 *
from
(
Select QuestionId, QuestionTitle
from ForumQuestion
Where
ForumQuestion.QuestionTitle Like @SearchKeyword
) MyTable
ORDER BY NEWID()

Naming conventions in c# and sql server

Naming Conventions which we need to follow in every language.

Similarly for C# and sql server we have some specifications to follow.

In this article i am explaining the each one with example.

First let’s understand different types of casing styles.

• 1)    UpperCase – All letters in uppercase. Example: ISITEMREQUIRED
• 2)     LowerCase – All letters in lowercase. Example: isitemrequired
• 3)     CamelCase – first letter in identifier is lowercase and each subsequent concatenated word is capitalized.  Example: isItemRequired
• 4)     PascalCase – first letter in identifier and each subsequent concatenated word is capitalized.  Example: IsItemRequired

Naming Conventions for C# Coding

Identifier	Casing Style to use	Example
Local variable declarations	Camel casing	userName
Private variables	Camel casing	statusMessage
Property declaration	pascal casing	ForeColor
Public variables	Pascal casing	ErrorCode
Const, Static or Readonly fields	Pascal casing	IsMembershipRequired
Method Name	Pascal casing	ProcessApplication()
Enum	Pascal casing	MembershipLevels
Class Name	Pascal casing	MemberDetails
Interface	Pascal casing	IDisposable, *Using “I” in front of interface name to avoid confusion between other class, while inheriting.
Events	Pascal casing	SubmitButtonClick, use “Functionality name” + “Event name”
Namespace	Pascal casing	CompanyName.ProjectName

To summarize it easily, anything which is public in nature then use pascal casing.  Avoid using underscore “_” or hyphen “-“ while naming identifier.

Naming Conventions for Database(sql server)

Table name convention.

·         It should be in UpperCase

·         It should not have Spaces

·         Multiple words should be split with Underscore, since some of DB Client always shows DB name in uppercase, using case will not be good choice.

·         It should be Plural (more than one in number) - Example: EMPLOYEES Table, rather than EMPLOYEE.  If it contains multiple words only last word should be plural.  Example: EMPLOYEE_PHOTOS

Field name convention.

·         It should not have Spaces

·         Multiple words should be split with Underscore.

·         It should be Singular - Example: EMPLOYEE_ID column name, rather than EMPLOYEES_ID or EMPLOYEE_IDS.

Procedure name convention

·         Procedure name should be defined as TableName_ProcedureFunctionalityName.  Example: Employees_SelectAll,  Employees_Insert, Employees_Update, Employees_Delete.  If table name is too long, it is also better to use short name of table rather than full tablename prefix, Example: Emp_SelectAll, Emp_Insert.  If table name contains multiple words like Employee_Locations then it is better to give name like EL_SelectAll, EL_Insert.  If short name are getting duplicate, then you can change of one of short name to avoid duplication or confusion.

·         If you are creating procedure which is general in nature or combines 2 or more tables or mainly business logic which cannot be associated with any table, then it is better to use as BusinessLogicName_ProcedureFunctionalityName.  Example:  procedure for employees quarterly sales report should be named something like Reports_Emp_Quaterly_Sales.  That way you can combine all reports procedure together to easily find them in a complex database structure.

·         Remember, naming conventions is to help finding things easily and a standard which can be easily explain to anyone joining a development team.  So always name considering this scenario in mind.

Function name convention

·         Function name are mostly generic utilities, but incase if they are associated with table, then follow procedure naming conventions approach, else use meaningful name.  Example:  AgeFromDOB  - If you pass a valid date, this function will return age, no. of years between current date and DOB.

Primary Key convention

·         Primary key should be name as PK_TableName.  Example:  PK_Employees.   If you are using SQL Server, whenever you are creating primary key in table designer, it will automatically follows above naming convention.

Foreign Key convention

·         Foreign key should be name as FK_PrimaryTableName_ForeignTableName.  Example:  PK_Employees_Departments.   If you are using SQL Server, whenever you are creating foreign key in table designer, it will automatically follows above naming conventions.

Constraint name convention

·         Constraint name should be name as ConstraintShort_ConstraintColumnName.  Example: 

Default value constraint for IsActive column field in employe table should be 1 (or true).  DF_IsActive.  Here DF stands for Default value constraint and IsActive is column field in Employees Table.

Index name convention

·         Index name should be name with prefix idx_ColumnName.  Example: 

Idx_Employee_ID

Joins in sql server

Introduction:

In this post I will explain what are the Joins in SQL Server and different types of Joins example (SQL LEFT outer Join, SQL RIGHT outer Join, SQL FULL outer Join, SQL Cross Join, SQL inner Join sample, Self Join example) and uses of Joins in SQL Server.

Description:

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use  primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

Before enter into Joins concept first design two tables in database and enter data like as shown below

Create one table with primary key and give name as “UserDetails”

UserID	UserName	FirstName	LastName
1	RajashekarBandi	Rajashekar	Bandi
2	ChanduDodda	Chandu	Dodda
3	MaheshBandi	Mahesh	Bandi

Here UserID is the Primary key in UserDetails table

After that create another table with Foreign Key and give name as OrderDetails

OrderID	OrderNo	UserID
1	543224	1
2	213424	2
3	977776	3
4	323233	3
5	998756	1

Here OrderID is the Primary key and UserID is the foreign key in OrderDetails table.

SQL contains different types of Joins we will see each concept with example by using above tables.

Types of Joins

            1) Inner Joins

            2) Outer Joins

            3) Self Join

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.

Syntax for Inner Join

SELECT t1.column_name,t2.column_name FROM table_name1 t1 INNER JOIN table_name2 t2 ON t1.column_name=t2.column_name

Now check the below query for inner join

Example

SELECT u.UserName,u.LastName,o.OrderNo FROM UserDetails u INNER JOIN OrderDetails o ON u.UserID=o.UserID

Once we run that query our output will be like this

UserName	LastName	OrderNo
RajashekarBandi	Bandi	543224
ChanduDodda	Dodda	213424
MaheshBandi	Bandi	977776
MaheshBandi	Bandi	323233
RajashekarBandi	Bandi	998756

We can write our inner join query like this also it will give same result

SELECT u.UserName,u.LastName,o.OrderNo FROM UserDetails u JOIN OrderDetails o ON u.UserID=o.UserID

Based on above result we can say that INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "UserDetails" that do not have matches in "OrderDetails", those rows will NOT be listed.

In inner Join we are having different types of Joins those are

          1) Equi Join

2        2) Natural Join

           3) Cross Join

Equi Join

 The Equi join is used to display all the matched records from the joined tables and also display redundant values. In this join we need to use * sign to join the table.

Syntax for Equi Join

SELECT * FROM table_name1 t1 INNER JOIN table_name2 t2 ON t1.column_name=t2.column_name

Now check the below query for Equi join

Example

SELECT * FROM UserDetails u INNER JOIN OrderDetails o ON u.UserID=o.UserID

Once we run above query our output will be like this

UserID	UserName	FirstName	LastName	OrderID	OrderNo	UserID
1	RajashekarBandi	Rajashekar	Bandi	1	543224	1
2	ChanduDodda	Chandu	Dodda	2	213424	2
3	MaheshBandi	Mahesh	Bandi	3	977776	3
3	MaheshBandi	Mahesh	Bandi	4	323233	3
1	RajashekarBandi	Rajashekar	Bandi	5	998756	1

In equi join we need to use only equality comparisons in the join relation. If we use other operators such as (<,>) for our comparison condition then our Joins disqualifies for equi join.

Natural Joins

The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values.

Syntax for Natural Join

SELECT * FROM table_name1 t1 NATURAL JOIN table_name2 t2

Example

SELECT * FROM UserDetails NATURAL JOIN OrderDetails

Note: These NATURAL Joins won’t work in our SQL Server (only supports in Oracle) it will throw syntax error. If you observe above code "NATURAL" is not highlighted, indicating that it is not recognized as a keyword.

Cross Join

A cross join that produces Cartesian product of the tables that involved in the join. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

Syntax for Cross Join

SELECT * FROM table_name1 CROSS JOIN table_name2

 Or we can write it in another way also

SELECT * FROM table_name1,table_name2

Now check the below query for Cross join

Example

SELECT * FROM UserDetails CROSS JOIN OrderDetails

Or

SELECT * FROM UserDetails, OrderDetails

Once we run that query our output will be like this

UserID	UserName	FirstName	LastName	OrderID	OrderNo	UserID
1	RajashekarBandi	Rajashekar	Bandi	1	543224	1
1	RajashekarBandi	Rajashekar	Bandi	2	213424	2
1	RajashekarBandi	Rajashekar	Bandi	3	977776	3
1	RajashekarBandi	Rajashekar	Bandi	4	323233	3
1	RajashekarBandi	Rajashekar	Bandi	5	998756	1
2	ChanduDodda	Chandu	Dodda	1	543224	1
2	ChanduDodda	Chandu	Dodda	2	213424	2
2	ChanduDodda	Chandu	Dodda	3	977776	3
2	ChanduDodda	Chandu	Dodda	4	323233	3
2	ChanduDodda	Chandu	Dodda	5	998756	1
3	MaheshBandi	Mahesh	Bandi	1	543224	1
3	MaheshBandi	Mahesh	Bandi	2	213424	2
3	MaheshBandi	Mahesh	Bandi	3	977776	3
3	MaheshBandi	Mahesh	Bandi	4	323233	3
3	MaheshBandi	Mahesh	Bandi	5	998756	1

Outer Joins

A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.

We are having three types of Outer Joins

            Left Outer Join

             Right Outer Join

             Full Outer Join

Left Outer Join

The left outer join displays all the rows from the first table and matched rows from the second table.

Syntax for Left Outer Join

SELECT Column_List FROM table_name1 t1 LEFT OUTER JOIN table_name2 t2 ON t1.column_name=t2.column_name

Now check the below query for Left Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo FROM UserDetails u LEFT OUTER JOIN OrderDetails o ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID	UserName	OrderNo
1	RajashekarBandi	543224
1	RajashekarBandi	998756
2	ChanduDodda	213424
3	MaheshBandi	977776
3	MaheshBandi	323233

Right Outer Join

The right outer join displays all the rows from the second table and matched rows from the first table.

Syntax for Right Outer Join

SELECT Column_List FROM table_name1 t1 RIGHT OUTER JOIN table_name2 t2 ON t1.column_name=t2.column_name

Now check the below query for Right Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo FROM UserDetails u RIGHT OUTER JOIN OrderDetails o ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID	UserName	OrderNo
1	RajashekarBandi	543224
2	ChanduDodda	213424
3	MaheshBandi	977776
3	MaheshBandi	323233
1	RajashekarBandi	998756

Full Outer Join

Full Outer Join displays all the matching and non matching rows of both the tables.

Syntax for Full Outer Join

SELECT Column_List FROM table_name1 t1 FULL OUTER JOIN table_name2 t2 ON t1.column_name=t2.column_name

Now check the below query for Full Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo FROM UserDetails u RIGHT OUTER JOIN OrderDetails o ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID	UserName	FirstName	LastName	OrderID	OrderNo	UserID
1	RajashekarBandi	Rajashekar	Bandi	1	543224	1
1	RajashekarBandi	Rajashekar	Bandi	5	998756	1
2	ChanduDodda	Chandu	Dodda	2	213424	2
3	MaheshBandi	Mahesh	Bandi	3	977776	3
3	MaheshBandi	Mahesh	Bandi	4	323233	3

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

To implement self join first design table and give a name as “EmployeeDetails”

EmpID	EmpName	EmpMgrID
1	Rajashekar	2
2	Chandu	4
3	Mahesh	2
4	Sai	1
5	Nagaraju	1
6	Mahendra	3
7	Sanjay	3

Now I want to get manager names of particular employee for that we need to write query like this

select e2.EmpName,e1.EmpName as 'Manager' from EmployeeDetails e1 INNER JOIN EmployeeDetails e2 on e1.EmpID=e2.EmpMgrID

Here if you observe above query EmployeeDetails table joined itself using table aliases e1 and e2.

After that run our query output will be like this

EmpName	Manger
Sai	Rajashekar
Nagaraju	Rajashekar
Rajashekar	Chandu
Mahesh	Chandu
Mahendra	Mahesh
Sanjay	Mahesh
Chandu	Sai