GROUP BY, GROUPING SETS, ROLLUP and CUBE

I got a chance to look into some of the SQL server functions and I will say it is really useful specially for those who working in any kind of reporting like SSRS(SQL server reporting service) and also it is useful for all other SQL developers. Let us have look at these functions and  all these function are inter linked in terms of the way we want different types of results. Each functions are used to prepare some kind of summary results.

 

Before you I go directly with each function I can give you table view where I have tested all these functions.
I have used two tables to create an example with order and purchasing items for same.

 

GROUP BY and  GROUPING SETS

I do not need to explain much about GROUP BY since everybody knows well but when we combine GROUPING SETS with GROUP BY it is quiet different and gives a summary result on top of GROUP BY. So basically if some body want to find a total amount at each order or purchase level, this will be the ideal function. Please see below the query and results

SELECT oh.OrderID ,oh.ProductID ,SUM(p.price) AS Total FROM OrderHistory oh 
inner join product p on oh.productid = p.productid GROUP BY GROUPING SETS(oh.OrderID,oh.ProductID)

ROLLUP

ROLLUP is again going one more level into the summary . for eg : if we want to display Order and items purchased for that order and also total order.

SELECT COALESCE(cast(oh.OrderID as varchar(50)),'Grand : ') as 'Order', 
COALESCE(p.Name,'Total : ') as Product ,SUM(p.price) AS Total FROM OrderHistory oh 
inner join product p on oh.productid = p.productid GROUP BY  oh.OrderID,p.Name WITH ROLLUP

CUBE

 CUBE is again going one more level into the summary . for eg : if we want to display Order and each items purchased for that order and also total order.

SELECT oh.OrderID as ‘Order’, COALESCE(p.Name,‘Total : ‘) as Product, SUM(p.price) AS Total FROM OrderHistory oh inner join product p on oh.productid =p.productid GROUP BY oh.OrderID, p.Name WITH CUBE

 

happy reading and please let us know if anybody have a better approach than this or any other easy and optimized way to achieve the same summary results.

Split Web config for different environment

download complete source code

Introduction

Splitting web config file (Complete source code) for different environments is a brilliant thought since it has many advantages in the level of reducing work, lack of confusion between different files in different environment, rare chance to messed up files between different environment and also in keeping credentials in a secure place. Microsoft has given the facility to do same and I think majority of peoples  are not taking advantages of this facility when they setup different environments.

Advantages of Intelligent web config splitting

File structure for two different environment

Production

UAT and Staging

A case study

Here is an Old Environment

1. we have lots of country sites, it has same source code but separate domain(because of some valid reasons) and separate deployment.
2. We have development , testing, and Staging environment.
3. We do bug fixes , performance optimization and new enhancements.
4. We have some contractors in team and they will be knowing almost all credentials.
5. We have separate deployment team for testing and staging environment.
6. We have to raise ticket to correct entry or anything in deployment.
like many….

We do in following way before we thought about splitting web config

1. We do Nant script copy binary files across all country sites.
2. There will be some shared key which has to go to all sites, we do by opening each country sites and manually update. (sometime we are able to do by Nant script but not always).
3. we did not care much about DB passwords and other credentials.
4. sometime we messed up config file in different environment and we will raise another ticket to change and wait for that.

Cool…Here is the Real Advantages

1. We store all common keys (appsettings) in a common config file and shared for all country sites.
2. All credentials like Password we moved to another file which can only accessed by system Admins.
3. We maintained separate specific files for different environment so no confusion at all.
4. Some of the site specific appsetting we store at Site level and other share app setting keys are in share key file.
and finally you will have many other advantages for you scenario……..

Source code Samples – download complete source code

Splitting Keys (<appSettings>) into two different files

Web.config

<configuration> 
<connectionStrings configSource="admin.config"> </connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web> 
<appSettings file="key.config" >
<add key="ChildKey1" value="ChildValue1"/>
<add key="Childkey2" value="ChildValue2"/>
</appSettings>
<system.webServer>
<modules runAllManagedModulesForAllRequests="true"/>
</system.webServer>
</configuration>

Admin.Config

<connectionStrings >
<add name="BannerConnectionString" connectionString="Data Source=localhost;Initial Catalog=Banner;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

Key.Config

<?xml version="1.0" encoding="utf-8"?>
<appSettings >
<add key="ChildKey1" value="ChildValue1"/>
<add key="ChildKey2" value="ChildValue2"/>
</appSettings>

 

Finally very Important – How to protect splitted config files from end user

It is very important that splitted has to be hidden from end user and it can be done through following two methods

1. Admin can restrict folder level access by keeping new config files in a seprate folder.
2. We can restrict Files  by ASP.Net built-in access system in Web.config

<location path="Key.Config">
<system.web>
<authorization>
<deny users="*"/>
</authorization>
</system.web>
</location>

 

Breaking of web config does not make much advantage if your environment is any of following

1. small application having few developers and it does not have any environment other than development and production
2. Your application does not much care about security of database using in your application
3. Your do not have separate admin or deployment team to do production or UAT deployment
4. Config files in different environment are always in sync
like many reasons ………………..

Most of the peoples will ignore this because of few of following reasons (even me also did)

1. deployment will happen very rarely and also it is a one time job.
2. peoples are doesn’t care about security until and unless  some unauthorized person access database.
3. Even if configuration files messed up in different environment, it can be changed not a big thing

Conclusion

This is a simple idea but I think it is nice to have and please let me know if anybody have better idea or any comments. Happy reading…

Workflow Service 4.0 with complete working code

Download Complete working Solution

Workflow Service 4.0

Introduction

It is very interesting that Microsoft has come up with something new in Windows Workflow foundation 4.0. I got a chance to check new Workflow features in Visual Studio 2010 RTM and noticed that there are lots of differences between WF3.5 and WF4. The major replacement is that they removed state machine workflow and provided with a better one “Flow chart” and no more code activity. WCF workflow services 4.0 are implemented very differently, and there are no more code-behind files (sorry to those who are looking for code activity in WF 4.0). All of these changes are done to improve performance, code separation and for better understanding of workflows.

Background

This article is presented for those who have some knowledge about workflow 3.0, 3.5 and WCF service, even though all other beginners can learn workflow by downloading the source code.

New Features and Replacements in Workflow 4.0

Have a look at the below picture between work flow templates in 4.0 and 3.5

State machine workflow is replaced with Flow chart

As we know, there are two types of workflows in WF3.0 or 3.5, sequential and state machine. You can add activities to these workflows. In WF4, a workflow is an activity which contains other activities. That explains why there is only an Activity Library project template in WF4.Flow chart workflow is a better choice Microsoft has come up with. Most of us know about control of flow chart and this workflow exactly works in the same concept.

No “CodeActivity” in tool box and it can be done by creating a custom code Activity

Workflow 4.0 is completely working based on XAML design and there is no more code behind concept. But still anybody wants to call any method, there are proper control items like method invoker and configure type and method name. I will be explaining below in detail how to call the method in WF4.0.

How to use Invoke method in Workflow service 4.0

Please use my another article : Invoke Method Activity

Message flow and Workflow Instance

Workflow4.0 services start with Receive Request and End with Send response to the service client. All the implementation can be done between these two main activities.

New workflow instance will be created by checking below property “CanCreateInstance” on “Receive Request” (shown in the above picture). We don’t need to create workflow instance as we used to do earlier.

Creating public variables in Workflow instance

Earlier, we used to create public variables in code behind files but in workflow 4 can allow only create variables from workflow XAML designer. These public variables can be used to pass as parameter or return value in method or any kind of manipulations.

Follow below steps to create variables

1. Select topmost activity (sequence) and click variables link at the bottom of window.
2. Give name, type, scope and default as shown below, Custom Type can be given by browse option in drop downs.

Getting Started with Workflow Service 4.0 Sample Source code: Calling WCF service from Workflow Service

Overview about Solution structure

As we see, the solution contains total 6 projects, in three categories Service host, Service Library and Web client to test integration with all these services. I have separated service host and service library for easy deployment and responsibility . Contract is a separate assembly which will be referred across all projects. In this sample application, WCF client is invoking by custom code activity called “CreateOrder” in service library.

Custom Exception handling through Fault Exception in Workflow service 4.0

Exception handling ca be done via placing try catch activity in work flow designer and in each catch block will be typed with proper exception type. In the attached code I have created a custom exception class to send custom messages to the client side. Send receive activity is required to place in each catch block to throw back exception to web client.

Conclusion

Initially it will be bit difficult to understand but once you get into then it is very easy. Attached source code is a better tutorial for readers. Any clarification I am happy to help .