SQLite使用笔记

一.使用Sqlite 分别添加，SQLite.Interop.dll 和　System.Data.SQLite.dll 到项目

如果要给数据库设置密码，则使用 SQLiteConnection 对象的ChangePassword(“123”);

1.创建SQLite 初始化信息

            SQLiteConnection conn = new SQLiteConnection
            ("Data Source=TestDataBase;Version=3;New=False;Compress=True;Password=123;");
            /**
             * 也可以先创建 SQLiteConnection conn = new SQLiteConnection();
             * 然后创建 SQLiteConnectionStringBuilder 指定 数据连接信息
             **/
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();

            connstr.BaseSchemaName = "TestDataBase";
            connstr.Password = "123";
            connstr.Version = 3;
            conn.ConnectionString = connstr.ToString();
            //SQLiteCommand cmd = new SQLiteCommand("select * from TestTable" ,conn);
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from TestTable";

2.创建SQLite数据库

        public static void CreateDataBase()
        {
            //CreateDataBase
            SQLiteConnection.CreateFile("TestDataBase");
        }

3.执行SQl语句，（来创建表，约束–）

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">sql语句字符串</param>
        public void ExecuteSqlStr(string sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection
            ("Data Source = TestDataBase; Version = 3; New = False; Compress = True; Password = 123; "))
            {
                if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
                {
                    //打开数据库
                    conn.Open();
                }
                using (SQLiteCommand cmd = new SQLiteCommand(sql,conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

4.执行带参数的语句查询

        /// <summary>
        /// 执行带参数的查询语句，返回SQLiteDataReader ( 注意：调用该方法后，一定要对
        SQLiteDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public SQLiteDataReader GetReader(string strSql, params SQLiteParameter[] 
        cmdParms)
        {
            using (SQLiteConnection con = new SQLiteConnection(_connectString))
            {
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand(strSql, con);
                    if (cmdParms != null)
                    {
                        foreach (SQLiteParameter param in cmdParms)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    con.Open();
                    SQLiteDataReader myReader = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    return myReader;
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }