You can use Visual Studio to create Report Definition Language (.RDL) files that can be used within SQL Server Reporting Services. The editor for .RDL files allows you to put together the SQL Query, design the report layout, and preview the report all within Visual Studio.
Microsoft has also provided a Report Viewer control that allows you to display reports within your application. This control is not tied to SQL Server since you can use your own data sources as input to the report (though it can also display reports that originate from SQL Server Reporting Services). Reports are defined using the .RDLC format, which is the same format as .RDL files, but ignores certains aspects of the definitions (more details here).
Of course, since a data source can be defined within a Visual Studio project, you lose the ability to preview your report easily. Generally, you have to compile and run the application in order to view your report and verify that it is correct. Being able to quickly view your report output was a nice feature of working with the SQL Server Reporting Services version of the report editor.
However, since the .RDL and .RDLC formats are the same, you can use an .RDL specification as input to the Report Viewer control. All you need to do is to implement some code that runs the query embedded in the .RDL file. By doing so, you may use the Visual Studio 2005 version of the Reporting Services editor (with its handy preview mode) but still use the Report Viewer component within your application.
Here’s source code that shows this technique:
using System;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WinForms;
namespace ReportView
{
static class Program
{
[STAThread]
static void Main( string[] argList )
{
try
{
if( argList.Length != 1 )
throw new ArgumentException( "Must specify name of .RDL file." );
string rdlFileName = argList[0];
if( !File.Exists( rdlFileName ) )
throw new FileNotFoundException( "Could not find .RDL file." );
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault( false );
Application.Run( new ReportViewForm( rdlFileName ) );
}
catch( Exception ex )
{
StringBuilder errMsg = new StringBuilder();
errMsg.Append( "Application Error generated:\n" );
for( Exception x = ex; x != null; x = x.InnerException )
errMsg.AppendFormat( " {0} - {1}\n", x.GetType().FullName, x.Message );
errMsg.AppendFormat( "Stack Trace:\n{0}", ex.StackTrace );
MessageBox.Show( errMsg.ToString(), "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
}
}
}
class ReportViewForm : Form
{
private Microsoft.Reporting.WinForms.ReportViewer rptView;
private string reportFileName;
public ReportViewForm( string fileName )
{
reportFileName = fileName;
rptView = new Microsoft.Reporting.WinForms.ReportViewer();
SuspendLayout();
rptView.Dock = System.Windows.Forms.DockStyle.Fill;
rptView.Name = "rptView";
rptView.TabIndex = 0;
Controls.Add( rptView );
const int border = 20;
System.Drawing.Size winSize = Screen.PrimaryScreen.WorkingArea.Size;
Location = new System.Drawing.Point( border, border );
Size = new System.Drawing.Size( winSize.Width - ( 2 * border ), winSize.Height - ( 2 * border ) );
StartPosition = FormStartPosition.Manual;
Name = "ReportViewForm";
Text = Path.GetFileName( reportFileName );
ResumeLayout( false );
}
const string ReportDefinitionNS = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
const string ReportDesignerNS = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
protected override void OnLoad( EventArgs e )
{
base.OnLoad( e );
rptView.ProcessingMode = ProcessingMode.Local;
using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
rptView.LocalReport.LoadReportDefinition( readStream );
XPathDocument xpDoc;
using( Stream readStream = new FileStream( reportFileName, FileMode.Open, FileAccess.Read, FileShare.Read ) )
xpDoc = new XPathDocument( readStream );
ConnectionStringSettings connSett = ConfigurationManager.ConnectionStrings["Default"];
using( SqlConnection sqlConn = new SqlConnection( connSett.ConnectionString ) )
{
sqlConn.Open();
XPathNavigator xpNav = xpDoc.CreateNavigator();
XPathExpression xpe = xpNav.Compile( "/r:Report/r:DataSets/r:DataSet" );
XmlNamespaceManager nsMgr = new XmlNamespaceManager( xpNav.NameTable );
nsMgr.AddNamespace( "r", ReportDefinitionNS );
nsMgr.AddNamespace( "rd", ReportDesignerNS );
xpe.SetContext( nsMgr );
foreach( XPathNavigator xpNode in xpNav.Select( xpe ) )
{
string dsName = Convert.ToString( xpNode.Evaluate( "string( @Name )", nsMgr ) );
string cmdTxt = Convert.ToString( xpNode.Evaluate( "string( r:Query/r:CommandText )", nsMgr ) );
if( String.IsNullOrEmpty( dsName ) || String.IsNullOrEmpty( cmdTxt ) )
throw new Exception( "Could not obtain query for report definition." );
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = cmdTxt;
DataTable dataTable = new DataTable();
SqlDataAdapter sqlAdapter = new SqlDataAdapter( sqlCmd );
sqlAdapter.Fill( dataTable );
rptView.LocalReport.DataSources.Add( new ReportDataSource( dsName, dataTable ) );
}
}
rptView.RefreshReport();
}
}
}
Second Stanza 