以下是頻繁用到的Sqlite函數(shù)，內(nèi)容格式相對(duì)固定，封裝一下有助于提高開發(fā)效率（^_^至少提高Codeeer的效率了）
而且，我發(fā)現(xiàn)Sqlite中文資料比較少，起碼相對(duì)其他找起來(lái)要復(fù)雜些，服務(wù)一下大眾~
我沒有封裝讀取部分，因?yàn)閿?shù)據(jù)庫(kù)讀取靈活性太大，封裝起來(lái)難度也大，而且就算封裝好了，也難以應(yīng)付所有情況，還是建議根據(jù)實(shí)際情況設(shè)計(jì)代碼邏輯。
創(chuàng)建：
復(fù)制代碼 代碼如下:
/// 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, Liststring> 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;
}
}
刪除：
復(fù)制代碼 代碼如下:
/// 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;
}
}
插入：
這里要說(shuō)明下，因?yàn)榇嬖诙嘧侄瓮瑫r(shí)插入的情況（何止存在，很普遍- -。沒見過(guò)誰(shuí)的數(shù)據(jù)庫(kù)像意大利面條一樣）
在這里設(shè)計(jì)了Insert結(jié)構(gòu)用以儲(chǔ)存字段和值的關(guān)系（曾考慮過(guò)用數(shù)組的辦法實(shí)現(xiàn)，可是那玩意不太方便調(diào)用，瞅著挺抽象的，不太好用，如果有更好的建議，歡迎留言~）
復(fù)制代碼 代碼如下:
/// 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;
}
}
以下為插入模塊的主函數(shù)：
復(fù)制代碼 代碼如下:
/// 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, ListInsertBag> 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 數(shù)據(jù)包為空，睜大你的狗眼……");
}
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;
}
}
目測(cè)有點(diǎn)復(fù)雜呢，來(lái)個(gè)Demo，有必要說(shuō)下，“W2”和“W44”是已經(jīng)設(shè)計(jì)好的字段，而“TableTest”是已經(jīng)添加好的表段
復(fù)制代碼 代碼如下:
ListSqlite.InsertBag> Lst = new ListSqlite.InsertBag>();
Lst.Add(new Sqlite.InsertBag("W2", "222222222"));
Lst.Add(new Sqlite.InsertBag("W44", "4444444"));
Sqlite.Insert(@"D:\1.Sql3", "TableTest", Lst);
表段獲取：
復(fù)制代碼 代碼如下:
/// summary>
/// Get Tables From Sqlite
/// /summary>
/// returns>list of Tables/returns>
public static Liststring> GetTables(string DataSource)
{
Liststring> ResultLst = new Liststring>();
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;
}
字段獲取：
復(fù)制代碼 代碼如下:
/// summary>
/// Get Words From Table->Sqlite
/// /summary>
/// param name="TargetTable">Target Table/param>
/// returns>list of Words/returns>
public static Liststring> GetWords(string DataSource,string TargetTable)
{
Liststring> WordsLst = new Liststring>();
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;
}
解釋下，為啥代碼中的注釋基本都用英文寫了，因?yàn)檫@段時(shí)間在學(xué)雙拼- -。可是還不太熟悉，打字超慢，而且Code的時(shí)候容易打斷思路，好在~英文不多，而且這些都看不懂的話你……你要向我解釋一下你是怎么一路學(xué)到數(shù)據(jù)庫(kù)的 0。
您可能感興趣的文章:- 讓Sqlite脫離VC++ Runtime獨(dú)立運(yùn)行的方法
- C++操作SQLite簡(jiǎn)明教程
- SQLite 入門教程三 好多約束 Constraints
- C#中使用SQLite數(shù)據(jù)庫(kù)的方法介紹
- ASP.NET(C#)中操作SQLite數(shù)據(jù)庫(kù)實(shí)例
- VC++基于Dx實(shí)現(xiàn)的截圖程序示例代碼
- VC++實(shí)現(xiàn)輸出GIF到窗體并顯示GIF動(dòng)畫的方法
- VC++開發(fā)中完美解決頭文件相互包含問(wèn)題的方法解析
- 淺析VC++中的頭文件包含問(wèn)題
- VC++操作SQLite簡(jiǎn)單實(shí)例
