- public static class SuperSqlite
- {
- /// <summary>
- /// Change Password
- /// </summary>
- /// <param name="DataSource"></param>
- /// <param name="OldPassword"></param>
- /// <param name="NewPassword"></param>
- /// <returns></returns>
- public static bool PasswordChange(string DataSource, string OldPassword, string NewPassword)
- {
- try
- {
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = OldPassword;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- //Change Password
- conn.ChangePassword(NewPassword);
- }
- return true;
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return false;
- }
- }
- /// <summary>
- /// Creat New Sqlite File
- /// </summary>
- /// <param name="NewTable">New Table Name</param>
- /// <param name="NewWords">Words list of the New Table</param>
- /// <param name="CreatNew">Creat or Add to the Database(Using to make Multi Tables)</param>
- /// <returns></returns>
- public static bool Creat(string DataSource, string NewTable, List<string> NewWords, string PassWord = null, bool CreatNew = true)
- {
- try
- {
- if (CreatNew)
- {
- //Creat Data File
- SQLiteConnection.CreateFile(DataSource);
- }
- //Creat Table
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- //Creat
- string Bazinga = "create table [" + NewTable + "] (";
- foreach (string Words in NewWords)
- {
- Bazinga += "[" + Words + "] TEXT COLLATE NOCASE,";
- }
- //Set Primary Key
- //The Top item from the "NewWords"
- Bazinga += @"PRIMARY KEY ([" + NewWords[0] + "]))";
- DbCommand cmd = conn.CreateCommand();
- cmd.Connection = conn;
- cmd.CommandText = Bazinga;
- cmd.ExecuteNonQuery();
- }
- return true;
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return false;
- }
- }
- /// <summary>
- /// Get Tables From Sqlite
- /// </summary>
- /// <returns>list of Tables</returns>
- public static List<string> GetTables(string DataSource, string PassWord = null)
- {
- List<string> ResultLst = new List<string>();
- try
- {
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- using (SQLiteCommand tablesGet = new SQLiteCommand("SELECT name from sqlite_master where type='table'", conn))
- {
- using (SQLiteDataReader tables = tablesGet.ExecuteReader())
- {
- while (tables.Read())
- {
- try
- {
- ResultLst.Add(tables[0].ToString());
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
- }
- }
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- return ResultLst;
- }
- /// <summary>
- /// Get Words From Table->Sqlite
- /// </summary>
- /// <param name="TargetTable">Target Table</param>
- /// <returns>list of Words</returns>
- public static List<string> GetWords(string DataSource, string TargetTable, string PassWord = null)
- {
- List<string> WordsLst = new List<string>();
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- using (SQLiteCommand tablesGet = new SQLiteCommand(@"SELECT * FROM '" + TargetTable + "'", conn))
- {
- using (SQLiteDataReader Words = tablesGet.ExecuteReader())
- {
- try
- {
- for (int i = 0; i < Words.FieldCount; i++)
- {
- WordsLst.Add(Words.GetName(i));
- }
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
- }
- return WordsLst;
- }
- /// <summary>
- /// Get Values From Sqlite
- /// </summary>
- /// <returns>list of Values</returns>
- public static List<string> GetValues(string DataSource, string Sql, string GetColumu, string PassWord = null)
- {
- List<string> ResultLst = new List<string>();
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- using (SQLiteCommand cmd = new SQLiteCommand(Sql, conn))
- {
- using (SQLiteDataReader dr = cmd.ExecuteReader())
- {
- while (dr.Read())
- {
- try
- {
- ResultLst.Add(dr[GetColumu].ToString());
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- }
- }
- }
- }
- }
- return ResultLst;
- }
- /// <summary>
- /// Insert Data
- /// ValueS的字符串要把'分号加在两端
- /// </summary>
- /// <param name="DataSource"></param>
- /// <param name="TargetTable"></param>
- /// <returns></returns>
- public static void Insert(string DataSource, string TargetTable, string ColumnS, string ValueS, string PassWord = null)
- {
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- //Insert
- DbCommand cmd = conn.CreateCommand();
- cmd.CommandText = "insert into [" + TargetTable + "] (" + ColumnS + ") values (" + ValueS + ")";
- cmd.ExecuteNonQuery();
- }
- }
- /// <summary>
- /// Delete Date
- /// </summary>
- /// <param name="DataSource"></param>
- /// <param name="TargetTable"></param>
- /// <param name="Word"></param>
- /// <param name="Value"></param>
- /// <returns></returns>
- public static bool Delete(string DataSource, string TargetTable, string Word, string Value, string PassWord = null)
- {
- try
- {
- //Connect
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- DbCommand cmd = conn.CreateCommand();
- cmd.Connection = conn;
- //Delete
- cmd.CommandText = "Delete From '" + TargetTable + "' where [" + Word + "] = '" + Value + "'";
- cmd.ExecuteNonQuery();
- }
- return true;
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return false;
- }
- }
- /// <summary>
- /// SQLiteCommand
- /// </summary>
- /// <param name="DataSource"></param>
- /// <param name="Sql"></param>
- /// <returns></returns>
- public static bool SqlCommand(string DataSource, string Sql, string PassWord = null)
- {
- try
- {
- using (SQLiteConnection conn = new SQLiteConnection())
- {
- SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
- SCS.DataSource = DataSource;
- SCS.Password = PassWord;
- conn.ConnectionString = SCS.ToString();
- conn.Open();
- using (SQLiteCommand cmd_Re = new SQLiteCommand(Sql, conn))
- {
- cmd_Re.ExecuteNonQuery();
- }
- }
- return true;
- }
- catch (Exception E)
- {
- MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return false;
- }
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/2601201614495.html
来源: http://www.codesnippet.cn/detail/2601201614495.html