Geeks With Blogs
Łukasz Kuryło's blog

I have a simple database table with only id and one xml column as shown below.

 extensions db table

 

 

 

 

 

In this column I have a list of available extensions saved as xml.

    public class FileExtensionsList
    {
        public FileExtensionsList()
        {
            Id = Guid.NewGuid();
        }

        public virtual Guid Id { set; get; }
        public virtual XmlDocument Extensions { set; get; }
    }

This xml I'm not creating manually. I have a custom entity which I serialize to xml:

 

    [XmlRoot("FileExtensionsList")]
    [Serializable]
    public class FileExtension
    {
        [XmlAttribute]
        public string Extension { set; get; }

        public override bool Equals(object obj)
        {
            if (obj == null)
                return false;

            if ((obj as FileExtension) == null)
                return false;

            if (this.Extension == ((FileExtension)obj).Extension)
            {
                return true;
            }

            return false;
        }

        public override int GetHashCode()
        {
            return Extension.GetHashCode();
        }
    }

And now I want to get this list from db using NHibernate. I'm not sure, in NHibernate 3.2 maybe there is already a build-in solution for working with xml columns, but in this post I will show how we can use a custom user type. This solution work for me from month and I don't see a reason why not to use it.

At the beginning we must add this custom user type. I have found somewhere a working implementation. Unfortunatelly I don't remember where. Code is below:

 /*
* UserType allowing easy saving of NHIbernate XmlDocument property.
*
* Example
* =======
*
* //Message.cs - Example class with a XmlDocument
* public class Message
* {
* public XmlDocument Body{get;set;}
* }
*
* //Message.hbm.xml - The mapping
*  *  *  *  * 
*
* History
* =======
* - This code was found online somewhere, sorry, I can't remember where :-(
* - I've tweaked it a little to work with 2nd level cache and NHibernate 2.1.x.
* - Tobin Harris
*/
    public class XmlType : IUserType
    {
        public new bool Equals(object x, object y)
        {
            if (x == null || y == null)
                return false;

            var xdoc_x = (XmlDocument)x;
            var xdoc_y = (XmlDocument)y;
            return xdoc_y.OuterXml == xdoc_x.OuterXml;
        }

        public int GetHashCode(object x)
        {
            return x.GetHashCode();
        }


        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            if (names.Length != 1)
                throw new InvalidOperationException("names array has more than one element. can't handle this!");

            var document = new XmlDocument();

            var val = rs[names[0]] as string;

            if (val != null)
            {
                document.LoadXml(val);
                return document;
            }

            return null;
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            var parameter = (DbParameter)cmd.Parameters[index];

            if (value == null)
            {
                parameter.Value = DBNull.Value;
                return;
            }

            parameter.Value = ((XmlDocument)value).OuterXml;
        }

        public object DeepCopy(object value)
        {
            var toCopy = value as XmlDocument;

            if (toCopy == null)
                return null;

            var copy = new XmlDocument();
            copy.LoadXml(toCopy.OuterXml);
            return copy;
        }

        public object Replace(object original, object target, object owner)
        {
            throw new NotImplementedException();
        }

        public object Assemble(object cached, object owner)
        {
            var str = cached as string;
            if (str != null)
            {
                var doc = new XmlDocument();
                doc.LoadXml(str);
                return doc;
            }
            else
            {
                return null;
            }

        }

        public object Disassemble(object value)
        {
            var val = value as XmlDocument;
            if (val != null)
            {
                return val.OuterXml;
            }
            else
            {
                return null;
            }
        }

        public Types.SqlType[] SqlTypes
        {
            get
            {
                return new Types.SqlType[] { new SqlXmlType() };
            }
        }

        public Type ReturnedType
        {
            get { return typeof(XmlDocument); }
        }

        public bool IsMutable
        {
            get { return true; }
        }
    }

    public class SqlXmlType : Types.SqlType
    {
        public SqlXmlType()
            : base(DbType.Xml)
        {
        }
    }

    public class SqlXmlStringType : Types.SqlType
    {
        public SqlXmlStringType()
            : base(DbType.String, 4000)
        {
        }
    }

How this code works is not important. Important is that, now we can map our entities using this code and get access to the xml columns in our tables.

 

So let's map the entity from the beginning of this post.

    public class FileExtensionsListMap : ClassMapping<FileExtensionsList>
    {
        public FileExtensionsListMap()
        {
            Table("Extensions");
            Id(x=>x.Id, x=>x.Generator(Generators.Guid));
            Property(x => x.Extensions, x => x.Type<XmlType>());
        }
    }

I have used here the new to NHibernate 3.2 mapping by code, which I personally prefer much more than creating the *.hbm.xml files manually or even using the Fluent NHibernate. I used to have some problems with FN, maybe that is the reason..

Ok. When the mapping is created, we can configure the NHibernate and do some queries to check if everything works as we expecting. I'm not going to show right now hot to configure the NHibernate, this will be in one of my futures posts.

So let's look at the queries. To get the extensions as XMLDocument object we can use

    public class GetAvailableExtensionsList
    {
        public XmlDocument Execute(ISession session)
        {
            ICriteria criteria = session.CreateCriteria<FileExtensionsList>()
                  .SetProjection(Projections.Property("Extensions"));

            XmlDocument result = criteria.UniqueResult<XmlDocument>();
            return result;
        }
    }

If we want to work on the objects, not the xml itself we can deserialized it:

SerializationUtils.DeSerializeXmlToObject<List<FileExtension>>(extensions)

where extensions is the XmlDocument object.

With the raw xml we can use e.g. XSL Transforations to convert it to html and show on the page.

To set the data to database, the query looks that

    public class SetAvailableExtensionsList
    {
        public FileExtensionsList Execute(ISession session, XmlDocument extensions)
        {
            var element =
                session.CreateCriteria<FileExtensionsList>()
                .UniqueResult<FileExtensionsList>();

            if (element == null)
            {
                element = new FileExtensionsList();
            }

            element.Extensions = extensions;
            return element;
        }
    }

Now we must serialize the extensions and execute the ISession.Save() or ISession.Update() method on this query. ISession has the SaveOrUpdate() method too, but it need do something more to use it, because otherwise we will get an exception. In ony of my futures posts I will try to take a look at it.

In this example I'm using the ISession.Save() method

            XmlDocument serializedExtensions =
                SerializationUtils.SerializeObjectToXml<List<FileExtension>>(extensions);

            OperationsInTransaction.Execute(x =>
            {
                var element = new SetAvailableExtensionsList().Execute(x, serializedExtensions);
                x.Save(element);
            });

What is the OperationsInTransaction and how the serialize/deserialize mechanism is implemented  I will cover in other posts.

 

SQL Server has the possibility to e.g. query the xml in database with XPath, but I don't know yet if it is possible to do it with NHibernate. At this time I'm working with the xml after I took the entire file from the database in the c# code.

 

PS. This solution works properly with the xml column type in the database. But this is also possible to change the xml column type to nvarchar(max) and the solution will work in this situation too. To do it, we must change the code from

       public Types.SqlType[] SqlTypes
        {
            get
            {
                return new Types.SqlType[] { new SqlXmlType() };
            }
        }

to

        public Types.SqlType[] SqlTypes
        {
            get
            {
                return new Types.SqlType[] { new SqlXmlStringType() };
            }
        }
Posted on Wednesday, August 17, 2011 1:35 PM NHibernate | Back to top


Comments on this post: Using NHibernate with xml column

# re: Using NHibernate with xml column
Requesting Gravatar...
Hello,
You can use these custom native NH types:
"NHibernate.Type.XDocType" for mapping System.Xml.Linq.XDocument
"NHibernate.Type.XmlDocType" for mapping System.Xml.XmlDocument
Left by Vahid on Aug 17, 2011 4:08 PM

# re: Using NHibernate with xml column
Requesting Gravatar...
So there is at last a build-in solution for that. Vahid thank you for your feedback.
Left by Łukasz Kuryło on Aug 17, 2011 4:25 PM

Comments have been closed on this topic.
Copyright © Łukasz Kuryło | Powered by: GeeksWithBlogs.net