MVC Routing Concept

URL stand for Uniform Resource Locator
for example www.abc.com/directory/emplyee.htm

URI is an identifier for some resource, but a URL gives you specific information as to obtain that resource.URL doesn’t necessarily mean a physical location of a static file on a web server’s
hard drive somewhere ,it means some unique identifier.

Most of the cases Webserver  and then directory and sub directory and file name

But in MVC the method is different

1. MVC matches controller action rather than file system
2. MVC also matches outgoing request with  controller action rather than file system

Route definitions start with the URL pattern, which specifies the pattern that the route will match. Along with the route URL, routes can also specify default values and constraints for the various parts of the URL, providing tight control over how and when the route matches incoming request URLs


Application_Start()   method contains a call to a method named the
RegisterRoutes method


 public static void  RegisterRoutes (  RouteCollection routes  )
{
      routes.MapRoute( “example”, “{first}/{second}/{third}”);
}

Routing: /albums /display/123
Result: first = “albums” second = “display” third = “123”
routes.MapRoute(“simple”, “{first}/{second}/{third}”);

The {action} parameter value is used to indicate which method of the controller to call in order
to handle the current request. Note that this method invocation applies only to controller classes
that inherit from the System.Web.Mvc.Controller base class. Classes that directly implement
IController can implement their own conventions for handling mapping code to handle the
request.

Any route parameters other than {controller} and {action} can be passed as parameters to the
action method, if they exist. For example, assuming the following controller:

public class AlbumsController : Controller
{
            public ActionResult Display(int id)
           {
                return View();
            }
}

ROUTE URL PATTERN                           EXAMPLES OF URLS THAT MATCH
{controller}/{action}/{genre}                      /albums/list/rock
service/{action}-{format}                            /service/display-xml
{report}/{year}/{month}                              /{day} /sales/2008/1/23

routes.MapRoute(“simple”, “{controller}/{action}/{id}”,new {id = UrlParameter.Optional});


Multiple default values can be provided. The following snippet demonstrates providing a default
value for the {action} parameter as well:
routes.MapRoute(“simple”, “{controller}/{action}/{id}”, new {id = UrlParameter.Optional, action=”index”});

using System.Web.Routing;

SQL Server 2012 Magic

1)Unusual Sort Orders
         (Order by Contain Case)

SELECT  certificate_id,
                certificate_name,
                certificate_code,
                type_id,
               date_position
                              FROM tbl_sref_train_certificate
                             ORDER BY
                                     CASE date_position WHEN NULL
                                        THEN type_id
                                   ELSE  date_position  END

2) NULLIF,COALESCE

ISNULL : Returns a null value if the two specified expressions are equal.

SELECT NULLIF(2,2)

Result
----------------------------------------
 NULL

SELECT NULLIF(2,3) 
Result
----------------------------------------
2

COALESCE : coalesce returns the first non-null expression among its arguments.

Example 1
SELECT COALESCE(NULL, NULL, NULL, GETDATE()) 

Result
----------------------------------------
2014-11-02 22:48:40.780

The above example may have single value or four values. If it has single value, then it fills null values with remaining attributes.

---------------------------------------------------------------
column 1              |     column1
----------------------------------------------------------------
 Abc                          NULL
NULL                      Efg
--------------------------------------------------------------

Example 2
 SELECT COALESCE(column 1, column1 )

Result
----------------------------------------
  Abc
 Efg

Example 3
Concatination

---------------------------------------------------------------
Country              |     Currency
----------------------------------------------------------------
India                          Rupee
Japan                         Yen
--------------------------------------------------------------


SELECT  Country ,
                 Currency,
                 COALESCE(Country,Currency)  As Country_Currency

---------------------------------------------------------------
Country              |     Currency     | Country_Currency
----------------------------------------------------------------
India                          Rupee           India Rupee          
Japan                         Yen               Japan Yen                              
--------------------------------------------------------------

3) UNION AND UNION Goof Exapmle

DECLARE @test1 TABLE
(
  ID INT,
  NAME VARCHAR(200)
)
DECLARE @test2 TABLE
(
  ID INT,
  NAME VARCHAR(200)
)

INSERT INTO @test1 VALUES (1,NULL),(2,'India'),(3,'Japan'),(4,NULL)
INSERT INTO @test1 VALUES (1,NULL),(2,'USA'),(3,'Braziln'),(4,NULL),(1,'India')

SELECT ID,NAME FROM @test1
UNION
SELECT ID,NAME FROM @test2

SELECT ID,NAME FROM @test1
UNION ALL
SELECT ID,NAME FROM @test2

Result
----------------------------------------
ID NAME
-----------------------------------------
1    NULL
1    India
2    India
2    USA
3    Braziln
3    Japan
4    NULL

Result
----------------------------------------
ID NAME
-----------------------------------------
1    NULL
2    India
3    Japan
4    NULL
1    NULL
2    USA
3    Braziln
4    NULL
1    India

4)Random Rows Return

SELECT  member_id,
        last_name,
        first_name
 FROM tbl_member
 TABLESAMPLE SYSTEM (1 PERCENT);

Result (1st Execution)
----------------------------------------
102  rows

Result (2st Execution)
----------------------------------------
106  rows

So on...

5)Overriding an IDENTITY Column

CREATE TABLE TEST
(
    ID INT IDENTITY  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (COUNTRY) VALUES ('India'),('Brazil'),('China')

SELECT * FROM TEST

Result
----------------------------------------
ID NAME
-----------------------------------------
1    India
2    Brazil
3    China



SET IDENTITY_INSERT [Database].TEST
INSERT INTO TEST (COUNTRY) VALUES (4,'Pakistan'),(5,'Irac')


----------------------------------------
ID NAME
-----------------------------------------
1    India
2    Brazil
3    China
4   Pakistan
5   Irac


6) Deleting Rows and Returning the Deleted Rows

CREATE TABLE TEST
(
    ID INT IDENTITY  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (COUNTRY) VALUES  ( 'India' ),( 'Brazil'  ),(  'China'  ),(  'Nepal'  )

DELETE
FROM TEST
OUTPUT deleted.COUNTRY
WHERE ID=4

7) Inserting Rows and Returning the Inserted Rows

CREATE TABLE TEST
(
    ID INT  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (ID,COUNTRY)
OUTPUT INSERTED.COUNTRY,INSERTED.ID
VALUES       (1,'India')  ,  (2,'Brazil')  ,  (3,'China')  ,  (4,'Nepal')

8)Updating Data and Returning the Affected Rows

CREATE TABLE TEST
(
    ID INT  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (ID,COUNTRY)
VALUES (1,'India') , (2,'Brazil') , (3,'China') , (4,'Nepal')


CREATE TABLE TEST1
(
    ID INT  ,
    COUNTRYX VARCHAR(200) NULL
)

INSERT INTO TEST1
VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL)

SELECT * FROM TEST1

UPDATE A
           SET A.COUNTRYX=B.COUNTRY
           OUTPUT INSERTED.COUNTRYX
FROM  TEST1 A
INNER JOIN TEST B
ON A.ID=B.ID

SELECT * FROM TEST1


9) SoundIndex ...Sound Like

DROP TABLE TEST

CREATE TABLE TEST
(
    ID INT  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (ID,COUNTRY)
VALUES (1,'India'   )  ,
                (2,'Brazil'  )  ,
                (3,'China'  )  ,
                (4,'Nepal'  )  ,
                (5,'ian'      )  ,
                (6,'en'       )  ,
                (7,'an'       )  ,
                (8,'am'      ) ,
                (9,'em'      ),
                (10,'am'    )



SELECT  top 5 COUNTRY
FROM TEST
WHERE  SOUNDEX(COUNTRY)=SOUNDEX('inI')

Result
----------------------------------------
COUNTRY
-----------------------------------------
ian


10) Merger Statement

CREATE TABLE TEST
(
    ID INT  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (ID,COUNTRY)
VALUES    (1,'India'),
                   (2,'Brazil'),
                   (3,'China'),
                   (4,'Nepal'),
                   (5,'ian'),
                   (6,'en'),
                   (7,'an'),
                   (8,'am'),
                   (9,'em'),
                   (10,'am')


CREATE TABLE TEST1
(
    ID INT  ,
    COUNTRYX VARCHAR(200) NULL
)


MERGE INTO TEST1 AS C
USING TEST AS CT
        ON C.ID = CT.ID
WHEN MATCHED THEN
    UPDATE SET
      C.COUNTRYX = CT.COUNTRY    
WHEN NOT MATCHED THEN
      INSERT (ID,COUNTRYX)
      VALUES (CT.ID, CT.COUNTRY);
     
SELECT * FROM TEST1

---------------------------------------------------------------------------------
ID COUNTRYX
----------------------------------------------------------------------------------
1    India
2    Brazil
3    China
4    Nepal
5    ian
6    en
7    an
8    am
9    em
9    am

11) Sring Operation 

LEFT & RIGHT

SELECT LEFT  (  'India Govt has started clean india mission.' , 10)
SELECT RIGHT ( 'India Govt has started clean india mission.' , 10) ;

Result
----------------------------------------
(No column name)
-----------------------------------------
 India Govt


Result
----------------------------------------
(No column name)
-----------------------------------------
a mission.


UPPER & LOWER 

SELECT UPPER ( 'India Govt has started clean india mission. '  )
SELECT LOWER (  'India Govt has started clean india mission.'  )

Result
----------------------------------------
(No column name)
-----------------------------------------
 INDIA GOVT HAS STARTED CLEAN INDIA MISSION.


Result
----------------------------------------
(No column name)
-----------------------------------------
india govt has started clean india mission.


12) Date Function

ISDATE :valid date or not

 SELECT MyDate ,ISDATE(MyDate) AS IsValiDate
                                    FROM (
                                                 VALUES(  'IsValiDate'   ),
                                                                (  '2012-02-14' ),
                                                                (  '2012-01-01T00:00:00'  ),
                                                                (  '2014-12-31T23:59:59.9999999' )
                                               )   dt(MyDate)

 
Result
--------------------------------------------------------------------------------------------
MyDate                                    |                    IsValiDate
-------------------------------------------------------------------------------------------
 IsValiDate                                                              0
2012-02-14                                                             1
2012-01-01T00:00:00                                            1
2014-12-31T23:59:59.9999999                             0

13) @@IDENTITY && SCOPE_IDENTITY 

@@IDENTITY returns the last identity value generated by any table in the current session. If the insert statement fires a trigger that inserts into another table with an identity column, the value returned by @@IDENTITY will be that of the table inserted into by the trigger.
SCOPE_IDENTITY returns the last identity value generated by any table in the current session and scope. In the previous scenario, SCOPE_IDENTITY returns the identity value returned by the first insert statement, not the insert into the second table from the trigger.

CREATE TABLE TEST
(
    IDX INT IDENTITY ,
    ID INT  ,
    COUNTRY VARCHAR(200)
)

INSERT INTO TEST (ID,COUNTRY)
VALUES    (1  ,  'India'  ),
                   (2  ,  'Brazil' ),
                   (3  ,  'China' ),
                   (4  ,  'Nepal' ),
                   (5  ,  'ian'     ),
                   (6  ,  'en'      ),
                   (7  ,  'an'     ),
                   (8  ,  'am'    ),
                   (9  ,  'em'   ),
                   (10 , 'am'   )

 SELECT @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT('dbo.TEST');

14) ESCAPE 

 SELECT access_name FROM tbl_user
 WHERE  access_name LIKE  '%%%' 












all ,value return , no match for wildcard %

Instead, you can try one of the following solutions:

SELECT access_nameFROM tbl_userWHERE 
    access_name LIKE  '%[%]%' 
 

SELECT access_name FROM tbl_user  WHERE
    access_name LIKE '%\%%' ESCAPE '\'