Wahyu Kurniawan Blog

Display Dynamic Image in Crystal Report .NET

If you have imagepath that store in your database, Crystal Report .NET in Visual Studio 2003/2005 cannot display image file dynamically unless you use dynamic image location feature in Crystal Report XI.

But don't worry, it can display with some work around for this.

1. You must have imagepath in your database.
2. Create new Dataset/XML Schema (xsd) to use as resource data in creating report. Add an additional field that is not in the table and which is of type base64Binary :
   <xs:element name="image_stream" type="xs:base64Binary" minOccurs="0" />
3. When designing a report drag and drop the "image_stream" field in the region where you want it to appear.
4. Add the following method in you code:
   private void AddImageColumn(DataTable objDataTable, string strFieldName)
   {
    try
    {
     DataColumn objDataColumn = new DataColumn(strFieldName, Type.GetType("System.Byte[]"));
     objDataTable.Columns.Add(objDataColumn);
    }
    catch (Exception ex)
    {
     Response.Write("<font color=red>"+ex.Message+"</font>");
    }
   }
5. And this one to load the image:
   private void LoadImage(DataRow objDataRow, string strImageField, string FilePath)
   {
    try
    {
     FileStream fs = new FileStream(FilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
     byte[] Image = new byte[fs.Length];
     fs.Read(Image, 0, Convert.ToInt32(fs.Length));
     fs.Close();
     objDataRow[strImageField] = Image;
    }
    catch (Exception ex)
    {
     Response.Write("<font color=red>"+ex.Message+"</font>");
    }
   }
6. Before assigning the dataset to the "SetDataSource" of your report, add the following code:
   AddImageColumn(ds.Tables[0], "image_stream");
   for (int index=0; index < ds.Tables[0].Rows.Count; index++)
   {
    if (ds.Tables[0].Rows[index]["image_path"].ToString() != "")
    {
     if(File.Exists(this.Server.MapPath(ds.Tables[0].Rows[index]["image_path"].ToString())))
     {
      LoadImage(ds.Tables[0].Rows[index], "image_stream", ds.Tables[0].Rows[index]["image_path"].ToString());
     }
     else
     {
      LoadImage(ds.Tables[0].Rows[index], "image_stream", "C:\NoImage.jpg");
     }
    }
    else
    {
     LoadImage(ds.Tables[0].Rows[index], "image_stream", "C:\NoImage.jpg");
    }
   }
7. And finally to display report in crystal report viewer
   crDoc.SetDatabaseLogon(UserID, Password, Server, Database);
   crDoc.SetDataSource(ds.Tables[0]);
   CrystalReportViewer1.ReportSource = crDoc;

Share this post: | | | |

Split String in SQL Server

This function is used to split string with dynamic delimiter.

Example:
SELECT * FROM dbo.f_Split('AAABB+BBBCC+CCCDD+DDDEE','+')

Code:
CREATE FUNCTION f_Split
(
 @Keyword VARCHAR(8000),
 @Delimiter VARCHAR(255)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000))
AS
BEGIN
 DECLARE @Word VARCHAR(255)
 DECLARE @TempKeyword TABLE (Keyword VARCHAR(8000))

 WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)
 BEGIN
  SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) - 1)
  SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))
  INSERT INTO @TempKeyword VALUES(@Word)
 END
 
 INSERT INTO @TempKeyword VALUES(@Keyword)
 
 INSERT @SplitKeyword
 SELECT * FROM @TempKeyword
 RETURN
END

Share this post: | | | |

SQL Server Reporting Services and Crystal Reports: A Competitive Analysis

Copyright 2005, Brian Bischof

EXECUTIVE SUMMARY

During 2004, Microsoft grabbed the attention of the Visual Studio .NET community by announcing a new reporting product: SQL Server Reporting Services (SSRS). Not only did they promise to give programmers a new reporting tool, but it was going to be free as well. Suddenly everyone was comparing Reporting Services to Crystal Reports - the report designer that has been bundled with VB since VB 3 and integrated into Visual Studio .Net (and will also be included in the next release of Visual Studio .Net 2005).

The goal of this paper is to illustrate differences between SSRS and the current version of Crystal Reports, Crystal Reports XI (version 11). Each product has its strengths and weaknesses and these are highlighted here. It's important to evaluate each product and consider which one works best for your application's reporting requirements.

After working with both products, I compared them on product offering, report file format, licensing, data connectivity, security, and design capabilities. We'll get into the full details on each subject, but I do want to highlight two things about Reporting Services:

1. Although advertised as being free, Microsoft does not recommend the "free" configuration and additional SQL Server licensing (anywhere from $5000 to $25000) will be required just to get started.
2. Reporting Services only handles about 75% of common reporting scenarios in my estimation. Some obvious features expected from a reporting tool are missing and still has bugs that needs to be fixed. This most likely won't be enough for developers who need to write new reports on a regular basis or have users who are critical of the report output.

Table of Contents
Executive Summary
Reporting Services Overview
Reporting Services Overall Impressions
Product Offerings and File Formats
Licensing Details
Connecting To Data
Securing Sites with Forms Authentication
Designing Reports
Passing Parameters
Exporting Reports
Subreports
Conclusion

Share this post: | | | |