Thursday, March 22, 2012
Typically when asked to display a list data, lets say customers, we create a first name column, last name column and then iterate over the rows to produce a table. Something like this:
But recently I was asked to produce a pivot of this data, putting each customer in their own column instead of an individual row. After doing the obligatory internet searching I found no solution that I really liked. So I wrote my own…
Lets start by creating our customer object:
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
Now that we got him, lets mock up some data in our controller:
public ActionResult Index()
{
var customers = new List<Customer>();
for (int i = 0; i < 100; i++)
{
customers.Add(new Customer()
{
Id = i,
FirstName = string.Format("First {0}", i),
LastName = string.Format("Last {0}", i),
Age = i
});
}
ViewBag.Customers = customers;
return View();
}
Finally, the meat, using a bit of reflection magic lets get a “list” of all the properties on our customer, iterate over each of them to generate our rows, then iterate over each customer and get the “value” for that row.
<div style="width: 600px; overflow: auto">
<table style="white-space: nowrap;">
<thead>
<tr>
<th></th>
@foreach (var customer in ViewBag.Customers)
{
<th style="">
Customer @customer.Id
</th>
}
</tr>
</thead>
<tbody>
@foreach (var prop in typeof(Customer).GetProperties())
{
<tr>
<td>
@prop.Name
</td>
@foreach (var customer in ViewBag.Customers)
{
<td>
@prop.GetValue(customer, null)
</td>
}
</tr>
}
</tbody>
</table>
</div>
And ta-da we have a table with dynamically generated columns based on the number of “customers” in our collection.
Saturday, March 17, 2012
Ever had an excel file that looked like this?
and need an xml file that looks like this?
Converting takes 2 steps:
Step 1: Read the excel file into objects in memory
var products = new List<Product>();
using (var con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;Extended Properties=\"Excel 8.0;HDR=YES;\""))
using (var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "SELECT * FROM [Sheet1$]";
var dr = cmd.ExecuteReader();
while (dr.Read())
{
products.Add(new Product()
{
ProductGroup = dr[0].ToString(),
ProductSubGroup = dr[1].ToString(),
ProductNumber = dr[2].ToString()
});
}
}
Step 2: Use LINQ to group the objects into a hierarchy and convert them into xml
var xml = new XElement("ProductGroups",
products.GroupBy(x => x.ProductGroup).Select(x => new XElement("ProductGroup",
new XElement("name", x.Key),
new XElement("ProductSubGroups",
x.GroupBy(y => y.ProductSubGroup).Select(y => new XElement("ProductSubGroup",
new XElement("name", y.Key),
new XElement("Product", y.Select(z => new XElement("number", z.ProductNumber)))))))));
xml.Save("test.xml");
Sunday, August 28, 2011
Orchard the open source CMS from Microsoft (http://www.orchardproject.net/) has a great tempting engine. The out of the box base template “The Theme Machine” is very powerful and flexible, however if you are as design challenged as I, creating something with it that looks decent is out of the question. To that end I use a very cool template builder, Artisteer (http://www.artisteer.com/) if that is not your speed there are about a million websites where you can download free/low cost templates, however most of them generate a standard HTML page with some javascript and css. I wanted to take a minute to document how I converted my HTML template into an Orchard theme.
(Disclaimer this worked for my site, your template might require some different html changes, especially when it comes time to edit the HTML and CSS)
Step 1: Get the html template. (Did I really need to tell you that?)
Step 2: The goal is to make a theme based on the base template “The Theme Machine”. You will need to follow the instructions on the Orchard site to “Generate a New Theme” http://www.orchardproject.net/docs/Writing-a-new-theme.ashx, however their instructions have you creating an empty theme. We need to create one based on “The Theme Machine”. To do that just alter the codegen step to use the following command:
>> codegen theme MyTheme /BasedOn:TheThemeMachine /CreateProject:true /IncludeInSolution:true
Step 3: Copy the CSS & related images into the “Styles” folder of your new theme
Step 4: Copy the javascript into the “Scripts” folder of your new theme
Step 5: Copy the template html file into the Views folder. I do this so that I have it for safe keeping and it stays with the project. Orchard will not be using it directly.
Step 6: Copy the “Layout.cshtml” file from the “Views” folder from the “TheThemeMachine” template into the “Views” folder of our new theme.
Step 7: This is the hard part, time to merge the HTML template that you found/created in Step 1 with the Layout.cshtml file you copied into your “Views” folder in Step 6.
There are 2 things that you will have to worry about here:
First you need to add references for the css and js files. My template requires 3 css files and 1 js file, so I needed to add the following at the top of the layout.cshtml file. I put mine just below the includes “Style.Include” statements that were already in the file.
My template looks like this:
Converted to the layout.cshtml file it looks like:
Now that we have the head tag filled with all its goodness, it is time to do the body part. Copy over the html from the template into your Layout.cshtml file. You want to copy the html just below the “@tag.StartElement” line of code. With the markup in place, start moving the placeholders from the Layout.cshtml file into the proper places in your new template.
Once you have moved the placeholders around, you will probably need to add a css file tweak everything just right.
Step 8: The menu. My template and my client required a drop down menu, however out of the box Orchard only supports a single level menu. Time to add a module, the “Advanced Menu” module from Piotr Szmyd to be specific. After installing this module you will now have the ability to create multi level menus. However it comes with its own look and feel. If this works for you great, if not, you will have to modify it. Since my template provides for a menu that I like I am going to use it. To do so I need to copy 2 more cshtml files into my "Views” folder. After adding the module you should see a folder for it listed in your “Modules” folder called “Szmyd.Orchard.Modules.Menu”. In there you will find a “Views” folder and in it you will see Menu.cshtml, and MenuItem.cshtml. Copy those two files into the "Views” folder of your theme. Now alter them to match your template, just like you did the layout.cshtml file.
Tuesday, August 02, 2011
I was deploying a website and was seeing the following behavior:
- site configured to use Windows Authentication & Impersonation
- everything in KB 942043 was set correctly (http://support.microsoft.com/kb/942043)
- site was working from IE on the WebServer
- site was working from FireFox on my desktop
- site was NOT working in Chrome on my desktop “Error 338 (net::ERR_INVALID_AUTH_CREDENTIALS): Unknown error.”
- site was NOT working in IE on my desktop, 401 error message after asking for UserName and Password 3 times
The fix was to force the web server to use NTLM. To do this in IIS 7 is just a setting in system.web/system.webServer/security/authentication/windowsAuthentication/providers section of the web.config:
Here is a screen print of my web.config
Tuesday, July 26, 2011
I was investigating techniques to do auditing and think this is going to fit my needs, thought I would share. . . .
Let me start by saying there are about 1000 examples similar to this on the web. However none of the examples I found meet my specific requirements.
First lets discuss the requirements:
- I need to be able to apply auditing to only some of the types in my system
- I need to be able to ignore some of the properties on a type (i.e. not audit them)
- I need something flexible enough that I can apply it to multiple DbContexts
- I needed to have audit columns on each record (CreatedBy, CreatedOn, UpdatedOn, UpdatedBy).
- I needed a log table that would record when we got new records, when a field got changed, and when a record was deleted (including who and when it was done)
IAuditable Interface. I am using this guy to allow each type to “opt-in” to the auditing mechanism.
AuditIgnoreAttribute. I am using this to “opt-out” on a field by field basis of the auditing mechanism.
So now when I create my business object, I can mark him up appropriately depending on the requirements for that type. As you can see I want to audit my customer object, but not the Bio property.
Great now for the magic, the AuditableContext. This guy inherits from a normal DbContext, and overrides the SaveChanges method to add the auditing implementation. The auditing implementation is done in 5 steps:
- Figure out what the user changed and hang on to those changes for later
- Update the CreatedBy, CreatedOn, UpdatedOn, UpdatedBy columns as appropriate
- Perform a SaveChanges to write the users changes to the DB
- Update our list of user changes with the proper id’s (we need to do this because the data base is assigning the primary keys for our new entities)
- Save the list of user changes to the log table
That code should be straight forward. I did implement a helper method that figures out what fields we are interested by using a bit of reflection. NOTE: we are doing the reflection once per type and caching the results in a dictionary.
We only need to implement the AuditableContext once, each time we create a context in our app, we just inherit from it and we are off and to the races!
You can download a full working example from here
Happy Coding!
Saturday, July 23, 2011
I had a great time at the event today and wanted to thank a few folks that helped make this event:
What is next? How about the Kinect? http://research.microsoft.com/en-us/um/redmond/projects/kinectsdk/
Friday, July 22, 2011
Last night at the Dallas .NET UG (http://www.ddnug.net/) Corey Smith (http://addressof.com) demoed a between extension method in VB.NET. I threw together this version for C# for your enjoyment 
All it is doing is wrapping the built in Where LINQ method . . .
As you can see that it will work on any type that implements IComparable (http://msdn.microsoft.com/en-us/library/system.icomparable.aspx). IComparable is used in .NET for ordering and sorting, just what we need!
Here is how you would call him:
As you can see there is an optional parameter at the end, so you can select if you want an inclusive or exclusive between (i.e. inclusive says we should return 10 & 20 in the results and exclusive says we should not). I defaulted it to true since that is the behavior of SQL Server (http://msdn.microsoft.com/en-us/library/ms187922.aspx)
Happy coding!
Friday, July 15, 2011
Many times you want to seed data into your DB but don’t want to put all that seed data into your code files, or perhaps you want to add stored procedures, triggers or make other database changes after code first creates your database. While I would argue that the latter impacts one of the “goals” of EF Code First (i.e. centralizing your business logic in one spot), I can see where it might have value. So lets see how we can achieve this mission.
Lets start by creating our data objects. I am going to create a simple object “Tweet”:
Great now Lets wire it up to our DbContext:
You will see that my “SetInitializer” creates a special ContextInitializer. I borrowed this idea from Julie Lerman’s blog post on seeding data (http://thedatafarm.com/blog/data-access/seeding-a-database-with-ef4-code-first/). Lets take a look at the code in that guy:
As you can see we inherit from the “CreateDatabaseIfNotExists<T>” object. This means that EF Code First will only touch database schema if the database is totally dropped. Preventing any unintended accidental database modifications. In the seed method you see that I query the file system for all files that end in .sql, sort them alphabetically (so we can control the order that they are applied to our new database). Then one by one execute them against the context. Great now I can write seed files, stored procedures, triggers or whatever else I want in standard .sql files, and Code First will take care of applying them when it creates the database.
Here is an example of some .sql files for our little Twitter clone:
Nothing all that interesting, except for the naming convention. By placing the numbers in front I can control the order that they get executed against the database.
One last note, I think using EF Code First to “generate” your database for dev/test/qa environments is a great idea. This allows all development and testing to work against a “known”, “common” database state that is easily re-creatable. I am not quite sold on using it to generate the db in production. IMHO when it comes time to migrate to production you can use one of the many database schema diff tools (VS Data Dude, Red Gate, etc.) and prepare delta scripts to “Alter” the production DB to match the test/qa server, and tightly control what gets moved to production and when.
Monday, July 04, 2011
Stumbled across this during a discussion of RIA services today. . . .
Apparently in VB you can have the “partial” modifier on only 1/2 of a partial class. However in C# you are required to put the partial modifier on both halves of the class. I will let you guys argue about what way is better. . . . I think that both have merits. . .
Wednesday, June 29, 2011
Playing with the WCF REST Template today (http://visualstudiogallery.msdn.microsoft.com/fbc7e5c1-a0d2-41bd-9d7b-e54c845394cd). Good Stuff.
When you create a new project with the template it gives you a service called “Service1” we are going to use this guy with 1 minor change. In the “Create” method set it to echo back the object that was passed to it.
[WebInvoke(UriTemplate = "", Method = "POST")]
public SampleItem Create(SampleItem instance)
{
return instance;
}
Ok now lets get to the meat of it and write the client
WebClient client = new WebClient();
string uri = "http://localhost.:9309/Service1/";
string data = "<SampleItem xmlns=\"http://schemas.datacontract.org/2004/07/WcfRestService2\"><Id>2147483647</Id><StringValue>String content</StringValue></SampleItem>";
client.Headers.Add("Content-Type", "application/xml");
var response = client.UploadString(uri, "POST", data);
Console.WriteLine(response);
Console.ReadKey();
I got the contents of the data string by looking at the “help” provide by the service. You can see this if you append “help” to your services URL. In my case that was http://localhost.:9309/Service1/help”. Don’t forget to add the appropriate content type. Finally upload the string and capture the response. . . that is it!
BONUS: if you want to create the XML request without the “uber-string” try this on for size. . . .
//create the xml request document
XNamespace ns = "http://schemas.datacontract.org/2004/07/WcfRestService2";
XDocument doc = new XDocument( new XElement(ns + "SampleItem", new XElement(ns + "Id", "2147483647"), new XElement(ns + "StringValue", "String content") ));