Export to Excel using Dynamic Data

If you are using Dynamic Data ASP.NET web application you are likely using the Entity Framework and a Domain Service. With these key technologies it is easy to add an “Export to Excel” link to all your List pages in your project. The best part is that once you add this, you don’t have to write it again for each table. This is a write once use for everything solution.

To implement the writing of data to Excel I don’t want to use anything that requires Microsoft Excel to be installed (except by the people that are downloading the file). I chose a free solution from CarlosAg.net called Excel Xml Writer. Click here to go to the product page and download it. It is implemented using C# and is a managed solution (no COM, interops, etc). The file that is generated is a xml version of the Excel file format so it is easy to debug and tweak if needed. Best of all the installation is simple: just add a reference to the single .dll. There should be no other dependencies.

I wanted to use the same column names that are in List page that I am exporting and I didn’t want to have to hard code them. I wanted them to be read from the MyDomainService.metadata.cs. If you use the DisplayAttribute to specify user friendly names, my code uses them instead of the column names. Also, not every property in the table are shown. It depends on what the MetaTable.GetScaffoldColumns() returns which is what the DefaultAutoFieldGenerator() for the List.aspx.cs page uses.

To encapsulate this logic, I created a class called PopulateExcelWorksheetForDynamicData.cs.  To download the source for the file click the filename. I’m not going to go through all the code step by step. However, the basic idea is that the Populate() method is called. You pass it an IQueryable object and the MetaTable information. Based on that the MetaTAble.GetScaffoldColumns() is called to determine what columns should be shown. For any foreign key columns I resolve to the related (called the parent table) and display the appropriate column based on the DisplayColumnAttribute or default DisplayColumn if one is not specified via the attribute.

The datatypes that are specified in the MetaTable are converted to Excel types. Special formatting such as DateTimes require a special format in order for Excel to know how to show them as dates. I make some assumptions about the format the user will want to see. This is hardcoded, but could be extended or changed.

You can tweak the formatting of the file as well. There are two formats I use. See: SetCannedStyle1(), SetDefaultStyle(). I haven’t used SetCannedStyle1() in a while, but I think it should still work.

That is the guts of the logic. Now it is just a matter of creating a button or a link on the List.aspx.cs page and executing code similar to the following.

protected void btnExportToExcel_Click(object sender, EventArgs e)
       {
           IQueryable queryResults = null;
           using (var service = new DistiPromoDomainService())
           {
               // execute the query that was used to populate the GridView.
               var selectMethodInfo = service.GetType().GetMethod(GridDataSource.QueryName);
               queryResults = selectMethodInfo.Invoke(service, null) as IQueryable;

  // add in any filters that are applied via the QueryExtender as well
   var queryExtenderExpressions = queryExtender.Expressions;

                foreach (DataSourceExpression expr in queryExtenderExpressions)
                {
                    queryResults = expr.GetQueryable(queryResults);
                }

               var workbook = new Workbook();
               var sheet = workbook.Worksheets.Add("Sheet 1");
               var excel = new PopulateExcelWorksheetForDynamicData(sheet);
               excel.Populate(queryResults, table);
               PopulateExcelWorksheetForDynamicData.SetDefaultStyle(workbook);
               // don't use spaces as this will cause issues. You can also try .xls, but you will get an corruption warning in IE.
               string filename = Title.Replace(" ", "-") + "-Export.xml";
               PopulateExcelWorksheetForDynamicData.SendToBrowserForDownload(workbook, filename, Context.Response);
           }
       }
You may want to tweak the filename. That should do it.

Automatic Auditing of Changes using Domain Service and Entity Framework

A special thanks to Fredrik Normen for providing the basis for my code. His great article is here. For a better understanding of the basic code, please read Fredrik’s implementation first. He does a good job of walking you through his code.

With that said I have changed most of his code and added logging to a database table using another Entity Framework (EF) model. You can download my updates to his code under the Source Code section.

Please note that only changes made through the Domain Service will be logged. If you don’t use a Domain Service to modify all your data that you want logged this solution is probably not for you. However, if you use a Domain Service and Entity Framework this is a pretty robust solution for adding auditing to your ASP.NET web application in minutes. I am using this with my Dynamic Data web application and it works great!

The code works because the Domain Service has a concept of a ChangeSet. When you save (update or insert or delete) a ChangeSet is created that basically shows what the original entities look like and what the current entities look like. This is all standard Domain Service stuff. Nothing I did. However, what I do is look at the original and current values and create EntityChangeInfo objects and PropertyChangeInfo objects. These then get written to the database where there is a record in the database for each field. This is very verbose for Inserts and for Deletes, but just write for Update. If you don’t need insert and delete auditing, you can easily comment them out.

To implement my solution, here are the basic steps (from my memory) to add auditing to you application.

1. Download and use the AuditLog-Create.sql to create an AuditLog table in a database.
2. Create an Auditing directory in your Services directory (if you have one).
3. Download and copy the AuditLogger.cs, ChangeSetHelper.cs, EntityChangeInfo.cs, PropertyChangeInfo.cs to the Auditing directory you just created.
4. In your Models directory (if you have one) create a new ADO.NET Entity Framework Data Model that points to the database where you created the AuditLog table. Call the model AuditModel. The only table you want to add to it is the AuditLog table. You don’t need a Domain Services or anything else for this. We will reference it directly.
5. Download and copy the snippet from the DomainService-PersistChangeSetSnippet.cs and paste it in your domain service file that you already should have in your project. This is the file for the domain service that you want to audit. If you don’t want INSERT or DELETE auditing you can comment out the appropriate line in this snippet.

That is it. You should now be able to run your application, make some changes and see the changes show up in the AuditLog table.

You can then execute a query something like this to see the history of a particular record in a table.

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Insert'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Update'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Delete'

My Comments

You may notice that I log my times in UTC and that the date/time is the same for a given update, insert, or delete. In other words, when an update is written to the database, multiple records are created (one for each column that changes), but the CreatedDateTimeUTC field is stamped with a ONE datetime.

When values are inserted, the OriginalValue column will ALWAYS blanks. When a record is deleted, the CurrentValue column will ALWAYS be blank.

If you have a properties that you have added to an entity they will have an initial value and the computed value in some cases. If this is the case for you, you may want to hard code these property names the ChagneSetHelper.GetPropertiesChangeInfo method as properties to not log. Just like I have done for the EntityState property. Up to you.

Source Code

AuditLog-Create.sql

AuditLogger.cs

ChangeSetHelper.cs

EntityChangeInfo.cs

PropertyChangeInfo.cs

DomainService-PersistChangeSetSnippet.cs

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

You will get this error when you try to use the msdb.dbo.sp_send_dbmail stored proc and the user that you are using does not have permissions to execute the stored proc.

The user must be a member of the DatabaseMailUserRole in the msdb database. To give the user permission to do so, execute the following:

EXEC msdb.dbo.sp_addrolemember 'DatabaseMailUserRole', 'username here'

NOTE: If the user has not be added to the msdb database, then you will get an error like the following:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'username here' does not exist in this database.

To fix this, you can execute the following

use msdb
go
CREATE USER [username here] FOR LOGIN [username here]

You can also use MS SQL Management Studio to add the user to msdb and to the DatabaseMailUserRole in one step if you like the UI. :)