What SQL ORM may i use to replace this old code

Question

Sorry since this question is specific to my problem.

While learning reflections i did a mini SQL ORM in a week then minor tweaks while using it for another week. Since it has very little work put into it, its really only compatibility with sqlite. I havent had problems with the code so far but i would like to port it to something that supports TSQL or MySql.

The example code is here which is outdated but has the most used functions in my class. What library can i port that code over too with the smallest about of pain. Note that it must support foreign keys.

Answer 1

My suggestion is to check out the tutorials of a few ORMs, and then go with whichever works best for you.

The NHibernate reference documentation suggests 30mins to get the example in the introduction chapter up and running.

My own ORM short list would be:

• NHibernate
• Linq 2 SQL

Some related questions:

• What are your favorite .NET Object Relational Mappers (ORM)?
• Which .NET Object Relational Mapper is fastest?
• Best Performing ORM for .NET
• What are the differences and pros and cons of these ORM tools/technologies?

NHibernate Example

// create and save an entity
var u = new User{ Name="foo", SignupDate=DateTime.Now };
session.Save( u );

// fetch and update entity, saving a related entity
var post = session.Get<Post>( 42 );
post.Tags["foo"].Count++;
session.Save( post );

// save a related item for an entity
post.Tags.Add( new Tag("Name")); 
session.Save( post );

Some example mappings and classes:

public class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual DateTime SignupDate { get; set; }
}

public class Post
{
    public virtual int Id { get; set; }
    public virtual User User { get; set; }
    public virtual string Title { get; set; }
    public virtual string Body { get; set; }
    public virtual IList<Tag> Tags { get; set; }
}

public class Tag
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Count { get; set; }
}

<class name="User" table="user">
  <id name="Id">
    <generator class="native" />
  </id>
  <property name="Name" />
  <property name="SignupDate" />
</class>

<class name="Post" table="post">
  <id name="Id">
    <generator class="native" />
  </id>
  <property name="Title" />
  <property name="Body" type="StringCLob" /> <!-- ntext -->
  <many-to-one name="User" />
  <bag name="Tags" table="post_tag" cascade="save-update">
      <key column="postid" />
      <many-to-many class="Tag" column="tagid" />
  </bag>
</class>

<class name="Tag" table="tag">
  <id name="Id">
    <generator class="native" />
  </id>
  <property name="Name" />
  <property name="Count" />
</class>

Answer 2

Mindscape Lightspeed has most of the common stuff from NHibernate and EntityFramework and can work from POCO

Supports SQL Server, MySQL and lots of others

Plenty of samples, tutorials, and fast friendly, and personal help

Microsoft even employed one of it's designers to work on EntityFramework

Try then out

Answer 3

For such a simple data model I would look at using either Linq 2 Sql or SubSonic, with a lean towards SubSonic.

I have used Lightspeed 2, but not the newly release version 3. So Lightspeed 3 could also be a good choice. For our Lightspeed project we also looked at six or seven other ORMs, including nHibernate. Lightspeed was the second fastest. The fastest ORM generated some really funky models!

Any one of the ORMs mentioned plus those not mentioned will all do the job. I think it will come down to how much effort you want to take to model your objects and what your personal preferences are.

I prefer SubSonic because all you have to do is set three variables in a T4 template and drop into a folder. All the code gets generated automatically. Also if needed you can tweek the templates to your liking.