Link to home
Start Free TrialLog in
Avatar of canuckconsulting
canuckconsultingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Connect .Net Entity Framework to Crystal Report

I have created an ASP.Net application in c# and need to add display a Crystal report control Order Table.  The application has been designed using the Entity Framework and I am unsure how to create and display a Crystal Report based on these objects.  In my report I need to use the following tow Order and OrderDetail tables:


Order Table
ID
CustomerName
CustomerOrderNumber

OrderDetail Table
ID
OrderID --FK to Order
ProductName
Quantity

I have created a Crystal report OrderSummary.rpt and selected the corresponding .Net objects.  I then created a web page and added the Crystal Reports Viewer pointing to the new report.  I am stuck if I run the report I get the error “Database logon failed.”  If I have a  List<Order> can someone please advise how I can get this Crystal Viewer to use this as data?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should set the datasource of the report to the instance of your entity class before to run  the report. Otherwise the report will try to retrieve he information from a database.
Avatar of canuckconsulting

ASKER

Do you have an example of doing this (setting datasource to an entity)?  I can do this with a dataset but am not able to get even a basic example working with EF.  Ideally I need to pass in two linked tables.  With datasets I Filll() each table up...how can I do this with EF?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ultimately it seems EF cannot be used directly with Crystal.  In the end I creating a data set in the creating only the tables I needed.  Then for the report I "filled" each table as follows:


            var connectionString = ConfigurationManager.ConnectionStrings["WWD_SQLServer"].ConnectionString;

            //load each table in report
            DataSetFWOrders myDS = new DataSetFWOrders();
            using (var myConnection = new SqlConnection(connectionString))
            {
                var sql = "SELECT * FROM FWOrder " + whereClause;
                using (var myDA = new SqlDataAdapter(sql, myConnection))
                {
                    myDA.Fill(myDS, "FWOrder");
                }

                sql = "SELECT * FROM Buyer where ID in (select BuyerID from fworder " + whereClause + ")";
                using (var myDA = new SqlDataAdapter(sql, myConnection))
                {
                    myDA.Fill(myDS, "Buyer");
                }

                sql = "SELECT * FROM Brand where ID in (select BrandID from fworder " + whereClause + ")";
                using (var myDA = new SqlDataAdapter(sql, myConnection))
                {
                    myDA.Fill(myDS, "Brand");
                }

                sql = "SELECT * FROM FWOrderDetails where FWOrderID in (select ID from fworder " + whereClause + ")";
                using (var myDA = new SqlDataAdapter(sql, myConnection))
                {
                    myDA.Fill(myDS, "FWOrderDetails");
                }
            }

            //Generate PDF report from data
            ExportFormatType format = ExportFormatType.PortableDocFormat;
            using (var rpt = new CrystalReportFWOrders())
            {
                rpt.SetDataSource(myDS);
                result = rpt.ExportToStream(format);
            }
            return result;