Matt Penner

In every DBA’s career I think having to concatenate results happens at least a few times.  Probably more than we like to admit because we tend to live in table-land.    However, there are those occasions, which are usually driven by some downstream requirement to format output.  Now, I know that formatting should be handled by whatever data viewing method you are using, but sometimes that just isn’t possible or practical.  Other times it may just be that we need to transform data from one system to another and that other system is not as normalized as the tables you are working with.

Like I said, I do it fairly infrequently, so I never remember the best way in my head.  I usually end up looking at how I’ve done it in the past.  I started thinking that there may be better ways then some of the convoluted strategies I’ve found in previous solutions.

Trusty Google sent me here:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

It’s an incredible (though certainly not exhaustive) list of ways to deal with this depending on your need.  I like XML and chose to go with simplicity so, for this particular task, I went with Eugene Kogan’s “blackbox XML” method.  It’s only a few lines and if you are familiar with XML and SQL then it’s not that hard to understand.

I’ve definitely bookmarked this for later reference!

I am upgrading an older web app of ours as I referenced in my last blog post.  This was originally a straight html app with no dynamic content at all.  I created a .Net ASPX web app out of it and used LINQ to quickly and easily create a survey form that our users could fill out.  It worked great on my machine.

Unfortunately the happy ending got derailed when I deployed it to our web server.  Our web server is an ancient Windows 2000 server box with IIS 5.  this is because it’s where all our main apps our housed, everything works and there is great fear in changing it.  <sigh>

So, I either had to figure out how to get my .Net 3.5 app running on IIS5 with .Net 2 or I had to abandon LINQ and go back to data readers (yuck!).  I first tried to install .Net 3.5 on the web server but quickly found out that it requires Windows XP or Server 2003 as a minimum.  OK, so that’s ruled out.

I knew that the asp.net framework has always been 2.0 (until the new release of 4.0 that is) and .Net 3.0 and 3.5 just added extra features on top of 2.0 but never changed the underlying base classes.  So you can easily use .Net 3.5 apps on a .Net 2.0 web server.  In fact, this has caused a lot of confusion because there simply is no 3.0 or 3.5 selection in IIS for the .Net framework.

I knew if I could just reference the required .Net 3.5 dlls then this shoudl work.  Doing a quick search on Google lead me to this great article.  I was wondering if something like this was possible and, sure enough, it pointed me in the right direction.

Here is what I did and it worked like a charm.

I first set my build target for the web app in Visual Studio 2008 to .Net 2.0.  This caused VS 2008 to instantly remove any non-.Net 3.5 compatible references such as LINQ.  I did a build and received numerous errors, most pertaining to my code that made use of LINQ.

I copied the System.core and Linq.Data DLLs into my web app’s bin folder and referenced them.  After another attempt to build the solution the LINQ errors went away but it still didn’t understand my lambda expressions or my auto-properties.  This makes perfect sense.  These are compiler features and not referenced code.  Since, by default, asp.net compiles on the server it had better understand these.  I could change the autoproperties back to normal properties but there is no lambda equivalent for .Net 2.0.

So, I created a new project and moved all LINQ code into it and had it target .Net 3.5.  Having my data access classes in a separate project felt much cleaner and probably would having been an eventual refactoring later.  I removed this code from the web app and created a reference to the new project.

Ran a build and received the welcome success message.

I then deployed the web app to the web server.  Upon opening one of the new pages, which runs a LINQ query to obtain some data to populate a drop down list, I received the following error:
Could not load type ‘System.ComponentModel.INotifyPropertyChanging’ from assembly ‘System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

After some googling it turns out that INotifyPropertyChanging wasn’t introduced until .Net 2.0 SP1.  Sure enough, our web server had 2.0 but no service packs.

I installed .Net 2.0 SP2 and everything worked great!

I am in the process of redesigning our entire department’s website and that is all built on MVC and several other current technologies.  I have another web server that is running 2003 for that.  I might miss out on some of the newer IIS 7 features but .Net 4 runs on it just fine so at least this is a major step forward.

Take care!

I’m adding some new features to an older ASP.Net Web Forms app we have.  Fortunately I can take advantage of .Net 3.5, though not MVC because it’s on a Windows 2000 server with IIS 5.  Anyway, this allows me to use LINQ, which has been incredible.  Creating the mapping and using LINQ queries is leaps and bounds more convenient and less prone to error than our older data readers and such we used in the past.

I am more in the TDD camp and like to manage my own data bindings so I don’t like to use the LINQ model designer.  Instead of dragging and dropping tables onto the designer and letting LINQ create the classes for me in the background I create my own concrete classes and then put attributes on the classes themselves.  I know, some may not like putting attributes in their code because they argue that it makes their objects dependent on the database framework rather than being completely database agnostic.  However, 100% of our apps make use of Microsoft SQL and I don’t see that ever changing in the near future.  So, rather than go through the overhead and complexity of a truly database agnostic ORM setup I’ll happily add a few attributes to my code that plainly show exactly what’s going on.

In this particular case we have parents and students that will be filling out a survey based on when they graduated from our programs.  Because we serve multiple districts, each with multiple schools, I ask the user to select which district and school they graduated from.  Thus, I have a SchoolDistrict class which contains a List of School classes.  In LINQ you can map a relationship using the Association attribute.  I got the details and examples from Microsoft’s MSDN article here: http://msdn.microsoft.com/en-us/library/bb386950.aspx

Here was my initial code for SchoolDistrict:

Here was my initial code for School:

Everything worked great and I was rolling.  Then I had ReSharper clean up a few things and I took some of its suggestions on optimizing my code.  I made a few other changes and, then all of a sudden, I got this when I ran the code:

System.Security.VerificationException: Operation could destabilize the runtime.

Ugh!  What’s that???!!

After a lot of Googling I had come up with nothing.  Most of the fixes I saw related to mapping IEnumerables to IQueryables, covariance vs contravariance, etc.  Nothing seemed to fit my particular scenario and none of the fixes worked.  I went over my code with a fine tooth comb making sure my mappings were still correct, making sure the capitalization in my attributes were not fouling things up, etc.  I came up with nothing.

So, I went back to square one.  I looked at the Microsoft example again and I found one modifier that was different.  ReSharper had noted that I could mark my Schools EntitySet in my SchoolDistrict class as readonly.  I removed this modifier and, voila, everything worked.  Sure enough, ReSharper again started suggesting that I mark this as readonly.  I did and my code broke again.  I had found the culprit.

I had not seen this mentioned anywhere on Google and I guess, in hindsight, most people wouldn’t mark this as readonly. 

I don’t always take ReSharper’s suggestions but this is the first time that ReSharper actually broke my code.    Truth be told, ReSharper is a tool and it’s only as good as the one wielding it.  If I let ReSharper perform invalid operations that’s not ReSharper’s fault but my own.  However, I still didn’t like that I may not remember this key piece of information in the future.

So, I told ReSharper to ignore this in the future and wrote a comment as to why.  That cluttered up my code and it now looks like this:

Wow, that is ugly!  I hate having to put comments in my code that are because of dependencies to frameworks.  Now I have a comment in my code that’s dependent on an IDE tool, not even something required for the app itself!!!  That’s just plain ugly and a major code smell if you ask me.  However, that’s just the way it is for now.  The app works and I’ll go on with my life.  It’s not perfect but I don’t want to spend a day figuring out a better way.  If someone posts a better suggestion or I find something in the future then I’ll revamp it, but for now, I’ll get the app out the door and life will go on.

I hope that this helps someone else out there that may run into this issue.

In the end ReSharper is one of the best tools I have in my toolbox, number 2 right behind Visual Studio itself.  It’s incredible and I can’t imagine coding without it.  It truly has helped make me a better developer than any other tool I’ve used to date (again, after VS).

Take care all and happy developing!

There are a lot of examples out there on how to pass a SelectList or MultiSelectList object to an Html.DropDownList or Html.ListBox helper.  However, I couldn’t find anything that was explaining the problem I was having.

In my app we have a Show object that represents a podcast.  Each show may have multiple Guest objects, which are accessed by the Show.Guests member.

In the Admin controller I have the following Action method:

In the Edit View I display the fields for the Show and a ListBox for the Guests.  You can select multiple Guests for a show.  Ideally, when you edit a Show the Guests that are already associated with the Show are pre-selected.  Makes sense, right?

Here is the code called in the View:

If you follow this you’ll notice that I am adding to the ViewData a MultiSelectList object, which contains an IEnumerable collection of all the Guests, that “Id” is the value field, “Name” is the text field, and an IEnumerable collection of the guest.Id values that are already associated with the show.  However, this never caused the ListBox to select the values upon rendering the page.

I used the debugger and, sure enough, my MultiSelectList object was successfully populated with the properly selected items.  What was the deal?  The breakdown must be happening with the Html.ListBox helper.  I spent a couple of hours scouring this myself and scouring the Internet.  I went home determined to re-write the Html.ListBox helper myself and just be done with it. 

However, the next morning I decided to download the MVC source.  Here’s a great and simple article from Steve Sanderson on how to attach it to your project so that you can step through it with the Visual Studio debugger.

One nice thing about Html.ListBox and Html.DropDownList helpers is that you don’t have to provide a collection of SelectItems explicitly.  If you have an item in the Model or ViewData that has the same name as the ListBox or DropDownList then it will find it automatically.  If you do explicitly provide the SelectItem collection you can opt to not provide the default values explicitly.  Again, as long as you have a object (or member of an object) that has the same name as the ListBox or DropDownList it will find it and use it.

Here’s the rub though.  If you explicitly define the SelectList collection and the default values (as I did in my code) the Html helper looks to the Model and ViewData first before your default items.  This normally never causes a problem, until you name your ListBox or DropDownBox the same name as one of your objects or members.

Notice that I called my DropDownList “Guests”? Notice how the Model is a Show object, which has a Guests member variable?  It turns out that the Html helper was grabbing this collection, rather than my values.  Technically these are the guests I want to default to, however, the Html helper was getting a collection of Guest objects not a collection of Guest Id values.  When it compared each guest Id in the full list with the actual Guest objects in the default list it never found a match.  Thus, nothing was ever selected.

What’s the fix?  Simply change the name of your ListBox or DropDownList.  When I changed the name value to “GuestListBox” all worked well, because, as you would expect there is no object or member in the Model or ViewData called “GuestListBox”.  Once it couldn’t find one it dropped through that code and used the default values I provided.

To find exactly where MVC is doing this check out the SelectInternal member in the SelectExtensions.cs file.  This is the internal method that both ListBox and DropDownList eventually call.  You can see commented code that states:

In my opinion this is a bug.  It shouldn’t even check the ViewData or Model if you have already explicitely provided the default values.

So, I added my vote to the bug that was already submitted for this on CodePlex.  I haven’t check to see if this has been resolved in MVC v2.

Ever since virtual PC type systems have been out, long has been the dream to run any type of operating system you want, multiple operating systems at the same time, all working independently of each other.

However, only in the last year or two has this really become a reality with the latest hardware supporting virtual environments natively.  Now Windows 7 lets you boot off of a VHD as if it was a standard hard drive making full use of your actual hardware devices.

After attending Stephen Rose’s presentation on Virtualization 101 for Developers my eyes were suddenly open to the possibilities of what could be done.  I could move my entire development environment to a virtual PC image.  This would allow me to copy that image at any time allowing me to test beta products, new upgrades, etc without worrying about damaging my current environment.  Many times I have upgraded to new releases (or betas) that caused previous programs to stop running, Dlls required in legacy projects to be removed, etc.  In some rare cases (SQL Server 2008 RC2) I had to reload my entire environment because I could never get my machine back to its original state.

Now that Windows 7 allows you to boot from a VHD my wheels are suddenly turning.  I could move my entire computer use (not just development) to VHDs.  This would allow me to have a base image for general tasks like email, MS Office, etc that my wife and I could use for general day to day tasks.  I could have another environment for all my video editing work, which is especially useful since video compression codecs can sometimes cause havoc.  I could have a production development environment and as many beta environments as I want.  If I want to download a 30 day trial of any product I just create a copy of the appropriate VHD, boot off of that and try out the product.  After 30 days if I don’t like it I just delete the testing VHD and it will have never touched my production VHD.  If I do like the product I just delete the testing VHD, purchase the full copy and install it to my production VHD.  Very nice and clean.

I just purchased a new HP dv7 laptop with the Intel core i7.  So, this is the perfect time to get started.  While it is possible to wipe the drive clean and use VHDs without any core operating system at all I’ll leave the HP environment intact.  That way I can always fall back to that just in case.  If for some reason my sound stops working I doubt HP will be willing to care if I can’t reproduce it in the standard OS.

So, I’ll document how I set this up and how it works out in future blog posts.  Enjoy the ride!

I’m going to be doing a talk on Practical TDD at the Inland Empire .Net Users’ Group in the near future.  We’ve had TDD talks in the past but they have been more overviews with only a little code.  I plan to take the group through craeting a simple blog engine from scratch completely using TDD.  I hope that this will give a real great example of how to develop a project using TDD.

The presentation was supposed to be in August, but as that lands directly on my 8 year wedding anniversary and our son’s 3rd birthday I had to postpone it.  I don’t like sleeping on the couch.   James Johnson, the president, is still working on where to put my talk, so I’ll post an update when that happens.

Anyway, in the spirit of getting ready for this talk I’ll be writing a few blog posts on some great TDD resources I’ve found.

Recently Roy Osherove, the author of the Art of Unit Testing, started doing actual TDD code reviews on his blog.  These are great!  It’s one thing to read a blogger’s posts with their thoughts and opinions on topics but seeing Roy go through a live code review in a video is just spectacular.  It’s like getting a chance to sit in on a session with Roy.

While he admits that he’s a little harsh on the Nerd Dinner review, having done it at 2am, I think he is spot on in his findings.  Of course, Roy is a very experienced veteran in this area so we’d think nothnig less.  However, the points Roy makes are great and he really brings to light what good and bad examlpes of TDD he sees in the app.

I can’t wait to check out the rest of the videos.

He offers to review code that is sent in.  I’m not sure I’ll be brave enough to do that, but if I’m not that would indicate a smell in my code, wouldn’t it? Maybe when I get my talk together I’ll pass my code on to Roy to review prior to my presentation.  That would really give me a great resource to make sure my presentation is sound and a little more authority on this topic.

Here’s a great article on how to get a geographic location from an IP address within SQL Server:
http://www.sqlservercentral.com/articles/SQL+Server/67215/

The article is very easy to follow and gives great direction of setting up a user defined function in SQL to give you back a location based on the IP address.  Since SQL servers are very good at processing data quickly this seems like a natural way to get this information.  Once you have the function set up you can easily use it in asynchronous processes like analyzing logs, post processing of customer data, etc.  You can also set this up as a trigger within SQL or a service callable by outside apps, such as a webapp.

I’ve seen this used in a lot of ways that I don’t care for (thanks for letting me know about all the fictional hot girls that live in my area, but I don’t think my wife would approve ) but there are some legitimate ideas coming around.  For instance, let me power up my iPhone and see what Nerd Dinners are available in my area (work in progress).

Another scenario is blocking spam.  For instance, at my work we service Riverside County in southern California, USA.  We have methods to stop unauthorized users from creating accounts and blocking spam to our Wiki’s and such.  But why not use location based blocks as well?  I know my users are all from Riverside County, so why not block everyone from, say, outside of southern California?  While a user or two may be blocked while attempting to access their work from their vacation in Maui, I don’t think I’d get that much flack from blocking these edge cases.

On an old Web Forms app we had the request to allow users to download the data from a formatted SSRS report as an Excel spreadsheet.  They currently view the formatted report using the ReportViewerControl, however, when you use the export feature it exports it to Excel with all the formatting of the report, including groups.  This is fairly unusable.  What you really need is a simple spreadsheet with a cell for every value without any grouping.

So, I created a report that had no grouping or formatting and a column for every field of data. 

I could have forced the user to view this ugly report in the ReportViewer and then export it as an Excel spreadsheet but I wanted them to be able to click on a link and get the report directly.  That’s easy enough because you can get an SSRS report formatted as Excel by appending “rs:Format=Excel” to the end of the report url.  This doesn’t work for us, however, for two reasons:

1) The website is accessible to users outside our network and the SSRS server is not.

2) The report retrieves sensitive data filtered by a parameter.  It would be fairly easy for a user to change the parameters in the url to obtain data they shouldn’t be viewing.

In the ReportViewerControl we change the parameters on the server side so the user simply views the generated report with no option to change the parameters.  Now, I needed a way to let them download the Excel version with the same restrictions.

Below is the solution I used with the help of several different other sites on the web.

I created a hanlder that would create the url with the proper parameters and formatting, retrieve the report and then send it out to the user as an Excel document. This way when the user clicked the link for Excel version of the report their browser would open a dialogue to Open or Save the Excel file.  Works like a charm.

Here is the code:

The first few lines simply clear any headers or content that may be initially set.  This is mandatory when sending files because we are going to be setting the headers later.

Line 7 is the url of our report.  Notice that “rs:Format=Excel” is in the url letting SSRS know we want this report as an Excel doc.  Also notice that I set the DistrictName parameter by getting the logged in user’s district name via a utility method.

Line 8 sets up an HttpWebRequest object so that we can make a call to the SSRS server just as you would with a browser. 

Since the SSRS server is on our domain and user restrictions are in place Line 9 sets the credentials we need. Our webapp impersonates a user with the correct access so that we can have the proper security restrictions in place yet allow non-domain users access to the data.  This impersonation is set up in the web.config and DefaultNetworkCredentials looks their first to get the credential information.

Lines 12 & 13 simply grab the web response and sets up a Stream object to read it with.

Lines 15 – 20 sets up a temporary MemoryStream object which holds the bytes read from the response (remember SSRS is sending us an Excel file, which is a binary file, not plain text).

Lines 22 – 28 finally sets up the headers and content type, sends the bytes and closes the stream.

I probably didn’t need a MemoryStream object.  In the loop I probably could have written the bytes directly to the context.Response object.  But this seems a little easier to read and potentially debug if necessary.

In the end I’d rather use MVC.  In fact I created the exact same result (except as a CSV file) using MVC and LINQ.  It was a snap and I had it done in 5 minutes.  Unfortunately the current site is on a Windows 2000 server and makes heavy use of Web Forms and the SSRS viewer controls.  I know I could have simply had the link request the result from an MVC app on another server but I wanted to keep this all fairly consistent and coherent.

Look for me to change this all up next year when I’ve moved our department webpage entirely over to MVC.

Well, soon I’ll be off to MIX09!

I can’t wait to go, but already we’re off to a rocky start.  The first Keynote starts at 9am with registration and the breakfast anytime before that.  That’s not bad but I probably won’t even show up in Vegas until 2am that morning to say nothing of checking in and finally falling asleep.  This is because we have our Inland Empire .Net User Group the night before.  Tom Opgenorth is presenting on ASP.Net MVC.  He is a great speaker and this is a great topic.  Tom is the guy who got me back into TDD and, after playing with MVC for about 6 months, I’m excited to ask him some detailed questions.  If you missed his presentation on TDD you’re in luck.  We recorded it and you can view the entire session on the user group website in the Videos section.  So anyway, the meeting will end about 9:30, so by the time I pack up, hit Starbucks and head out I’ll probably be pulling into Vegas at ~2am.  Rough day when you want to be conscious during the keynote.

Anyway, before all that we had a great plan.  About 8 of us were going to leave the meeting together and caravan to Vegas.  Now, through various schedule changes and some bosses reneging on their approval we’re down to 5 in the caravan.  No big but it kind of lets the steam out when your “party group” gets cut in half.  But, those of us going are still jazzed and ready to hit the town.

To add to the misery my kids have been pretty sick.  It’s the saddest thing to see your two year old just absolutely miserable with a cold.   Anyway, he has graciously let me participate in that I’m starting to come down with it.  I hope I am not full blown sick in Vegas.

OK, on to the real topic (about time!):

So, as I am architecting a site for my new department in MVC I am hitting most of the MVC talks.  Silverlight 3 is probably one of the main focuses of the event along with Windows Azure.  However, I have no actual business use for Silverlight 3 yet (mainly due to the technical restrictions hampering Silverlight’s adoption for my clients) so that’s just eye candy that I don’t have time for.  Also, most of the Azure talks are fairly elementary, rehashing the same “What is Azure” topics.  I’ve seen most of this stuff and, again, have no real practical use at my paying job for it so I’ll skip most of these as well.  I am checking out the Azure Storage because I’m interested in queues, however, since I’m leaning towards SDS I could easily find myself in another session. 

That leaves me with MVC.  Prior to my move to a new department I have been working with MVC for the last 6 months on a particular project, which coincidentally was for my new department.  Now that I’m in my new position this task has grown.  The entire department site will be hosted in MVC with several sub-apps based on various technologies, including MVC.  So, naturally I’m hitting most of these.

I’m the dev guy who puts a high emphasis on “usability” but wish could design better.  So, for Friday afternoon I’m not sure whether to go for the “Advance Your Design with UX Design Patterns" session (quince looks like a great tool!) or the mini sessions on Infographics and Microformats.  I don’t care about the “UI discussion” much so I’ll probably hit the UX Design Patterns session at 12:30 and then skip out to see 12:55, or I may just stay for the whole thing.  Whatever I miss I can catch later from the online recordings.

Here’s my schedule for those of you who happen to be interested:

Day 1 – Wednesday

Time	Activity
9:00 AM	Keynote – Bill Buxton & Scott Guthrie
11:30 AM	RESTful Services for the Programmable Web with WCF – Ron Jacobs
12:45 PM	Lunch
2:15 PM	??? – Not sure what I want to go to yet.  Have an idea?
4:00 PM	How’d they do it? Real App. Real Code. Two Weeks. Nothing but .NET – Scott Hanselman
6:00 PM	Attendee Party at TAO Las Vegas

Day 2 – Thursday

Day 3 – Friday

Well, after a very long labor of love videos of the Inland Empire .Net User Group sessions are now available.

Check out the first two at http://www.iedotnetug.org/UG/videos.aspx.

This has been a long time coming.  There is so much great knowledge and content at our user groups that it’s a real shame that it wasn’t getting recorded.  If you missed a meeting you really missed out.  And if you wanted to brush up on a topic you attended several months ago all you had were your notes, slides or an email contact.  These are great assets but nothing like having access to the actual live presentation.

So, I approached James Johnson (the president of the group) about recording the sessions.  He loved the idea.  We got a VGAUSB2 VGA frame grabber sponsored from epiphan.  (I’m still working on proper sponsor recognition for our videos).  The frame grabber allows us to record the actual live desktop content without installing software on the presenter’s machine.

My dream is having a combination of live video, live screen capture and live audio.  It worked great for the TDD session with Tom Opgenorth.  Unfortunately it took me hours and hours cutting the footage together with nice dissolves and such.  Also my camera is pretty poor on it’s low light ability.  So, for now we’ve abandoned live video.  I think it really adds a lot so getting this back is in the works.

Now we’ve also stepped up to a wireless lapel mic for our speakers.  It’s a major improvement in sound quality, which you can hear in the two videos.  The TDD presentation is just a USB nearby mic where as the Virtual presentation is with the wireless lapel.

So, what are the next steps?  These are not in any particular order.

• The user group site is moving to Sitefinity from Telerik.  So, getting this same page available in SF is next and already 99% done.
• Create a proper intro branding video (like 10 seconds) representing the user group
• Create a proper sponsorship branding video (like 3-5 seconds) for epiphan
• Get a decent low-light HD camera for live video.
• Create a new layout template accommodating live video and screen capture so I don’t have to actually edit the result.  Much like the PDC videos.  I really like that layout and it’s minimal setup/maintenance.
• Take over the world!

That last one is jokingly but I’m actually drafting plans to setup a SoCal org that facilitates recording of presentations at local user groups and conferences and making them available for public viewing.  check out my post here for the beginning of this concept.