User Defined Functions in SQL Server

User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below –

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –

• Inline Table
• Multi-statement Table

We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –

• Customers
• Employees
• Orders
• Order Details
• Products

Let’s start querying the above table. Open a new Query window and write the following commands –

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad –



The above function returns an integer value. To test this function, we will write some code as shown below –



Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad –



We will test this function with different years for an employee as shown below –

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below –



To test this example we will use a select statement as shown below –



Another example of the Inline Table Valued Function is as shown below –



To test this function, we will use different years as shown below –




We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below –



To use the Multi-Statement Table Valued function, use this code –



There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below –

• You cannot modify the state of the database using UDFs
• Unlike Stored Procedures, UDF can return only one single result set
• UDF does not support Try-Catch, @ERROR or RAISERROR function

Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

.net session timeout settings in IIS6 on windows 2003 server

Does this sound simple web.config setup? 

Nope this is way beyond that newbie configuration.

Problem: We were facing timeout issue on one of our servers. Web.config settings had no effect on the time out.

Note: In all screen shots pink color is hiding the actual server details.

Solution:
So here are some more interesting findings related to timeouts of sessions in IIS6 hosted on a windows 2003 server. You may need to change one or more of these setups in your server according to your setup.
There are totally 5 setups that influence your timeout. All of those are discussed here.
1. Web.config file of Application: First one to influence timeout is Session timeout setting in your we.config file.



Note: Web.config file is inherited to the subfolders and subfolders config settings take the precedence in case they exist.

2. Application session timeout in IIS:
Go to IIS, right click on the web application, go to properties. Go to Directory tab, click on Configuration button. Application configuration tab opens, click on Options tab as in the screenshot, you will find enable session state. Change the session time out period here.



3. Default website session timeout in IIS: Right click default web site under IIS and choose Home directory tab. Click on configuration button to open application configuration. Choose options tab and you will find Enable session state again similar to session state of each application. Enter higher session timeout period which will be application to all sites in the webserver.


4. Change Worker process idle timeout of application pool – Shutdown worker process after being idle for (time in minutes) – Default is 20 minutes. This setup is applied for all websites that use this application pool. To change this, Right click on the required application pool, choose Performance tab, and change the duration of idle time or uncheck the option so that the process is never recycled. You can also create an application pool, configure it, and use it for a selected list of web applications.



5. Change Recycle timeout period of application pool: Right click on application pool or default application, and choose Recycling tab. Change the “Recycle worker process (in minutes)” option to either change the period or disable the option by un checking. Un checking this option may be a good idea from performance point of view.



And finally there is a last one in machine.config file. But that does not affect any of these configurations and these setups take the precedence.

Authentication in .NET Web Api 2 Video

Excellent video on Web API security

WEB API - Quick reference -1

Working With Transaction in Entity Framework 6.0

Entity Framework internally maintains transactions when the SaveChanges() method is called. It means the Entity Framework maintains a transaction for the multiple entity insert, update and delete in a single SaveChanges() method. When we execute another operation, the Entity Framework creates a new transaction.

For example I have two entities, employee and department, and I have added the employee and department entities to the context object and finally call the SaveChanges() method. The Entity Framework creates a single transaction for these multiple inserts.

1. using (EntitiesContext context = new EntitiesContext())  
2. {  
3.     EmployeeMaster employee = new EmployeeMaster();  
4.     employee.Code = "A0001";  
5.     employee.Name = "Jignesh Trivedi";  
6.     employee.DepartmentId = 1;  
7.     context.Employees.Add(employee);  
8.   
9.     DepartmentMaster dept = new DepartmentMaster();  
10.     dept.Code = "DEP0001";  
11.     dept.Name = "Department 1";  
12.     context.Departments.Add(dept);  
13.   
14.     context.SaveChanges();  
15.     Console.ReadLine();  
16. }  

SQL logging output:





Earlier the System.Transactions namespace was used to handle transactions in the Entity Framework using TransactionScope and the Entity Framework uses this transaction to save the changes in the database.

1. using (TransactionScope scope = new TransactionScope())  
2. {  
3.       //Code Here  
4. }  

New API with Entity Framework 6.0
Entity Framework 6.0 introduced two new APIs to maintain the transaction. 

1. DbContext.Database.BeginTransaction: It allows us to begin a transaction. It allows us to combine several operations to be combined within the same transaction and hence all the transactions are either committed or rolled back. This method allows us to specify the isolation level for the transaction.
2. DbContext.Database.UseTransaction: It allows DbContext to use a transaction that was stated outside of the Entity Framework. It means using this API we can use any existing transaction with Entity Framework.

DbContext.Database.BeginTransaction
This method returns a DbContextTransaction object. The BeginTransaction method has two overloads, one has no argument and the other accepts an explicit Isolation Level. 

A DbContextTransaction object provides Commit() and Rollback() methods to do commit and rollback on the underlying store transaction.

Test Code

1. using (EntitiesContext context = new EntitiesContext())  
2. {  
3.     using (var transaction = context.Database.BeginTransaction())  
4.     {  
5.         try  
6.         {  
7.             EmployeeMaster employee = new EmployeeMaster();  
8.             employee.Code = "A0001";  
9.             employee.Name = "Jignesh Trivedi";  
10.             employee.DepartmentId = 1;  
11.             context.Employees.Add(employee);  
12.             context.SaveChanges();  
13.   
14.             DepartmentMaster dept = new DepartmentMaster();  
15.             dept.Code = "DEP0001";  
16.             dept.Name = "Department 1";  
17.             context.Departments.Add(dept);  
18.             context.SaveChanges();  
19.   
20.             transaction.Commit();  
21.         }  
22.         catch (Exception ex)  
23.         {  
24.             transaction.Rollback();  
25.         }  
26.     }  
27. }  

This method requires an open underlying stored connection. This method opens a connection if it is not already open. This method will close the connection when Dispose () is called.

The overloaded version of the DbContext.Database.BeginTransaction method:



SQL logging output



DbContext.Database.UseTransaction 

Sometimes we must use an existing transaction that is started outside of the Entity Framework. In this case the DbContext.Database.UseTransaction API is very useful. In this method we can pass an existing transaction object.

To maintain the transaction, the connection object must be the same, so we are required to create a DbContext contractor that takes a connection as an argument.

1. public class EntitiesContext : DbContext  
2. {  
3.     public EntitiesContext()  
4.         : base("name=Entities")  
5.     {  
6.         Database.Log = Console.WriteLine;  
7.     }  
8.     public EntitiesContext(DbConnection existingConnection, bool contextOwnsConnection)  
9.         : base(existingConnection, contextOwnsConnection)  
10.     {  
11.   
12.     }  
13. }  

Test Code

1. using(SqlConnection con = new SqlConnection("connectionString"))  
2. {  
3.     con.Open();  
4.     using(var transaction = con.BeginTransaction())  
5.     {  
6.         // Do something....  
7.   
8.         //Pass this transaction to EF....  
9.         using (EntitiesContext context = new EntitiesContext(con, false))  
10.         {  
11.             context.Database.UseTransaction(transaction);  
12.             EmployeeMaster employee = new EmployeeMaster();  
13.             employee.Code = "A0001";  
14.             employee.Name = "Jignesh Trivedi";  
15.             employee.DepartmentId = 1;  
16.             context.Employees.Add(employee);  
17.   
18.             context.SaveChanges();  
19.         }  
20.     }  
21. }  

Here the contextOwnsConnection flag must be set to false when we pass an open connection to the DBContext. This is very important because it informs the Entity Framework to not close the connection when it is done.

Why You Should Use AngularJS in your Next Web Application

Adding a new javascript framework like AngularJS to your web app requires some careful evaluation.  Most projects already are using jQuery, maybe jQuery UI, and potentially other javascript libraries to handle other functionality not covered by jQuery or even jQuery plugins for UI elements like charts, multi-selects, infinite scrollers, sliders, etc.  Any javascript framework has a tough task to find a space to fill with developers.

Your decision might consider the additional lines of code added, worrying about if this may slow down your javascript execution, or page load times.  You know you will also have a huge initial learning phase where you discover how to use, then try to learn best practices as you start to play around with implementing your app.  Maybe you are concerned that it is just a fad framework too, there are other options for similar Javascript frameworks like Backbone.js, Knockout.js.

For most, I bet the initial learning phase and temporary slowdown in development is the primary hurdle.  This can slow down a project that could be quickly done with existing technology.  The long term benefit has to be there in order to spend your time learning and figuring out best practices, and ultimately the new piece of technology must be solving a problem in your software architecture to justify the time.

I found when researching AngularJS and going through the process of implementing an app with it, that it was going to be a great tool in making web applications.  For a big list of criteria I recommend to evaluate when considering adding a new component to your web application, check out my article here titled 18 Questions to Ask Before Adding New Tech to your Web App.  Here are some of the criteria from that list and how my evaluation of AngularJS went.  It's my opinion that there is a significant hole in front-end web development that AngularJS fills.

1. Address some problems in your software architecture

When writing web applications, I have objects in the server-side code that often times aren’t represented as objects in the client-side code.  For simple apps, this might be OK, but when it gets complicated, it can be a big help to mirror these objects on both sides.  Also leads to a terminology issue, a Person object on the server can’t truly be talked about as a Person on the client side because it doesn’t look or feel the same way.  Doesn’t have the same methods, isn’t represented as code, sometimes is stuffed into hidden inputs or in data attributes.

Managing this complexity can be very hard.  AngularJS has ng-resource which you use to create services that hook up to REST APIs and return back that object in JSON and you can attach methods to that object so it can be a fully functional object.  It feels more like something familiar to what you are working with on the server side.  All without much work on your end, you have methods like save(), get(), update(), that map to REST API endpoints and are most likely the similar methods you might have in your Data Mapper on the server side.

AngularJS encourages you to also deal with models on the client side just like you have them on the server side, big plus there.

I also don’t feel like the design using jQuery + Mustache is elegant when it comes to having an object that has properties represented in different ways within the web UI.  An example, you have a table of Person objects from the REST API, you have a button for each Person to denote they have “Accepted” an invitation, so when they click, you want the checkbox to change and you want the style on the row to change.  In jQuery, you listen for the checkbox change event, then you toggle the class on the button and the row.  In AngularJS, the model is the source of truth so you build everything from that.

See what I mean by taking a look at this jQuery vs. AngularJS plunker I created and compare the type of code you write.

2. Enable you to create software more quickly and with less effort

AngularJS having the ng-model and ng-class directives alone cover so many of the common operations that we all have been doing in jQuery.  Two-way data binding and saving to the server now takes a small number of lines in AngularJS, but in jQuery would require creating your own object, and several different click and event handlers.  Switching from watching elements and events to watching a model is a big shift in the right direction.

3. Result in software that is more maintainable

AngularJS encourages using the model as the source of truth, which starts to get you to also think object oriented design on the client-side.  This allows you to keep in mind the same object-oriented design principles that in general make software more maintainable compared to procedural.

4. Improve the testability of your software

AngularJS has dependency injection at its core, which makes it easy to test.  Even the documentation on the AngularJS site has testing as a part of every tutorial step, which almost makes it hard NOT to test.

5. Encourage good programming practices

Model as the source of truth, dependency injection, ability to create directives that can decorate elements that lends to reusable and shareable components, REST API connection to your server, lots of benefits from just following AngularJS basic usage.

6. Allow you to collaborate more easily with other people

Using models as the source of truth is something that is familiar with anybody who is doing object-oriented MVC server-side software, so this should make it easy to pick up for most web developers.

Also, being able to create directives in AngularJS and dependency injection makes it easy to create components that can be shared easily between developers and really has excited the developer community.  Lots of existing projects have developed AngularJS directive bridge libraries so you can use their code by tossing an AngularJS directive to decorate an existing element with new functionality.  Like Select2, Infinite Scroller, Bootstrap, and Angular has its own UI companion suite.  Just check outhttp://ngmodules.org/.

7. Allow you to become proficient in a reasonable time

It took me around 2 full weeks to feel like I was proficient, where I was starting to pick out and understand best practices and look at some of the finer points of the framework.  Where I started from is that I have a lot of experience developing with Javascript, jQuery, jQuery UI, and also implemented a project using Backbone.js earlier this year which is one of the other Javascript frameworks built to solve a similar type of solution as AngularJS.  I felt like knowing how Backbone.js works helped a lot with picking up AngularJS.