I described how to create Crystal Report.
In this article, I am going to demonstrate how to create a Crystal Report with ADO.NET DataTable.
You will find this article very interesting, simple and easy to understand.
download SAP Crystal Report for Visual Studio
-------------------------------------------------------------------------------------------
Step 1 : Add a DataSet and DataTable
Change the datatype of the DataTable columns as shown below :
Now save this DataTable as "ds_FORMORDER" and "ds_GROUP_FORMORDER"
Step 2 : Adding A Crystal Report
Choose the data source from project data which is "ds_FORMORDER" and "ds_GROUP_FORMORDER"
Step 3 : Blinding our Report to our DataSource.
Below is the C# code to bind our report to the datasource.
protected void FillData(string strsql)
{
SqlDataAdapter da;
DataSet ds = new DataSet();
// object for sql
UseSQL us = new UseSQL();
// Creating object of our report.
Research_Age dsCustomers = new Research_Age();
// open connection
us.OpenConnectionMethod();
da = us.ExecuteByDataAdapter(strsql);
// here my_dt is the name of the DataTable which we
da.Fill(dsCustomers, "ds_GROUP_FORMORDER");
// close connection
us.CloseConnectionMethod();
// Set Report
ReportDocument doc = new ReportDocument();
doc.Load(Server.MapPath(@"CrystalReport/RS_Report_group_age.rpt"));
// Setting data source of our report object
doc.SetDataSource(dsCustomers);
// Binding the crystalReportViewer with our report object.
CrystalReportViewer1.ReportSource = doc;
doc.PrintOptions.PaperSize = PaperSize.PaperA4;
doc.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, "ผลสำรวจสัดส่วนของสตรีไทย");
}
Object Class SQL
//////////////////////////////// code ////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ConnDB;
public class UseSQL
{
SqlConnection sqlConn = new SqlConnection();
SqlCommand sqlCom;
SqlDataAdapter sqlda;
SqlDataReader sqlreader;
ConDb condb = new ConDb();
string strconn;
public void OpenConnectionMethod()
{
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
}
catch
{
sqlConn.Close();
sqlConn.Dispose();
}
}
public void CloseConnectionMethod()
{
try
{
sqlConn.Close();
sqlConn.Dispose();
}
catch
{
sqlConn.Close();
sqlConn.Dispose();
}
}
public Boolean DBConnectionStatus()
{
try
{
using (SqlConnection sqlConn =
new SqlConnection(condb.getStrConn()))
{
sqlConn.Open();
return (sqlConn.State == ConnectionState.Open);
}
}
catch (SqlException)
{
sqlConn.Close();
sqlConn.Dispose();
return false;
}
catch (Exception)
{
sqlConn.Close();
sqlConn.Dispose();
return false;
}
}
public DataTable ExecuteReader(string strsql)
{
DataTable dt = new DataTable();
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlCom = new SqlCommand();
sqlCom.CommandText = strsql;
sqlCom.CommandType = CommandType.Text;
sqlCom.Connection = sqlConn;
dt.Load(sqlCom.ExecuteReader());
}
catch
{
sqlda.Dispose();
sqlConn.Close();
}
//Conn.Close();
sqlda.Dispose();
sqlConn.Close();
return dt;
}
public SqlDataAdapter ExecuteByDataAdapter(string strsql)
{
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlda = new SqlDataAdapter(strsql, sqlConn);
}
catch
{
sqlda.Dispose();
sqlConn.Close();
}
//sqlda.Dispose();
sqlConn.Close();
return sqlda;
}
public DataTable ExecuteByDataTable(string strsql)
{
DataTable dt = new DataTable();
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlda = new SqlDataAdapter(strsql, sqlConn);
sqlda.Fill(dt);
}
catch
{
sqlda.Dispose();
sqlConn.Close();
}
sqlda.Dispose();
sqlConn.Close();
return dt;
}
public DataSet ExecuteByDataSet(string strsql)
{
DataSet ds = new DataSet();
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlda = new SqlDataAdapter(strsql, sqlConn);
sqlda.Fill(ds);
//using (sqlConn)
//{
// using (sqlda = new SqlDataAdapter(strsql, sqlConn))
// {
// sqlda.Fill(ds, "dataset");
// }
//}
}
catch (Exception)
{
sqlda.Dispose();
sqlConn.Close();
}
sqlda.Dispose();
sqlConn.Close();
return ds;
}
public String returnString(string strsql)
{
string result = null;
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlCom = new SqlCommand(strsql, sqlConn);
sqlreader = sqlCom.ExecuteReader();
if (sqlreader.HasRows)
{
while (sqlreader.Read())
{
result = sqlreader[0].ToString();
}
}
else
{
sqlCom.Dispose();
sqlConn.Close();
result = "Not Found!";
}
}
catch
{
sqlCom.Dispose();
sqlConn.Close();
}
sqlCom.Dispose();
sqlConn.Close();
return result;
}
public void ExNonQuery(string stR)
{
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlCom = new SqlCommand(stR, sqlConn);
sqlCom.ExecuteNonQuery();
}
catch (Exception)
{
sqlCom.Dispose();
sqlConn.Close();
}
sqlCom.Dispose();
sqlConn.Close();
}
public String ExReturnError(string strsql)
{
string sta = "error";
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlCom = new SqlCommand(strsql, sqlConn);
sqlCom.ExecuteNonQuery();
sta = "completed";
}
catch (Exception ex)
{
sqlCom.Dispose();
sqlConn.Close();
sta = "error " + ex.ToString();
return sta;
}
sqlCom.Dispose();
sqlConn.Close();
return sta;
}
public int returnInt(string strsql)
{
int resultInt = 0;
try
{
if (sqlConn.State == ConnectionState.Closed)
{
strconn = condb.getStrConn();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
}
sqlCom = new SqlCommand(strsql, sqlConn);
sqlreader = sqlCom.ExecuteReader();
if (sqlreader.HasRows)
{
while (sqlreader.Read())
{
resultInt = int.Parse(sqlreader[0].ToString());
}
}
else
{
sqlCom.Dispose();
sqlConn.Close();
resultInt = 0;
}
}
catch
{
sqlCom.Dispose();
sqlConn.Close();
resultInt = 0;
}
sqlCom.Dispose();
sqlConn.Close();
return resultInt;
}
public Boolean QueryExecuteNonQuery(String strSQL)
{
sqlConn = new SqlConnection();
sqlConn.ConnectionString = strconn;
sqlConn.Open();
try
{
sqlCom = new SqlCommand();
sqlCom.Connection = sqlConn;
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = strSQL;
sqlCom.ExecuteNonQuery();
return true; //*** Return True ***//
}
catch (Exception)
{
return false; //*** Return False ***//
}
}
}
