Populating Crystal Report DataSource via Object and ExportToStream

Question

I am using VS 2015, Crystal Report is also up-to date, SQL Server 2012.

I want to populate the Crystal Report datasource via a viewmodel class and export it to PDF

 ReportDocument rptH = new ReportDocument();
 rptH.FileName = Server.MapPath("~/reports/InvoiceReportsSample1.rpt");

 var showInvoices =
               (from ids in context.Invoices
                where ids.InvoiceNumber == "01-04-2017-1"
                select new Invoice_Report_ViewModel()
                {
                    Invoiceid = ids.Invoiceid,
                    InvoiceNumber = ids.InvoiceNumber,
                    CustomerCompanyName = ids.CustomerCompanyName,
                    FirstBlankSpaceForPanel1 = ids.FirstBlankSpaceForPanel1,
                    MainDiscount = ids.MainDiscount,
                    MainTaxes = ids.MainTaxes,
                    MainTotal = ids.MainTotal,
                    TypeOfPortals = ids.TypeOfPortals,
                    TypeOfTickets = ids.TypeOfTickets,
                    SecondBlankSpaceForPanel2 = ids.SecondBlankSpaceForPanel2,
                }).First();

        var showInvoiceDetails = (from ids in context.InvoiceDetailses
                                  where
                                      ids.Invoiceid == showInvoices.Invoiceid
                                  select new InvoiceDetails_Report_ViewModel()
                                  {

                                  }).ToList();
       var querylist = new List<Invoice_Report_ViewModel> { showInvoices };
        rptH.Database.Tables[0].SetDataSource(querylist);
        rptH.Database.Tables[0].SetDataSource(showInvoiceDetails);
        rptH.Refresh();           
        Stream stream = rptH.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
        return File(stream, "application/pdf"); 

Please help I get an error

Data source Login Failed

but I am not connecting report to the database and also I have configure report using .NET object

Please help

Answer 1

I'm assuming that you are trying to set two datasources. You need to set them to related datatable. You can try something like below.

 rptH.Database.Tables[0].SetDataSource(querylist);
 rptH.Database.Tables[1].SetDataSource(showInvoiceDetails);

Answer 2

I've encountered this error, First you need to create a custom DataTable contains the fields in different tables.

then you need to add this code .ToDatable is from the ReportHelper

    //domResult -> List of your View Model
     DataTable dt = ReportHelper.ToDataTable(domResult);
    // LCDraft_Domestic--> Crystal Report
     LCDraft_Domestic rpt = new LCDraft_Domestic();
   //My Data Table
      rpt.Database.Tables["DraftData"].SetDataSource(dt);

To avoid system nullable error that crystal report throws, you need to convert your list query to DataTable

Create a static Class Name ReportHelper

 public static class ReportHelper
{



    public static DataTable ToDataTable<T>(this IList<T> items)
    {
        var tb = new DataTable(typeof(T).Name);

        PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (PropertyInfo prop in props)
        {
            Type t = GetCoreType(prop.PropertyType);
            tb.Columns.Add(prop.Name, t);
        }

        foreach (T item in items)
        {
            var values = new object[props.Length];

            for (int i = 0; i < props.Length; i++)
            {
                values[i] = props[i].GetValue(item, null);
            }

            tb.Rows.Add(values);
        }

        return tb;
    }

    /// <summary>
    /// Determine of specified type is nullable
    /// </summary>
    public static bool IsNullable(Type type)
    {
        return !type.IsValueType || (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
    }

    /// <summary>
    /// Return underlying type if type is Nullable otherwise return the type
    /// </summary>
    public static Type GetCoreType(Type type)
    {
        if (type != null && IsNullable(type))
        {
            if (!type.IsValueType)
            {
                return type;
            }
            else
            {
                return Nullable.GetUnderlyingType(type);
            }
        }
        else
        {
            return type;
        }
    }
    static TableLogOnInfo crTableLogonInfo;
    static ConnectionInfo crConnectionInfo;
    static Tables crTables;
    static Database crDatabase;

    public static void ReportLogin(ReportDocument crDoc, string Server, string Database, string UserID, string Password)
    {
        crConnectionInfo = new ConnectionInfo();
        crConnectionInfo.ServerName = Server;
        crConnectionInfo.DatabaseName = Database;
        crConnectionInfo.UserID = UserID;
        crConnectionInfo.Password = Password;
        crDatabase = crDoc.Database;
        crTables = crDatabase.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
        {
            crTableLogonInfo = crTable.LogOnInfo;
            crTableLogonInfo.ConnectionInfo = crConnectionInfo;
            crTable.ApplyLogOnInfo(crTableLogonInfo);
        }
    }
    //No Login
    public static void ReportLogin(ReportDocument crDoc, string Server, string Database)
    {
        crConnectionInfo = new ConnectionInfo();
        crConnectionInfo.ServerName = Server;
        crConnectionInfo.DatabaseName = Database;
        crConnectionInfo.IntegratedSecurity = true;
        crDatabase = crDoc.Database;
        crTables = crDatabase.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
        {
            crTableLogonInfo = crTable.LogOnInfo;
            crTableLogonInfo.ConnectionInfo = crConnectionInfo;
            crTable.ApplyLogOnInfo(crTableLogonInfo);
        }
    }

}