服务器之家:专注于服务器技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|JAVA教程|ASP教程|

服务器之家 - 编程语言 - ASP.NET教程 - Sqlite 常用函数封装提高Codeeer的效率

Sqlite 常用函数封装提高Codeeer的效率

2019-10-14 11:54asp.net教程网 ASP.NET教程

以下是频繁用到的Sqlite函数,内容格式相对固定,封装一下有助于提高开发效率^_^至少提高Codeeer的效率了

以下是频繁用到的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。

延伸 · 阅读

精彩推荐