Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / C#
Article

Working with Crystal Reports in C#

Rate me:
Please Sign up or sign in to vote.
4.31/5 (30 votes)
20 Apr 20042 min read 312.7K   5.5K   86   11
All about working with Crystal Reports in .NET

Image 1

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:
C#
namespace Maija
{
  /// <summary>
  /// Summary description for Class1.
  /// </summary>
  public class Maija
  {
    public Maija()
    {
      //
      // TODO: Add constructor logic here
      //
    }

    /// <summary>
    /// Export report to file
    /// </summary>
    /// <param name="crReportDocument">ReportDocument< /param >
    /// <param name="ExpType">Export Type (pdf, xls, doc, rpt, htm)< /param >
    /// <param name="ExportPath">Export Path (physical path 
    /// on the disk were exported document will be stored on)< /param >
    /// <param name="filename">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;
      }
    }

    /// <summary>
    /// Export report to byte array
    /// </summary>
    /// <param name="crReportDocument">
    /// ReportDocument< /param >
    /// <param name="exptype">
    /// 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;

    }

    /// <summary>
    /// Export report to string
    /// </summary>
    /// <param name="crReportDocument">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 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
  1. Connect to Oracle database
  2. Retrieve information from table
  3. Create report based on this information
  4. Displays created report
  5. Stores report into file and stores report to Oracle databse as BLOB object
  6. 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
  1. 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 )
  2. 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.
C#
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:
C#
    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;

    }
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Ukraine Ukraine

Comments and Discussions

 
GeneralGreat article but.... Pin
Colin Bowdery5-Apr-10 23:30
Colin Bowdery5-Apr-10 23:30 
GeneralRe: Great article but.... Pin
Mark McArthey20-Apr-10 4:22
Mark McArthey20-Apr-10 4:22 
QuestionIssue with charts in crystal reports Pin
vsuki30-May-07 1:14
vsuki30-May-07 1:14 
QuestionProblem with A3 report size ??? Pin
Doan Quynh21-May-07 16:28
Doan Quynh21-May-07 16:28 
GeneralProblem with excel Pin
Zapss30-May-06 22:11
Zapss30-May-06 22:11 
QuestionCrystal reports add on Pin
kokowawa6788-Apr-06 21:47
kokowawa6788-Apr-06 21:47 
Questiondatabase login window popup for windows form Pin
dinoniko5-Dec-05 11:52
dinoniko5-Dec-05 11:52 
AnswerRe: database login window popup for windows form Pin
sridhar chatla15-Apr-06 4:42
sridhar chatla15-Apr-06 4:42 
GeneralCrystal Reports ActiveX Pin
H.R20-Jul-05 11:00
H.R20-Jul-05 11:00 
GeneralCustom data sources Pin
amirchip31-Dec-04 8:09
amirchip31-Dec-04 8:09 
GeneralRe: Custom data sources Pin
dotnetdev@univ.kiev.ua5-Jan-05 2:03
dotnetdev@univ.kiev.ua5-Jan-05 2:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.