Import and export JSON data with Starcounter

July 21, 2016 8:59 pm Published by Leave your thoughts

This short article explains how to:

  • Export data into JSON string.
  • Import data from JSON string.

The source code is available on GitHub – Import and export JSON data with Starcounter.

Define database classes

namespace StarcounterJsonData.Database {
    [fusion_builder_container hundred_percent="yes" overflow="visible"][fusion_builder_row][fusion_builder_column type="1_1" background_position="left top" background_color="" border_size="" border_color="" border_style="solid" spacing="yes" background_image="" background_repeat="no-repeat" padding="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="no" center_content="no" min_height="none"][Database]
    public class Brand {
        public string Name { get; set; }

        public QueryResultRows<Socket> Sockets {
            get {
                return Db.SQL<Socket>("SELECT s FROM StarcounterJsonData.Database.Socket s WHERE s.Brand = ?", this);
            }
        }

        public object ToJson() {
            return new {
                this.Name,
                Sockets = this.Sockets.Select(x => x.ToJson())
            };
        }
    }

    [Database]
    public class Socket {
        public Brand Brand { get; set; }
        public string Name { get; set; }
        public int Year { get; set; }

        public QueryResultRows<Cpu> Cpus {
            get {
                return Db.SQL<Cpu>("SELECT c FROM StarcounterJsonData.Database.Cpu c WHERE c.Socket = ?", this);
            }
        }

        public object ToJson() {
            return new {
                this.Name,
                this.Year,
                Cpus = this.Cpus.Select(x => x.ToJson())
            };
        }
    }

    [Database]
    public class Cpu {
        public Socket Socket { get; set; }
        public string Name { get; set; }
        public decimal Frequency { get; set; }
        public int Cores { get; set; }
        public int Threads { get; set; }

        public object ToJson() {
            return new {
                this.Name,
                this.Frequency,
                this.Cores,
                this.Threads
            };
        }
    }
}

The database classes naturally have cross-references. A CPU has a reference to Socket, and a Socket has list of CPUs. That is why trying to serialize such objects will fail with stack overflow exception. Adding a helper ToJson method solves the issue.

We also need a class to contain all top level database tables.

namespace StarcounterJsonData.Database {
    public class JsonContainer {
        public QueryResultRows<Brand> Brands {
            get {
                return Db.SQL<Brand>("SELECT b FROM StarcounterJsonData.Database.Brand b");
            }
        }

        public object ToJson() {
            return new {
                Brands = this.Brands.Select(x => x.ToJson())
            };
        }
    }
}

Let’s use the famous Newtonsoft Json.NET to export the data.

JsonContainer container = new JsonContainer();
string json = JsonConvert.SerializeObject(container.ToJson());

Import some data back into the database

Deserialization of objects requires a couple more tricks. The Brand and Cpu classes contain read only properties. The Brand.Sockets and Socket.Cpus properties should be turned into editable collections. The JsonContainer class also has a read only property Brands, it should also be updated.

To be able to work with Newtonsoft Json.NET deserialization the collection should implement IEnumerable<T> and ICollection<T> interfaces.

See the implementation of the editable Starcounter collections on GitHub:

namespace StarcounterJsonData.Database {
    public class BrandCollection : StarcounterCollection<Brand> {
        protected override string GetSqlQuery() {
            return "SELECT b FROM StarcounterJsonData.Database.Brand b";
        }
    }

    public class SocketCollection : StarcounterRelationCollection<Socket, Brand> {
        public SocketCollection(Brand Brand) : base(Brand) { }

        protected override string GetSqlQuery() {
            return "SELECT s FROM StarcounterJsonData.Database s WHERE s.Brand = ?";
        }

        protected override Brand GetSubject(Socket Entity) {
            return Entity.Brand;
        }

        protected override void RemoveSubject(Socket Entity) {
            Entity.Brand = null;
        }

        protected override void SetSubject(Socket Entity, Brand Subject) {
            Entity.Brand = Subject;
        }
    }

    public class CpuCollection : StarcounterRelationCollection<Cpu, Socket> {
        public CpuCollection(Socket Socket) : base(Socket) { }

        protected override string GetSqlQuery() {
            return "SELECT c FROM StarcounterJsonData.Database.Cpu c WHERE c.Socket = ?";
        }

        protected override Socket GetSubject(Cpu Entity) {
            return Entity.Socket;
        }

        protected override void RemoveSubject(Cpu Entity) {
            Entity.Socket = null;
        }

        protected override void SetSubject(Cpu Entity, Socket Subject) {
            Entity.Socket = Subject;
        }
    }
}

Now there are three editable collections: BrandCollection, SocketCollection, and CpuCollection. They should be used instead of QueryResultRow.

public BrandCollection Brands { get; protected set; }
public SocketCollection Sockets {
    get {
        return new SocketCollection(this);
    }
}
public CpuCollection Cpus {
    get {
        return new CpuCollection(this);
    }
}

Note: it is not allowed to declare custom non-readonly properties in the database classes. That is why the Sockets and Cpus collections cannot be saved in the class the same way as JsonContainer saves Brands collection.

The final step is to deserialize a JSON string with Json.NET. The deserialization should be performed inside a Db.Transact or Db.Scope since it will create database objects.

public string json = "Fill in your JSON data here!";

Db.Transact(() => {
    JsonConvert.DeserializeObject<JsonContainer>(json);
});

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Categorised in:

This post was written by Konstantin Cherniavskyi

Leave a Reply

Your email address will not be published. Required fields are marked *