locked
How to run store procedure in report local mode RRS feed

  • Question

  • User1144390192 posted

    i use reporting service 2008 and i want to run store procedure from report  in local mode

    in fact i dont want set dataset and bind it to report

    in rdl report we can do it(Remote mode ),but  in rdlc cant...

    Sunday, October 23, 2011 2:11 PM

Answers

  • User992473171 posted

    Hi,

     

    I am talking about RDLC report only. Here, You can create a dataset (On report Design ) and use this datatable to design your report (In my report I used "DataSet_Report_Items") . So Now we have to bind this this dataset at run time. For that we can use the code below.

    DataSet thisDataSet = new System.Data.DataSet();

    thisDataSet =SqlHelper.ExecuteDataset(co.connect(), CommandType.Text, commandtext);

    ReportDataSource datasource = new ReportDataSource("DataSet_Report_Items", thisDataSet.Tables[0]);

    ReportViewer1.LocalReport.DataSources.Clear();

    ReportViewer1.LocalReport.DataSources.Add(datasource);

    ReportViewer1.LocalReport.Refresh();

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 24, 2011 6:56 AM

All replies

  • User992473171 posted
    using System; 
    using System.Data; 
    using System.Configuration; 
    using System.Collections; 
    using System.Web; 
    using System.Web.Security; 
    using System.Web.UI; 
    using System.Web.UI.WebControls; 
    using System.Web.UI.WebControls.WebParts; 
    using System.Web.UI.HtmlControls; 
    using System.Data.SqlClient; 
    using Microsoft.Reporting; 
    using Microsoft.ApplicationBlocks.Data; 
    using Microsoft.Reporting.WebForms; 
    using System.Drawing.Printing; 
    using System.IO; 
     
    public partial class _Default : System.Web.UI.Page 
    { 
     
        protected void Page_Load(object sender, EventArgs e) 
        { 
            string commandtext = ""; 
            //commandtext = Request.QueryString["cd"].ToString(); 
     
            commandtext = Session["cd"].ToString();  // My querry will be passed as session variable named "cd". 
     
     
            Connct co = new Connct(); 
            SqlCommand com = new SqlCommand(commandtext, co.connect()); 
            SqlDataAdapter adp = new SqlDataAdapter(); 
            adp.SelectCommand = com; 
            DataSet ds = new DataSet(); 
            adp.Fill(ds); 
     
            //GridView1.DataSource = ds.Tables[0]; 
            //GridView1.DataBind(); 
     
            // Report Test 
     
            System.Data.DataSet thisDataSet = new System.Data.DataSet(); 
     
            thisDataSet = SqlHelper.ExecuteDataset(co.connect(), CommandType.Text, commandtext); 
            ReportDataSource datasource = new ReportDataSource("DataSet_Report_Items", thisDataSet.Tables[0]); 
            ReportViewer1.LocalReport.DataSources.Clear(); 
                      
            ReportViewer1.LocalReport.DataSources.Add(datasource); 
            ReportViewer1.LocalReport.Refresh();Here Is the Solution 
    
    Examole Code Page
     
    <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"  
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)"  
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt"> 
            <LocalReport ReportPath="Report.rdlc"> 
                <DataSources> 
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource2"  
                        Name="ShowProductByCategory" /> 
                </DataSources> 
            </LocalReport> 
        </rsweb:ReportViewer> 
         
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"  
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetData"  
            TypeName="WebApplication2.DataSetProductsTableAdapters.ShowProductByCategoryTableAdapter"> 
            <SelectParameters> 
                <asp:Parameter DefaultValue="Audit" Name="CategoryName" Type="String" /> 
            </SelectParameters> 
    

    using System; 
    using System.Data; 
    using System.Configuration; 
    using System.Collections; 
    using System.Web; 
    using System.Web.Security; 
    using System.Web.UI; 
    using System.Web.UI.WebControls; 
    using System.Web.UI.WebControls.WebParts; 
    using System.Web.UI.HtmlControls; 
    using System.Data.SqlClient; 
    using Microsoft.Reporting; 
    using Microsoft.ApplicationBlocks.Data; 
    using Microsoft.Reporting.WebForms; 
    using System.Drawing.Printing; 
    using System.IO; 
     
    public partial class _Default : System.Web.UI.Page 
    { 
     
       
    protected void Page_Load(object sender, EventArgs e) 
       
    { 
           
    string commandtext = ""; 
           
    //commandtext = Request.QueryString["cd"].ToString(); 
     
            commandtext
    = Session["cd"].ToString();  // My querry will be passed as session variable named "cd". 
     
     
           
    Connct co = new Connct(); 
           
    SqlCommand com = new SqlCommand(commandtext, co.connect()); 
           
    SqlDataAdapter adp = new SqlDataAdapter(); 
            adp
    .SelectCommand = com; 
           
    DataSet ds = new DataSet(); 
            adp
    .Fill(ds); 
     
           
    //GridView1.DataSource = ds.Tables[0]; 
           
    //GridView1.DataBind(); 
     
           
    // Report Test 
     
           
    System.Data.DataSet thisDataSet = new System.Data.DataSet(); 
     
            thisDataSet
    = SqlHelper.ExecuteDataset(co.connect(), CommandType.Text, commandtext); 
           
    ReportDataSource datasource = new ReportDataSource("DataSet_Report_Items", thisDataSet.Tables[0]); 
           
    ReportViewer1.LocalReport.DataSources.Clear(); 
                      
           
    ReportViewer1.LocalReport.DataSources.Add(datasource); 
           
    ReportViewer1.LocalReport.Refresh();Here Is the Solution 
     

    Monday, October 24, 2011 4:07 AM
  • User992473171 posted

    See this Link

    http://forums.asp.net/t/1681381.aspx/1?Dynamically+binding+dataset+to+the+Report+rdlc

    You can even use stored procedure. on the above technique

     

    Copy this Link in new browser window if not working

    Monday, October 24, 2011 4:08 AM
  • User992473171 posted

     

    Go to that Site And Download the Application , install It. You can find a dll in the bindirectory named Microsoft.ApplicationBlocks.Data;

    Add reference and use  the code that I posted Above.... It works fine for me.........

    http://www.microsoft.com/download/en/details.aspx?id=435

     

    Monday, October 24, 2011 4:14 AM
  • User1144390192 posted

    hi Niyaskc

    thank you for reply

    but in code you send me you use dataset run time (Ado.net)and bind it to report run time

    you know, i want to set dataset in rdlc report design time as the rdl reports

    when we convert rdlc to rdl in design mode you can see we loose dataset and parameters

    thanks again

    Monday, October 24, 2011 4:38 AM
  • User992473171 posted

    Hi,

     

    I am talking about RDLC report only. Here, You can create a dataset (On report Design ) and use this datatable to design your report (In my report I used "DataSet_Report_Items") . So Now we have to bind this this dataset at run time. For that we can use the code below.

    DataSet thisDataSet = new System.Data.DataSet();

    thisDataSet =SqlHelper.ExecuteDataset(co.connect(), CommandType.Text, commandtext);

    ReportDataSource datasource = new ReportDataSource("DataSet_Report_Items", thisDataSet.Tables[0]);

    ReportViewer1.LocalReport.DataSources.Clear();

    ReportViewer1.LocalReport.DataSources.Add(datasource);

    ReportViewer1.LocalReport.Refresh();

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 24, 2011 6:56 AM