SQLiteSample.cs

gistfile1.txt

public bool OpenConnection(string dbFalieName)
{
    if (!File.Exists(dbFalieName))
    {
        SQLiteConnection.CreateFile(dbFalieName);
        connection = new SQLiteConnection("Data Source=" + dbFalieName + ";Version=3;");
        InitTables();
    }
    else
    {
        connection = new SQLiteConnection("Data Source=" + dbFalieName + ";Version=3;");
    }

    return true;
}

private void InitTables()
{
    string sql = @"CREATE TABLE Students (
                  'Id'	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
                  'Name'	TEXT NOT NULL,
                  'Score'	REAL,
                  'City'	TEXT
                 );";

    connection.Open();
    using (cmd = new SQLiteCommand(sql, connection))
    {
        cmd.ExecuteNonQuery();
    }
    connection.Close();
}

public List<Student> GetAll()
{
    List<Student> retVal = new List<Student>();

    using (cmd = new SQLiteCommand("SELECT * FROM Students", connection))
    {
        connection.Open();
        using (dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                retVal.Add(new Student()
                {
                    Id = dr.GetInt32(0),
                    Name = dr.GetString(1),
                    Score = dr.GetFloat(2),
                    City = dr.GetString(3)
                });

            }
        }
        connection.Close();
    }

    return retVal;
}

public void Add(Student item)
{
    using (cmd = new SQLiteCommand(@"INSERT INTO Students('Name','Score','City') 
                                                   VALUES(@name, @score, @city);", connection))
    {
        connection.Open();

        cmd.Parameters.Add(new SQLiteParameter("name", item.Name));
        cmd.Parameters.Add(new SQLiteParameter("score", item.Score));
        cmd.Parameters.Add(new SQLiteParameter("city", item.City));
        cmd.ExecuteNonQuery();

        connection.Close();
    }

}