How to populate Crystal Reports with SQL

Question

Since there doesn't seem to be any way to load old VB6 (ActiveReports) reports in .Net, I need to recreate dozens of reports in .Net. I'd like to do this in the least painful way possible.

In VB6, the original authors simply did something like this for every report:

adoConn.ConnectionString = globalConnectionObject.ConnectionString
adoConn.Source = ReportFunctions.GetAllUsers()

GetAllUsers() returns an SQL string which selects a bunch of fields; those fields are then used in the report.

Now:
How can I do something similar in .Net (either using the built-in Crystal Reports, or the built-in "Microsoft Reporting Technology")?

I can't get "Database Expert" to recognize globalConnectionObject (an ADODB.Connection object); and if I fill a dataset and do

report.SetDataSource(dataSet)

It tells me "The report has no tables."

How do I populate a Crystal Reports report!? (the connection string/data location is not known at compile time)

Answer 1

We do something similar using Crystal Reports, and it has caused me to really dislike Crystal Reports. Using the database expert I created a new connection to the database and a new command with the SQL that I want to use. This defines the columns that Crystal Reports can use and allows you to create the report. Then when displaying the report we do:

ReportDocument rd = new ReportDocument();
rd.Load(MapPathSecure("NameOfMyReport.rpt"));
rd.SetDataSource(dataSet.Tables[0]);

I have only done it with one table, so I don't know how having multiple tables in the DataSet would affect it. Basically, you set the structure of the report with the Database Expert and the command. Then you overwrite the actual data at runtime. I really wish that Crystal Reports had a more robust way of doing this, or that I understood it if it exists.

Answer 2

Creating Connection:

/// <summary>
/// Sets the connection.
/// </summary>
public void SetConnection()
{
    _connection = new SqlConnection(Settings.Default.connectionString);
    if (_connection.State == System.Data.ConnectionState.Open)
    {
        _connection.Close();
    }
    _connection.Open();
}

/// <summary>
/// Closes the connection.
/// </summary>
public void CloseConnection()
{
    if (_connection.State == System.Data.ConnectionState.Open)
    {
        _connection.Close();
        _connection.Dispose();
    }
}

using above connection Populate dataset manually or Add a dataset to your solution to get data without writing connection codes. Say You have added Employee.XSD. Add a tableadapter in the XSD which will help you in pulling data from database, by auto generating queries and datatables. After doing all these things. Create a method somewhere in your project,

Public DataTable GetAllEmployees()
{
    Employee.EmployeeTableAdapter adapt = New Employee.EmployeeTableAdapter();
    DataTable dt = New DataTable();
    dt =    adapt.GetData();   // you can also use fill based on your criteria.
    return dt;   //your datatable with data
}

Now on Your Form add a reportviewer control.

ReportViewer1 rpt = New ReportViewer();
ReportDocument rptDoc = new ReportDocument();
rptDoc.Load("path of rpt file");
rptDoc.SetDataSource(GetAllEmployees());
rpt.Document =  rptDoc;
rpt.Refresh();

Before that From Crystal Report , add the fields of table on the report as per your requirement.

Another Way To achieve

Crystal Reports can be used over various objects. If you have scenario to bind it dynamically, see my answer below Then u can do one thing that is , add a new dataset to solution. Create a datatable and add required columns with appropriate data type. DO not add query or table adapter. Now from your code add a class file and create properties exactly similar to the datatable columns. Now set the datatable as source to report and add its column on the report.

For example , if you have columns
ID - integer
EmpName - string
Salary - double
Department - string

Create a class

public class Employee
{
  private SqlConnection _connection;
  public int ID {get;set;}
  public string EmpName {get;set;}
  public double Salary {get;set;}
  public string Department  {get;set;}



/// <summary>
/// Sets the connection.
/// </summary>
public void SetConnection()
{
    //assuming connection string is placed in settings file from Project Properties.
    _connection = new SqlConnection(Settings.Default.connectionString);
    if (_connection.State == System.Data.ConnectionState.Open)
    {
        _connection.Close();
    }
    _connection.Open();
}

/// <summary>
/// Closes the connection.
/// </summary>
public void CloseConnection()
{
    if (_connection.State == System.Data.ConnectionState.Open)
    {
        _connection.Close();
        _connection.Dispose();
    }
}


  public DataTable GetEmployees()
  {
       DataTable dt = new DataTable("Employee");
       // using above connection
       SetConnection();
      using(SqlCommand command = new SqlCOmmand("commandText",_connection))
      {
         using(SqlDataReader reader = command.ExecuteReader())
         {
              dt.Load(reader);
         }
      }
       return dt;
  }
}

Now create another function that will populate the datatable created inside the dtataset.

public void PopulateDataTable()
{
      DataTable dt = GetEmployee();
      Employee dsEmployee = New DataSet();
      dsEmployee.EmployeeDataTable dtEmp = new dsEmployee.EmployeeDataTable();
      dtEmp = dt;
}