Passing SQL to Crystal Reports 10 using C# express and SQL Server 2005

Tools used in this example:
1. C# Express 2008
2. MS SQL Server Express 2005
3. Crystal Reports 10

I. Create the Report File
1. Click on File->New and select "As blank report" (The Database Expert wizard shows)
2. Expand the "Create New Connection" folder, and expand OLE DB (ADO). A window will appear.

3. On the Provider section, look for SQL Native Client. Select it and click Next.
4. On the Data Source combo box, type in the name of your server. (Mine is RIXX\SQLExpress)
5. Select the Integrated Security checkbox so that it uses your windows account for authentication.
6. Choose your database from the Database combo box and click finish. I have a database with the following attributes:
Name: TestDB1
Tables:
TestTable1
Columns: Id, Description
Data:
Id Description
1 aaa
2 bbb
3 ccc
4 ddd
5 eee

7. Add your tables to the Selected Tables section and click Ok.
8. Drag the fields to the Details section of the report. Save your report. I used Report1.rpt.


II. Making the C# Project:
1. Create a new Windows application project.
2. Right click on the Toolbox and choose "Choose Items..."
3. Select the COM components tab.
4. Select the "Crystal ActiveX Report Viewer Control 10.0"
5. Click Ok. (The Crystal Report Viewer Control icon should now be visible on the toolbox.



6. Add a Crystal Report Viewer to the form.
7. Right Click on the Solutions Explorer and click Add Reference.
8. Click the Browse tab.
9. select the following location (or depending on where you installed your crystal report): "c:\Program Files\Common Files\Crystal Decisions\2.5\bin\" and select "CRAXDRT.dll"
10. Click Ok
11. Use the following lines of code when opening a report:

CRAXDRT.Report rpt = new CRAXDRT.Report();
CRAXDRT.Application app = new CRAXDRT.Application();
rpt = app.OpenReport("", null);
axCrystalActiveXReportViewer1.ReportSource = rpt;
axCrystalActiveXReportViewer1.ViewReport();

12. the report viewer should then show the report you have created.

III. Passing SQL statements to the report.
1. Modify the code above into:
CRAXDRT.Report rpt = new CRAXDRT.Report();
CRAXDRT.Application app = new CRAXDRT.Application();
app.LogOnServerEx("crdb_ado.dll",
@"RIXX\SQLExpress", "TestDB1", "", "", null, @"Provider=SQLNCLI;Server=localhost;Database=TestDB1;Trusted_Connection=yes;"); //This is used for logging in to your SQL Server
rpt = app.OpenReport("d:\\report1.rpt", null);
rpt.SQLQueryString = "select * from TestTable1 where Id=4"; //instead of displaying all 5 records, I chose to display only the one with Id # 4.
axCrystalActiveXReportViewer1.ReportSource = rpt;
axCrystalActiveXReportViewer1.ViewReport();

Comments

3 Responses to "Passing SQL to Crystal Reports 10 using C# express and SQL Server 2005"

Kaiser said... December 12, 2008 at 11:53 AM

Hi Sir,

I just would like to ask if you are interested to have own domain/web hosting.. you can check my website for details. I have already many clients and one of them is http://exanderbarrios.info/.

Thanks

Vigneshwar Gupta said... January 22, 2009 at 5:13 PM

I am stryggling from One week to make DataType Independent in Crystal report. This Post is helpfull to me. Thanks for nice post.

fiShBoN3 said... January 22, 2009 at 10:25 PM

I'm glad I can help. You are welcome. :D