Email Report from MVC application

[William]

We have created a set of reports in VS that run just fine.  There is a listing of Orders with a button that launches each report based upon the row selected in the listing.  Now they would like me to add another button to simply email out the report for the selected row.  So what I have done is:

1. Taken existing VS report and imported them into the StandAlone Designer.
2. Saved the .tdrx files to the app_Data folder.
3. Used the XML Deserializer to get the report.
4. Render the report as a PDF. (See issue below)
5. Attached the PDF to an email and send.

The basics work, however I am having trouble fully rendering the report due to the data connection. 

Issue:
I can't seem to figure out if the connection string from the original VS report; the string needs to be modified in the .tdrx or (as in my code below) programically added just before rendering????

If needed at run time, I have 2 SQL data sources in the original report, how do I do multiples in my code?

public void MailReportWparam(string Neededreport,
            string param,
            string paramValue,
            string reportNames,
            string from,
            string to,
            string subject,
            string body)
        {
            ReportProcessor reportProcessor = new ReportProcessor();

            System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
            settings.IgnoreWhitespace = true;

            using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create("C:\\Development\\PFSc2\\PFSc2\\PFSc2\\App_Data\\" + Neededreport + ".trdx", settings))
            {
                //Generate Report from XML Template
                Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =
                    new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();

                Telerik.Reporting.Report report = (Telerik.Reporting.Report)
                    xmlSerializer.Deserialize(xmlReader);
                    report.Name = reportNames;
                //Set Database connection
                //Edit for Production
                    Telerik.Reporting.SqlDataSource dataSource = new Telerik.Reporting.SqlDataSource();
                    dataSource.ConnectionString = "Data Source=ITDESKTOP\\PFS;Initial Catalog=PFS;Persist Security Info=True;User ID=########;Password=##########";
                    dataSource.SelectCommand = "SELECT        KF_Procurement.ID, KF_Procurement.Vendor_ID, KF_Procurement.Receiving_Date, KF_Procurement.Create_Date, KF_Procurement.BOL_ID, KF_Procurement.BOL_Date, KF_Procurement.PO_ID, KF_Procurement.PO_Date, KF_Procurement.Rec_Del_Method_ID, KF_Procurement.Temperature,KF_Procurement.Confirm_Date, KF_Procurement.Sent_Date, KF_Procurement.POType_ID, KF_Procurement.Revision_Date, KF_Procurement.warehouse_ID, KF_Procurement.VendorAddressID, KF_Procurement.Trucking_PackType_ID, KF_Address.TypeID, KF_Address.IsActive AS Expr2, KF_Address.Street1, KF_Address.Street2, KF_Address.City, KF_Address.StateID, KF_Address.State, KF_Address.CountryID, KF_Address.Country, KF_Address.Postal, KF_Address.Start_Date AS Expr3, KF_Address.End_Date AS Expr4, KF_Address.ID AS AddrID, KF_Address.Ven_ID AS AddrVenID, KF_Address.Store_ID, KF_Address.Cust_ID, KF_Address.Ware_ID, KF_Address.Person_ID, KF_Address.Owner_ID AS Expr5, KF_Address.IsPrimary, KF_Address.Owner_Type, KF_BOLs.ID AS BOLID, KF_BOLs.Date, KF_BOLs.Number, KF_BOLs.POType_ID AS BOLPOtypeID, KF_BOLs.POType, KF_BOLs.Status_ID, KF_BOLs.OwnerType_ID, KF_BOLs.Owner_ID AS BOLOwnID, KF_BOLs.DeliveryDate, KF_BOLs.Comment, KF_BOLs.Issued, KF_BOLs.Date_Issued, KF_BOLs.InsertDt, KF_BOLs.InsertBy,  KF_BOLs.UpdateDt, KF_BOLs.UpdateBy, KF_BOLs.Old_Sys_Num, KF_Person.FName, KF_Person.LName, KF_Person.FullName, KF_Person.OwnerID,  KF_Person.IsActive AS PerIsActive, KF_Person.Start_Date AS PerStartDt, KF_Person.End_Date AS PerEndDt, KF_Person.Vendor_ID AS PerVenId,  KF_Person.Cust_ID AS PerCustID, KF_Person.Store_ID AS PerStoreID, KF_Person.ID AS PersonID, KF_Person.WrHse_ID, KF_Person.IsPrimary AS PerIsPrimary,  KF_Person.UserID, KF_Phone_Email.Owner_ID AS PhEmOwnId, KF_Phone_Email.Phone_Number AS PhEmPhNum, KF_Phone_Email.EmailAddress AS PhEmEmail,  KF_Phone_Email.IsActive AS PhEmIsActive, KF_Phone_Email.Start_Date AS PhEmStartDt, KF_Phone_Email.End_Date AS PhEmEndDt, KF_Phone_Email.Type_ID,  KF_Phone_Email.ID AS PhEmID, KF_Phone_Email.Cust_ID AS PhEmCustID, KF_Phone_Email.Ven_ID AS PhEmVenID, KF_Phone_Email.Person_ID AS PhEmPerId,  KF_Phone_Email.Ware_ID AS PhEmWarID, KF_Phone_Email.Store_ID AS PhEmStoreID, KF_Phone_Email.IsPrimary AS PhEmIsPrimary, KF_Vendor.ID AS VenID,  KF_Vendor.Name FROM            KF_Procurement LEFT OUTER JOIN KF_Vendor ON KF_Procurement.Vendor_ID = KF_Vendor.ID LEFT OUTER JOIN KF_Person ON KF_Vendor.ID = KF_Person.Vendor_ID LEFT OUTER JOIN KF_Phone_Email ON KF_Vendor.ID = KF_Phone_Email.Ven_ID AND KF_Person.ID = KF_Phone_Email.Person_ID LEFT OUTER JOIN KF_BOLs ON KF_Procurement.BOL_ID = KF_BOLs.ID FULL OUTER JOIN KF_Address ON KF_Vendor.ID = KF_Address.Ven_ID AND KF_Procurement.VendorAddressID = KF_Address.ID AND KF_Person.ID = KF_Address.Person_ID WHERE        (KF_Procurement.ID = @ProcID) AND (KF_Person.IsPrimary = 1)";
                    dataSource.Name = "ProcDataSource";

                    report.DataSource = dataSource;
                //Feed report paramaters
                    report.ReportParameters[param].Value = paramValue;
                //Create PDF of report
                    RenderingResult result = reportProcessor.RenderReport("PDF", report, null);
                    MemoryStream ms = new MemoryStream(result.DocumentBytes);
                    ms.Position = 0;

                //Create Email Message with Attachments
                    MailMessage msg = new MailMessage(from, to, subject, body);
                    Attachment attachment = new Attachment(ms, report.Name + ".pdf");
                    msg.Attachments.Add(attachment);
                    SmtpClient client = new SmtpClient();
                    client.Send(msg);
           }
}

[Peter]

Hi William,

Generally the connections strings are stored in the report definition (trdx) or if you have chosen to save the connection string in the SqlDataSource wizard, the connection string will be saved in the default application configuration file and the SqlDataSource.ConnetionString will be set to the connection string name. You can easily check that if you open the trdx file with a text editor or with the standalone Report Designer.

If the connection string is saved by name you will have to add a connection string with the same name in the current application configuration file web.config. Otherwise if the connection string is saved in the report definition and it is valid you don't have to take any further actions.

Regards,
Peter
Telerik

[William]

I have tried to put into the .TDRX the following connection strings:

<SqlDataSource ConnectionString="Data Source=ITDESKTOP\PFS;Initial Catalog=PFS;Persist Security Info=True;User ID=##########;Password=#########" SelectCommand="SELECT  KF_Procurement.ID, ..." Name="ProcItemDataSource">
<SqlDataSource ConnectionString="Data Source=ITDESKTOP\PFS;Initial Catalog=PFS;Persist Security Info=True;User ID=##########;Password=#########" SelectCommand="SELECT  KF_Procurement.ID, ..." Name="ProcDataSource">

I also removed the setting of the database connection from the method.

And when I run it I get

System.NullReferenceException: Object reference not set to an instance of an object.

The error is on this line:
 RenderingResult result = reportProcessor.RenderReport("PDF", report, null);

Is this the right syntax for the .TRDX?

[Peter]

Hi William,

We highly recommend to use the standalone Report Designer to modify the report definitions. The report definitions are serialized in XML thus some characters may have to be encoded. Try to set up the trdx with the standalone designer and make sure that the report is working in preview. 

Additionally in the provided snippet we have noticed that the provider name is missing from the connection string. Here is an example of a correct connection string:

<SqlDataSource ConnectionString="Data Source=.\sqlexpress;Initial Catalog=AdventureWorks;Integrated Security=True" ProviderName="System.Data.SqlClient"

Regards,
Peter
Telerik