SQL 2012 NEW FEATURES PART 1

1 Sequence Object.


We used  IDENTITY to get sequential Number(1,2,3......). Now, In SQL Server 2012, there is a new feature called Sequence. 

Sequence it  is a user-defined object that creates a sequence of a number.
It is same as  identity column.

Syntax for creating Sequence object:

CREATE SEQUENCE [schema_name . ] sequence_name  
[ AS [ built_in_integer_type | user-defined_integer_type ] ]  
[ START WITH <constant> ]  
[ INCREMENT BY <constant> ]  
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
[ CYCLE | { NO CYCLE } ]  
[ { CACHE [ <constant> ] } | { NO CACHE } ]  

[ ; ]

Explanation:

START WITH: From which Number it should start
INCREMENT BY: Increment Value 
MINVALUE: Minimum Value.
MAXVALUE: Maxmimum Value
CYCLE: If we set the MAXVALUE with the CYCLE, when it reaches the MAXVALUE it will recycle to the MINVALUE and start again.
CACHE: If a CACHE VALUE IS PROVIDED, THEN SQL Server will cache (store in memory) the amount of values specified.

Example:

CREATE SEQUENCE Sequence AS BIGINT  

START WITH 12  

INCREMENT BY 1  

MINVALUE 12  

MAXVALUE 30  

CYCLE  

CACHE 10; 

Now, I am going to Create a table.

CREATE TABLE Employee
(  
ID INT,  
EName VARCHAR(150) NOT NULL  
)


The keyword "NEXT VALUE" is used to get the next sequential number from the Sequence.


Now i am inserting values into the table Employee using the Next Value as shown below 

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR');  INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR'); 

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'UDAY');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ABC');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'SDASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ASDFSD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'KITTU');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'AFSASD');  

INSERT Employee (ID, EName)VALUES (NEXT VALUE FOR SEQUENCE , 'ANKUR');  


SELECT * FROM Employee

How to Add Sequence object to  table

CREATE TABLE Students
(  
ID  INT DEFAULT (NEXT value for dbo.Sequence),  
Name   VARCHAR(50) NOT NULL  
) 

Note: 
dbo.Sequence is the Table name which we have created before(Sequence Object)

INSERT Students(Name)VALUES ('UDAY');  

INSERT Students (Name)VALUES ('Kumar');  

INSERT Students (Name)VALUES ('Kittu');  

Now, After Inserting Rows into students Table , we can see the values 

SELECT * FROM STUDENTS

Note: Here we can see the id value starts with 14

Alter Command 



Alter SEQUENCE SEQUENCE 
RESTART WITH 2  
INCREMENT BY 1  
MINVALUE 2  
MAXVALUE 90  
CYCLE  
CACHE 10;  

To View all Sequences in DataBase

SELECT * FROM SYS.SEQUENCES

Drop Sequence
Syntax:DROP SEQUENCE SequenceName

Identity: Identity value is specific only to a  table column.
Sequence: Sequence number we can use across multiple tables.

Creating Sequence using UI


Click on DataBase

And Click on Ok Button.


Pagination

Using OFFSET and FETCH in SQL Server 2012

Example:

CREATE TABLE Student  
(  
 ID INT,
 SNAME VARCHAR(50)
)  

INSERT INTO Student VALUES (1,'a')
INSERT INTO Student VALUES (2,'B')
INSERT INTO Student VALUES (3,'C')
INSERT INTO Student VALUES (4,'d')
INSERT INTO Student VALUES (5,'e')
INSERT INTO Student VALUES (6,'f')
INSERT INTO Student VALUES (7,'g')
INSERT INTO Student VALUES (8,'h')
INSERT INTO Student VALUES (9,'i')  
INSERT INTO Student VALUES (10,'j')
INSERT INTO Student VALUES (11,'k')
INSERT INTO Student VALUES (12,'l')
INSERT INTO Student VALUES (13,'m')
INSERT INTO Student VALUES (14,'n')
INSERT INTO Student VALUES (15,'o')
INSERT INTO Student VALUES (16,'p')
INSERT INTO Student VALUES (17,'q')

SELECT * FROM Student

OFFSET Keyword:  Here query will skip the number of records we specified in OFFSET n Rows.

SELECT *  
FROM STUDENT  
ORDER BY ID  
OFFSET 2 ROWS  

OFFSET 2 ROWS, means sql server will skip 2 records from the result and display the remaining records , now see the output  in the below screenshot.

FETCH NEXT Keywords: 
If we are not using Offset than we will get an error as shown below.

SELECT *  

FROM STUDENT  

ORDER BY ID  

--OFFSET 2 ROWS  

FETCH NEXT 10 ROWS ONLY;

Example with offset and fetch

SELECT *  

FROM STUDENT  

ORDER BY ID  

OFFSET 2 ROWS  

FETCH NEXT 6 ROWS ONLY;

Now the output is

Here it display only the 6 Rows starting from 3 to 8)

Bec: we have given Offset :2 (To skip 2 Records)

Fetch 6( To display 6 Records)

We cannot use Fetch Next without Offset.

With Stored Procedure

CREATE PROCEDURE StudentPagination  

(  

  @PageNo INT,  

  @Size INT  

)  

AS  

DECLARE @Count INT  

SET @Count = (@PageNo-1)*@Size  

SELECT  *  

FROM Student 

ORDER BY ID  

OFFSET @Count ROWS  

FETCH NEXT @Size ROWS ONLY

SQL 2000 / SQL 2005/ SQL 2008 / SQL 2012

SQL SERVER 2000

Only Maximum of 65,535 databases

No XML data type

No Exception Handling

We cannot Encrypt the DataBase


SQL SERVER 2005

XML Data Type is There

Exception Handling Is There

Varchar(max) and VarBinary(max)

We can Encrypt the DataBase


SQL SERVER 2008

Merge is included

Common Table Expression

Four dateTime Data Types Introduced


SQL SERVER 2012

Pagination 
Sequence Object
Using THROW Statement


CHOOSE (Transact-SQL)
IIF (Transact-SQL)

PARSE (Transact-SQL)
TRY_PARSE (Transact-SQL)
TRY_CONVERT (Transact-SQL)
Date and time Functions

DATEFROMPARTS Function
TIMEFROMPARTS Function
DATETIMEFROMPARTS Function
EMONTH Function .. and so on
String Functions

FORMAT (Transact-SQL)
CONCAT (Transact-SQL)
Analytic Functions

First_Value Function
Last_Value Function

File UPLOAD WITH HANDLER USING JQUERY

File ->New->WebSite

Add->Add New Item

Add New WebForm

And Click on Add Button

Now, we need to Add JQuery Files 

Now, we Need to Add Jquery Ajax File as shown below.

We can download the Jquery Files From the below link.

http://jqueryui.com/download/all/
Select Add-> Add New Item

And Click on Add Button., write the following javascript code as shown below.


Javascript1.js Code

$(document).ready(function () {
    FileUpload();
});

function FileUpload() {
    var extension = '';
    var button = $('#browsefilebtn'), interval;

    $.ajax_upload(button, {
        action: 'Handler.ashx',
        name: 'myfile',
        type: 'GET',
        onSubmit: function (file, ext) {
            extension = ext;
            interval = window.setInterval(function () {
            }, 200);
        },
        
        onComplete: function (file, response) {
            response = response.replace(/"/gi, "");
            var zip = 'responseText';
            var src = response.substring(0, response.lastIndexOf("."));
            if (file != "Invalid" || file != "Loading Error") {
               $('#divItemfiles').append('<div> <a class="files" target="_blank" href="Files/UploadFiles/' + response + '") >' + response + '</a> <a href="javascript:"  onclick="DeleteFile(this)"></a></div>');
                window.clearInterval(interval);
            }
            else {
                alert("Error Message");
            }
           }
    });

}


screen shot of javascript1.js File

Now, we need to Add Handler.

Add->New Item

Now, Create the Folder Structure  in the Application as shown below(i.e Add two Folders)

Now, write the following code in Handler as shown Below.


Handler Code


public class Handler : IHttpHandler {
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        string strResponse = "error";
        try
        {
            string strFileName = System.IO.Path.GetFileName(context.Request.Files[0].FileName);
            string strExtension = System.IO.Path.GetExtension(context.Request.Files[0].FileName).ToLower();

            string strSaveLocation = context.Server.MapPath("~/Files/UploadFiles");
            string filename = System.IO.Path.Combine(strSaveLocation, strFileName);
            HttpPostedFile oFile = context.Request.Files[0];
            oFile.SaveAs(filename);
            System.Web.Script.Serialization.JavaScriptSerializer javaScriptSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            strResponse = javaScriptSerializer.Serialize(strFileName);
        }
        catch (Exception Ex)
        {
            strResponse = Ex.Message;
        }
        context.Response.Write(strResponse);
    }
    public bool IsReusable {
        get {
            return false;
        }
    }

ScreenShot of Handler Code

Write the Following Code in Default page as shown Below.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Jquery.js"></script>
    <script src="jquery-ui.js"></script>
    <script src="jquery.ajax_upload.0.6.min.js"></script>
    <script src="JavaScript.js"></script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <input type="Button" runat="server" value="Browse" id="browsefilebtn" />
    </div>
        <div id="divItemfiles" runat="server">
        </div>
    </form>
</body>

</html>

Note: We need not write any Code in .cs Page.


Output:

Click on Browse Button and Now, u can upload Files as shown Below.

Facebook LIKE BUTTON IN ASP.NET

Go to the developers WebSite of Facebook

https://developers.facebook.com/

The Following Screen is displayed

Click on Docs

1. In Docs Click on Sharing

1. In Sharing Clik on Social Plugins

Click on Like Button (Web)

The Following Screen is Displayed.

Now, Click on Get Code Button.

Now, Click on Get Code.

Copy the Javascript Code 

<div id="fb-root"></div>
<script>(function(d, s, id) {
  var js, fjs = d.getElementsByTagName [0];
  if (d.getElementById(id)) return;
  js = d.createElement ; js.id = id;
  js.src = "//connect.facebook.net/en_US/sdk.js#xfbml=1&version=v2.0";
  fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>


Now, Open Visual Studio 

File-> New- Website

Click on Asp.net Empty Web Site.

Add-> Add New Item

Write the Following Code

<head runat="server">
    <title></title>
</head>
<body>
    <div id="fb-root"></div>
    <script>(function (d, s, id) {
    var js, fjs = d.getElementsByTagName(s)[0];
    if (d.getElementById(id)) return;
    js = d.createElement(s); js.id = id;
    js.src = "//connect.facebook.net/en_US/all.js#xfbml=1";
    fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>

    <form id="form1" runat="server">
    <div>
        <div class="fb-comments" data-href="https://developers.facebook.com/docs/plugins/" data-numposts="10" data-colorscheme="light"></div>
    </div>
    </form>
</body>

</html>


Run,and see the output.