Create Crystal Report from DataSet using ASP.NET and MySql


Web based reporting is very necessary tools. Now popular reporting tools Crystal Report is integrated with Visual Studio 2005 and later. It makes programmers life little bit easier. But there are some complexity when you want to generate web report in VS2005. I am totally failure to generate web report from design view created DataSet. May be there exists some bugs.

Now i can generate any kind of web report using Crystal Report including cross tab reporting just changing the strategy. Here i try to describe my strategy of report generation.

Step 1: Create dataSet of the whole database. Then save this dataset as XML format in the disk.

public void CreateDS()
{
MySqlDataReader reader = null;
DataSet rptDS = new DataSet();
string tblName;
MySqlConnection myCon;

//Used previous crated Connection
myCon = this.dbCon;

MySqlCommand cmd = new MySqlCommand("SHOW TABLES", dbCon);
try
{
reader = cmd.ExecuteReader();

while (reader.Read())
{
tblName = reader.GetString(0);
//Fill DataSet
this.FillDSWithTable("select * from " + tblName, tblName, ref rptDS, myCon);

}
// Write Dataset to disk AS XML
rptDS.WriteXml("E:/MyDataSet.XML", XmlWriteMode.WriteSchema);
lblMsg.Text = "Completed ....";
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine("Failed to populate table list: " + ex.Message);
}
finally
{
if (reader != null) reader.Close();
}

public void FillDSWithTable(string sqlStr, string dsTblName,ref DataSet rptDS, MySqlConnection dbCon)
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, dbCon);
da.Fill(rptDS, dsTblName);

}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
}//end FillDSWithTable

Step 2: Now design Report using newly crated XML. I want to use XML data source.
a. Add Crystal report in your ASP.NET project.
b. Select Standard option
c. Create New ADO.NET Connection

d. Select Table

e. Select Fields

f. Press Finish. Then Report is generated

Step 3: Now Display this report in ASP.NET page. This time you need to change the data source of the Report file in the run time by Creating new data set. you also need Crystal Report Viewer to display the report.
a. Add a New aspx page
b. Add Crystal report Viewer in that page
c. Write below code in code behind page

protected void Page_Load(object sender, EventArgs e)
{
dbCon = DALMysql.Connection;
CreateReport();
}
public void CreateReport()
{
ReportDocument myReport = new ReportDocument();</code>

DataSet rptDS = new DataSet();
MySqlConnection temCon;
temCon = this.dbCon;
//load Report
myReport.Load(MapPath("~/" + "CrystalReport.rpt"));

//Create Dataset
//here we use single table. if there mutiple table we need to use loop for every table
this.FillDSWithTable("select * from MyTable", MyTable, ref rptDS, temCon);

//Set Report Data Source
//here we use single table. if there mutiple table we need to use loop for every table
myReport.Database.Tables[0].SetDataSource(rptDS.Tables[myReport.Database.Tables[0].Name]);

CrystalReportViewer1.ReportSource = myReport;
}//end Create Report

public void FillDSWithTable(string sqlStr, string dsTblName,ref DataSet rptDS, MySqlConnection dbCon)
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, dbCon);
da.Fill(rptDS, dsTblName);

}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
}//end Func

Add two reference at top the page

using MySql.Data.MySqlClient;
using CrystalDecisions.CrystalReports.Engine;

d. Run the page.

You can create report using ODBC connection following same approach. But, it will create many unwanted problem specially when you want to use cross tab reporting.
You can check following links for more help,
http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples-tools-with-crystalreports.html
http://www.codeproject.com/KB/database/Le_Quang_Anh.aspx

If this post do any help, please comments.

Thanks
Khayer
GIS Programmer, CEGIS
Bangladesh

Advertisements

12 thoughts on “Create Crystal Report from DataSet using ASP.NET and MySql

  1. I found your site on technorati and read a few of your other posts. Keep up the good work. I just added your RSS feed to my Google News Reader. Looking forward to reading more from you down the road!

  2. It’s OK…
    May be you can get better alternative.
    But this technique is useful for every database, specially for Mysql.

  3. I am really impressed with your writing skills
    and also with the layout on your weblog. Is this
    a paid theme or did you customize it yourself?
    Either way keep up the excellent quality writing, it’s rare to see a great blog like this one today.

  4. Excellent blog right here! Additionally your website
    quite a bit up very fast! What host are you the usage of?
    Can I get your associate hyperlink for your host?
    I want my site loaded up as fast as yours lol

  5. I don’t even know the way I finished up right here,
    however I believed this publish was good. I don’t recognize who you’re but certainly you’re going to a
    famous blogger if you are not already. Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s