What is the difference between Web Farm and Web Garden ?

I have been asked this question many times by different readers of my blog. They wanted to know about the fundamentals of Web Farms and Web Garden . In this blog post  I am going to explain the what is the exact difference between web farm and web garden, what are the advantages and disadvantages of using them. I have also described how to create web garden in different version of IIS.
Overview :
Visual Studio having its own integrated ASP.NET engine which is used to run the ASP.NET Web application from Visual Studio. ASP.NET Development Server  is responsible for execute all the request and response from client. Now after the end of development, when you want to host the site on some server to allow other peoples to access, concept of web servers comes in between.  A web server is responsible for  provide the response for all the requests that are coming from clients. Below diagram showing the typical deployment structure of a ASP.NET Web application with a single IIS.

Clients request for resources and IIS Process the request and send back to clients.

Web Farm :

This is the case, where you have only one web server and multiple clients requesting for the resources from the same server. But when there is huge numbers of  incoming traffic for your web sites, one standalone server is not sufficient to process the request.  You may need to use multiple server to host the application and divide the traffic among them.  This is called “Web Farm” . So when you are hosting your single web site on multiple web server over load balancer called “Web Farm”. Below diagram showing the over all representation of Web Farms.

 

In general web farm architecture, a single application is hosted on multiple IIS Server and those are connected with the VIP ( Virtual IP ) with Load Balancer. Load Balancer IP’s exposed to external worlds to access. So whenever some request will come to server from clients, it will first hit the Load Balancer, then based on the traffic on each server LB distributed the request to corresponding web server.  These web server may share same DB server or may be they can use replicated server in the back end.
So, In a single statement, When we host a web application over multiple web server to distributed the load among them  is called Web Farm.
Web Garden :
Now, let’s have a look, what is Web Garden ?  Both the terms  sounds same,  but they are totally different with each other.  Before starting with Web Garden, I hope you have fundamental idea of what is Application Pool and what is Worker Process. 
Just to recall,  When we are talking about requesting processing with in IIS, Worker Process (w3wp.exe ) takes care all of these. Worker Process runs the ASP.Net application in IIS. All the ASP.Net functionality inside IIS  runs under the scope of worker process. Worker Process is responsible for handling all kind of request, response, session data, cache data.  Application Pool is the container of worker process. Application pools is used to separate sets of IIS worker processes and enables a better security, reliability, and availability for any web application.
Now, by default each and every Application pool contains a single worker process. Application which contains the multiple worker process called “Web Garden”. Below is the typical diagram for a web garden application.
In the above diagram you can see, on of the application containing the  multiple worker process, which is now a web garden.
So, a Web application hosted on multiple server and access based on the load on servers is called Web Farms and When a single Application pool contain multiple Worker process is called web garden.
Create Web Garden in IIS 6 and IIS 7
Now, I am going to show how you can change the Number of Worker process In both IIS 6 and IIS 7.  For IIS 6,  Right Click on Application Pool > Properties > Goto  Performance Tab.

In the “Performance Tab” Section you would have one option called “Web Garden” where worker process sets to “1”, you can set the number of worker process that you required.
For IIS 7, Right Click on Application Pool > Go To Advance Settings > In Process Model section, you will have “Maximum Worker Processes” . You can change it more than 1 to make it as web garden.

In the above image you can also check the definition of Web Garden also.


Advantages of Web Farm and Web Garden :
Now, let’s have a look in to the advantages of both the Web farms and Web Garden.
Advantages of Web Farm

• It provides high availability. If any of the server in the farm goes down, Load balancer can redirects the requests to other servers.
• Provides high performance response for client requests.
• Provides Better scalability of the web application and reduce the failure of application.
• Session and other resource can be stored in a centralized location to access by the all server.

Advantages of Web Garden:

• provides better application availability by sharing request between multiple worker process.
• Web garden use processor affinity where application can swapped out based on preference and tag setting.
• Less consumption of physical space for web garden configuration.

How to manage session in Web Farm Mode ?
While using session, requests are distributed among different servers. By default session mode is set to In Proc where session data stored inside worker process memory. But, In Web farm mode we can share the session among all the server using a single session store location my making it Out proc (State Server or SQL Server Mode). So, if some of the server goes down and request transferred to the other server by the Load balancer session data should be available for that request.
In the above diagram,  you can see we can both the IIS server sharing the same session data which is stored in out of worker process.
How to manage session in Web Garden Mode ?
When we are using Web garden where request is being taking care by different worker process we have to make the session mode as out process session mode as described earlier. For Web Garden we have configure the out process with in same server but for different worker process.

While using Web garden with your application you need do couple of configuration settings in web.config in <process Model> section where you need to set certain properties like cpuMask, RequestLimit, webGarden, ClientConnectCheck etc.
Summary : When we host a web application over multiple web server to distributed the load among them  is called Web Farm and when One application having multiple worker worker process called Web garden.

Handling Division By Zero Scenarios in SQL using NULLIF

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

?

DECLARE @dividend INT DECLARE @divisor INT SET @dividend = 1 SET @divisor = 0 SELECT @dividend/@divisor /* Error: Msg 8134, Level 16, State 1, Line 7 Divide by zero error encountered. */

What you can do is you can code around it, so your users and your app do not get this error.

Alternative 1: NULLIF (preferred)

The NULLIF built in function returns a NULL if the two parameters are equal. In our case, we want to check if the divisor is zero.

?

DECLARE @dividend INT DECLARE @divisor INT SET @dividend = 1 SET @divisor = 0 SELECT @dividend/NULLIF(@divisor,0) /* Returns NULL */

Alternatively, instead of NULL, you may want to display just 0

?

SELECT ISNULL(@dividend/NULLIF(@divisor,0),0) /* Returns NULL, no error */

Alternative 2: CASE

You can also use CASE to drive what values you want to show if the divisor. The downside to this approach is your code can get really lengthy right away by having multiple CASE statements.

?

SELECT     CASE @divisor        WHEN 0 THEN 0        ELSE @dividend/NULLIF(@divisor,0)     END /* Returns 0, no error */

Alternative 3: IF/ELSE

You can also use IF/ELSE. However this means you cannot just have one SELECT statement. This needs to be in a script, a stored proc, or UDF.

?

IF @divisor = 0 BEGIN    SELECT 0 END ELSE BEGIN    SELECT @dividend/@divisor END /* Returns 0, no error */

What's the difference between a Debug vs Release Build?

When you compile your application in release mode debug information is not generated and this is the option normally used when you want to deploy your application to client.debug mode is heavy since debugging info gets generated and is used while development to find out error in your code.
When we are moving code to production then we make the mode as Release. Because there no debug is required and the execution also very fast in this mode due to the .pdb file formation will not happens.

Debug build
1. Basically this for developer
2. Complier code optimization is OFF

Release build
1. Basically this for Client to whom you want to distribute the application
2. Complier code optimization is ON

How to call a specific base constructor in C#?

How to call a specific base constructor in C#?

What is a Constructor? - It is a method that gets invoked when an instance of a class is created. In case a class has plenty of constructors, i.e. there are plenty of overloaded constructors, in such a scenario, it is still possible to invoke a specific base constructor. But there is a special way, as explicit calls to a base constructor is not possible in C#. See code below:

C# Example
public class dotnetClass
{
public dotnetClass()
{
// The constructor method here
}
// Write the class members here
}

// below shows how to overload a constructor
public class dotnetClass
{
public dotnetClass()
{
// This constructor is without a parameter
// Constructor #1
}

public dotnetClass(string name)
{
// This constructor has 1 parameter.
// Constructor #2

}
}

This constructor gets executed when an object of this class is instantiated. This is possible in C#. Calling a specific constructor will depend on how many parameters, and what parameters match a specific constructor. Note that a compile time error may get generated when 2 constructors of the same signature are created.

We may make use of the this keyword and invoke a constructor. See code example below.

this("some dotnet string");
//This will call Constructor #2 above

What is the use of the base keyword. Suppose we have a derived class named dotnetderivedclass. If this derived class is to invoke the constructor of a base class, we make use of the base keyword. See code example below on how to use a base keyword to invoke the base class constructor.

public class dotnetClass
{
public dotnetClass()
{
// The 1st base class constructor defined here
}

public dotnetClass(string Name)
{
// The 2nd base class constructor defined here
}
}

public class dotnetderivedclass : dotnetClass
// A class is being inherited out here
{
public dotnetderivedclass()
{
// dotnetderivedclass 1st constructor defined here
}

public dotnetderivedclass(string name):base(name)
{
// dotnetderivedclass 2nd constructor defined here
}
}

Note that we have used the base keyword in the sample code above. The sequence of execution of the constructors will be as follows:
public dotnetClass() method -> public dotnetderivedclass() method

The above sequence triggers when there is no initializer to the base class, and thus it triggers the parameterless base class constructor. The other base class constructor may also get invoked when we pass a parameter while defining it.

Magic Tables in SQL Server

What are Magic Tables in SQL Server?

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server.

 

Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.Let’s see the demonstration of inserted magic table in SQL Server:

INSERTED Magic Table:

When we insert record into table then SQL server automatically created ‘inserted’ magic table and recently inserted record are available in this table, If we want to recover this data which are recently inserted then we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table in SQL Server.

Example:

--- CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE ----

CREATE TABLE INSERT_MAGIC

(

ID INT,

TRIGGER_MESSAGE VARCHAR(50)

 )

 -------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_INSERT

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  SELECT @ID = (SELECT ID FROM inserted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')

  END

  GO

Note: Here ‘inserted’ is insert magic table

DELETED Magic Table:

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted record from original table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_DELETE

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  DECLARE @NAME VARCHAR(50)

  SELECT @ID = (SELECT ID FROM deleted)

  SELECT @NAME = (SELECT NAME FROM deleted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)

  END

  GO

Page Life Cycle Events

1. OnInit (Init) -- Inializes all Child controls on the Page

2. LoadControlState -- Loads the Control State

3. LoadViewState -- Loads the View State

4. LoadPostData -- Control properties are set according to the received form data

5. Load -- Actions can be performed on the controls as all the pre-activities are complete by this time

6. RaisePostDataChangedEvent -- This event is raised if data has been changed in previous and Current Postbacks.

7. RaisePostBackEvent -- This event handles the user action that caused the Postback.

8. PreRender (OnPreRender) -- This event takes place between postback and saving viewstate. Till this stage changes will be saved to the control.

9. SaveControlState -- Self Explanatory

10. SaveViewState -- Self Explanatory

11. Render -- Generates artifacts at Client side (HTML,DHTML,Scripts) to properly display controls

12. Dispose -- Releases unmanaged Resource ( Database connections, file handles)

13. Unload -- Releases managed Resources ( Instances created by CLR)

UNION vs UNION ALL?

 Why UNION ALL is faster than UNION?

 

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.
UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.
SQL Scripts below proves why UNION ALL is faster than UNION:

1	-- Create First table: #tempTable1

2	select FirstName, LastName into #tempTable1

3	from Person.Contact

4	where ContactID <= 100 -- Create Second table: #tempTable2 select FirstName, LastName into #tempTable2 from Person.Contact where ContactID > 100 and ContactID <= 200

Comparison:

1	SET STATISTICS PROFILE ON

2

3	-- Using UNION - Query Cost: 35%

4	select * from #tempTable1

5

UNION

6	select * from #tempTable2

Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	-- Using UNION ALL - Query Cost: 13%

2	select * from #tempTable1

3

UNION ALL

4	select * from #tempTable2

Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	-- Using UNION ALL with DISTINCT - Query Cost: 52%

2	select DISTINCT * from #tempTable1

3

UNION ALL

4	select DISTINCT * from #tempTable2

Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC,

[tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC,

[tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	SET STATISTICS PROFILE OFF

2

3	-- Final Cleanup

4	drop table #tempTable1

5	drop table #tempTable2

The Query plan for:
- UNION ALL SELECT above shows it just does a concatenation.
- UNION SELECT above shows it does a SORT on final concatenation.
- UNION ALL with DISTINCT SELECT above shows it does a SORT of individual tables than does the final concatenation.
The Query cost with:
- UNION ALL = 13%
- UNION = 35%
- UNION ALL with DISTINCT = 52%
This proves that:

• UNION ALL is faster and more optimized than UNION. But this does not mean you use UNION ALL in every scenario.
• UNION is not equivalent to “UNION ALL with DISTINCT”.

referenced from http://sqlwithmanoj.wordpress.com/2010/12/

Primary keys without Clustered Index

Primary keys without Clustered Index ...

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below:

Method 1:

Using this method we can specify it while creating the table schema itself.

Create Table Empolyee
(
EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
)
Go

Method 2:

Using this method also we could specify it while creating the table schema. It just depends on your preference.

Create Table Employee
(
 EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
Constraint pk_parent primary key nonclustered (EmpID)
)
Go

Method 3:

If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.

Step 1: Find the contraint name

sp_helpconstraint Employee

/*
This way we could find out the constraint name. Lets assume that our constraint name is PK_Employee_22568A79
*/

Step 2: First drop the existing constaint

Alter table Employee drop constraint PK_Employee_22568A79

Step 3: Add the new nonclustered index to that field now

Alter table Employee add constraint PK_EmpIdNew primary key nonclustered (EmpID )

Difference between TRUNCATE, DELETE and DROP

Difference between TRUNCATE, DELETE and DROP?

DELETE & TRUNCATE are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.
DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked if provided inside a transaction.
DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Reverse a string using CTE in sql

DECLARE @StringToReverse VARCHAR(55)
    SET @StringToReverse = 'jagdish kholiya'   
    ;WITH cte AS (
          SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln        
           UNION ALL
          SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
          FROM cte
          WHERE ln >= 1)
    SELECT @StringToReverse AS String, revStr FROM cte WHERE ln = 0

Finnding & Removing Duplicate records from a table

There are lots of questions/posts about dealing with duplicate records in many SQL Server forums. Many of these questions are asked in “SQL Server Interviews” and many developers starting their carrier in database programming find it challenging to deal with. Here we will see how we can deal with such records.
Duplicate data is one of the biggest pain points in our IT industry, which various projects have to deal with. Whatever state-of-art technology and best practices followed, the big ERP, CRM, SCM and other inventory based database management projects ends up in having duplicate & redundant data. Duplicate data keeps on increasing by manual entries and automated data loads. Various data leads getting pumped into system’s databases without proper deduping & data cleansing leads to redundant data and thus duplicated record-sets.
Data cleansing requires regular exercise of identifying duplicates, validating and removing them. To minimize these type of scenarios various checks and filters should also be applied before loading new leads into the system.
Lets check this by a simple exercise how we can identify & remove duplicate data from a table:

1	USE [AdventureWorks]

2

GO

3

4	-- Insert some sample records from Person.Contact table of [AdventureWorks] database:

5	SELECT TOP 10 ContactID, FirstName, LastName, EmailAddress, Phone

6	INTO DupContacts

7	FROM Person.Contact

8

9	SELECT * FROM DupContacts

1	-- Insert some duplicate records from the same list inserted above.

2	INSERT INTO DupContacts

3	SELECT TOP 50 PERCENT FirstName, LastName, EmailAddress, Phone

4	from DupContacts

5

6	SELECT * FROM DupContacts

1	-- Insert some more duplicate records from the same list inserted above.

2	INSERT INTO DupContacts

3	SELECT TOP 20 PERCENT FirstName, LastName, EmailAddress, Phone

4	from DupContacts

5

6	SELECT * FROM DupContacts

01	--// Identify Duplicate records & delete them:-

02

03	-- Method #1: by using ROW_NUMBER() function:

04	;WITH dup as (

05	SELECT ContactID, FirstName, LastName, EmailAddress, Phone,

06	ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups

07	FROM DupContacts)

08	SELECT * FROM dup

09	WHERE NumOfDups > 1

10	ORDER BY ContactID

1	-- Remove/Delete duplicate records:

2	;WITH dup as (

3	SELECT ContactID, FirstName, LastName, EmailAddress, Phone,

4	ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY ContactID) AS NumOfDups

5	FROM DupContacts)

6	DELETE FROM dup

7	WHERE NumOfDups > 1

8

9	SELECT * FROM DupContacts

1	-- Method #2: by using SELF-JOIN:

2	SELECT DISTINCT a.ContactID, a.FirstName, a.LastName, a.EmailAddress, a.Phone

3	FROM DupContacts a

4	JOIN DupContacts b

5	ON a.FirstName = b.FirstName

6	AND a.LastName = b.LastName

7	AND a.ContactID > b.ContactID

1	-- Remove/Delete duplicate records:

2

DELETE a

3	FROM DupContacts a

4	JOIN DupContacts b

5	ON a.FirstName = b.FirstName

6	AND a.LastName = b.LastName

7	AND a.ContactID > b.ContactID

8

9	SELECT * FROM DupContacts

1	-- Method #3: by using AGGREGATES & Sub-QUERY:

2	SELECT * FROM DupContacts

3	WHERE ContactID NOT IN (SELECT MIN(ContactID)

4	FROM DupContacts

5	GROUP BY FirstName, LastName)

1	-- Remove/Delete duplicate records:

2	DELETE FROM DupContacts

3	WHERE ContactID NOT IN (SELECT MIN(ContactID)

4	FROM DupContacts

5	GROUP BY FirstName, LastName)

6

7	SELECT * FROM DupContacts

 
Referenced from : http://sqlwithmanoj.wordpress.com/category/sql-tips/

Ajax,Linq and XML in ASP.NET 3.5 and C#

AJAX and LINQ are probably the most-talked about Microsoft offerings at the moment, and rightly-so. Both provide us with powerful features to add to our development arsenal- AJAX allows us to create more user-friendly web applications that can almost rival our desktop equivalents; and then LINQ provides a unified way for us to communicate and interact with a wide array of data sources.
This tutorial will combine the two, and throw in a bit of XML too. In this example we are going to create a web application that will use an XML file to store small amounts of data, that we will display and also add to using AJAX and LINQ.
To demonstrate this, we will be storing names, cities and ages of people in the XML file. We will use a literal control to display the XML data and three textboxes to input new data.
Our XML file structure will look something like this:

<?xml version="1.0" encoding="utf-8"?> <Persons> <Person> <Name>Paxton</Name> <City>Munich</City> <Age>29</Age> </Person> <Person> <Name>Mike</Name> <City>Orlando</City> <Age>33</Age> </Person> </Persons>

Because we are using VS.NET 2008, all we need to do to enable AJAX on our page is add the following:

<form id="form1" runat="server"> <asp:ScriptManager id="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="updAdd" runat="server"> <ContentTemplate> </ContentTemplate> </asp:UpdatePanel> </form>

This is the AJAX structure we will be using for our page. The ScriptManager will handle all of our AJAX calls, we don't need to do anything with that. The UpdatePanel specifies an area of the page that will partially refresh- instead of posting back the whole page. We will also add a trigger, to specify what will 'trigger' the partial refresh. To do this, though, we need the name of our button.
We add a button and the trigger to the page:

<form id="form1" runat="server"> <asp:ScriptManager id="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="updAdd" runat="server"> <Triggers> <asp:AsyncPostBackTrigger ControlID="butAdd" EventName="Click" /> </Triggers> <ContentTemplate> <asp:Button ID="butAdd" runat="server" Text="Add" onclick="butAdd_Click" /> </ContentTemplate> </asp:UpdatePanel> </form>

We reference the button in the trigger field of the UpdatePanel, which will cause the partial refresh to initiate when the button is clicked.
Next, we can add the rest of our form - the textboxes for data input, and the literal control for reading the XML. We can also add another button to initiate the reading of the XML file:

<form id="form1" runat="server"> <asp:ScriptManager id="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="updAdd" runat="server"> <Triggers> <asp:AsyncPostBackTrigger ControlID="butAdd" EventName="Click" /> </Triggers> <ContentTemplate> <strong>Add to XML</strong><br /> Name:<br /> <asp:TextBox ID="txtName" runat="server" /><br /> City:<br /> <asp:TextBox ID="txtCity" runat="server" /><br /> Age:<br /> <asp:TextBox ID="txtAge" runat="server" /><br /> <asp:Button ID="butAdd" runat="server" Text="Add" onclick="butAdd_Click" /><br /> <asp:Label ID="lblStatus" runat="server" /> <br /><br /> <strong>Read XML:</strong><br /> <asp:Button ID="butRead" runat="server" Text="Read" onclick="butRead_Click" /><br /> <asp:Literal ID="litResults" runat="server" /> </ContentTemplate> </asp:UpdatePanel> </form>

Notice that both of our buttons have the onclick handlers specified. We can now code these in the code-behind, like so:

protected void butRead_Click(object sender, EventArgs e) { readXML(); lblStatus.Text = ""; } protected void butAdd_Click(object sender, EventArgs e) { try { XDocument xmlDoc = XDocument.Load(Server.MapPath("People.xml")); xmlDoc.Element("Persons").Add(new XElement("Person", new XElement("Name", txtName.Text), new XElement("City", txtCity.Text), new XElement("Age", txtAge.Text))); xmlDoc.Save(Server.MapPath("People.xml")); lblStatus.Text = "Data successfully added to XML file."; readXML(); } catch { lblStatus.Text = "Sorry, unable to process request. Please try again."; } }

We tell the read button to call another method when it is clicked because we can reuse it - notice we call the same method at the end of the add button. This is so we can update the displayed XML when we add new content. We are using LINQ to open the XML document first, and then add a new element into the parent element of Persons. Then we save changes to the XML file and let the user know.
The readXML method looks like this:

protected void readXML() { XDocument xmlDoc = XDocument.Load(Server.MapPath("People.xml")); var persons = from person in xmlDoc.Descendants("Person") select new { Name = person.Element("Name").Value, City = person.Element("City").Value, Age = person.Element("Age").Value, }; litResults.Text = ""; foreach (var person in persons) { litResults.Text = litResults.Text + "Name: " + person.Name + "<br />"; litResults.Text = litResults.Text + "City: " + person.City + "<br />"; litResults.Text = litResults.Text + "Age: " + person.Age + "<br /><br />"; } if (litResults.Text == "") litResults.Text = "No Results."; }

As you can see, LINQ looks similar to SQL, but instead of communicating with a database only, we can use LINQ to communication with any data source - in this case an XML file. First we declare a variable persons and assign to it the collection from the XML file's descendants, Person. Then we select all the data from within and simply loop through our collection and output to the literal control.

LINQ to SQL

What is LINQ TO SQL ? LINQ to SQL is new component that is released with .NetFramework 3.5. LINQ to SQL provides a way where a developer can manage a relational database object as a .net object. In simple terms, it is an ORM (Object-relational mapping) that is used to manage a back end object as a front end object in object oriented way i.e. relational to object oriented type system. We can query, insert, update and delete the underlying database object with LINQ to SQL classes.  When we query or update or delete these .net objects, LINQ to SQL will automatically take care of the operations in underlying relational data objects.

LINQ to SQL uses LINQ as querying technique to query the database object through the LINQ to SQL. What is LINQ? LINQ stands for Language Integrated Query.  LINQ is a data querying methodology which provides querying capabilities to .Net languages with syntax similar to SQL query. LINQ has a set of querying operators that can be used to query in memory object collection, Sql database, XML, etc. LINQ processing engine will then convert the LINQ query to native query specific to the database to execute against the datasource.  Since, the querying feature is integrated with the language; one can build an efficient query based on the language of their choice.  With Visual Studio, we have intellisense support and with language support, we have type safety and compile-time error checks. There are many advantages when we use ORM in our projects. Below are few, 1.      You can query the database object treating it as a .net object using .net language. 2.      Your data access development will be easier. 3.      It handles the SQL injection attacks automatically. 4.      You will get type safety and compiler advantages since the database objects are managed as .net object. 5.      Using Visual Studio provides you intellisense support and other tools for developing the LINQ to SQL classes Constructing your first LINQ to class Visual studio 2008 is packed with tool called LINQ to SQL designer which will help us to build the LINQ to SQL classes very easily. LINQ to SQL designer It is a new designer to design relational database object as LINQ to SQL objects.  We can either, drag and drop database object from “Server Explorer” or, we can design the LINQ to SQL object manually using the designer and tools. To understand better, we will create two simple tables, Employee and Department, will design our LINQ to SQL class. Open Visual Studio 2008, Click File >Website and choose ASP.Net Website. Choose the language of your choice and name your website according to your need. In solution explorer, right the project and select “Add New Item”. Select “LINQ to SQL classes” as shown in below figure. I have named it as EmployeeInfo.   This will add an EmployeeInfo.dbml file inside “App_Code” folder.  In Visual Studio, EmployeeInfo.dbml will have 2 panes. The left pane is for deigning the data objects and the right pane can be used for creating methods that operates on the data objects. The Visual Studio toolbar will contain a new toolbar for designing LINQ to SQL objects. Refer below,   We will see more about using these toolbar to create our own object in future articles. Once we include the LINQ to SQL Class in the project, there will be 3 files added to the App_Code. EmployeeInfo.dbml An XML file that contains information about the tables like table name, column name, type, etc EmployeeInfo.layout An XML file that specifies how the designer places the objects. EmployeeInfo.designer.cs The C# code file that will have code to interact with the underlying database called DataContext object. It also has entity class definition. Designing the LINQ to SQL classes Open Server Explorer, Expand the database tables. Drag Employee and Department into LINQ to SQL designer and click Save. The Objects will be created automatically. Refer the below figure.   This will update the EmployeeInfo.designer.cs file with class definition for Employee and Department. It will also generate the DataContext object for these objects for interacting with the underlying datasource i.e. it is with the help of this DataContext class we can interact with the database objects in our managed environment. In our case, it is EmployeeInfoDataContext. The arrow between the object is the foreign key relationship between them. This means that the Employee class will have the Department class as a member. Thus, we have modelled LINQ to SQL classes which has O/R mapping. Moving forward, we will see how we can we use these objects to do our data access. Using LINQ to SQL classes The below code will fetch all the employees rows and will populate a GridView control. EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();         var LINQQuery = from emp in dbEmp.Employees                         select emp;         gvEmployee.DataSource = LINQQuery;         gvEmployee.DataBind(); Updating Employee Information The below code uses the LINQ to SQL class to update the employee information whose employee id is fetched from txtEmpID. EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();                string EmpID = txtEmpID.Text;                    Employee empTemp = (from emp in dbEmp.Employees                            where emp.EmpID == int.Parse(EmpID)                            select emp).Single();                empTemp.EmpName = txtEmpName.Text;                 empTemp.DeptID = int.Parse(ddlTemp.SelectedValue);                empTemp.Age = int.Parse(txtAge.Text);                 empTemp.Address = txtAddr.Text;         dbEmp.SubmitChanges(); Deleting a Row EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();               string EmpID = null;               EmpID = txtEmpID.Text;         Employee empTemp = (from emp in dbEmp.Employees                             where emp.EmpID == int.Parse(EmpID)                             select emp).Single();         dbEmp.Employees.DeleteOnSubmit(empTemp);         dbEmp.SubmitChanges(); Inserting a new Row EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();         Employee emp = new Employee();              emp.EmpName = txtEmpName.Text;             emp.DeptID = int.Parse(ddlTemp.SelectedValue);             emp.Age = int.Parse(txtAge.Text);             emp.Address = txtAddr.Text;        dbEmp.Employees.InsertOnSubmit(emp);        dbEmp.SubmitChanges();