canuckconsulting
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Conne ctionStrin gs["WWD_SQ LServer"]. Connection String;
//load each table in report
DataSetFWOrders myDS = new DataSetFWOrders();
using (var myConnection = new SqlConnection(connectionSt ring))
{
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.PortableD ocFormat;
using (var rpt = new CrystalReportFWOrders())
{
rpt.SetDataSource(myDS);
result = rpt.ExportToStream(format) ;
}
return result;
var connectionString = ConfigurationManager.Conne
//load each table in report
DataSetFWOrders myDS = new DataSetFWOrders();
using (var myConnection = new SqlConnection(connectionSt
{
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.PortableD
using (var rpt = new CrystalReportFWOrders())
{
rpt.SetDataSource(myDS);
result = rpt.ExportToStream(format)
}
return result;