Microsoft Report Viewer with SqlConnection

**Leon Jollans** · Jul 23 '08, 12:45 AM

Re: Microsoft Report Viewer with SqlConnection

Hi Jassim,

This can be a big question, and your question is a bit vague. I'm assuming
that you have a rdl file already and that has a datasource defined so the
report can process the results. Actually, the fact that it's a stored proc
is irrelevant since the report viewer doesn't query for data, you have to
get the data yourself and then assign it to the report.

First off you need to know the datasource name required. You can find these
out easily enough by.iterating over the DataSources collection.

Now assuming you have an aspx file with a ReportViewer web control on it
called ReportViewer1, and an RDL(C) Report File already loaded in it, this
code will get you the datasource names contained.

// code block 1
List<stringdata SourceNames = new List<string>();
foreach(ReportD ataSource rds in ReportViewer1.L ocalReport.Data Sources)
{
dataSourceNames .Add(rds.Name);
}

now you can assign to the data sources by index or name. so if your report
has a single datasource called DataSet1_DataTa ble1 which has the columns
defined, you can simply do

DataTable data = YourMethodToLoa dDataFromYourSt oredProc();
// line 2
ReportViewer1.L ocalReport.Data Sources["DataSet1_DataT able1"].Value = data;
ReportViewer1.R efresh();

or you can avoid looking through the defined datasources first and simply
assign to ReportViewer1.L ocalReport.Data Sources[0] - although this will be
error prone.

now if you don't know the stored proc or query to call in advance, but it's
specified in the report, things are trickier because the ReportViewer API
doesn't appear to expose this information. You will need to parse the XML
from the RDL file and find the DataSets/DataSet/Query node for each DataSet
in the report. Among other things, this node has a child node CommandText,
the InnerText of which will be the query - either a SQL command or the
stored proc call.

If the query expects parameters it's more difficualt again. To obtain this
information you will need to parse the Query/QueryParameters node from the
XML and resolve the parameters yourself. Note that the report viewer doesn't
generate any UI for user input of report parameters so if you want this to
be automatic, you'll need to write some code to generate the UI dynamically
yourself.

if you need some help with parsing the XML let me know. it's easy enough,
but it's a little beyond beginner level if you've not done it before.

Leon

"Jassim Rahma" <jrahma@hotmail .comwrote in message
news:92616CB7-F48B-4A69-AEFE-C116A175E96A@mi crosoft.com...

>
anyone has a code sample to connect the Microsoft Report Viewer Control
using Microsoft SQL Server stored procedure?
>
>
>

Microsoft Report Viewer with SqlConnection

Microsoft Report Viewer with SqlConnection

Comment