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。