O/R Mappers for MySql and .NET

[David Dimmer]

Ok, let me start out by saying I spent all weekend trying to get an O/R mapper to work. It's not easy and there is typically little to no documentation; and if you are trying to use MySql there is even less documentation. By the end of the weekend I had MySql.Data v1.0.3 working with O/R mapper v.3.1.0.1 with the WilsonORMapper.

For the sake of getting more people involved I am going to provide some quicklinks to get us, MySql users up to speed.

=================================================

What is an O/R Mapper:

An O/R Mapper was supposed to be built in to ASP.NET 2.0 called ObjectSpaces (http://aspalliance.com/articleViewer.aspx?aId=248&pId=). Microsoft then pushed ObjectSpaces back to Longhorn (Windows XP's next release) and then lastly pushed it back further to the WinFS file system upgrade.

Here is what it was supposed to be:
ADO.NET v2.0 will finally provide a built-in object-relational mapper called ObjectSpaces. What is an O/R mapper? Its a framework that lets you totally avoid SQL and stored procs! Note that I'm not talking about generating the SQL or stored procs, like many tools do now; no, I mean totally avoid it and have the framework transparently do all the CRUD for you. That may sound like an impossible dream, if you've never looked into these types of tools, but its very doable and its been rather commonplace in the Java world for quite some time. ASP.NET, and now Avalon, are declarative GUI models -- ObjectSpaces is just declarative CRUD.

===================LINKS=========================
I worked with the Paul Wilson (very responsive if you have questions) the autho of O/R Mapper (http://weblogs.asp.net/PWilson/) and after a long weekend, successfully got it working.

Please note if you are going to try this product you will need CodeSmith (http://www.ericjsmith.net/codesmith/)

Here is a post to get MySql connections working in MySql:
(http://www.ericjsmith.net/codesmith/forum/default.aspx?f=10&m=2900&p=2)

Working Templates for O/R Mapper to use in CodeSmith
(http://www.ericjsmith.net/codesmith/forum/default.aspx?f=9&m=4357&p=1 )
=================================================

So does this exist? The answer is YES. There are 4 projects I know of: WilsonOrMapper, NHibernate, LLBLGen Pro, and EntityBroker. I have chosen the WilsonOrMapper because it appears to support the most databases and Paul Wilson is extremely responsive, something I like when I am trying a new technology.

=================================================
The code in use:

// Load Item 21
BusinessObjects.Project p = (BusinessObjects.Project) Manager.Engine.GetObject(typeof(BusinessObjects.Project),21);

// Display Title
Response.Write(p.Title);
// Display Title
Response.Write(p["Title"]);

// Fetches projectdetails (from separate table)
this.DataGrid1.DataSource = p.Projectdetail;

// Fetches all of the projects
ObjectReader OR = Manager.Engine.GetObjectReader(typeof(BusinessObjects.Project),"");
this.DataGrid2.DataSource = OR;


// Change Title and display
p.Title = "Test 3";
p["DisplayOrder"] = 0;

// Save Changes
Manager.Engine.PersistChanges(p);

//Delete ID = 15
Manager.Engine.ExecuteDelete(typeof(BusinessObjects.Detail),"id = 15");
=================================================

If that doesn't make you exicted, then take a second look. This is completely database independant code!

=================================================

How does it work:
Basically your database table gets mapped to an XML file:

[entity] is your table
[type] is your .NET class with full namespace (CodeSmith will build these for you, each table gets 1 class)
[keyMember] is your database key
[keyType] is the type of key

[attribute] column in your DB
[member] is your private variable in your entity.type
[field] is the field in your database
[alias] is the public method in your class

[relation] links to tables together so you can quickly load everything that belongs to one object. This is the "magic" its really neat!

<relation relationship="OneToMany" member="projectdetail" field="ProjectID" type="DeLeers.BusinessObjects.Projectdetail" lazyLoad="true" />

[CodeSmith can generate this file for you]
=================================================
<?xml version="1.0" encoding="utf-8" ?>
<mappings version="3.1">
<entity type="DeLeers.BusinessObjects.Project" table="project" keyMember="id" keyType="Auto">
<attribute member="id" field="ID" alias="Id" />
<attribute member="title" field="title" alias="Title" />
<attribute member="description" field="description" alias="Description" />
<attribute member="specialCare" field="SpecialCare" alias="SpecialCare" />
<attribute member="feature" field="features" alias="Feature" />
<attribute member="material" field="materials" alias="Material" />
<attribute member="quote" field="quote" alias="Quote" />
<attribute member="quoteAuthor" field="QuoteAuthor" alias="QuoteAuthor" />
<attribute member="typeID" field="ProjectTypeID" alias="TypeID" />
<attribute member="internalid" field="internalid" alias="Internalid" />
<attribute member="counter" field="counter" alias="Counter" />
<relation relationship="OneToMany" member="projectdetail" field="ProjectID" type="DeLeers.BusinessObjects.Projectdetail" lazyLoad="true" />
</entity>
<entity type="DeLeers.BusinessObjects.Projectdetail" table="projectdetails" keyMember="id" keyType="Auto">
<attribute member="id" field="ID" alias="Id" />
<attribute member="projectID" field="ProjectID" alias="ProjectID" />
<attribute member="detailID" field="DetailID" alias="DetailID" />
<attribute member="description" field="Description" alias="Description" />
</entity>
</mappings>
=================================================

You need to setup your "Engine" in your .NET project, here is the correct syntax for a MySql engine using .NET connector v.1.0.3

static Manager() {
string mappingFile = AppDomain.CurrentDomain.BaseDirectory + @"Data\Mappings.config";

string connectString = "Server=serverstring;Port=3306;Database=dbstring;Uid=uidstring;Pwd=pwdstring";

//Provider provider;
CustomProvider provider;
provider=new CustomProvider("MySql.Data", "MySql.Data.MySqlClient.MySqlConnection", "MySql.Data.MySqlClient.MySqlDataAdapter");
provider.StartDelimiter="";
provider.EndDelimiter="";
provider.LineTerminator=";";
provider.IdentityQuery= "SELECT LAST_INSERT_ID()";
provider.SelectPageQuery="SELECT * LIMIT {0} OFFSET {1}";
provider.ParameterPrefix = "?";
engine = new ObjectSpace(mappingFile, connectString, provider, 20, 5);
}


[CodeSmith can generate this file for you]
=================================================
using System;
using System.Collections;
using Wilson.ORMapper;

namespace DeLeers.BusinessObjects
{
public class Project : IObjectHelper, IObjectNotification
{
private int id;
private string title;
private string description;
private string specialCare;
private string feature;
private string material;
private string quote;
private string quoteAuthor;
private string typeID;
private string internalid;
private int counter;
private IList projectdetail;

public int Id
{
get { return this.id; }
set { this.id = value; }
}

public string Title
{
get { return this.title; }
set { this.title = value; }
}

public string Description
{
get { return this.description; }
set { this.description = value; }
}

public string SpecialCare
{
get { return this.specialCare; }
set { this.specialCare = value; }
}

public string Feature
{
get { return this.feature; }
set { this.feature = value; }
}

public string Material
{
get { return this.material; }
set { this.material = value; }
}

public string Quote
{
get { return this.quote; }
set { this.quote = value; }
}

public string QuoteAuthor
{
get { return this.quoteAuthor; }
set { this.quoteAuthor = value; }
}

public string TypeID
{
get { return this.typeID; }
set { this.typeID = value; }
}

public string Internalid
{
get { return this.internalid; }
set { this.internalid = value; }
}

public int Counter
{
get { return this.counter; }
set { this.counter = value; }
}

public IList Projectdetail
{
get { return this.projectdetail; }
set { this.projectdetail = value; }
}


#region IObjectHelper Members
public object this[string memberName]
{
get {
switch (memberName) {
case "id": return this.id;
case "title": return this.title;
case "description": return this.description;
case "specialCare": return this.specialCare;
case "feature": return this.feature;
case "material": return this.material;
case "quote": return this.quote;
case "quoteAuthor": return this.quoteAuthor;
case "typeID": return this.typeID;
case "internalid": return this.internalid;
case "counter": return this.counter;
case "projectdetail": return this.projectdetail;
default: throw new Exception("Invalid Member");
}
}
set {
switch (memberName) {
case "id": this.id = (int) value; break;
case "title": this.title = (string) value; break;
case "description": this.description = (string) value; break;
case "specialCare": this.specialCare = (string) value; break;
case "feature": this.feature = (string) value; break;
case "material": this.material = (string) value; break;
case "quote": this.quote = (string) value; break;
case "quoteAuthor": this.quoteAuthor = (string) value; break;
case "typeID": this.typeID = (string) value; break;
case "internalid": this.internalid = (string) value; break;
case "counter": this.counter = (int) value; break;
case "projectdetail": this.projectdetail = (IList) value; break;
default: throw new Exception("Invalid Member");
}
}
}
#endregion

#region IObjectNotification Members
public void OnCreated(Transaction transaction)
{
// TODO
}

public void OnCreating(Transaction transaction)
{
// TODO
}

public void OnDeleted(Transaction transaction)
{
// TODO
}

public void OnDeleting(Transaction transaction)
{
// TODO
}

public void OnMaterialized(System.Data.IDataRecord dataRecord)
{
// TODO
}

public void OnPersistError(Transaction transaction, Exception exception)
{
// TODO
}

public void OnUpdated(Transaction transaction)
{
// TODO
}

public void OnUpdating(Transaction transaction)
{
// TODO
}
#endregion
}
}
=================================================

Why did I post all of this; A) little documentation exist, B) I want more peers to collaborate on this with. Please don't hesitate to ask me questions, I wan more of us to "get this". JAVA is doing this, and has been for years! It's time us .NET coders got up to speed!

Sincerely,
David Dimmer
Director of Development

Fyin, Inc.
Local: [phone]
Toll free: [phone]
Fax: [phone]
W: http://www.fyin.com
Complete Website Design and Interactive Programming