Second IUserType of the day 8211 Store a list of string in database with NHIbernate

I have an object that has 4 properties of IList<String> type. You can natively map these properties in a separate table with this simple mapping

1
2
3
4

<bag name="keys" access="field" cascade="all-delete-orphan" table="Keys" fetch="join">
    <key column="deps_id" />
    <element column="deps_key" type="String" />
</bag>

Ok, this is a simple mapping that store all the keys into another table with two columns, one is the foreign key to the main object, the other is a string column to store the data. But a similar mapping does not satisfy me, because I need to create 4 more table to store string coming from my 4 IList<String> properties…too bad.

The solution is…again….IUserType. The solution is store the strings in a single column, combining the strings in one with a separator that does not happens to be part of any string, as an example, if the collection contains (“Key1”, “Key2”) I want to be stored as “Key1#Key2”, here is the get part of the usertype

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
{
    List<String> result = new List<String>();
    Int32 index = rs.GetOrdinal(names[0]);
    if (rs.IsDBNull(index) || String.IsNullOrEmpty((String) rs[index]))
        return result;
    foreach (String s in ((String)rs[index]).Split(cStringSeparator))
        result.Add(s);
    return result;
}

If the data in database is null, or is an empty string I return an empty List<String>, but if the data is not empty I split the string with the separator, and then add each single string in the list. The set part is similar

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
{
    if (value == null || value == DBNull.Value)
    {
        NHibernateUtil.String.NullSafeSet(cmd, null, index);
    }
    IEnumerable<String> stringList = (IEnumerable<String>) value;
    StringBuilder sb = new StringBuilder();
    foreach(String s in stringList) {
        sb.Append(s);
        sb.Append(cStringSeparator);
    }
    if (sb.Length > 0) sb.Length--;
    NHibernateUtil.String.Set(cmd, sb.ToString(), index);
}

If the value is null I set null value, if not I simply cast the original value as an IEnumerable<String>, that is sufficient for me to enumerate all the string, store them in a StringBuilder, and finally remove the trailing separator and store the string in database. Here is a Test

1
2
3
4
5
6

...
Domain d = new Domain();
d.BaseUri = new Uri("http://www.nablasoft.com");
d.ContentBlackList.Add("Key1");
d.ContentBlackList.Add("Key2");
...

This piece of code produces this SQL

1
2
3

INSERT INTO Domain (doma_baseUrl, doma_contentBlackList, doma_Id) 
VALUES (@p0, @p1, @p2); 
@p0 = 'http://www.nablasoft.com/', @p1 = 'Key1#Key2', @p2 = '28857095-f642-4d11-875a-714eb35f0537'

As you can see the Uri is translated into a string and the two keywords are combined into one, in this way I can store all the 4 properties in the same table, having a clearer structure of the database.

alk.

Tags: NHibernate IUserType