Sqlite 常用函數封裝提高Codeeer的效率

而且，我發現Sqlite中文資料比較少，起碼相對其他找起來要復雜些，服務一下大眾~
我沒有封裝讀取部分，因為數據庫讀取靈活性太大，封裝起來難度也大，而且就算封裝好了，也難以應付所有情況，還是建議根據實際情況設計代碼邏輯。

創建：
代碼如下:

/// <summary> 
/// Creat New Sqlite File 
/// </summary> 
/// <param name="NewTable">New Table Name</param> 
/// <param name="NewWords">Words list of the New Table</param> 
/// <returns>IsSuccessful</returns> 
public static bool Creat(string DataSource, string NewTable, List<string> NewWords) 
{ 
try 
{ 
//Creat Data File 
SQLiteConnection.CreateFile(DataSource); 
//Creat Table 
using (DbConnection conn = SQLiteFactory.Instance.CreateConnection()) 
{ 
//Connect 
conn.ConnectionString = "Data Source=" + DataSource; 
conn.Open(); 
//Creat 
string Bazinga = "create table [" + NewTable + "] ("; 
foreach (string Words in NewWords) 
{ 
Bazinga += "[" + Words + "] BLOB 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> 
/// 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) 
{ 
try 
{ 
//Connect 
using (DbConnection conn = SQLiteFactory.Instance.CreateConnection()) 
{ 
conn.ConnectionString = "Data Source=" + DataSource; 
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; 
} 
} 
 
插入： 
這里要說明下，因為存在多字段同時插入的情況（何止存在，很普遍- -。沒見過誰的數據庫像意大利面條一樣） 
在這里設計了Insert結構用以儲存字段和值的關系（曾考慮過用數組的辦法實現，可是那玩意不太方便調用，瞅著挺抽象的，不太好用，如果有更好的建議，歡迎留言~） 
 代碼如下:

/// <summary> 
/// Use to format Insert column's value 
/// </summary> 
public struct InsertBag 
{ 
public string ColumnName; 
public string Value; 
public InsertBag(string Column, string value) 
{ 
ColumnName = Column; 
Value = value; 
} 
} 
 
以下為插入模塊的主函數： 
 代碼如下:

/// <summary> 
/// Insert Data 
/// </summary> 
/// <param name="DataSource"></param> 
/// <param name="TargetTable"></param> 
/// <param name="InsertBags">struck of InsertBag</param> 
/// <returns></returns> 
public static bool Insert(string DataSource, string TargetTable, List<InsertBag> InsertBags) 
{ 
try 
{ 
using (DbConnection conn = SQLiteFactory.Instance.CreateConnection()) 
{ 
//Connect Database 
conn.ConnectionString = "Data Source=" + DataSource; 
conn.Open(); 
//Deal InsertBags 
StringBuilder ColumnS = new StringBuilder(); 
StringBuilder ValueS = new StringBuilder(); 
for (int i = 0; i < InsertBags.Count; i++) 
{ 
ColumnS.Append(InsertBags[i].ColumnName + ","); 
ValueS.Append("'" + InsertBags[i].Value + "',"); 
} 
if (InsertBags.Count == 0) 
{ 
throw new Exception("InsertBag 數據包為空，睜大你的狗眼……"); 
} 
else 
{ 
//Drop the last "," from the ColumnS and ValueS 
ColumnS = ColumnS.Remove(ColumnS.Length - 1, 1); 
ValueS = ValueS.Remove(ValueS.Length - 1, 1); 
} 
//Insert 
DbCommand cmd = conn.CreateCommand(); 
cmd.CommandText = "insert into [" + TargetTable + "] (" + ColumnS.ToString() + ") values (" + ValueS.ToString() + ")"; 
cmd.ExecuteNonQuery(); 
return true; 
} 
} 
catch (Exception E) 
{ 
MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 
return false; 
} 
} 
 
目測有點復雜呢，來個Demo，有必要說下，“W2”和“W44”是已經設計好的字段，而“TableTest”是已經添加好的表段 
 代碼如下:

List<Sqlite.InsertBag> Lst = new List<Sqlite.InsertBag>(); 
Lst.Add(new Sqlite.InsertBag("W2", "222222222")); 
Lst.Add(new Sqlite.InsertBag("W44", "4444444")); 
Sqlite.Insert(@"D:\1.Sql3", "TableTest", Lst); 
 
表段獲取： 
 代碼如下:

/// <summary> 
/// Get Tables From Sqlite 
/// </summary> 
/// <returns>list of Tables</returns> 
public static List<string> GetTables(string DataSource) 
{ 
List<string> ResultLst = new List<string>(); 
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + DataSource)) 
{ 
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); 
} 
} 
} 
} 
} 
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) 
{ 
List<string> WordsLst = new List<string>(); 
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + DataSource)) 
{ 
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; 
} 
 
解釋下，為啥代碼中的注釋基本都用英文寫了，因為這段時間在學雙拼- -。可是還不太熟悉，打字超慢，而且Code的時候容易打斷思路，好在~英文不多，而且這些都看不懂的話你……你要向我解釋一下你是怎么一路學到數據庫的 0。
 
 




聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com