C#SQLite数据库操作封装

public class SQLiteDatabase {// Fieldsprivate System.Data.SQLite.SQLiteConnection m_Connection;private string m_strFileName;// Methodspublic SQLiteDatabase(string strFileName, string strPassword, bool bReadOnly &＃61; false, bool b加载数据到内存 &＃61; false){if (b加载数据到内存 &＃61;&＃61; false){m_strFileName &＃61; strFileName;if (bReadOnly){try{this.OpenReadOnly(strFileName, strPassword);}catch{this.OpenReadWrite(strFileName, strPassword);}}else{this.OpenReadWrite(strFileName, strPassword);}}else{m_Connection &＃61; new System.Data.SQLite.SQLiteConnection("Data Source&＃61;:memory:");m_Connection.Open();}}public string FileName{get{return m_strFileName;}}public static void CreateDBFile(string strFileName){try{if (System.IO.File.Exists(strFileName)) System.IO.File.Delete(strFileName);System.Data.SQLite.SQLiteConnection.CreateFile(strFileName);}catch{}}private void ChangeNewConnection(System.Data.SQLite.SQLiteConnection m_NewConnection){m_Connection.Clone();m_Connection &＃61; m_NewConnection;}public void CloseConnection(){m_Connection.ReleaseMemory();m_Connection.Close();}public void ExecuteSQL(string strSQL){ System.Data.SQLite.SQLiteCommand cmd &＃61; new System.Data.SQLite.SQLiteCommand(null, m_Connection);cmd.CommandType &＃61; System.Data.CommandType.Text;cmd.CommandText &＃61; strSQL;cmd.ExecuteNonQuery();cmd.Dispose();}public void ExecuteSQL(List listSql)//, System.Data.SQLite.SQLiteTransaction tran)
{System.Data.SQLite.SQLiteCommand cmd &＃61; new System.Data.SQLite.SQLiteCommand(null, m_Connection);cmd.Transaction &＃61; m_Connection.BeginTransaction(); ;// tran;foreach (string strSQL in listSql){cmd.CommandType &＃61; CommandType.Text;cmd.CommandText &＃61; strSQL;cmd.ExecuteNonQuery();}cmd.Transaction.Commit();cmd.Dispose();}public bool FieldExists(string strTableName, string strFieldName){DataTable table &＃61; this.GetTableBySQL(String.Format("select * from &＃39;{0}&＃39; limit 0,1", strTableName));if (table.Columns.Contains(strFieldName))return true;return false;}public int GetLastID(){System.Data.SQLite.SQLiteCommand command &＃61; new System.Data.SQLite.SQLiteCommand(null, m_Connection){CommandType &＃61; CommandType.Text,CommandText &＃61; "select last_insert_rowid()"};return Convert.ToInt32(command.ExecuteScalar());}public DataTable GetTableBySQL(string strSQL, bool bAddWithKey &＃61; false){System.Data.SQLite.SQLiteCommand selectCommand &＃61; new System.Data.SQLite.SQLiteCommand(null, m_Connection){CommandType &＃61; CommandType.Text,CommandText &＃61; strSQL};System.Data.SQLite.SQLiteDataAdapter adapter &＃61; new System.Data.SQLite.SQLiteDataAdapter(selectCommand);if (bAddWithKey) adapter.MissingSchemaAction &＃61; MissingSchemaAction.AddWithKey;DataTable dataTable &＃61; new DataTable();adapter.Fill(dataTable);return dataTable;}public List<string> GetTableName(){List<string> list &＃61; new List<string>();foreach (DataRow row in this.GetTableBySQL("select * from sqlite_master where type&＃61;&＃39;table&＃39;").Rows){list.Add(row["name"].ToString().ToUpper());}return list;}public System.Data.Common.DbCommand NewCommand(string strCommandText){return new System.Data.SQLite.SQLiteCommand(strCommandText, m_Connection);}public void OpenConnection(){m_Connection.Open();}private void OpenReadOnly(string strFileName, string strPassword){m_Connection &＃61; new System.Data.SQLite.SQLiteConnection();System.Data.SQLite.SQLiteConnectionStringBuilder connstr &＃61; new System.Data.SQLite.SQLiteConnectionStringBuilder();connstr.DataSource &＃61; strFileName;connstr.Password &＃61; strPassword;//设置密码&＃xff0c;SQLite ADO.NET实现了数据库密码保护connstr.ReadOnly &＃61; true;m_Connection.ConnectionString &＃61; connstr.ToString();m_Connection.Open();}private void OpenReadWrite(string strFileName, string strPassword){m_Connection &＃61; new System.Data.SQLite.SQLiteConnection();System.Data.SQLite.SQLiteConnectionStringBuilder connstr &＃61; new System.Data.SQLite.SQLiteConnectionStringBuilder();connstr.DataSource &＃61; strFileName;connstr.Password &＃61; strPassword;connstr.ReadOnly &＃61; false;m_Connection.ConnectionString &＃61; connstr.ToString();m_Connection.Open();}public System.Data.SQLite.SQLiteTransaction BeginTransaction(){return m_Connection.BeginTransaction();}public void ExecuteNonQueryTransaction(string sql, List listSQLiteParameter, System.Data.SQLite.SQLiteTransaction targetTransaction, int commandTimeout &＃61; 600){System.Data.SQLite.SQLiteCommand sqliteCommand &＃61; new System.Data.SQLite.SQLiteCommand(sql, m_Connection, targetTransaction);sqliteCommand.CommandTimeout &＃61; commandTimeout;sqliteCommand.Parameters.AddRange(listSQLiteParameter.ToArray());sqliteCommand.ExecuteNonQuery();sqliteCommand.Dispose();}public DataRow GetRowBySQL(string strSQL){DataTable dTable &＃61; GetTableBySQL(strSQL);if (dTable.Rows.Count &＃61;&＃61; 0)return null;elsereturn dTable.Rows[0];}public bool TableExists(string strTableName){List<string> listTableName &＃61; GetTableName();foreach (string s in listTableName){if (string.Compare(s, strTableName, true) &＃61;&＃61; 0)return true;}return false;}public DataTable GetColumnTable(string strTableName){throw new NotImplementedException();}//备份数据库到另一个连接public Truelore.Common2.Database.SQLiteDatabase CreateNewMemeoyBackupDatabase(){Truelore.Common2.Database.SQLiteDatabase newSQLiteDatabase &＃61; new Truelore.Common2.Database.SQLiteDatabase("", "", false, true); System.Data.SQLite.SQLiteConnection m_ConnectionInMemeoy &＃61; new System.Data.SQLite.SQLiteConnection("Data Source&＃61;:memory:");m_ConnectionInMemeoy.Open();m_Connection.BackupDatabase(m_ConnectionInMemeoy, "main", "main", -1, null, 0);newSQLiteDatabase.ChangeNewConnection(m_ConnectionInMemeoy);return newSQLiteDatabase;}}