Marcus Belz Marcus Belz - 1 year ago 80
C# Question

Deserialization in a CLR Function fails

I have written some routines for the phonetic conversion of a text in C#. These routines require a bunch aof defined rules for the conversion (search-string/replace-string). The idea was to store thes rules as an embedded ressource within the assembly and then read the rules from it. The starting point for the the deserialization is as follows

public static phonet42n.Core.Rules Deserialize(phonet42n.Core.Rules.Ressources ressource)
string ressourceName;
phonet42n.Core.Rules returnValue;
System.Xml.XmlReader reader;
System.Xml.Serialization.XmlSerializer xmlSerializer;
phonet42n.Core.SerializableRules serializeableRules;

returnValue = new phonet42n.Core.Rules();

switch (ressource)
case Ressources.German_01:
ressourceName = RESSOURCE_XML_GERMAN_01;
case Ressources.German_02:
ressourceName = RESSOURCE_XML_GERMAN_02;
ressourceName = RESSOURCE_XML_GERMAN_01;

using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(ressourceName))
using (reader = System.Xml.XmlReader.Create(stream))
xmlSerializer = new System.Xml.Serialization.XmlSerializer(typeof(phonet42n.Core.SerializableRules));
serializeableRules = (phonet42n.Core.SerializableRules)xmlSerializer.Deserialize(reader);

foreach (phonet42n.Core.Rule entry in serializeableRules.Rules)
if (entry.SearchString != null && entry.SearchString.Length > 0)
returnValue.Add(entry.Index, entry);
return returnValue;

The application works fine when executed in an regular executable.

When executing the registered function in SQL Server I get the following error:

SELECT [dbo].[Phonet42n]('mayer', 1)


Meldung 6522, Ebene 16, Status 1, Zeile 22

.NET Framework-Fehler beim Ausf├╝hren der benutzerdefinierten Routine oder des benutzerdefinierten Aggregats 'Phonet42n':

System.InvalidOperationException: Fehler im XML-Dokument (3,4). ---> System.MethodAccessException: Fehler beim Versuch der Methode "Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read8_SerializableRules(Boolean, Boolean)", auf Methode "phonet42n.Core.Rule..ctor()" zuzugreifen.


bei System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)

bei System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)

bei System.Activator.CreateInstance(Type type, Boolean nonPublic)

bei System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)

bei System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)

bei System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)

bei Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read8_SerializableRules(Boolean isNullable, Boolean checkType)

bei Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read19_Rules()


bei System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

bei System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader)

bei phonet42n.Core.Rules.Deserialize(Ressources ressource)

bei phonet42n.Core.Rules..ctor(Ressources ressource, Characters characters)

bei phonet42n.Core.HashTable..ctor(Ressources ressource)

bei phonet42n.Core.Match..ctor(Re...

Any idea?

Answer Source

Whenever there are security exceptions, you can first try setting the Assembly to PERMISSION_SET = EXTERNAL_ACCESS, and if that doesn't work you can try UNSAFE. However, if one is attempting to dynamically load an Assembly, then that is supposed to be forbidden even for Assemblies marked as UNSAFE.

Since the issue here is of wanting to include a set of rules, that could probably be done in another Assembly. Then the main Assembly can reference the one containing the rules, and you just load the one containing the rules into SQL Server first. This would allow both Assemblies to remain marked as SAFE.

Of course, if there is no pressing need to keep the rules separate, then you could also just place them directly into a Collection in a Class.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download