Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT

IDENT_CURRENT('table_name')  -returns the last identity for at table in any scope and any session

Scope - scope means ex- trigger ,stored procedure is scope difrerent from normal query insert
session - session is for current user

 Scope_Identity() - will give the last identity inserted in current scope and current session

@@Identity  -it will return the last identity inserted in any scope and current session.



Create following two tables and trigger



Now we will execute following commands but within same session (with in same query window)



Result of both select statements is empty.

Now we will execute following commands but within same session (with in same query window)

Note: Insert statement on table1 will insert value ‘1' in table 1 and trigger will insert value ‘100' in table2

So we have two insert on single insert

One in table1 and another in table2 so we have two scope one is current related to table1 one another is global scope related two table1 and table2

Now open a new query window (new session) and execute the following commands:

So we have two scenario to compare session and scope

	Session	Scope
@@IDENTITY	Same Session	Global scope value
SCOPE_IDENTITY()	Same Session	Local scope value
IDENT_CURRENT()	May be different	Depends on table name passed in parameter

Scope - scope whether execution query is in same script  stored proc,trigger etc. or in different script like in above example insert query is different and other query is in trigger means other scope

Session- executing query in same window or different or same user is executing in on flow.
Conclusion:
SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).

SQL SERVER – Maximum Number of Index per Table

For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx

Differences between STUFF, REPLACE and SUBSTRING in SQL Server

The differences between STUFF, REPLACE and SUBSTRING functions is one of the most common interview question. By using the STUFF, REPLACE and SUBSTRING functions we can modify the strings as per our requirement.

STUFF():
STUFF is used to replace the part of string with some other string OR It delete a specified length of characters within a string and replace with another set of characters. 

Syntax:

STUFF(string_expression , start, length, string_expression2)

string_expression : represents the string in which the stuff is to be applied. 
start : indicates the starting position of the character in string_expression.
length : indicates the length of characters which need to be replaced. 
string_expression2 : indicates the string that will be replaced to the start position.

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, STUFF(@str,4,5,'_STUFF')    

Output: By observing the output it replace the index position 4 from next 5 characters with '_STUFF' in a give string_expression.

 

REPLACE():
REPLACE is used to replace all the occurrences of the given pattern in a string.

Syntax

REPLACE (string_expression, replace, string_expression2)

string_expression : Specifies the string that contains the substring to replace all instances of with another.
Replace : Specifies the substring to locate.
string_expression2 : Specifies the substring with which to replace the located substring.

Example: 

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, REPLACE(@str,'DEFGH','_REPLACE')  

Output: By observing the output 'DEFGH' is replace with '_REPLACE' in 'ABCDEFGH'.



SUBSTRING(): 
SUBSTRING returns the part of the string from a given string_expression. 

Syntax

SUBSTRING (string_expression, start, length)

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, SUBSTRING(@str,1,3)  

Output: By observing the output it returns specified string from a given string_expression.



Summary
Lets take an example to describe STUFF, REPLACE and SUBSTRING functions.

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, STUFF(@str,4,5,'_STUFF')    
3. SELECT @str, REPLACE(@str,'DEFGH','_REPLACE')    
4. SELECT @str, SUBSTRING(@str,1,3)  

Output: Here you can observe the clear differences between STUFF, REPLACE and SUBSTRING.

Factorial No Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter the Factorial Number");
            int no = Convert.ToInt32( Console.ReadLine());
            int x=1;
            for (int i = 1; i <= no; i++)
            {
                x = x * i;
            }

            Console.WriteLine("Factorial of "+ no +" = "+x);
            Console.ReadLine();
        }
    }
}

Recursion

static void Main(string[] args)
        {
            Console.WriteLine("Enter the Factorial Number");
            int no = Convert.ToInt32( Console.ReadLine());

            int x = Fac(no);
           Console.WriteLine("Factorial of "+ no +" = "+x);
          

            //Console.WriteLine("Factorial of "+ no +" = "+x);
            Console.ReadLine();
        }

        public static int Fac(int x)
        {
            if (x == 1)
            { return 1; }
            return (x * Fac(x - 1));
            
           

            
        }

$on $emit and $broadcast in Angularjs

<html>
<head>
    <script src="~/Scripts/angular.min.js"></script>
    <script src="~/Scripts/AngularT2.js"></script>
    <script src="~/Scripts/AreaService.js"></script>
</head>
<body ng-app="myapp">
  

    <div ng-controller="TopLevel" style="border:solid;">
        <b> Top Level</b> <button ng-click="SendDownword()">SendDown</button>

        <div ng-controller="MiddleLevel" style="border: solid; ">
            <b> Middle Level</b>
            <div ng-controller="LowerLevel" style="border: solid; ">
                <b> Lower Level</b>
                <button ng-click="SendUpword()">SendUp</button>
            </div>
        </div>

    </div>


</body>
</html>

/// <reference path="angular.min.js" />
var app2 = angular.module("myapp", []);

app2.controller("TopLevel", function ($scope) {
    $scope.$on("myevent", function (event, args) {
        alert("This is parent ="+args);
    });

    $scope.SendDownword = function () {
        $scope.$broadcast("myevent", "sent down");
    }
   
});

app2.controller("MiddleLevel", function ($scope) {
    $scope.$on("myevent", function (event,data) {
        alert("this is middle=" + data);
    });

});

app2.controller("LowerLevel", function ($scope) {
    $scope.$on("myevent", function (event, data) {
        alert("this is lower level=" + data);
    });

    $scope.SendUpword = function () {
        $scope.$emit("myevent", "sent up");
    }
});



});

$q ,defer and promise example

------------------

var msgDefer = $q.defer();
    msgDefer.promise.then(function (msg) {
        alert("1" + msg);
        return "1";
}
    ).then(function (msg) {
        alert("2" + msg);

    }).catch(function (msg) {
        alert("reject" + msg);
    });

----------

<html>
<head>
    <script src="~/Scripts/angular.min.js"></script>
    <script src="~/Scripts/AngularT2.js"></script>
    <script src="~/Scripts/AreaService.js"></script>
</head>
<body ng-app="myapp">
    <div ng-controller="contrlT">
        Result: {{result}}
        Elapsed Time :{{elapsedTime}}
        Failure Reason:{{failure}}
    </div>
    <div ng-controller="cntrl1">
        FirstName:<p>{{firstName}}</p>
        Department:<p>{{department}}</p>
        Area: <b>{{Area}}</b>

    </div>
    <div ng-controller="cnrl2">
     Company: {{company}}
     Department:{{department}}
     Add: {{Add}}
        Divide:{{Divide}}
    </div>

   

</body>
</html>

/// <reference path="angular.min.js" />
var app2 = angular.module("myapp", []);

app2.controller("Parent", function($scope) {
    $scope.$on("myevent", function(event, args) {
        alert("This is parent");
    });

    $scope.SendDownword = function() {
        $scope.$broadcast("myevent", "sent down");
    }

});


app2.controller("contrlT", function($scope, $q) {


    function Add(x, y) {
        var defers = $q.defer();
        var z = x + y;
        if (z >= 0)
            defers.resolve(z);
        else if (z < 0)
            defers.reject("negative values = " + z);
        return defers.promise;
    }
    var starttime = Date.now();
    Add(4, 5).then(function(result) {
            $scope.result = result;
            $scope.elapsedTime = Date.now() - starttime;
        })
        .catch(function(failure) {
            $scope.failure = failure;
        })
        .finally(function() {
            $scope.elapsedTime = "changed time";
        })

    //function Add(x, y,callback1)
    //{
    //    $timeout(function () { callback1(x + y); }, 100)

    //}

    //var starttime = Date.now();
    //Add(5, 6, function (result) {
    //    Add(result, 66, function (result) { 
    //    $scope.result = result;
    //    $scope.elapsedTime = Date.now() - starttime;
    //    });
    //});



    //var deferT = $q.defer();
    //deferT.promise
    //.then(function (msg) {
    //    alert("promise  alert" + msg);
    //    return "2";
    //}).then(function (msg) {

    //    alert("promise alert" + msg);
    //    throw Ex;
    //}, function (msg1) {
    //    alert(msg);
    //})



    //deferT.resolve("1");
    //deferT.reject("exeception occured");
});

How IIS Process ASP.NET Request

Introduction

When request come from client to the server a lot of operation is performed before sending response to the client. This is all about how IIS Process the request.  Here I am not going to describe the Page Life Cycle and there events, this article is all about the operation of IIS Level.  Before we start with the actual details, let’s start from the beginning so that each and everyone understand it’s details easily.  Please provide your valuable feedback and suggestion to improve this article.

What is Web Server ?

When we run our ASP.NET Web Application from visual studio IDE, VS Integrated ASP.NET Engine is responsible for executing all kind of asp.net requests and responses.  The process name is “WebDev.WebServer.Exe” which takes care of all request and response of a web application which is running from Visual Studio IDE.
Now, the name “Web Server” comes into picture when we want to host the application on a centralized location and wanted to access from many places. Web server is responsible for handle all the requests that are coming from clients, process them and provide the responses.

What is IIS ?

IIS (Internet Information Services) is one of the most powerful web servers from Microsoft that is used to host your ASP.NET Web application. IIS has its own ASP.NET Process Engine to handle the ASP.NET request. So, when a request comes from client to server, IIS takes that request and process it and send the response back to clients.

Request Processing :

Hope, till now it’s clear to you that what is the Web server and IIS is and what is the use of them. Now let’s have a look how they do things internally. Before we move ahead, you have to know about two main concepts

1.    Worker Process
2.   Application Pool

Worker Process:  Worker Process (w3wp.exe) runs the ASP.Net application in IIS. This process is responsible for managing all the request and response that are coming from the client system.  All the ASP.Net functionality runs under the scope of the worker process.  When a request comes to the server from a client worker process is responsible for generating the request and response. In a single word, we can say worker process is the heart of ASP.NET Web Application which runs on IIS.
Application Pool: Application pool is the container of the worker process.  Application pools are used to separate sets of IIS worker processes that share the same configuration.  Application pools enable a better security, reliability, and availability for any web application.  The worker process serves as the process boundary that separates each application pool so that when one worker process or application is having an issue or recycles, other applications or worker processes are not affected. This makes sure that a particular web application doesn’t impact other web application as they are configured into different application pools.

Application Pool with multiple worker processes is called “Web Garden.”
Now, I have covered all the basic stuff like the Web server, Application Pool, Worker process. Now let’s have a look how IIS process the request when a new request comes up from a client.
If we look into the IIS 6.0 Architecture, we can divide them into Two Layer

1.    Kernel Mode
2.    User Mode

Now, Kernel mode is introduced with IIS 6.0, which contains the HTTP.SYS.  So whenever a request comes from Client to Server, it will hit HTTP.SYS First.

Now, HTTP.SYS is Responsible for pass the request to the particular Application pool. Now here is one question, How HTTP.SYS does come to know where to send the request?  This is not a random pickup. Whenever we create a new Application Pool, the ID of the Application Pool is being generated, and it’s registered with the HTTP.SYS. So whenever HTTP.SYS Received the request from any web application, it checked for the Application Pool and based on the application pool it sends the request.

So, this was the first steps of IIS Request Processing.
Till now, Client Requested for some information and request came to the Kernel level of IIS means at HTTP.SYS. HTTP.SYS has been identified the name of the application pool where to send. Now, let’s see how this request moves from HTTP.SYS to Application Pool.

In User Level of IIS, we have Web Admin Services (WAS) which takes the request from HTTP.SYS and pass it to the respective application pool.

When Application pool receives the request, it just passes the request to worker process (w3wp.exe). The worker process “w3wp.exe” looks up the URL of the request to load the correct ISAPI extension. ISAPI extensions are the IIS way to handle requests for different resources. Once ASP.NET is installed, it installs its own ISAPI extension (aspnet_isapi.dll) and adds the mapping into IIS.
Note: Sometimes if we install IIS after installing asp.net, we need to register the extension with IIS using an aspnet_regiis command.

When Worker process loads the aspnet_isapi.dll, it starts an HTTPRuntime, which is the entry point of an application. HTTPRuntime is a class which calls the ProcessRequest method to start Processing.

When this method called, a new instance of HTTPContext is created.  Which is accessible using HTTPContext.Current  Properties. This object remains alive during the life time of object request.  Using HttpContext.Current we can access some other objects like Request, Response, Session, etc.

After that HttpRuntime load, an HttpApplication object with the help of  HttpApplicationFactory class. Every request should pass through the corresponding HTTPModule to reach to HTTPHandler, this list of a module is configured by the HTTPApplication.
Now, the concept comes called “HTTPPipeline.” It is called a pipeline because it contains a set of HttpModules ( For Both Web.config and Machine.config level) that intercept the request on its way to the HttpHandler. HTTPModules are classes that have access to the incoming request. We can also create our HTTPModule if we need to handle anything during upcoming request and response.

HTTP Handlers are the endpoints in the HTTP pipeline. All request that is passing through the HTTPModule should reach to HTTPHandler.  The  HTTP Handler generates the output for the requested resource. So, when we were requesting for any aspx web pages,   it returns the corresponding HTML output.
All the request now passes from httpModule to respective HTTPHandler then the method and the ASP.NET Page life cycle starts.  This ends the IIS Request processing and starts the ASP.NET Page Lifecycle.

Conclusion

When the client request for some information from a web server, request first reaches to HTTP.SYS of IIS. HTTP.SYS then send the request to particular  Application Pool. Application Pool then forwards the request to worker process to load the ISAPI Extension which will create an HTTPRuntime Object to Process the request via HTTPModule and HTTP handler. After that, the ASP.NET Page LifeCycle events start.

Microsoft Visual Studio 2013 Serial Numbers

Microsoft Visual Studio 2013 Serial Numbers

Microsoft Visual Studio Ultimate  2013

BWG7X-J98B3-W34RT-33B3R-JVYW9

============================

Microsoft Visual Studio Professional 2013

XDM3T-W3T3V-MGJWK-8BFVD-GVPKY

============================

Microsoft Visual Studio Premium 2013

FBJVC-3CMTX-D8DVP-RTQCT-92494

Caching application data in asp.net

The following stored procedure takes 5 seconds to execute and return data. We are using WAITFOR DELAY, to introduce artificial query processing time of 5 seconds. 
CREATE Procedure spGetProducts  
as  
Begin  
 Waitfor Delay '00:00:05'  
 Select * from tblProducts  
End

Create an asp.net web application, copy and paste the following HTML in WebForm1.aspx.
<div style="font-family:Arial">
    <asp:Button ID="btnGetProducts" runat="server" Text="Get Products Data" 
        onclick="btnGetProducts_Click" />
    <br /><br />
    <asp:GridView ID="gvProducts" runat="server">
    </asp:GridView>
    <br />
    <asp:Label ID="lblMessage" Font-Bold="true" runat="server"></asp:Label>
</div>

Copy and paste the following code in WebForm1.aspx.cs. The code is well documented and is self explanatory.
protected void btnGetProducts_Click(object sender, EventArgs e)
{
    DateTime dtStartDateTime = DateTime.Now;
    System.Text.StringBuilder sbMessage = new System.Text.StringBuilder();
    // Check if the data is already cached
    if (Cache["ProductsData"] != null)
    {
        // If data is cached, retrieve data from Cache using the key "ProductsData"
        DataSet ds = (DataSet)Cache["ProductsData"];
        // Set the dataset as the datasource
        gvProducts.DataSource = ds;
        gvProducts.DataBind();
        // Retrieve the total rows count
        sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from cache.");
    }
    // If the data is not cached
    else
    {
        // Get the data from the database
        DataSet ds = GetProductsData();
        // Cache the dataset using the key "ProductsData"
        Cache["ProductsData"] = ds;
        // Set the dataset as the datasource
        gvProducts.DataSource = ds;
        gvProducts.DataBind();
        sbMessage.Append(ds.Tables[0].Rows.Count.ToString() + " rows retrieved from database.");
    }
    DateTime dtEndDateTime = DateTime.Now;
    sbMessage.Append((dtEndDateTime - dtStartDateTime).Seconds.ToString() + " Seconds Load Time");
    lblMessage.Text = sbMessage.ToString();
}

private DataSet GetProductsData()
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlConnection con = new SqlConnection(CS);
    SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;

    DataSet dsProducts = new DataSet();
    da.Fill(dsProducts);

    return dsProducts;
}

In this video, we discussed about storing application data in cache, using direct assignment. That is using a key and assiging value to it, as shown below.
Cache["ProductsData"] = ds