ARTICLE

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:
 

Introduction

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

1.JPG

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
2.JPG
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)
{
npgConnection.Open();
IDbDataAdapter daPgsql = new Npgsql.NpgsqlDataAdapter(query, npgConnection);
DataSet dsPg = new DataSet();

daPgsql.Fill(dsPg);


DataTable dtPg = dsPg.Tables[0];
npgConnection.Close();
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
3.JPG
Drag & drop ComboBox name it with cmbUsers
Drag & drop CrystalReportViewer

 public Form1()
{
InitializeComponent();
myBaseDAO.connect2DB();
DataTable DTUsers = myBaseDAO.SelectionQuery("Select username from \"NonGeometrySchema\".users");
for (int i = 0; i < DTUsers.Rows.Count; i++)
{
cmbUsers.Items.Add((string)DTUsers.Rows[i][0]);
}
}
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() + "')");

myReportDocument.Load(@"E:\CrystalReportWithPotsgreSQL\CrystalReportWithPotsgreSQL\Reports\ReportCarsOfSpecifiedUser.rpt");

myReportDocument.SetDataSource(myDT);
crystalReportViewer1.ReportSource = myReportDocument;
crystalReportViewer1.Refresh();

}
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


Conclousion
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

Employers - Post Free Jobs