Kapiteinhook
New Member
Good morning everyone, I have a bit of a unique problem. I am registering a dll as an assembly inside of a SQL Server database that takes in an SQLXml variable, along with two strings, and serializes the data into JSON format. For reference, here is the method call:\[code\][SqlProcedure]public static void Receipt(SqlString initiatorPassword, SqlString initiatorId, SqlXml XMLOut, out SqlString strMessge)\[/code\]I would use Newtonsoft.Json or Jayrock for this application if this was any other type of app. Normally I would follow the answer given here and do something similar to:\[code\]XmlReader r = (XmlReader)XmlOut.CreateReader();XmlDocument doc = new XmlDocument();doc.load(r);\[/code\]However, since I am using SQLClr, there are certain rules of the road. One of which is that \[code\].Load()\[/code\] and any other inherited method can't be used. I think the .Net framework said it best:\[quote\] System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException:
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.\[/quote\]I am not fluent in SqlClr by any means, but if I am understanding this blog correctly, this is caused by SqlCLR's rules not allowing .Load() and inherited methods without being signed for and having a strong name. My DLL and the 3rd party DLLs I'm using do not have a strong name nor can I rebuild and sign them myself. So, this leaves me stuck with attempting to complete this task without using load (Unless someone knows another way this can be done)My only solution I could come up with is a very ugly while loop that isn't working properly, I have been getting a "Jayrock.Json.JsonException: A JSON member value inside a JSON object must be preceded by its member name" exception. Here is the while loop I wrote (not my best code, I know):\[code\] int lastdepth = -1; Boolean objend = true; Boolean wt = false;//Write Member/Object statements for the header omittedJsonWriter w = new JsonTextWriter() while (m.Read()) { if ((lastdepth == -1) && (m.IsStartElement())) {//Checking for root element lastdepth = 0; } if ((m.IsStartElement()) && (lastdepth != -1)) {//Checking for Start element ( <html> ) w.WriteMember(m.Name); if (objend) { //Check if element is new Parent Node, if so, write start object w.WriteStartObject(); objend = false; } } if (m.NodeType == XmlNodeType.Text) { //Writes text here. NOTE: m.Depth > lastdepth here!!!!!!! w.WriteString(m.Value); wt = true; } if (m.NodeType == XmlNodeType.Whitespace) //If whitespace, keep on truckin { m.Skip(); } if ((m.NodeType == XmlNodeType.EndElement) && (wt == false) && (lastdepth > m.Depth)) {//End element that ends a series of "Child" nodes w.WriteEndObject(); objend = true; } if ((m.NodeType == XmlNodeType.EndElement) && (wt == true))//Standard end of an el { wt = false; } lastdepth = m.Depth; } w.WriteEndObject(); jout = w.ToString();}\[/code\]My question is, since I can't use .load() and my while loop is a mess to debug, what would be the best approach here? The other approach commonly discussed is deserialization into an Object with matching variables but I have a rather large XML coming out of SQL Server. My loop is an attempt at dynamic programming since there are ~200 fields that are being pulled to make this XML. Thank you in advance!Note: I am using Jayrock and working in .Net Framework 2.0. I can not change the framework version at this time.
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.\[/quote\]I am not fluent in SqlClr by any means, but if I am understanding this blog correctly, this is caused by SqlCLR's rules not allowing .Load() and inherited methods without being signed for and having a strong name. My DLL and the 3rd party DLLs I'm using do not have a strong name nor can I rebuild and sign them myself. So, this leaves me stuck with attempting to complete this task without using load (Unless someone knows another way this can be done)My only solution I could come up with is a very ugly while loop that isn't working properly, I have been getting a "Jayrock.Json.JsonException: A JSON member value inside a JSON object must be preceded by its member name" exception. Here is the while loop I wrote (not my best code, I know):\[code\] int lastdepth = -1; Boolean objend = true; Boolean wt = false;//Write Member/Object statements for the header omittedJsonWriter w = new JsonTextWriter() while (m.Read()) { if ((lastdepth == -1) && (m.IsStartElement())) {//Checking for root element lastdepth = 0; } if ((m.IsStartElement()) && (lastdepth != -1)) {//Checking for Start element ( <html> ) w.WriteMember(m.Name); if (objend) { //Check if element is new Parent Node, if so, write start object w.WriteStartObject(); objend = false; } } if (m.NodeType == XmlNodeType.Text) { //Writes text here. NOTE: m.Depth > lastdepth here!!!!!!! w.WriteString(m.Value); wt = true; } if (m.NodeType == XmlNodeType.Whitespace) //If whitespace, keep on truckin { m.Skip(); } if ((m.NodeType == XmlNodeType.EndElement) && (wt == false) && (lastdepth > m.Depth)) {//End element that ends a series of "Child" nodes w.WriteEndObject(); objend = true; } if ((m.NodeType == XmlNodeType.EndElement) && (wt == true))//Standard end of an el { wt = false; } lastdepth = m.Depth; } w.WriteEndObject(); jout = w.ToString();}\[/code\]My question is, since I can't use .load() and my while loop is a mess to debug, what would be the best approach here? The other approach commonly discussed is deserialization into an Object with matching variables but I have a rather large XML coming out of SQL Server. My loop is an attempt at dynamic programming since there are ~200 fields that are being pulled to make this XML. Thank you in advance!Note: I am using Jayrock and working in .Net Framework 2.0. I can not change the framework version at this time.