Sqlite封装代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 Sqlite封装
作者:webxiaohua / 发布于2014/7/17/ 470
封装函数如下：修改密码，创建、查询、修改、删除：库、表、字段和直接命令执行

	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;
	 }
	 }
	}
试试其它关键字
　Sqlite　
同语言下
可能有用的
webxiaohua贡献的其它代码(2)