Tech Touch

Storing and retrieving Crystal reports in Database

2009-08-05T06:10:00.000-07:00

Storing and retrieving Crystal reports in Database

It is not advisable to store and retrieve the crystal reports in database due to increase the system as well the network performance. The right way is store the reports in local disk and file name in database. Even though a lot of ways to secure the files in system disk, some clients not compromise with that.
So in this thread I’m representing a way to store the crystal reports in database and a way to retrieve it and show to the user

Create and Store Report in Database:
· Create a layout file (rpt). You can use either PULL or PUSH method to create the report.
· Add a ReportDocument object and do the necessary steps to load the report into that Report Document object (Load report, set required parameters, set Login information in the case of PULL method and set SetDatasource in the case of PUSH method). There are many articles related to this, so I’m not going to discuss this in depth.
· Create a table with a column VARBINARY(MAX) to store the binary format of report.
· Write the below procedure to insert the value in to the table. Here you can use your inline query instead of stored procedure also.

CREATE PROCEDURE [dbo].[USP_INSERT_REPORT_INDB]
@report_id AS INT
,@report_image AS VARBINARY(MAX)
,@report_creator AS INT
AS
BEGIN
INSERT INTO [dbo].[REPORT_DETAIL]([report_id,[rd_image],[rd_created_date],[rd_creator])
VALUES(@report_id,@report_image,GETDATE(),@report_creator)
END

· Write the below C# code to convert the report document object into binary and store it in database.

private bool SaveReportInDB(ReportDocument crReportDocument)
{
bool IsReportSavedInDB = false;
try
{
int reportId = Convert.ToInt32(ViewState["reportId"]);
int reportCreator;
//If you are going to show rpt file next time from DB use ExportFormatType.CrystalReport Or use the appropriate type
MemoryStream memStream = MemoryStream)crReportDocument.ExportToStream(ExportFormatType.CrystalReport);
string connection_string = "your connecion string";
SqlConnection myConnection = new SqlConnection(connection_string);
myConnection.Open();
SqlCommand myCommand = new SqlCommand("USP_INSERT_REPORT_INDB", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter pramReportId = new SqlParameter("@report_id", reportId);
SqlParameter paramReport = new SqlParameter("@report_image", memStream.ToArray());
SqlParameter paramReportCreator = new SqlParameter("@report_creator", reportCreator);
// add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(pramReportId);
myCommand.Parameters.Add(paramReport);
myCommand.Parameters.Add(paramReportCreator);
//Call the SP
myCommand.ExecuteNonQuery();
IsReportSavedInDB = true;
}
catch (Exception)
{
}
return IsReportSavedInDB;
}

Retrieve and display Report from Database

· Same like above write a inline query or SP to retive the stored report data
· Write the below code in your C#

private void LoadReport()
{
try
{
ReportDocument crReportDocument = null;
string filename = string.Empty;
//Create a new SQL Connection to the database
SqlConnection sqlCon = new SqlConnection("Server=server;Database=db;uid=uis;pwd=pass");
//Create a command object for reading document from Database
SqlCommand sqlCom = new SqlCommand("Select rd_image from REPORT_DETAIL Where report_id=1", sqlCon);
sqlCon.Open();
//Read data into DataReader
SqlDataReader sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
//If you store the report in rpt format then the extension should be in .rpt or in appropriate format
filename = Server.MapPath("report_archive") + "\\testReportDB.rpt";
//Read binary Data into Bytes Array
byte[] fileBytes = (byte[])sqlDr["rd_image"];
//Create FileStream Object from for saving file
FileStream oFileStream = new FileStream(filename, FileMode.Create);
//SaveFile
oFileStream.Write(fileBytes, 0, fileBytes.Length);
//Dispose the memory of used objects
oFileStream.Close(); oFileStream.Dispose();
fileBytes = null;
}
//Close Reader and Connection
sqlDr.Close(); sqlCon.Close();
//Dispose Memory
sqlDr.Dispose(); sqlCon.Dispose(); sqlCom.Dispose();
//if yo want to load the report in crystal report viewer use the below code
// Or if you want render directly just open the 'filename' in new browser(in case pdf,doc and excel files)
if (filename != string.Empty)
{
crReportDocument = new ReportDocument();
crReportDocument.Load(filename);
CrystalReportViewer1.ReportSource = crReportDocument;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}

Here the main problem may be the removing of temporary created files. I will come with the update for that.
Any comments and feedbacks always welcome…..

genral tips

2008-09-29T01:32:00.000-07:00

genral tips

Dynamic Images at runtime in Crystal Report XI using ASP.Net 2.0

2008-06-16T12:30:00.000-07:00

Hello,

This article helps to display dynamic Images in crystal report Using ASP.Net 2.0.

We can use two methods to dynamically change the picture in the crystal report either the Image stored in the database as a BLOB and as a picture available in the local path.

Method I: Using recordset

1. Add a recordset and add a table in that. Add a column of System.Byte[] (Only System.Byte is available in the data type of data Table, Manually add the System.Byte[] in that. System.Byte not allowed for images).Then use the below code

2. Design the report with that dataset. You can add this System.Byte[] column as part of your main data table which have the all data or add a separate data table with a System.Byte[] and a key column that link to the main data table.

3. Add the below code

private ds_Images Images1;
rptTest crReportDocument = new rptTest(); // rptTest is your crystal report name

protected void btnShowReport_Click(object sender, EventArgs e)
{
ImageTable(); crReportDocument.Database.Tables["tblImages"].SetDataSource(Images1.Tables[0].DataSet);
string ExportPathFinal;
ExportPathFinal = ExportPath + "\\" + "TEMP" + "\\";
if (Directory.Exists(ExportPathFinal) == false) Directory.CreateDirectory(ExportPathFinal);

//Export Crystal Report to PDF

ExportOptions crExportOptions = new ExportOptions();
DiskFileDestinationOptions crDiskFileDestinationOptions = new DiskFileDestinationOptions();
crExportOptions = crReportDocument.ExportOptions;
crDiskFileDestinationOptions.DiskFileName = ExportPathFinal + "MyreportTest.pdf";

//Set the required report ExportOptions properties
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat; // Or any other Format
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
crReportDocument.Export();

string ExportPathFinal1 = ExportPath1 + "\\" + "TEMP" + "\\";
string pathToPDFfile = ExportPathFinal1 + "MyreportTest.pdf";
Response.Redirect(pathToPDFfile, true);

//Close and dispose of report
crReportDocument.Close();
crReportDocument.Dispose();
GC.Collect();
}

private void ImageTable()
{

ds_Images Images1 = new ds_Images();
string fileName = @"\\img\a.JPG";
fileName = Server.MapPath(fileName.Trim());
DataRow row;
Images1.Tables[0].TableName = "tblImages";
Images1.Tables[0].Columns.Clear();
Images1.Tables[0].Columns.Add("img", System.Type.GetType("System.Byte[]"));
row = Images1.Tables[0].NewRow();
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
row[0] = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length));
row[1] = 1;
Images1.Tables[0].Rows.Add(row);
br = null;
fs.Close();
fs = null;
}

Method II: Using Picture Object of Crystal Report

Using the Dynamic Graphic Location of picture OLE object of crystal report, we can change picture dynamically at run-time.

1. Create a parameter field in the report with string data type.
2 In the report Add a picture OLE object. inside report right click->Insert->OLE object- >select Bitmap Image
3 Right click the OLE picture object and select Format Object- >select Picture tab ->Graphic location -> inside it drag the parameter field.

in the front end just pass the Image URL to the report

ParameterDiscreteValue crParameterimgLocation;

string img_url = @"\\images/newImages/nevadadot.JPG";
img_url = Server.MapPath(img_url);
crParameterField = crParameterFields["imgLocation"];
crParameterValues = crParameterField.CurrentValues;
this.crParameterimgLocation = new ParameterDiscreteValue();
this.crParameterimgLocation.Value = img_url;

//Add current value for the parameter field
crParameterValues.Add(crParameterimgLocation);

Note: while passing the Image URL do not put put single quotes.

We can use either method to display the Images in the report dynamically.

In Crystal Reports XI, the 'Dynamic Image Location' feature does not work with images in GIF format. Why does this behavior occur and how can you resolve it?" This drawback matches to the recordset method too.

"To debug the issue

This behavior occurs because Crystal Reports XI does not fully support the GIF file format. To resolve this behavior, use a supported image format when working with the 'Dynamic Image Location' feature. Supported image formats are Joint Photographic Experts (JPG), bitmap (BMP), Tagged Image File Format (TIF) and Portable Network Graphics (PNG)."

Any comments appreciated

Passing Values between Pages in ASP.Net

2008-05-14T01:16:00.000-07:00

Passing values between pages in ASP.Net
###########################################

Here are some useful way to pass values between pages. I think it will useful for the beginners who entered in Web development

1.Response.Redirect

This maybe the easiest of them all. You start by writing some data in the text field, and when you finish writing the data, you press the button labeled 'Response.Redirect'. One tip that I would like to share with you is, sometimes we want to transfer to another page inside the catch exception, meaning exception is caught and we want to transfer to another page. If you try to do this, it may give you a System.Threading exception. This exception is raised because you are transferring to another page leaving behind the thread running. You can solve this problem using:

Response.Redirect("WebForm2.aspx",false);

This tells the compiler to go to page "WebForm2.aspx", and "false" here means that don't end what you were doing on the current page. You should also look at the System.Threading class for threading issues. Below, you can see the C# code of the button event. "txtName" is the name of the text field whose value is being transferred to a page called "WebForm2.aspx". "Name" which is just after "?" sign is just a temporary response variable which will hold the value of the text box.

private void Button1_Click(object sender, System.EventArgs e)
{
// Value sent using HttpResponse
Response.Redirect("WebForm2.aspx?valName="+txtName.Text);
}

Okay, up till this point, you have send the values using Response. But now, where do I collect the values, so in the "WebForm2.aspx" page_Load event, write this code. First, we check that the value entered is not null. If it's not, then we simply display the value on the page using a Label control. Note: When you use Response.Redirect method to pass the values, all the values are visible in the URL of the browser. You should never pass credit card numbers and confidential information via Response.Redirect.

if (Request.QueryString["valName"]!= null)
lblName.Text = Request.QueryString["Name"];

-------------------------------------------------------------------------------------------

2.Cookies

Next up is cookies. Cookies are created on the server side but saved on the client side. In the button click event of 'Cookies', write this code:

HttpCookie cName = new HttpCookie("valName");
cName.Value = txtName.Text;
Response.Cookies.Add(cName);
Response.Redirect("WebForm2.aspx");

First, we create a cookie named "cName". Since one cookie instance can hold many values, we tell the compiler that this cookie will hold "Name" value. We assign to it the value of the TextBox and finally add it in the Response stream, and sent it to the other page using Response.Redirect method.

Let's see here how we can get the value of the cookie which is sent by one page.

if (Request.Cookies["valName"] != null )
lblName.Text = Request.Cookies["valName"].Value;

As you see, it's exactly the same way as we did before, but now we are using Request.Cookies instead of Request.QueryString. Remember that some browsers don't accept cookies.

--------------------------------------------------------------------------------------

3.Session Variables

Next we see the session variables which are handled by the server. Sessions are created as soon as the first response is being sent from the client to the server, and session ends when the user closes his browser window or some abnormal operation takes place. Here is how you can use session variables for transferring values. Below you can see a Session is created for the user and "Name" is the key, also known as the Session key, which is assigned the TextBox value.

// Session Created
Session["Name"] = txtName.Text;
Response.Redirect("WebForm2.aspx");

// The code below shows how to get the session value.
// This code must be placed in other page.
if(Session["Name"] != null)
Label3.Text = Session["Name"].ToString();

It is best practice to declare all your session variables in the global.asax file, in the session_start() event, so it will available for your whole application.
--------------------------------------------------------------------------------

4.Application Variables

Sometimes, we need to access a value from anywhere in our page. For that, you can use Application variables. Here is a small code that shows how to do that. Once you created and assigned the Application variable, you can retrieve its value anywhere in your application.

// This sets the value of the Application Variable
Application["valName"] = txtName.Text;
Response.Redirect("WebForm2.aspx");

// This is how we retrieve the value of the Application Variable
if( Application["valName"] != null )
lblName.Text = Application["valName"].ToString();

-----------------------------------------------------------------

5.HttpContext

You can also use HttpContext to retrieve values from pages. The values are retrieved using properties or methods. It's a good idea to use properties since they are easier to code and modify. In your first page, make a property that returns the value of the TextBox.

public string GetName
{
get { return txtName.Text; }
}

We will use Server.Transfer to send the control to a new page. Note that Server.Transfer only transfers the control to the new page and does not redirect the browser to it, which means you will see the address of the old page in your URL. Simply add the following line of code in 'Server.Transfer' button click event:

Server.Transfer("WebForm2.aspx");

Now, let's go to the page where the values are being transferred, which in this case is "webForm2.aspx".

// You can declare this Globally or in any event you like
WebForm1 w;

// Gets the Page.Context which is Associated with this page
w = (WebForm1)Context.Handler;
// Assign the Label control with the property "GetName" which returns string
lblName.Text = w.GetName;

============================================================

Hope it will helpful for you

any comments appreciated

Secure PDF Files and opening reports in a new window in ASP.Net 2.0

2008-05-14T01:07:00.000-07:00

The main concern we faced while handling the PDF file is its security.We export the crystal report into PDF file and rendered in the browser. After viewing the PDF file anyone can access the PDF if they came to know the path. After analyzed a lot of methods we concluded with the below solution. It is easy to implement and feel happy to share this with you all the experts.

During the analysis I came to know various ways to secure the PDF files such as Form authentication,storing the PDF files in other secure formats(like .recourse) and while calling the PDF file just save as the file into PDF and rendered using Response.WriteFile method, using session Id with the PDF file name and a few more.

For the stored PDF files or the Crystal reports need to generate and to be stored in a external device for the future purpose

We can use the below methods

1.Create a separate page 'SecurePdf.aspx'. we can use this page for entire application

2.Place a Iframe control
iframe id="fraPdfRender" runat="server" height="800" scrolling="auto" width="100%"

3.If crystal report need to rendered as a PDF file, first convert that into PDF file using the export method of crystal document and use the below code on the required page which the report or the PDF file need to rendered . It will open the 'SecurePdf.aspx' in a separate window (the popup blocker should allow this site, or if it is not required to open in a new window use the normal response.redirect method for opening the page)

string url = reportfilename;
Session["securePdf"] = url;
string script = "window.open('/reports/securePdf/SecurePdf.aspx','new_Win');";
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", script, true);

4.place this code in the code behind file
string url = Session["securePdf_n"].ToString();
HtmlControl frame1 = (HtmlControl)this.FindControl("fraPdfRender");
frame1.Attributes["src"] = url;

I believe this code is easy to impalement and the url of the PDF file is not shown to the user.I implemented into my application but didn't get the feedback from my client.

If Crystal reports needs to generate for on demand as PDF file:

after analyzed some I used the below method

ReportDocument crReportDocument;
crReportDocument = new cReportName();

crReportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat,HttpResponse,false);

this will convert the crystal report into PDF automatically and render into the browser without storing in a external disk.

Believe it will helpful for the secure PDF file handling , rendering the Crystal report as PDF file and for opening the report in separate window.

As comments and opinion always expected from you