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 this article first before reading mine.
Writing wrapper
First we will write wrapper for that implements some functions 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:
namespace Maija
{
public class Maija
{
public Maija()
{
}
public static bool ExportReport(ReportDocument crReportDocument,
string ExpType,string ExportPath, string filename)
{
filename = filename + "." + ExpType;
if (!Directory.Exists(ExportPath))
Directory.CreateDirectory(ExportPath);
DiskFileDestinationOptions crDiskFileDestinationOptions =
new DiskFileDestinationOptions();
ExportOptions crExportOptions = crReportDocument.ExportOptions;
switch(ExpType)
{
case "rtf":
{
crDiskFileDestinationOptions.DiskFileName =
ExportPath + filename;
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.RichText;
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
break;
}
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
{
crReportDocument.Export();
return true;
}
catch (Exception err)
{
return false;
}
}
public static byte[] ExportReportToStream(ReportDocument
crReportDocument,ExportFormatType exptype)
{
Stream st;
st = crReportDocument.ExportToStream(exptype);
byte[] arr = new byte[st.Length];
st.Read(arr,0,(int) st.Length);
return arr;
}
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 write
Maija.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 this project 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
In my case, imagine that Oracle database (or schema) contains 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 )
Then you should create a new ASP.NET project and name it 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.
private void SaveToDB_Click(object sender, System.EventArgs e)
{
string sql4rep = "SELECT * from USERS";
string sql4db = "SELECT * FROM REPORTS";
OracleConnection con = new OracleConnection(
"User=user;Password=pwd;Data Source=TEST;");
OracleDataAdapter da4rep, da4db;
OracleCommandBuilder comBuilder;
DataSet ds4rep, ds4db;
FileStream fs;
ReportDocument rep;
byte[] data;
da4rep = new OracleDataAdapter(sql4rep,con);
ds4rep = new DataSet();
da4rep.Fill(ds4rep,"BA_USERS");
rep = new ReportDocument();
rep.Load(Server.MapPath("crReport.rpt"));
rep.SetDataSource(ds4rep);
Maija.ExportReport(rep, "rpt", @"c:\TMP\WrapperReports\",
Session.SessionID.ToString());
fs = new FileStream(@"c:\TMP\WrapperReports\"+
Session.SessionID.ToString()+".rpt",
FileMode.OpenOrCreate,FileAccess.ReadWrite);
data = new byte[fs.Length];
fs.Read(data,0,Convert.ToInt32(fs.Length));
fs.Close();
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");
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:
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];
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();
ReportDocument rep = new ReportDocument();
rep.Load(@"C:\TMP\WrapperReports\fromDB\fromDB1.rpt");
crViewer.ReportSource = rep;
}
That's all. I hope this article and code would be helpful for some one who'll be faced with problems of storing reports as BLOB data to Oracle database.