How to use(create db, create table, query, etc) praeclarum sqlite-net?

Question

I want to use sqlite-net available from this link https://github.com/praeclarum/sqlite-net.

Unfortunately, the getting started documentation are not enough. It doesnt even mention how to create a database. I tried looking at the examples, unfortunately, the examples are broken(unable to compile, run time error etc).

The most practical tutorial i can find on the net is http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

Unfortunately, sqlite-net doesnt fully support sqlite.org sqlite implementation, thus making the tutorial useless for praeclarum sqlite-net.

What is the equivalent method to do the same thing from the tutorial but in praeclarum sqlite-net?

From the tutorial

Create database(Here is where i stuck)

SQLiteConnection.CreateFile("MyDatabase.sqlite");

Connect to database

SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();

Create table

string sql = "create table highscores (name varchar(20), score int)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

Fill table

string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

Query database

string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
    Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);

Solution 1

In your Where you can use lambdas. The classes are strongly typed.

Makes things a lot cleaner.

If you get into any amount of data caching, you'll end up wishing you had something like Microsoft's sync framework to use in Mono. I'm really guessing by your post, that you are looking at using Xamarin. Take a look at their SQLCipher component, if you are going to be caching data locally.

Also, if you do use SQLCipher via the component store.. it works in Android 2.3 on up. So don't expect a fully backward compatible system even with the Support Library added to your project.

var db = new SQLiteConnection("sqllite.db")

db.CreateTable<SyncRecord> ();

db.Insert (new SyncRecord () { SyncDate = DateTime.UtcNow });

var query = db.Table<SyncRecord> ().Where( /* your lambda to filter*/);

Solution 2

My suggested answer is based on @Slack-Shot response.

I try to convert the tutorial to be praeclarum sqlite syntax compatible for reference to other super noobs like me.

Create and/or connect to database

private string dbPath = System.IO.Path.Combine
    (System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location),
     "MyDatabase.sqlite");

using (var m_dbConnection = new SQLite.SQLiteConnection(dbPath)) {}

Create table

public class highscore
{
    [MaxLength(20)]
    public string name { get; set; }
    public int score { get; set; }
}

using (var m_dbConnection = new SQLite.SQLiteConnection(dbPath))
{
    m_dbConnection.CreateTable<highscore>();
}

Fill table

using (var m_dbConnection = new SQLite.SQLiteConnection(dbPath))
{
    m_dbConnection.Insert(new highscore()
    {
        name = "Me",
        score = 9001
    });
    m_dbConnection.Insert(new highscore()
    {
        name = "Me",
        score = 3000
    });
    m_dbConnection.Insert(new highscore()
    {
        name = "Myself",
        score = 6000
    });
    m_dbConnection.Insert(new highscore()
    {
        name = "And I",
        score = 9001
    });
}

Query database

Assuming I have a simple SQL string like so: "select * from highscores order by score desc"

How do I display it in this form:

for(int i = 0; i < totalDataQueried; i++)
    Console.WriteLine("Name: " + name[i] + "\tScore: " + score[i]);

Comments

[Syaiful Nizam Yahya]

Thanks. The syntax are totally different. Save a lot of my time.

[Noctis]

first, read a short 3 min intro to lambda expressions. then, what do you need / want to query next?

[Syaiful Nizam Yahya]

Accepted. Could you help me on general SQL queries on my suggested answer? Thanks.

[Slack Shot]

var items = m_dbConnection.Table<highscore>().OrderByDescending( t => t.score); That's a lambda expression that will order by descending.

[Slack Shot]

Then, you can use something like.. foreach(highscore hs in items) Console.WriteLine("Name: "+hs.name+"\tScore:"+hs.score);

[PositiveGuy]

I don't see a CreateTable method, I'm using a SQLiteConnection object, am I doing something wrong?

[Slack Shot]

@CoffeeAddict, are you using SQLLiteNet?