SQLite C# 用法

標籤：style   io   ar   os   sp   for   on   檔案   資料   

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 [email protected],[email protected]_time where [email protected]";

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 [email protected]";

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 [email protected]";

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 [email protected]";

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);

SQLite C# 用法