Create a Report Using Crystal Report in Visual Studio 2010

Create a web site in VS 2010 as in the following:

Then we will create a DataSet. For this, right click on the project name in Solution Explorer and click Add New Item. The Add New Item Dialog will open. Select DataSet from the list of items. Give it's name as per your choice  and click Add.

Now we will add the table to the DataSet. On the Server Explorer right click Data Connections and then click Add Connection.

Enter your server name and select the Database.

After that drag a table to the DataSet designer and save it.

Now right click on the project in Solution Explorer and select Add New Item. Select Crystal Report from the list and add it as CustomerReport.

 

The Crystal Report Gallery will open. Select Blank Report and click OK.

Now in the field explorer window right click on Database Fields and click Database Expert.

Click the plus sign of Project Data, then ADO.NET DataSets. Select Customers under Customer. Click the add (>) button to add the table to our report and click OK.

You will get all the fields of the Customers table under the Customers option under Database Fields in Field Explorer. Drag and drop the required fields in the Section 3 (Details) part of the report and save it.

Finally drag CrystalReportViewer control from the Reporting section of the Toolbox to our webpage. 

Now go to the code behind page and write the following code in the Page_Load event.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using CrystalDecisions.CrystalReports.Engine;



public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection connection =  

new SqlConnection("Server=myserver;uid=sa;pwd=nothing;Database=MyDB;");

        SqlCommand command =  

new SqlCommand("Select top 10 * From Customers", connection);

        SqlDataAdapter adapter = new SqlDataAdapter(command);

        //Customer _Customer = new Customer();

        DataSet dataset = new DataSet();

        adapter.Fill(dataset, "Customer");

        ReportDocument CustomerReport = new ReportDocument();

        CustomerReport.Load(Server.MapPath("CustomerReport.rpt"));

        CustomerReport.SetDataSource(dataset.Tables["Customer"]);

        CrystalReportViewer1.ReportSource = CustomerReport;

        CrystalReportViewer1.DataBind();

    }

}

Now it's done. Press F5; you will get the report.

Export a Crystal Report to a PDF file Programmatically in ASP.NET

You can export a Crystal Report to various formats using CrystalReportViewer's Export option. However, there are occasions when you want your users to see the report direct in PDF format in the browser as soon they click the Report button in your application.

The ReportDocument class provides Export methods to export a report to various formats including PDF, Excel, Word, and HTML.

The following steps will guide you to achieve the same:

1. Add crystal report (.cr) file to your ASP.NET application.
2. Add a report instance on the page level.
   
   Dim report As MyReport = New MyReport

3. Populate reports data on Page_Init  
   
     ' Get data in a DataSet or DataTable

        Dim ds As DataSet = GetData()

        ' Fill report with the data

     report.SetDataSource(ds)

4. Export Report
   report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, False, "ExportedReport")

If you wish to format report to other formats, just change the ExportFormatType enumeration value to your desired format.

If you wish to download the report, then you simply change the third parameter of ExportToHttpResponse method in Step 4 to True.

The following code shows how to export a report to other formats including Word and Excel.

' Export report to the selected format

If ExportFormatList.SelectedItem.Text = "Adobe (PDF)" Then

                report.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, True, "ExportedReport")

            ElseIf ExportFormatList.SelectedItem.Text = "MS Word" Then

                report.ExportToHttpResponse(ExportFormatType.WordForWindows, Response, True, "ExportedReport")

            ElseIf ExportFormatList.SelectedItem.Text = "MS Excel 97 - 2000" Then

                report.ExportToHttpResponse(ExportFormatType.Excel, Response, True, "ExportedReport")

            ElseIf ExportFormatList.SelectedItem.Text = "MS Excel 97 - 2000 (Data Only)" Then

                report.ExportToHttpResponse(ExportFormatType.ExcelRecord, Response, True, "ExportedReport")

            End If

Generate ASP.NET Crystal Report using DataSet

This article will give a clear idea of how to create Crystal Report in ASP.NET/C#. We can create a crystal report.net using the following steps: 

1. Create a Dataset and define the schema by drag and drop the database table from Server Explorer.If there are multiple tables then put all the tables within one dataset itself.
   STEPS:Right Click Solution Explorer -> Add -> Add New Item -> choose DataSet under the Categories (Web Project Items - data).
   Add new Connection in the Server Explorer and expand the connection to retrieve the database tables and choose the required table and drag and drop it in the Dataset xsd pane.
   
2. Generate Dataset from the Dataset XSD.
   STEPS:Right click on the dataset xsd pane and click Generate Dataset
   
3. Create Crystal Report.
   STEPS:Right Click Solution Explorer -> Add -> Add New Item -> choose Crystal Report under the Categories (Web Project Items).
   
4. Configure the Crystal Report.
   STEPS: 
   1. Select Report Layout, ProjectData, ADO.NET DataSets
   2. Expand ADO.NET DataSets and select the table
   3. Select the fields
      
5. Create a WebForm and drag and drop the CrystalReportViewer control from the Toolbox(General).
   
6. Put a textbox and button
   
7. Open the Code window of the WebForm and write the following code.
   And change the value of the connectionstring variable sqlConn. 

Webform1.aspx.cs

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace CrystalReportEg

{

          /// <summary>

          /// Summary description for WebForm1.

          /// </summary>

          public class WebForm1 : System.Web.UI.Page

          {

                   protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;

                   protected System.Web.UI.WebControls.Label Label1;

                   protected System.Web.UI.WebControls.TextBox TextBox1;

                   protected System.Web.UI.WebControls.Button Button1;

                   public Customer oRpt = null;

                   private void Page_Load(object sender, System.EventArgs e)

                   {

                             // Put user code to initialize the page here

                   }

                   #region Web Form Designer generated code

                   override protected void OnInit(EventArgs e)

                   {

                             //

                             // CODEGEN: This call is required by the ASP.NET Web Form Designer.

                             //

                             InitializeComponent();

                             base.OnInit(e);

                             oRpt = new Customer();

                             GenerateReport();

                   }

                   

                   /// <summary>

                   /// Required method for Designer support - do not modify

                   /// the contents of this method with the code editor.

                   /// </summary>

                   private void InitializeComponent()

                   {    

                             this.Button1.Click += new System.EventHandler(this.Button1_Click);

                             this.Load += new System.EventHandler(this.Page_Load);

                   }

                   #endregion

                   private void Button1_Click(object sender, System.EventArgs e)

                   {

                             GenerateReport();

                   }

                   protected void GenerateReport()

                   {

                             SqlConnection sqlConn = new SqlConnection("Server=localhost;uid=sa;password=;initial

                                                                      catalog=Northwind;");

                                                         

                             SqlCommand comd;

                             comd = new SqlCommand();

                             comd.Connection = sqlConn;

                             comd.CommandType = CommandType.StoredProcedure;

                             comd.CommandText = "up_GetAllCustomer";

                             comd.Parameters.Add("@Companyname",SqlDbType.VarChar,50);

                             if(TextBox1.Text.Trim()!="")

                                      comd.Parameters[0].Value=TextBox1.Text;

                             else

                                      comd.Parameters[0].Value=DBNull.Value;

         

                             SqlDataAdapter sqlAdapter = new SqlDataAdapter();

                             sqlAdapter.SelectCommand = comd;

                            

                             Dataset1 ds = new Dataset1();

                             sqlAdapter.Fill(ds, "Customers");

                            

                             oRpt.SetDataSource (ds);

                                               

                             CrystalReportViewer1.Visible=true;

                   

                             CrystalReportViewer1.ReportSource = oRpt;

                   }

          }

}

How to pass SQL Query Parameters to Crystal Reports using Oracle

Introduction

This article gives an idea as how to use Crystal report in .Net Windows Application with parametrized queries and Oracle as Database using dataset.I had searched on 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 on how to Use Crystal Report with Oracle. There are many methods to do this job, even we can create Oracle packeges which will return mutiple 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 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 query to have sample data
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 off all create a project in Microsoft Visual Studio 2005 and name it CrystalReportWithOracle. Then add a crystal report to it as shown below.


Step 2:
Then from the available data sources choose OLEDB(ADO) as below.


Then Select the Provider as Oracle Provider for OLEDB as in the image.


Then Provide the necessary information for databse login as per your oracle Configurations.


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.



Step 4:
Now add a Empty DataSet to your project.


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.
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 required parameters. This function resides in the DLApplication class.

The main function which is binding the crystal report is given below, when ever the user changes his choice from the combo box a new report is generated for the selected combo value.
//   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)
            bjRpt.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.

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();            }        }    }}