Working with Crystal Reports in C#

• Download source - 4.71 Kb

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
{
  /// <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.

    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:

    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.