Cross Apply in SQL Server

Apply is one of the very useful feature in SQL server and it might be the case that you will realize that you could have used this in one of your requirements, while working on database, if it was not know till now. So let's discuss it. 

Apply keyword provides functionality similar to Join but they can be used in situation where it is not possible to use a Join. This keyword can be used in 2 ways:

• Cross Apply : This is similar to use of Inner Join.
• Outer Apply : This is similar to use of Left Outer Join

So let's start by creating two different tables named Category and Product and add some data. As from the name, Category will be a parent table and Product will be a child table. So our dummy data will look like the following:

Now let's come to the use of Cross Apply. As we discussed earlier, Cross apply can be used like an Inner join. So in order to get the data from both the tables, we can apply the Cross apply as:

Easy enough, quite similar to co-related sub-query, for each row of the Category table (outer query), each and every row of the Product table is evaluated (inner query) and if the result matches, the record is kept for the final result set. 

The point is that if we already have the Joins, than what is the use of apply. The Apply keyword is mainly intended to be used with Table Value Functions, where the outer query record set is from a table valued function. So let's move the outer query to create a table valued function:

Now, in order to use it, replace the inner query with the function call and see the results.

Great to work with. Happy Querying...!!!

GO keyword in SQL

While generating the SQL scripts, you might have often seen the GO keyword. What it is ? Let's see what exactly it is.

At first look, it might seem to be a keyword of T-SQL like any other keyword like SELECT, DISTINCT, ORDER BY etc. However, it is not. Yes, it is not a T-SQL keyword. As per MSDN, this keyword is:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

This means, it is not considered as a T-SQL keyword and is more of a command to SQL Server. This keyword basically act as a command for SQL to execute a group or batch of statements which are written before the GO keyword is encountered. 

An important point to note here is that the GO keyword limits the scope of the variables to the batch of statements on which this command is used. This means if we try to use a declared variable after the GO keyword is executed, it will throw an error i.e. the keyword will not be available after it. See the following sample code:

Here, the (1 row(s) affected) message refers to the first SELECT statement which returns the value. However, the second SELECT returns error as the variable cannot be accessed after the GO command is executed.

Happy Querying...!!!

Generate UML Sequence diagram from Visual Studio

Yes it is correct, you can generate Sequence diagrams for your code, from within the Visual Studio and that too automatically.No need to create it your self. Let's see how we can do this.

Let's create a new sample project. Let's add a static classes Class1 and static method TestMethod1 in this class. Now let's create a test method on Default.aspx and call it on the Page_Load event. This function, in turn, will call TestMethod1 of Class1. So our code will look like the following:

Now, right click on the Page_Load event, and select the option "Generate Sequence Diagram". 

This will open up a window to define the settings to be used for generating this diagram. You can set what level of code it should consider for generating the diagram, whether to include the code within the entire solution or current project only etc.

Also, you can specify whether to save the diagram in current project or not. So we increase the call depth to 2 and keep rest of the settings as it is. 

Click "Ok" and it will generate the sequence diagram for the code we have.  

So the sequence diagram is ready. Just increase the "Maximum Call Depth" property and create the diagram to understand the code in easily. Happy Coding...!!!

MS SQL Database backup using SQL Command

Use the following SQL command in order to generate the back-up of the MS SQL database.


BACKUP DATABASE Database_Name TO DISK 'Location_For_Backup'

For example, generate back-up of database SampleDB, we can use the following database;

BACKUP DATABASE SampleDB TO DISK 'D:\SampleDB.BAK' 

Happy Querying...!!!

IIS Log files

Have you ever wondered about logs of IIS. Yes, they do exist, providing a lot of vital information about the applications that we have hosted in the IIS. For each website we host in IIS, it's logs are created and can be configured for different settings. 

To test it, let's create a sample ASP.Net application and we name it as IISLogging and host it in the IIS. Browse the application to check it is hosted properly.

Now in order to check the logs, click on the website and select the Logging option in the right pane and double click to open it. 

Clicking the option will open the logging settings section. Here we can check what is the location of the IIS logs for our website, which we have hosted. Also it provides different configuration settings for generation of the log files.

Now go to the location as displayed and check out the log files getting generated. Now here you can see different folders getting generated with the name prefix as W3SVC and a number added to it as a suffix. 

How to identify the website IIS log file ?

Now here you may have different folders getting generated. But which one is yours. In order to identify this, go to the website in the IIS and click Advance Settings for the website. This will open it's settings windows. 

Check the ID property. This is the ID of the website in IIS and your log file folder will have this number as the suffix attached to it. So in our case the log file folder is W3SVC2. Check the folder and we can see the log file generated for it.

So we have our log files generated based on the dates here. 

How to analyze IIS log files ?

In order to read these log files in effective way, different tools are available which include log parser lizard and weblog expert. So happy logging now...!!!

Basic Authentication in Web-API at HOST level using Authentication Filters

In our previous discussions, we discussed about the basics about the security in asp.net web api. If you have not read that then i would recommend you to read that first here, as it will act as a base of where and how to apply the security in asp.net web api, as we discussed the basics of security. Also we discussed the concept of how we can Implement Basic Authentication in ASP.Net Web API at Host level (IIS). 

Continuing on same lines, we will implement basic authentication using the concept of Authentication filters. An authentication filter is nothing but creating an attribute and applying it on the class or method.

To start with, we will create an empty ASP.Net application. Next, we add an API controller called SampleController and derive it from ApiController to make it a web-api type controller. Also we add a method named GetList() to return list of string values. So our class will look like the following.

 public class SampleController : ApiController
 {
        public IEnumerable<String> GetList()
        {
          return new String[] { "value1", "value2" };
        }
 }

Let's browse the api method using the path: and see the results. We can easily view the results. No username/password required.

Anybody, who knows the url of your api, can make call to it and use the method and we would like to restrict this, so that only people who are authorized to access the system, should get access to the api method. So, in order to apply security on our method, we need to do the following:

1. Create an authentication filter, which is nothing but a class derived from IAuthenticationFilter interface and Attribute class. It will implement the following IAuthenticate methods. These are:
• AuthenticateAsync
• ChallengeAsync
2. Apply this filter as an attribute on the method or the controller, on which we would like to add the security.
3. Apply the Authorize attribute along with this attribute. 

How the security process will work ? 

When the IAuthenticationFilter is applied, AuthenticateAsync is the method which will receive any request for the web-api methods and check for the credentials(if they are present), in the request header. There are 3 possible scenarios:

Case 1 - No credentials provided: Client request has not sent any credentials. In such a case, authentication filter will do nothing and pass it on to Authorize attribute to handle it. Since there were no credentials, and identity was NOT authenticated, the request is rejected with Code 401 unauthorized result.

Case 2 - Invalid credentials provided: In case the client has provided invalid credentials, the ErrorResult property of the current HttpAuthenticationContext instance is set with UnauthorizedResult. An authentication challenge is added to the Result property of the current HttpAuthenticationContext instance. This is done by the ChallengeAsync method of the IAuthenticationFilter interface.

Case 3 - Valid credentials provided: If the credentials provided in the request header are valid, then a valid ClaimPrincipal will be generated and will be assigned to the Principle property of the current HttpAuthenticationContext instance. Further the Authorize attribute will check if the Identity is established for the request, then allow the access to the method or controller. 

So one of the major role of Authorize attribute is checking whether an identity for the user has been established or not by the authentication filters, which were executed previously in the pipeline. If it is not established, then return 401 Unauthorized error, else request will be allowed to access the method.

So let's start by creating the authentication filter class. We will name it as BasicAuthenticationFilter. We will derive it from Attribute class and IAuthenticationFilter. So our code will look like the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Security.Claims;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.Filters;
using System.Web.Http.Results;
namespace AuthenticationFilter
{
    public class BasicAuthenticationFilter : Attribute, IAuthenticationFilter
    {
        private readonly String realm;
        public Boolean AllowMultiple { get { return false; } }
        public BasicAuthenticationFilter(string realm)
        {
             this.realm = "realm=" + realm;
        }
        public Task AuthenticateAsync(HttpAuthenticationContext context, CancellationToken cancellationToken)
        {
             var currentRequest = context.Request;
             if (currentRequest.Headers.Authorization != null && currentRequest.Headers.Authorization.Scheme == "Basic")
            {
                 Encoding encoding = Encoding.GetEncoding("iso-8859-1");
                 var headerData = encoding.GetString(Convert.FromBase64String(currentRequest.Headers.Authorization.Parameter));
                 var credentials = headerData.Split(':');
                 var userId = credentials[0].Trim();
                 var pwd = credentials[1].Trim();
                 if (userId == "jasminder" && pwd == "jasminder")
                 {
                    var claims = new List<Claim>()
                    {
                                new Claim(ClaimTypes.Name, "auth")
                    };
                    var id = new ClaimsIdentity(claims, "Basic");
                    var principal = new ClaimsPrincipal(new[] { id });
                    context.Principal = principal;
                 }
                else
                {
                    context.ErrorResult = new UnauthorizedResult(new AuthenticationHeaderValue[0], context.Request);
                }
            }
              return Task.FromResult(0);
     }
    public async Task ChallengeAsync(HttpAuthenticationChallengeContext context, CancellationToken cancellationToken)
        {
            HttpResponseMessage result = await context.Result.ExecuteAsync(cancellationToken);
            if (result.StatusCode == HttpStatusCode.Unauthorized)
            {
                result.Headers.WwwAuthenticate.Add(new AuthenticationHeaderValue("Basic", "realm=" + this.realm));
            }
            context.Result = new ResponseMessageResult(result);
        }
    } 
}

Attribute class will make it possible to apply it as an attribute on the method or controller. AuthenticateAsync will be used for authenticating the request credentials, if present, and ChallengeAsync will be used to prepare the final HttpResponse, based on the result of ExecuteAsync method of the current HttpAuthenticationChallengeContexts' context.

Now simply apply this filter and the authorize attribute on our method, like any other attribute:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace AuthenticationFilter
{
    public class SampleController : ApiController
    {
        [BasicAuthenticationFilter("testRealm")]
        [Authorize]
        public IEnumerable<String> GetList()
        {
            return new String[] { "value1", "value2" };
        }
    }
}

Now let's test the 3 cases that we discussed above.

Case 1:  Run the application and you will be prompted for a username/password.

Click Cancel and see that you get the message Authorization has been denied for this request.

This was because, when the authentication filter received the request, no credentials were provided. So it passed on the request to the Authorize attribute. Due to absence of an authenticated principle, it rejected the request and this message was received.

Case 2:  Run the api again and this time enter invalid credentials. This time, the authenticate method will check for the credentials. Since the credentials were not valid, it will set the ErrorResult property of the current HttpAuthenticationContext instance.

Case 3:  Since the credentials were invalid, it will again ask for the credentials. This time, we will provide the valid credentials and see the code get's executed:

This time the credentials were valid. So an Identity is established for the current request (as above) and method result is returned.

Now a point that I have mentioned above, about the ExecuteAsync is very important. This means that when ever the ChallengeAsync method get's executed, it will get the HttpResponse generated for the request, from the ExecuteAsync method. So if the ExecuteAsync method says that the request is UnAuthorized request, we will check the response and set the basic authentication challenge in the response. Read a very important point here, from the official asp.net web-api. It says:

It's important to understand that ChallengeAsync is called before the HTTP response is created, and possibly even before the controller action runs. When ChallengeAsync is called, context.Result contains an IHttpActionResult, which is used later to create the HTTP response. So when ChallengeAsync is called, you don't know anything about the HTTP response yet. The ChallengeAsync method should replace the original value of context.Result with a new IHttpActionResult. This IHttpActionResult must wrap the original context.Result.

The best way to test this point is to remove the Authorize attribute and add a debugger to the ChallengeAsync and the api method we need to access. When we run the request, we can see that the ExecuteAsync will be able to execute the api method (in our case it is GetList) and HttpStatusCode as OK will be returned. This means the HttpResponse was successfully generated for the request, by the ExecuteAsync method. Had we applied the Authorize attribute, it would have returned UnAuthorized status and based on the results it returned, we can then add our authentication challenge on the response from the ExecuteAsync.

So this was how we can implement the basic authentication filter using web-api Authentication filters. Hope you enjoyed reading it. Happy coding...!!!

The type or namespace name 'IAuthenticationFilter' could not be found

IAuthenticationFilter interface is used for inheritance, while creating the security filters for web-api. If you face the issue:

The type or namespace name 'IAuthenticationFilter' could not be found, 

just add the namespace: System.Web.Http.Filters and it will work.  Happy coding...!!!

ASP.Net SignalR IIS hosting

Recently I created a sample SignalR simple chat application. That included a hub class and the calling client in the same application i.e. when the client application is hosted, the hub get's hosted itself. You can check that article here. But after creating that, I realized that what if we need to have the hub and the client applications separately, say the hub is hosted as one application in IIS and the client application is hosted on another server. So decided to create these two as separate applications and see what configurations are required for them.

So let's start by creating the hub class. For this, we create a new empty asp.net application. Next, in order to make it a server hub class, we add references to the SignalR package using nuget package manager. This also includes the OWIN libraries so that the OWIN Starup class can be created for this.

Next, we add a class file to create a hub on the server and name it as ServerHub.cs. This will be having the same method which we had in our previous article of chat application i.e. BroadCastMessage. We will derive it from the Hub class. So the code will look like below:

 using Microsoft.AspNet.SignalR;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;

 namespace HubClass
 {
    public class ServerHub : Hub
    {
        public void BroadCastMessage(String msgFrom, String msg)
        {
            Clients.All.receiveMessage(msgFrom, msg);
        }
    }
 } 

Next, we add another class and name it as Startup.cs, which will be for OWIN startup class. We add the OwinStartup attribute and Configuration method. Here, an important point is needed to be considered. This is app.UseCors(CoreOptions.AllowAll), which is required for cross domain calls, between the hub and the client. If we hover over it, it's definition can be seen as:

Adds a CORS middleware to your web application pipeline to allow cross domain

So the code will look like thee following:

 using Microsoft.AspNet.SignalR;
 using Microsoft.Owin;
 using Microsoft.Owin.Cors;
 using Owin;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;

 [assembly: OwinStartup(typeof(HubClassStartup))]
 namespace HubClass
 {
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.UseCors(CorsOptions.AllowAll);
            app.MapSignalR();
        }
    }
 }

So our server hub is now ready to be hosted in IIS. Open your IIS and create a new virtual application under the Default Website. We will name it as ServerHub and point it to location of our code. See the image below:

Now, start the website and click on Browse application to see the hub hosted. In order to make sure that the hub is hosted properly, add the suffix '/signalr/hubs' to the url of the application. This will load the hub proxy javascript file in the browser. See the image below:

So our hub is now ready to be called from a separate application. For this, we create another empty asp.net application and add an html page. All the client calling code will remain the same, except two changes are to be made. But before we that, we need to add reference to the signalr client libraries, including the javascript files. See the image below:

So our solution will look like the following:

The first change we make is the url reference to the javascript proxy file which will help in connecting to the server hub. This will be changed to the following:

     <script src="http://localhost/ServerHub/signalr/hubs"></script>

Note that we have added the base url where the hub is hosted, to the reference. The second change we make is, add the hub url to be of the same location, where we have the hub hosted. So it will be like the following:

$.connection.hub.url = "http://localhost/ServerHub/signalr/";

Rest of the code remain the same and can be checked at: http://dotnetfreakblog.blogspot.com/2015/02/chat-application-using-aspnet-signal-r.html 

That's it. Run the application in two different browsers and you can see the code in action. 

So this was how we can host the signalr hub on IIS as a separate application. Hope you enjoyed reading it. Happy coding...!!!

SirgnalR - Error during negotiation request

While creating a SignalR sample application, i came across an issue related to request negotiation with the server. It was:  

Error during negotiation request

My scenario was that I had a SignalR hub which was hosted on IIS and a javascript client application which was connecting to this hub. So basically they were created as separate applications. In my case, the issue required a single line fix with the following code in the javascript client:

 $.connection.hub.url = "http://localhost/MyHub/signalr/";

Hope this helps others in case they face the same issue. Happy coding...!!!

Implement Basic Authentication in ASP.Net Web API at Host level (IIS)

In our previous discussion, we discussed what are different ways to implement security in ASP.Net web-api. Before you proceed, I would recommend to go through that article on the link below first, as that will act as a kind of base for where and how we will implement the security.

• Security in ASP.Net Web-API

In this discussion, we will be implementing basic authentication in web-api at the host level. 

As per MSDN, basic authentication is:

IIS implements Basic authentication, which is part of the HTTP 1.0 specification, using Windows user accounts. When using Basic authentication, the browser prompts the user for a user name and password. This information is then transmitted across HTTP where it is encoded using Base64 encoding. Although most Web servers, proxy servers, and Web browsers support Basic authentication, it is inherently insecure. Because it is easy to decode Base64 encoded data, Basic authentication is essentially sending the password as plain text.

As per the above description, there is one limitation to the use of IIS as the host and implementing basic authentication at IIS level. This means, IIS uses Windows credentials to authenticate the users. So this implementation is suitable for any intranet applications, where the users have to access the application within the domain. 

To start with, we will create a new empty project using Empty ASP.Net Web -API template project. Let's name it as BasicAuthenticationIIS.

Next, we add a controller called as SampleController and inherit it from ApiController, to make it a WebAPI controller type. Also, we add a method named GetCurrentDateTime which will return current date and time as string to the user. So our controller will look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace BasicAuthenticationIIS.Controllers
{
    public class SampleController : ApiController
    {
        public String GetCurrentDateTime()
        {
            return DateTime.Now.ToString();
        }
    }
}

Next, we simply host the application in IIS. So open IIS, select " Default Web Site", right click and select the option "Add Application". Provide an Alias name to it and provide the physical path to the application directory. 

Browse the application and enter the path of the webapi and see the results. 

So this was without any security. Anyone who is not authenticated, can access the method. Let's add the authentication to the api. So we start by adding the settings in the config file. So we add the authentication mode to the web.config as Windows. 

<system.web>

    <authentication mode="Windows"></authentication>

</system.web>

Next, add the [Authorize] attribute on the method for which we need to add the security. So our method will look like:

[Authorize]

public String GetCurrentDateTime()

{

            return DateTime.Now.ToString();

}

Build the application. Next, go to IIS and select the option "Authentication".

Within this, select the Basic Authentication option, right click and Enable it.

That's it. Restart the application in IIS and browse the application. It asks for credentials. 

Cancel it and it says Un-Authorized.

Let's refresh and enter windows account credentials. 

Press Ok and see the results.

We can also create a sample application and test it. Create a new a Console application and write the following code:

WebRequest req = WebRequest.Create("http://localhost/BasicAuthSample/api/sample/GetCurrentDateTime");

req.Method = "GET";

req.ContentType = "text/xml";

byte[] authBytes = Encoding.UTF8.GetBytes("emailAddress:Password".ToCharArray());

req.Headers.Add("Authorization", "BASIC " + Convert.ToBase64String(authBytes));

System.Net.WebResponse response = req.GetResponse();

System.IO.StreamReader reader = new StreamReader(response.GetResponseStream());

string str = reader.ReadToEnd();

Run the application and see the results:

In case you do not send the credentials, it will throw an error of type WebException, which you can catch and check status code. The complete code will be like the following:

class Program
    {
        static void Main(string[] args)
        {
            try
            {
                WebRequest req = WebRequest.Create("http://localhost/BasicAuthSample/api/sample/GetCurrentDateTime");
                req.Method = "GET";
                req.ContentType = "text/xml";
                Byte[] authBytes = Encoding.UTF8.GetBytes((ConstantStruct.UserName + ":" + ConstantStruct.Password));
                req.Headers.Add("Authorization", "BASIC " + Convert.ToBase64String(authBytes));
                req.Headers.Add("test", "test");

                System.NetWebResponse response = req.GetResponse();
                System.IOStreamReader reader = new StreamReader(response.GetResponseStream());
                Console.Write(reader.ReadToEnd());
            }
            catch (WebException ex)
            {
                var statusCode = ((HttpWebResponse)ex.Response).StatusCode;
                if (statusCode == HttpStatusCode.Unauthorized)
                {
                    Console.Write("Authorization failed.");
                }
            }
            Console.Read();
        }
    }

So this is how we can implement the host level (i.e. IIS), basic authentication in a web-api. Hope you enjoyed reading it. Happy coding...!!!