SQLite笔记

一、SQLite下载：

http://www.sqlite.org/download.html　（或在NuGet下载安装）

二、SQLite操作：

　　1、添加引用System.Data.SQLite，如安装目录在E:\Program Files\System.Data.SQLite\2010\bin，则找到System.Data.SQLite.dll引用到当前项目中；

using System.Data.SQLite;

　　2、进行简单增删改查操作，语法跟sql server相差不大

 public class UseSQLIte
    {
        SQLiteConnection m_dbConnection;
        public UseSQLIte()
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            ShowInfo();
        }

        //创建一个空的数据库
        void createNewDatabase()
        {
            SQLiteConnection.CreateFile("SqliteDemo");
        }

        //建立连接
        bool connectToDatabase()
        {
            try
            {
                m_dbConnection = new SQLiteConnection("Data Source=SqliteDemo;Version=3;");
                m_dbConnection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }

        //创建表 
        void createTable()
        {
            string sql = "create table OnePiece(name VARCHAR(20), Reward BIGINT)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }

        //插入数据
        void fillTable()
        {
            string sql = "insert into OnePiece (name, Reward) values ('路飞', 5000000000)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

            sql = "insert into OnePiece (name, Reward) values ('索隆', 3000000000)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

            sql = "insert into OnePiece (name, Reward) values ('山治', 2000000000)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

            sql = "insert into OnePiece (name, Reward) values ('乔巴', 100)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }

        //查询语句，并显示结果
        void ShowInfo()
        {
            string sql = "select * from OnePiece order by Reward desc";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                    Console.WriteLine("姓名: " + reader["name"] + "\t赏金: " + reader["Reward"]);
            }
            Console.ReadLine();
        }

        bool check(string tableName)
        {
            string sql = "select count(*) from sqlite_master where type='table' and name ='" + tableName + "'";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            int i = Convert.ToInt32(command.ExecuteScalar());
            return i > 0;
        }
    }