SQLite C# 用法

1，下载官方的DLL文件 System.Data.SQLite.dll

?

2，修改微软官方的SqlHelper，打造SQLiteHelper，改造方法很简单，批量替换方式

SqlConnection=》SQLiteConnection

SqlCommand=》SQLiteCommand

SqlParameter=》SQLiteParameter

SqlDataReader=》SQLiteDataReader

SqlTransaction=》SQLiteTransaction

网上的SQLite helper 改造的烂七八糟，我们需要纯净版。

?

SQLiteHelper DB路径

public static readonly string ConnectionString = string.Format("DataSource={0}", Application.StartupPath + "/fulu");

?

3, SQLite工具 sqlite database browser(个人感觉比sqlite expert 好用)，主要作用是创建数据库，创建表，把SQL 语句复制进去，运行的时候会自动转成sqlite 语句。

?

4，操作类（和SQL一模一样）

public class OrderManage

{

public void add(long tid, string status, string seller_nick, string buyer_nick, DateTime created, DateTime pay_time, long num_iid, string title, int num, decimal price, decimal payment, string receiver_address)

{

string sql = "insert into TradeOrder(tid,status,seller_nick,buyer_nick,created,pay_time,num_iid,title,num,price,payment,receiver_address) values(@tid,@status,@seller_nick,@buyer_nick,@created,@pay_time,@num_iid,@title,@num,@price,@payment,@receiver_address)";

SQLiteParameter[] parms =

{

new SQLiteParameter("@tid",tid),

new SQLiteParameter("@status",status),

new SQLiteParameter("@seller_nick",seller_nick),

new SQLiteParameter("@buyer_nick",buyer_nick),

new SQLiteParameter("@created",created),

new SQLiteParameter("@pay_time",pay_time),

new SQLiteParameter("@num_iid",num_iid),

new SQLiteParameter("@title",title),

new SQLiteParameter("@num",num),

new SQLiteParameter("@price",price),

new SQLiteParameter("@payment",payment),

new SQLiteParameter("@receiver_address",receiver_address)

};

int r = SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms);

}

?

public void update(long tid, string status, DateTime end_time)

{

string sql = "update TradeOrder set status=@status,end_time=@end_time where tid=@tid";

SQLiteParameter[] parms =

{

new SQLiteParameter("@tid",tid),

new SQLiteParameter("@status",status),

new SQLiteParameter("@end_time",end_time)

};

int r = SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms);

}

?

public void delete(long tid)

{

string sql = "delete from TradeOrder where tid=@tid";

SQLiteParameter[] parms =

{

new SQLiteParameter("@tid",tid)

};

int r = SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms);

}

?

public DataTable select(long tid)

{

string sql = "select * from TradeOrder where tid=@tid";

SQLiteParameter[] parms =

{

new SQLiteParameter("@tid",tid)

};

DataSet ds = SQLiteHelper.ExecuteDataset(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms);

return ds.Tables[0];

}

?

public string select2(long tid)

{

string sql = "select status from TradeOrder where tid=@tid";

SQLiteParameter[] parms =

{

new SQLiteParameter("@tid",tid)

};

object obj = SQLiteHelper.ExecuteScalar(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms);

if (obj != null)

{

return obj.ToString();

}

return "";

}

?

public void select3(DateTime created, ref List<TradeOrder> orders)

{

string sql = "select * from TradeOrder where created>@created";

SQLiteParameter[] parms =

{

new SQLiteParameter("@created",created)

};

using (SQLiteDataReader dr = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionString, CommandType.Text, sql, parms))

{

while (dr.Read())

{

TradeOrder order = new TradeOrder();

order.tid = long.Parse(dr["tid"].ToString());

order.status = dr["status"].ToString();

order.seller_nick = dr["seller_nick"].ToString();

order.buyer_nick = dr["buyer_nick"].ToString();

order.seller_nick = dr["seller_nick"].ToString();

order.created = string.IsNullOrEmpty(dr["created"].ToString()) ? DateTime.MinValue : DateTime.Parse(dr["created"].ToString());

order.pay_time = dr["pay_time"] == null ? DateTime.MinValue : DateTime.Parse(dr["pay_time"].ToString());

order.end_time = string.IsNullOrEmpty(dr["end_time"].ToString()) ? DateTime.MinValue : DateTime.Parse(dr["end_time"].ToString());

order.num_iid = long.Parse(dr["num_iid"].ToString());

order.num = int.Parse(dr["num"].ToString());

order.price = decimal.Parse(dr["price"].ToString());

order.payment = decimal.Parse(dr["payment"].ToString());

order.receiver_address = dr["receiver_address"].ToString();

orders.Add(order);

}

}

}

}

?

5，业务逻辑测试

OrderManage orderMng = new OrderManage();

//add

long tid = new Random().Next(1, 100);

orderMng.add(tid, "未处理", "一福二碌", "top15from", DateTime.Now, DateTime.Now, long.Parse("15"), "CF", 1, decimal.Parse("0.95"), decimal.Parse("0.95"), "不需要收获地址");

tid = new Random().Next(1, 100);

orderMng.add(tid, "未处理", "一福二碌", "top20from", DateTime.Now, DateTime.Now, long.Parse("20"), "DNF", 2, decimal.Parse("0.95"), decimal.Parse("1.9"), "不需要收获地址");

?

//select1

DataTable dt = orderMng.select(tid);

?

//update

orderMng.update(tid, "完成", DateTime.Now);

?

//select2

string status = orderMng.select2(tid);

?

//delete

orderMng.delete(tid);

?

//select3

List<TradeOrder> orders = new List<TradeOrder>();

orderMng.select3(DateTime.Now.AddHours(-1), ref orders);