Friday, January 30, 2009

How to display SQL Reports using C#.Net

Very simple !!!, few steps are needed to Display your SQL Reports using C# code.

Let’s create a sample project that displays a SQL report using C#.Net. Here I will assume that you have already designed the report. Now follow the below steps to display you reports in your application

1. Let’s say your report name is EmployeeReport, and that accept Employee Id as a parameter.Now start Visual Studio 2005 or 2008, (I will be using VS 2008 for below examples, the code is same for VS2005 and 2008), create new project “MyReportProj”


2. On the default.aspx page place a text box for employee Id parameter to the report and a button control (name generate report)

Your HTM will look like

<%@ 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 runat="server">

<title>Untitled Pagetitle>

head>

<body>

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

<div>

Employeee: <asp:TextBox ID ="txtEmployee" runat="server">asp:TextBox>

<br />

<asp:Button ID ="btnGenerateReport" runat="server" OnClick="btnGenerateReport_Click" Text="Generate Report" />

div>

form>

body>

html>


1. Place following code in Default.aspx.cs file

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnGenerateReport_Click(object sender, EventArgs e)

{

string employeeId = txtEmployee.Text;

string querystring = "EmployeeReportViewer.aspx?EmployeeId=" + employeeId;

showNewWindow(querystring);

}

protected void showNewWindow(string strScreenName)

{

Random r = new Random();

string PopUpWindowPage = strScreenName;

string Script = "";

Script += "";

if (!Page.IsClientScriptBlockRegistered("PopupWindow"))

Page.RegisterClientScriptBlock("PopupWindow", Script);

}

}

2. Now create another page EmployeeRepportViewer.aspx – this page will have report viewer object and upon page load it will call a method that will display the SQL report directly in pdf, so user can directly get the pdf repot

3. Drag and drop a Report viewer object on the page EmployeeReportViewer.aspx, your html will look like below

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="EmployeeReportViewer.aspx.cs"

Inherits="EmployeeReportViewer" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a"

Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

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 runat="server">

<title>Untitled Pagetitle>

head>

<body>

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

<div>

<rsweb:ReportViewer ID="ReportViewer1" runat="server">

rsweb:ReportViewer>

div>

form>

body>

html>


6. Now put following code in EmployeeReportViewer.aspx.cs file

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using Microsoft.Reporting.WebForms;

public partial class EmployeeReportViewer : System.Web.UI.Page

{

private const string REPORT_NAME = "EmployeeReport";

//Name of your report rdl file, If your report name is EmployeeReport.rdl, then only put EmployeeName as the report name above

public const string PIPE = "|";

private const string DEVICE_INFO = "" +

"True" +

"";

//Desired format goes here (PDF, Excel)

private const string PDF_FORMAT = "PDF";

private const string EXCEL_FORMAT = "Excel";

//

private const string CONTENT_TYPE_PDF = "application/pdf";

private const string CONTENT_DISPOSITION = "Content-disposition";

private const string CONTENT_TYPE_XLS = "application/excel";

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

showReport();

}

private void showReport()

{

string empId = String.Empty;

char[] seprator = { '|' };

string data = String.Empty;

string data1 = String.Empty;

string data2 = String.Empty;

string strParms = String.Empty;

if (Request.QueryString["EmployeeId"] == null)

{

return;

}

empId = Convert.ToString(Request.QueryString["EmployeeId"]);

strParms = "EmployeeId^" + empId;

// here EmployeeId is concatenated with a cap character, This EmployeeId is the parameter which is passing to the SQL report,

//so the name should be same as that which you have specified in you report designer

string[] parmArray = strParms.Split(seprator);

ReportParameter[] parm = new ReportParameter[parmArray.Length];

for (int i = 0; i <>

{

data = parmArray[i];

data1 = data.Substring(0, data.IndexOf("^"));

data2 = data.Substring(data.IndexOf("^") + 1);

parm[i] = new ReportParameter(data1, data2);

}

try

{

displayReport(ReportViewer1, REPORT_NAME, parm);

}

catch (Exception ex)

{

// HANDLE EXCEPTION, REPORT NOT DISPLAYED

}

}

public void displayReport(ReportViewer reportViewer, string reportName, ReportParameter[] parm)

{

string reportFormat = "PDF";

string report_file_name_pdf = reportName + ".pdf";

string report_file_name_xls = reportName + ".xls";

reportViewer.ShowCredentialPrompts = false;

reportViewer.ShowParameterPrompts = false;

reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

reportViewer.ServerReport.ReportServerUrl = new System.Uri("http://NameOfYourReportServer/ReportServer");

reportViewer.ServerReport.ReportPath = "/"+ "NameOfYourReportFolder"+ "/" + reportName;

// Name of your report folder you can get from http://NameOfYourReportServer/Reports this will diplay you the all the report folders and data source

// to export in pdf format

string mimeType, encoding, extension, deviceInfo;

string[] streamids;

byte[] bytes;

Microsoft.Reporting.WebForms.Warning[] warnings;

deviceInfo = DEVICE_INFO;

try

{

reportViewer.ServerReport.SetParameters(parm);

bytes = reportViewer.ServerReport.Render(reportFormat, deviceInfo, out mimeType,

out encoding, out extension, out streamids, out warnings);

Response.Clear();

if (reportFormat == PDF_FORMAT)

{

Response.ContentType = CONTENT_TYPE_PDF;

Response.AddHeader(CONTENT_DISPOSITION, "filename=" + report_file_name_pdf);

}

else if (reportFormat == EXCEL_FORMAT)

{

Response.ContentType = CONTENT_TYPE_XLS;

Response.AddHeader(CONTENT_DISPOSITION, "filename=" + report_file_name_xls);

}

Response.OutputStream.Write(bytes, 0, bytes.Length);

}

catch (Exception ex)

{

//Handle exception

}

finally

{

Response.OutputStream.Flush();

Response.OutputStream.Close();

Response.Flush();

Response.Close();

}

}

}

You are done now, you can run your project and enter valid employee Id in the employee text box, then hit Generate Report Button, you will get the report pdf in new window.



5 comments:

  1. hi!!
    I try to use your code for my problem...
    as you tell in the asp.net forum....
    but in some cases i don't found some key-words... (for ex. "displayReport" or "reportViewer.ServerReport.ReportPath")
    maybe i have to insert some "using" at the top of the .cs page???

    ReplyDelete
  2. you must add name space "using Microsoft.Reporting.WebForms;"

    displayReport() this is a method which i have written above, and calling this method from showreport method.

    ReplyDelete
  3. nice article.. It really helped me

    ReplyDelete
  4. hey could u plz tell me how to give name of report dynamically. i want to give the name of the report in atext box and want to show the report in my C# window application Through thr report viewer by clicking a button.

    ReplyDelete
  5. u can send me an email-paritoshpanigrahi@gmail.com

    ReplyDelete