Be the first to write a review
Free - Applied MS Reporting Services 101 using Smart Client
Introduction
I still remember it was a neatly done report that got me my first pay raise. Ever since, I am very passionate about report writing (every one likes a pay raise right?). In this article, I will guide you through step by step on how to create a simple report using MS Reporting Services 2005; and host it with a Smart Client application.
So, are you ready to get your pay raise? Why not! Who knows, your neatly done report can just do that.
Prior to this article, I wrote three others, which were addressing different issues related to the reporting services. However, all of them were targeted towards the intermediate-advance level audience. From all the feedback I received, one was common, quite a few of you asked for an article which will be specially geared towards the novice-beginner level.
I assume the reader has a basic understanding of Visual Studio 2005 IDE and is comfortable with writing code using C#. You don’t have to know MS Reporting Services to understand this article; although, any pervious experience with writing a report would help.
Although, I am calling this article 101, my intention is to adopt the applied approach rather then discussing each and every topic associated with reporting services. I am touching the most common aspects of report design and I use the most common controls. I would strongly encourage you to go through the MSDN documentation for more detailed information.
Step 5: Let's write some C# code to bring life to our report
Phew… I hope you guys are not exhausted already. Hang in there; we are at the last step now. It's like we have waited for that a long nine months and time has come to witness the miracle of birth.
From solution explorer, select Form1. Right click on surface of form and select View Code.
using System.Data.SqlClient;
using Microsoft.Reporting.WinForms;
Make sure the Form1_Load event has following code:
private void Form1_Load(object sender, EventArgs e)
{
//declare connection string
string cnString = @"(local); Initial Catalog=northwind;" +
"User Id=northwind;Password=northwind";
//use following if you use standard security
//string cnString = @"Data Source=(local);Initial
//Catalog=northwind; Integrated Security=SSPI";
//declare Connection, command and other related objects
SqlConnection conReport = new SqlConnection(cnString);
SqlCommand cmdReport = new SqlCommand();
SqlDataReader drReport;
DataSet dsReport = new dsProduct();
try
{
//open connection
conReport.Open();
//prepare connection object to get the data through reader and
populate into dataset
cmdReport.CommandType = CommandType.Text;
cmdReport.Connection = conReport;
cmdReport.CommandText = "Select TOP 5 * FROM
Products Order By ProductName";
//read data from command object
drReport = cmdReport.ExecuteReader();
//new cool thing with ADO.NET... load data directly from reader
to dataset
dsReport.Tables[0].Load(drReport);
//close reader and connection
drReport.Close();
conReport.Close();
//provide local report information to viewer
rpvAbraKaDabra.LocalReport.ReportEmbeddedResource =
"rsWin101.rptProductList.rdlc";
//prepare report data source
ReportDataSource rds = new ReportDataSource();
rds.Name = "dsProduct_dtProductList";
rds.Value = dsReport.Tables[0];
rpvAbraKaDabra.LocalReport.DataSources.Add(rds);
//load report viewer
rpvAbraKaDabra.RefreshReport();
}
catch (Exception ex)
{
//display generic error message back to user
MessageBox.Show(ex.Message);
}
finally
{
//check if connection is still open then attempt to close it
if (conReport.State == ConnectionState.Open)
{
conReport.Close();
}
}
}
You might be wondering why I have used "TOP 5" for the select query; the reason is, I wanted to limit the output so that I can show you a summary total in Image 1.
Tip: Name property of ReportDataSource object should be always "DataSet_DataTable".
Asif Sayed
I started to code in 1990 using C and GW basic, then moved to 4GL world with DBIII, Clipper and FoxPro etc. and continued till I found and hooked to MS goody bag Visual Basic. For last four years, I am working extensively on .NET technologies and scripting both VB.NET and C#. My ideal choice of database is SQL Server; however, I do interact with Oracle when required. I have worked on all sort of Business applications, but my ideal project would be anything which has some Financial part associated with it, I wish I can put a Balance-sheet report in all the project I do… I also teach .NET related technologies as part-time faculty with local community college and try to help share my knowledge with others.