CODEWITHSUNDEEP
c#.netpostgresql
baramuse
baramuse

Reputation: 360

parse hstore column in .net

I was wondering what was the best way to retrieve and handle hstore data in .net.

If I do a basic query, then it'll output a string formatted that way :

"key1" => "value1", "key2" => "value2"

Looks alike KeyValuePair that I today parse like that :

SimpleJson.SimpleJson
.DeserializeObject<Dictionary<string, string>> ("{" + tags.Replace ("=>", ":") + "}");

I could do it manually with something like :

But then, what if there is a "," inside a value or a full element inside a value, should I do a recursive parsing? and there goes on all the questions about parsing a string :)

Do you see a better way than the trick of Json ?

Upvotes: 1

Views: 1162

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

You have a few options. I can't say whether any are better than what you have now. My recommendation actually would be to start beta testing 9.3 as soon as it is in beta and look at moving your code over there. 9.3's hstore has a cast to json, so you can just do it in the db. This may be a good reason to plan to be an early adopter. In essence in 9.3, you will just be able to: 

SELECT my_hstore::json FROM mytable;

A second possibility is that you could create a structure in memory in PostgreSQL (for existing supported versions in 9.1 and 9.2) to write your own cast to json. For insight on how to do it, see Andrew's blog below.

A third possibility is to use existing functions of XML and write an XML cast. This is fairly easy. With a simple function you could easily do a:

SELECT my_hstore::xml FROM mytable;

The final possibility is to do string parsing in .net. This is the area I have the least knowledge of, but I would also suggest it is the last choice in large part because with the others, you have direct access to the hstore data interfaces, and also because the functions you would be using all have relatively tight contracts and a lot of other users.

Upvotes: 1

Related Questions