How to save a dictionary into a database with C#?

Question

I have been using following way to save dictionaries into the database:

1. Convert the dictionary to XML.
2. Pass this XML to a SP(Stored Procedure).

3. In the SP use:

   Select 
       Key1,                   
       Value1
   into #TempTable                                      
   FROM OPENXML(@handle, '//ValueSet/Values', 1)
   WITH 
   (                                  
     key1 VARCHAR(MAX),     
     value1 varchar(100)  
   )
   

4. Done.

Is there a way to save dictionaries to a database without converting it to XML?

Answer 1

It depends whether...

• You want the data to be stored: The fastest way (both implementation and performance) to do that is by binary serialization (Protocol buffers for example). However the data is not readable with a select and every application who needs to read the data must use the same serialization (if it exists in the same technology/language). From my point of view, it breaks the purpose of storing in a SQL database.

• You want the data to be readable by humans: XML is an option while not so fast and a little bit difficult to read and still it is not query-able. However, it is quite fast to implement. You can also dump the result to a file and it's still readable. Moreover, you can share the data with other applications as XML is a widespread format. Json or Yaml are other viable options and a bit better performance-wise because of the reduced verbosity compared to XML. Json has the added benefit that it's easy to use in Web Api/Javascript Front.

• You want the data to be query-able. Depending on the way you go, it could be not so easy to implement. You would need two tables (one for keys and one for values). Then you could write either your own custom mapping code to map columns to properties or you could use frameworks for mapping objects to tables like Entity framework or NHibernate.

While Entity or NHibernate may appear a bit huge swiss knife for a small problem, it's always interesting to built some expertise in it, as the inner concepts are re-usable and it can really speed up development once you got a working setup.

Answer 2

Loop through the dictionary using a foreach statement.

Answer 3

Serialize the Dictionary, and store the binary data.

Then De-Serialize your data back into Dictionary.

Tutorial1 Tutorial2