Crystal Report with PostgreSQL

Posted by Mohammed Thabet Articles | Crystal Reports C# August 25, 2009
I explain here how to show reports from PotgreSQL
Reader Level:


You can get the first steps in developing with PostgreSQL using tutorial "Using PostgreSQL from Microsoft.NET" by John Charles Olamendy
I used here Npgsql.Net provider to connect to PostgreSQL

Getting started with the solution

I want here to show reports from postgreSQL using DataSet that can connect to any DB as Crystal Report only has a limited DataSources
I assumed that you have DB with 2 tables users & cars

Using the code
Creating Dataset


Create dateset DataSetGeo using Right Click Solution Explorer -> Add -> Add New Item -> choose DataSet under the Categories
then create DataTable called "CarsDT" contains the columns as in the figure

Creating report
Right Click Solution Explorer -> Add -> Add New Item -> choose Crystal Report
Standard->from available datasources choose (Project Data-ADO.Net DataSets-DataSetGeo-CarsDT) then move it to selected tables
Move all available fields to fields to display

Create Connection to PostgreSQL
I create "BaseDAO" class that can handle all communications between your application & PostgreSQL

 public void connect2DB()
npgConnection = new NpgsqlConnection("Server=localhost;UID=postgres;PWD=123;Database=GeoWebsite;Port=5432;");


public DataTable SelectionQuery(string query)
IDbDataAdapter daPgsql = new Npgsql.NpgsqlDataAdapter(query, npgConnection);
DataSet dsPg = new DataSet();


DataTable dtPg = dsPg.Tables[0];
return dtPg;

I create at BaseDAO 2 main methods

1)Connect2DB : that open a connection to your GeoWebsite DB
2)SelectionQuery: that get SQL query as a parameter & return data in dataTable

Create Form
Drag & drop ComboBox name it with cmbUsers
Drag & drop CrystalReportViewer

 public Form1()
DataTable DTUsers = myBaseDAO.SelectionQuery("Select username from \"NonGeometrySchema\".users");
for (int i = 0; i < DTUsers.Rows.Count; i++)
In method Form1(),I fill cmbUsers with all available users .
CrystalDecisions.CrystalReports.Engine.ReportDocument myReportDocument;  
private void cmbUsers_SelectedValueChanged(object sender, EventArgs e)
myReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

DataTable myDT = myBaseDAO.SelectionQuery("Select cardrivername,cardrivermob,gpsno,carname,carmotorcc,carfuelrate,carcode from \"NonGeometrySchema\".cars WHERE userid = (SELECT userid from \"NonGeometrySchema\".users as users where users.username='" + cmbUsers.SelectedItem.ToString() + "')");


crystalReportViewer1.ReportSource = myReportDocument;

then in event SelectedvalueChanged of cmbUsers,I show the report of specified user you selected from cmbUsers using simple steps
1-create new ReportDocument
2-Using selctionQuery method ,I send SQl query to select all cars of specified user
3-Load report document from its location
4-Set DataSource of reportDocumenet to the return DataTable from SelectionQuery method
5-Set reportSource of CrystalReport Viewer to my custom reportDocument
6-Refresh CrystalReportViewer to show report

I wish to solve the problem of connecting CrystalReport with PostgreSQL in clear
I used here general way to solve this problem , also this way can make a connection between any DB to Crystal report
This way is more efficient when generate report in runtime