Crystal Report : Programmatically set Text of Label

A simple(?) line changes the text of a label (TextObject in Crystal Report):

((TextObject)rptDoc.ReportDefinition.ReportObjects["myTextObject"]).Text = "Here is my text.";

Here "myTextObject" is the name of the TextObject used in the report. To set the ObjectName, right click on the object >> format object >> object name.

Sample Code:

string reportPatfh = "CrystalReport1.rpt";

ReportDocument rptDoc = new ReportDocument();

rptDoc.Load(reportPatfh); ((TextObject)rptDoc.ReportDefinition.ReportObjects["myTextObject"]).Text = "Here is my text.";

crystalReportViewer1.ReportSource = rptDoc;

There is another way:

((TextObject)rptDoc.ReportDefinition.Sections[2].ReportObjects["myTextObject"]).Text = "Here Is My Text";

But i'm surprised to see that SectionIndex does not have any effect i.e. whatever the index is (1, 2 3 0r 4 ), the result is same. Can anybody explain this?

[+/-] Ringkasan saja...

Identify RGB value of color

Sometimes we need to know the RGB(Red-Green-Blue) value of some color, specially in case of GUI design or Web design. Here i'll show a way to know the RGB value from an image.

• Open the image in Paint. Say we want to get RGB of the pointed color
• Select 'pick color' pointer from the toolbox
• Click on the color you want to pick
• Click on menubar Colors -> Edit Colors
• Click on 'Define Custom Colors' button. You'll get the RGB value on lower-left corner.

** If you need to get the color from sources other than image, then follow the common trick of 'Print Screen' :)

[+/-] Ringkasan saja...

Crystal Report : Populate with dynamic query

Last few days I was facing a problem of creating CrystalReport with dynamic query. More specifically, I wanted to change the query in runtime and populate the report with query-results. And I somehow managed to get rid of this. Still I’m not sure if it’s the best way, but it works.

Followings are the steps I found useful:

1. Firstly, create a crystal report with the database fields (which will be viewed) embedded in the report.
2. Then, create a ReportDocument object, say ‘rptDoc’. [required namespace ‘CrystalDecisions.CrystalReports.Engine’]
3. Load the crystal report within rptDoc.
4. Set its data source, with the DataSet / DataTable which contains the results of the query.
5. Finally, for viewing the report set the ReportSource property of CrystalReportViewer to this rptDoc object.

Sample Code:

// prepare the DataTable for the report
string connectionString = "Dsn=TestDB";
OdbcConnection conn = new OdbcConnection(connectionString);
conn.Open();
string query = "select * from person where personId <>; // dynamic query
OdbcCommand command = new OdbcCommand(query, conn);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
conn.Close();

// populate the report with the created DataTable dt
string reportPatfh = "CrystalReport1.rpt"; // path of the crystal report
ReportDocument rptDoc = new ReportDocument();
rptDoc.Load(reportPatfh);
rptDoc.SetDataSource(dt);
crystalReportViewer1.ReportSource = rptDoc;

Notes:

Here only data are changed dynamically, not the format of the report.

[+/-] Ringkasan saja...

Database : Connect from C#

For creating any database related software, we need to know how to do the following 3 basic things-

1. How to connect with database
2. How to execute non-query commands (insert, update etc.)
3. How to get the results of query (select)

Here, I’ll try to show these basic things using C#, .net, MS Access database and Odbc. (Don’t worry about code. A complete code is given at the last.)

Database Specification:

There is an access file name "TestDB.mdb" which contains a table "person(personId, name, address)". Here personId is an AutoNumber, and other two are Text.

Connection with database:

You need to initiate a OdbcConnection object which needs a connection string. Some sample connection strings are as follows:

1. "Driver={SQL Server};Server=(local);Trusted_Connection=Yes;Database=AdventureWorks;"
2. "Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=Yes"
3. "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\Northwind.mdb"
4. "Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
5. "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
6. "DSN=dsnname"
   

Sample Code:

string connnectionString = "Dsn=MyDsn";

OdbcConnection conn = new OdbcConnection(connectionString);

Execute non-query commands:

Simple steps are as follows:

1. Open the OdbcConnection.
2. Create an OdbcCommand with that connection and the SqlCommand.
3. Then, execute the command.
4. Finally, close the connection.
   

Sample Code:

conn.Open();

string query = "insert into person(name, address) values ('Alor Chhota', 'Dhaka, Bangladesh');";

odbcCommand.Connection = conn;

odbcCommand.CommandText = query;

int noOfAffectedRows = odbcCommand.ExecuteNonQuery();

conn.Close();

Execute Query:

Steps:

1. Open the OdbcConnection.
2. Create an OdbcCommand with that connection and the needed query.
3. Create an OdbcDataAdapter with the OdbcCommand
4. Fill a dataset by the OdbcDataAdapter. This dataset contains the query results.
5. As usual, close the connection.
   

Sample Code:

conn.Open();

string query = "select * from person";

odbcCommand.Connection = conn;

odbcCommand.CommandText = query;

odbcDataAdapter.SelectCommand = odbcCommand;

DataSet dataSet = new DataSet();

odbcDataAdapter.Fill(dataSet);

conn.Close();

Complete Code:

You can use the following DatabaseManager class for your convenience. Here the constructor takes the connection string. And there are two functions for executing query and non-query commands.

Class: DatabaseManager

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Windows.Forms;

namespace DatabaseTest
{
public class DatabaseManager
{
private string m_ConnectionString;
private OdbcDataAdapter m_OdbcDataAdapter;
private OdbcConnection m_OdbcConnection;
private OdbcCommand m_OdbcCommand;

public DatabaseManager(string connectionString)
{
this.m_ConnectionString = connectionString;
this.m_OdbcConnection = new OdbcConnection(this.m_ConnectionString);
m_OdbcDataAdapter = new OdbcDataAdapter();
m_OdbcCommand = new OdbcCommand();
}

public DataTable executeQuery(string query)
{
DataTable dataTable = null;
try

{
this.m_OdbcConnection.Open();
this.m_OdbcCommand.Connection = this.m_OdbcConnection;
this.m_OdbcCommand.CommandText = query;
this.m_OdbcDataAdapter.SelectCommand = this.m_OdbcCommand;
DataSet dataSet = new DataSet();
this.m_OdbcDataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
}
finally
{
if (this.m_OdbcConnection.State != ConnectionState.Closed)
this.m_OdbcConnection.Close();
}
return dataTable;
}

public int excecuteNonQuery(string query)
{

int noOfAffectedRows = -1;
try
{
this.m_OdbcConnection.Open();
this.m_OdbcCommand.Connection = this.m_OdbcConnection;
this.m_OdbcCommand.CommandText = query;
noOfAffectedRows = this.m_OdbcCommand.ExecuteNonQuery();
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
}
finally
{
if (this.m_OdbcConnection.State != ConnectionState.Closed)
this.m_OdbcConnection.Close();
}
return noOfAffectedRows;
}
}
}

Testing Code:

string connectionString = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\TestDB.mdb";

DatabaseManager dm = new DatabaseManager(connectionString);

dm.excecuteNonQuery("insert into person(name, address) values ('Ashis Saha', 'Mohammadpur, Dhaka.');");

dm.excecuteNonQuery("insert into person(name, address) values ('Rakib Shahriar', 'Nazrul Islam Hall, BUET.');");

DataTable dt = dm.executeQuery("select * from person");

this.dataGridView1.DataSource = dt; // for showing in windows form

[+/-] Ringkasan saja...