ASP.NET Runtime SQL Parameter question

[Mitchell]

Hello.  I have a fairly simple problem with an ASP.NET application, and I'm hoping there is a simple solution.

I've created a class in my Visual Studio 10 solution called RFO_Output.cs.  I chose to not create a separate class library for two reasons (1) the reports I'm creating are not going to be shared in other applications (2) I couldn't find an example of how to call those external reports from an ASP.Net application.  Including it in my solution was easy enough, so I went that route.

The report is a simple banded report, but in my case I only want to show a single record at a time. The RFO_Output.cs class has a Telerik SQLdatasource component with the following SQL code in it (a very simple select):

SELECT [DCN_Outage_Ticket]
      ,[Event_Start_Time]
      ,[Event_End_Time]
      ,[Duration_Days]
      ,[Duration_Hours]
      ,[Duration_Minutes]
      ,[Duration_Seconds]
      ,[Summary]
      ,[Root_Cause]
      ,[Preventative_Measures]
  FROM tblMOPReason_For_Outage

I've created an ASP Panel on one of the .aspx pages in my solution, and included a Telerik ReportViewer component in the panel, with a button provided so that the end-user can have the panel be hidden or visible. The ReportViewer component has its ReportSource property set to the RFO_Output class mentioned above.  So far, no problems.  The Telerik examples are clear and helpful to this point.

My question involves how to limit my report to the desired records.  In my case, I want to select a particular row from the table above, based on a column in the same table.  I can add a WHERE clause, of course, then either pass a parameter ("WHERE KeyCol = @KeyVal") or modify the SQL at runtime to include the value ("WHERE KeyCol = 5").  But this is where things get murky.

The "Using Parameters with the SqlDataSource" component help page shows how to add a new SqlDataSource object in the C# code itself, but doesn't give an example of how to update an existing SqlDatasource already in the report class, or how to assign this new SqlDataSource to an existing reportsource.

I tried the following code:
            Telerik.Reporting.SqlDataSource thisSource = new Telerik.Reporting.SqlDataSource();
            thisSource.ConnectionString = "DCNConnectionString";
            thisSource.SelectCommand = "SELECT [DCN_Outage_Ticket], [Event_Start_Time] ,[Event_End_Time] ,[Duration_Days] " +
                                     "      ,[Duration_Hours] ,[Duration_Minutes] ,[Duration_Seconds] ,[Summary] " +
                                     "      ,[Root_Cause] ,[Preventative_Measures] FROM tblMOPReason_For_Outage " +
                                     "WHERE RFO_UID = " + HidRFOID.Value.ToString();
            Telerik.Reporting.Report thisReport = ReportViewer1.ReportSource as Telerik.Reporting.Report;
            thisReport.DataSource = thisSource;

But this doesn't compile, as the conversion from ReportSource to Report fails.  (An interesting side note is that the Telerik add-in doesn't show this as an error, but it shows up at compile time.)

Any suggestions on how to make this happen would be appreciated.  I'm not clear on how to update the SQL code for an in-place SQLDataSource, or how to reference the in-place report so that I can point it at a new SQLDataSource.

Thanks for your input on this.

[Stef]

Hi Mitchell,

You can use a parameterized SQL query, where the SQL parameters are mapped to report parameters as in the Using Parameters with the SqlDataSource component article. The code snippets in the article illustrates a code that is auto-generated by the Report Designers after configuring the data source component.
For example follow the steps from the SqlDataSource Wizard article . Already existing components can be reconfigured in the designer - select the component and Configure from its Context Menu.

After the configuration there will be added report parameters, which values can be:

• Set from the viewer (if the parameters Visible properties are set to true);
• Set from the wrapping report source object. The ReportSource.Parameters collection is mapped by key to the Report.ReportParameters collection.

To get the report from the viewer's ReportSource, you need to convert to the corresponding report source type first. Consider the example in the Changing the connection string dynamically according to runtime data KB article.

Let us know if you need any further help.

Regards,
Stef
Telerik