Introduction
Crystal Reports. For a lot of people including myself, it's a Love / Hate relationship. On one hand, it (usually) integrates nicely with Visual Studio, and it is probably the most widely known reporting system for .NET developers. I was one of the first to jump onto the VS2012 bandwagon, however to my dismay, I quickly learned Crystal Reports was not available for the new IDE! But after multiple delays, Crystal is here, and my lord it angered me. After hours of chat time with tech support, and some lucky mouse clicks, I finally got it installed and working properly...only to find that for some odd reason, SAP decided NOT to support Entity Framework, and allow POCOs to be used as a
RecordSource for the report. Our whole product is based around Entity Framework. I scoured the Internet for a solution, but to no avail. So I sat down and really thought about it, and worked out a fairly elegant way to use the information in the POCOs to populate your Crystal Reports using Reflection and little bit of Generics.
Background
A pretty solid understanding of Entity Framework (EF) would be beneficial before proceeding, considering this is an article about Entity Framework....
Don't worry if you aren't too sure how to take advantage of all the features Reflection has to offer. I will explain my code step by step. Same goes for using Generics.
You'll want to have Crystal Reports installed, any version will do. I will be working with SAP Crystal Reports, developer version for Microsoft Visual Studio, the newest release as of January 2013.
I will be working with Entity Framework 5.x If you do not have EF installed yet, please go to your Package Manager Console under Tools -> Library Package Manager -> Package Manager Console. In the console type "Install-Package EntityFrameWork -pre" without the quotes. This will get you prepared to use EF. However, this article will not explain how to use EF. There are many tutorials around the web for that.
Before We Get Into It
The following class is going to be our EF POCO that we will be using to populate our Crystal Report:
Partial Public Class WorkOrder
Public Property ID As Integer
Public Property WorkOrderNumber As Integer
Public Property Description As String
Public Property PartNumber As Integer
Public Property Quantity As Integer
End Class
This is a very basic table / class but it will serve it's purpose for this article.
You are going to want to create a new Windows Form. Click Project - > Add Windows Form...
Name your new form 'frmReportViewer' and click the Add button. I'm sure you can guess we are going to use this form to view our
Crystal Report when we are finished. Resize this form to a reasonable size for viewing reports. Next we want to add a CrystalReportViewer
object to the form. From your Toolbox on the left, scroll down to Reporting. If Crystal Reports is installed correctly,
you should see CrystalReportViewer under the Reporting node. Drag the report viewer onto your form. Now you've got something to display
your report. Rename your report viewer in the properties pane to crvReportViewer.
Now we need a report! But before we can create one, we reach our first road block. If you aren't using EF,
you would normally create a connection to your database, and bind your report to a table from your data; selecting
the fields you would like to display and presto, you got your report. By binding the data to your report,
Crystal has the information it needs, more accurately, it knows the Schema of the table its bound to. But the joy of using EF is you are working with objects, and not directly with the data. A Crystal Report needs to know the Schema of the table(s) its going to be bound to before displaying the information. So how to we tell Crystal the 'Schema' of our EF object?...
Creating an .XSD Schema File
I won't go into the gritty details of the .XSD file, but in short, all it really is is a Dataset designer. You can graphically create a Dataset, adding tables, or DataTables to your Dataset, which will allow you to create the Schema for your EF object.
Click on Project ->Add New Item -> Data (under Common Items on the left Tree Structure) -> DataSet. Name your DataSet 'dsWorkOrderSchema' and click the Add button. This will bring to a boring designer. Let's make it less boring! From the Toolbox, drag a
DataTable onto your designer window. See, less boring! Click on the table name (currently name DataTable1), and change it to WorkOrder. Next you want to right-click on your table, from the context menu click 'Add', then select 'Column'. Rename your new column to 'ID'. Create another new column and rename it 'WorkOrderNumber'. See where I'm going? Repeat this for the rest of the properties from your
WorkOrder class described at the start of this article. Ensure you name the columns the same as your property names (exactly! Is Case Sensitive). Once completed, you now have a Schema for your EF object!! You can use this object to start designing your report. So lets do that...
Creating the Crystal Report
Before continuing, Save your project. Now we need to create our report. Click Project-> Add New Item -> Reporting -> Crystal Reports. Name your report 'crWorkOrder'. Click the Add button. A window will pop up. Make sure the 'Using the Report Wizard' radio button is selected and click 'OK'. A new window will pop up. From your 'Available Data Sources' pane on the left, you want to expand the 'Project Data' node, then expand the 'ADO.NET DataSets' node, and you should see your
WorkOrder DataSet. Select it, click the '>' button in the center of the window and click 'Next'. Now here is where you will choose the fields you want to display on your report. For this example we are just going to select all of them so click the '>>' button and click 'Finish'. If you click 'Next' instead of 'Finish', you can fine tune things a little bit by grouping your fields. You can play with these options on your own, for this article we will be ignoring grouping.
Now you have a very simple report! We are not going to go into formatting of the report, I'll leave that for your own exploration. Now Save your project, and we will dive into the code.
The Cure For The "Banging Your Head Against The Desk" Syndrome
To pass data to your report, like I mentioned earlier, you would usually bind your report to a database table, and Crystal would take care of the rest. Another way would be in code. First create DataTables, fill the DataTables with data, then create a
TableAdapter, use the TableAdapter to fill a DataSet, then bind the
DataSet to the report. Now this is the right way to do it and common practice. We actually did two of these steps. Remember in the DataSet designer when we dragged a DataTable onto the designer window and created the columns? There we created our DataTable and assigned it to a our
DataSet. Now here's the issue; how to we get the information from our EF object, and insert it into the
DataTable that is assigned to our Dataset so that we can bind our DataSet to our report to display?
In a perfect world, and kind of what I was expecting from SAP, this would work:
Dim crNewReport As New crWorkOrder
Dim context As New MyContext
Dim newWorkOrder As WorkOrder
newWorkOrder = context.Set(Of WorkOrder). Find(1)
crNewReport.Load("crWorkOrder.rpt")
crNewReport.SetDataSource(newWorkOrder) But it doesn't. Even though all the data you want to report resides in your WorkOrder EF Object, even tho the properties of your
WorkOrder EF Object exactly match the Schema we created in our .XSD
DataSet file that we bound to the report, Crystal Reports still requires you to pass a filled
DataSet object as it's source. Crap. Okay, so now try and take your information from your EF Object, and fill your DataSet...
...Still trying? Don't feel bad. Here is the tedious, long winded way of doing this...
Dim dsDataSet As New DataSet
Dim dtDataTable As New DataTable("WorkOrder")
Dim drNewRow as DataRow
Dim newContext As New MyContext
Dim newWorkOrder As WorkOrder
Dim crNewReport As New crWorkOrder
dtDataTable.Columns.Add("ID")
dtDataTable.Columns.Add("WorkOrderNumber")
dtDataTable.Columns.Add("PartNumber")
dtDataTable.Columns.Add("Description")
dtDataTable.Columns.Add("Quantity")
dsDataSet.Tables.Add(dtDataTable)
newWorkOrder = newContext.Set(Of WorkOrder).Find(1)
drNewRow = dsDataSet.Tables(0).NewRow
drNewRow("ID") = newWorkOrder.ID
drNewRow("WorkOrderNumber") = newWorkOrder.WorkOrderNumber
drNewRow("Description") = newWorkOrder.Description
drNewRow("PartNumber") = newWorkOrder.PartNumber
drNewRow("Quantity") = newWorkOrder.Quantity
dsDataSet.Tables(0).Rows.Add(drNewRow)
crNewReport.Load("crWorkOrder.rpt") This is for just one
WorkOrder record! If you had multiple WorkOrders you would like to display, you would loop through an
IEnumerable object of WorkOrder and add each row. And this is for just ONE report. You would have to write this out for each report you wanted to generate, making sure you know the names of all the columns you want to add data to. And what if you had multiple tables that you wanted to bind to the dataset? (I know this code can be shortened, but I wanted to give you an idea of how frustrating it can become if you were creating multiple, sometimes hundreds, of different reports).
So what's the solution? Generics and Reflection. I'm just going to show you the code I designed first, then I will go through it and explain how it makes every part of your life easier...
Sub EntityToDataSet(Of TEntity)(ByRef ds As DataSet, ByVal MyEntity As TEntity)
Dim strTableName As String
Dim drNewRow As DataRow
Dim EntityFields = GetType(TEntity).GetProperties.Where(Function(a) a.CanRead)
strTableName = MyEntity.GetType.FullName
drNewRow = ds.Tables(strTableName).NewRow
For Each field in EntityFields
If drNewRow.Table.Columns.Contains(field.Name) Then
drNewRow(field.Name) = field.GetValue(MyEntity, Nothing)
End If
Next
ds.Tables(strTableName).Rows.Add(drNewRow)
End Sub You would use it like this...
EntityToDataSet(dsMyDataSet, newWorkOrder)
That's it! Have a good day!
Okay, I guess I'll explain. First, I would (and did) create this Sub Procedure in a Module, to allow your entire Project access to it. I will go through it line by line.
The First Line
EntityToDataSet(Of TEntity)
If this is your first foray into Generics this can be a hard concept to grasp. After the method name, by writing (Of TEntity), you are basically saying 'I don't care what kind of object
TEntity is, but allow it to be used/passed as an argument for this method'. Now we could have omitted this, but when passing our arguments (which I'll get to in a second), you would have had to write 'ByVal MyEntity As WorkOrder'. Now the problem with this is we would have to write a separate procedure for EACH and EVERY Entity Framework POCO that we would be using in our reports! Some systems can have hundreds, even thousands of POCOs. Do you really want to write a procedure that is nearly identical a thousand times!? By allowing a Generic object to be passed, this one procedure will work for ALL of your POCOs! Cool eh?
(ByRef ds As DataSet, ByVal MyEntity As TEntity)
By passing a Reference to a DataSet, any action we perform in our procedure will directly effect / change the
DataSet. Simple concept, I won't get into it. If you are unclear of the difference between 'ByRef' and 'ByVal', please refer to a few beginner tutorials that explain the differences.
The next argument is our EF POCO we want to get our data from. Again, because we are using Generics, this object can be any object. In our case, we will be using our WorkOrder object. We do not need to pass a reference to our WorkOrder object, as we only need to pull the data from it. We don't want or need to change any of the properties of the object.
Lines 2 and 3
Dim strTableName As String
Dim drNewRow as DataRow
I don't think I need to go into detail here. strTableName will store the name of the table in the DataSet we would like to add a record to. drNewRow will be used to hold the information from our WorkOrder object. Each row is equal to 1 record. Just as each WorkOrder object is equal to 1 record in your database. In this scenario, we will only be passing one WorkOrder object to this procedure. At the end of this article, I will quickly explain how you can pass multiple objects, even of different types,
to this procedure.
Line 4
Dim EntityFields = GetType(TEntity).GetProperties.Where(Function(a) a.CanRead)
Reflection is a wonderful thing. I can't get into the details of Reflection as it is was beyond the scope of this article. You could spend months and months studying up on Reflection and all it's glory.
GetType(TEntity)
Because we are passing in a Generic object, we still need to know why type of object we are working with.
GetType(TEntity) will do just that. Once we know the type, we can then perform...
GetType(TEntity).GetProperties
The method name pretty much sums it up. Remember the properties we created for our WorkOrder object and the beginning (WorkOrderNumber, PartNumber, etc)? GetProperties is getting all the properties of the WorkOrder object.
GetType(TEntity).GetProperties.Where(Function(a) a.CanRead)
If you are not familiar with Lambda functions or Predicate Functions, please do a little Googling! By using this Lambda, we are basically saying, 'Return only the properties from which we can read from'. In this case, it's all properties as they are all public. Now the EntityFields object contains an array of all the property names of our WorkOrder object.
Line 6 and 7
strTableName = MyEntity.GetType.FullName
drNewRow = ds.Tables(strTableName).NewRow
Again we use Reflection here.
MyEntity.GetType.FullName is actually finding out the name of object, more specifically, the Class name of the object. For example, even though we name our WorkOrder object newWorkOrder, when we call
MyEntity.GetType.FullName, it will return 'WorkOrder', not newWorkOrder. Remember when we created our .XSD DataSet file? What did we name our DataTable name? 'WorkOrder'! As long as we follow this convention for creating .XSD DataSets (Naming the DataTables with the same name as our POCOs), we can get the table name we want to reference without even knowing the type of EF object we have passed.
The next line is instantiating our DataRow object. We are saying 'I want drNewRow to represent a new record in our strTableName (in this case 'WorkOrder') table.
Lines 9 to 13
For Each field In EntityFields
If drNewRow.Table.Columns.Contains(field.Name) Then
drNewRow(field.Name) = field.GetValue(MyEntity, Nothing)
End If
Next
Now we are going to loop through all the fields (or all the properties) of our WorkOrder object which we stored in the EntityFields array. To get the name of the property, we use 'field.Name'. This returns the name of the property. Each field.Name represents a column name in our DataTable we created in our .XSD file.
The conditional If statement is saying asking 'in the DataTable of the new record we are creating, is there a column with the name field.Name'? For example, the first time we run through the loop, field.Name might equal 'WorkOrderNumber'. So the if statement is checking to see if there is a column with the name 'WorkOrderNumber' in the
DataTable. Of course we know that in fact there is a column with this name, so now we want to add some data to this column.
Saying 'drNewRow(field.Name)' references the column in the DataTable with the name field.Name.
We assign the value of the field by saying field.GetValue(MyEntity, Nothing). Just as .Name gives us the name of the property,
.GetValue will give us the value stored in the property. The .GetValue function takes two parameters; the object to which we want to get the property value from (We want the value from a property of our
MyEntity object), and an optional Index value for indexed properties. We don't have Indexed properties, so we pass it a Null, or Nothing value.
The loop will continue to repeat these steps for each property of our
MyEntity object, until all the data of our object is stored in our new drNewRow object.
Line 14 ;
ds.Tables(strTableName).Rows.Add(drNewRow)
Finally, we are adding our newly created row / record to the DataTable that belongs to our DataSet object. We have officially used our Entity Framework object to fill a DataSet. We can now bind our DataSet to our Crystal Report, and report to our hearts content!
The Finale
Here is how we can put our new procedure in action! You can put this in the
Form_Load event of your frmReportViewer Form.
Dim dsWorkOrder As New dsWorkOrderSchema
Dim newWorkOrder As WorkOrder
Dim newContext As New MyContext
Dim crNewReport As New crWorkOrder
newWorkOrder = newContext.Set(Of WorkOrder).Find(1)
EntityToDataSet(dsWorkOrder, newWorkOrder)
crNewReport.Load("crWorkOrder.rpt")
crNewReport.SetDataSource(dsWorkOrder)
crvReportViewer.Report = crNewReport
I wanted to go into as much detail as possible with this article because I spent a lot of time searching the web for solution to this problem, and came up relatively empty. I truly hope this article helps at least one person out. At least then the 4 hours I spent writing it will not be in vain! Happy Coding!
Taking The EntityToDataSet Procedure One Step Further
I promised I would show you how you could modify the procedure to accept multiple EF POCO's. I'll show you the code, but I believe you have enough understanding now to realize how the code works without my explanation.
Sub EntityToDataSet(Of TEntity)(ByRef ds As DataSet, ByVal MyEntities As List(Of TEntity))
Dim strTableName As String
Dim drNewRow As DataRow
For Each POCO In MyEntities
Dim EntityFields = POCO.GetType.GetProperties.Where(Function(a) a.CanRead)
strTableName = POCO.GetType.FullName
drNewRow = ds.Tables(strTableName).NewRow
For Each field In EntityFields
If drNewRow.Table.Columns.Contains(field.Name) Then
drNewRow(field.Name) = field.GetValue(POCO, Nothing)
End If
Next
ds.Tables(strTableName).Rows.Add(drNewRow)
Next POCO
End Sub
