Crystal Report with PostgreSQL

I explain here how to show reports from PotgreSQL

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



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) { 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

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