Guide for Developers

Tuesday, 26 August 2014

What is the difference between Web Farm and Web Garden?

I have been asked this question many times by different readers of my blog. They wanted to know about the fundamentals of Web Farms and Web Garden. In this blog post, I am going to explain the exact difference between web farm and web garden, and the advantages and disadvantages of using them. I have also described how to create web garden in different version of IIS.

Overview

Visual Studio has its own integrated ASP.NET engine which is used to run the ASP.NET Web application from Visual Studio. ASP.NET Development Server is responsible for executing all the requests and responses from the client. Now after the end of development, when you want to host the site on some server to allow other people to access, concept of web servers comes in between. A web server is responsible for providing the response for all the requests that are coming from clients. The below diagram shows the typical deployment structure of an ASP.NET Web application with a single IIS.

Clients request for resources and IIS process the request and send back to clients. If you want to know more details on How IIS Processes the request, please read one of my articles about “How IIS Process ASP.NET Request?”.

Web Farm

This is the case where you have only one web server and multiple clients requesting for resources from the same server. But when are is huge amount of incoming traffic for your web sites, one standalone server is not sufficient to process the request. You may need to use multiple servers to host the application and divide the traffic among them. This is called “Web Farm”. So when you are hosting your single web site on multiple web servers over load balancer is called “Web Farm”. The below diagram shows the overall representation of Web Farms.

In general web farm architecture, a single application is hosted on multiple IIS Server and those are connected with the VIP (Virtual IP) with Load Balancer. Load Balancer IPs are exposed to external world to access. So whenever some request will come to server from clients, it will first hit the Load Balancer, then based on the traffic on each server, LB distributes the request to the corresponding web server. These web servers may share the same DB server or may be they can use a replicated server in the back end.

So, in a single statement, when we host a web application over multiple web servers to distribute the load among them, it is called Web Farm.

Web Garden

Now, let’s have a look at what is Web Garden? Both the terms sound the same, but they are totally different from each other. Before starting with Web Garden, I hope you have a fundamental idea of what an Application Pool is and what a Worker Process is. If you have already read the article, “How IIS Processes ASP.NET Request ?”, then I can expect that you now have a good idea about both of them.

Just to recall, when we are talking about requesting processing within IIS, Worker Process (w3wp.exe) takes care of all of these. Worker Process runs the ASP.NET application in IIS. All the ASP.NET functionality inside IIS runs under the scope of worker process. Worker Process is responsible for handling all kinds of request, response, session data, cache data. Application Pool is the container of worker process. Application pool is used to separate sets of IIS worker processes and enables a better security, reliability, and availability for any web application.

Now, by default, each and every Application pool contains a single worker process. Application which contains the multiple worker process is called “Web Garden”. Below is the typical diagram for a web garden application.

In the above diagram, you can see one of the applications containing the multiple worker processes, which is now a web garden.

So, a Web application hosted on multiple servers and access based on the load on servers is called Web Farms and when a single application pool contains multiple Worker processes, it is called a web garden.

Create Web Garden in IIS 6 and IIS 7

Now, I am going to show how you can change the Number of Worker processes in both IIS 6 and IIS 7. For IIS 6, Right Click on Application Pool > Properties > Goto Performance Tab.

In the “Performance Tab” section, you would have one option called “Web Garden” where worker process sets to “1”, you can set the number of worker processes that you required.

For IIS 7, Right Click on Application Pool > Go To Advance Settings > In Process Model section, you will have “Maximum Worker Processes”. You can change it more than 1 to make it as a web garden.

In the above image, you can also check the definition of Web Garden.

You can find one of my previous articles on the basics of the same over here.

Advantages of Web Farm and Web Garden

Now, let’s have a look into the advantages of both the Web Farms and Web Gardens.

Advantages of Web Farm

It provides high availability. If any of the servers in the farm goes down, Load balancer can redirect the requests to other servers.
Provides high performance response for client requests.
Provides better scalability of the web application and reduces the failure of the application.
Session and other resources can be stored in a centralized location to access by all the servers.

Advantages of Web Garden

Provides better application availability by sharing requests between multiple worker process.
Web garden uses processor affinity where application can be swapped out based on preference and tag setting.
Less consumption of physical space for web garden configuration.

How to Manage Session in Web Farm Mode?

While using session, requests are distributed among different servers. By default, session mode is set to In Proc where session data is stored inside worker process memory. But, in Web farm mode, we can share the session among all the servers using a single session store location by making it Out proc (State Server or SQL Server Mode). So, if some of the servers go down and request is transferred to the other server by the Load balancer, session data should be available for that request.

In the above diagram, you can see that we can both the IIS server sharing the same session data which is stored in out of worker process. You can read one of my previous articles “Exploring Session in ASP.NET” where I have explained how you can configure session mode for Out Process mode.

How to Manage Session in Web Garden Mode?

When we are using Web garden where request is being taken care of by different worker process, we have to make the session mode as out process session mode as described earlier. For Web Garden, we have to configure the out process within the same server but for different worker process.

While using Web garden with your application, you need make a couple of configuration settings inweb.config in <process Model> section where you need to set certain properties like cpuMask,RequestLimit, webGarden, ClientConnectCheck, etc.

Summary

When we host a web application over multiple web servers to distribute the load among them, it is called Web Farm and when one application has multiple worker processes, it is called a Web garden.

In this blog post, I have explained the very basics of what a Web Farm is and what a Web Garden is. As this blog post contains the basic information to understand the fundamentals of web farms and web garden concept, I will be posting a separate article with details configuration setting for web garden and web farm. You can read the following articles for more information:

Hope this will help you.

SQL Server Interview Questions

SQL Server Interview Questions : Database

What is DBMS?
What is RDBMS?
What are the ACID properties?
What is Normalization?
What is D-normalization and when do we need it?
What are the different Normal Forms?

SQL Server Interview Questions: Table Design

What is Table? *
What is Data type? *
What are the different types of data types in SQL Server? *
What is the difference between Float, BIG Int and Integer? *
Explain the Decimal Data types and its uses. *
What is the use of different types of Integer data types? *
What is the difference between bit, Char and Varchar? *
What is Unicode? **
What is the difference between Varchar and Nvarchar? **
What are different date time data types? *
What are unique identifiers in SQL Server Table? **
What is check constraint in SQL Server? **
What is default constraint? *
What is Null ability? *
Explain table relationship? *
What is Primary Key? *
What is Foreign Key? *
What is composite key? *
What are Primary Key and Foreign Key Constraint? *
How to get the list of primary key and foreign key of the table? ****
Can we insert null value in primary column? *
What is difference between Primary Key and Unique Key? **
What is Identity Column in Table? *
Syntax to check current Identity of the table? *
What is the difference between Scope of Identity on @@identity? ***
Can we change identity key values for a table or reset the identity key value. ***
What is function Ident_INCR? ***
What is times stamp data type is SQL Server? **
What is the alternative of timestamp? **

SQL Server Interview Questions: Select and joins

What is select statement in TSQL? *
What is the Join in SQL Server? *
What are the different types of join? *
Difference between Left join and Outer Join? *
What is left outer join and right outer join? *
What is full outer join? *
What is inner join? *
What is cross join? *
Write one example for self-join? **
What is where clause? *
What are the sub queries? *
What are the nested queries in SQL Server? **
What is inline query? **
What are the aggregate functions in SQL Server? *
What is Group By? *
What is Having clause? **
What is difference between where and having clause? **
What is the table variable? **
What is the temporary table? **
What is the global temporary table? **
Difference between temporary table and table variable? **
What is the union all clause? **
What is union Clause? **
Difference between union all and union clause? **
What is the Ranking function in SQL Server? ****
Difference between rank and dense rank? ****
What is top operator in SQL Server? **
What is table sample in SQL Server? ***
What is the delete command? *
What is truncate command? **
Difference between delete and truncate? **
What are dynamic queries? ***
What is distinct clause? How it works? **
How to use Order by in Sub Queries? **
What is the Limit of small date time function? **
What is the @@error? **
What is the @@raise error? **
What is the @@row count? **
What is Date Diff function? **
What is Date Add Function? **
What is date part function? ***
What is coalesce function in SQL Server? ***
What is stuff function? ***
What is replacing function in SQL Server? ***
What are the sparse column and when we use it? ****

SQL Server Interview Questions: Views

What is the view in SQL Server? **
What are the updated views? ***
What are the materialized views or Indexed Views in SQL Server? ****
What is view with check option? ****
Write the Create syntax for Views. **
What is common table expression (CTE)? ***
What is difference between CTE and View? ****

SQL Server Interview Questions: Stored procedure

What is stored procedure in SQL Server? **
How to pass record set in stored procedure? ***
Is nesting possible in stored procedure? If yes How many number of times? ***
Write the create syntax for the procedure. **
How to return output parameter from stored procedure? ***
How to perform error handling in Stored procedure? ****

SQL Server Interview Questions: Function

What is the User defined functions? **
What are the difference types of UDF? **
What are difference between UDF and stored procedure? ***
Can we create a table in function? ****

SQL Server Interview Questions: Triggers

What is a trigger in SQL Server? **
What is the nesting level of triggers? **
What are the different types of triggers in SQL Server? ***

SQL Server Interview Questions: Cursors

What is the cursor in SQL server? **
What are the different types of cursor in SQL Server? ***
When to use Cursor in SQL Server? ***

SQL Server Interview Questions: Index

What is Index in SQL Server? ***
Explain different types of index in SQL Server? ***
What is filter index in SQL Server? ****
What is Covering Index? ****
What is index scan and index seek? ****
What is B+ tree index? ****
Explain the architecture of the index? ****
Explain the performance impact of index? ****
Can we create clustered index on null value column? ****
What are indexes in SQL Server? ****
How many clustered index can be created on the table? ****
How to enforce index on a query? ****

SQL Server Interview Questions: Performance tuning

What is SQL Profiler?
Explain the use of SQL Profiler.
What is Database Tuning Advisor?
What is Execution Plan?
How to Read Execution Plan?
What are the statistics and the impact on SQL Server?
What are the table hint and how we can provide it on table?
How to read execution plan?
What are the algorithms does SQL Server used to fetch data?
What is bookmark lookup?

SQL Server Interview Questions: Transaction

What are the different types of transaction errors?
What is No lock?
What happens if a transaction is failed in nested transaction, then other outer transactions will execute or not?
What is isolation level in SQL Server?
What are different types of isolation level in SQL Server?
What is default isolation Level for SQL Server?

SQL Server Interview Questions: Other Questions

Write a query to find the nth minimum and maximum.
Write a query to get the last record of the table.
Write a query to get the no rows of a table without using count or any other clause.
How many columns can be used with roll up or with cube command?
How many columns can be selected in a select statement?
How many columns can be selected in an insert statement?
What level of nesting is possible for sub Queries?
What level of nesting is possible for Triggers?
How many non-clustered indexes can be created on a table?
How many parameters can be provided to a stored procedure?
How many parameters can be provided to a UDF?
How many columns can be taken in update statement?
What is NO COUNT used for?
What is SET ANSI NULL ON used for?
What is arithmetic abort used for?
What is SET ROW COUNT used for?
What is SET ANSI padding?
What is SET ANSI WARNING on?
What is SET NO EXEC?
Which command using Query Analyzer will give you the version of SQL server and operating system?
How to delete duplicate records from table using query?

Add, Edit, update, Delete gridview

Here I as explaining how we can add new recored and edit of existing record and delete a record in Gridview.
Step1 : You can add this gridview in your aspx form.
Here some events of Gridview are fired:-
i) OnRowCancelingEdit
ii) OnRowDeleting
iii) OnRowEditing
iv) OnRowUpdating
Add this gridview in .aspx page
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="cat_id" ForeColor="#333333" GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField HeaderText="Edit-Update" ShowEditButton="True" />
<asp:BoundField DataField="cat_id" HeaderText="Category ID" ReadOnly="True" />
<asp:BoundField DataField="cat_name" HeaderText="Category Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
</Columns>
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
for binding gridveiw you have to write following code:
private void BindData()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
Step 2:
Write this procedure to add a new row.
as you click on button it will appear on top of gridview and you can add new record in database thru gridview.
protected void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);

// Here we'll add a blank row to the returned DataTable
DataRow dr = dt.NewRow();
dt.Rows.InsertAt(dr, 0);

//Creating the first row of GridView to be Editable
GridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();

//Changing the Text for Inserting a New Record
((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";
}
Step 3:
Suppose you change your mind and canel record to addthen you have to write following code:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}

Step 4:
if you are editing a row you want to some change in existing record then you have to write following code:
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
GridView1.EditIndex = -1;
BindData();
}
Step: 5
You wanna to add new record or you wanna add a new record then u can follow :
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) {if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert") {SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name) VALUES(@cat_name)";cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

GridView1.EditIndex = -1;
BindData();
}

Step 6:
If you wnat to delete particulare record from database in gridview then u can follow this:
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "DELETE FROM quest_categories WHERE cat_id=@cat_id";cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindData();
}

Introducing “Razor” – a new view engine for ASP.NET

One of the things my team has been working on has been a new view engine option for ASP.NET.
ASP.NET MVC has always supported the concept of “view engines” – which are the pluggable modules that implement different template syntax options. The “default” view engine for ASP.NET MVC today uses the same .aspx/.ascx/.master file templates as ASP.NET Web Forms. Other popular ASP.NET MVC view engines used today include Spark and NHaml.
The new view-engine option we’ve been working on is optimized around HTML generation using a code-focused templating approach. The codename for this new view engine is “Razor”, and we’ll be shipping the first public beta of it shortly.

Design Goals

We had several design goals in mind as we prototyped and evaluated “Razor”:

Compact, Expressive, and Fluid: Razor minimizes the number of characters and keystrokes required in a file, and enables a fast, fluid coding workflow. Unlike most template syntaxes, you do not need to interrupt your coding to explicitly denote server blocks within your HTML. The parser is smart enough to infer this from your code. This enables a really compact and expressive syntax which is clean, fast and fun to type.

Easy to Learn: Razor is easy to learn and enables you to quickly be productive with a minimum of concepts. You use all your existing language and HTML skills.

Is not a new language: We consciously chose not to create a new imperative language with Razor. Instead we wanted to enable developers to use their existing C#/VB (or other) language skills with Razor, and deliver a template markup syntax that enables an awesome HTML construction workflow with your language of choice.

Works with any Text Editor: Razor doesn’t require a specific tool and enables you to be productive in any plain old text editor (notepad works great).

Has great Intellisense: While Razor has been designed to not require a specific tool or code editor, it will have awesome statement completion support within Visual Studio. We’ll be updating Visual Studio 2010 and Visual Web Developer 2010 to have full editor intellisense for it.

Unit Testable: The new view engine implementation will support the ability to unit test views (without requiring a controller or web-server, and can be hosted in any unit test project – no special app-domain required).

We’ve spent the last few months building applications with it and doing lots of usability studies of it with a variety of volunteers (including several groups of non-.NET web developers). The feedback so far from people using it has been really great.

Choice and Flexibility

One of the best things about ASP.NET is that most things in it are pluggable. If you find something doesn’t work the way you want it to, you can swap it out for something else.
The next release of ASP.NET MVC will include a new “Add->View” dialog that makes it easy for you to choose the syntax you want to use when you create a new view template file. It will allow you to easily select any of of the available view engines you have installed on your machine – giving you the choice to use whichever view approach feels most natural to you:

Razor will be one of the view engine options we ship built-into ASP.NET MVC. All view helper methods and programming model features will be available with both Razor and the .ASPX view engine.
You’ll also be able to mix and match view templates written using multiple view-engines within a single application or site. For example, you could write some views using .aspx files, some with .cshtml or .vbhtml files (the file-extensions for Razor files – C# and VB respectively), and some with Spark or NHaml. You can also have a view template using one view-engine use a partial view template written in another. You’ll have full choice and flexibility.

Hello World Sample with Razor

Razor enables you to start with static HTML (or any textual content) and then make it dynamic by adding server code to it. One of the core design goals behind Razor is to make this coding process fluid, and to enable you to quickly integrate server code into your HTML markup with a minimum of keystrokes.
To see a quick example of this let’s create a simple “hello world” sample that outputs a message like so:

Building it with .ASPX Code Nuggets
If we were to build the above “hello world” sample using ASP.NET’s existing .ASPX markup syntax, we might write it using <%= %> blocks to indicate “code nuggets” within our HTML markup like so:

One observation to make about this “hello world” sample is that each code nugget block requires 5 characters (<%= %>) to denote the start and stop of the code sequence. Some of these characters (in particular the % key – which is center top on most keyboards) aren’t the easiest to touch-type.
Building it with Razor Syntax
You denote the start of a code block with Razor using a @ character. Unlike <% %> code nuggets, Razor does not require you to explicitly close the code-block:

The Razor parser has semantic knowledge of C#/VB code used within code-blocks – which is why we didn’t need to explicitly close the code blocks above. Razor was able to identify the above statements as self-contained code blocks, and implicitly closed them for us.
Even in this trivial “hello world” example we’ve managed to save ourselves 12 keystrokes over what we had to type before. The @ character is also easier to reach on the keyboard than the % character which makes it faster and more fluid to type.

Loops and Nested HTML Sample

Let’s look at another simple scenario where we want to list some products (and the price of each product beside it):

Building it with .ASPX Code Nuggets
If we were to implement this using ASP.NET’s existing .ASPX markup syntax, we might write the below code to dynamically generate a <ul> list with <li> items for each product inside it:

Building it with Razor Syntax
Below is how to generate the equivalent output using Razor:

Notice above how we started a “foreach” loop using the @ symbol, and then contained a line of HTML content with code blocks within it. Because the Razor parser understands the C# semantics in our code block, it was able to determine that the <li> content should be contained within the foreach and treated like content that should be looped. It also recognized that the trailing } terminated the foreach statement.
Razor was also smart enough to identify the @p.Name and @p.Price statements within the <li> element as server code – and execute them each time through the loop. Notice how Razor was smart enough to automatically close the @p.Name and @p.Price code blocks by inferring how the HTML and code is being used together.
The ability to code like this without having to add lots of open/close markers throughout your templates ends up making the whole coding process really fluid and fast.

If-Blocks and Multi-line Statements

Below are a few examples of other common scenarios:
If Statements
Like our foreach example above, you can embed content within if statements (or any other C# or VB language construct), without having to be explicit about the code block’s begin/end. For example:

Multi-line Statements
You can denote multiple lines of code by wrapping it within a @{ code } block like so:

Notice above how variables can span multiple server code blocks – the “message” variable defined within the multi-line @{ } block, for example, is also being used within the @message code block. This is conceptually the same as the <% %> and <%= %> syntax within .aspx markup files.
Multi-Token Statements
The @( ) syntax enables a code block to have multiple tokens. For example, we could re-write the above code to concatenate a string and the number together within a @( code ) block:

Integrating Content and Code

The Razor parser has a lot of language smarts built-into it – enabling you to rely on it to do the heavily lifting, as opposed to you having to explicitly do it yourself.
Does it break with email addresses and other usages of @ in HTML?
Razor’s language parser is clever enough in most cases to infer whether a @ character within a template is being used for code or static content. For example, below I’m using a @ character as part of an email address:

When parsing a file, Razor examines the content on the right-hand side of any @ character and attempts to determine whether it is C# code (if it is a CSHTML file) or VB code (if it is a VBHTML file) or whether it is just static content. The above code will output the following HTML (where the email address is output as static content and the @DateTime.Now is evaluated as code:

In cases where the content is valid as code as well (and you want to treat it as content), you can explicitly escape out @ characters by typing @@.
Identifying Nested Content
When nesting HTML content within an if/else, foreach or other block statement, you should look to wrap the inner content within an HTML or XML element to better identify that it is the beginning of a content block.
For example, below I’ve wrapped a multi-line content block (which includes a code-nugget) with a <span> element:

This will render the below content to the client – note that it includes the <span> tag:

You can optionally wrap nested content with a <text> block for cases where you have content that you want to render to the client without a wrapping tag:

The above code will render the below content to the client – note that it does not include any wrapping tag:

HTML Encoding
By default content emitted using a @ block is automatically HTML encoded to better protect against XSS attack scenarios.

Layout/MasterPage Scenarios – The Basics

It is important to have a consistent look and feel across all of the pages within your web-site/application. ASP.NET 2.0 introduced the concept of “master pages” which helps enable this when using .aspx based pages or templates. Razor also supports this concept using “layout pages” – which allow you to define a common site template, and then inherit its look and feel across all the views/pages on your site.
Simple Layout Example
Below is a simple example of a layout page – which we’ll save in a file called “SiteLayout.cshtml”. It can contain any static HTML content we want to include in it, as well as dynamic server code. We’ll then add a call to the “RenderBody()” helper method at the location in the template where we want to “fill in” specific body content for a requested URL:

We can then create a view template called “Home.cshtml” that contains only the content/code necessary to construct the specific body of a requested page, and which relies on the layout template for its outer content:

Notice above how we are explicitly setting the “LayoutPage” property in code within our Home.cshtml file. This indicates that we want to use the SiteLayout.cshtml template as the layout for this view. We could alternatively indicate the layout file we want to use within a ASP.NET MVC Controller invoking Home.cshtml as a view template, or by configuring it as the default layout to use for our site (in which case we can specify it in one file in our project and have all view templates pick it up automatically).
When we render Home.cshtml as a view-template, it will combine the content from the layout and sub-page and send the following content to the client:

Compact, Clean, Expressive Code

One of the things to notice in the code above is that the syntax for defining layouts and using them from views/pages is clean and minimal. The code screen-shots above of the SiteLayout.cshtml and Home.cshtml files contain literally all of the content in the two .cshtml files – there is no extra configuration or additional tags, no <%@ Page%> prefix, nor any other markup or properties that need to be set.
We are trying to keep the code you write compact, easy and fluid. We also want to enable anyone with a text editor to be able to open, edit and easily tweak/customize them. No code generation or intellisense required.

Layout/MasterPage Scenarios – Adding Section Overrides

Layout pages optionally support the ability to define different “sections” within them that view templates based on the layout can then override and “fill-in” with custom content. This enables you to easily override/fill-in discontinuous content regions within a layout page, and provides you with a lot of layout flexibility for your site.
For example, we could return to our SiteLayout.cshtml file and define two sections within our layout that the view templates within our site can optionally choose to fill-in. We’ll name these sections “menu” and “footer” – and indicate that they are optional (and not required) within our site by passing an optional=true parameter to the RenderSection() helper call (we are doing this using the new C# optional parameter syntax that I’ve previously blogged about).

Because these two sections are marked as “optional”, I’m not required to define them within my Home.cshtml file. My site will continue to work fine if they aren’t there.
Let’s go back into Home.cshtml, though, and define a custom Menu and Footer section for them. The below screenshot contains all of the content in Home.cshtml – there is nothing else required in the file. Note: I moved setting the LayoutPage to be a site wide setting – which is why it is no longer there.

Our custom “menu” and “footer” section overrides are being defined within named @section { } blocks within the file. We chose not to require you to wrap the “main/body” content within a section and instead to just keep it inline (which both saves keystrokes and enables you to easily add sections to your layout pages without having to go back through all your existing pages changing their syntax).
When we render Home.cshtml as a view-template again, it will now combine the content from the layout and sub-page, integrating the two new custom section overrides in it, and send down the following content to the client:

Encapsulation and Re-Use with HTML Helpers

We’ve covered how to maintain a consistent site-wide look and feel using layout pages. Let’s now look at how we can also create re-usable “HTML helpers” that enable us to cleanly encapsulate HTML generation functionality into libraries that we can re-use across our site – or even across multiple different sites.
Code Based HTML Helpers
ASP.NET MVC today has the concept of “HTML Helpers” – which are methods that can be invoked within code-blocks, and which encapsulate generating HTML. These are implemented using pure code today (typically as extension methods). All of the existing HTML extension methods built with ASP.NET MVC (both ones we’ve built and ones built by others) will work using the “Razor” view engine (no code changes required):

Declarative HTML Helpers
Generating HTML output using a code-only class approach works – but is not ideal.
One of the features we are looking to enable with Razor is an easy way to create re-usable HTML helpers using a more declarative approach. Our plan is to enable you to define reusable helpers using a @helper { } declarative syntax like below.

You’ll be able to place .cshtml files that contain these helpers into a Views\Helpers directory and then re-use them from any view or page in your site (no extra steps required):

Note above how our ProductListing() helper is able to define arguments and parameters. This enables you to pass any parameters you want to them (and take full advantage of existing languages features like optional parameters, nullable types, generics, etc). You’ll also get debugging support for them within Visual Studio.
Note: The @helper syntax won’t be in the first beta of Razor – but is something we hope will be enabled with the next drop. Code-based helpers will work with the first beta.
Passing Inline Templates as Parameters
One other useful (and extremely powerful) feature we are enabling with Razor is the ability to pass “inline template” parameters to helper methods. These “inline templates” can contain both HTML and code, and can be invoked on-demand by helper methods.
Below is an example of this feature in action using a “Grid” HTML Helper that renders a DataGrid to the client:

The Grid.Render() method call above is C#. We are using the new C# named parameter syntax to pass strongly-typed arguments to the Grid.Render method - which means we get full statement completion/intellisense and compile-time checking for the above syntax.
The “format” parameter we are passing when defining columns is an “inline template” – which contains both custom html and code, and which we can use to customize the format of the data. What is powerful about this is that the Grid helper can invoke our inline template as a delegate method, and invoke it as needed and as many times as it wants. In the scenario above it will call it each time it renders a row in the grid – and pass in the “item” that our template can use to display the appropriate response.
This capability will enable much richer HTML helper methods to be developed. You’ll be able to implement them using both a code approach (like the way you build extension methods today) as well as using the declarative @helper {} approach.

Visual Studio Support

As I mentioned earlier, one of our goals with Razor is to minimize typing, and enable it to be easily edited with nothing more than a basic text editor (notepad works great). We’ve kept the syntax clean, compact and simple to help enable that.
We have also designed Razor so that you get a rich code editing experience within Visual Studio. We will provide full HTML, JavaScript and C#/VB code intellisense within Razor based files:

Notice above how we are providing intellisense for a Product object on the “@p.” code embedded within the <li> element inside a foreach loop. Also notice how our \Views folder within the Solution Explorer contains both .aspx and .cshtml view templates. You can use multiple view engines within a single application – making it easy to choose whichever syntax feels best to you.

Summary

We think “Razor” provides a great new view-engine option that is streamlined for code-focused templating. It a coding workflow that is fast, expressive and fun. It’s syntax is compact and reduces typing – while at the same time improving the overall readability of your markup and code. It will be shipping as a built-in view engine with the next release of ASP.NET MVC. You can also drop standalone .cshtml/.vbhtml files into your application and run them as single-pages – which also enables you to take advantage of it within ASP.NET Web Forms applications as well.
The feedback from developers who have been trying it out the last few months has been extremely positive. We are going to be shipping the first public beta of it shortly, and are looking forward to your feedback on it.
Hope this helps,

Thursday, 19 June 2014

Exporting a report to multiple Excel tabs

Sometimes, SQL Server Reporting Services users like to see what could be considered multiple reports in a single SSRS report. In most cases we can easily accomplish this using more than one data region in the report. If not, we can always use a subreport.
Let's say we have a report called the Top 10 Report. In it, we wish to see the top ten products sold by order quantity. We'd also like to see the top 10 resellers of our products, again by order quantity. This could be accomplished using multiple data regions or using two subreports.
We'll use two table data regions for this demonstration. This is shown in the following figure.

Previewing the report produces the following results.

This generally works well. Our users can view the single report and examine the details from its two data regions. If we choose, we can use conditional formatting techniques to dynamically show or hide each data region. But I digress; back to our example.
When our Top 10 Report is exported into Microsoft Excel format, both data regions are exported to the same worksheet as shown in the figure below.

This causes some users angst. They wish each data region to be exported to a separate worksheet or tab.
We can easily do this. Examine the properties for the first table data region. You'll notice that there is a boolean property named PageBreakAtEnd. By default, this value is set to False. Changing its value to True will cause a page break to be inserted after the table.

A page break at the end of a data region will cause a new tab to be created when the report is exported to Microsoft. The first data region, the Top 10 Products Sold by Order Quantity, appears on the first tab.

The Top 10 Resellers by Purchase Quantity report appears on the second tab of the Excel workbook.

So using this technique we can effectively combine multiple reports in the one Reporting Services report, while giving our users the ability to export them to different tabs in Microsoft Excel.

Naming Excel sheets when exporting reports

New features of SSRS 2008 R2 - Part 1 Naming Excel sheets when exporting reports

posted 19/6/2014 by Narendra Kushwaha - Views: [50375]

This is the first of what I hope to be a long series of posts that demonstrate some new features of SQL Server Reporting Services in the 2008 R2 upcoming release. At some point I may define and go into more detail but for now, I simply want to expose you, the reader, to some new features in the next version of SQL Server Reporting Services (SSRS).
At the company that I work for, we have created a series of reports that have been enhanced over the years based on customer feedback. These reports were built on SQL Server 2005 while it was in beta. The application is still running in SQL 2005 and it uses all of the BI componants of SQL Server -
Enough of the background....
The ProblemOne of our enhancements was taking several reports that were very similar in nature and combining them into one report. The key requirement was that these reports need to export into seperate tabs when exported into Excel. This meant that the user could run one report that contained several pages of seperate reports where as before the user would have had to run the reports seperately. We were able to tell SSRS to create a new page for each grouping of the dataset and so we combined all of the seperate reports into one dataset and seperated the data by using these groups. This worked well with one problem. When you export the data to Excel it names the sheets "Sheet 1", "Sheet 2", etc... There was no way to control this nameing without really digging under the covers of the rendering engine and creating custom code. Newsgroups were full of posts enquiring on how to name sheets in Excel with no good answers.
There was hope... SQL Server 2008 was released... but still no solution. Denied!
The Solution
Back in November I got my first view of SSRS 2008 R2 Nov CTP release during a presentation at SQL PASS. Bob Meyers and Sean Boon did a great job of showing off many new features of SSRS - many of these will hopefully be bloged in the next parts of this series soon. At the end they did a quick demo and showed that in the R2 release we will have the ability to name the sheets for exports to Excel. This 90 second demo earned the applause of the entire room.
Here is how it is done:
I have created a boring and simple report using the Adventure Works database simply for the purpose of showing this feature.
This report simply pulls Sales Person data and groups it by Territory so that each territory has it's own page.
Here is an example of the report exported to Excel before setting the sheet names.

In order to name the Sheets appropriately all you have to do is select the Group that has the page breaks from the Row Groups shown below

Then in the properties window navigate into the Groups section and find the PageName attribute. By clicking on this attribute you can select the TerritoryName field from the dataset used to fill the report. This can be any field or expression that you want to use, but for my example I wanted to use the name of the territory as the name of the sheets.

Here is a screen of the final result - SIMPLE!!