Common Table Expression (CTE)

               few days ago I’ve written a  blog post in SQL server using CTE to find  Sundays. Lets dig some more deep in to this SQL server feature.

                                                       

    What is CTE

     The common table expression is a temporary named result set that can refer in a SELECT,INSERT, UPDATE or DELETE statement.

       simply a CTE is similar to a derived table but it is not stored as an object and lasts only for the duration of the query.

    Unlike a derived table, a CTE can be self referencing and can be referenced multiple times in the same query

we can use a CTE in View create statement also we can add CTE in to the new MERGE statement (In sql server 2008)

SQL Server support two types of CTEs

• recursive
• non recursive

 Syntax

A Common Table Expression contains three main parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

With CTE_Name (Column_Name_1, Column_Name_2 … Column_Name_n )
As
(
     cte_Query_Definition
)
--TSQL which uses the CTE

Some Examples

WITH Table1(Name,Designation,DateofJoin)
As   
(
    select CN.Name,D.Name,E.DateOfJoin from ContactName CN
    Inner join AM_Employee E on E.ContactId = CN.ContactId
    Inner join AM_Designation D on E.DesignationId = D.Id
)
select Name,Designation,DateofJoin from Table1

 

ASP.NET MVC 4 Developer Preview

                 One week ago in Build conference Microsoft  announced a lot of their products latest versions developer previews. They are Including Windows 8, VS 2011, ASP.NET MVC 4 etc. In this post l would like to discuss about ASP.NET MVC 4 and lets check what are the new features are included in MVC 4 Developer preview.you can read more details about ASP.NET MVC 4 from asp.net site
http://www.asp.net/mvc/mvc4


Download links
         you can download and install the mvc-4 developer preview using the following links

Install ASP.NET MVC 4 using the Web Platform Installer

• ASP.NET MVC 4 Web Platform Installer for Visual Studio 2010
• ASP.NET MVC 4 Web Platform Installer for Visual Studio 11 Developer Preview
• Download the installer executable

 Features at a glance:

        Cosmetic changes:

             Refreshed and modernized default project Template.

             New Template for mobile web applications.

        Other changes

             Adaptive Rendering.

             Redirect to different views based on requested device.

             Enhanced support for asynchronous methods.

                  

MVC team introduced a brand new default Template in MVC 4, using this we can create good looking websites with out much more design effort.

         New Registration and Login windows using JQuery UI

 

Mobile Application Template with jQuery Mobile

Mobile Log On

Mobile View Registration Form

you can read more details about asp.net mvc 4 developer preview from  Phill Hacck

and Scott Hanselman blog posts. you can read more about mobile features from here .

Select All option using jQuery

      selecting some more checkboxes is cumbersome and tedious. usually we are trying select all or Unselect All option by checking or unchecking one. Find the snippet of jQuery to select / unselect the check boxes from a table row by changing one check box value. 

Here the code:

$(".chkAll").change(function () 

     {           

       var checkState = $(this).attr('checked');

       var row = $(this).closest('tr')   

       $(row).find('input:checkbox').attr('checked', checkState);         

});

by changing the status of first checkbox,we can select or unselect the entire row of checkboxes.

first column checkboxes has contain a class " chkAll” and call the jQuery function whenever a change occurred in that checkbox. set the current status of that (chkAll) checkbox in to a variable checkstate and then find the closest table row to the checkbox. After that find all check boxes from that row and change the status of all in to the variable value.

Generate SQL Queries

select *from AM_DataSource

select 'Insert into AM_CalculationMaster(Id,Name,IsFinancialField,AccountNumber,EquationString)values('+ CAST(Id As Varchar(100))+','+ QUOTENAME(Name,'''')+',1,0,'+ IsNULL(QUOTENAME(EquationString,''''),'')+ ')' from Am_DataSource

 

Replace a string value with another in SQL Server

select *from sa_Name
update sa_Name set SurName = REPLACE(SurName,'Aravindakshan','A')
select *from sa_Name

     after executing this query the Name ‘Aravindakshan’ is replaced with ‘ A’ all the occurrence of this word in Surname column should be replaced with ‘A’.

Find All Sundays between two dates in SQL server Using CTE

• A Common table Experession (CTE) can be treated as a temporary result set within the scope of a single SELECT,INSERT,UPDATE or DELTE Statement.
• A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
• CTE can be self-referencing and can be referenced multiple times in the same query.

 

declare @DateFrom DateTime ='2011-07-01',

            @DateTo DateTime = '2011-07-31'

;WITH CTE(dt)

AS

(

      Select @DateFrom

      Union All

      Select DATEADD(d,1,dt)FROM CTE

      Where dt<@DateTo

)

select 'Sunday',dt from CTE

where DATENAME(dw,dt)In('Sunday')

Output

 

Confirm a request using multiple submit buttons.

   Recently i have faced a situation to confirm an Inactivate request from user.I have done it using multiple submit buttons.In ASP.net MVC we can bind the submit buttons value using same binding methods. Use two submit buttons in Confirm view.

<p>
    <input type="submit" name="Confirm" value="Confirm"/>
    <input type ="submit" name="Cancel" value="Cancel" />
</p>

We used name="confirm" and name="cancel" to bind the submit buttons values. if an user clicked on confirm button then  the ActionResult method string variable 'Confirm' will bind with a value "Confirm" and string Cancel will be null else vice versa. By checking this values we can perform the required action.

        [HttpPost, ActionName("InActive")]

        public ActionResult InActiveConfirmed(string Confirm, string Cancel)
        {
            var response = Confirm ?? Cancel;
 
            if (response == "Confirm")
            {
                // do the things..
                // In Activate the item
            }
            else
            {
                // return back with out InActivating Item
            }
 
 
        }