WebApi Exception: Multiple Action were found that match the request.

Usually webapi controller contains GET,GET(id),Post,Put,Patch & Delete methods but sometimes we need to create multiple get or post method or more custom methods to support http verbs.

Let say we have existing Get() method and now we want to add one more custom method names as “GetALL()” to support http Get verb.My Api Controller code looks like:

c2

When you defined your new method with http Get verb along with existing Get() method  and run webapi than below error comes:

C1

WebApiConfig.cs for above code which is created by default when new api project created.

C3

So talk about why this error comes if every thing is perfect in code.So look at the defined route in config file and .In webapi routing only controller name is mentioned in route template and there is no action like (Get,Post or any Custom Action Name) are defined.

Here is the difference in mvc routing and Webapi routing. In mvc routing action name are by default included in Url’s while in webapi actions names are not mandatory.

MVC Route: url: “{controller}/{action}/{id}”

WebApi Route: routeTemplate: “api/{controller}/{id}”

So when ever any request comes to webapi,it always goes to default http verbs and if default GET or Post methods used then it returns a response to the client.

But when we have defined some custom methods along with default Api methods than same request will thrown an exception because now there are multiple action methods that supports http verbs  and server not able to identify which method have to execute.

Why this happened because we have not defined any specific action name in webapi Route.

So what is the solution of this problem as we need many custom action names along with default http verbs in our webapi solution to solve the day-to-day business needs.So question comes in mind whether custom method names are allowed in webapi or not.

Then answer is “yes”,off-course we can add custom action names as much as we want but some changes have to make in webapi routing to support custom action names.

To support custom action method names we have to add {action} with controller name in default route as per below:

routeTemplate: “api/{controller}/{action}/{id}”

Now Complete Webapiconfig.cs after make some changes:

c4

Now Test our methods with these changes.

.1.when request goes to default methods:

C5

2.When request goes to custom action method (GetAll)

C6

 

 

 

 

 

 

Advertisements

WebApi Field Level Response Without Implementing Odata.

Download Complete Project: WebApiFieldLevelSelection

When you are writing a RESTful web API you often want to allow clients to feed a list of fields to the API that the clients need. The reason is to return only the useful data to the client. Say for example, you have an entity called Product that has many properties. The client may need only a few properties of the Product object. If you return the entire object every time the client asks for a product.

it unnecessarily wastes bandwidth and increases the response time. So to avoid that you can accept a list of fields the client wants and return only those. How can you do that?

Odata is best way to achieve this where you can use $Select command to fetch specific database fields in response.

Problem comes when webapi not implementing odata then how can achieve this functionality ?

To achieve this you have to use some basic .net objects like dynamic,expendoObject or  generic collections etc.

Let’s resolve the problem step by step:

  1. Create empty Webapi Project with controller name as “ProductCategory” with Two Get method.one is parameter less and other with string parameter that will accept comma separated field list in request.
  2. Get() method will return all fields of database in response while Get(string fields) method accept list of fields and return desired fields in response.
  3. In below example i have use hardcoded list with dummy values.You may replace it with actual database.

    ProductCategroyController.cs

w1.png

 DynamicObject Method:

DynamicObject accept the list of fields  and return object.here I have use .net reflection to get the value of each fields and respective value to dictionary<string,object> object. later this dictionary object pass to linq query.

w2.PNG

ApiHelper.cs

w3.PNG

OUTPUT:

  1.  When user pass two fields (productid and productName) as query string in request.you can see only two fields are coming in json response.

w4

  1.  When User pass three fields (productId,ProductName,Price) as query string in request.You can see now three fields are coming with json response.

w5.PNG

So you can see how you can implement field level selection on webapi without Odata implementation.

MVC View With Multiple Models Using Tuples And Expendo Object.

DownLoad Sample Project: MvcViewWithMultipleModels 

In mvc programming a view can be associate with multiple models and developers have to send multiple models from controller to cshtml page.There are many  common approaches like ViewBag,ViewData,ViewModel etc can be use to achieve this functionality.

But here i am not going to demonstrate all above three approches.you can use .net framework 4.0 new features to pass the multiple models from controller to view effeciently.

  1. Expendo Object
  2. Tuples

1.) Using Dynamic Model (Expendo Object):

The ExpandoObject class enables you to add and delete members of its instances at run time and also to set and get values of these members. This class supports dynamic binding, which enables you to use standard syntax like sampleObject.sampleMember instead of more complex syntax like sampleObject.GetAttribute(“sampleMember”).

Let say you have two models named as “Department” and “Employee” and you want bind these two models with mvc view.Below are the model definitions:

Department.cs:

c1.PNG

Employee.cs

c2

Controller.cs :

c4

Method details fetch employee and department details as per below:

c5.PNG

View (Index.Html):

On the view you have to use dynamic property of .net framework 4.0 and need to be declared as @model dynamic.this is not strongly type of view.

c6

Rendered output:

c3.PNG

2.) Using Tuples:

In C#, tuples are used to store data. It’s sort of like a variable, but in order for it to be a tuple it must contain more than one value. Each instance of a tuple has a fixed number of items within it (and each item has its own distinct type, eg. a variable or an int), and once the tuple has been created, it can’t be altered in any way.

Let’s continue with previous create models and there are no changes in model class.the only change need to do in controller and view.

Now Controller looks like after code updating for tuples:

HomeController.cs

t4

View (index.html)

you have use tuples at top of the view and you have to declare as per below:

@model Tuples<List<Employee>,List<Department>

complete view looks like:

t2.PNG

Rendered Output:

t1.PNG

You can see there is no change in final rendered output.

This article describes how to pass multiple models from controller to view .I hope this will be helpful for programmers.

“Drop If Exists” Syntax in Sql-Server 2016

With the recent Sql server 2016 Service Pack 1, one important feature “Drop If Exists” syntax has been added, which developers were missing from long time.Prior to Sqlserver 2016 developers need to check database objects existence before creating.

Prior to sql server 2016,If Exist clause was used to check the db object existence then developers were taking appropriate action if objects exists.

If the Database objects doesn’t exists it will not raise any error, it will continue executing the next statement in the batch.

If Exist In Older Versions:

Previously you need to add an IF EXISTS() condition to check if the database object already exists or not. If exists then drop and then create a new objects, like:

IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'DatabaseName')
  Do your thing...

Example:
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'Sql2016DB')
drop database Sql2016DB

New Way :

You can see new syntax much easier and shorter as compare to older syntax.Developer’s not need to memories whole long syntax to check and drop database objects.

Query Syntax: DROP <DbObject> [ IF EXISTS ] <ObjectName>

Here DbOject can be any database,store procedure,functions,Tables or triggers.


Let See  real-time examples for few important database objects:

1)Drop Database if Exists:

Now query syntax to drop the database is very easy and shorter.

Syntax:  Drop Database If Exists  <DatbaseName>

Example: let say our database  name is SqlDb2016 than below query will drop database by using new query syntax.

Drop database if exists SqlDb2016

d1


2. Drop Tables If Exists:

Syntax: Drop Database If Exists <TableName>

Examplelet “EmployeeMaster” is a table in Sqldb2016 database than below query will drop table if exists in database.

Old Syntax :

d3.PNG

New query Syntax in Sql Server 2016

d2.PNG


3.Drop Procedure If Exist:

Drop procedure query is very often to use during the complex procedure writing and developers needs to apply some logic to check whether procedure is exists in database or not.

New “Drop If Exists” query syntax make developers life easy and new syntax is more short and memorable.

Old Query Syntax:

d4.PNG

 

New Query Syntax:

d5.PNG


4.) Drop Function If Exists:

User-defined functions are routines that accept parameters, perform an action and return the result of that action as a value. The return value can either be a single scalar value or a result set (table).

Old Query Syntax:

d6.PNG

New Query Syntax:

d7.PNG


5.) Drop Views If Exists:

A view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Old Query Syntax:

d8

New Query Syntax:

d9.PNG


6.)Drop Trigger If Exists:

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

Old Query Syntax:

d10.PNG

New Query Syntax:

d11.PNG


Drop if Exists statement is not limited to above mentioned database objects it can be use with other database objects like :

  • Index
  • constraints
  • Columns
  • Schema
  • Synonym
  • Type
  • Users
  • Role

 

SqlServer 2016 Virtual Machine Setup In Microsoft Azure.

Azure VMs provide a way to move your on-premises SQL Server workloads and applications to the Cloud.

There are many reasons that you might choose to host your data in Azure. If your application is moving to Azure, it improves performance to also move the data. But there are other benefits. You automatically have access to multiple data centers for a global presence and disaster recovery. The data is also highly secured and durable.

Find step-by-step guidance for this process in the tutorial, Provision a SQL Server virtual machine in the Azure portal.

1.Login on azure portal by using your  Microsoft azure account. if you not have any azure account then you may use Microsoft azure trial.

 

A1.PNG

2. look for virtual machines option from the left blade and and click on it.Now click “Add” option and select sqlserver virtual machine from all available images.A2.PNG

Now All available images will come in new pane and you have select sqlserver 2016 image.

A3.png

3. Under Select a deployment model, verify that Resource Manager is selected. Resource Manager is the recommended deployment model for new virtual machines. Click Create.

A4.PNG

Configure Virtual Machine

There are five important steps to configure the virtual machine as per below:

A5.PNG

1.Configure Basic Settings:

Provide following information on basic configure tab:

  • Provide Unique Virtual Machine Name.
  • User name for the local administrator account on the VM.
  • Provide a strong Password.
  • Select Subscription what ever you have.
  • In the Resource group box, type a name for a new resource group. Alternatively, to use an existing resource group click Select existing.To know more about resource group.https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-overview
  • Select a Location for this deployment.
  • Click OK to save the settings.

a6

2. Select Virtual Image Size:

There are lot of virtual images size available on azure portal. Initially most recommended images comes on screen and you can  select any other image size which is most appropriate for you.

A7.PNG

3.Configure optional features:

On the Settings blade, configure Azure storage, networking, and monitoring for the virtual machine.A8.PNG

4.Configure sql server settings:

Sql server configurations is important section of virtual machine setup,here you have to configure various type of settings like Authentication,Network connectivity.

  • Public (internet) to allow connections to SQL Server from machines or services on the internet. With this option selected, Azure automatically configures the firewall and the network security group to allow traffic on port 1433.
  • Local (inside VM only) to allow connections to SQL Server only from within the VM.
  • Private (within Virtual Network) to allow connections to SQL Server from machines or services in the same virtual network.
  • If you require SQL Server Authentication, click Enable under SQL authentication.Under Sql authentication you have to specify username and password.

Here i am going with public network with Sql authentication.

a9

5. Summary:

this is last step of virtual machine configuration and here you only have to review all the steps activity from step 1 to step 4.If all the things are good then click ok. Now your Sql Server machine setup is complete and ready to use.

A10.PNG

Click again on virtual machine option in left blade of screen and now you can see your newly created sql server machine is listed.

A11.PNG

Connect virtual machine with Remote Desktop

Use below steps to connect VM with remote desktop:

  • Click on your new SQL virtual machine. A Virtual machine blade displays your virtual machine details.
  • At the top of the Virtual machine blade, click Connect.
  • The browser downloads an RDP file for the VM. Open the RDP file.

A12.PNG

Once you have opened .RDP file,you have to enter your username and password. These are the same username and password which was specified in Sql authentication.

a13

Once you have entered correct username and password then you will navigate to your virtual machine.

a20

After you connect to the SQL Server virtual machine, you can launch SQL Server Management Studio and connect with Windows Authentication using your local administrator credentials. If you enabled SQL Server Authentication, you can also connect with SQL Authentication using the SQL login and password you configured during provisioning.

 

A16.PNG

 

 

What is TypeScript and why would we use it in place of JavaScript?

1-grk7bten0ojeqrkgg2qs2a

It’s never been a better time to be a JavaScript developer! JavaScript is everywhere now – it’s on the front and back-end of a website, and many desktop text editors are powered by JavaScript. In fact you can build a whole range of desktop applications using web technologies as their foundation.

TypeScript is a Superset of JavaScript.

TypeScript: Making .NET Developers Comfortable with JavaScript

“.TS” is TypeScript File Extension as similar to java script file extension “.Js”.

TypeScript is a free and open-source programming language developed and maintained by Microsoft. It is a strict superset of JavaScript, and adds optional static typing and class-based object-oriented programming to the language.TypeScript may be used to develop JavaScript applications for client-side or server-side (Node.js) execution.

TypeScript is designed for development of large applications and transcompiles to JavaScript.[7] As TypeScript is a superset of JavaScript, any existing JavaScript programs are also valid TypeScript programs.

TypeScript adds common concepts such as classes, modules, interfaces, generics and (optional) static typing to JavaScript. All JavaScript code is valid TypeScript code so it can be added seamlessly to any project. The TypeScript compiler emits JavaScript.

Installing TypeScript

There are two main ways to get the TypeScript tools:

  • Via npm (the Node.js package manager)
  • By installing TypeScript’s Visual Studio plugins

Visual Studio 2015 and Visual Studio 2013 Update 2 include TypeScript by default.

For NPM users:

Open Command prompt and execute below command.It will take some time to download and install all required assemblies.

npm install -g typescript

t1

t2

Now Validate the setup process by executing Tsc command on same command prompt.

t3

Supportive tools for TypeScript:

T4.JPG

Benefits of typescript:

  • Due to the static typing, code written in TypeScript is more predictable, and is generally easier to debug.
  • Makes it easier to organize the code base for very large and complicated apps thanks to modules, namespace and strong OOP support.
  • TypeScript has a compilation step to JavaScript that catches all kinds of errors before they reach runtime and break something.
  • The upcoming Angular 2 framework is written in TypeScript and it’s recommended that developers use the language in their projects as well.

For more details please refer https://www.typescriptlang.org/docs/tutorial.html

TypeScript PlayGround:

Type Script Playground is online tool for developing and testing typescript programs without installing any external tools.It automatically translate type script to java script.

https://www.typescriptlang.org/play/index.html

t5

 

Some important features of TypeScript:

  1. TypeScript Code is Converted into Plain JavaScript Code
  2. Data Types
  3. Classes
  4. Access Modifiers and Properties
  5. Static and Instance Members
  6. Function Overloading
  7. Constructors
  8. Inheritance
  9. Interfaces
  10. Modules
  11. Type Annotation
  12. Generics
  13. Type inference
  14. Tuple
  15. Await

Since TypeScript generates plain JavaScript code you can use it with any browser. Overall TypeScript is a promising language  that can certainly help you neatly write and organize your JavaScript code base making it more maintainable and extensible.

Different Behavior of @@ROWCOUNT with Set,Print and Set NoCount On.

Few days back someone ask to me why a sql server global variable  @@RowCount produce different output for different-different set of queries in  store procedure.

Below is a simplified version of the query, can you tell why @@Rowcount will be 0?

  1. When Print Statements used before accessing the @@RowCount Variable.r1

Think what will be the output produce from print and select statements which are used at line number  12.

images

Are you thinking about 3 as there are 3 entries in a table.if you are thinking in this direction then it is completely wrong.

Answer:if you run complete query then value of variable @showcount will be 0.

Why:because print resets the value of @@ROWCOUNT to 0 and the same  value assigned to @showcount parameter.So that @Showcount returns 0 .

so it is recommended that  print statements should not be used before @@RowCount variable.if you are doing so then you always get surprise output which will not be as per expectation.

So best practices says,do not use any  other query  before @@RowCount variable and use local variables to hold the value of @@RowCount.Do not include  global variables directly in your business logic.

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Generic Text’.

Correct way to use @@RowCount:

If you want to store the rows that were affected by A DML statement then you need to grab @@ROWCOUNT immediately after the DML statement. There can’t be any code between the DML statement and your code that stores @@ROWCOUNT into a variable.

Here i am using same query as i mentioned top of the article with little bit changes to make query more feasible.

R2.JPGNow you can see that output is 3  and it is as per expectations. Because i have moved print statement just after the reading the @@RowCount variable.

There are more intersting facts about @@RowCount variables:

  1. IF statement also resets the @@ROWCOUNT and you get back 0.Try below query:
DECLARE @ShowCount INT
 
SELECT 1
UNION all
SELECT 2
 
if 1=1
SELECT @ShowCount = @@ROWCOUNT
 
SELECT @ShowCount

2. Set Statements also reset the @@ROWCOUNT and you get back 1.

Script:

Select 1 as output
union All
select 2 as output

Declare @Flag bit
Set @Flag=1

select @@ROWCOUNT as TotalRecords

OutPut:

r3

Again Surprise output,as per the select statement @@Rowcount should retuns 2 becuase UNION ALL used with both select statements but @@RowCount returns 1.

this happens due to Set statement is used just before accessing the value of @@RowCount.So set statement reset the counter to 1 so that different output produce by the sql engine.

 

There are many more statements for which @@ RowCount differently as per the expectation.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0

 

What is UDL(Universal Data Link)

One of the most common problem in any data driven application working is the database connection string. How many times in your development life have you heard “I can’t connect to the database” and the problem ends up a minor detail in the connection string?

“How to verify sql server connection without SSMS?” or “How to find installed sql server instance names on a machine without SSMS or any tools?”

The Microsoft Data Access Components (MDAC) provide the Data Link Properties dialog box as the common user interface for specifying connection information to a data provider on Windows 2000 and later operating systems. You can use the Data Link Properties dialog box to save connection information in a universal data link (.udl) file.

To create a Universal Data Link (.udl) file

  • RUN → Notepad
  • Once the notepad is opened, File → Save As… (Do not type anything in the notepad)
  • Choose the path → Save as type: All Files (*.*) → File name: Rakesh.udl (File name can be anything but the extension must be .udl)

U1

  • Double click on the udl file.

U2

  • Select provider name for whatever you want test database connection.here  “Microsoft OleDb Provider for SqlServer” is used for testing.
  • Now go to the Connection tab and select the database to connect to. You may just browse through the list but SQLExpress instances do not always show up. You may also type the server name into the list box. select appropriate authentication mode(windows or sqlserver).
  • Now you will see all databases in drop down and select any one database.
  • Assuming all’s well, select the database and click the Test Connection button.

U5.JPG

  • Now open the file in notepad – remember, the .udl file is simply a text file. You should see something like this:

U6.JPG

 

In the file is the connection string you just tested. Copy and paste were needed in your application and you should be good to go.

MVC5 Dependency Injection With Microsoft Unity.

Download Source Code: DIWithUnityMVC

Dependency injection separates the creation of a client’s dependencies from the client’s behavior, which allows program designs to be loosely coupled and to follow the dependency inversion and single responsibility principles.

Dependency injection allows a program design to follow the dependency inversion principle. The client delegates to external code (the injector) the responsibility of providing its dependencies. The client is not allowed to call the injector code.It is the injecting code that constructs the services and calls the client to inject them. This means the client code does not need to know about the injecting code.

The client does not need to know how to construct the services. The client does not need to know which actual services it is using. The client only needs to know about the intrinsic interfaces of the services because these define how the client may use the services. This separates the responsibilities of use and construction.

What is Unity: Unity is a lightweight, extensible dependency injection container that supports interception, constructor injection, property injection, and method call injection.Unity is a general-purpose container for use in any type of Microsoft .NET Framework-based application. It provides all of the features commonly found in dependency injection mechanisms, including methods to register type mappings and object instances, resolve objects, manage object lifetimes, and inject dependent objects into the parameters of constructors and methods and as the value of properties of objects it resolves.

https://msdn.microsoft.com/en-us/library/dn507457(v=pandp.30).aspx

I’m going to create sample web application for Employee details listing website. There I have Employee as main entities.

Let’s start to build mvc application step by step with snap shots.

1. Create Sample Web Application

let’s start by creating sample MVC web application using Visual Studio 2013.

d1d1-1

2. Install Unity.Mvc5 Library

To install Unity.Mvc5 Library using nuget, we have to right-click on project and click on manage nuget packages.

d2d2.1d2.2.jpg

After successfully completion of nuget packages,check the solution explorer and you can see newly added UnityConfig class inside App_Start folder. That’s where we register all our components with the container.

d3D3.1

3. Update Sample Application

first I’ll add two another Class Library projects called Entities and Services. There Entities project holds all the entity (model) classes we are going to use and Services project will be used to put our service classes.After adding two new projects your solution will look like this.

D4.JPG

Now i added all required model classes(entities) in entities project.

Employee Class (Employee.cs)

namespace Entities
{
   public class Employee
    {
       public int EmployeeId { get; set; }
       public string EmployeeName { get; set; }
       public string HomeTown {get;set;}
       public int DepartmentId { get; set; }
    }
}

 

Next I will add reference to Entities project from Services project and create service classes for Employee . And also I will add relevant interfaces for those classes.

D5.jpg

IEmployeeService Interface:

using Entities;
 
namespace Services
{
   public interface IEmployeeService
    {
      IList GetEmployeeBy();   
    }

EmployeeService Class (EmployeeService.cs):

using Entities;
 
namespace Services
{
   public class EmployeeService:IEmployeeService
    {
        public IList GetEmployeeBy()
        {
            IList empList = new List();
            empList.Add(new Employee() {EmployeeId=1001,EmployeeName="Rakesh Mahur",DepartmentId=111,HomeTown="Meerut" });
            empList.Add(new Employee() { EmployeeId = 1001, EmployeeName = "Siddhart Tandon", DepartmentId = 111, HomeTown = "Lucknow" });
            empList.Add(new Employee() { EmployeeId = 1002, EmployeeName = "Sumit Gupta", DepartmentId = 112, HomeTown = "Haldwani" });
            empList.Add(new Employee() { EmployeeId = 1003, EmployeeName = "Avay Azad", DepartmentId = 113, HomeTown = "Patna" });
            empList.Add(new Employee() { EmployeeId = 1004, EmployeeName = "Sachin Mittal", DepartmentId = 114, HomeTown = "Delhi" });
 
            return empList;
        }
    }
}

 4. Configure Unity:

Let’s register our components with the unity container. There first we have to update our UnityConfig class as below.

using Unity.Mvc5;
using Services;
namespace DIWithUnityMVC
{
    public static class UnityConfig
    {
        public static void RegisterComponents()
        {
			var container = new UnityContainer();
            
            // register all your components with the container here
            // it is NOT necessary to register your controllers
            
            // e.g. container.RegisterType<itestservice, testservice>();
 
            container.RegisterType<IEmployeeService, EmployeeService>();
            
            DependencyResolver.SetResolver(new UnityDependencyResolver(container));
        }
    }
}

This RegisterComponents method can be called within the Application_Start method in Global.asax. We can update our Global.asax as below.

namespace DIWithUnityMVC
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            UnityConfig.RegisterComponents();
 
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}

5. Continue with Sample Application:

Now i am  going to display these employee  information in our web application. I’ll add new Empty Controller called Employee to our MVC application by right clicking Controllers folder.

D5.1.JPG

Normally if we want to use our EmployeeService within the EmployeeController, we need to create instance of EmployeeService inside EmployeeController.

Dependency injection is basically providing the objects that an object needs (here the EmployeeService ) instead of having it construct them itself. I’m going to use constructor injection in this application and let the unity to create new instance of EmployeeService for EmployeeController. There we need to add constructor to our EmployeeControllerwith parameter as below.

using Services;
 
 
namespace DIWithUnityMVC.Controllers
{
    public class EmployeeController : Controller
    {
 
        private readonly IEmployeeService employeeService;
 
        //Constructor Injection to inject the dependency at runtime
        public EmployeeController(IEmployeeService _employeeService)
        {
            employeeService = _employeeService;
        }
 
        //Get : EmployeeDetails
        public ActionResult Index()
        {
            return View(employeeService.GetEmployeeDetails());
        }
	}
}

You can see that i can not create any object of EmployeeService inside the controller.So how employee object available in controller. here DI (Dependency Injection) play vital role and i have use Constructor injection to instantiate the employee class.

Next I will add the view to show the employee details as per below:

D5.2.JPG

Enter a caption

After adding view successfully,your cshtml file will look likes below:

Snippet

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeName)
        
        
            @Html.DisplayNameFor(model => model.HomeTown)
        
        
            @Html.DisplayNameFor(model => model.DepartmentId)
        
        
    
 
@foreach (var item in Model) {
    
        
            @Html.DisplayFor(modelItem => item.EmployeeName)
        
        
            @Html.DisplayFor(modelItem => item.HomeTown)
        
        
            @Html.DisplayFor(modelItem => item.DepartmentId)
        
        
            @Html.ActionLink("Edit", "Edit", new { id=item.EmployeeId }) |
            @Html.ActionLink("Details", "Details", new { id=item.EmployeeId }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.EmployeeId })
        
    
}
 

It’s done, you can run your application and try /Institution/Index. You should get following output.

D5.3

6. Move Unity Configuration to Separate File

We can move unity configuration to separate file which enable us to do modifications without recompiling existing application. We can do this by adding config section to web.config file as below.

Updated Unity.Config:

 public static class UnityConfig
    {
        public static void RegisterComponents()
        {
			var container = new UnityContainer();
            
            // register all your components with the container here
            // it is NOT necessary to register your controllers
            
            // e.g. container.RegisterType<ITestService, TestService>();
 
            //container.RegisterType<IEmployeeService, EmployeeService>();
 
            container.LoadConfiguration();
            
            DependencyResolver.SetResolver(new UnityDependencyResolver(container));
        }
    }

Modified Web.Config:

 <configSections>
    <section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <unity configSource="unity.config" />
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-DIWithUnityMVC-20160724093832.mdf;Initial Catalog=aspnet-DIWithUnityMVC-20160724093832;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

New Unity.Config:

Snippet
<?xml version="1.0" encoding="utf-8" ?>
<unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
  <container>
    <register type="Services.IEmployeeService, Services" mapTo="Services.EmployeeService, Services" />
  </container>
</unity>

SSRS -Multi-level drill down report?

You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drill-down action.

You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix.

In order to define a drill down action, a tablix must first be created with the appropriate level of aggregation all the way to the level of drill down detail desired. This detail includes both the row and columns desired for the lowest level viewing needed. The drill down functionality works either at the row level, the column level, or the group (column or row) level.

Let’s start step by step:

  1. Create a table “DimEmployee” with following employee details.
EmployeeKey ParentEmployeeKey SalesTerritoryKey EmployeeName
1 NULL 101 Rakesh
2 NULL 102 Amit
3 2 103 Sunil
4 2 104 Avay
5 3 105 Sachin
6 3 106 Gaurav
7 5 107 Rupesh

2. Create another table “EmpSalesAmount” having sales details for each employee.

EmployeeKey Amount
1 5000
2 5225
3 4879
4 6589
5 412
6 5987

3. Create a query to read the details from database.

 

;WITH cte AS (

SELECT

EmployeeKey ,  EmployeeName , ParentEmployeeKey , 1 EmployeeLevel

FROM dbo.DimEmployee WHERE ParentEmployeeKey IS NULL

UNION ALL

SELECT

c.EmployeeKey ,c. EmployeeName , c.ParentEmployeeKey , p.EmployeeLevel + 1

FROM dbo.DimEmployee c

JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey

)

SELECT cte.*, ISNULL(f.Amount, 0) SalesAmount

FROM cte

LEFT JOIN dbo.Empsalesamount f

ON f.EmployeeKey = cte.EmployeeKey  ORDER BY EmployeeLevel, EmployeeName

4. Create Table on Report and drag and drop EmpName field from the dataset :

Capture1

5. Right click on the row and select row group properties :

Capture2.JPG

6. Configure the row group properties as per below:

Capture3.JPG

Capture4Capture5

8. Set Left Indent properties:

Capture6.JPG

9.  So we have done with our drill-down report. To see the preview, click on preview tab:

Capture7Capture8