Converting ADO.NET DataReader to a List: Possible Base for Cross-platform Data Access

I’ve been experimenting with different .NET ORM options recently, including mapping ADO.NET DataReader rows directly to strongly typed objects. I came across a Stackoverflow post by Jon Skeet that proved really useful, so I thought I would present it here in more detail.

I have a local instance of Sql Server that contains database objects for a publisher, including a stored procedure that returns book details. I started by creating an ORM class to hold the results of the stored procedure. I’ve also added a static method (Select) that accepts an IDataReader argument and maps the reader row values to my BookDetails class.

orm_1

Click for larger view.

I then create an extension method that loops through the DataReader rows and projects the values into an object, using the Func (T, TResult) delegate.

orm_2

Click for larger view.

Finally I tie it together in my method GetBookDetails(). This uses standard ADO.NET to call the “spGetBooks” stored procedure and return the results to a SqlDataReader. I then use the Select extension method to map the SqlDataReader rows to a List<BookDetails>. Note that the FromDataReader method is passed in as an argument in the Select method.

orm_3

Click for larger view.

This approach is pretty simple. However, it can be used to drive a more legitimate DAL, in particular one that reaches out to different kinds of database servers. I have an upcoming integration project that requires accessing data from SQL Server as well as IBM Informix. It appears that Informix is compatible with Entity Framework,  but it also seems like one of those things that could be troublesome and buggy.

The IDataReader interface used in this approach will accept both SqlDataReader and OdbcDataReader (and I’m certain that Informix accepts ODBC). To that point, it might be interesting to leverage both IDataReader and the abstract class DbDataReader, as all of the different readers roll up to it. It could be the basis for a relatively painless cross-platform ORM.

As an aside, I would recommend Skeet’s C# in Depth. It’s a great read for an intermediate C# developer, and it’s proven to be a good reference when trying to write more elegant code, if you have the time for such indulgences.

Advertisements

Checking for folder permissions in C#

I just spent 3 hours writing a simple method to check if the user running my app has write access permissions on a folder. Definitely cracked a beer into the 3rd hour.

It was harder than I thought. I first found a simple, clean example on Stackoverflow and tried that:

public bool CheckFolderPermissions(string folderPath)
{
    var permissionSet = new PermissionSet(PermissionState.None);
    var writePermission = new FileIOPermission(FileIOPermissionAccess.Write, folderPath);
    permissionSet.AddPermission(writePermission);

    if (permissionSet.IsSubsetOf(AppDomain.CurrentDomain.PermissionSet))
      return true;
    else
      return false;
}

Soon after writing this, I realized I needed to test it somehow. I ended up creating a new user on my dev server with only write privileges on the target folder. I Shift-right-clicked on my executable, ran as this new user, and then attached to that process in Visual Studio. Then I dealt with a mysterious error for a while, which ended up being this new user not having access to SQL Server. Hopefully you won’t get that.

The nice little method above didn’t work. I feel like I don’t fully understand AppDomains–maybe running as a different user is not enough to shift the properties of AppDomain.CurrentDomain.

So I tried another longer method (“diesel” as we would say here in NYC):

public bool CheckFolderPermissions(string folderPath)
{
     WindowsIdentity currentUser = WindowsIdentity.GetCurrent();
     var domainAndUser = currentUser.Name;
     DirectoryInfo dirInfo = new DirectoryInfo(folderPath);
     DirectorySecurity dirAC = dirInfo.GetAccessControl(AccessControlSections.All);
     AuthorizationRuleCollection rules = dirAC.GetAccessRules(true, true, typeof(NTAccount));

     foreach (AuthorizationRule rule in rules)
     {
         if (rule.IdentityReference.Value.Equals(domainAndUser, StringComparison.CurrentCultureIgnoreCase))
         {
             if ((((FileSystemAccessRule)rule).FileSystemRights & FileSystemRights.WriteData) > 0)
             return true;
         }
      }
return false;
}

This method did a great job of determining the permissions of the folder against the current user if the user has write privileges already. If not, then dirInfo.GetAccessControl bombs, obviously, because if you can’t write to a folder, you can’t comprehensively read its privileges (most of the time).

But, this lead me to the correct solution. A simple try/catch, looking for GetAccessControl to bomb:

 public bool CheckFolderPermission(string folderPath)
 {
    DirectoryInfo dirInfo = new DirectoryInfo(folderPath);
    try
    {
         DirectorySecurity dirAC = dirInfo.GetAccessControl(AccessControlSections.All);
         return true;
    }
    catch (PrivilegeNotHeldException)
    {
        return false;
    }
 }

Creating Custom Actions with Wix (and running as Administrator)

1. To create a custom action method to call through Wix, you’ll need to add a Custom Action project to your solution. Right click on your solution, and then select “C# Custom Action Project”. I’ve named my project “MyApp.CustomActions”.

wix_1

2. When you add the project, it will automatically create a CustomActions class with a public static method decorated with the CustomAction attribute, which is exactly what you’ll need to call your method through Wix.

Below is a method that I created to encrypt a connection string in app.config. You’ll notice that I initially tried to get the installation path from the “INSTALLFOLDER” key passed to Session by Wix, but I ended up commenting that section out. This ultimately didn’t work given my requirement to run the method as Administrator.

wix_2

Click on image for larger size.

3.  Back in your Wix project, add a new wxs file. I’ve named mine “CustomAction.wxs”. Add the following Fragment node to your file. This will tell Wix which dll to look for and which method to call for your custom action.

wix_3

Click on image for larger size.

There are a few key points to mention about this Fragment node. It includes two changes that make it possible to run the method with elevated privileges. The Impersonate attribute is added and set to “no”. This will ensure that our method runs under Local System and not the user who runs the Msi. Also, the  Execute attribute is set to “commit”, which means that our custom action will run at the end of installation. This also means that you can’t access variables from Session in your custom action code, which is unfortunate. For more info on these attributes, check out the Custom Action page on Wix.

Also, notice how the SourceFile attribute is pointing to the MyApp.CustomActions.CA.dll. The “CA” is important here. Your custom actions project will produce two dlls, one normal and one appended with “CA.” You need to point to the CA one.

4. Finally, add a InstallExecuteSequence tag to your Product.wxs file.

wix_4

This will point back to the fragment you created in step #3. I’ve set the After attribute to “InstallFiles”. This means that the script will run after the files are installed on the target machine. This makes sense for my task, since I need to update the app.config file that is deployed by the Msi. I initially had this value set to “InstallFinalize”, which made the most sense to me based on the Execute=’commit’. However, Visual Studio complained about that value when I built the project, saying that it needed a stage between InstallInitialize and InstallFinalize.

Debugging

I was able to see the Msi install log (including the log messages I included in my custom action method) using this helpful command.

 

 

Changed computer/server name and TFS blew up? Here’s the fix.

  1. From Command Prompt, cd into C:\Program Files\Microsoft Team Foundation Server 12.0\Tools, and execute the following command: TFSConfig.exe RemapDBs /DatabaseName:[YourServerName];[YourDatabaseName] /SQLInstances:[YourServerName]
  2. In C:\Program Files\Microsoft Team Foundation Server 11.0\Application Tier\Web Services, update connection string with the new server name under <appSettings>
  3. From Visual Studio Command Prompt, execute the following command: tf workspaces /updateComputerName:[YourComputerName] /collection:[Your TFS Default Collection URL]

In 3 above, your TFS Default Collection URL is usually http://localhost:8080/tfs/DefaultCollection

Like the technician who fixed my washer/drier said, we would not have jobs if things didn’t break down once in a while.

Generate Wix File for Referenced DLLs

I just created a Wix installer for a Windows Application. The application uses DevExpress UI controls, which results in a ton of project references that need to be Gac’d when the installer runs. Since there is no clear way (that I could find) to loop through a directory in Wix, I was facing adding 37 Component elements by hand to my installer files. Luckily I found the Heat tool that comes with Wix.

Heat is a command line tool that, among other useful things, will “harvest” a directory for you, creating an output wxs file that can be used with your installer. Here is the command I used to harvest my app’s bin folder. I ran this from the project (csproj) directory:

"C:\Program Files (x86)\WiX Toolset v3.8\bin\heat.exe" dir ".\bin\Debug" -cg YodaRefs -ag -scom -sreg -sfrag –srd -template fragment -out yodarefs.wxs

Note that you might need to change the program files path above to correspond to your installed version of Wix.

There is a better way to add references automatically in Visual Studio. I didn’t have the time to tinker with VS, so I just copied the output from the command above into my product.wxs file.

Fall Streaming

It’s that time of year again, when you can watch five films in one weekend and have family only be mildly concerned that you’re depressed. Accordingly, I’ve collected some brief reviews of films currently streaming  on Netflix, for those minus-30-dark-at-4-kind-of days.

What Maisie Knew

What Maisie Knew still

Family dramas are not my normal cup of tea, but I saw this poster for months outside of the Angelika theater so I’d thought I would give it a whirl. This movie is about a young girl suffering through her two self-absorbed wealthy Manhattanite parents’ divorce. Her parents are the type that I see in TriBeCa coffee houses on the weekends, forcing their only-child to eat cashew butter and green juice–the type that I really hope are not the awful people that this film makes them out to be. Moore and Coogan play truly despicable parents who ignore Maisie to pursue either trans-Atlantic business pursuits (Coogan) or nostalgic rock re-unions (Moore), the latter being one of the more far-fetched and ridiculous parts of the film. Both also marry young, fairly innocent hipsters for the sole purpose of having someone to babysit their daughter. Despite being a middle-class bartender and nanny, these hipsters are (surprise!) better parents than Maisie’s real sad-sack biological parents.

I liked this film because it depicted how a childhood could be both drenched in material goods and completely devoid of stability and love, something that is probably quite common in America’s ruling class. It also offered some interesting generational commentary. Despite lacking careers, in this film Gen Yers are decent human beings and parents, while Gen Xers are materialistic, confused people who follow in the footsteps of the worst of the boomers. One of the several unrealistic parts that bothered me was that Maisie, although adorable, was not realistic in her ultra-mature, zen-like approach to her parents awfulness. Most kids would be well on their way to being little monsters.

Only God Forgives

Only God Forgives still

Staying on the topic of bad parents, we have this beautiful and gruesome crime flick, Only God Forgives. Kristin Scott Thomas has an epic performance, playing a sadistic and quasi-incestuous crime-family matriarch, Crystal. Crystal manipulates with her son (Gosling) with some deep dark Freudian stuff. All poor Gosling wants is to not kill innocent people and children, and to have a prostitute like him. Both are hunted by a demi-god Thai police chief who routinely murders and chops off limbs to impose his ethics.

This film looks amazing. It could have been Panos Cosmatos’ next film. It could have also been a misfire by David Lynch, since it is filled with dark corridors, red lighting, and hands moving through veils. It would have been a misfire, though, because despite the visuals, excellent soundtrack by Cliff Martinez, and Thomas’ performance, the plot and the acting are not that good.

Holy Motors

Holy Motors still

I’m a fan of strange experimental films, and this was probably the strangest film I’ve seen all year. Not just because the plot is totally surreal, but it’s real-deal surreal, meaning French. And sometimes French things are so weird it’s like they’re from another planet.

Denis Lavant gets to “meta-act” in this film, playing a mysterious man who is driven around Paris in a limo, to his various “appointments,” acting as a homeless beggar, a futuristic gaming green-screen sex actor à la The Lawnmower Man, and a flower-eating, sewer-dwelling, Eva Mendes breast-revealing dwarf, to name a few. The latter was so bizarre and so seemingly packed with literary and historic reference that my brain couldn’t really process it.

All that aside, I didn’t actually finish the film, since it was near 3am and suddenly Kylie Minogue appeared and it became a musical. Nevertheless, this is some crazy adventurous stuff along the lines of Enter the Void, and it’s definitely worth seeing.

Electrick Children

Electrick Children still

The plot of Electrick Children is wistful. A Mormon teenager listens to Blondie and becomes pregnant with the Child of God, then takes off to the big city to find the rock star father. It would be a mediocre or bad film if it was only the plot that was wistful. But it’s a good film because the mood is nostalgic as well.

It was hard to put my finger on exactly what I liked about this film, but whatever it was definitely had a residue of nostalgia. It clearly dealt with the repression of Mormonism, or any religious orthodoxy, for that matter. More importantly, however, it managed to portray teenage love in messy way, complete with a final escape to the ultimate symbol of freedom, the California coast. It kind of reminded of a Paris, Texas for the 2010s. Expansive, austere landscapes, missing fathers, urban decay (Las Vegas, Houston), and contrasting suburbia.

Después de Lucía

Despues_de_Lucia

A good friend, knowing that I am a fan of dark cinema, suggested that I check this one out, as it might challenge my tastes. And it did. This was one of the only films in recent memory that I initially regretted watching because it was so disturbing.

I say initially because I think Después de Lucía is a unique and important film. It deals with themes of grief and the terrifying notion that terrible things happen to decent people who have just experienced terrible things, for no apparent reason. At the expense of sounding like a misinformed white man, I feel that there is something particularly Mexican about this kind of nihilism, or at least that it is born in a place where people live amongst barbaric drug-trade murders and thousands of women disappear without a trace.

What’s interesting about this film, though, is that its terror does not reside in the slums of Ciudad Juárez but in the wealthy enclaves of Mexico City. The terrorists are not drug cartels, but rich teenagers with iPhones. Yet the same themes creep in; in particular, violence against women and inadequate law enforcement. Lucía’s father ends of up taking on the role of the enforcer, or judge, jury, and executioner. In the films unforgettable final scene, his cold-steel and mechanical murder of Lucía’s schoolmate/tormentor summarizes something terrible about the world that is best unearthed only once. So yes, the movie is not something I would watch repeatedly, but definitely worth watching.

Serializing Form Data in MVC using Json.NET

I recently worked on a project where we designed a web application to submit a form to a certain dreaded government agency. We needed to load the form data into SQL Server and retrieve it, in case the customer’s application was rejected. My co-developer suggested Json–I was initially doubtful, since SQL Server has a ton of support for XML but very little for Json.

It ended up being super easy to use Json, maybe the smoothest part of the project aside from Stripe integration. We leveraged the Json.NET library and the view model approach. Here is how we did it:

In your Models, create a view model for your form data:

namespace MyApp.ViewModels
{
public class FormViewModel
{
public string LastName {get; set;}
public string FirstName {get; set;}
public string SomeMoreFormData{get; set;}
}

}

Then create another model for your application domain data that matches your database table.

namespace MyApp.Models
{
public class ApplicationModel
{
public Guid ID {get; set;}
public Guid CustomerID {get; set;}
public string FormViewJson {get; set;}
}
}

In your form POST method, grab the ViewModel (if valid) and serialize it using Json.NET.

string _formViewJson= JsonConvert.SerializeObject(_formViewModel);

Then map it to your FormViewModel property in your ApplicationModel, and load it into the DB with the ORM of your choice (we used Dapper, which was great btw).

When the customer returns and needs to access their form data again, just grab the FormViewJson from the DB, and deserialize it into FormViewModel, like this:

_formViewModel = JsonConvert.DeserializeObject<FormViewModel>(_applicationModel.FormViewJson);

Pass _formViewModel in your “update form” GET controller method to a strongly typed view and voila! You have the customer’s previous form data rendered.