Generating C# Classes from Excel

A while back I was tasked with converting and migrating data from an existing accounting system to a new one, (un)fortunately this was not the first time I was asked to do a data conversion. A year prior I had to migrate data from a 20 year old legacy system to a new software solution.

While the systems were completely different I was able to reuse a lot of the patterns and helper libraries which I had used before. During the first data conversion I felt a large portion of my time was wasted creating classes for the corresponding excel input and output files. I thought if I could generate classes with properties corresponding to the excel columns and their types, that it might save some unnecessary typing to create those classes.

So i created ExcelToClasses, a NuGet package which will open an .xls file and convert each worksheet to a class and each column to a property within that class.

The NuGet package can be found here:

https://www.nuget.org/packages/ExcelToClasses/

The package will add some T4 templates to your project, all that’s needed is to open the DataConversion.tt file and supply one or many paths to the excel file(s) and save the DataConversion.tt file.

A .cs file will be generated per worksheet, each property will have an attribute with the name and position of the corresponding excel column. This will be very useful if you are generating classes for the output schema (the format of the new system) and you wish to rename the properties or change their position in the class without affecting the output file to be generated.

Github:

https://github.com/OmarElabd/ExcelToClasses

Enjoy

ASP.NET MVC 4 using the MySQL Simple Membership Provider (errno: 150)

I’ve been using the fairly new Simple Membership Provider (that ships with versions of the MySQL Connector/Net 6.9.0 and greater) for use with a new ASP.NET MVC 4 project.  However when following the tutorial for getting up and running from the MySql site ( https://dev.mysql.com/doc/connector-net/en/connector-net-simple-membership-tutorial-creating.html ). I ran into an issue when generating the webpages_* tables.

The specific error i received is: Can’t create table ‘.webpages_membership’ (errno: 150)

MySql Foreign Key Error

I have tried this multiple times using different configurations of visual studio and MySql server and the issue only seems to be with the MySql server version being too low, however the requirements never mention a specific minimum MySql server version number. Requirements can be found here ( https://dev.mysql.com/doc/connector-net/en/connector-net-simple-membership-tutorial-requirements.html )

However if you are using an older version of MySql server you can run the following script to generate the tables:

CREATE TABLE `webpages_membership` (
  `UserId` int(11) NOT NULL,
  `CreateDate` datetime DEFAULT NULL,
  `ConfirmationToken` varchar(128) DEFAULT NULL,
  `IsConfirmed` tinyint(1) DEFAULT '0',
  `LastPasswordFailureDate` datetime DEFAULT NULL,
  `PasswordFailuresSinceLastSuccess` int(11) NOT NULL DEFAULT '0',
  `Password` varchar(128) NOT NULL,
  `PasswordChangedDate` datetime DEFAULT NULL,
  `PasswordSalt` varchar(128) NOT NULL,
  `PasswordVerificationToken` varchar(128) DEFAULT NULL,
  `PasswordVerificationTokenExpirationDate` datetime DEFAULT NULL,
  PRIMARY KEY (`UserId`),
  CONSTRAINT `fk_User_Membership` FOREIGN KEY (`UserId`) REFERENCES `userprofile` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `webpages_oauthmembership` (
  `Provider` varchar(30) NOT NULL,
  `ProviderUserId` varchar(100) NOT NULL,
  `UserId` int(11) NOT NULL,
  PRIMARY KEY (`Provider`,`ProviderUserId`),
  KEY `fk_UserId_OAuth_idx` (`UserId`),
  CONSTRAINT `fk_UserId_OAuth` FOREIGN KEY (`UserId`) REFERENCES `userprofile` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `webpages_roles` (
  `RoleId` int(11) NOT NULL AUTO_INCREMENT,
  `RoleName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`RoleId`),
  UNIQUE KEY `RoleName_UNIQUE` (`RoleName`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `webpages_usersinroles` (
  `UserId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `fk_Role_idx` (`RoleId`),
  CONSTRAINT `fk_User` FOREIGN KEY (`UserId`) REFERENCES `userprofile` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Role` FOREIGN KEY (`RoleId`) REFERENCES `webpages_roles` (`RoleId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `userprofile` (
  `UserId` int(11) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(250) NOT NULL,
  PRIMARY KEY (`UserId`),
  UNIQUE KEY `UserName` (`UserName`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

 

Hope that helps.