Saturday, 7 July 2018

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.



Filter Overrides in ASP.Net MVC 5

Filters
 Five types of filters available with MVC:
  • Authentication filters
  • Authorization filters
  • Action filters
  • Result filters
  • Exception filters
So we have five type filter overrides corresponding to this:
  • OverrideAuthenticationAttribute
  • OverrideAuthorizationAttribute
  • OverrideActionFiltersAttribute
  • OverrideResultAttribute
  • OverrideExceptionAttribute
We can mark any action method with an override filter attribute that essentially clears all filters in an upper scope (in other words controller level or global level).

Example
In the following example, the Authorize Filter is applied at the controller level. So, all the action methods of the Home controller can be accessed by the Admin user only. Now I want to exclude or bypass the Authorize Filter from the "About" method.
[Authorize(Users="Admin")]
public class HomeController : Controller{
    public ActionResult Index()
    {
        ViewBag.Message = "Welcome to ASP.NET MVC!";
        return View();
    } 
    public ActionResult About()
    {
        return View();
    }
}
So in this case we can mark this "About" method with the “OverrideAuthorization” attribute. Now all the action methods of the home controller can be accessed by the Admin user except the "About" method. We can access the "About" action method without any authorization.
[Authorize(Users="Admin")]
public class HomeController : Controller{
    public ActionResult Index()
    {
        ViewBag.Message = "Welcome to ASP.NET MVC!";
        return View();
    }
    [OverrideAuthorization]   

 public ActionResult About()
    {
        return View();
    }
}
Note: The OverrideAuthorizationAttribute does not work properly with MVC version 5.0 due to some internal bug. This bug was resolved in MVC version 5.1.

Comparison of MVC with ASP.net


 _ViewStart:
A) The _ViewStart file can be used to define common view code that you want to execute at the start of each View's rendering
B) Viewstart is used like Masterpage in traditional forms (ASP.Net pages).
C) Viewstart render first in the views.
D) Viestart is used to layout of the application.
E) Viewstart override all Views layout/template under "Views" folder in MVC .

For example, we could write code within our _ViewStart.cshtml file to programmatically set the Layout property for each View to be the SiteLayout.cshtml file by default:

Proper way to use the file "ViewStart.cshtml":

The only other built-in place is that you can specify it from the controller -- one of the overloads to View() allows you to pass the layout template (the param is called master page).

Use, _VewStart.cshtml in each of the view folders, it doesn't mean that it is not DRY

The _VewStart.cshtml file contains following code.


@{
  Layout="~/Views/Shared/_Layout.cshtml";
}

General

• If you return PartialView() from your controllers (instead of return View()), then _viewstart.cshtml will not be executed.

• Assign a session in MVC:

Current.Session["UserName"] ="Guest";


• RedirectToActionPermanent() Method for which Status code represents? 300
• RedirectToAction() Method for which Status code represents? Ans. 301
• What is ActionResult() ? Ans: It is an abstract Class
• What is ViewResult() ? Ans:  It is a Concrete Class
• return View() works like in ASP.Net MVC C# as "Server.Transfer()".
• RedirectToAction() works like in ASP.Net MVC C# as "Response.Redirect()".
• In which format data can be return from XML into table ? Ans: Dataset
• Can we use view state in MVC ? Ans.NO
• Which Namespace is used for Razor View Engine ? Ans. System.Web.Razor
• Which Namespace is used for ASPX View Engine ?
Ans. System.Web.Mvc.WebFormViewEngine
• The Razor View Engine uses to render server side content. Ans. @
• The ASPX View Engine uses to render server side content. Ans. <%= %>
• Razor Engine supports for TDD, ASPX View Engine not support.

• If you have already implemented different filters then what will be order of these filters?

1) Authorization filters
2) Action filters
3) Result filters
4) Exception filters

• Can you specify different types of filters in ASP.Net MVC application?

1) Authorization filters (IAuthorizationFilter)
2) Action filters   (IActionFilter)
3) Result filters (IResultFilter)
4) Exception filters (IExceptionFilter)

• What is the significance of ASP.NET routing?

Ansr:

We don't have route config in asp.net.
But we have in MVC 
 Default Route Name:
"{controller}/{action}/{id}", // URL with parameters
By default routing is defined under Global.asax file. MVC ASP.Net uses routing to map between incoming browser request to controller action methods.)

• Can be it possible to share single view across multiple controllers in MVC?

Ans: We can put the view under shared folder, it will automatically view the across the multiple controllers.

•MVC 6 Features:

1.We can merge MVC and Web API merged
2.new JSON project based structure
3.only save change, hitting the save but then refreshing the browser to reflect changes

• Return type of Controller action Method:

ViewResult -
 Renders a specified view to the response stream
PartialViewResult - Renders a specified partial view to the response stream
EmptyResult - An empty response is returned
RedirectResult - Performs an HTTP redirection to a specified URL
RedirectToRouteResult - Performs an HTTP redirection to a URL that is determined by the routing engine, based on given route data
JsonResult - Serializes a given object to JSON format
JavaScriptResult - Returns a piece of JavaScript code that can be executed on the client
ContentResult - Writes content to the response stream without requiring a view
FileContentResult - Returns a file to the client
FileStreamResult - Returns a file to the client, which is provided by a Stream
FilePathResult - Returns a file to the client


Types of SQL Commands

 These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.
The main categories are
  • DDL (Data Definition Language)
  • DML (Data Manipulation Language)
  • DQL (Data Query Language)
  • DCL (Data Control Language)
  • Data administration commands
  • Transactional control commands

Defining Database Structures

Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table.
Some of the most fundamental DDL commands discussed during following hours include the following:
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
These commands are discussed in detail during Hour 3, "Managing Database Objects," Hour 17, "Improving Database Performance," and Hour 20, "Creating and Using Views and Synonyms."

Manipulating Data

Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.
There are three basic DML commands:
INSERT
UPDATE
DELETE

Selecting Data (Data Query Language)

Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is as follows:
SELECT
This command, accompanied by many options and clauses, is used to compose queries against a relational database. Queries, from simple to complex, from vague to specific, can be easily created.
query is an inquiry to the database for information. A query is usually issued to the database through an application interface or via a command line prompt.

Data Control Language

Data control commands in SQL allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:
ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM
You will find that these commands are often grouped with other commands and may appear in a number of different lessons throughout this book.

Data Administration Commands

Data administration commands allow the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows:
START AUDIT
STOP AUDIT
Do not get data administration confused with database administration. Database administration is the overall administration of a database, which envelops the use of all levels of commands. Database administration is much more specific to each SQL implementation than are those core commands of the SQL language.

Transactional Control Commands

In addition to the previously introduced categories of commands, there are commands that allow the user to manage database transactions.
  • COMMIT Saves database transactions
  • ROLLBACK Undoes database transactions
  • SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
  • SET TRANSACTION Places a name on a transaction

Sunday, 8 October 2017

Create Dynamic SQLs via Stored Procedure

If you’re a developer, irrespective of the platform, you would have to work with Databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially with dealing gigantic tables that have hundreds of columns.
Writing SQL statements manually every time becomes a tiresome process. But we have a solution. You could write a Stored Procedure to automatically generate the queries. We have attached the Stored Procedure code for MSSQL Server, though you can replicate to any database with minor changes.
CREATE proc [dbo].[USP_QuerycreationSupport]
(
@table_Name varchar(100)

)
as
begin
DECLARE @InserCols NVARCHAR(MAX)
DECLARE @Inserparam NVARCHAR(MAX)
DECLARE @Insertquery NVARCHAR(MAX)
DECLARE @Selectquery NVARCHAR(MAX)
DECLARE @Update NVARCHAR(MAX)
DECLARE @DeleteQuery NVARCHAR(MAX)

 -- sp param

 SELECT 
    '@'+c.name+ SPACE(1) + case cast(t.Name as nvarchar(40))   when 'nvarchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')' 
                  when 'varchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'char'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'decimal' then t.Name+'(18,2)' else t.Name end +'=null,' as colss
   
   
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@table_Name)




 select 'Insert query'
 SET @InserCols=  ( SELECT DISTINCT (SELECT   sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @InserCols=LEFT(@InserCols,LEN(@InserCols)-1)
 --select @InserCols

 
 SET @Inserparam=  ( SELECT DISTINCT (SELECT   '@'+sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Inserparam=LEFT(@Inserparam,LEN(@Inserparam)-1)
 --select @Inserparam

 set @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'
 select @Insertquery

 select 'Update Query'
 SET @Update=  ( SELECT DISTINCT (SELECT   sc.NAME +'=@'+sc.NAME+',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Update=LEFT(@Update,LEN(@Update)-1)
 --select @Update
 SET @Update='UPdate  '+@table_Name+'  set '+@Update
 select @Update

 -- For select Query
 select 'Select Query'
  
 set @Selectquery='select '+@InserCols +' from '+ @table_Name
 select @Selectquery

 -- For Delete Query
 select 'Delete Query'
  
 set @DeleteQuery='delete from  '+ @table_Name
 select @DeleteQuery
end

 --   exec USP_QuerycreationSupport @table_Name='MST_ComboMain'
 

Step 1: Create the StoredProcedure. The attached file contains the code for creating a Stored Procedure that auto-generates SQL Queries.
Step 2: Execute the StoredProcedure, passing your required table name as a parameter.
execUSP_QuerycreationSupport@table_Name=’mstCustomer’