///Helping Site/////////////////
http://csharpdotnetfreak.blogspot.com/2009/07/creating-crystal-reports-in-aspnet.html
//////////////////////
functions of
simple wrapper which has only three function (honestly, I used only one of
them). But you can extend this wrapper to include some additional functionality
you need. All functions of my wrapper are static so you don't need to create an
instance of
code (as always ;) ) so I think my code will be simply for understanding. Here
the implementation of Maija - I have named namespace for Crystal Report wrapper:
![]()
style="CURSOR: hand" height=9 src="file:///C:/images/minus.gif" width=9
preid="0"> Collapse
project I create a ASP.NET which using wrapper described earlier and do it's
functionality by following sequence
with to tables
Web4Wrapper. Add to the WebForm1.aspx two buttons and one CrystalReportViewer.
Name CrystalReportViewer to crViewer. Name Buton1 to SaveToDB; Name Button2 to
LoadFromDB; Ok, that all. Herein I will place the code which processes OnButton
click events, and as usual I tried to comment each line of my code.
![]()
style="CURSOR: hand" height=9 src="file:///C:/images/minus.gif" width=9
preid="1"> Collapse
![]()
style="CURSOR: hand" height=9 src="file:///C:/images/minus.gif" width=9
preid="2"> Collapse
/////////////////////
![]()
style="CURSOR: hand" height=9 src="file:///C:/images/minus.gif" width=9
preid="2"> Collapse
http://csharpdotnetfreak.blogspot.com/2009/07/creating-crystal-reports-in-aspnet.html
//////////////////////
Introduction
In this article I describe working with Crystal Reports in an ASP.NET
application. Also I'll show you how to manage you reports, store generated
reports to Oracle like BLOB objects database and retrieving stored reports from
database. I my article, I expect that you are quite familiar with Crystal
Reports and you know the difference between pull and push model. If you feel
that you need some initial knowledge about Crystal Reports I recommend you to
read following article Before reading my article I recommend you to read href="http://aspalliance.com/articleViewer.aspx?aId=265&pId=1">this
article first before reading mine.
Writing wrapper
First we will write wrapper for that implements somefunctions of
ReportDocument class. In my case this is a quite simple wrapper which has only three function (honestly, I used only one of
them). But you can extend this wrapper to include some additional functionality
you need. All functions of my wrapper are static so you don't need to create an
instance of
CrystalReportWrapper. I tried to comment each line of code (as always ;) ) so I think my code will be simply for understanding. Here
the implementation of Maija - I have named namespace for Crystal Report wrapper:
preid="0"> Collapse
namespace Maija
{
///
/// Summary description for Class1.
///
public class Maija
{
public Maija()
{
//
// TODO: Add constructor logic here
//
}
///
/// Export report to file
///
/// ReportDocument< /param >
/// Export Type (pdf, xls, doc, rpt, htm)< /param >
/// Export Path (physical path
/// on the disk were exported document will be stored on)< /param >
/// File name (file name without
/// extension f.e. "MyReport1")< /param >
/// < returns>returns true if export was succesfull< /returns >
public static bool ExportReport(ReportDocument crReportDocument,
string ExpType,string ExportPath, string filename)
{
//creating full report file name
//for example if the filename was "MyReport1"
//and ExpType was "pdf", full file name will be "MyReport1.pdf"
filename = filename + "." + ExpType;
//creating storage directory if not exists
if (!Directory.Exists(ExportPath))
Directory.CreateDirectory(ExportPath);
//creating new instance representing disk file destination
//options such as filename, export type etc.
DiskFileDestinationOptions crDiskFileDestinationOptions =
new DiskFileDestinationOptions();
ExportOptions crExportOptions = crReportDocument.ExportOptions;
switch(ExpType)
{
case "rtf":
{
//setting disk file name
crDiskFileDestinationOptions.DiskFileName =
ExportPath + filename;
//setting destination type in our case disk file
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
//setuing export format type
crExportOptions.ExportFormatType = ExportFormatType.RichText;
//setting previously defined destination
//opions to our input report document
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
break;
}
//NOTE following code is similar to previous, so I want comment it again
case "pdf":
{
crDiskFileDestinationOptions.DiskFileName =
ExportPath + filename;
crExportOptions.DestinationOptions =
crDiskFileDestinationOptions;
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType =
ExportFormatType.PortableDocFormat;
break;
}
case "doc":
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.WordForWindows;
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
break;
}
case "xls":
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.Excel;
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
break;
}
case "rpt":
{
crDiskFileDestinationOptions.DiskFileName = ExportPath + filename;
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.CrystalReport;
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
break;
}
case "htm":
{
HTMLFormatOptions HTML40Formatopts = new HTMLFormatOptions();
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.HTML40;
HTML40Formatopts.HTMLBaseFolderName = ExportPath + filename;
HTML40Formatopts.HTMLFileName = "HTML40.html";
HTML40Formatopts.HTMLEnableSeparatedPages = true;
HTML40Formatopts.HTMLHasPageNavigator = true;
HTML40Formatopts.FirstPageNumber = 1;
HTML40Formatopts.LastPageNumber = 3;
crExportOptions.FormatOptions = HTML40Formatopts;
break;
}
}
try
{
//trying to export input report document,
//and if success returns true
crReportDocument.Export();
return true;
}
catch (Exception err)
{
return false;
}
}
///
/// Export report to byte array
///
///
/// ReportDocument< /param >
///
/// CrystalDecisions.Shared.ExportFormatType< /param >
/// < returns>byte array representing current report< /returns >
public static byte[] ExportReportToStream(ReportDocument
crReportDocument,ExportFormatType exptype)
{//this code exports input report document into stream,
//and returns array of bytes
Stream st;
st = crReportDocument.ExportToStream(exptype);
byte[] arr = new byte[st.Length];
st.Read(arr,0,(int) st.Length);
return arr;
}
///
/// Export report to string
///
/// ReportDocument< /param >
/// < returns>byte unicode string
/// representing current report< /returns >
public static string ExportReportToString(
ReportDocument crReportDocument)
{
Stream st;
st = crReportDocument.ExportToStream(
ExportFormatType.PortableDocFormat);
byte[] arr = new byte[st.Length];
st.Read(arr,0,(int) st.Length);
string rep = new UnicodeEncoding().GetString(arr);
return rep;
}
}
}
Using the code
Using the code is quite simple to. You just writeMaija.ExportReport(rptDoc, "rpt", @"c:\TMP\WrapperReports\",
Session.SessionID.ToString()); Do not be scared if you do not understand this line. Just read more, and you
will understand all this, you'll see.
Creating Project
Ok, this is the biggest part of the article. In thisproject I create a ASP.NET which using wrapper described earlier and do it's
functionality by following sequence
- Connect to Oracle database
- Retrieve information from table
- Create report based on this information
- Displays created report
- Stores report into file and stores report to Oracle databse as BLOB object
- Retrieves report from Oracle database, stores it to file and shows the
result
with to tables
- USERS - report will get information from this table. It has two fields id
and username.create table BA_USERS ( CODE NUMBER not null, USERNAME
NVARCHAR2(225) not null ) - REPORTS - my ASP.NET application will store generated report into this
table. It has three fields id, description and report.create table
REPORTS ( ID VARCHAR2(100) not null, DESCRIPTION
VARCHAR2(100), REPORT BLOB )
Web4Wrapper. Add to the WebForm1.aspx two buttons and one CrystalReportViewer.
Name CrystalReportViewer to crViewer. Name Buton1 to SaveToDB; Name Button2 to
LoadFromDB; Ok, that all. Herein I will place the code which processes OnButton
click events, and as usual I tried to comment each line of my code.
preid="1"> Collapse
private void SaveToDB_Click(object sender, System.EventArgs e)
{
//selection command for report information
string sql4rep = "SELECT * from USERS";
//selection command for REPORTS table
string sql4db = "SELECT * FROM REPORTS";
//connection object
OracleConnection con = new OracleConnection(
"User=user;Password=pwd;Data Source=TEST;");
//dataadapters for USESR and REPORTS tables
OracleDataAdapter da4rep, da4db;
//Command builder, we need it to call DataAdapter.Update() function
OracleCommandBuilder comBuilder;
//data sets, one for report inforamtion another
//for storing report into DB
DataSet ds4rep, ds4db;
//file stream representing file report
FileStream fs;
//report document
ReportDocument rep;
//array of bytes, we need it to write data from fs
byte[] data;
da4rep = new OracleDataAdapter(sql4rep,con);
ds4rep = new DataSet();
da4rep.Fill(ds4rep,"BA_USERS");
rep = new ReportDocument();
rep.Load(Server.MapPath("crReport.rpt"));//crReport.rpt -
// so I called my report added to solution Web4Wrapper
rep.SetDataSource(ds4rep);
//using wrapper function ExportReport
Maija.ExportReport(rep, "rpt", @"c:\TMP\WrapperReports\",
Session.SessionID.ToString());
//reading exported reports
fs = new FileStream(@"c:\TMP\WrapperReports\"+
Session.SessionID.ToString()+".rpt",
FileMode.OpenOrCreate,FileAccess.ReadWrite);
data = new byte[fs.Length];
//saving read data to byte array
fs.Read(data,0,Convert.ToInt32(fs.Length));
fs.Close();
//SAVING REPORT TO DB as BLOB object
da4db = new OracleDataAdapter(sql4db,con);
ds4db = new DataSet();
da4db.Fill(ds4db,"REPORTS");
comBuilder = new OracleCommandBuilder(da4db);
DataRow r = ds4db.Tables["REPORTS"].NewRow();
r["id"] = "1";
r["description"] = "description 1";
r["report"] = data;
ds4db.Tables["REPORTS"].Rows.Add(r);
da4db.Update(ds4db,"REPORTS");
//showing the result
ReportDocument rd = new ReportDocument();
rd.Load(@"c:\TMP\WrapperReports\"+Session.SessionID.ToString()+".rpt");
crViewer.ReportSource = rd;
}
Here is the code to load report from database: preid="2"> Collapse
private void rptLoad_Click(object sender, System.EventArgs e)
{
string sql = "select report from REPORTS";
OracleConnection conn = new OracleConnection(
"User=user;Password=pwd;Data Source=TEST;");
OracleDataAdapter da = new OracleDataAdapter(sql,conn);
byte[] data= new byte[0];
conn.Open();
OracleCommand cmd = new OracleCommand(sql,conn);
OracleCommandBuilder MyCB = new OracleCommandBuilder(da);
DataSet ds = new DataSet("REPORTS");
da.Fill(ds,"REPORTS");
DataRow myRow;
myRow=ds.Tables["REPORTS"].Rows[0];
//retrieving BLOB to data
data = (byte[])myRow["report"];
int ArraySize = new int();
ArraySize = data.GetUpperBound(0);
FileStream fs = new FileStream(
@"C:\TMP\WrapperReports\fromDB\fromDB1.rpt",
FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(data, 0,ArraySize);
fs.Close();
//showing the result
ReportDocument rep = new ReportDocument();
rep.Load(@"C:\TMP\WrapperReports\fromDB\fromDB1.rpt");
crViewer.ReportSource = rep;
}
/////////////////////
Introduction
The following code shows how to load Crystal Reports in VB.NET, solving all
the issues of logon, including sub reports and parameter passing. You can view
your reports by simply calling the required functions with its parameters.
Using the code
Using this code in your application is very simple. In the first step you
need to add a form, and name it frmViewReport, then place the
Crystal Report Viewer control on the form and name it rptViewer. In
the code section, simply paste the following function. You can call this
function from any where in your application with reference to frmViewReport form. The sample code for calling the function is
given below:
Dim objForm As New frmViewReport
objForm.ViewReport("C:\test.rtp", , "@parameter1=test¶mter2=10")
objForm.show()Now, let me explain you in detail, what is going on in the code and what the
format of the parameter string is. If there are parameters in the Crystal
Reports then they should be passed with their values to the param
of the function. The parameter string should be in the following format:
=&
=.. Note the parameter name and its value pairs are separated by an ‘&’. The
report name with its full path should be passed to sReportName
function.
Following is the function code with comments. I hope there will be no problem
in understanding the code. Even if there is anything bothering you then drop me
a message I'll explain that:
preid="2"> Collapse
Friend Function ViewReport(ByVal sReportName As String, _
Optional ByVal sSelectionFormula As String = "", _
Optional ByVal param As String = "") As Boolean
'Declaring variablesables
Dim intCounter As Integer
Dim intCounter1 As Integer
'Crystal Report's report document object
Dim objReport As New _
CrystalDecisions.CrystalReports.Engine.ReportDocument
'object of table Log on info of Crystal report
Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo
'Parameter value object of crystal report
' parameters used for adding the value to parameter.
Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
'Current parameter value object(collection) of crystal report parameters.
Dim currValue As CrystalDecisions.Shared.ParameterValues
'Sub report object of crystal report.
Dim mySubReportObject As _
CrystalDecisions.CrystalReports.Engine.SubreportObject
'Sub report document of crystal report.
Dim mySubRepDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim strParValPair() As String
Dim strVal() As String
Dim index As Integer
Try
'Load the report
objReport.Load(sReportName)
'Check if there are parameters or not in report.
intCounter = objReport.DataDefinition.ParameterFields.Count
'As parameter fields collection also picks the selection
' formula which is not the parametermeter
' so if total parameter count is 1 then we check whether
' its a parameter or selection formula.
If intCounter = 1 Then
If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName,_
".", CompareMethod.Text) > 0 Then
intCounter = 0
End If
End If
'If there are parameters in report and
'user has passed them then split the
'parameter string and Apply the values
'to there concurent parameters.
If intCounter > 0 And Trim(param) <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index), "=") > 0 Then
strVal = strParValPair(index).Split("=")
paraValue.Value = strVal(1)
currValue = _
objReport.DataDefinition.ParameterFields(strVal(0)).CurrentValues
currValue.Add(paraValue)
objReport.DataDefinition.ParameterFields(strVal(0)).ApplyCurrentValues(_
currValue)
End If
Next
End If
'Set the connection information to ConInfo object so that we can apply the
' connection information on each table in the reporteport
ConInfo.ConnectionInfo.UserID =
ConInfo.ConnectionInfo.Password =
ConInfo.ConnectionInfo.ServerName =
ConInfo.ConnectionInfo.DatabaseName =
For intCounter = 0 To objReport.Database.Tables.Count - 1
objReport.Database.Tables(intCounter).ApplyLogOnInfo(ConInfo)
Next
' Loop through each section on the report then look
' through each object in the section
' if the object is a subreport, then apply logon info
' on each table of that sub report
For index = 0 To objReport.ReportDefinition.Sections.Count - 1
For intCounter = 0 To _
objReport.ReportDefinition.Sections(index).ReportObjects.Count - 1
With objReport.ReportDefinition.Sections(index)
If .ReportObjects(intCounter).Kind = _
CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
mySubReportObject = CType(.ReportObjects(intCounter), _
CrystalDecisions.CrystalReports.Engine.SubreportObject)
mySubRepDoc = _
mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
mySubRepDoc.Database.Tables(intCounter1).ApplyLogOnInfo(_
ConInfo)sp;
mySubRepDoc.Database.Tables(intCounter1).ApplyLogOnInfo(_
ConInfo)
Next
End If
End With
Next
Next
'If there is a selection formula passed to this function then use that
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionFormula = sSelectionFormula
End If
'Re setting control
rptViewer.ReportSource = Nothing
'Set the current report object to report.
rptViewer.ReportSource = objReport
'Show the report
rptViewer.Show()
Return True
Catch ex As System.Exception
MsgBox(ex.Message)
End Tryd Try
End Function
Comments