Different Types of SQL Server Functions

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). 

Types of Function

1. System Defined Function

   These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

   1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      System Scalar Function

      Scalar Function

      Description

      abs(-10.67)

      This returns absolute number of the given number means 10.67.

      rand(10)

      This will generate random number of 10 characters.

      round(17.56719,3)

      This will round off the given number to 3 places of decimal means 17.567

      upper('dotnet')

      This will returns upper case of given string means 'DOTNET'

      lower('DOTNET')

      This will returns lower case of given string means 'dotnet'

      ltrim(' dotnet')

      This will remove the spaces from left hand side of 'dotnet' string.

      convert(int, 15.56)

      This will convert the given float value to integer means 15.

   2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      System Aggregate Function

      Aggregate Function

      Description

      max()

      This returns maximum value from a collection of values.

      min()

      This returns minimum value from a collection of values.

      avg()

      This returns average of all values in a collection.

      count()

      This returns no of counts from a collection of values.

2. User Defined Function

   These functions are created by user in system database or in user defined database. We three types of user defined functions.

   1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

      
       --Create a table 
      CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       FirstName varchar(50) NULL,
       LastName varchar(50) NULL,
       Salary int NULL,
       Address varchar(100) NULL,
      )
      --Insert Data
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      --See created table
      Select * from Employee 
      
      

      
      

      
       --Create function to get emp full name 
      Create function fnGetEmpFullName
      (
       @FirstName varchar(50),
       @LastName varchar(50)
      )
      returns varchar(101)
      As
      Begin return (Select @FirstName + ' '+ @LastName);
      end 
      
      

      
      

      
       --Calling the above created function
      Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 
      
      

      

   2. Inline Table-Valued Function

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

      
       --Create function to get employees
      Create function fnGetEmployee()
      returns Table
      As
       return (Select * from Employee) 
      
      

      

      
       --Now call the above created function
      Select * from fnGetEmployee() 
      
      

      

   3. Multi-Statement Table-Valued Function

      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

      
       --Create function for EmpID,FirstName and Salary of Employee
      Create function fnGetMulEmployee()
      returns @Emp Table
      (
      EmpID int, 
      FirstName varchar(50),
      Salary int
      )
      As
      begin
       Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      --Now update salary of first employee
       update @Emp set Salary=25000 where EmpID=1;
      --It will update only in @Emp table not in Original Employee table
      return
      end 
      
      

      
      

      
       --Now call the above created function
      Select * from fnGetMulEmployee() 
      
      

      
      

      
       --Now see the original table. This is not affected by above function update command
      Select * from Employee 
      
      

      

Note

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.

Creating Custom Action Filters in ASP.NET MVC

Introduction

In ASP.NET MVC applications the controller consists of one or more methods known as actions or action methods. In certain cases you require that in addition to the code written in the action method, some extra processing be carried out before or after the action method execution. To accomplish this MVC offers what is known as Action Filter. If you have programmed in ASP.NET MVC before, chances are that you already used some in-built action filters. For example, the [OutputCache] and [Authorize] attributes provided by ASP.NET MVC are actually action filters. Additionally you can build your own action filters to fit a specific purpose. This article teaches you just that.

Types of Action Filters

If an action method has more than one action filter applied, the order in which they are executed is not fixed. This may not be desirable in all the situations. Consider, for example, a case where your custom action filter is doing some security checking and you wish to run it before any other action filter runs. To take care of such situations the ASP.NET MVC framework provides certain interfaces. Based on the interface your action filter implements its execution order is determined. To be specific, the following four types of action filters can be created based on the interface they implement.

1. Authorization Filters : These filters are always run first before any other filters and they implement IAuthorizationFilter interface.
2. Action Filters : Action filters run before and after the controller action method and implement IActionFilter interface.
3. Result Filters : Result filters are executed before and after the view and implement IResultFilter interface.
4. Exception Filters : These filters execute in the end and implement IExceptionFilter interface.

Thus action filters are executed in the order 1-2-3-4 and you can control where a specific custom filter goes with the help of corresponding interface.

Technically an action filter is a class that inherits from FilterAttribute base class and then implements required interfaces. Before we go ahead and create our own action filter here is what a skeleton code looks like :

public class MyActionFilterAttribute : FilterAttribute, IActionFilter
{
 ...
}
public class Home : Controller
{
   [MyActionFilter]
   public ActionResult Index() {...} 
}

To simplify your work ASP.NET MVC comes with a class - ActionFilterAttribute that already inherits from the FilterAttribute base class and implements IActionFilter and IResultFilter interfaces. So, as an alternative you can also inherit your class directly from ActionFilterAttribute base class and then override its methods.

Creating Your Own Action Filter

At a minimum a custom action filter class inherits from the FilterAttribute base class. Based on your requirements you may need to perform the following steps:

1. Decide whether your custom action filter needs to have a specific order in the execution chain.
2. Depending on the required order, implement IAuthorizationFilter or IActionFilter or IResultFilter or IExceptionFilter.
3. If you are implementing IAuthorizationFilter interface then write implementation for OnAuthorization() method.
4. If you are implementing IActionFilter interface then write implementation for OnActionExecuting() and OnActionExecuted() methods.
5. If you are implementing IResultFilter interface then write implementation for OnResultExecuting() and OnResultExecuted() methods.
6. If you are implementing from IExceptionFilter interface then write implementation for OnException() method.
7. Decorate action methods with one or more action filters.

Sample Action Filters

In order to understand how action filters work you will create the following four action filters :

ValidateUserRoles

ASP.NET MVC comes with an inbuilt action filter - [Authorize] - that can be used for role based security. The [Authorize] attribute, however, expects a role name at development time. For example, here is a sample usage of [Authorize] attribute:

[Authorize(Roles="Administrator")]

But what if you don't know the role name at development time? What if the roles allowed to invoke an action method are coming from a database? In such cases you cannot use the [Authorize] attribute as shown above. To tackle this problem you will create a ValidateUserRoles custom action filter that will give you a chance to verify a currently logged in user against roles that are pulled from the database (though we won't write the actual database code here to keep things simple). Clearly ValidateUserRoles is an authorization filter and will implement IAuthorizationFilter interface.

TrackUserIP

At times you need to capture the IP address of the client invoking an action method (say for security, tracking or analytical reasons). The TrackUserIP action filter will do just that. The TrackUserIP is a normal action filter and hence will implement IActionFilter interface.

DisplayAds

A common scenario in web pages is to render advertisements. One way to achieve this is to include advertisement rendering logic in the web page itself. However, this approach may not be suitable in all the cases. The DisplayAds action filter appends advertisements when a result is processed. This way the view need not know anything about the advertisement display logic. They are emitted in the final output by the DisplayAds action filter. Since DisplayAds action filter works on the output of views it needs to implement IResultFilter interface.

NotifyException

The NotifyException action filter sends a notification to an email address whenever there is any error in an action method or view. You can use this action filter to notify the administrator or technical team about the exception so that corrective action can be taken if necessary. The NotifyException action filter is an exception action filter and hence will implement IExceptionFilter interface.

To begin developing these custom action filters, create a new ASP.NET MVC 3 web application. You can either implement Forms Authentication yourself or use the default mechanism. The following sections assume that you have Forms Authentication and membership in place with a role - Administrators.

Then create a folder named CustomFilters and add four class files to it viz. ValidateUserRoles.cs, TrackUserIP.cs, DisplayAds.cs and NotifyException.cs

ValidateUserRoles Action Filter

The following listing shows the ValidateUserRoles action filter :

public class ValidateUserRoles:FilterAttribute,IAuthorizationFilter
{
    public void OnAuthorization(AuthorizationContext filterContext)
    {
        Debug.WriteLine("Inside OnAuthorization");
        if (filterContext.HttpContext.Request.IsAuthenticated)
        {
            if (!Roles.IsUserInRole("Administrators"))
            {
                ViewResult result = new ViewResult();
                result.ViewName = "SecurityError";
                result.ViewBag.ErrorMessage = "You are not authorized to use this page. Please contact administrator!";
                filterContext.Result = result;
            }
        }
        else
        {
            ViewResult result = new ViewResult();
            result.ViewName = "SecurityError";
            result.ViewBag.ErrorMessage = "You are not authenticated. Please log-in and try again!";
            filterContext.Result = result;
        }
 
    }
}

As you can see, the ValidateUserRoles class inherits from the FilterAttribute base class and also implements the IAuthorizationFilter interface. The IAuthorizationFilter interface expects you to write implementation of the OnAuthorization() method.

The OnAuthorization() method receives a parameter of type AuthorizationContext that gives you access to the underlying controller and result. The code then checks whether the current request is authenticated or not. If the request is authenticated it further checks the user role. In the example above you have used the role name (Administrators) as a literal value but in a more real world scenario you can fetch it from some database or configuration file. If the user doesn't belong to the specified role we create a new ViewResult object based on the SecurityError view (you will create the views later), set the ErrorMessage member of the ViewBag and then set the Result property of the filterContext parameter. This way if the user doesn't belong to the Administrators role, an error message will be displayed on the screen. If the user is not yet authenticated we display an appropriate error message prompting him to log-in and try again.

TrackUserIP Action Filter

The TrackUserIP action filter inherits from the FilterAttribute base class and implements IActionFilter. The following listing shows the complete code of the TrackUserIP action filter.

public class TrackUserIP:FilterAttribute,IActionFilter
{
    public void OnActionExecuting(ActionExecutingContext filterContext)
    {
        Debug.WriteLine("Inside OnActionExecuting");
        string userIP = filterContext.HttpContext.Request.UserHostAddress;
        LogIP(filterContext.HttpContext.Request.Url.PathAndQuery,userIP,"Attempted");
    }
 
    public void OnActionExecuted(ActionExecutedContext filterContext)
    {
        Debug.WriteLine("Inside OnActionExecuted");
        string userIP = filterContext.HttpContext.Request.UserHostAddress;
        LogIP(filterContext.HttpContext.Request.Url.PathAndQuery, userIP, "Completed");
    }
 
    private void LogIP(string url,string ip,string msg)
    {
        Debug.WriteLine(msg + " : " + url + "[" + ip + "] on " + DateTime.Now.ToString());
    }
 
}

The IActionFilter interface requires that you implement OnActionExecuting() and OnActionExecuted() methods. As you might have guessed these methods are executed pre and post the controller action under consideration. In both the methods we just log the IP address of the client to the Visual Studio Debug window. In a more realistic situation you will store it in some database. So the sequence of execution will be OnActionExecuting() - Action Method - OnActionExecuted().

DisplayAds Action Filter

The DisplayAds action filter is a result filter and implements the IResultFilter interface. The complete code of the DisplayAds action filter is given below:

public class DisplayAds:FilterAttribute,IResultFilter
{
    public void OnResultExecuting(ResultExecutingContext filterContext)
    {
        Debug.WriteLine("Inside OnResultExecuting"); 
        filterContext.Controller.ViewBag.AdMarkup = GetAdMarkup();
    }
 
    public void OnResultExecuted(ResultExecutedContext filterContext)
    {
        Debug.WriteLine("Inside OnResultExecuted"); 
        UpdateAdImpressions();
    }
 
    private string GetAdMarkup()
    {
        return "<hr />This is ad text.<hr />";
    }
 
    private void UpdateAdImpressions()
    {
        //write database code to increment
        //ad impressions here.
    }
}

The IResultFilter interface expects you to implement OnResultExecuting() and OnResultExecuted() methods. These methods are executed before and after the view is processed. The OnResultExecuting() method retrieves the ad markup based on some logic and sets a ViewBag member accordingly. This way AdMarkup member is available to the view before rendering the contents. The OnResultExecuted() method can be used to keep track of ad impressions. Note that GetAdMarkup() and UpdateAdImpressions() methods don't contain any significant logic in the example above. In a real world scenario you will have some database driven logic in these methods.

NotifyException Action Filter

The NotifyException action filter is an exception filter and hence implements the IExceptionFilter interface. The complete code of the NotifyException class is given below:

public class NotifyException:FilterAttribute, IExceptionFilter
{
    public void OnException(ExceptionContext filterContext)
    {
        Debug.WriteLine("Inside OnException"); 
 
        if (filterContext.Exception != null)
        {
            string msg = filterContext.Exception.Message;
            SmtpClient email = new SmtpClient();
            email.Send("admin@foo.com", "test@foo.com", "Error in MVC application", msg);
        }
    }
}

The IExceptionFilter interface requires that you implement the OnException() method. The OnException() method is called only if there is any exception during the execution of the action or result. The Exception property of the filterContext parameter gives you the Exception that was thrown. You can then send an automated email using the SmtpClient class.

Using Custom Action Filters

Now that your custom action filters are ready, it's time to use them in some controller. Add a new controller named HomeController and code the Index() action method as shown below:

public class HomeController : Controller
{
    [Authorize]
    [ValidateUserRoles]
    [TrackUserIP]
    [DisplayAds]
    [NotifyException]
    public ActionResult Index()
    {
        ViewBag.Message = "Welcome!";
        return View();
            
    }
}

Notice how the Index() method is decorated with action filter attributes. The Index() method simply sets a member of ViewBag (Message) and renders the Index view. The Index view is shown below:

<html>
<head runat="server">
    <title>Index</title>
</head>
<body>
    <div>
       <h1><%= ViewBag.Message %></h1>
    </div>
    <%= (ViewBag.AdMarkup == null ? "" : ViewBag.AdMarkup)%>
</body>
</html>

Recollect that AdMarkup member is being set via the DisplayAds action filter.

Before you run the application you need one more view - SecurityError. The markup of the SecurityError view is shown below :

<html>
<head runat="server">
    <title>SecurityError</title>
</head>
<body>
    <div>
        <strong><%= ViewBag.ErrorMessage %></strong>
        <%= Html.ActionLink("Login","Login","Membership") %>
    </div>
</body>
</html>

The SecurityError view simply displays the ErrorMessage as set by the ValidateUserRoles action filter. A login link is also rendered so that the user can navigate to the login page. (You will find the Membership controller in the code download. If you are using some other mechanism of authenticating users you should change the ActionLink call accordingly.)

Now run the web application and log-in to the system. If the user is not an Administrator you should get an error message like this :

Figure 1: Error: User not an Administrator

If the user belongs to the Administrators role you will be able to view the Index view as shown below :

Figure 2: View the Index

Notice how the advertisement markup as set by the DisplayAds action filter is displayed. If you see a Visual Studio Output window, it should resemble as shown below :

Figure 3: Visual Studio Window

Notice the sequence of various Debug.WriteLine() statements that are outputted.