C# .NET - crystal report using sql stored procedure

Asked By saran vasan on 11-Feb-12 11:56 PM
how to create crytal report using dataset  for sql stored procedure in asp.net with c sharp
D Company replied to saran vasan on 12-Feb-12 12:16 AM
Hello Saran,

Here are the steps given below to do all this.

1.)you want to use SP  in crystal report,

when you design your crystal report you must have to set datasource and dataset for the report.set your datasource as stored procedure, from the wizard.and that will return set of records . that you can configured according to your need.

2.)you want to populate reports using asp .net

drag and drop a crystal report control on your page.

click on database expert wizard and set the properties than select SQL Native client and enter you SQL server address , username , password and pick database name from the dropdown. 

Now design the report , drag and fields from Database fields in field explorer and which you want to show in report and drop them .


Now to view the report drag and drop CrystalReportViewer from the toolbox, click on smart tag and choose new report source.


Note: dont forget to give credentials on page load.  something like this

protected void Page_Load(object sender, EventArgs e)
    {
      ReportDocument myCrystalReport = new ReportDocument();
      crystalReport.Load(Server.MapPath("CrystalReport.rpt"));
      crystalReport.SetDatabaseLogon
        ("urUsernamehere", "SeturPassword", "ServerName", "DataBaseName");
      CrystalReportViewer1.ReportSource = mycrystalReport;
    }


Hope its clear to you!!
Regards
D
Sandeep Mittal replied to saran vasan on 12-Feb-12 01:03 AM
Refer below posts

http://www.codeproject.com/Articles/28899/Crystal-Report-with-DataSet-and-DataTable-using-C
http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-without-database.htm
http://www.c-sharpcorner.com/UploadFile/rsubhajit/CrystalReportwithDataSet03012006060655AM/CrystalReportwithDataSet.aspx?ArticleID=5c41229b-0d00-4cde-a9e2-c10a917c6521
kalpana aparnathi replied to saran vasan on 12-Feb-12 04:52 AM
hi,

Try below code for example to you for creating report using sql stores procedure

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.SqlClient;
 
   
  protected void Page_Init(object sender, System.EventArgs e) {
    ConfigureReport();
  }
   
  private void ConfigureReport() {
    if (!Page.IsPostBack) {
      ConnectionInfo myConnectionInfo = new ConnectionInfo();
      myConnectionInfo.DatabaseName = DatabaseName;
      myConnectionInfo.UserID = userid;
      myConnectionInfo.Password = password;
      string ReportPath = Server.MapPath("YourReport.rpt");
      ReportViewer1.ReportSource = ReportPath;
      ParameterField field1 = this.ReportViewer1.ParameterFieldInfo(0);
      ParameterField field2 = this.ReportViewer1.ParameterFieldInfo(1);
      ParameterDiscreteValue val1 = new ParameterDiscreteValue();
      ParameterDiscreteValue val2 = new ParameterDiscreteValue();
      val1.Value = "value1";
      val2.Value = "value2";
      field1.CurrentValues.Add(val1);
      field2.CurrentValues.Add(val2);
      SetDBLogonForReport(myConnectionInfo);
    }
  }
   
  private void SetDBLogonForReport(ConnectionInfo myConnectionInfo) {
    TableLogOnInfos myTableLogOnInfos = ReportViewer1.LogOnInfo();
    foreach (TableLogOnInfo myTableLogOnInfo in myTableLogOnInfos) {
      myTableLogOnInfo.ConnectionInfo = myConnectionInfo;
    }
  }

Create your parameterized stored procedure in sql server
 
Sample stored procedure:
 
CREATE PROCEDURE sp_YourProcedure
 
@value1 int,
@value2 int
 
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Table WHERE column1=@value1 and column2=@value2
    
END
Suchit shah replied to saran vasan on 13-Feb-12 01:57 AM

 

Step 1: Right click the solution Explorer and click on add new item
 
Step 2:Add a Crystal Report from the Dialogue box.
 
Step 3:Select the Radio Button(using the Report Wizard) from the Crystal Reports Gallery and choose a Standard Expert and click OK button.
 
Step 4:Here we have to add Database..Select the Create New connection from the available Datasources window and click OLEDB(ADO)
 
Step 5:select A new connection and then select a provider from the list (Microsoft OLEDB Provider for SQL Server)
 
Step 6:Enter the server name and select the Database and click the Integrated Security and press next and click finish button.
 
Step 7:Now,come back to Standard Report Creation Wizard and select the Table from the Database.
 
Step 8:Select the required fields to display in the Crystal Report from the table
 
Step 9:Select the style from the availble styles window (Ex:Standard) and Click Finish
 
Step 10:Now we have to add one WebForm from Add New Item and press Add.
 
Step 11:Here,we have to import two references that is
 
CrystalDecisions.CrystalReports.Engine
 
CrystalDecisions.Shared
 
Step12:Now,add one Crystal Report Viewer from the Toolbox
 
Step13:Now we have to import the following namespaces in the defalut.aspx.cs
 
using CrystalDecisions.CrystalReports.Engine;
 
using CrystalDecisions.Shared;
 
using System.Data.SqlClient;
 
Step14:Declare the variable ReportDocument report = new ReportDocument();
 
Step15:Next,write the following code in the page Load Event
 
pt.Load(@"E:\practices\update\emp_details\crystalreports\CrystalReport.rpt");
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.RefreshReport();

Below link also explain you step by step how to create a Crystal report

http://csharpdotnetfreak.blogspot.com/2009/07/creating-crystal-reports-in-aspnet.html
http://www.c-sharpcorner.com/UploadFile/rsubhajit/CrystalReportwithDataSet03012006060655AM/CrystalReportwithDataSet.aspx 
http://www.codeproject.com/KB/cs/Crystal_Report.aspx 
http://www.aspdotnet-suresh.com/2011/11/how-to-create-crystal-reports-in-aspnet.html