Articles of Interest

Our company CEO Mr. Joey Gurango, regularly writes for ComputerWorld Philippines.

His latest article as of this writing may be read here.

Here's two of my favorite articles (which I am sure is also the favorite of many of my co-workers):

Keeping Alisbayans at Home (Part 1)
Keeping Alisbayans at Home (Part 2)

Other articles by Sir Joey maybe found by using the search function of the site, since I don't think the site offers each of its contributing authors their own personal page.

Crystal Reports using Dynamic DataSets

While working on a project for our company, I was required to make use of Crystal Reports to generate certain reports for certain parts of the program. The application made use of different databases, depending on which one the user chose.

So, to make the long story short, I had to make Crystal Reports work with dynamic data. For those of you who have used Crystal Reports before, you are aware that you add fields into your report by dragging a field or column from the table into your report. How then are you going to do this with dynamic data or without the use of any fixed table?

After spending hours looking for answers online, I could not find any definite solution so I had to do a little experimentation with Crystal Reports before I finally came up with a solution.

This solution makes use of Visual Studio .NET, Visual C# and Crystal Reports. In this example, we'll create a report for an employee and we'll be using a windows based form. We'll keep it simple and have one table named Employees and 2 fields, Name and Position.

Before starting make sure that your project already has a default form with a crystal report viewer added into the form.

There are 4 basic steps to do this:

1. Create a strongly typed dataset in your application. This will serve as the data source for your crystal report.
2. Create a report and use the strongly typed dataset as the data source.
3. Fill in the data with a little programming which I will show you later in this article.
4. Attach the filled in dynamic dataset to the report file.

Step One: Create a strongly typed dataset.

• In your solution or project, add a new dataset. To do this, right click on the project and select add > add new item > dataset. Name this dataset dsEmployee.
• Using the toolbox add an element into the newly created dataset.
• In the first row, type the name of your table, Employee.
• In the next 2 rows type in the 2 fields, Name and Position.

Step Two: Create a report and use the strongly typed dataset as the data source.

• In your solution or project, add a new report. To do this, right click on the project and select add > add new item > crystal report. Name this report rptEmployee.
• Once the new report is added, set the data source to the dataset you created earlier. To do this, right click on any part of the report and select database > add/remove database.
• From the Database Expert window, select Project Data > ADO .NET Datasets and select the strongly typed dataset you created earlier.
• Add the Employees table. After doing this you should see the Employees table and it's corresponding fields in the Field explorer window under Database Fields.
• Drag the two fields into the report and design the report any which way you please.

Step 3: Do a little programming magic to fill up the dataset

The main thing that has to be done is to connect the dataset with the crystal report.

• Instantiate the strongly typed dataset you created earlier.
  
  dsEmployee employeeData = new dsEmployee( );
  
  
• Fill up the information for the employee. Do this by creating a DataRow based on dsEmployee.
  

DataRow empRow = employeeData.Employee.NewRow( );

Notice in this part of the code:

employeeData.Employee.NewRow( )

Employee is the name of the table/element we added earlier. It only goes to follow that the DataRows for this should have the same name as the fields we added earlier. So in effect:

empRow["Name"] = "Juan Dela Cruz";
empRow["Position"] = "Systems Administrator";


• Finally add the row into the table:
  

employeeData.Rows.Add(empRow);

Now, this is a very simple example of how to fill up the report data. You can be as creative or complicated as you wish, you may use other datasets, xml files, flat files or what have you to fill up the data.

Step Four: Attach the filled in dynamic dataset to the report file.

• Make sure your are referencing the needed Crystal Reports dlls. Add the following lines into the using directive of your form:
  
  using CrystalDecisions.CrystalReports.Engine;
  using CrystalDecisions.Shared;
  
  
• Create a report document in your code:
  
  ReportDocument rptdocEmployee = new ReportDocument( );
  
  
• Load the report into the report document:
  

rptdocEmployee.Load(rptDir + "rptEmployee.rpt")

Where rptDir is the directory where the report file you created earlier is located.


• Add the following lines of code:
  

rptdocEmployee.SetDataSource(employeeData) ;

Where employeeData is the dataset we created in Step 3.


• Finally, attach the report to the report viewer.
  

vwrEmpReport.ReportSource = rptdocEmployee;

Where vwrEmpReport is a crystal report viewer added into the form which houses your generated report.

Compile the file and execute it, the report should show up in the report viewer.

CSV To DataTable

This piece of code written in C#, takes a csv file and translates it's contents into a datatable given that the csv file follows the correct format.

The heart of this code is the regex that parses the csv file. It took me 3 months to figure out the damn regex and get the csv file to parse correctly even if there are whitespaces in it where fields should be. I'm not really an expert on regular expressions, it's a whole other field of study.

Pardon the indentations, I'm too lazy to indent them correctly but the code should be understandable enough.

public DataTable ParseCSV (string FPath, out int returnValue, out string errorMsg)
{
returnValue = 0;
errorMsg = "";
string[] rows = null;
DataTable dt = new DataTable();

dt.Clear();

try
{
StreamReader sr = new StreamReader(FPath);
string inputString = sr.ReadToEnd();

//remove delimeters from file
if ( inputString.Length > 0 )
{
char carriageReturn = "\r"[0];
char lineFeed = "\n"[0];
string trimmed = inputString.TrimEnd( carriageReturn, lineFeed );
string cleaned = trimmed.Replace( "\n", "" );

//number of records in file
rows = cleaned.Split( carriageReturn );
}

//the miraculous regular expression
Regex rex = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");

if (rows.Length > 0 )
{
for (int i=0; i < rows.Length; i++)
{
//split records into matching regex
string[] values = rex.Split(rows[i]);
if (i==0)
{
//create columns if from first line/record of file
for (int j=0; j < values.Length; j++)
{
DataColumn dc = new DataColumn();
dt.Columns.Add(dc);
}
}
DataRow dr = dt.NewRow();

//add rows, based only on number of columns from first line
for (int k=0; k < dt.Columns.Count; k++)
{

//check if values are more or less the number of columns
if (k < values.Length) { dr[k] = values[k];
}
else
{
//break the loop if length of values is less than the number of columns
//values exceeding the number of columns will not be considered
break;
}
}
dt.Rows.Add(dr);
}
//end for
}
//end if
}
catch (System.Exception e)
{
//throw new Exception("Cannot process CSV file. " + e.Message.ToString());
returnValue = 1;
errorMsg = e.Message.ToString();
}
if (dt.Rows.Count == 0)
{ returnValue = 1;
errorMsg = "CSV File is empty.";
}
return dt;
}

Hello World!

On this blog will be posted code snippets, samples, solutions or work arounds I have found useful while working as a software developer for a software development firm.

Most codes will be in c# .NET, the language I use to write most of my programs.