Subsonic DAL fails compilation on columns which are keywords

I'm using Subsonic 3 (Active Record), VS2010, Framework 3.5 against a MySQL database. Someone named a column "string." I S@*t you not.

OK, I've named some database objects some dumb names (Like naming a SQL Server table "User") before but... c'mon! And I cannot change the table because of dependencies in the app!

OK I feel a little better now that I've vented a little开发者_开发问答 thanks for listening

So, of course, in the generated code I get errors all over the place from lines like:

private string string {}

I don't see this as a priority bug for Subsonic unless others are having similar issues with other column names. Any plans to do anything about this?

Thanks

Paul

p.s. I will try to talk the other developers into changing, or allowing me to change the column name, I know that is the real solution, but Subsonic could gracefully handle illegal names, maybe something like the way .netTiers handles it (add _safeName to the name, so it would become string_safeName).

I perhaps used the wrong language. Before I went ahead and made modifications, I wanted to make sure this was not all ready on some priority list somewhere or being worked on. Here's the way I went with it.

In Settings.ttinclude:

string[] reservedWords = new string[]{"abstract", "as", "base", "bool", "break", "byte", "case", "catch", "char", "checked", 
            "class", "const", "continue", "decimal", "default", "delegate", "do", "double", "else", "enum", "event", "explicit", 
            "extern", "finally", "fixed", "float", "for", "foreach", "goto", "if", "implicit", "in", "int", "interface", "internal", 
            "is", "lock", "long", "namespace", "new", "null", "object", "operator", "out", "override", "params", "private", 
            "protected", "public", "readonly", "ref", "return", "sbyte", "sealed", "short", "sizeof", "stackalloc", "static", "string", 
            "struct", "switch", "this", "throw", "try", "typeof", "unit", "ulong", "unchecked", "unsafe", "ushort", "using", "virtual", 
            "void", "volatile", "while", "FALSE", "TRUE", "yield", "by", "descending", "from", "group", "into", "orderby", "select", 
            "var", "where" };

 string CleanUp(string tableName){
    string result=tableName;

    //strip blanks
    result=result.Replace(" ","");

    if(reservedWords.Contains(result)){
        result += "_SafeName";
    }
    //put your logic here...

    return result;
}

The CleanUp function already fixes table and column names, so I put my logic there. I hope I got all the reserved words...

This is also on a smaller project where the database is MySQL.

So this hack/patch is only for c#/mysql, but very easy to move to other DBs/languages.

You can fix this yourself very easily.

Open up the SQLServer.ttinclude file. It's a T4 template file that SubSonic uses to generate your code.

Almost halfway down, on line 155, you will find the LoadColumns function:

List<Column> LoadColumns(Table tbl){
    var result=new List<Column>();
    var cmd=GetCommand(COLUMN_SQL);
    cmd.Parameters.AddWithValue("@tableName",tbl.Name);

    using(IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){
        while(rdr.Read()){
            Column col=new Column();
            col.Name=rdr["ColumnName"].ToString();
            col.CleanName=CleanUp(col.Name);
            col.DataType=rdr["DataType"].ToString();
            col.SysType=GetSysType(col.DataType);
            col.DbType=GetDbType(col.DataType);
            col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";
            col.IsNullable=rdr["IsNullable"].ToString()=="YES";
            int.TryParse(rdr["MaxLength"].ToString(),out col.MaxLength);

            result.Add(col);
        }

    }

    return result;
}

Simply add logic here in this function when it assigns the name to change from the column name in your database, "string", to something a little more sane.

List<Column> LoadColumns(Table tbl){
    var result=new List<Column>();
    var cmd=GetCommand(COLUMN_SQL);
    cmd.Parameters.AddWithValue("@tableName",tbl.Name);

    using(IDataReader rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)){
        while(rdr.Read()){
            Column col=new Column();

            var rawName = rdr["ColumnName"].ToString();
            if (rawName.Equals("string")) {
                col.Name="StringColumn";
            } else {
                col.Name=rawName;
            }

            col.CleanName=CleanUp(col.Name);
            col.DataType=rdr["DataType"].ToString();
            col.SysType=GetSysType(col.DataType);
            col.DbType=GetDbType(col.DataType);
            col.AutoIncrement=rdr["IsIdentity"].ToString()=="1";
            col.IsNullable=rdr["IsNullable"].ToString()=="YES";
            int.TryParse(rdr["MaxLength"].ToString(),out col.MaxLength);

            result.Add(col);
        }

    }

    return result;
}

Also, were you to put some time into it and make it handle all keywords (say, through lookup in a static dictionary and change the names by appending a common suffix or dictionary substitution), then you could submit back a patch to the project and contribute instead of trying to talk the other developers into doing it.

If you change the LoadColumns method you'll still have problems on the foreign keys, but you can replace your CleanUp(string tableName) method in Setting.ttinclude to:

string CleanUp(string tableName){
        string result=tableName;

        //strip blanks
        result=result.Replace(" ","");

        //put your logic here...
        if (reservedWords.Contains(result)) {
            result = "_" + result;
        }

        return result;
    }

this worked like a charm for me.