In my situation, I have a C# DLL I wrote myself that has been registered in a SQL Server database containing sales/customer data. As of now, I'm a bit stuck.
The DLL makes a call to a remote server to obtain a token. The token is then added to the database. Ideally, the next step is to retrieve data from the SQL server into the DLL and then build and post a JSON file to a remote server, using the token the DLL retreived.
Where I am stuck is there are 134 elements, with different data types, in the receipt section of my JSON file alone. I will need to be able to handle all of that data in my C# DLL and in the future I may need to pull a lot more data into this JSON file to be posted. I've done some reasearch and using user defined type (UDT) wouldn't quite work and from what I can tell, is an option I should stay away from. My other two options I know of would be to either export to XML and parse it in my DLL or to create and read in 134+ variables.
My question is: Is there a simpler way to do this besides XML/hard coding? It would be ideal if there was a way to use an array or an object but neither seem to be supported according to what I've read here
Thank you.
Important note: Because of the database and the JSON library I'm using, I'm working in .Net framework 2.0
I would recommend you to use XML serialization on the C# side. You create an object that models your database schema.
As you are using .NET 2.0 you have already a good set of base classes to model your database schema in an object oriented way. Even nullable columns can be mapped to nullable objects to save memory and network space.
From your SQL side you use the FOR XML clause, that will change the output of your query from tabular to XML. You have to make just one good SP that will create XML in the exact hierarchy as your C# objects.
This XML has to match the names and the casing of the classes and the properties of your c# class(es).
Then you will de-serialize this XML from the C# side in no more than 10 lines of code. No matter how big or how complex the data hierarchy is, and you will have instantly in memory objects that you can immediately serialize into JSON again.
Let me know if you need some good examples on how to achieve this. And please clarify if you are running inside of the SQL Server CLR execution context, as you might need special permissions for serializing/deserialize data.
I guess its a very primitive way of achieving what Entity Framework does. but it works.