* 輕巧靈活，基于單個數(shù)據(jù)庫文件，方便創(chuàng)建、移動，能方便的進行數(shù)據(jù)的分類存儲(按業(yè)務、按功能、按日期、按客戶等)，非常適合與靈活的系統(tǒng)搭配使用。

* 部署容易，.NET環(huán)境只需一個dll文件(Access可能需要安裝MDAC)。

* 開源。

SQLite開發(fā)環(huán)境與開發(fā)資料的準備

.Net SQLite數(shù)據(jù)庫驅動：System.Data.SQLite.dll以及文檔

SQLite GUI數(shù)據(jù)庫工具：SQLite Expert PersonalEdition（個人版免費，夠用了）

.Net SQLite數(shù)據(jù)庫驅動源碼：sqlite-netFx-source-1.0.76.0

C#動態(tài)創(chuàng)建SQLite數(shù)據(jù)庫

調用SQLiteConnection. CreateFile靜態(tài)方法創(chuàng)建

SQLiteConnection.CreateFile(path)

path測試路徑：Data目錄存在，Data2目錄不存在

                var path1 = "dummy1.db";//成功

                var path2 = "Data/dummy2.db";//成功

                var path3 = "Data\\dummy3.db";//成功

                var path4 = "c:\\Data\\dummy4.db";//成功

                var path5 = "Data2//dummy5.db";//錯誤，Data2目錄不存在

           var path6 = "C:\\Data2\\dummy6.db";//錯誤，Data2目錄不存在

SQLiteConnection. CreateFile內部的實現(xiàn)源碼：使用了File.Create創(chuàng)建0字節(jié)文件

    /// <summary>

    /// Creates a database file.  This just creates a zero-byte file whichSQLite

    /// will turn into a database when the file is openedproperly.

    /// </summary>

    /// <paramname="databaseFileName">Thefile to create</param>

    static public void CreateFile(string databaseFileName)

    {

      FileStream fs = File.Create(databaseFileName);

      fs.Close();

    }

C#連接SQLite與數(shù)據(jù)庫安全

常用的連接字符串生成

                /// <summary>

                /// 生成SQLite連接字符串

                /// </summary>

                public static class SQLiteConnectionString

                {

                                public static string GetConnectionString(stringpath)

                                {

                                                return GetConnectionString(path,null);

                                }

                                public static string GetConnectionString(stringpath, string password)

                                {

                                                if (string.IsNullOrEmpty(password))

                                                                return "Data Source=" + path;

                                                return "Data Source=" + path + ";Password=" + password;

                                }

                }

修改數(shù)據(jù)庫密碼

                /// <summary>

                /// 修改密碼

                /// </summary>

                /// <paramname="path"></param>

                /// <param name="newPassword"></param>

                /// <param name="oldPassword">沒有密碼時為空</param>

                public static boolChangePassword(stringpath, string newPassword,string oldPassword = null)

                {

                                try

                                {

                                                varcon = new SQLiteConnection(SQLiteConnectionString.GetConnectionString(path, oldPassword));

                                                con.Open();

                                                con.ChangePassword(newPassword);

                                                con.Close();

                                }

                                catch (Exception ex)

                                {

                                                return false;

                                }

                                return true;

                }

修改密碼測試：

                var path2 = "Data/dummy2.db";//數(shù)據(jù)庫初始密碼是空值

                ChangePassword(path2, "123");//成功，數(shù)據(jù)庫密碼修改為123

                ChangePassword(path2, "234","");//錯誤，當前密碼是123

                ChangePassword(path2, "234","123");//成功，數(shù)據(jù)庫密碼修改為123

                ChangePassword(path2, "","234");//成功，數(shù)據(jù)庫密碼修改為空

C#常用類型與SQLite類型的映射

*上述類型映射不是唯一的；已進行寫讀的測試。

C#對SQLite數(shù)據(jù)庫插入記錄并返回自動遞增ID

SQLite設置自動遞增列：創(chuàng)建INTEGER數(shù)據(jù)類型的列，設置該列為主鍵，然后才能設置自動遞增屬性。

執(zhí)行插入的sql后，調用last_insert_rowid()函數(shù)返回自動遞增ID

                object result = null;

                IDbConnection conn = null;

                IDbCommand cmd = null;

                //略…

                inti = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                if (i> 0)

                {

                                cmd.CommandText = "SELECT last_insert_rowid()";

                                result = cmd.ExecuteScalar();

                }

                else

                {

                                result = -1;

                }

                //略…

C#下的SQLite事務控制

已在TransactionScope管道下測試事務，在內部引發(fā)異常，事務能夠終止。

                try

                {

                                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())

                                {

                                                //引發(fā)異常

                                                scope.Complete();

                                }

                }

                catch (Exception)

                {

                }