Crystal Reports

In this example i m going to describe how to create crystal reports in ASP.NET.

In this i am generating report by fetching data from two tables and grouping them based on Project Name. Database tables are just for demo purpose you can create your own tables with whatever schema you want
Two tables are as shown below.

Create a new website and right click on solution explorer > add new Item > Select Crystal Report
In the dialog box choose blank report.

 

Now click on CrystalReports Menu in VS and select DataBase Expert 

  

In database expert dialog box expend create new connection > OLEDB(ADO) section

 

Now select SQL Native client and enter you SQL server address , username , password and pick database name from the dropdown. 

 

  

In next screen Expend your database objects in left pane and add the tables you want to use in right pane 

 

Link your tables based on Primary keys (If any)

Click ok to finish the wizard.
Right click on Field Explorer and select Group Name Fields  > Insert Group

In next box select the field you to report to be grouped (in my case it's ProjectsName)

Click on OK to finish
Now design the report , drag and fields from Database fields in field explorer and which you want to show in report and drop them in Section3(Details), and preview the report, it should look like show below.

Go to default.aspx page and drag and drop CrystalReportViewer from the toolbox, click on smart tag and choose new report source.

Choose you report from the dropdown menu and click ok to finish.
Now when you build and run the sample , it asks for the database password everytime.

 

To fix this we need to load the report programmatically and provide username and password from code behind .

Now run the report , it should look like this 

 

Html markup of default.aspx look like

<form id="form1" runat="server"> <div>   <CR:CrystalReportViewer ID="CrystalReportViewer1"                            runat="server" AutoDataBind="True"                           Height="1039px"                            ReportSourceID="CrystalReportSource1"                            Width="901px" />   <CR:CrystalReportSource ID="CrystalReportSource1"                            runat="server">             <Report FileName="CrystalReport.rpt">             </Report>    </CR:CrystalReportSource>          </div>     </form>

C# code behind

Write this code in the event you find appropriate , i m writing it in Page_Load , you can write this code in click event of button or in pagePreRender event
The code to provide password programmatically.

protected void Page_Load(object sender, EventArgs e)     {         ReportDocument crystalReport = new ReportDocument();         crystalReport.Load(Server.MapPath("CrystalReport.rpt"));         crystalReport.SetDatabaseLogon             ("amit", "password", @"AMIT\SQLEXPRESS", "TestDB");         CrystalReportViewer1.ReportSource = crystalReport;     }

VB.NET code behind

Protected Sub Page_Load (ByVal sender As Object, ByVal e As EventArgs)  Dim crystalReport As New ReportDocument()  crystalReport.Load(Server.MapPath("CrystalReport.rpt"))  crystalReport.SetDatabaseLogon ("amit", "password", "AMIT\SQLEXPRESS", "TestDB")  CrystalReportViewer1.ReportSource = crystalReport  End Sub

Hope this helps

Read More

Grid View to Pdf Exporting using ITextSharp dll

Introduction:

In one of the previous articles we demonstrated how to export a GridView to excel. In this article we will demonstrate how to export GridView to PDF format using the ITextSharp library.

Creating the Entity Class:

Let's create a simple entity class "Customer" and populate it with some dummy data. The Customer class looks like the following:

view sourceprint?

01	public class Customer

02

{

03	private string _firstName;

04	private string _lastName;

05	private string _email;

06	public string FirstName

07

{

08	get { return _firstName; }

09	set { _firstName = value; }

10

}

11	public string LastName

12

{

13	get { return _lastName; }

14	set { _lastName = value; }

15

}

16	public string FullName

17

{

18	get { return _firstName + " " + _lastName; }

19

}

20	public string Email

21

{

22	get { return _email; }

23	set { _email = value; }

24

}

25

}

Now, let's create a GridView control and poppulate it with the dummy Customer objects.

view sourceprint?

01	<asp:GridView BackColor="Green" ID="gvCustomers" runat="server" />

02	private void BindData()

03

{

04	gvCustomers.DataSource = GetCustomers();

05	gvCustomers.DataBind();

06

}

07	private List<Customer> GetCustomers()

08

{

09	List<Customer> customers = new List<Customer>();

10	for (int i = 1; i <= 20; i++)

11

{

12	Customer customer = new Customer();

13	customer.FirstName = "FirstName" + i;

14	customer.LastName = "LastName" + i;

15	customer.Email = "Email" + i;

16	customers.Add(customer);

17

}

18	return customers;

19

}

The above code will populate the GridView with dummy Customers. Take a look at the screenshot of the GridView below:



Now, let's try to export this GridView to PDF.

How *NOT* to Export a GridView to PDF:

Here is the code which is used to export GridView to PDF.

view sourceprint?

01	protected void WrongExportGridViewToPDF(object sender, EventArgs e)

02

{

03	Response.Clear();

04

05	StringWriter sw = new StringWriter();

06	HtmlTextWriter htw = new HtmlTextWriter(sw);

07	gvCustomers.RenderControl(htw);

08

09	Response.ContentType = "application/pdf";

10	Response.AddHeader("content-disposition", "attachment; filename=MypdfFile.pdf");

11	Response.Write(sw.ToString());

12	Response.End();

13

}

When you export the GridView to PDF using the above code it will seem that the export is taking place correctly but when you try to open the file you will realize that the file is corrupted. Although this particular code works when exporting to Excel or Text files this code will fail when exporting to PDF files. To export GridView to PDF we will need to use a third party library ITextSharp. Let's see how we can use this library.

Downloading the ITextSharp Library:

You can download the ITextSharp library using the following link:

ITextSharp Library

The library is also included in the download sample files.

Exporting the GridView to Excel Using ITextSharp and ResponseOutputStream:

We are going to use the ITextSharp library to write the document to the ResponeOutputStream. Let's take a look at the code:

view sourceprint?

01	protected void ExportToPDFClick(object sender, EventArgs e)

02

{

03	Response.Clear();

04	StringBuilder sb = new StringBuilder();

05	StringWriter sw = new StringWriter(sb);

06	HtmlTextWriter htw = new HtmlTextWriter(sw);

07	gvCustomers.RenderControl(htw);

08	Response.ContentType = "application/pdf";

09	Response.AddHeader("content-disposition", "attachment; filename=MypdfFile.pdf");

10	Document document = new Document();

11	PdfWriter.GetInstance(document, Response.OutputStream);

12	document.Open();

13	string html = sb.ToString();

14	XmlTextReader reader = new XmlTextReader(new StringReader(html));

15	HtmlParser.Parse(document, reader);

16	document.Close();

17	sw.Close();

18	Response.Flush();

19	Response.End();

20

}

The above code is using the Document object which represents a Pdf document. The GridView is rendered into the HtmlTextWriter. The HtmlTextWriter uses the support of StringWriter and StringBuilder to get the HTML for the GridView control. The PdrWriter.getInstance is used to set the Document instance to the Stream which in this case is the Response Stream. The HtmlParser class is responsible for parsing the rendered GridView HTML. This is responsible for creating a table display for the PDF document. If we omit the use of HtmlParser then the raw HTML will be displayed in the Pdf document.

Take a look at the screenshot below which shows the GridView as a Pdf Document:



As, you can see the Pdf document is poorly styled and the table cells are of variable length. Let's see how we can improve the Table cell rendering.

Creating Pdf Document Using ITextSharp Tables:

The ITextSharp library includes the Table class which is used to create Tables in PdfDocuments. We will use the Table class to improve our display.

The button click will fire the following event:

view sourceprint?

1	protected void ExportCollectionToPDFClick(object sender,EventArgs e)

2

{

3	ExportToPDFCollection(new string[] { "FirstName", "LastName", "FullName" });

4

}

The ExportToPDFCollection method is responsible for creating the Table using the Customer collection. It takes a single parameter which is the names of the columns we will want to export to the Pdf document.

Here is the implementation of the ExportToPDFCollection method:

view sourceprint?

01	private void ExportToPDFCollection(string[] columnNames)

02

{

03	List<Customer> customers = GetCustomers();

04	int noOfColumns = columnNames.Length;

05	int noOfRows = customers.Count;

06	string propertyValue = String.Empty;

07

08	iTextSharp.text.Table table = new iTextSharp.text.Table(noOfColumns, noOfRows);

09	table.BackgroundColor = new iTextSharp.text.Color(gvCustomers.BackColor);

10

11	table.AutoFillEmptyCells = true;

12	for (int rowNo = 0; rowNo < noOfRows; rowNo++)

13

{

14	for (int columnNo = 0; columnNo < noOfColumns; columnNo++)

15

{

16	propertyValue = GetPropertyValue(customers[rowNo], columnNames[columnNo]);

17	table.AddCell(propertyValue, new Point(rowNo, columnNo));

18

}

19

}

20	Document document = new Document();

21	PdfWriter.GetInstance(document, Response.OutputStream);

22	document.Open();

23	document.Add(table);

24	document.Close();

25	Response.ContentType = "application/pdf";

26	Response.AddHeader("content-disposition", "attachment; filename=MypdfFile.pdf");

27	Response.End();

28

}

The above code creates the Table and append cells to the Table. Each cell is populated with the particular propertyname of the Customer class. The GetPropertyValue method uses reflection to get the value from the property.

view sourceprint?

1	private string GetPropertyValue(Customer customer,string propertyName)

2

{

3	PropertyInfo property = customer.GetType().GetProperty(propertyName);

4	return property.GetValue(customer, null).ToString();

5

}

Here is the result:



Although the resultant Pdf document is not pretty but you can always make adjustments to make it better. Also note that the cells are property listed.

Conclusion:

In this article we learned how to export GridView to Pdf. We discussed different ways of performing this operation. In the future articles we will try to demonstrate how to customize the display of the exported Pdf document.

[Download Sample]

Read More

Select Distinct From DataSet

This step-by-step article illustrates the equivalent to select distinct values from one or more column of a DataSet or DataTable
Example :

NOTE: Add the following to the top of the code window:

 

using System.Data;

//Add the following constructor code :

System.Data.DataSet ds = new System.Data.DataSet();

// Create source table

System.Data.DataTable dt = new System.Data.DataTable("MyTableName");
dt.Columns.Add("ColumnA", Type.GetType("System.Int32"));
dt.Rows.Add(1);
dt.Rows.Add(2);
dt.Rows.Add(3);
dt.Rows.Add(1);
Ds.Tables.Add(dt);
System.Data.DataView Dv = Ds.Tables["MyTableName"].DefaultView;
System.Data.DataTable DtD = Dv.ToTable(true, "ColumnA");

//DtD contains only Distinct values (1,2 and 3)

The command Dv.ToTable(true, .. help you to Select Distinct from Ds DataSet the column : "ColumnA"

Read More

Merge Similar Rows in Gridview

In this example i am going to describe how to merge GridView cells or Columns in gridview rows containing same data or content using C# and VB.NET in ASP.NET

For this i m using DataBound Event of gridview, counting total rows and then checking each cells value against value of same cell in previous row and then setting the RowSpan of cells.

For this i have created a table containing Counties ,states and respective cities and country and state cells / columns are merged in rows having same country or states.




Html source of the page look like this

<asp:GridView ID="GridView1" runat="server"      AutoGenerateColumns="False"       BorderStyle="None" BorderWidth="1px" CellPadding="4"      GridLines="Horizontal" ForeColor="Black"      Height="119px" DataSourceID="SqlDataSource1"      OnDataBound="GridView1_DataBound1">              <Columns>             <asp:BoundField DataField="Country"                              HeaderText="Country"                              SortExpression="Country" />             <asp:BoundField DataField="State"                              HeaderText="State"                              SortExpression="State" />             <asp:BoundField DataField="City"                              HeaderText="City"                              SortExpression="City" />         </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server"  ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [Country], [State], [City]                 FROM [Details] ORDER BY [State]"> </asp:SqlDataSource>

C# code behind

protected void GridView1_DataBound1(object sender, EventArgs e) {   for (int rowIndex = GridView1.Rows.Count - 2;                                       rowIndex >= 0; rowIndex--)   {     GridViewRow gvRow = GridView1.Rows[rowIndex];     GridViewRow gvPreviousRow = GridView1.Rows[rowIndex + 1];     for (int cellCount = 0; cellCount < gvRow.Cells.Count;                                                    cellCount++)     {      if (gvRow.Cells[cellCount].Text ==                              gvPreviousRow.Cells[cellCount].Text)      {        if (gvPreviousRow.Cells[cellCount].RowSpan < 2)        {          gvRow.Cells[cellCount].RowSpan = 2;        }        else        {         gvRow.Cells[cellCount].RowSpan =              gvPreviousRow.Cells[cellCount].RowSpan + 1;        }        gvPreviousRow.Cells[cellCount].Visible = false;     }    }  } } 

VB.NET code behind

Protected Sub GridView1_DataBound1            (ByVal sender As Object, ByVal e As EventArgs)  For rowIndex As Integer = GridView1.Rows.Count - 2 To 0 Step -1     Dim gvRow As GridViewRow = GridView1.Rows(rowIndex)     Dim gvPreviousRow As GridViewRow = GridView1.Rows(rowIndex + 1)     For cellCount As Integer = 0 To gvRow.Cells.Count - 1     If gvRow.Cells(cellCount).Text =                           gvPreviousRow.Cells(cellCount).Text Then     If gvPreviousRow.Cells(cellCount).RowSpan < 2 Then     gvRow.Cells(cellCount).RowSpan = 2     Else     gvRow.Cells(cellCount).RowSpan =                         gvPreviousRow.Cells(cellCount).RowSpan + 1     End If     gvPreviousRow.Cells(cellCount).Visible = False     End If     Next   Next End Sub 

--

Read More