.net mvc 5 C# with crystal report

Question

I use .net mvc 5 c# repository pattern with database first approach, In my Service layer i calculate and apply group by condition on there and pass this data into viewmodel and razor view, my question is can i used this viewmodel (with data) for creating the crystal report from this viewmodel ? crystal report is installed on visual studio (2015). code information are

code on controller are

 public ActionResult Top20SupplierReport()
 {
 var AllSupplier = _supplier.Top20Supplier();
}

Service layer code are

public List<GroupBySupplierVM> Top20Supplier()
    {
        var AllSupplier = //code for get all supplier list from database
        var groupByData = from sup in AllSupplier 
                          group sup by sup .cf02supplier_Name into g
                          let TotalVol = g.Sum(x => x.cf08collection_Received_Volume)
                          let TotalAmount = g.Sum(x => x.cf08collection_Balance)
                          orderby TotalVol descending
                          select new GroupBySupplierVM
                          {
                              Key = g.Key,
                              Values = g.ToList(),
                              TotalReceivedVolume = Convert.ToDouble(TotalVol),
                              TotalBalance = TotalAmount
                          };
        return groupByData.Take(20).ToList();
    }

ViewModel are

    public class GroupBySupplierVM
        {
            public string Key;
            public List<SupplierVM> Values;
            [Display(Name = "Total")]
            public double TotalReceivedVolume { get; set; }
            public double? TotalBalance { get; set; }
    
        }
and 

    public class SupplierVM
        {
          
            public int cf02supplier_Id { get; set; }
    
            public string cf02supplier_Address { get; set; }
          
            public string cf02supplier_Name { get; set; }
    
            public string cf02supplier_City_Id { get; set; }
    
            public string cf02supplier_Telephone { get; set; }
    
            public string cf02supplier_MobileNo { get; set; }

            public decimal cf02supplier_Balance { get; set; }
             ......
             // other Entity are also there
            
        }

can i create crystal report from the GroupBySupplierVM ? if yes how to use on crystal report and how to show on view page ? anybody have knowledge about this how to use on crystal report. Please help me...

Answers below are misleading. I am able to pass List into reports. — Steve Greene, Apr 21 '19 at 14:45

score 1 · Answer 1 · answered Aug 11 '17 at 02:28

Yes you can. -Create a Data Table

Sample DataTable

-and my code

//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);

Use to Convert List to DataTable

-- My ReportHelper -- Create a static class a put this code

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);
    }
}}

score 0 · Answer 2 · answered Aug 02 '17 at 07:55

Crystal reports really only understands DataSets or DataTables. You have to convert your object collection to DataTable or DataSet and then setting it as the report's data source. see .NET - Convert Generic Collection to DataTable and Convert generic List/Enumerable to DataTable? for tips on creating your table.

Once you have your dataset/table, you need to create the xml schema required to design your report. This can be done with the WriteXml method. You only need this while designing the report. Locate this xml file, and create your report.

var table = groupByData.ToDataTable(); // using your extension method
// snippet for creating schema from table
using (var fs = new StreamWriter(xmlFile)) // XML File Path
{
    table.WriteXml(fs, XmlWriteMode.WriteSchema);
}

As for Crystal and MVC, not really. Crystal Reports is WebForms technology, so you'll have to have an aspx page to host the report viewer in your MVC application. You can use Microsoft.Aspnet.FriendlyUrl and routes.EnableFriendlyUrls(); to hide the extension.

The actual databinding is quite simple:

var table = groupByData.ToDataTable(); // using your extension method
report.SetDataSource(table);
viewer.ReportSource = report;

.net mvc 5 C# with crystal report

2 Answers2