using System;

using System.Data.SQLite;

namespace SQLiteSamples

{

    class Program

    {

        //数据库连接

        SQLiteConnection m_dbConnection;

        static void Main(string[] args)

        {

            Program p = new Program();

        }

        public Program()

        {

            createNewDatabase();

            connectToDatabase();

            createTable();

            fillTable();

            printHighscores();

        }

        //创建一个空的数据库

        void createNewDatabase()

        {

            SQLiteConnection.CreateFile("MyDatabase");

     //默认生成的数据库文件和System.Data.SQLite.dll在同一路径下

        }

        //创建一个连接到指定数据库

        void connectToDatabase()

        {

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

            m_dbConnection.Open();

        }

        //在指定数据库中创建一个table

        void createTable()

        {

            string sql = "create table highscores (name varchar(20), score int)";

            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

            command.ExecuteNonQuery();

        }

        //插入一些数据

        void fillTable()

        {

            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();

        }

        //使用sql查询语句，并显示结果

        void printHighscores()

        {

            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"]);

            Console.ReadLine();

        }

    }

}