NEW Features of SQL Server

DROPIFEXISTS:

A new syntax has been introduced to check if an object exists before dropping it. Previously, if you wanted to check if a procedure existed before you dropped it, you had to write a statement like this
/****** Object:  StoredProcedure [its].[updQueueItemEmailStatus]    ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[its].[updQueueItemEmailStatus]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [its].[updQueueItemEmailStatus]
END
GO

Now with 2016 you can write this as

Syntax:
DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME
IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement then it checks the existence of the object, if it exists it will drop otherwise continues executing the next statement in the block without raising any issues.


DROP PROCEDURE IF EXISTS [its].[updQueueItemEmailStatus]

Note: Now if the stored procedure does not exists it won't throw any Error
If the stored procedure doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the stored procedure WelcomeMessage which is already dropped.


Example:

If the table doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Table dbo.Customers which is already dropped.

If the Database doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Database SqlHintsDemoDB which is already dropped.

In the similar way we can drop Schema And Views


2 JSON support

Similar like XML, SQL Server now supports the JSON format. You can for example convert tabular data to JSON using the FOR JSON clause. An example:

SELECT NAME,first_name,entity_key,name_key FROM its_name
FOR JSON AUTO


Output:

OPENJSON - OPENJSON is a table-value function, which accepts some text which includes a JSON value and returns rows in the required format. FOR JSON, converts the rows into JSON format, but OPENJSON will convert back the JSON text into a table of rows.

IsJSON (< JSON text >) - This function verifies, whether the given text is formatted according to JSON standards/rules.

JSON_Value () - This is a scalar function and it parses the JSON text then extracts a value if it exists in the path arguments. This functionality is similar to 

XPATH (XML Path) which is used for selecting nodes from XML text. In addition, XPath may be used to compute values (e.g. strings, numbers or Boolean values) from the content of an XML document.

FOR JSON PATH – We can define the JSON structure in the query by defining column alias with a dot (.) separated value (for example ‘Root.level1’). This Functionality is similar to FOR XML PATH.

Example 1: Basic FOR JSON PATH example

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON PATH

Output:

[{"FirstName":"Uday","LastName":"Kumar","SurName":"Rajakonda"}]

Example 2 :Basic FOR JSON AUTO requires at-least one table for generating the JSON output

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON AUTO

RESULT:

Msg 13600, Level 16, State 1, Line 10

FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

From the above result it is clear that the FOR JSON AUTO clause works only if at-least one table is mentioned in the from clause.

Example 3: FOR JSON PATH/AUTO example where column names are not specified in the select list, instead * is mentioned

Example 4: FOR JSON PATH/AUTO example where required columns in the JSON output are specified in the SELECT clause

PRINT '******* FOR JSON PATH output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO

Output:

Example 5: To include NULL values in the JSON output, we need to specify the property INCLUDE_NULL_VALUES in the FOR JSON clause

Note: If this option is not specified, in case of NULL value the name-value pair will be removed from the JSON output  

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES

Output: 

Example 6 :We can use the ROOT option in the FOR JSON clause to generate a wrapper object around the generated JSON output. In the below example the ROOT option creates a Customers JSON wrapper object around the generated JSON output:

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES,ROOT('Names')

Example 7: In case of FOR JSON PATH clause using “.” Symbol in the column aliases, we can name the each object in the resultant JSON array as shown below:

SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

Output:

Using “.” symbol in the column aliases doesn’t have any effect in the resulting JSON output in case of FOR JSON AUTO as shown below:


using JSON AUTO


SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON Auto,INCLUDE_NULL_VALUES,ROOT('Names')


Output:

Example 8: We can convert each row into a JSON object with multiple sub-objects by using “.” Symbol in the column alias as shown below:

SELECT   name_key[Name.namekey],name[Name.name],entity_key[Entity.entityKey] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

OutPut:

3 FORMATMESSAGE

FROM 2016, We can supply our own String
Declare @str varchar(max)
set @str=FORMATMESSAGE('Hello %s,Uday %s' ,'RajaKonda', 'Kumar')
select @str

4 STRING_SPLIT

In previous release we used to write lot of code ( we used to create a function to split the string).
Old way of writing code
Example
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM [dbo].[fnSplitString] (@string,',')

But with sql server 2016 we can write as
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM STRING_SPLIT (@string,',')


Output:

Why C Sharp Supports Multiple Inheritance with Interface only.

Multiple Inhertiance

A class which derived from more than one class it is called Multiple inheritance

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

Example:

public class Employee

    {

        public virtual void DoWork()

        {

            //Do work

        }

    }

    public class Manager:Employee

    {

        public override void DoWork()

        {

            //Manager's implementation of do work

        }

    }

    public class Developer : Employee

    {

        public override void DoWork()

        {

            //Deveoper's implementation of do work

        }

    }

    public class JuniorDeveloper:Manager, Developer// Compile Time Error :Cannot have multiple base classes but assume that it were possible

    { }

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

public class A

    {

        public virtual void A_Method()

        {

            Console.WriteLine("Class A Method");

        }

    }

    public class B : A

    {

        public override void A_Method()

        {

            Console.WriteLine("Class B Method");

        }

    }

    public class C : A

    {

        public override void A_Method()

        {

            Console.WriteLine("Class C Method");

        }

    }

    public class D : B, C  // If Multiple inheritence is possible in C# then

    {

        public override void A_Method()

        {

            Console.WriteLine("Class C Method");

        }

    }

    public static void main()

    {

        D objD = new D();

        objD.A_Method();// Making object of class D and calling overloaded method A_method will 

                        //confuse the compiler which class method to call as both inherited class methods has been overloaded.

    }

Explanation:

In the code above we have the A_Method()

which is defined by class A. But, the problem is that class D derives from both classes B and C, which both derive from class A. This means that there are essentially 2 copies of the A_Method()

that are available because there are 2 instances of A in D’s class hierarchy.

And the compiler will give an error and say that the reference to A_Method() is ambiguous.

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

Step1 Interface Definition

public interface IEmployee
{
   string GetEmployeeId();
}
The above code contains a method signature named DoWork ().
 Step 2 : Interface Implementation
As I have already defined an interface IEmployee in the above code snippet. Now if I want to implement the interface to my class Engineer as shown below.
public class Engineer:IEmployee
{

}
Step 3: 

Now if I execute the above code I will get a compile time error

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

Step 4:

The correct implementation of the above code is shown below

As we can see from the above code the signature of the GetEmployeeId() method in the Engineer class is same as the signature of the method in the (IEmployee)Interface.

Step 5:

Interface can only “inherit” from the interface only. Suppose if we want to extend the implementation of an interface in some part of our project without disturbing the already existing interface, in that case we can create a new interface and inherit it into new interface as shown below.

Example:

public interface Ibase1
    {
        void message();
    }
    public interface Ibase2
    {
        void message();
    }
    public class child : Ibase1, Ibase2
    {
        public void message()
        {
            Console.WriteLine("Hello Multiple Inheritance");
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            child obj = new child();
            obj.message();
            Console.ReadLine();
        }
    }

Output:

Important Points to Remembers 
    Class FirstClass { }
    Class SecondClass { }

interface X { }
interface Y { }
You can inherit like the following:

class NewClass : X, Y { }
In the above code, the class "NewClass" is created from multiple interfaces.


class NewClass : FirstClass, X { }
In the above code, the class "NewClass" is created from interface X and class "FirstClass".

WRONG:

class NewClass : FirstClass, SecondClass  { }
C# doesn't support Multiple inheritance

The above code is wrong in C# because the new class is created from class "FirstClass" and class "SecondClass". The reason is that C# does not allows multiple inheritance.



Finally Note: 
Point 1: 
When we use the Multiple inheritance , we use more than one class. Lets  us Assume one condition  like below
class A and class B are base classes and class c is is multiple inherting it and where class c is inheriting a function ,ok it may be possible that this function with same name and same signature can present in both class A and Class B .That time how the compiler will know that which function it should take wheather from class A or class B.
So this time Multiple inheritanc won't work .So avoiding this problem we use Interface. In interface we just declare a function  and in derived class we write the implemenation.

Point 2:
C# does not allow multiple inheritance because of ambiguity.

How to create crystal Reports with MVC.

Visual Studio 2015 installation will not have Crystal Reports and hence we need to  install it you can download it from the following location

Download Link

http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0_18.exe

After downloading the link. Click on Open  and follow the below steps .

After installation of Crystal Reports. Let us start working on the example.

Go to Visual Studio

File-> New-Project

After clicking on ok , the below screen gets displayed: 

Select Empty and select the check box MVC

Configuring and connecting Entity Framework to database

Go to App Data to Add the DataBase as shown below

App Data--->Add--> New Item--> Sql Server DataBase 

In the visual Studio in the solution explorer you can find as shown below

Click on the Open,  the below screen gets displayed  and select New Query and execute the below script  to create the table

Script: 

CREATE TABLE [dbo].[EmployeeInfo] (

    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,

    [EmpName]     VARCHAR (50) NOT NULL,

    [Salary]      INT          NOT NULL,

    [DeptName]    VARCHAR (50) NOT NULL,

    [Designation] VARCHAR (50) NOT NULL,

    [HRA]         AS           ([Salary]*(0.2)),

    [TA]          AS           ([Salary]*(0.15)),

)

Now the table has been created , 

Go To Model Folder

Add-->New Item-->ADO.NET Entity Data Model

Give any Name and click on Add, the below screen will be displayed

And Click On Finish

Controller:

public class CrstalReportExampleController : Controller
    {
        // GET: CrstalReportExample
        public ActionResult Index()
        {
            EmployeeModel emp = new EmployeeModel();
            return View(from employee in emp.EmployeeInfoes.Take(10)
                        select employee);
        }
    }

Index.cs.html

<html>
<body>
    <h4>Employees</h4>
    <hr />
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>Employee Id</th>
            <th>Employee Name</th>
            <th>DeptName</th>  
            <th>Designation</th>  
           
        </tr>
        @foreach (WebApplication1.Models.EmployeeInfo employee in Model)
        {
            <tr>
                <td>@employee.EmpNo</td>
                <td>@employee.EmpName</td>
                <td>@employee.DeptName</td>
                <td>@employee.Designation</td>
            </tr>
        }
    </table>
    <br />
    <a href="~/Reports/WebForm1.aspx">View Report</a>
</body>
</html>

Create a new folder with the name Reports 

Right click on the folder Reports -Select -->Add-->New Item

Click on Add button

Select Standard Button as shown below

Once you press OK in the above screen  dialog, the Report Wizard starts and you get the following dialog where you need to choose the type of Database connection for your Crystal Report. 

Click on Next Button


Choose the columns which you want to display in the reports as shown below

After Clicking on Finish button the below screen gets displayed

Note now, Crystal Report works only with a Crystal Report Viewer control which is available only in ASP.Net Web Forms and hence for displaying a Crystal Report, you will need to add a Web Forms page.

Go to Reports Folder and Select Add--> New Item--> 

Now in this web form we need to add  Crystal Report Viewer control from the ToolBox as shown below

WebForm.aspx( Create this form in the Reports Folder).

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" Height="450" Width="650"/>

    </form>
</body>
</html>


.cs

 private EmployeeModel context = new EmployeeModel();
        protected void Page_Load(object sender, EventArgs e)
        {

            
            CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
            CrystalReport1 crystalReport = new CrystalReport1();
            List<EmployeeInfo> allemps = new List<EmployeeInfo>();
            allemps = context.EmployeeInfoes.ToList();

            crystalReport.SetDataSource(allemps);
            CrystalReportViewer1.ReportSource = crystalReport;
            //Response.Buffer = false;
            Response.ClearContent();
            //Response.ClearHeaders();
        }


Output:

After clicking on view Report

2 Example :

You can display the same report in another way as shown below

Modify the Controller code as shown below

private EmployeeModel context = new EmployeeModel();
        // GET: CrstalReportExample
        public ActionResult Index()
        {
            EmployeeModel emp = new EmployeeModel();
            return View(from employee in emp.EmployeeInfoes.Take(10)
                        select employee);
        }

        public ActionResult ExportCustomers()
        {

            List<EmployeeInfo> allemps = new List<EmployeeInfo>();
            allemps = context.EmployeeInfoes.ToList();


            ReportDocument rd = new ReportDocument();
            rd.Load(Path.Combine(Server.MapPath("~/Reports"), "CrystalReport1.rpt"));

            rd.SetDataSource(allemps);

            Response.Buffer = false;
            Response.ClearContent();
            Response.ClearHeaders();


            Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
            stream.Seek(0, SeekOrigin.Begin);
            return File(stream, "application/pdf", "EmployeeList.pdf");
        }

In the index.cs.html add the below line


<div><a href="@Url.Action("ExportCustomers")"> View Crystal Report </a></div>

output

How to Play Videos in ASP.NET MVC

Go to Visual Studio

File-> New-Project

The below screen gets displayed. Give any name of the application.

After clicking on ok , the below screen gets displayed: 

Select Empty and select the check box MVC

Let us create a custom action result, Now i have created a Folder with the Name CustomResult inside that i have created a new class File as shown Below

Write the below code in the PlayVideo.cs File 

public class PlayVideo:ActionResult

    {

        public override void ExecuteResult(ControllerContext context)

        {

            //The File Path 

            var videoFilePath = HostingEnvironment.MapPath("~/Videos/big_buck_bunny.mp4");

            //The header information 

            context.HttpContext.Response.AddHeader("Content-Disposition", "attachment; filename=Avent Anniversary.mp4");

            var file = new FileInfo(videoFilePath);

            //Check the file exist,  it will be written into the response 

            if (file.Exists)

            {

                var stream = file.OpenRead();

                var bytesinfile = new byte[stream.Length];

                stream.Read(bytesinfile, 0, (int)file.Length);

                context.HttpContext.Response.BinaryWrite(bytesinfile);

            }

        }

    }

Controller . Create a new controller with the Name VideoController

public class VideoController : Controller

    {

        // GET: Video

        public ActionResult Index()

        {

            return new PlayVideo();

        }

    }

Create a new Index 

Now run the application , Now You can see the below output  

Create another Contoller with the Name 

public class PlayVideoLiveController : Controller

    {

        // GET: PlayVideoLive

        public ActionResult Index()

        {

            return View();

        }

    }

Index.cs.html

<video width="320" height="240" controls autoplay="autoplay">

    <source src="@Url.Action("Index","Video")" type="video/mp4">

    </video>

Output:

Working with PDF IN ASP.NET MVC with Entity FrameWork.

With this example you can create pdf and print pdf.

If You are using ASP.NET MVC , PDF can be created by using Rotativa 

Rotativa is a framework that is used to print PDF documents in ASP.NET MVC Applications. This framework we can download using the nuget packages as shown below

Go to Visual Studio

File-> New-Project

The below screen gets displayed. Give any name of the application.

After clicking on ok , the below screen gets displayed: 

Select Empty and select the check box MVC

Let us configure rotative using Nuget as shown below .
Goto to Visual Studio Tools-NuGet Package Manager->Package Manager Console
The below screen gets displayed-> Type the Nuget package command for Rotative

Go to App Data to Add the DataBase as shown below

App Data--->Add--> New Item--> Sql Server DataBase 

In the visual Studio in the solution explorer you can find as shown below

Click on the Open,  the below screen gets displayed  and select New Query and execute the below script  to create the table

Script: 

CREATE TABLE [dbo].[EmployeeInfo] (
    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,
    [EmpName]     VARCHAR (50) NOT NULL,
    [Salary]      INT          NOT NULL,
    [DeptName]    VARCHAR (50) NOT NULL,
    [Designation] VARCHAR (50) NOT NULL,
    [HRA]         AS           ([Salary]*(0.2)),
    [TA]          AS           ([Salary]*(0.15)),
)

Now the table has been created , 

GoTo Model Folder
Add-->New Item-->ADO.NET Entity Data Model

Give any Name and click on Add, the below screen will be displayed

And Click On Finish

Now goto Contoller Folder and Add New Item

Controller Code:

public class MVCPDFController : Controller

    {

        public ActionResult Index()

        {

            var emps = ctx.EmployeeInfoes.ToList();

            return View(emps);

        }

        EmployeeModel ctx;

        public MVCPDFController()

        {

            ctx = new Models.EmployeeModel();

        }

        public ActionResult PrintAllReports()

        {

            var report = new ActionAsPdf("Index");

            return report;

        }

        public ActionResult PrintPdfDetails(int id)

        {

            var emp = ctx.EmployeeInfoes.Where(e => e.EmpNo == id).First();

            return View(emp);

        }

        public ActionResult PrintSalaryDetails(int id)

        {

            var report = new ActionAsPdf("PrintPdfDetails", new { id = id });

            return report;

        }

    }

Index.cs.html

@model IEnumerable<WebApplication1.Models.EmployeeInfo>

@{

    ViewBag.Title = "Index";

}

<h2>Index</h2>

<p>

    @Html.ActionLink("View In PDF Format", "PrintAllReports")

</p>

<table class="table">

    <tr>

        <th>

            @Html.DisplayNameFor(model => model.EmpName)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.Salary)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.DeptName)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.Designation)

        </th>

        <th></th>

    </tr>

    @foreach (var item in Model)

    {

        <tr>

            <td>

                @Html.DisplayFor(modelItem => item.EmpName)

            </td>

            <td>

                @Html.DisplayFor(modelItem => item.Salary)

            </td>

            <td>

                @Html.DisplayFor(modelItem => item.DeptName)

            </td>

            <td>

                @Html.DisplayFor(modelItem => item.Designation)

            </td>

           

            <td>

                @Html.ActionLink("Print Pay Slip", "PrintSalaryDetails", new { id = item.EmpNo })

            </td>

        </tr>

    }

</table>

PrintPDFDetails.cs.html

@model WebApplication1.Models.EmployeeInfo

@{

    ViewBag.Title = "PrintPdfDetails";

}

<div>

<h2> Salary Details of the Employee : @Model.EmpName 

</h2>

<table>

    <tr>

        <td>Department Name :</td>

        <td>@Model.DeptName</td>

    </tr>

    <tr>

        <td>Designation :</td>

        <td>@Model.Designation</td>

    </tr>

</table>

    <table>

        <tr>

            <td>

                <table>

                    <tr>

                        <td colspan="2">Income Details</td>

                    </tr>

                    <tr>

                        <td>Salary</td>

                        <td>@Model.Salary</td>

                    </tr>

                    <tr>

                        <td>HRA</td>

                        <td>@Model.HRA</td>

                    </tr>

                    <tr>

                        <td>TA</td>

                        <td>@Model.TA</td>

                    </tr>

                </table>

            </td>

        </tr>

    </table>

    <p>

        @Html.ActionLink("Back to Home", "Index")

    </p>

</div>

OutPut

On Clicking on Print Pay Slip

On clicking on View pdf