Thursday, February 12, 2015

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

Wednesday, February 11, 2015

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.







Kubernetes

Prerequisites We assume anyone who wants to understand Kubernetes should have an understating of how the Docker works, how the Docker images...