Create a report with Reporting services

See the clip

What is Reporting Service?

Reporting service is a free application coming with sqlserver so if you have sql server in your organization then probably you have Reporting service as well. It is a repository for your reports you can create reports by some tools then publish your report to this repository then your applications including windows and web can send request to this repository and get the report in a browser. Since you can create complex report to show different charts and graphs, it is one of the handy tools to use specially in Intranet web applications. Also you can use reporting service for internet applications However, you will be charged for that.

What do you need to know about Reporting services?

1- How to create a report

2- How to publish a report

3- How to see the report in the server

4- How to access a report from other .net applications (Next post)

5- How to create a chart report (there is a sample in clip)

6- How to create a parameterized report (future posts)

7- How to pass parameters to a report from other applications (future posts)

How to create a report?

When you install sqlserver2005 you have a special template will be added to your visual studio which is "Sql server Business intelligence development studio". You can use this template to create a report then as I explained you have to publish your report then other applications can address that report. Open visual studio from file menu select New Project then you can select Business intelligence project. In this list you can find Report server project wizard or Report Server project. Using Wizard help you to speed up implementation of you report.

Let's select Report Server Project. As you see in solution explorer you have two folders one is shared data source and another one is Reports. You need a connection to Database in Reporting service we call that Data source. But if you have multiple reports over one data base you better create one Shared data source to share between them. Because if database changed then you need just change shared data source and everything should be all right. The reports folder is where you create your Reports. I recommend you to use wizard to create the basics of your report then change report to the way you like.

Create a share data source:

Right click on Shared Data sources folder and select Add new data source then you will see this window:

All you need to do provide an alias name for the shared data source and a correct connection string. The connection string implies to which data base shared data source can connect. You may click on Edit then you will see a familiar window to define a connection string. You can use windows or sql server authentication. Then click on ok. You are done. Now you have a shared data source that can be used in different reports in your project.

How to create a report?

All you need right click on Reports and select Add New Report then you will see the wizard window. As I said you better use wizard then customize your report the way you like later on. (I am going to take a report from a table that has suburbs, postcodes, states)

Click on next in next page select the shared data source as you see you can select a custom data source but I do not recommend this. Using shared data source is more maintainable.

In Next step you need to define your query in our case we are retrieving data from a table but you can retrieve data form view as well. If you take a look at the next picture you will see the query builder button when you go to that page in left top corner of window there is a button which is generic query designer then you can add the tables and views you want and define the query with query designer assistance.

In next step you need to define your report type in our case we want Tabular:

In next step you have the ability to define which fields you need to show in report also you can define group by to show details of one product (please see the clip to see a sample of this) right now we just choose the fields we want to have in our report.

Select a style for your Report and click on next you are done

You have three views: Data, Layout and Preview

In the Data view you can see and modify your query also you can add other queries you may use in the report (I will explain how to add another query for this report and why you may need to do that in future posts)

In layout you can change look and feel of your report or add other element please see your toolbox when you are in this view then you will see these elements (I will talk about some of these elements in next posts):

In the Preview tab you can see the result which is your report.

How to publish a report to reporting server

Next step is that you need to publish you report to Reporting Server to do this you need to right click on your project in solution explorer and select properties. Then you need define 4 properties at least:

TargetServerUrl: the reportserver address usually it is the same server as your sqlserver with report server virtual directory see the picture

TargetRootFolder: define in which folder the report should be deployed in your reporting server

TargetDatasourceFolder: define in which folder the data sources should be deployed in your reporting server

OverwriteDataSources: define whether you prefer to overwrite previous deployed data sources.

I highly recommend specify the same folder for TargetRootFolder and TargetDatasourceFolder because if you are getting Reporting services for an internet site from a shared reporting services in internet you will be offered just one folder so you need to have all of them in one folder.

Click on ok again right click on your project select deploy then after a while it will be deployed to the server.

How to check report in your server?

Go to your reporting service through a browser like this: http://yourserver/Reportingserver in my case I have to use http://falcon/Reporingserver then in that page find the folder in my case TestProjectReport then you will see all reports in that folder select your report in my case Report1

Then you will see the result.
The very power full point about Reporting service is that you can export data to different sources like pdf, Excel, Web services and etc.

Please see the clip to get some more information. Also you will learn how to create Chart reports.

See the clip

Using Ajax in Asp.Net

See the Clip

Download the Source Code

Before learning programming with Ajax It is necessary to understand Ajax and when it is worth full to use.

What is Ajax?
Ajax is a library to help you call methods of server side in client (browser) without refreshing the whole page but part of page. Let’s say you have list of countries as soon as user selects a country you need to show cities of that country in another dropdown. Imagine you have different elements in this page. What you need is just updating city dropdown. This is the best case to use Ajax since other part of page will stay in the page and users just feel that city dropdown is updated.

Ajax Help you to call server methods in client side without updating the whole page.

When using Ajax is worth full?
After working with Ajax a lot of programmers decide to use this technology a lot however, this is obviously a big mistake. Ajax should be used when implementing a functionality is completely impossible with javascript or massive data should be loaded to client. Let me give you an example if you need change data of a datagrid based on a dropdownlist it is not worth to write this code with javascript. If you need to just hide a part of data like a panel based on a check box it is not worth full to write this code with ajax.

There is a trade-off between using Ajax and JavaScript. Never use Ajax when you are able to implement the same functionality with JavaScript as long as you are not forced to load massive data to client.

How to create Ajax application?
If you are using visual studio 2005 you may need to download ajax library. If you are using visual studio 2008 you have ajax library. After installing ajax you can create Asp.net Ajax-Enabled Web site from file new Web site. If you take a look to this project you will understand that there are some codes being added in web.config to enable ajax also at least for default page you will see a tag which is ScriptManager.

Ajax Elements:
In each page that you need to use ajax technology you defiantly need a ScriptManger control which has no user interface but it mostly prepare ajax technology for that page. This control will add some library to browser and some javascript. It is important to put this control as the first control in html view in your page. Then you can use an update panel to place controls that are going to be updated between postback. For example let’s say we have a page with different content but a grid should be updated without posting back the whole page so we put that grid inside the Update Panel. If your task is time consuming you can use updateProgress control to show a picture identifying you are loading data. In this post I am going to talk about UpdatePanel. I will examine other controls in next posts.

A sample code:
In this sample I used the same product table was created for other samples of website which is a simple product table I need to show information of this table in a Gridview based on price. So when 200 is defined as price we need to show all products that are less than 200. Take a look to this picture. Then download the code and also see the clip.

.Net Remoting Activator Sample

download the Samaple Code

see the clip

In the previous lesson of Remoting I talked about remoting basics I examined a code sample. If you take a look to that post some people get back to me with the question how we can hide service from client process well this lesson is talk about how to hide Service from Client. First take a look to what we discussed in last lesson.

As you see in above diagram client application is talking to server application to create an object which will be created in server. This object metadata (definition) is inside service app. What client needs to talk to server application is a proxy object. This proxy object will be created based on one of the classes in service app. For example if you have a class called Test with one method called Dosth. The proxy object in client will be a class called Test with one method called Dosth. When this method is called proxy object path this call to the serve so in the server the real object will be created then the method in serve is called. So Server definitely needs the service app to understand the Type “Test”. The Issue here is that in reality in most of the projects we do not like to give client the service application because a clever client may open this library and then could be a threat to our business rule or security. But how client can create proxy object if no Type is available in client process?

See the movie please I explained more about this issue.

There are two ways to solve this issue:

1. You could create the same service app and change all code in new service app in a way that all methods even constructors throw an exception (I do not recommend this way since it is time consuming and there is no standardization. )


2. Using interface between client and server is another approach. It means that each class in service app inherits from an interface it means that we need extra application that has just interfaces. Client has this library from where it can find metadata to create the proxy object. Meanwhile, client is not able to see the service implementation. We are done, However, there is a big issue here: if we just give client an interface then the proxy object will be like interface how we are going to create an object from interface? Well we do not create proxy object. We use Activator class to create an object from server application but map that to the interface take a look to this picture
   

In above diagram as you see client just access to IService app that has just interface and interface does not have any implementation. And server has both of them. Server exposes Test object. Remember Test object is a type of ITest as well (because of inheritance) and client receive The test object but the way client look to Test object is ITest then when client call ITest methods the Test methods are called.

How to write activator code?
The coding that we had in previous example in client slightly changes take a look at this line of code. Let’s say I have a Hello class and IHello interface then the code in client is like this:

IHello h = (IHello)Activator.GetObject (typeof(IHello),"http://localhost:4000/Myuri");

Activator object create an object in server as you see and then map that type to interface. Please see the code and also the clip.

download the Samaple Code

see the clip

Multi tier Architecture utilities (NLOG)

see these Related topics:

1. Multi tier basics
2. A best practice for multi tier architecture
3. A sample code based on best practice
4. Examine utilities to speed up Multi tier programming- NLog

Please download the second version of Multi tier code

In previous lessons I talked about basic concepts of Multi tier and a best practice Architecture. Also I examine a sample code about that Architecture in this lesson I am going to review ”NLOG” one of the best tools that with no doubt you better use in that architecture to Log both errors and interaction with application.
What is NLOG?
NLOG is a free tool could be used to log into different resources like file, event viewer, database, Memory, Console, MSMQ, Mail and Web Service and etc. Meanwhile it can be configured to different level of log. Moreover, it can be switched off or on. Also you can have different source of log. Above of all it is open source application. Should you need any changes you will be able to make it.
How NLOG works?NLog gives you a class library to call its services. Also it has a configuration file called NLog.config where you set which log sources you need for instance file or database. Also you can configure the source for example for a file you probably configures the file name. You can have multiple sources. Take look at this sample:

this configuration file will be in UI application then other layers in Multi tier architecture do not need extra Nlog.config file all of them will use this configuration file. If you look at the logDirecotry in above code you will see that actually nlog will create a folder called Logs inside application folder and it will log data for each day. I just showed you logging in files. Log in other data sources like database has its own configuration variables.
It you look at the rules tag you will find two logger tags where you define each one of these logger
1- which class will represents them (“name”)
2- what is the minimum level of log also you can disable log by setting this value to debug for a release version of application (“minLevel”)
3- Which target this logger is using to log information

In a multi tier application we always put our logger in Common Layer because we need this library in other layers. Also I highly recommend you to write a rapper class to simplify using NLog. Take a look at the DataLogger code:

In above code simply two methods are written Info and Warn to log information. If you look at the parameters you see multiple parameters. To save these parameters an xml structure is being used.
Please see the next version of multi tier code with logging
I remind you that I used very simple logging here you may change it and adapt it to your requirements. So this is not a best practice however, it is a good practice using Nlog in your projects. Nlog is really a lot more than I show you here. If you need any extra information or you think it is worth to have more detail information about NLOG let me know and raise some comments here. Do not underestimate this fabulous tool. I believe it needs at least 20 posts to show its capability!
In next lesson I am going to talk about my favourite tool Code Smith to generate code. Please download the next version of the code.

Please download the second version of Multi tier code

sending configurable email in Asp.net (Best practice)

Sending email is very common in Asp.net application. In most of asp.net applications I was asked to send email to client or admin etc. Meanwhile, almost always I was asked to change content of email many times. Like change a sentence, put space between sentences. Sometimes to create email the way client needs I had to change email content 10 times. Finally I come up with the idea that I need to make this process configurable and allow clients change the content themselves easily. Another issue is that since email is dependent to other factors like mail server sometimes your application may not be successful to send email so you need detect unsuccessful emails and send email in later steps. This Post will talk about best practice to send email to client. please download the Code

Understanding the issue:

We are going to send email to users. We need these functionalities:

1- The Email content should be configurable

2- We may need to have a link in our email to somewhere in our site or in other sites

3- If sending email is not successful we need to log that email in database to be able to send email in future

You can send email in .net using System.Web.Mail.SmtpMail which is obsolete. Also you can use System.Net.Mail.SmtpClient I highly recommend the second one also try to use System.Net.MailMessage which is new class in .Net 2.0 unless you are using .net 1.1 By using SmtpClint class you can configure your smtp server information in web.config take a look at this code:

Quick look to class diagram:

I need to remind you that I did not use multi tier architecture here. I highly recommend that Accessing to database should be done through DataAccessLayer you can find more information about DataAccessLayer and multi tier programming in my blog.

EmailHelper, EmailLog, EmailLogProvider, DataAccessHelper:

UI will call EmailHelper class methods to send email in our case we have just one type of email if I had 10 different emails (of course with different content) I would have 10 methods to send Emails. All of those 10 methods would use SendEmail method (a private method) to send email. Also in this class there is another method called LogException used to Log the exception in database. Meanwhile, the email information is being saved so we can send email in later step to client. In this method an object of email log is being created then it is passed to EmailLogProvider Insert method to insert into database. DataAccessHelper is a utility class that you may use to add parameter to a command and to run a storedprocedures. In our example it is being used by EmailLogProvider class.

EmailReader, EmailLinkLoader:

When a method of EmailHelper is being called to send an email first a MailMessage object is created afterwards, it is passed to EmailReader class to prepare the body and other information. In this method we load information form an Xml file. So it is highly maintainable because as long as client gets back to us to change content of email we can apply those changes in this xml file not to our code. Take a look of this xml file.

As you see we even have "from emai"l and "from email display name" also as many as required bcc email can be configured. A very nice trick in this code is that if you look closer to the xml file you will find $firstName$ . This is a key for our code and we will replace this value in EmailLoader.LoadRegistration .So the email can have dynamic information for example another good example is that you may want to send username and password to a user but this information may vary from one user to another user so we use other keys and we replace this information in our code exactly the same way for $firstName$. In most of the sites that I worked I had more than one email also many of them had some special links like Terms and Condition link that should be placed into email. A good practice always suggests that never put a link hard coded as long as you have more than one email content connecting to the same link. So imagine you have 5 email and all of them somewhere in their content have a link to Terms and condition. You better make this link configurable because when this link changes you need change just one configuration rather than looking for this link in all emails. So EmailLinkLoader actually load another xml file and find the link address then cache this information. So next time it does not need to look up link again. This link information is passed to EmailReader to be applied to body content.

Important (how to run the code)

Open Visual studio and form file menu select open web site and form file system select the project folder. Afterwards you can run the project. You need to apply smtpserver address. A good test is without applying smtpserver if you run application then application ask your first Name and your email address. Afterwards since sending email is not successful the email information is saved into database. And it shows you unsuccessful message. In later steps we can use EmailLogProvider class and load all EmailLog objects so we can retry and resend email.

please download the Code

Multi Tier Sample Code (Best Practice)

see these Related topics:

1. Multi tier basics
2. A best practice for multi tier architecture
3. A sample code based on best practice
4. Examine utilities to speed up Multi tier programming- NLog

In previous lessons I discussed about different layers and now I am going to Show you a sample code and discus what goes where.

We are going to create a web application to show different products and add products. See the database definition:

First open visual studio then create an empty solution afterwards add these projects to the Solution:

Common Layer (A class library)
In this layer we are going to define business entities and interfaces. Also sometimes we may define enumerations since it should be understandable for all other layers. We may want log information so having a logger class here could be useful serving all layers. The best tool for logging information and bugs is NLog (I will explain how to work with NLog in future posts). We also need a serviceFacotry class to create proper object of Facade layer. If you look at the architecture (previous lesson) you will see that UIProvider layer connect to facade layer but we may need to change this facade layer to a webservice so creating object from this layer should be configurable. One way is that a service factory can be used another way is using Spring. I will talk about Spring in future posts. Please look at the sample code.

Let's look at the code in ServiceFactory class:

using System;

using System.Collections.Generic;

using System.Text;

using MultiTier.Common.ServiceInterface;

using MultiTier.Common.BusinessEntities;

using System.Configuration;

using System.Reflection;

using System.Web;

namespace MultiTier.Common

{

public class ServiceFactory

{

private static Assembly asm;

public static IProductService GetProductService()

{

if (asm == null)

{

//Load Assembly

string AssemblyName = ConfigurationManager.AppSettings["Assembly"];

string asmPath = HttpContext.Current.Server.MapPath("~/bin/" + AssemblyName);

asm = Assembly.LoadFile(asmPath);

if (asm == null)

throw new Exception("Assembly could not be found: " + AssemblyName);

}

string className = ConfigurationManager.AppSettings["Product"];

IProductService obj = asm.CreateInstance(className) as IProductService;

if (obj == null)

throw new Exception("class could not be created: " + className);

return obj;

}

}

}

There are different ways of implementing ServiceFactory. This implementation is the simplest way but not the efficient way. I would say using spring is the best approach. Firstly a proper assembly is loaded then a new product object is created afterwards it is casted to the IProductService interface finally object is returned as IProductService. What you need to know is that we are using Reflection to load assembly and to create productService object. The address of assembly and the class name that we need to create object is configured in configuration file (in our case this configuration is web.config in UI project)

Data Access Layer (a class library)

The main purpose of this layer is to save and retrieve data from database. So there is a class named "ProductDal" gets data from database and map the data to the business entity. And send it back to BL layer. In ProductDal some stored procedures are called and a helper class is created to add parameter for each stored procedure. (Please download the source code)

Business Layer (a class library)

In This business layer we do not have any special business but let me give you an example if a user select bunch of products to buy then in this layer the price should be calculated. And then some code will be written here to withdraw his account.

Facade Layer (a class library)

The responsibility of this layer is that make it easier for UI to connect to this layer without understanding the complexity of backend. Let me give you an example. In this layer we may add some code to log accessing to data also we may check security etc. Please see the code.

UIProvider Layer (a class library)

What you need in UI may different in terms of object entities so you need add some modification or even create new objects to bind to UI elements. Moreover, you can test this layer so you know what is binding to UI. Best practice suggests that we should test this layer to sure about data which is going to be bind to UI. One of the best tools for testing is NUnit that I am going to talk about NUnit in next lessons.

UI(a web application)

Main purpose of this layer is showing information to user and getting information from user. In our case this layer is a web application. As you see I applied a css (some free template form dear Google search). There is a Master page and also two pages, one page to add Product and another to see list of products. In both pages I used object data bindings. Please get the code.

In next Lesson I am going to talk about NLog, Spring, NUnit and my favourite tool "code smith" to generate code automatically. I am sure you came up with the idea that this is really time consuming and you need something to speed up your implementation. Code smith helps you to generate code automatically and fast.

Important: After getting the code you need to change the connection string (It depends on where you put the sample code but if you look at the sample code there is a database that is in web.config you need to specify address of this file)

.Net Remoting Sample

DownLoad The Source Code

See Clip

Remoting is a technology that helps you to communicate between different applications regardless of whether they reside on the same computer or on different computers. Even these applications can have different operation systems.

Remoting needs:

1. A server object that expose the functionality to callers outside its boundary

2. A client that makes calls to the server object

3. A transportation mechanism to pass the calls from one end to the other

.Net Remoting system provides a number of services:

1. to activate objects

2. control the lifetime of each object

3. Transport message to and from remote objects using communication channels from client application to server application.

All objects in .Net divide to 2 groups

1. Remotable objects

2. NonRemotable objects

Remotable Objects:

There are two types of Remotable objects:

1. Marshal by Value objects: when these objects are created actually a copy of these objects will be send to client so actually object is sent to client. In .Net if you have a class that is serializable then it is marshal by value. To make a class serializable you should just add an attribute to the class [Serializable].

2. Marshal by Reference objects: When these objects are created they are actually stay in Server and client will connect to these objects. So a reference to server object exists in the client. In fact client application has another object which is called proxy. Proxy object is like server object. However, when proxy method is called it will pass that request to server object. Still client assumes that it is working with real object. In .Net classes that are inherited from MarshalByRefObject are in this category.

Other objects apart from these two categories are NonRemotable.

In .net Remoting marshal value objects are simple one because they are in context of client. Sometimes you cannot send copy of object to client and you need client call server object like applying business rule. Generally data objects (like dataset or business entities) have to be marshal by value and business objects have to be marshal by reference.

In .net Remoting Marshal by reference objects are more complicated since these objects are created in server. There are some concerns about them for instance When objects are created in server, When objects will be deleted, how long objects can leave in server so on so forth.

So let's have a look to marshal by reference concept and see different types of marshal by ref objects.

Activation

Before an object instance of a remotable type can be access it must be created and initialized by a process known as activation.

Different types of marshal by ref objects:

1- Server activation (in .net Remoting code it is called Wellknown)

2- Client activation (in .net Remoting code it is called Activated)

There are two types of server activation

1- Singleton: it means that there is just one object in server serving all clients and all requests. (if you understand web applications this kind of objects are like Application object in web)

2- SingleCall :it means that for each call from each client an object will be created and afterwards that object will be deleted immediately. (it is like webforms in web)

Client activation: for each instance of client and each instance that client create from server object there is a separate object in server (like session in web)

What is passed in Remoting between server and client?

1- URI : When Remoting activate an object give that object a URI to reference for all requests. For server activated the URI is in server application. On the other hand, for client activated Remoting will generate a GUID for the URI and maps it to remote object. So for client activation it is handled by Remoting.

2- Metadata: Remoting has to pass type information (it means it has to pass type of class)

3- Channel Information: Remoting needs to know from which channel can access the object.

Basics for coding .net Remoting

In .Net Remoting we have different types of channels (I will get back to this concept in next lessons) as you know in .Net Remoting we need a server application and a client application and a channel we usually register channel in server. There is a class named Remotingconfiguration that we use to register objects. This class has 4 type of Register

1-RegisterActivatedClientType

2-RegisterActivatedServiceType

3-RegisterWellKnownClientType

4-RegisterWellKnownServiceType

In these methods Activated means client activation and WellKnown means server activation and Service implies you are registering in server and Client implies you are registering in Client application

Time to Write Remoting code (Download the source Code)

Please follow these steps to write a Remoting code:

1- Create a class library name it Service : in class library you just have a class named Test

namespace Service

{

public class Test:MarshalByRefObject

{

public Test()

{

Console.WriteLine("New Object!");

}

public string PrintYourAddress()

{

string myaddress = AppDomain.CurrentDomain.BaseDirectory;

Console.WriteLine("This is myaddress " + myaddress);

return myaddress;

}

public void WriteInServer()

{

Console.WriteLine("I wrote time in server " + DateTime.Now.ToShortTimeString());

}

}

}

2- Create a console application name it Server: you have to write some code in Main method. You need to add System.Runtime.Remoting to your references. Furthermore you need to add Service library to this project.

using System;

using System.Collections.Generic;

using System.Text;

using System.Runtime.Remoting;

using System.Runtime.Remoting.Channels;

using System.Runtime.Remoting.Channels.Http;

namespace Server

{

class Program

{

static void Main(string[] args)

{

HttpChannel ch = new HttpChannel(4000);

ChannelServices.RegisterChannel(ch);

//because this is written in server so we need to use Service type methods

RemotingConfiguration.RegisterWellKnownServiceType(typeof(Service.Test), "ServiceURI", WellKnownObjectMode.Singleton);

Console.WriteLine("Server is Started...");

Console.ReadLine();

}

}

}

3- Create a windows application name it Client: First of all you need to add reference service. Add two buttons and in button clicks add these code also you need to add some code in FormLoad event

Test t;

private void Form1_Load(object sender, EventArgs e)

{

System.Runtime.Remoting.RemotingConfiguration.RegisterWellKnownClientType(typeof(Test), "http://localhost:4000/ServiceURI");

t= new Test();

}

private void button1_Click(object sender, EventArgs e)

{

t.WriteInServer();

}

private void button2_Click(object sender, EventArgs e)

{

string str = t.PrintYourAddress();

MessageBox.Show(str);

}

4- First run client application if you click on any of buttons you should see an error because the server is not running. So you need run server then client that should be fine for more detail information please see the movie

See Clip

Learn more about .Net Remoting (Activator sample)