Menu
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:
[SqlProcedure]
public static void Receipt(SqlString initiatorPassword,
SqlString initiatorId,
SqlXml XMLOut,
out SqlString strMessge)
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:
XmlReader r = (XmlReader)XmlOut.CreateReader();
XmlDocument doc = new XmlDocument();
doc.load(r);
However, since I am using SQLClr, there are certain rules of the road. One of which is that .Load() and any other inherited method can't be used. I think the .Net framework said it best:
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.
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):
int lastdepth = -1;
Boolean objend = true;
Boolean wt = false;
//Write Member/Object statements for the header omitted
JsonWriter 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();
}
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.
Note: I am using Jayrock and working in .Net Framework 2.0. I can not change the framework version at this time.
996
Once you have an XmlDocument object, you can use Json.NET to convert that object into a Json representation. var json = JsonConvert. SerializeXmlNode(doc, Formatting.
Click Tools=> NuGet Package Manager=> Package Manager Console. Type “PM> Install-Package Newtonsoft. Json”. DeserializeXmlNode() is the helper method to convert JSON to XML.
A JSON is a lightweight data-interchange format and the format of JSON is like a key-value pair. We can convert a JSONObject into an XML format using org. json. XML class, this provides static methods to convert an XML text into a JSONObject and to convert a JSONObject into an XML text.
I'm hoping this is applicable to you, but won't know unless you try. From here:
EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.
I believe one of these two is what you need, probably EXTERNAL_ACCESS. More:
We recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.
Note: You must create a new login to associate with the asymmetric key. This login is only used to grant permissions; it does not have to be associated with a user, or used within the application.
And the relevant code (I've used this successfully in the past):
USE master;
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
GO
I imagine you also want Generate serialization assembly turned on in your project.
This sidesteps your question in reality, but this really is the preferred way to allow other DLL's to be successfully loaded so you can write the correct code.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
