Creating Dataset Dynamically and passing to ReportViewer

Question

all i did is just create a reportViewer in the form, then i have this code:

        SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=G:\I.S\C#\billingSystem\Store.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");


        private void Form1_Load()
        {
            runRptViewer();
            cn.Open();
        }

        private void rptGetDataset()
        {
            DataSet ds = new DataSet();
            ds.DataSetName = "dsNewDataSet";
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM NewBill", cn);
            ds.GetXmlSchema();
            da.Fill(ds);
            ds.WriteXmlSchema(@"G:\I.S\Testoooooooo\Testoooooooo\Dataset1.xsd");
            ds.WriteXml(@"G:\I.S\Testoooooooo\Testoooooooo\Dataset1.xml");
        }

        private DataTable getData()
        {
            DataSet dss = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM NewBill", cn);
            da.Fill(dss);
            DataTable dt = dss.Tables["NewBill"];
            return dt;
        }

        private void runRptViewer()
        {
            this.reportViewer2.Reset();
            //this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("Report.rdlc");
            this.reportViewer2.LocalReport.ReportPath =(@"G:\I.S\Testoooooooo\Testoooooooo\Report1.rdlc");
            ReportDataSource rds = new ReportDataSource("dsNewDataSet_NewBill", getData());
            this.reportViewer2.LocalReport.DataSources.Clear();
            this.reportViewer2.LocalReport.DataSources.Add(rds);
            //this.reportViewer2.DataBind();
            this.reportViewer2.LocalReport.Refresh();
        }
    }

i have two reportViewer the reportViewer1 work but in case the directory of the db has change it will not work, so thats why i try in another reportViewer, to make it work even if the directory of the db changed, i can just change the Connection string.

The problem is the report don't show anything, i think the problem in the code:

//this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("Report.rdlc");

this is a windows form so there is no server, ive change it to:

this.reportViewer2.LocalReport.ReportPath =(@"G:\I.S\Testoooooooo\Testoooooooo\Report1.rdlc");

and this one dont work:

//this.reportViewer2.DataBind();

i cant understand this two lines, does it mean to create a Dataset1.xsd and Dataset1.xml, or just edit them. ds.WriteXmlSchema(@"G:\I.S\Testoooooooo\Testoooooooo\Dataset1.xsd"); ds.WriteXml(@"G:\I.S\Testoooooooo\Testoooooooo\Dataset1.xml"); if possible i need a steps from creating every thing to codding that will be great.

Solution 1

How did you create your dataset? Is it SQL or from Objects in memory? If you havent created a dataset, you do need to create one before the report can work properly. This might help: http://shrutikapoor-ubc.blogspot.com/2013/05/using-business-objects-in-report-viewer.html

Solution 2

To use the report viewer in your C# WinForm project add the following code to a button or inthe form_load:

string strConnectionString = "Data Source=(local);Initial Catalog=Projects_DB;Integrated Security=True";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("sp_GetProject " + "'0660CAD6-6F1A-4D19-A1FD-17BF3655AC98'");
cmd.CommandType = CommandType.Text;
cmd.Connection = new SqlConnection (strConnectionString);
da.SelectCommand = cmd;

da.Fill(ds,"DataSet1");

reportViewer1.ProcessingMode = ProcessingMode.Local;
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", ds.Tables[0]));
reportViewer1.LocalReport.Refresh();
reportViewer1.RefreshReport();

Assuming that you have a stored procedure that accepts @ProjectID parameter. You have to set the cmd.CommandType = CommandType.Text if you pass these parameters along with the sql command. However, if you don't want to pass parameters just change the commandType to cmd.CommandType = CommandType.StoredProcedure.

Below is the stored procedure used in this example:

CREATE PROC [dbo].[sp_GetProject]
    @ProjectID      nvarchar(50)=NULL

AS
BEGIN

   SELECT ProjectID, ProjectName FROM Projects
    WHERE 
    (@ProjectID IS NULL) OR (ProjectID = @ProjectID)

END

Solution 3

Microsoft has a pretty good walkthrough here...

http://blogs.msdn.com/b/sqlforum/archive/2011/04/28/sql-reporting-services-ssrs-bind-dynamic-dataset-to-your-local-report-with-reportviewer.aspx