Crystal Reports & Stored Proc in Visual Studio

Today I’m going talk about the reporting issues which I have been faced. In .net we can design crystal report in different ways. Using XML Schema, Dataset component some of methods which I’ve used, are much difficult to maintain. Suppose you have to change the report field, then you have to change xml or dataset by manually and after that refresh the report. Other disadvantage is, we have to provide the data source (DataTable,DataSet or List of objects)

Here is the eg.

this is method return data to display . tblReport datatable column names should be match with the xml or dataset columns names (mapping).

DataTable tblReport=GetAllDetails();

ReportDocument rptDoc = new ReportDocument();

rptViewWorkOrderDetailsWithYC rpt = new rptViewWorkOrderDetailsWithYC();

rptDoc = rpt;

rptDoc.SetDataSource(tblReport);

rpt.SetParameterValue("@@@Company", "cc");//Pameters passing to the report

rpt.SetParameterValue("@@@Heading", "cccc”);

crystalReportViewer1.ReportSource = rptDoc;  //Display report in Report Viewer

Above code is well running code, but it has to lot of mapping things.

with Proc as data source:

Design crystal reports with stored procedure are more efficiency way to build reports. It is not needed any data sources. We can use ado data provider to access the store proc in database. If proc has any parameters then it will be added automatically to report.

here are the steps :

Step 1:Make the connection to the crystal report.add crystal report to project and using selection expert set the connection (ole db - ado).

Step 2:after completing step 1, then (Assume we already have created a proc in database)add proc to report.         

Developer PC1 and DatabaseServer DevelopementServer1 (discuss later )

Test is the SP that i used.

create proc [dbo].[Test]

@UserName varchar(50)

as

select 'MYPC ' + @UserName as test

In Test sp has parameter as @Username, it will be automatically added to the report.

 report desining part is done.here code snippet to run the report.

        ReportDocument rptDoc = new ReportDocument();

        Report.TEST rpt = new Report.TEST();

        rptDoc = rpt;

        rpt.SetParameterValue("@UserName", "cc");//Pameters passing to the report

        crystalReportViewer1.ReportSource = rptDoc;

how easy it is.no any column mapping & only need proc that's all.this will be displayed report as you wished in production (in pc1 with DevelopementServer1 )

when this report is hosted into live system , there no errors but data will be wrong .because it will be displayed records in pc & DevelopementServer1 machines.

here is the issue.

PC01 is the pc which is developed, remember report is added to proc in this (pc01)machine database.now it has changed to new server.got the point.

now it is needed to change the server name as according to environment.in .net windows or web application we used file called app or web configuration file to share common-thing through out application.database connection is usually stored in this config file , we change it according to development environment.

  <connectionStrings>

    <add name="con" connectionString="Data Source=pc01;Initial Catalog=Teset;User ID=sa" providerName="System.Data.SqlClient" />

  </connectionStrings>

how do we overcome the these situation in crystal report with proc in .net.

simple thing, have to change the connection string in reprot dynamically according to config file connection string.

        #region change the report data server according to app.config

        ConnectionInfo getConnectionInfo()

        {

            ConnectionInfo ConnInfo = new ConnectionInfo();

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString());

            ConnInfo.UserID = "sa";

            ConnInfo.Password = "";

            ConnInfo.ServerName = con.DataSource;

            ConnInfo.DatabaseName = con.Database;

            return ConnInfo;

        }

        private void SetDBLogonForReport(ReportDocument reportDocument)

        {

            ConnectionInfo connectionInfo = getConnectionInfo();

            Tables tables = reportDocument.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

            {

                TableLogOnInfo tableLogonInfo = table.LogOnInfo;

                tableLogonInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(tableLogonInfo);

            }

        }

        #endregion

here are the two method it changes the connection using connection in config file.

before set the crystalreportviewer1 report source add SetDBLogonForReport with report document parameter.

            SetDBLogonForReport(rptReportDocument);

            crystalReportViewer1.ReportSource = rptReportDocument;

that's all.hope this will help to all guys.

happy coding...