NOSQL for SQLServer etc

KeyValueStore.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using Microsoft.CSharp.RuntimeBinder;
using Newtonsoft.Json;

namespace Blommekes.Infrastructure
{
    public class KeyValueStore
    {
        static bool TableExistenceVerified = false;

        private IDbConnection NewConnection()
        {
            var constr = ConfigurationManager.ConnectionStrings["Dampen"];
            var conn = new System.Data.SqlClient.SqlConnection(constr.ConnectionString);
            lock (this)
            {
                if (!TableExistenceVerified)
                {
                    try
                    {
                        conn.Open();
                        conn.ExecuteNonQuery(
                            "CREATE TABLE [KeyValues] ( " +
                            "  [Value] ntext NOT NULL " +
                            ", [Id] nvarchar(100) NOT NULL " +
                            ", [TypeName] nvarchar(255) NOT NULL " +
                            ", [RowId] ROWVERSION NOT NULL " +
                            ", CONSTRAINT PK_KeyValues PRIMARY KEY([Id],[TypeName])" +
                            ")");
                    }
                    catch (Exception e)
                    {
                        // will be a problem with localized messages
                        if (e.Message.Contains("already"))
                            System.Diagnostics.Debug.WriteLine(e.Message);
                        //else
                        //    throw;
                    }
                }
            }
            return conn;
        }

        public string Upsert<T>(T value)
        {
            var Key = MyTypeInfo.Get(value).GetId(value) ?? Guid.NewGuid().ToString();
            Upsert(Key, value);
            return Key;
        }

        // uses optimistic concurrency if object contains a string named "RowId"
        public void Upsert<T>(string key, T value)
        {
            string json = JsonConvert.SerializeObject(value, Formatting.Indented);
            var ti = MyTypeInfo.Get(value);
            string rowid = ti.GetRowId(value);
            using (var conn = NewConnection())
            using (var cmd = conn.CreateCommand())
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                var sql =
                    "MERGE KeyValues AS target " +
                    "USING (VALUES(@p0,@p1,@p2,@p3)) AS source(ID,VALUE,TYPENAME,ROWID) " +
                    "   ON source.id = target.id " +
   (!ti.HasRowId ? "" : "  AND target.ROWID = source.ROWID ") +
                    "  AND target.TYPENAME = source.TYPENAME " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET VALUE = source.VALUE " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT (ID,VALUE,TYPENAME) " +
                    "  VALUES (source.ID,source.VALUE,source.TYPENAME);";
                conn.ExecuteNonQuery(sql, key, json, typeof(T).Name, rowid);
                conn.Close();
            }
        }

        public T Get<T>(string key)
        {
            using (var conn = NewConnection())
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                var rdr = conn.GetReader("select value,rowid from KeyValues where id = @p0 and typename=@p1", key, typeof(T).Name).FirstOrDefault();
                if (rdr != null)
                {
                    var json = rdr.GetString(0);
                    var result = JsonConvert.DeserializeObject<T>(json);
                    var ti = MyTypeInfo.Get(result);
                    if (ti.HasRowId)
                        ti.SetRowId(result, rdr.GetString(1));
                    return result;
                }
                else
                {
                    return default(T);
                }
            }
        }

        public void Delete<T>(string key)
        {
            using (var conn = NewConnection())
            {
                conn.ExecuteNonQuery("delete from KeyValues where id = @p0 and typename= @p1", key, typeof(T).Name);
                conn.Close();
            }
        }

        private class MyTypeInfo
        {
            public bool HasRowId { get; protected set; }

            public bool HasId { get; protected set; }

            public Func<object, string> GetRowId { get { return x => { if (!HasRowId) return null; dynamic d = x; return (string)d.RowId; }; } }

            public Action<object, string> SetRowId { get { return (x, y) => { if (!HasRowId) return; dynamic d = x; d.RowId = y; }; } }

            public Func<object, string> GetId { get { return x => { if (!HasId) return null; dynamic d = x; return (string)d.Id; }; } }

            public Action<object, string> SetId { get { return (x, y) => { if (!HasId) return; dynamic d = x; d.Id = y; }; } }

            private MyTypeInfo(object o)
            {
                try
                {
                    HasRowId = true;
                    string blah = GetRowId(o);
                    SetRowId(o, blah);
                }
                catch (RuntimeBinderException)
                {
                    HasRowId = false;
                }
                try
                {
                    HasId = true;
                    string blah = GetId(o);
                    SetId(o, blah);
                }
                catch (RuntimeBinderException)
                {
                    HasId = false;
                }
            }

            [ThreadStatic]
            static Dictionary<Type, MyTypeInfo> MyTypes;

            static public MyTypeInfo Get<T>(T value)
            {
                if (MyTypes == null) MyTypes = new Dictionary<Type, MyTypeInfo>();
                var t = typeof(T);
                if (!MyTypes.ContainsKey(t))
                    MyTypes[t] = new MyTypeInfo(value);
                return MyTypes[t];
            }
        }
    }
}