.Net and Sql BI: 2011

Monday, November 28, 2011

Invalid postback or callback argument. Event validation is enabled using

Any one Find out

Friday, November 25, 2011

Remove Whitespace from C# Strings

You probably knew that you can use the String.Trim method to remove whitespace from the start and end of a C# string. Unfortunately, the Trim method does not remove whitespace from the middle of a string.

Given this example:

string text = "  My testnstringrn ist quite long  ";
string trim = text.Trim();

The ‘trim’ string will be:

“My testnstringrn ist quite long” (31 characters)

Another approach is to use the String.Replace method, but that requires you to remove each individual whitespace character via multiple method calls:

string trim = text.Replace( " ", "" );
trim = trim.Replace( "r", "" );
trim = trim.Replace( "n", "" );
trim = trim.Replace( "t", "" );

The best approach is to use regular expressions. You can use the Regex.Replace method, which replaces all matches defined by a regular expression with a replacement string. In this case, use the regular expression pattern “s”, which matches any whitespace character including the space, tab, linefeed and newline.

string trim = Regex.Replace( text, @"s", "" );

The ‘trim’ string will be:

“Myteststringisquitelong” (23 characters)

Enumerate Collections without Exceptions

It’s important to note that an enumerator does not have exclusive, thread-safe access to its collection. Even when a collection is synchronized, other threads can still modify the collection. Therefore, a collection’s contents can change while enumerating through it, which will cause the enumerator to throw an exception. So there are three key ways to safely enumerate a collection:

1. Lock the Collection During Enumeration

To prevent changes to a collection while enumerating through it, you can lock the collection using its SyncRoot property. Note that you can grow your own SyncRoot for collections that don’t have a SyncRoot property. The downside to this approach is that large collections may be locked for long periods of time, resulting in poor performance or deadlocks.

ArrayList list = new ArrayList();
lock (list.SyncRoot)
{
    foreach (object obj in list)
    {
        // do something
    }
}

2. Export Collection Contents to an Array

The best way to guarantee thread-safety is to exclusively own the collection, and the easiest way to do this is to export the collection’s contents to an array. The downside to this approach is the time and memory used to create a separate array.

ArrayList list = new ArrayList();
Array array = list.ToArray();
foreach (object obj in array)
{
    // do something
}

3. Catch Enumerator Exceptions

The final approach is simply to catch any exceptions and re-start the enumeration. The downside to this approach is the enumeration could restart multiple times for oft-changing collections, and there may be cases where you do not want to repeat the enumeration.

ArrayList list = new ArrayList();
bool finished = false;
while (!finished)
{
    try
    {
        foreach (object obj in array)
        {
            // do something
        }
        finished = true;
    }
    catch (InvalidOperationException)
    {
        // collection changed
    }
}

Nested Generics

Given two generic classes:

public class Type1<T> {}
public class Type2<T> {}

.NET allows you to specify a generic type as the type of another generic type:

Type1<Type2<int>> obj = new Type1<Type2<int>>();

Simple Example

Consider a simple reference class that might be used for lazy-fetching an object. For simplicity, the lazy-fetching code has been removed:

public class Ref<T>
{
    public Ref()
    {
    }
    public Ref( T val )
    {
        this.Value = val;
    }
    public T Value;
    public override string ToString()
    {
        return this.Value.ToString();
    }
}

It’s possible to store this generic type Ref<T> in a List<T>, which is also a generic type.
Here’s a sample console program to demonstrate this:

static void Main( string[] args )
{
    List<Ref<int>> refIntList = new List<Ref<int>>();
    refIntList.Add( new Ref<int>( 6 ) );
    refIntList.Add( new Ref<int>( 7 ) );
    foreach (Ref<int> refInt in refIntList)
    {
        Console.WriteLine( refInt );
    }

    List<Ref<string>> refStringList = new List<Ref<string>>();
    refStringList.Add( new Ref<string>( "six" ) );
    refStringList.Add( new Ref<string>( "seven" ) );
    foreach (Ref<string> refString in refStringList)
    {
        Console.WriteLine( refString );
    }

    Console.ReadLine();
}

As you would expect, the console output is:

6
7
six
seven

C# Empty Enumerator

C# code for an empty enumerator. This generic class can be used to simulate enumeration over an empty collection of any type of objects. Here is the code:

using System;
using System.Collections;
using System.Collections.Generic;

public class EmptyEnumerator<T> : IEnumerator<T>
{
    public T Current
    {
        get
        {
            return default(T);
        }
    }
    object IEnumerator.Current
    {
        get
        {
            return this.Current;
        }
    }
    public void Dispose()
    {
    }
    public bool MoveNext()
    {
        return false;
    }
    public void Reset()
    {
    }
}

Empty Enumerator for Lazy Collections

An empty enumerator comes in handy for lazy collections. These are objects that contain a collection, but do not create the collection until it is necessary to add an object to that collection.
For example, consider this simple object “MyObject”:

public class MyObject
{
    public MyObject() { }
    public MyObject( string name )
    {
        this.Name = name;
    }
    public string Name;
    public override string ToString()
    {
        return this.Name;
    }
}

Following is an example of a lazy collection. This “MyList” object contains a collection of MyObject’s, but doesn’t create the list until it is necessary in the “Add” method.

public class MyList : IEnumerable<MyObject>
{
    public void Add( MyObject obj )
    {
        if (obj != null)
        {
            if (this.m_List == null)
                this.m_List = new List<MyObject>();
            this.m_List.Add( obj );
        }
    }
    public int Count
    {
        get
        {
            return this.m_List == null ?
                0 : this.m_List.Count;
        }
    }
    public IEnumerator<MyObject> GetEnumerator()
    {
        IEnumerator<MyObject> enumerator = null;
        if (this.m_List == null)
            enumerator = new EmptyEnumerator<MyObject>();
        else
            enumerator = this.m_List.GetEnumerator();
        return enumerator;
    }
    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }
    private List<MyObject> m_List;
    public void Remove( MyObject obj )
    {
        if (obj != null && this.m_List != null)
            this.m_List.Remove( obj );
    }
}

But what happens if the user wants to enumerate over MyList when it’s empty? There is no enumerator because the collection hasn’t been created yet. Hence, if there is no collection, instead we return an EmptyEnumerator:

public IEnumerator<MyObject> GetEnumerator()
{
    IEnumerator<MyObject> enumerator = null;
    if (this.m_List == null)
        enumerator = new EmptyEnumerator<MyObject>();
    else
        enumerator = this.m_List.GetEnumerator();
    return enumerator;
}

Ternary Operator Doesn’t Work

One interesting thing to notice in the “GetEnumerator” method is the if-else block. You might be tempted to write it with a ternary operator as follows:

public IEnumerator<MyObject> GetEnumerator()
{
    return this.m_List == null ?
        new EmptyEnumerator<MyObject>() :
        this.m_List.GetEnumerator();
}

But this generates a compiler error:

Type of conditional expression cannot be determined because there is no implicit conversion between ‘EmptyEnumerator<EnumerableEmpty.MyObject>’ and ‘System.Collections.Generic.List<EnumerableEmpty.MyObject>.Enumerator’

Simple Example

Here is a simple console program to demonstrate this concept:

class Program
{
    static void Main( string[] args )
    {
        MyList list = new MyList();
        Write( list );

        list.Add( new MyObject( "Luke Skywalker" ) );
        Write( list );

        list.Add( new MyObject( "Darth Vader" ) );
        list.Add( new MyObject( "Yoda" ) );
        Write( list );

        Console.ReadLine();
    }
    static void Write( MyList list )
    {
        Console.WriteLine( "List Count={0}", list.Count );
        foreach (MyObject obj in list)
        {
            Console.WriteLine( obj );
        }
        Console.WriteLine();
    }
}

And the console output would be:

List Count=0
List Count=1
Luke Skywalker
List Count=3
Luke Skywalker
Darth Vader
Yoda

Clear C# StringBuilder

Many .NET developers are baffled by the lack of a “Clear” method in the StringBuilder class. For example, if you are using a StringBuilder in a loop, you may want to clear its contents at the beginning of each loop.

Options to Clear a StringBuilder

It turns out there are two common ways to clear a StringBuilder:
Option #1: Create a new StringBuilder object

StringBuilder sb = new StringBuilder();

Option #2: Set its Length to zero

sb.Length = 0;

Which Option is Better?

Setting the Length property to zero is about 25% faster than creating a new StringBuilder object every time.
Option 2 seems intuitively better because it does not create a new object on the heap every time. Even so, it’s not likely to make a noticeable performance difference in your software. When looping a million times on a fast PC, setting the Length to zero is about 1 second faster than creating a new object.

Sample Console Program

Here is a simple console program that demonstrates this:

using System;

using System.Text; 

namespace CSharp411

{

    class Program

    {

        static void Main( string[] args )

        {

            int loops = 1000000;

            int maxLength = 100; 

            DateTime time1 = DateTime.Now;

            for (int i = 0; i < loops; i++)

            {

                StringBuilder sb = new StringBuilder();

                for (int j = 0; j < maxLength; j++)

                {

                    sb.Append( 'a' );

                }

            } 

            DateTime time2 = DateTime.Now;

            StringBuilder sb2 = new StringBuilder();

            for (int i = 0; i < loops; i++)

            {

                sb2.Length = 0;

                for (int j = 0; j < maxLength; j++)

                {

                    sb2.Append( 'a' );

                }

            }

            DateTime time3 = DateTime.Now; 

            Console.WriteLine( "new = {0}, append = {1}", time2.Subtract( time1 ), time3.Subtract( time2 ) );

            Console.ReadLine();

        }

    }

}

The results of this program on my PC were:

new = 00:00:04.1050000, append = 00:00:03.0690000

C# Convert String to Stream, and Stream to String

It’s fairly easy to convert a C# String to a Stream and vice-versa.

Convert String to Stream

To convert a C# String to a MemoryStream object, use the GetBytes Encoding method to create a byte array, then pass that to the MemoryStream constructor:

byte[] byteArray = Encoding.ASCII.GetBytes( test );

MemoryStream stream = new MemoryStream( byteArray );

Convert Stream to String

To convert a Stream object (or any of its derived streams) to a C# String, create a StreamReader object, then call the ReadToEnd method:

StreamReader reader = new StreamReader( stream );

string text = reader.ReadToEnd();

Console Test Program

Here is a simple test program to demonstrate this round-trip conversion:

using System;

using System.IO;

using System.Text; 

namespace CSharp411

{

    class Program

    {

        static void Main( string[] args )

        {

            string test = "Testing 1-2-3";

            // convert string to stream

            byte[] byteArray = Encoding.ASCII.GetBytes( test );

            MemoryStream stream = new MemoryStream( byteArray ); 

            // convert stream to string

            StreamReader reader = new StreamReader( stream );

            string text = reader.ReadToEnd();

            Console.WriteLine( text );

            Console.ReadLine();

        }

    }

}

C# Internal Interface

When building a C# interface, you may find a need for both public and internal methods, such as:

public class MyClass : IMyInterface
{
    public void MyPublicMethod() { }
    internal void MyInternalMethod() { }
}
public interface IMyInterface
{
    public void MyPublicMethod();
    internal void MyInternalMethod();
}

(For simplicity in this example, we’ll only discuss methods, but this also works for properties, events and indexers.)
Unfortunately, the code above will not compile due to the following errors:

Compiler Error CS0106: The modifier ‘public’ is not valid for this item
Compiler Error CS0106: The modifier ‘internal’ is not valid for this item

Access modifiers such as “public” and “internal” are not allowed for interface members. That’s because the access modifier for the interface itself determines the access level for all members defined in the interface. Hence, adding an access modifier to an interface member would be redundant. But that also means that you cannot mix public and internal members in the same interface.

Separate Public and Internal Interfaces

The solution is to create two interfaces, one public and one internal, such as:

public class MyClass :
    IMyPublicInterface, IMyInternalInterface
{
    public void MyPublicMethod() { }
    internal void MyInternalMethod() { }
}
public interface IMyPublicInterface
{
    void MyPublicMethod();
}
public interface IMyInternalInterface
{
    void MyInternalMethod();
}

Unfortunately, this new code fails to compile due to another error:

Compiler Error CS0737: ‘InternalInterface.MyClass’ does not implement interface member ‘InternalInterface.IMyInternalInterface.MyInternalMethod()’. ‘InternalInterface.MyClass.MyInternalMethod()’ cannot implement an interface member because it is not public.

A method that implements an interface member must have public accessibility. So then how do you create an internal interface?

Explicit Interface Members

The trick is to use an explicit interface member implementation.
“An explicit interface member implementation is a method, property, event, or indexer declaration that references a fully qualified interface member name. Because explicit interface member implementations are not accessible through class or struct instances, they allow interface implementations to be excluded from the public interface of a class or struct. This is particularly useful when a class or struct implements an internal interface that is of no interest to a consumer of that class or struct.”
So in this example, you would define the internal method in the class with its interface prefix, and remove the internal access modifier, as shown:

void IMyInternalInterface.MyInternalMethod() { }

Internal Interface Example

So here is the working example of an object with both internal and public interfaces:

public class MyClass :
    IMyPublicInterface, IMyInternalInterface
{
    public void MyPublicMethod() { }
    void IMyInternalInterface.MyInternalMethod() { }
}
public interface IMyPublicInterface
{
    void MyPublicMethod();
}
internal interface IMyInternalInterface
{
    void MyInternalMethod();
}

Two Interfaces Means Two References

Don’t forget that you will need one reference for each interface. This means each object will have two references: a reference to the object’s public interface, and a reference to the object’s internal interface:

MyClass obj = new MyClass();
IMyPublicInterface objPub = obj;
IMyInternalInterface objInt = obj;
objPub.MyPublicMethod();
objInt.MyInternalMethod();

C# Custom Enumerators Made Simple with the Yield Keyword

An enumerator enables you to iterate over a collection in a foreach loop. You can use foreach to iterate over all C# collection classes, because all C# collection classes inherit from the IEnumerable interface (regular or generic). IEnumerable contains the GetEnumerator method, which returns an enumerator.
Occasionally you may find a need to create a custom enumerator, which used to be somewhat of a challenge until the yield keyword was introduced. Here is how Microsoft describes yield:

The yield keyword signals to the compiler that the method in which it appears is an iterator block. The compiler generates a class to implement the behavior that is expressed in the iterator block. In the iterator block, the yield keyword is used together with the return keyword to provide a value to the enumerator object. This is the value that is returned, for example, in each loop of a foreach statement.

So rather than creating your own enumerator class and managing the enumeration state — which is time consuming and tricky — you can simply write the enumeration logic in the GetEnumerator method, and the yield keyword will automagically wrap your code in a handy-dandy enumerator.

Custom Enumerator Example: Wrap a Collection

To demonstrate the power of yield, let’s create a simple custom enumerator. In this example problem, we have a collection of Base objects, but we only want to work with Derived objects.
So imagine two classes, where the “Derived” class inherits from the “Base” class:

public class Base
{
    public Base( string name )
    {
        this.Name = name;
    }
    public string Name;
}
public class Derived : Base
{
    public Derived( string name )
        : base( name ) { }
}

We also define a collection of Base objects:

public class BaseColl : List<Base> { }

But for this sample problem, we want to work only with Derived objects in the Base collection. To make it easy for developers to use, we’ll create a Derived collection that wraps a Base collection. Notice how the DerivedColl constructor takes a reference to the BaseColl that it wraps:

public class DerivedColl : IEnumerable<Derived>
{
    public DerivedColl( BaseColl baseColl )
    {
        this.m_BaseColl = baseColl;
    }
    private BaseColl m_BaseColl;
}

Yield Keyword

Missing from the DerivedColl code above is the GetEnumerator method where the “yield” magic occurs:

public IEnumerator<Derived> GetEnumerator()
{
    foreach (Base b in this.m_BaseColl)
    {
        Derived d = b as Derived;
        if (d != null)
            yield return d;
    }
}

The foreach code above iterates over the Base objects in the wrapped BaseColl collection. If the Base object is a Derived object (d != null), then the yield keyword returns it. The effect is that the enumerator returned by this GetEnumerator method iterates over ONLY the Derived objects in the Base collection.
Quite handy! If you’ve ever spent time writing a custom enumerator class, you will welcome the yield keyword.

Sample Program

Here is a sample console program:

using System;

using System.Collections;

using System.Collections.Generic;

namespace CSharp411

{

    class Program

    {

        public class Base

        {

            public Base( string name )

            {

                this.Name = name;

            }

            public string Name;

        }

        public class Derived : Base

        {

            public Derived( string name )

                : base( name )

            {

            }

        }

        public class BaseColl : List<Base> { }

        public class DerivedColl : IEnumerable<Derived>

        {

            public DerivedColl( BaseColl baseColl )

            {

                this.m_BaseColl = baseColl;

            }

            private BaseColl m_BaseColl;

            public IEnumerator<Derived> GetEnumerator()

            {

                foreach (Base b in this.m_BaseColl)

                {

                    Derived d = b as Derived;

                    if (d != null)

                        yield return d;

                }

            }

            System.Collections.IEnumerator IEnumerable.GetEnumerator()

            {

                return this.GetEnumerator();

            }

        }

        static void Main( string[] args )

        {

            BaseColl baseColl = new BaseColl();

            DerivedColl derivedColl = new DerivedColl( baseColl );

            Base b = new Base( "Base1" ); baseColl.Add( b );

            b = new Base( "Base2" ); baseColl.Add( b );

            Derived d = new Derived( "Derived1" ); baseColl.Add( d );

            d = new Derived( "Derived2" ); baseColl.Add( d );

            b = new Base( "Base3" ); baseColl.Add( b );

            b = new Base( "Base4" ); baseColl.Add( b );

            d = new Derived( "Derived3" ); baseColl.Add( d );

            d = new Derived( "Derived4" ); baseColl.Add( d );

            foreach (Derived derived in derivedColl)

            {

                Console.WriteLine( derived.Name );

            }

            Console.ReadLine();

        }

    }

}

Sample Output

The output is only the Derived objects:

Derived1
Derived2
Derived3
Derived4

Changing Crystal Report Database logon information at runtime in VS2005

Previously in VS2003, table.Location would report "DATABASE.dbo.NAME" and it was possible to use this to change the Location, but in vs2005 table.Location only reports back the NAME.

Using the code

Deploye the attached file and then pass the ReportDocument as argument to CReportAuthentication.Impersonate(ReportDocument Object)from the place where you want to launch the report.
Blocks of code should be set as style "Formatted" like this:

   Imports System.Configuration
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Public Class CReportAuthentication
    Public Shared Sub Impersonate(ByVal myRpt As ReportDocument)
        ' Set the login info dynamically for the report
        Dim username As String = ConfigurationManager.AppSettings("ReportUser")
        Dim password As String = ConfigurationManager.AppSettings("ReportPassword")
        Dim Server As String = ConfigurationManager.AppSettings("Server")
        Dim Database As String = ConfigurationManager.AppSettings("Database")
        Dim logonInfo As New TableLogOnInfo

        Dim table As Table

        For Each table In myRpt.Database.Tables
            logonInfo = table.LogOnInfo
            logonInfo.ConnectionInfo.ServerName = Server
            logonInfo.ConnectionInfo.DatabaseName = Database
            logonInfo.ConnectionInfo.UserID = username
            logonInfo.ConnectionInfo.Password = password
            table.ApplyLogOnInfo(logonInfo)
            'Previously in VS2003, table.Location would report "DATABASE.dbo.NAME"  - 
            'and it was possible to use this to change the Location, but in vs2005 table.
            'Location only reports back the NAME.  See below for a fix.
            'http://vstoolsforum.com/blogs/crystal_reports/archive/2007/06.aspx
            table.Location = Database & ".dbo." & table.Name
        Next table
    End Sub
End Class

Solving the problem of 'Object Instance not created' in asp.net while using Crystal Reports

it is easy to use crystal report in VB.NET or C#.Net. But when using Crystal Report in ASP.Net some problems can occur. One of the problems is the error Object Reference not set.......

Why this happens

This happens because you had not set virtual directory for the Crystal Report viewer, so the viewer can not be initialised.

Solution

The solution for this is simple just create a Virtual directory Named CrystalReportWebFormViewer which is pointing to <Visual Studio installled folder>\Crystal Reports\Viewers (Usually it is C:\Program Files\Microsoft Visual Studio .NET\Crystal Reports\Viewers). Now refresh, and your report will run properly.

How to Pass Parameters to Crystal Reports at Runtime

First of all, create a Crystal Report with the wizard. Now here, I am assuming that you know how to create a Crystal Report though a wizard in VS 2005. Let's suppose we have created the report that is linked to our table WorkOrders in the database.
Now from the Field Explorer, drag and drop the Unbound String Field to Crystal Report. And then from Field Explorer, explore the Formula Fields and right click on that unbound string field and rename that to UBWONo. Then right click on that field in Crystal report and format the object. Then set its font color to white so that it will not display at runtime. So now you have a field on the report that will get the parameter at runtime. But you must pass this parameter field value to the actual database WorkOrderNo field so that we could get the record of that work order number from the database.

Now right click on the database field WorkOrders.BOWONo and click on Select Expert�

And in the Select Expert Dialog Box, give the following parameters:

Now your Crystal Report is ready to get the parameters. Behind the Windows Form from where you are calling the report, in our case you will see the first picture as shown above.

Collapse | Copy Code

Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class FrmWOVehicleMaint
Public logOnInfo As New CrystalDecisions.Shared.TableLogOnInfo()
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles btnPrint.Click
    Me.Cursor = Cursors.WaitCursor
    If SqlConn.State = 1 Then SqlConn.Close()
    Dim frmRept As New FrmReportsDisplay
    Dim oCR As New rptBOWorkOrder
    oCR.DataDefinition.FormulaFields.Item("UBWONo").Text = "'" & Me.lblWONo.Text & "'"

strSQL = "SELECT VehiclesWorkOrders.BOWONo, VehiclesWorkOrders.DateGiven,  _
        VehiclesWorkOrders.TimeGiven, VehiclesWorkOrders.VehicleNo, _
        VehiclesWorkOrders.Mileage,VehiclesWorkOrders.DateComplete, _
        VehiclesWorkOrders.TimeComplete, VehiclesWorkOrders.TotalDownTime, _
        Employees.FirstName, Employees.MiddleName, Employees.LastName, _
        employees_1.FirstName AS Expr1, employees_1.MiddleName AS Expr2, _
        employees_1.LastName AS Expr3,VehicleCard.VehicleType, _
        VehicleCard.Manufacturer, VehicleCard.VinNo, _
        VehiclesWorkOrders.Problem, VehiclesWorkOrders.Diagnose, _
        VehiclesWorkOrders.PartsUsed, VehiclesWorkOrders.Remarks  _
        FROM VehiclesWorkOrders  LEFT OUTER JOIN Employees _
        AS Employees ON VehiclesWorkOrders.IssuedBy = _
        Employees.EmployeeID LEFT OUTER JOIN Employees AS employees_1 _
        ON VehiclesWorkOrders.HandoverTo = _
        employees_1.EmployeeID LEFT OUTER JOIN VehicleCard _
        AS VehicleCard ON VehiclesWorkOrders.VehicleNo = _
        VehicleCard.VehicleNo  WHERE VehiclesWorkOrders.BOWONo =_
        " & oCR.DataDefinition.FormulaFields.Item("UBWONo").Text
        Dim cmd As New SqlCommand(strSQL, SqlConn)
        Dim DA As New SqlDataAdapter(cmd)
        Dim DS As New DataSet
        DA.Fill(DS, "VehiclesWorkOrders,Employees,Employees_1,VehicleCard")
        DT = DS.Tables(0)
        SqlConn.Open()
        oCR.SetDataSource(DS)
        frmRept.CRViewer.ReportSource = (oCR)
        logOnInfo = oCR.Database.Tables(0).LogOnInfo
        logOnInfo.ConnectionInfo.ServerName = mServerName
        logOnInfo.ConnectionInfo.DatabaseName = mInitialCatalog
        logOnInfo.ConnectionInfo.UserID = mUser
        logOnInfo.ConnectionInfo.Password = mPassword
        oCR.Database.Tables(0).ApplyLogOnInfo(logOnInfo)
        frmRept.Show()
        SqlConn.Close()
        Me.Cursor = Cursors.Default
    End Sub

Creating Reports with SQL Reporting Service and Visual Studio .NET

Introduction

The following article will give you a quick start on how to use the new reporting service of Microsoft inside your ASP.NET Application. It is relatively very easy to use reporting services, however it is a bit different from what we are used to in Crystal reports.

Background

Reporting service is basically a reporting server that uses SQL server as its backend database, all reports are deployed on the reporting server and from their you can access any reports you have access rights to. The basic idea is to have a single location where all reports are deployed, and provides a single point of access, this created a very flexible environment to deploy your reports over the enterprise. The idea is a very similar to Crystal Reports Enterprise Reporting.

Requirements:

You will need the following tools before installing the reporting service, those tools are needed for development of reports for deployment you will need exactly the same environment without Visual Studio .NET

SQL Server 2000 with SP3
IIS 5.0 or 6.0
Visual Studio .NET

Accessing Report Server Management Interface:

You can start by accessing your reporting service by going to http://localhost/reports this is where you can manage your reporting service. You can view reports and other information directly from this web interface, manage subscriptions, security, data sources and other. Mostly we won't be using it in this article except for viewing reports.

The Reporting Service Web Management provides browsing folders that contain reports, data source names that you have deployed. This tool provides viewing of reports, however for developing reports you must have Visual Studio .NET

The above figure shows the report server windows service, as you can see it must be running to be able to access, view and deploy reports from your development tool
As I write this article I heard from Microsoft that they have bought a tool that can provide creating reports directly from the reporting service web interface, I do not have any information when it will be released but hopefully soon.

Developing Your Own Reports

1. Creating Your First Report

First you create a new project, and select Report Project this will create a reporting service project. From here you will find two folders shared data sources, and reports. Shared data sources is one very interesting feature, this is where your data source for your reports. You can have more than 1 shared data source or even a single data source for every report, however it wouldn't be a good idea to repeat the same data source twice if you are using the same database.

2. Creating a Shared Data Source

Here just create a shared data source selecting your SQL server, Northwind database, we will be using basically the Northwind database to build a very simple report to list all our customers.

3. Selecting Data

Before selecting the data for your report, just click on new report and choose the wizard, it will take you step by step. First select the data source that you have just created, then select the table, choose any table you like, in this example I chose the customer table. Then select tabular, and then select all data fields into the detail list box. After you are done, go to the Data Tab of your report you will find table customer, with all fields select here you can alter the table or fields you want to select in your report, just as you are used to when creating a view in SQL Server.

4. Selecting Design

After you are done selecting the data go to, report designer select the layout tab in your report, as you can see in the left toolbox you can use any of the report control to enhance your report functionality and design. You can include charts, images, matrix, etc.. after you're done lets preview the report.

5. Previewing Report

One of the features I love about the reporting service, is the ability to preview your report before deployment, here you can view your report as if you are in the deployment environment.

6. Deploying Report on Report Service

The deployment part is tricky now, you do not just include a reporting customer control in your ASP.NET page and that's it, well you have to first deploy them on your reporting service Server. Ok now as we said all your reports are developed on Visual Studio .NET then they are deploying to a reporting server, either on your machine, intranet, or internet anywhere you want them as long you have access rights to that reporting server. To start deployment right click your application and select properties, the following window will appear. You will find the property "OverwriteDataSources" to be false, make it to true, then select the target folder, this can be anything you like. Then enter the location of your reporting server here it is localhost however it can be a domain, IP address or any location you want as long as reporting service is installed to it. After you are done press F5 or right click the project and select deploy, the minute this is done your reports are deployed on your reporting server.

7. Viewing Report from Report Service

As I said now your report is deployed on the reporting server you can access it directly by going to http://localhost/reports , select the folder you installed the report in then select your report. The report should appear like the one shown below:

8. Including ReportViewer Custom Control in your ASP.NET Application

Now the tricky part on how to include this report in your ASP.NET application, here you will need to use a custom control however, Microsoft does not provide a custom control like crystal report viewer custom control, in fact you will find it deployed in the samples directory of Reporting service. The custom control is located at
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer
You can just go and open that project and compile it and use the ReportViewer DLL in your ASP.NET application. This can be done by opening your toolbox, then click Add/remove and click browse and select the ReportViewer.DLL I included the source and the DLL in the source in case you cannot find it or you didn't install the sample applications of reporting service. Anyway after selecting the DLL you have to select the custom control from the list as shown below:
You will find the name of the Custom Control ReportViewer "Microsoft Sample Report Viewer Application"
When you are done, just include the custom control in your ASP.NET page and change the following properties.

First you have to select the report path and this should be something like :- My Reports/Report1 - exactly the sample folder you deployed your reports in.
Second you have to edit the ServerURL and here you enter your reporting service location http://localhost/reportserver/ this is the reporting server location, while /reports is the report server web management so take care not to get mixed up.

Once both are done, you can start viewing your report by accessing your ASP.NET web page.

9. Viewing your ASP.NET Application, including your Report

Now enter the location of your web application and choose the asp.net page that contains the custom control, and bingo here you find your report as shown below. See how easy

Loading Crystal Report reports which use Stored Proc in C#

When I started working on Crystal Reports, mainly I was wondering about these problems:

How to set parameters of stored procedure from C# code using Crystal's APIs.
How to avoid popup window which comes when we use DSN with SQL Server authentication.
How to avoid these errors:
- Missing prompting unit
- The parameter is incorrect

This article gives a solution to all of the above issues and also gives a few notes to avoid unpredictable results.

Using the code

The attached code here loads the "SalseReport.rpt" file. The steps to run the attached application are:

Create database say "testDB" in SQL Server and execute the script "SalseData_Table_SP.sql" in the SQL Server Query Analyser which will create a stored procedure "Sel_SalesData" and a table "SalesData" for you.
Import the sample data to the table "salseData" from file "SalesData_Data.txt" (data is comma separated).
Create a DSN named "TestDB_DSN" with SQL Server authentication. Give valid user name and password.

Open "frmSalseData.cs" file and update the below line with your logon information, in the function "btnPreview_Click".

Collapse | Copy Code

//The parameters are in the order 

//- UserName, Password, DSN Name, DatabaseName, Case Sensitive

reportDocument.SetDatabaseLogon("pchitriv", "Windows2000", 
                           "TestDB_DSN", "testDB", false);

In case you have created a DSN with some other name than "TestDB_DSN", then open the "SalseReport.rpt" file from the Reports directory and set the DataSource location to point to the correct DSN and "Sel_SalseData" stored procedure again.

The code to load the report looks like this:

Collapse | Copy Code

private void btnPreview_Click(object sender, System.EventArgs e) 
{
    //Instantiate variables

    ReportDocument reportDocument = new ReportDocument();
    ParameterField paramField = new ParameterField();
    ParameterFields paramFields = new ParameterFields();
    ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();

    //Set instances for input parameter 1 -  @vDepartment

    paramField.Name = "@vDepartment";
    //Below variable can be set to any data 

    //present in SalseData table, Department column

    paramDiscreteValue.Value = "South";
    paramField.CurrentValues.Add(paramDiscreteValue);
    //Add the paramField to paramFields

    paramFields.Add(paramField); 

    //Set instances for input parameter 2 -  @iSalseYear

    //*Remember to reconstruct the paramDiscreteValue and paramField objects

    paramField = new ParameterField();
    paramField.Name = "@iSalesYear";
    paramDiscreteValue = new ParameterDiscreteValue();
    paramDiscreteValue.Value = "2004";
    paramField.CurrentValues.Add(paramDiscreteValue);

    //Add the paramField to paramFields

    paramFields.Add(paramField); 

    crystalReportViewer1.ParameterFieldInfo = paramFields;

    reportDocument.Load(@"..\..\..\Reports\SalseReport.rpt");

    //set the database loggon information. 

    //**Note that the third parameter is the DSN name 

    //  and not the Database or System name

    reportDocument.SetDatabaseLogon("pchitriv", "Windows2000", 
                               "TestDB_DSN", "testDB", false);
  
    //Load the report by setting the report source

    crystalReportViewer1.ReportSource = reportDocument;
}

Pages

Monday, November 28, 2011

Friday, November 25, 2011

1. Lock the Collection During Enumeration

2. Export Collection Contents to an Array

3. Catch Enumerator Exceptions

Simple Example

Empty Enumerator for Lazy Collections

Ternary Operator Doesn’t Work

Simple Example

Options to Clear a StringBuilder

Which Option is Better?

Sample Console Program

Convert String to Stream

Convert Stream to String

Console Test Program

Separate Public and Internal Interfaces

Explicit Interface Members

Internal Interface Example

Two Interfaces Means Two References

Custom Enumerator Example: Wrap a Collection

Yield Keyword

Sample Program

Sample Output

Using the code

Why this happens

Solution

Introduction

Background

Requirements:

Accessing Report Server Management Interface:

Developing Your Own Reports

1. Creating Your First Report

2. Creating a Shared Data Source

3. Selecting Data

4. Selecting Design

5. Previewing Report

6. Deploying Report on Report Service

7. Viewing Report from Report Service

8. Including ReportViewer Custom Control in your ASP.NET Application

9. Viewing your ASP.NET Application, including your Report

Using the code