MonoRail StringClob fix June 9, 2009

I am currently writing an application in C# (2.0) that uses SQL Server 2005 as a data store and, for my sins, I am sticking an ASP.NET interface onto the front of it.

To make life a little more bearable, I am using Castle Project’s well-considered MonoRail framework with their ActiveRecord ORM. I would have looked at Microsoft’s cheekily-named MVC framework, but it wasn’t ready at the start of the project to be a consideration for me.

I also looked to see if I could use Django with IronPython and SQL Server, but although Microsoft has done it, I didn’t consider the combination to be remotely production grade. I do hope it gets there, because I think Python is a great language, and well suited for web-related work, and Django is just lovely. For those who need to build a web interface on .NET with SQL Server, it would be the most enjoyable way, and probably the fastest to code.

Of the bits of C# and MonoRail I’ve got stuck on, all but one are a result of my newbiness. This post is about that one.

In MonoRail with ActiveRecord, C# strings are stored, by default, as NVARCHAR(255) in SQL Server, if you create your schema using your classes. For big strings, you can use the PropertyAttribute’s ColumnType property, as described in its ActiveRecord documentation. It does NOT, however, behave as advertised. It does not set the database type to Text.

I found three solutions:

Firstly, for strings where you know the length will be less than 4000 characters, use the Length property of PropertyAttribute. e.g.

[Property(Column = "reguser_bio", Length = 500)]
public string Bio
{
get { return bio; }
set { this.bio = value; }
}

This will result in an NVARCHAR(500) field.

Secondly, for strings of unknown maximum length, use the SqlType property instead. In SQL Server 2000, you probably want the NTEXT type. In SQL Server 2005, you probably want the NVARCHAR(MAX) type. (To see why, read this.)

[Property(Column = "thesis_content", SqlType = "NVARCHAR(MAX)")]
public string Content
{
get { return content; }
set { this.content = value; }
}

And thirdly — and this was the first solution I found, and I consider it the ugliest — you can manually alter the field type. It’s what Garth Williams suggests in his blog. I extended the ActiveRecordMediator class to do it.

public class ApplicationActiveRecordMediator : ActiveRecordMediator
{
public static void FixStringClobFields(string Table, string[] Fields)
{
// Build SQL string
string sql = “”;
foreach (string Field in Fields)
{
// From SQL2005, use NVARCHAR(MAX) instead of NTEXT
sql = sql + String.Format(@”
ALTER TABLE {0}
ALTER COLUMN {1} NVARCHAR(MAX) NULL; “, Table, Field);
}
// Execute SQL
ConnectAndExecuteNonQuery(sql);
}
public static void FixStringClobField(string Table, string Field)
{
string sql = String.Format(@”
ALTER TABLE {0}
ALTER COLUMN {1} NVARCHAR(MAX) NULL; “, Table, Field);
ConnectAndExecuteNonQuery(sql);
}
private static void ConnectAndExecuteNonQuery(string sql)
{
Execute(
typeof(ActiveRecordBase),
delegate(NHibernate.ISession session, object data)
{
IDbConnection conn = session.Connection;
IDbCommand cmd = conn.CreateCommand();
session.Transaction.Enlist(cmd);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
return true;
}, null);
}
}

Choose whichever suits you, but I expect that in future I’ll probably be going with option 2 for arbitrarily long strings.

Leave a Reply