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