C# Crystal Reports from SQL Query

The following section describes how to create a Crystal Reports in C# using SQL Query String .

All C# Crystal Reports Tutorial in this website is based on the following database - crystaldb. So before you begin this section , please take a look at the database structure of crystaldb - Click Here C# crystaldb

If you are new to Crystal Reports and do not know how to create Crystal Reports from C# , please take a look at the section step by step tutorial for creating a Crystal Reports from C#.

Generating a Strongly Typed DataSet

Here we are using a Strongly Typed Dataset for generating a Crystal Reports from SQL Query string . Before start this section , you can take look at the previous section of how to create a C# Crystal Report from Strongly Typed Dataset. Because here we are using Strongly Typed Dataset for generating Crystal Report from SQL Query string.

Here we are generating a report against the Product table . So we are passing the following sql and generating the report

sql = "SELECT Product_id,Product_name,Product_price FROM Product";

Create a new C# project and create a new Strongly Typed Dataset from Project - Add New Item Dialogue Box.

Add three column in the Strongly Typed Dataset .

Product_id
  Product_name
  Product_price

Create a new Crystal Report and select DataTable as Data Source . You can select DataTable from the wizard , Project Data - ADO.NET Dataset - Crystal report Dataset1 - dataset1. Click the Next Button.

Select fields ( Product_id , Product_name , Product_price ) from the next screen and click the finish button. Then you will get the designer screen with the selected fields. If you do not know how to create a dataset , refer the previous section How to create a C# Crystal Report from Strongly Typed Dataset.

Now the designing part is over. From the source code we can pass the SQL source code to Crystal Reports.

Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using System;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.SqlClient ;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn ;
            string connectionString = null;
            string sql = null;

            connectionString = "data source=SERVERNAME;initial catalog=DATABASENAME;user id=USERNAME;password=PASSWORD;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = "SELECT Product_id,Product_name,Product_price FROM Product";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet1 ds = new DataSet1();
            dscmd.Fill(ds, "Product");
            MessageBox.Show (ds.Tables[1].Rows.Count.ToString());
            cnn.Close();

            CrystalReport1 objRpt = new CrystalReport1();
            objRpt.SetDataSource(ds.Tables[1]);
            crystalReportViewer1.ReportSource = objRpt;
            crystalReportViewer1.Refresh();
        }
    }
}

connectionString = "data source=SERVERNAME;initial catalog=DATABASENAME;user id=USERNAME;password=PASSWORD;";
You have to provide the necessary database information to Connection String.