Click here to Skip to main content
15,393,863 members
Articles / Programming Languages / C#
Article
Posted 3 Aug 2008

Stats

130.9K views
2.8K downloads
36 bookmarked

Using Crystal Report with Oracle and Parametrized Query (Passing SQL Query Parameters to Crystal Reports)

Rate me:
Please Sign up or sign in to vote.
4.38/5 (13 votes)
3 Aug 2008CPOL3 min read
Using Crystal Report with Oracle and parametrized Query (C#.NET Windows application). Passing SQL query parameters to Crystal reports

Crystal_Reportwith_net/main.JPG

Introduction

This article gives an idea of how to use Crystal report in .NET Windows Application with parametrized queries and Oracle as Database using dataset. I searched on the internet for articles related to Crystal Report with parametrized query with Oracle, but I was unable to find any good article on this topic, so I am posting my article on this topic.

There are three main points in this article:

  1. Crystal Report with parametrized query
  2. Using Oracle Views because here stored procedure will not be able to do this job because of the fact that Oracle Stored Procedure does not return Multiple Rows (Record Set) as SQL Server Stored Procedures do
  3. Using DataSet

Hence, I have used Oracle views in this project to bind the Crystal Report and Dataset. Then I wrote my parametrized query on the view as we do generally on database tables. So this project will give a good idea of how to Use Crystal Report with Oracle. There are many methods to do this job, even we can create Oracle packages which will return multiple records as SQL Server Stored Procedures. But I found this way simple and fast to do.

Background

No special background knowledge is needed for this article. Any beginner or intermediate developer will be able to understand this code. It will be good if you have a basic knowledge of database views.

Using the Code

I have created 2 sample tables and a view for this project. Scripts of tables and view are as follows, with some sample insert queries to have sample data

SQL
create table tbl_project
(
PROJECT_ID NUMBER(4),
PROJECT_NAME  VARCHAR2(150),
GROUP_CODE  NUMBER(2)
)

create table tbl_project_group
(
GROUP_CODE  NUMBER(2),
GROUP_NAME  VARCHAR2(100)
);

create view view_project as
select a.PROJECT_NAME "PROJECT_NAME",b.GROUP_NAME "GROUP_NAME",
a.GROUP_CODE "GROUP_CODE"
from tbl_project a,tbl_project_group b where
a.GROUP_CODE=b.GROUP_CODE;

insert into tbl_project values(1,'CrystalReportWithOracle',1);

insert into tbl_project values(2,'Ajax Application',2);

insert into tbl_project_group values(1,'windows application');

insert into tbl_project_group values(2,'Web application');

Step 1

First of all, create a project in Microsoft Visual Studio 2005 and name it CrystalReportWithOracle. Then, add a Crystal report to it as shown below:

Crystal_Reportwith_net/2-1.JPG

Step 2

Then from the available data sources, choose OLEDB(ADO) as below:

Image 3

Then, select the Provider as Oracle Provider for OLEDB as in the image:

Image 4

Then, provide the necessary information for database login as per your Oracle configurations.

Image 5

Step 3

Then, from the available data source, add your view which you have created before to the selected table Container and add your fields which will be displayed in the report as shown below:

Image 6

Image 7

Step 4

Now add an Empty DataSet to your project.

Image 8

Then, you add a Crystal Report Viewer Control from the tool box to your form.

There is a class file DLApplication.cs for the sake of database connectivity. Here, you can change your connection string as per your Oracle configuration. If you are creating your own connectivity in the form itself, you can ignore this file.

There is a Combo Box in which the user can select his choice of project type so that the application generates a dynamic report on the user selection. Code to fill the combo box is as follows:

SQL
String Query = "Select GROUP_CODE,GROUP_NAME from  tbl_project_group order by GROUP_CODE ASC";
DLApplication oDl = new DLApplication();
OracleConnection Conn = oDl.GetCon();
DataView dv = oDl.getDataView(Query, Conn);

cmb_type.DataSource = dv;
cmb_type.ValueMember = "GROUP_CODE";
cmb_type.DisplayMember = "GROUP_NAME";
cmb_type.SelectedIndex = -1;

In the above code, getDataView() function is called with the required parameters. This function resides in the DLApplication class.

The main function which is binding the Crystal report is given below, whenever the user changes his choice from the combo box, a new report is generated for the selected combo value.

SQL
//         

   private void cmb_type_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (Convert.ToInt32(cmb_type.SelectedIndex) == -1 || 
(Convert.ToString(cmb_type.SelectedValue) == "System.Data.DataRowView"))
            {
                return;
            }
            CrystalReport1 objRpt;
            objRpt = new CrystalReport1();

            String ConnStr = "SERVER=newsdb;USER ID=ppms;PWD=ppms";

            OracleConnection myConnection = new OracleConnection(ConnStr);
// Here I am writing my query over the view
// we cannot write query directly over the tables because it will be a 
// join query and we will not be able to fill our adapter easily.
            string Query1 = "select PROJECT_NAME,GROUP_NAME from view_project
 where GROUP_CODE=" + cmb_type.SelectedValue;

            OracleDataAdapter adapter = new OracleDataAdapter(Query1, ConnStr);
            DataSet1 Ds = new DataSet1();

            adapter.Fill(Ds, "view_project");

            if (Ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("No data Found", "Project Tracker Suite");
                return;
            }

            objRpt.SetDataSource(Ds);

            CrystalDecisions.CrystalReports.Engine.TextObject root;
            root = (CrystalDecisions.CrystalReports.Engine.TextObject)
                    objRpt.ReportDefinition.ReportObjects["txtHeader"];
            root.Text = "Sample Report With Parameter!!";

            crystalReportViewer1.ReportSource = objRpt;
        }
    }
//

The DLApplication.cs file contains function which returns Oracle Data View, Oracle Data Reader, etc. You can have a look at the class file.

SQL
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace CrystalReportWithOracle
{
    public class DLApplication 
    {
        private const String My_name = "  DLApplication : ";
        private static String m_sConStr = "SERVER=yourdB;USER ID=user1;PWD=pass";
        private int userId;

        public int propertyUserId
        {
            get
            {
                return userId;
            }
            set
            {
                userId = value;
            }
        }

        public OracleConnection GetCon()
        {
            try
            {
                OracleConnection sqlcon = new OracleConnection(m_sConStr);
                return sqlcon;
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException(My_name + " GetCon: " + ex.Message);
            }
        }

        public OracleDataReader GetSqlReader(String Sql, ref OracleConnection con)
        {
            try
            {
                OracleCommand objOraCmd = new OracleCommand();
                OracleDataReader objOraDrRead;
                objOraCmd.Connection = con;
                objOraCmd.CommandType = CommandType.Text;
                objOraCmd.CommandText = Sql;
                if (con.State != ConnectionState.Open) con.Open();
                objOraDrRead = objOraCmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objOraDrRead;
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException(My_name + " GetSqlReader: " + ex.Message);
            }
        }

        public void CloseCon(ref  OracleConnection thisCon)
        {
            try
            {
                if (thisCon.State != ConnectionState.Closed)
                    thisCon.Close();
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException(My_name + " CloseCon: " + ex.Message);
            }
        }

        public void ExecNonQuery(String sQuery)
        {
            OracleConnection objCon = new OracleConnection(m_sConStr);
            OracleCommand objCmd;
            try
            {
                objCon.Open();
                objCmd = objCon.CreateCommand();
                objCmd.CommandText = sQuery;
                objCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException(My_name + " ExecNonQuery : " + ex.Message);
            }
            finally
            {
                if (objCon.State == ConnectionState.Open)
                    objCon.Close();
            }
        }

        public DataView getDataView(String Query, OracleConnection Conn)
        {
            try
            {
                OracleDataAdapter oDa;
                DataSet ds;

                oDa = new OracleDataAdapter(Query, Conn);
                ds = new DataSet();
                oDa.Fill(ds);
                if (Conn.State != ConnectionState.Closed)
                    Conn.Close();
                return (ds.Tables[0].DefaultView);
            }
            catch (Exception ex)
            {
                throw new System.ApplicationException(My_name + " getDataView : " + ex.Message);
            }
            finally
            {
                if (Conn.State != ConnectionState.Closed)
                    Conn.Close();
            }
        }

    }
}

Points of Interest

Oracle Stored procedures do not return multiple records, we can use package instead, but here, I have used Views which is much more easy and simple to understand.

Soon, I will post my articles on other topics such as:

  1. Posting data To HTTPS (https, i.e., Secure Connection) URL from a Windows application(.NET) by attaching Digital Certificates and getting the response back
  2. Ajax Techniques

History

  • I will update this article to give a clearer picture on this topic.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rehan Ahmad Abbasi
Software Developer (Senior) Al-Jazirah Corporation Riyadh KSA
United Arab Emirates United Arab Emirates
I am a Software Engineer having 4 + years of experience in various skill sets.
Proficiency in Asp.net, C#.net, Vb.net, Ado.net, VB 6.0, J2ME, Ajax, Xslt, Xml, Smart Device (Pocket PC 2003), and Oracle.

Extensive experience with analyzing, designing, development, and maintenance of Internet, Intranet, Client Server and Object Oriented applications built on .NET Framework (windows and web app.) and VB 6.0.

Comments and Discussions

 
QuestionUsing Crystal Report with Oracle and parametrized Query (view, stored procedure, or function) Pin
vbwrangler27-Mar-13 5:52
Membervbwrangler27-Mar-13 5:52 
GeneralMy vote of 4 Pin
hemantwithu17-May-11 18:52
Memberhemantwithu17-May-11 18:52 
Generalcrystal report in c#.net+cracle Pin
khushnood.abbas26-Mar-10 2:16
Memberkhushnood.abbas26-Mar-10 2:16 
Generaloracle backup Pin
pentium40426-Jun-09 19:04
Memberpentium40426-Jun-09 19:04 
QuestionHow can the lines of the report becomecloser? Pin
joseccz4-May-09 10:39
Memberjoseccz4-May-09 10:39 
GeneralPlease Vote for this article Pin
Rehan Ahmad Abbasi26-Aug-08 3:22
MemberRehan Ahmad Abbasi26-Aug-08 3:22 
QuestionCrystal Report Viewer Licensing/Royalties Pin
Member 43791665-Aug-08 20:26
MemberMember 43791665-Aug-08 20:26 
AnswerRe: Crystal Report Viewer Licensing/Royalties Pin
Rehan Ahmad Abbasi6-Aug-08 21:40
MemberRehan Ahmad Abbasi6-Aug-08 21:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.