URL Rewriting in ASP.NET using URLRewriter.Net

Friendly URLs have become necessary to obtain better rankings in Search engines. The improvement in rankings may not be huge but because of the burgeoning of websites in almost every niche we need to use every single advantage that we can get. Friendly URLs are URLS without a query sting or without the “?” character. Friendly URLs have a URL structure which look like static pages instead of dynamic pages.

Unfriendly URL : “www.MyShop.com/ShowProduct.aspx?ProductID=1
Friendly URL : “www.MyShop.com/Monitors/Samsung-Syncmaster.aspx

The absence of a good, built in URL rewriting solution in ASP.NET makes it difficut to implement friendly URLs in dynamic pages. The only type of URL rewriting available in ASP.NET can be used only for small websites with a few pages since we need to have a rule for each page. The following is an example of URL rewriting which can be done in the web.config file

<system.web>
<urlMappings enabled="true">
<add
url="~/Clubs/Computer Club.aspx"
mappedUrl="~/ShowPage.aspx?PageID=1" />
<add
url="~/Clubs/Eco Club.aspx"
mappedUrl="~/ShowPage.aspx?PageID=2" />
</urlMappings >
</system.web>

You can easily see that a website with even a fairly large number of pages can become difficult to maintain. Adding or deleting of pages will also require the web.cong file to be modified. This is a tedious solution and certainly not suitable for medium to large dynamic sites.

The guys at URLRewriter.NET have made a very useful URL Rewriting component . The best thing about it is that it is very simple to implement and it works in a shared hosting environment. All you have to do is download the dll file and copy it to your bin directory. Add a few lines in your web.config file as follows.


<configSections>
<section name="rewriter"
requirePermission="false"
type="Intelligencia.UrlRewriter.Configuration.RewriterConfigurationSectionHandler, Intelligencia.UrlRewriter" />
</configSections>
<system.web>
<httpModules>
<add name="UrlRewriter" type="Intelligencia.UrlRewriter.RewriterHttpModule, Intelligencia.UrlRewriter"/>
</httpModules>
</system.web>

Now you will have to define the rules in your web.config file

An example of a rule is as follows

<rewriter>
<rewrite url = "~/Products/([^/.]+)\.aspx” to=”~/ListProducts.aspx?Category=$1″ />
<rewrite url = “~/Products/(.+)/([0-9]+)-(.+).aspx” to=”~/ShowProduct.aspx?ProductName=$2″ />
</rewriter>

According to the the first rule “~/Products/Monitors.aspx” will be rewritten as “~/ListProducts.aspx?Category=Monitors”
According to the second rule “~/Products/Monitors/Samsung940BW.aspx” will be rewritten as “~/ShowProduct.aspx?ProductName=Samsung940BW”

Thus now you can use the friendly URLS instead of the URLs with query strings. It’s simple as that !

After using URLRewrite.NET , I had noticed a major bug. The bug is not in the component but with ASP.NET URL rewriting itself. All the pages using URL Rewriting do not get indexed by Google, this happens for both the built in URL rewriting and URLRewriter.NET component. Google bots cannot crawl pages which use Rewriting. It gives an error “Network unreachable” . This is a major problem for websites which depend on search engine traffic and almost all websites do.

After searching around a lot , I found a solution . The solution is to create a file name “genericmozilla5.browser” under the “App_Browsers” Directory in your root directory. Type in the following in the file

<browsers>
<browser id="GenericMozilla5" parentID="Mozilla">
<identification>
<userAgent match="Mozilla/5\.(?'minor'\d+).*[C|c]ompatible; ?(?’browser’.+); ?\+?(http://.+)\)” />
</identification>
<capabilities>
<capability name=”majorversion” value=”5″ />
<capability name=”minorversion” value=”${minor}” />
<capability name=”browser” value=”${browser}” />
<capability name=”Version” value=”5.${minor}” />
<capability name=”activexcontrols” value=”true” />
<capability name=”backgroundsounds” value=”true” />
<capability name=”cookies” value=”true” />
<capability name=”css1″ value=”true” />
<capability name=”css2″ value=”true” />
<capability name=”ecmascriptversion” value=”1.2″ />
<capability name=”frames” value=”true” />
<capability name=”javaapplets” value=”true” />
<capability name=”javascript” value=”true” />
<capability name=”jscriptversion” value=”5.0″ />
<capability name=”supportsCallback” value=”true” />
<capability name=”supportsFileUpload” value=”true” />
<capability name=”supportsMultilineTextBoxDisplay” value=”true” />
<capability name=”supportsMaintainScrollPositionOnPostback” value=”true” />
<capability name=”supportsVCard” value=”true” />
<capability name=”supportsXmlHttp” value=”true” />
<capability name=”tables” value=”true” />
<capability name=”vbscript” value=”true” />
<capability name=”w3cdomversion” value=”1.0″ />
<capability name=”xml” value=”true” />
<capability name=”tagwriter” value=”System.Web.UI.HtmlTextWriter” />
</capabilities>
</browser>
</browsers>

LinQ - left outer join with multi condition

Hi..
I find a post about the problem : http://forums.asp.net/t/1209451.aspx

SELECT a.c1,b.c2,b.c3
FROM t1 a LEFT OUTER JOIN t2 b
ON a.c1=b.c2 AND b.c3='n'


var o = from p in db.t1
join p2 in db.t2 on new { f1 = p.c1, f2 = "n" } equals new { f1 = p2.c2, f2 = p2.c3 } into temp
from x in temp.DefaultIfEmpty()
select new { f1 = p.c1, f2 = x.c2, f3 = x.c3 };

Spice Up Your Ad Reports - Using the ASP.NET Repeater Control to Group Data and Add Totals

Spice Up Your Ad Reports - Using the ASP.NET Repeater Control to Group Data and Add Totals

The Repeater control is truly amazing. Being template driven, it gives you a lot of flexibility to manipulate and render the final output. In this article, we will exploit this flexibility of the ASP.NET Repeater and build a sample to demonstrate how to use the templates to group and display data. We will also use a little code to ‘total’/sum up our data and display it on the fly. At the end, we will apply css to design a beautiful report from the Repeater control. I have borrowed the css from the blog of a great designer guy called Veerle

I have chosen to take the example of displaying ‘advertisement’ data through the Repeater control. For simplicity sake, I will assume two tables related to website advertisement and then show you how to use the template driven Repeater control to display and group advertisement data and add a total at runtime. I assume you have used the Repeater control and are familiar with it. So here we go…

Database Script

This is a sample db script on which I will base my demonstration. Please note that this script in no way portrays a good DB design. I have kept the design simple so as to keep the focus more on the Repeater control, rather than turning this article into ‘DB Normalization Best Practices’ one. The database of our sample application is called ‘MYWEBSITE’ and the two tables are ‘Ads’ and ‘AdsStats’

Sample Schema

CREATE DATABASE MYWEBSITE

GO

USE [MYWEBSITE]

GO

CREATE TABLE [dbo].[Ads](

[AdId] [int] IDENTITY(1,1) NOT NULL,

[AdName] [nvarchar](100) NULL,

[Comments] [nvarchar](40) NULL

) ON [PRIMARY]

GO

USE [MYWEBSITE]

GO

CREATE TABLE [dbo].[AdsStats](

[Id] [int] IDENTITY(1,1) NOT NULL,

[AdId] [int] NOT NULL,

[AdDate] [datetime] NULL,

[AdType] [int] NULL

) ON [PRIMARY]

GO

Sample Data

INSERT INTO [Ads] VALUES('AD1','Leaderboard');

INSERT INTO [Ads] VALUES('AD2','Banner');

INSERT INTO [Ads] VALUES('AD3','TextAds');

INSERT INTO [AdsStats] VALUES(1,'2008-09-23 04:47:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-23 06:17:33.000',0);

INSERT INTO [AdsStats] VALUES(2,'2008-09-23 07:42:33.000',0);

INSERT INTO [AdsStats] VALUES(2,'2008-09-23 08:32:33.000',1);

INSERT INTO [AdsStats] VALUES(3,'2008-09-23 08:41:33.000',1);

INSERT INTO [AdsStats] VALUES(1,'2008-09-23 19:27:33.000',0);

INSERT INTO [AdsStats] VALUES(2,'2008-09-23 19:37:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-23 19:47:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-24 01:47:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-24 02:17:33.000',1);

INSERT INTO [AdsStats] VALUES(3,'2008-09-24 03:22:33.000',0);

INSERT INTO [AdsStats] VALUES(2,'2008-09-24 04:32:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-24 05:11:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-25 02:27:33.000',0);

INSERT INTO [AdsStats] VALUES(3,'2008-09-25 03:47:33.000',0);

INSERT INTO [AdsStats] VALUES(2,'2008-09-25 04:17:33.000',0);

INSERT INTO [AdsStats] VALUES(3,'2008-09-25 05:12:33.000',1);

INSERT INTO [AdsStats] VALUES(2,'2008-09-25 05:32:33.000',1);

INSERT INTO [AdsStats] VALUES(3,'2008-09-25 06:41:33.000',0);

INSERT INTO [AdsStats] VALUES(1,'2008-09-25 07:47:33.000',0);

Now with the sample schema and data ready, we will move ahead to design our page that will group and display the advertisement data kept in these two tables. To understand what the data is about and what we will be doing with it, here’s a brief up. We will assume that there are 3 different kinds of ads our sample ‘MyWebSite’ serves : Leaderboard, Banner and TextAds. The ‘AdType’ column in AdsStats table displays the impressions and clicks that an advertisement receives. 0 means Impressions and 1 means a Click.

Our purpose is to display a daily report; that is to group the advertisement data based on date. We will also add a total at the end of each date, thereby displaying how many impressions and clicks were received from the different ads displayed, per day. I hope the case scenario is clear. If it isn’t, just wait till the end of this article and it will be. Let us now implement the functionality.

Create an ASP.NET website. Add a web.config file to the website to store the connection string as shown below:

<connectionStrings>

<remove name="AdConnString"/>

<add name="AdConnString" connectionString="Data Source=SUPROTIM; Initial Catalog=MYWEBSITE; Integrated Security = True; "providerName="System.Data.SqlClient"/>

connectionStrings>

To display a group wise data, we will have to use Nested Repeaters. So our parent repeater will contain a label(lblDate) control to display the Date (for grouping). Based on this grouping, we will then pull related data in our child repeater. The Nested child repeater will be contained in the of the Parent Repeater to replicate a display of hierarchical data.

SQL Queries:

The Repeaters will be bound to two different SQLDataSources. The queries for the two data sources are as follows:

Query for Parent Repeater - The Parent Repeater will display the date, to group the data. The query for it will look similar to the following:

SELECT DISTINCT CONVERT(VARCHAR(12), AdDate, 106) as AdDate

FROM AdsStats ORDER BY AdDate

As shown above, the query selects distinct date from the AdsStats table. Running the query displays the following result:

Query for Child Repeater – The Child Repeater will display the daily advertisement data. This data will be grouped based on the date in our Parent Repeater control. The query for displaying data in the child repeater will look similar to the following:

SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments,

SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression,

SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks

FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId

GROUP BY DATEADD(dd,DATEDIFF(dd,0,AdDate),0), al.Comments

Running the query will display the following result:

With our queries ready, we now need to add markup to display the data. The markup for grouping and displaying data is as shown below. I have also added css to beautify it:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title>My Ad Reportstitle>

<style type="text/css">

body

{

font: normal 11px auto "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;

background-color: #ffffff;

color: #4f6b72;

}

#mytable

{

width: 700px;

padding: 10px;

margin: 10px;

}

th {

font: bold 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;

color: #4f6b72;

border-right: 1px solid #C1DAD7;

border-bottom: 1px solid #C1DAD7;

border-top: 1px solid #C1DAD7;

letter-spacing: 2px;

text-transform: uppercase;

text-align: left;

padding: 6px 6px 6px 12px;

background: #D5EDEF;

}

td {

border-right: 1px solid #C1DAD7;

border-bottom: 1px solid #C1DAD7;

background: #fff;

padding: 6px 6px 6px 12px;

color: #4f6b72;

}

td.alt

{

background: #F5FAFA;

color: #797268;

}

td.boldtd

{

font: bold 13px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;

background: #D5EDEF;

color: #797268;

}

style>

head>

<body style="background-color:White">

<form id="form1" runat="server">

<div>

<h3>Daily Activity Reportsh3><br />

<asp:Repeater ID="ParentRepeater" runat="server" DataSourceID="SqlDataSource1">

<ItemTemplate>

<p style="margin-left:10px"><b><asp:Label ID="lblDate" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "AdDate", "{0: dd MMM}")%>'>asp:Label>b>p>

<asp:Repeater ID="ChildRepeater" runat="server" DataSourceID="SqlDataSource2">

<HeaderTemplate>

<table id="mytable">

<tr>

<th>Banner Typeth>

<th>Impressionsth>

<th>Clicksth>

tr>

HeaderTemplate>

<ItemTemplate>

<tr>

<td>

<%# Eval("AdComments")%>td>

<td> <%# Eval("AdImpression")%> td>

<td> <%# Eval("AdClicks")%>td>

tr>

ItemTemplate>

<AlternatingItemTemplate>

<tr>

<td class="alt">

<%# Eval("AdComments")%>td>

<td class="alt"> <%# Eval("AdImpression")%> td>

<td class="alt"> <%# Eval("AdClicks")%>td>

tr>

AlternatingItemTemplate>

<FooterTemplate>

table><br />

FooterTemplate>

asp:Repeater>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"

SelectCommand="SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments, SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression, SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId WHERE AdDate >= DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 0) and AdDate

<SelectParameters>

<asp:ControlParameter ControlID="lblDate" Name="AdDate" />

SelectParameters>asp:SqlDataSource>

ItemTemplate>

asp:Repeater>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"

SelectCommand="SELECT DISTINCT CONVERT(VARCHAR(12), AdDate, 106) as AdDate FROM AdsStats ORDER BY AdDate">asp:SqlDataSource>

div>

<br />

form>

body>

html>

The parent repeater contains a label control (lblDate). The lblDate gets its value from the SqlDataSource1 using DataBinder.Eval as shown below:

<asp:Label ID="lblDate" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "AdDate", "{0: dd MMM}")%>'>asp:Label>

Note that the query for the child repeater has been modified to accept the date parameter(@AdDate). This parameter takes its value from the label control (lblDate) kept in the Parent Repeater.

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdConnString %>"

SelectCommand="SELECT DATEADD(dd,DATEDIFF(dd,0,ast.AdDate),0) as AdDate,al.Comments as AdComments, SUM(CASE WHEN AdType=0 THEN 1 ELSE 0 END) AS AdImpression, SUM(CASE WHEN AdType=1 THEN 1 ELSE 0 END) AS AdClicks FROM Ads al INNER JOIN AdsStats ast ON ast.AdId =al.AdId WHERE AdDate >= DATEADD(dd, DATEDIFF(dd, 0, @AdDate), 0) and AdDate

<SelectParameters>

<asp:ControlParameter ControlID="lblDate" Name="AdDate" />

SelectParameters>

This is more or less like displaying data in a hierarchy, where the ‘Date’ is the parent and data related to that ‘Date’ becomes the child data.

The in the Child Repeater displays the headings for the columns. The and displays the advertisement data and receive the values from SQLDataSource2.

Generate a ‘Total’ in the Repeater control at runtime

The last step is to add a running total to display the total number of impressions and clicks received from all the 3 different advertisements on that particular day. To do so, we will modify the of the child repeater control to add two label controls to display a total of the Impressions and Clicks.

<FooterTemplate>

<tr class="boldtr" >

<td class="boldtd">Totalstd>

<td class="boldtd">

<asp:Label ID="lblImpressionCount" runat="server" Text="">asp:Label>td>

<td class="boldtd"><asp:Label ID="lblClickCount" runat="server" Text="">asp:Label>td>

tr>

table><br />

FooterTemplate>

We will also add the ItemDataBound event handler to the ChildRepeater

<asp:Repeater ID="ChildRepeater" runat="server" DataSourceID="SqlDataSource2" OnItemDataBound="ChildRepeater_ItemDataBound">

We need to add some code to the ItemDataBound event of the Child repeater to calculate the totals. As a note, the ItemDataBound event of the Repeater control is raised when an item is being data-bound, just before the item gets rendered. This gives us an ideal location to calculate our Total on the fly before the data is rendered. The code contains two class level variables cntView and cntClick which are calculated when the ListItemType is either an Item or an AlternatingItem. Finally when we reach the Footer, the data is displayed in the two label controls (lblImpressionCount and lblClickCount). Once displayed, the variables are reset to 0. This operation is repeated for each day (group) in the AdsStats table. Use the following code:

C#

Int64 cntView;

Int64 cntClick;

public void ChildRepeater_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)

{

ListItemType lt = e.Item.ItemType;

if (lt == ListItemType.Item || lt == ListItemType.AlternatingItem)

{

DataRowView dv = e.Item.DataItem as DataRowView;

if (dv != null)

{

cntView += Convert.ToInt64(dv["AdImpression"]);

cntClick += Convert.ToInt64(dv["AdClicks"]);

}

}

else if (e.Item.ItemType == ListItemType.Footer)

{

Label lblCount = e.Item.FindControl("lblImpressionCount") as Label;

lblCount.Text = cntView.ToString();

Label lblSum = e.Item.FindControl("lblClickCount") as Label;

lblSum.Text = cntClick.ToString();

cntView = 0;

cntClick = 0;

}

}

VB.NET

Private cntView As Int64

Private cntClick As Int64

Public Sub ChildRepeater_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)

Dim lt As ListItemType = e.Item.ItemType

If lt = ListItemType.Item OrElse lt = ListItemType.AlternatingItem Then

Dim dv As DataRowView = TryCast(e.Item.DataItem, DataRowView)

If Not dv Is Nothing Then

cntView += Convert.ToInt64(dv("AdImpression"))

cntClick += Convert.ToInt64(dv("AdClicks"))

End If

ElseIf e.Item.ItemType = ListItemType.Footer Then

Dim lblCount As Label = TryCast(e.Item.FindControl("lblImpressionCount"), Label)

lblCount.Text = cntView.ToString()

Dim lblSum As Label = TryCast(e.Item.FindControl("lblClickCount"), Label)

lblSum.Text = cntClick.ToString()

cntView = 0

cntClick = 0

End If

End Sub

That’s it. A template based, beautifully designed Advertisement Report is ready, all thanks to the wonderful and powerful ASP.NET Repeater control and the nice css designed by Veerle.

When you run the report, the data will be grouped date wise, with a total at the end of each day as shown below:

I hope this article was useful and I thank you for viewing it. The source code of the article in both C# and VB.NET can be downloaded over here.