Code First migrations and deployment with the Entity Framework

The Code First Migrations feature solves this problem by enabling Code First to update the database schema instead of dropping and re-creating the database. In this tutorial, you'll deploy the application, and to prepare for that you'll enable Migrations.

1. Disable the initializer that you set up earlier by commenting out or deleting the contextselement that you added to the application Web.config file.
   XML

   <entityFramework>
     <!--<contexts>
       <context type="ContosoUniversity.DAL.SchoolContext, ContosoUniversity">
         <databaseInitializer type="ContosoUniversity.DAL.SchoolInitializer, ContosoUniversity" />
       </context>
     </contexts>-->
     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
       <parameters>
         <parameter value="v11.0" />
       </parameters>
     </defaultConnectionFactory>
     <providers>
       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
     </providers>
   </entityFramework>
   

2. Also in the application Web.config file, change the name of the database in the connection string to ContosoUniversity2.
   XML

   <connectionStrings>
     <add name="SchoolContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
   </connectionStrings>
   

   This change sets up the project so that the first migration creates a new database. This isn't required but you'll see later why it's a good idea.
3. From the Tools menu, select NuGet Package Manager > Package Manager Console.
4. At the PM> prompt enter the following commands:
   text

   enable-migrations
   add-migration InitialCreate
   

   
   The enable-migrations command creates a Migrations folder in the ContosoUniversity project, and it puts in that folder a Configuration.cs file that you can edit to configure Migrations.
   (If you missed the step above that directs you to change the database name, Migrations will find the existing database and automatically do the add-migration command. That's okay, it just means you won't run a test of the migrations code before you deploy the database. Later when you run the update-database command nothing will happen because the database already exists.)
   

Difference between HAVING and WHERE Clause

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)FROM titles INNER JOIN publishersON titles.pub_id = publishers.pub_idWHERE publishers.state = 'CA'GROUP BY titles.pub_idHAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

Union VS Union All

UNION	UNION ALL
UNION removes duplicate rows.	“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort	“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.	UNION ALL can work with all data type columns.

Refer

Types of Routing

Routing is a mechanism to process the incoming url that is more descriptive and give desired response. In this case, URL is not mapped to specific files or folder as was the case of earlier days web sites.

Namespace for Routing : System.Web.Routing

There are two types of routing (after the introduction of ASP.NET MVC 5).

• Convention based routing - to define this type of routing, we call MapRoute method and set its unique name, url pattern and specify some default values.

File in following path App_Start/RouteConfig.cs page like this (It is convention based routing)

 public static void RegisterRoutes(RouteCollection routes)

        {

            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(

                name: "Default",

                url: "{controller}/{action}/{id}",

                defaults: new { controller = "Finance", action = "Finance", id = UrlParameter.Optional }

            );

        }

• Attribute based routing - to define this type of routing, we specify the Route attribute in the action method of the controller.

Different types of WCF bindings

You can also define your own custom binding in WCF to fulfill your need. All built in bindings are defined in the System.ServiceModel Namespace. Here is the list of 10 built in bindings in WCF which we commonly used:

1. Basic binding

   This binding is provided by the BasicHttpBinding class. It is designed to expose a WCF service as an ASMX web service, so that old clients (which are still using ASMX web service) can consume new service. By default, it uses Http protocol for transport and encodes the message in UTF - 8 text for-mat. You can also use Https with this binding.

2. Web binding

   This binding is provided by the WebHttpBinding class. It is designed to expose WCF services as Http requests by using HTTP-GET, HTTP-POST. It is used with REST based services which may give output as an XML or JSON format. This is very much used with social networks for implementing a syndication feed.

3. Web Service (WS) binding

   This binding is provided by the WSHttpBinding class. It is like as Basic binding and uses Http or Https protocols for transport. But this is designed to offer various WS - * specifications such as WS – Reliable Messaging, WS - Transactions, WS - Security and so on which are not supported by Basic binding.
      wsHttpBinding= basicHttpBinding + WS-* specification

4. WS Dual binding

   This binding is provided by the WsDualHttpBinding class. It is like as wsHttpBinding except it sup-ports bi-directional communication means both clients and services can send and receive messages.

5. TCP binding

   This binding is provided by the NetTcpBinding class. It uses TCP protocol for communication be-tween two machines with in intranet (means same network). It encodes the message in binary format. This is faster and more reliable binding as compared to the Http protocol bindings. It is only used when communication is WCF - to – WCF means both client and service should have WCF.

6. IPC binding

   This binding is provided by the NetNamedPipeBinding class. It uses named pipe for Communication between two services on the same machine. This is the most secure and fastest binding among all the bindings.

7. MSMQ binding

   This binding is provided by the NetMsmqBinding class. It uses MSMQ for transport and offers sup-port to disconnected message queued. It provides solutions for disconnected scenarios in which service processes the message at a different time than the client send the messages.

8. Federated WS binding

   This binding is provided by the WSFederationHttpBinding class. It is a specialized form of WS binding and provides support to federated security.

9. Peer Network binding

   This binding is provided by the NetPeerTcpBinding class. It uses TCP protocol but uses peer net-working as transport. In this networking each machine (node) acts as a client and a server to the other nodes. This is used in the file sharing systems like torrent.

10. MSMQ Integration binding

    This binding is provided by the MsmqIntegrationBinding class. This binding offers support to communicate with existing systems that communicate via MSMQ.

Choosing an Appropriate WCF binding

. Depending upon your requirements, you can choose a binding for your service as shown below in the diagram:

WCF bindings comparison

Binding

Protocol/Transport

Message Encoding

Security

Default Session

Transaction

Duplex

BasicHttpBinding

Http, Https

Text

None

No

-

-

WSHttpBinding

Http, Https

Text

Message

Optional

Yes

-

WSDualHttpBinding

Http, Https

Text

Message

Yes

Yes

Yes

NetTcpBinding

TCP

Binary

Transport

Optional

Yes

Yes

NetNamedPipeBinding

Named Pipe

Binary

Transport

Yes

Yes

Yes

NetMsmqBinding

MSMQ

Binary

Transport

Yes

Yes

No

WSFederationHttpBinding

Http, Https

Text

Message

Yes

Yes

No

NetPeerTcpBinding

P2P

Binary

Transport

-

-

Yes

MsmqIntegrationBinding

MSMQ

Not Supported

Transport

Yes

Yes

-

Inheritance in c#

Base Abstract  Class

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

namespace LearnInheritance
{
    public abstract class BaseAbstractClass
    {
        public abstract string PrintAbstract();// can not contain the body
       

        public virtual string PrintVirtual()
        {
            return "Base virtual Class";
        }
    }
}

Interface Class

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

namespace LearnInheritance
{
    interface Interface
    {
        string PrintInterface();
    }
}

Derived  Class

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

namespace LearnInheritance
{
    class DerivedClass : BaseAbstractClass, Interface
    {
        public override string PrintAbstract()
        {
            return "Derived Abstract Class";
        }

        public override string PrintVirtual()
        {
            return "Derived virtual Class";
        }

        public string PrintInterface()
        {
            return "Derived Interface Class";
        }
    }
}

Secondary Derived Class

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

namespace LearnInheritance
{
    class SecondDerivedClass : DerivedClass, Interface
    {
        public override string PrintAbstract()
        {
            //return base.PrintAbstract();
            return "Second Derived Class";
        }
        public new string PrintInterface()
        {
            return "Second Derived Interface Class";
            //return base.PrintInterface();
        }
    }
}

Program

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

namespace LearnInheritance
{
    class Program
    {
        static void Main(string[] args)
        {
            DerivedClass dc = new DerivedClass();
            Console.WriteLine(dc.PrintAbstract());
            Console.WriteLine(dc.PrintVirtual());
            Console.WriteLine(dc.PrintInterface());
            SecondDerivedClass sdc = new SecondDerivedClass();
            Console.WriteLine(sdc.PrintAbstract());
            Console.WriteLine(sdc.PrintInterface());
        }
    }
}

Output

Restrictions on Updating Data Through Views - SQL

You can insert, update, and delete rows in a view, subject to the following limitations:

• If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
• You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
• All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
• Text and image columns can't be modified through views.
• There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.

Using WITH CHECK OPTION

The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Listing 9.12 creates a view showing customers from Paris using the WITH CHECK OPTION statement.

Listing 9.12 Creating a View Using WITH CHECK OPTION

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'

WITH CHECK OPTION

The following Transact-SQL statement attempting to update data by moving everyone from Paris to Lyons will fail because Lyons does not meet the criteria defined in the view. If you did not have WITH CHECK OPTION defined, the UPDATE statement would succeed, and a requery of the view would return no rows. Here's the statement:

UPDATE vwCustomersParis
SET City = 'Lyons'

You may have noticed in Access that placing criteria on the RecordSource query of a form limits the records that are displayed in the form when you open it, but it doesn't limit what records can be added in the form. Using an Access project or an Access database with a form bound to a view that contains WITH CHECK OPTION would allow you to automatically have the criteria enforced for new or updated records.

Example :

create table Employee ( 

id int identity(1,1) primary key,

Name varchar(500)

)

create table Salary 

( 

id int identity(1,1) primary key,

Employee_id int ,

Amount int,

foreign key(Employee_id) REFERENCES Employee(id) 

)

insert into Employee values ('Kumar')

insert into Employee values ('Naveen')

insert into Employee values ('Madhu')

select * from Employee 

insert into Salary values (1,10000)

insert into Salary values (2,20000)

insert into Salary values (3,30000)

select * from Salary 

alter view VEmpSalary 

AS

select e.id,e.Name,s.Amount from Employee e 

join Salary s on e.id = s.Employee_id

select * from VEmpSalary

update VEmpSalary set Amount = 15000 where ID=1

select * from VEmpSalary

update VEmpSalary set name = 'Kumar',Amount = 18000 where ID=1

MVC5 Vs MVC6 in ASP.NET

1. .NET framework is now part of your project and you can point to multiple frameworks at the same. Like in the figure its pointing to 4.5 framework ( full framework) and also to .NET core ( which is cross platform depending what you choose).
   
2. Global.asax is replaced by startup.cs which is more light weight and customizable.
   
3. Web.config configuration will not go in Config.json.
   
4. As a developer we have been adding references using our favorite add reference , you can now also add in project.json and your project will reference it automatically and also vice versa.
   
5. They have given a special wwwroot folder which will store static files of your project. Any files including HTML files, CSS files, image files, and JavaScript files which are sent to the users browser should be stored inside this folder.
   
6. There is a dependencies node which shows which JavaScript files have been used in your project

Point 3 have been explained in detail below :

New Configuration and AppSettings for MVC6 - Web.config is Gone

Web.config is gone but the new solution is great, you get a dependency injected POCO with strongly typed settings instead

New Settings File - appsettings.json

Instead of web.config, all your settings are now located in appsettings.json. Here’s what the default one looks like, though I’ve also added an AppSettings section:

{

  "AppSettings": {

    "BaseUrls": {

      "API": "https://localhost:44307/",

      "Auth": "https://localhost:44329/",

      "Web": "https://localhost:44339/"

    },

    "AnalyticsEnabled": true

  },

  "Data": {

    "DefaultConnection": {

      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=aspnet5-AppSettings1-ad2c59cc-294a-4e72-bc31-078c88eb3a99;Trusted_Connection=True;MultipleActiveResultSets=true"

    }

  },

  "Logging": {

    "IncludeScopes": false,

    "LogLevel": {

      "Default": "Verbose",

      "System": "Information",

      "Microsoft": "Information"

    }

  }

}

Notice that we’re using JSON instead of XML now. This is pretty great with one big exception, No Intellisense.

Create an AppSettings class

If you’re used to using ConfigurationManager.AppSettings["MySetting"] in your controllers then you’re out of luck, instead you need to setup a class to hold your settings. As you can see above I like to add an “AppSettings” section to the config that maps directly to an AppSettings POCO. You can even nest complex classes as deep as you like:

public class AppSettings

{

    public BaseUrls BaseUrls { get; set; }

    public bool AnalyticsEnabled { get; set; }

}

public class BaseUrls

{

    public string Api { get; set; }

    public string Auth { get; set; }

    public string Web { get; set; }

}   

Configure Startup.cs

Now that we have a class to hold our settings, lets map the data from our appsettings.json. You can do it in a couple of ways.

Automatically bind all app settings:

public IServiceProvider ConfigureServices(IServiceCollection services)

{            

    services.Configure<AppSettings>(Configuration.GetSection("AppSettings"));

}

or if you need to alter or transform anything you can assign each property manually:

public IServiceProvider ConfigureServices(IServiceCollection services)

{            

    services.Configure<AppSettings>(appSettings =>

    {

        appSettings.BaseUrls = new BaseUrls()

        {

            // Untyped Syntax - Configuration[""]

            Api = Configuration["AppSettings:BaseUrls:Api"],

            Auth = Configuration["AppSettings:BaseUrls:Auth"],

            Web = Configuration["AppSettings:BaseUrls:Web"],

        };

        // Typed syntax - Configuration.Get<type>("")

        appSettings.AnalyticsEnabled = Configuration.Get<bool>("AppSettings:AnalyticsEnabled");

    });

}

Using the settings

Finally we can access our settings from within our controllers. We’ll be using dependency injection, so if you’re unfamiliar with that, get ready to learn!

public class HomeController : Controller

{

    private readonly AppSettings _appSettings;

    public HomeController(IOptions<AppSettings> appSettings)

    {

        _appSettings = appSettings.Value;

    }

    public IActionResult Index()

    {

        var webUrl = _appSettings.BaseUrls.Web;

        return View();

    }

}

There are a few important things to note here:

The class we are injecting is of type IOptions<AppSettings>. If you try to inject AppSettings directly it won’t work.

Instead of using the IOptions class throughout the code, instead I set the private variable to just AppSettings and assign it in the constructor using the .Valueproperty of the IOptions class.

By the way, the IOptions class is essentially a singleton. The instance we create during startup is the same throughout the lifetime of the application.

While this is a lot more setup than the old way of doing things, I think it forces developers to code in a cleaner and more modular way.