Storing and retrieving Crystal reports in Database

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