Change stored procedure output column names and data types in SQL

Using WITH RESULT SETS to redefine column names and data types of stored procedure’s result set

Using WITH RESULT SETS option, we can redefine the metadata of result set(s) of a stored procedure during execution. We can use the below code to change the column names and data types of result sets(s) of stored procedure without making any change in the existing code:

1 2 3 4 5 6 7 8 9 10 11 12 13

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101 WITH RESULT SETS (  (  ReportingLevel INT,  BusinessEntityID INT,  FirstName NVARCHAR(50),  LastName NVARCHAR(50),  OrganizationNode NVARCHAR(MAX),  ManagerName NVARCHAR(50)  ) )

Here is the output of the stored procedure after redefining the column definitions using WITH RESULT SETS option:

ReportingLevel BusinessEntityID FirstName LastName OrganizationNode ManagerName 

Below is the syntax to define column names and data types in case we need to handle multiple result sets:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

EXEC ProcedureName @Param = 'Value' WITH RESULT SETS ( --Result set 1 ( Column1 DataType, Column2 DataType, Column3 DataType --Define all columns ), --Result set 2 ( Column1 DataType, Column2 DataType, Column3 DataType --Define all columns ), --Result set 3 ( Column1 DataType, Column2 DataType, Column3 DataType --Define all columns ) --And so on )

WITH RESULT SETS option – Limitations

1. We cannot redefine a subset of columns – Either all columns of a result set need to be defined or none of them can be defined using WITH RESULT SETS option.
2. Cannot redefine a subset of result sets – In case the stored procedure return multiple result sets, either all result sets (with all columns) need to be defined or none of them can be defined.
3. Cannot change the order of the columns and result sets – We cannot change the sequence of columns and result sets.
4. Cannot use calculation or type casting – We cannot use calculations or type casting with the columns.

Where do you call the config files in MVC?

Generally, We are calling the config files from Application_Start()

using System.Web.Optimization;

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
}

Is it possible to disable authorization on one action in an MVC controller?

You can add [Authorize] To the controller class, and then add [AllowAnonymous] to the single action you don't want to be authorized. Example:

[Authorize]
    public class AccountController : Controller
    {
        public ActionResult Profile()
        {
            return View();
        }

        [AllowAnonymous]
        public ActionResult Login()
        {
            return View();
        }
    }

Difference between WCF and Web API and WCF REST and Web Service

Web Service

1. It is based on SOAP and return data in XML form.
2. It support only HTTP protocol.
3. It is not open source but can be consumed by any client that understands xml.
4. It can be hosted only on IIS.

WCF

1. It is also based on SOAP and return data in XML form.
2. It is the evolution of the web service(ASMX) and support various protocols like TCP, HTTP, HTTPS, Named Pipes, MSMQ.
3. The main issue with WCF is, its tedious and extensive configuration.
4. It is not open source but can be consumed by any client that understands xml.
5. It can be hosted with in the applicaion or on IIS or using window service.

WCF Rest

1. To use WCF as WCF Rest service you have to enable webHttpBindings.
2. It support HTTP GET and POST verbs by [WebGet] and [WebInvoke] attributes respectively.
3. To enable other HTTP verbs you have to do some configuration in IIS to accept request of that particular verb on .svc files
4. Passing data through parameters using a WebGet needs configuration. The UriTemplate must be specified
5. It support XML, JSON and ATOM data format.

Web API

1. This is the new framework for building HTTP services with easy and simple way.
2. Web API is open source an ideal platform for building REST-ful services over the .NET Framework.
3. Unlike WCF Rest service, it use the full featues of HTTP (like URIs, request/response headers, caching, versioning, various content formats)
4. It also supports the MVC features such as routing, controllers, action results, filter, model binders, IOC container or dependency injection, unit testing that makes it more simple and robust.
5. It can be hosted with in the application or on IIS.
6. It is light weight architecture and good for devices which have limited bandwidth like smart phones.
7. Responses are formatted by Web API’s MediaTypeFormatter into JSON, XML or whatever format you want to add as a MediaTypeFormatter.

To whom choose between WCF or WEB API

1. Choose WCF when you want to create a service that should support special scenarios such as one way messaging, message queues, duplex communication etc.
2. Choose WCF when you want to create a service that can use fast transport channels when available, such as TCP, Named Pipes, or maybe even UDP (in WCF 4.5), and you also want to support HTTP when all other transport channels are unavailable.
3. Choose Web API when you want to create a resource-oriented services over HTTP that can use the full features of HTTP (like URIs, request/response headers, caching, versioning, various content formats).
4. Choose Web API when you want to expose your service to a broad range of clients including browsers, mobiles, iphone and tablets.

How to Configure Sessions in WCF?

How to Configure Sessions in WCF?

To configure sessions in WCF, one should know the following three elements:

1. Binding – Because all bindings do not support sessions. Only WS-*, NetTcpBinding andNetNamedPipeBinding have session support so selection of appropriate binding is necessary.
2. SessionMode – This service contract specifies service’s possible expectation for session from incoming client request. It has three self describing values:
   • Allowed – Service can accept sessionful clients as well as sessionless.
   • Required – Service will entertain only those clients who have session, sessionless client cannot connect to service.
   • NotAllowed – Service can interact only with sessionless clients. Just opposite to Required.
3. InstanceContextMode – This is a service behavior that controls instantiation of actual service class. It has the following values:
• PerCall – Each request made to server will be served by a new instance of service class.
• PerSession – A Session will have its dedicated service instance and all requests pertaining to the session will be served by that instance only. All sessions will have their individual dedicated service instances.
• Single –All requests to the service will be served by a single unique instance of service class

4. [ServiceContract(SessionMode = SessionMode.Allowed)]
    public interface Iservice
    {
       //some code…
    }

5. [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession)]
    public class serviceclass : Iservice
    {
       // some code…
    }