Tuesday 9 August 2011

Change the data source of a Crystal Report at Run-time using C#

It’s a known fact, that in Crystal Reports, if you try to display details, using data in a SQL Server Database other than the one that you’ve used to design the report, either you have to set the database location (or refresh the report). This is a common issue that development team face when the reports are being deployed to the production environment.

But this can be prevented using the following method.

Following namespaces are required.

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;



And we have to assign the required SQL server information to each table/view of the report, sub reports and the report viewer.

Use the following code:


SQLReport report = new SQLReport();

//Get SQL Server Details
string zServer = @"SERVER_NAME";
string zDatabase = @"DATABASE";
string zUsername = @"USER";
string zPassword = @"PASSWORD";

ConnectionInfo ciReportConnection = new ConnectionInfo();

ciReportConnection.ServerName = zServer;
ciReportConnection.DatabaseName = zDatabase;
ciReportConnection.UserID = zUsername;
ciReportConnection.Password = zPassword;

//Assign data source details to tables

foreach (Table table in report.Database.Tables) {
table.LogOnInfo.ConnectionInfo = ciReportConnection;
table.ApplyLogOnInfo(table.LogOnInfo);
}

foreach (ReportDocument subrep in report.Subreports) {
foreach (Table table in subrep.Database.Tables) {
table.LogOnInfo.ConnectionInfo = ciReportConnection;
table.ApplyLogOnInfo(table.LogOnInfo);
}
}

//Assign data source details to the report viewer
if (this.crystalReportViewer1.LogOnInfo != null) {
TableLogOnInfos tlInfo = this.crystalReportViewer1.LogOnInfo;
foreach (TableLogOnInfo tbloginfo in tlInfo) {
tbloginfo.ConnectionInfo = ciReportConnection;
}
}


crystalReportViewer1.ReportSource = report;
crystalReportViewer1.Refresh();

Monday 8 August 2011

Invoke a custom method when Crystal Report Viewers’ print button is clicked / Add custom button to Crystal Report Viewer Toolbar

If you are developing applications which includes reporting with Crystal Reports, you may have noticed that it’s not possible to invoke a custom method, when the user prints the report. However this was something which was easily implemented in Crystal Reports 8/8.5 but removed from latter versions.

But there’s a workaround for this. In this example I will show you how to invoke a method in our client application, when the print button of the report viewer is clicked.

In order to do that we have to add our custom method to the report viewers’ print buttons’ print action.

Create a new windows application.

Add another form to the project and name it as ‘CustomReportViewer.cs’.

Add a Crystal Report viewer to a newly created form. (If the crystal report viewer is not available in the toolbox, please add it to the toolbox first)

img_scr_001_a

img_scr_002

Add new report to the project and name it as ‘SampleReport.rpt’.

img_scr_003

Now add the following code to the ‘CustomReportViewer’ class

public delegate void CustomPrintDelegate();


Add the following property.


public Delegate CustomPrintMethod { get; set; }


Add this additional code to the initialization method.


foreach (Control control in crystalReportViewer1.Controls) {
if (control is System.Windows.Forms.ToolStrip) {

//Default Print Button
ToolStripItem tsItem = ((ToolStrip)control).Items[1];
tsItem.Click += new EventHandler(tsItem_Click);

//Custom Button
ToolStripItem tsNewItem = ((ToolStrip)control).Items.Add("");
tsNewItem.ToolTipText = "Custom Print Button";
tsNewItem.Image = Resources.CustomButton;
tsNewItem.Tag = "99";
((ToolStrip)control).Items.Insert(0, tsNewItem);
tsNewItem.Click += new EventHandler(tsNewItem_Click);
}
}


Using the above coding we can find out the print button of the report viewers’ too strip. And the 1st item is for the print button. (I have found this out from its ToolTipText).


How ever you can add your own button if you like or you can use the existing print button. Both options are illustrated.


Add the following methods.



void tsNewItem_Click(object sender, EventArgs e) {
if (CustomPrintMethod != null) {
CustomPrintMethod.DynamicInvoke(null);
}
}

void tsItem_Click(object sender, EventArgs e) {
if (CustomPrintMethod != null) {
CustomPrintMethod.DynamicInvoke(null);
}
}


Here is the complete coding of the ‘CustomReportViewer’ class.


using System;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace PrintDelegateMethod {
public partial class CustomReportViewer : Form {

public delegate void CustomPrintDelegate();

public Delegate CustomPrintMethod { get; set; }

public CustomReportViewer() {
InitializeComponent();

foreach (Control control in crystalReportViewer1.Controls) {
if (control is System.Windows.Forms.ToolStrip) {

//Default Print Button
ToolStripItem tsItem = ((ToolStrip)control).Items[1];
tsItem.Click += new EventHandler(tsItem_Click);

//Custom Button
ToolStripItem tsNewItem = ((ToolStrip)control).Items.Add("");
tsNewItem.ToolTipText = "Custom Print Button";
tsNewItem.Image = Resources.CustomButton;
tsNewItem.Tag = "99";
((ToolStrip)control).Items.Insert(0, tsNewItem);
tsNewItem.Click += new EventHandler(tsNewItem_Click);
}
}
}

void tsNewItem_Click(object sender, EventArgs e) {
if (CustomPrintMethod != null) {
CustomPrintMethod.DynamicInvoke(null);
}
}

void tsItem_Click(object sender, EventArgs e) {
if (CustomPrintMethod != null) {
CustomPrintMethod.DynamicInvoke(null);
}
}

private void CustomReportViewer_Load(object sender, EventArgs e) {
SampleReport report = new SampleReport();
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.Refresh();
}
}
}


 


Add the following delegate to your calling class


public delegate void PrintDelegate();


Add the following method. This is the method that we want to invoke when the print button or the custom button is clicked.


private void CustomPrintMethod() {
MessageBox.Show("Custom Print Method");
}


And a button and the following click event code.


private void button1_Click(object sender, EventArgs e) {
CustomReportViewer viewer = new CustomReportViewer();
PrintDelegate mymethod = new PrintDelegate(CustomPrintMethod);
viewer.CustomPrintMethod = mymethod;
viewer.Show();

}


The complete source of the calling form:


using System;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace PrintDelegateMethod {
public partial class Form1 : Form {
public delegate void PrintDelegate();

public Form1() {
InitializeComponent();

}

private void CustomPrintMethod() {
MessageBox.Show("Custom Print Method");
}


private void button1_Click(object sender, EventArgs e) {
CustomReportViewer viewer = new CustomReportViewer();
PrintDelegate mymethod = new PrintDelegate(CustomPrintMethod);
viewer.CustomPrintMethod = mymethod;
viewer.Show();

}


}
}


Now if you run the project, you can get a similar screen shown below. And please note that I have added a resource file named ‘Resources’ and added an image named ‘CustomButton’

img_scr_011_a

And if you click either of the buttons, your custom method will be invoked. The default print method will be executed only when the print button is clicked.

img_scr_012

Friday 5 August 2011

How to insert data using SQL Views created using multiple tables

A view can be defined as a virtual table or a stored query and the data accessible through a view is not stored in the database as a distinct object. Only the select statement is stored on the database instead.

How ever views can be used and perform DML operations (Insert, Update & Delete) also.

Consider the following two tables.

CREATE TABLE STUDENT(
STD_ID INT,
STD_FNAME VARCHAR(20),
STD_LNAME VARCHAR(20)
)


CREATE TABLE STUDENT_PAYMENT(
STD_ID INT,
PAY_AMT MONEY,
PAY_DATE DATETIME
)



Now create the following views.


CREATE VIEW VW_STUDENT
AS
SELECT
STD_ID,
STD_FNAME,
STD_LNAME
FROM
STUDENT


CREATE VIEW VW_STUDENT_PAYMENT
AS
SELECT
STD_ID,
PAY_AMT,
PAY_DATE
FROM
STUDENT_PAYMENT


You can insert data to the above tables using the views we have just created. And it is the same syntax that we use to insert data to tables.


INSERT INTO VW_STUDENT
SELECT 1,'Peter','Parker' UNION
SELECT 2,'James', 'Watson'


INSERT INTO VW_STUDENT_PAYMENT
SELECT 1,1000,'01/01/2011' UNION
SELECT 1,1100,'01/02/2011' UNION
SELECT 1,1200,'01/03/2011' UNION
SELECT 1,1250,'01/04/2011' UNION
SELECT 1,1375,'01/05/2011' UNION
SELECT 2,750,'01/03/2011' UNION
SELECT 2,850,'01/04/2011' UNION
SELECT 2,950,'01/05/2011'

And if you query the tables you can see that the records have inserted correctly.

img_scr_009


Now we will create the following view. This time we will join two tables and create a somewhat complex query.


CREATE VIEW VW_LAST_PAYMENT_DETAILS AS
WITH CTE_STD (STD_ID,MAX_PAYDATE) AS (
SELECT SP.STD_ID, MAX(SP.PAY_DATE) AS MAX_PAYDATE
FROM STUDENT_PAYMENT AS SP
GROUP BY SP.STD_ID
)
SELECT S.STD_ID,S.STD_FNAME,S.STD_LNAME, P.PAY_AMT,P.PAY_DATE
FROM STUDENT AS S
JOIN STUDENT_PAYMENT AS P ON S.STD_ID = P.STD_ID
JOIN CTE_STD AS Q ON P.STD_ID = Q.STD_ID AND P.PAY_DATE = Q.MAX_PAYDATE
GROUP BY S.STD_ID,S.STD_FNAME,S.STD_LNAME, P.PAY_AMT,P.PAY_DATE


Using the above created view we can list the last payment details of each student.


img_scr_004


So if we required to insert last payment details using this view how shall we do it ? If you use the simple insert statements similar to the ones, we used earlier, you have could ended up with the following error.


INSERT INTO VW_LAST_PAYMENT_DETAILS (STD_ID,PAY_AMT,PAY_DATE)
SELECT 1,4440,GETDATE()


img_scr_007


In order to insert (update & delete) data to views created using multiple tables, you need to use an ‘Instead of trigger’.


**Please note that ‘After Triggers’ cannot be created for views.


Let’s create an instead of trigger using the following syntax.


CREATE TRIGGER TRGI_VW_PAYMENT ON VW_LAST_PAYMENT_DETAILS
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO STUDENT_PAYMENT
SELECT STD_ID,PAY_AMT,PAY_DATE
FROM INSERTED
END


Now using the above insert syntax, you can insert data without getting any error. If you inspect the ‘STUDENT_PAYMENT’ table you can see that the data  has been inserted successfully.


img_scr_010