CODEWITHSUNDEEP
c#jsondatabasesqlite
bairog
bairog

Reputation: 3387

Fill existing SQLite database with data from JSON (or other easily-readable text format) in C#

My database is divided into two parts: language-independent part and localizable part (containing Unicode text strings). For our translation staff it's much more easier to work not with some DB viewing tool but with some text format like JSON (via some tool of course). So I'm looking for a best way to load JSON data into my SQLite database. For now I use the foolowing approach (assume that I already have empty SQLite database):

  1. I deserealise JSON data into C# classes (via Newtonsoft.JSON.dll).
  2. For each of them I manually create INSERT command with paremeters (via System.Data.SQLite.dll) and then fill that parameters with class fields' values
  3. I execute that command.

Is that correct (easiest, reliable) aproach to fill SQLite database with JSON data? Maybe I've missed some useful API?

Any other easily-readable text format (with Unicode support!) which is better to work with in term of C# and SQLite is welcomed.

Upvotes: 1

Views: 4016

Answers (1)

PeterH
PeterH

Reputation: 841

Try Sqlite.NET, a basic Sqlite client and ORM: https://github.com/praeclarum/sqlite-net

From their wiki:

Once you have defined your entity, you can automatically generate tables in your database by calling CreateTable:

var db = new SQLiteConnection("foofoo");
db.CreateTable<Stock>();
db.CreateTable<Valuation>();

You can insert rows in the database using Insert. If the table contains an auto-incremented primary key, then the value for that key will be available to you after the insert:

public static void AddStock(SQLiteConnection db, string symbol) {
    var s = db.Insert(new Stock() {
        Symbol = symbol
    });
    Console.WriteLine("{0} == {1}", s.Symbol, s.Id);
}

Similar methods exist for Update and Delete.

You would get something like this: 

AddStock(string jsonString){
    var stock = JsonConvert.DeserializeObject<Stock>(jsonString);
    db.Insert(stock);
}

Upvotes: 1

Related Questions