SQLite C # Usage

1. Download the official DLL file System.Data.SQLite.dll

?

2, modify Microsoft Official SqlHelper, build Sqlitehelper, transformation method is very simple, batch replacement way

Sqlconnection= "Sqliteconnection

Sqlcommand= "Sqlitecommand

Sqlparameter= "Sqliteparameter

Sqldatareader= "Sqlitedatareader

Sqltransaction= "Sqlitetransaction

Online SQLite helper Makeover rotten seven or eight bad, we need pure version.

?

Sqlitehelper DB Path

public static readonly String ConnectionString = String. Format ("datasource={0}", Application.startuppath + "/fulu");

?

3, SQLite Tools SQLite Database Browser (personal feeling than SQLite expert), the main role is to create a database, create a table, SQL statements copied into the runtime will automatically turn into SQLite statements.

?

4, Operation class (identical to 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, Business logic test

Ordermanage ordermng = new Ordermanage ();

Add

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

Ordermng.add (TID, "Unhandled", "one blessing Two," "Top15from", DateTime.Now, DateTime.Now, long. Parse ("the"), "CF", 1, Decimal. Parse ("0.95"), decimal. Parse ("0.95"), "No need to harvest address");

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

Ordermng.add (TID, "Unhandled", "one blessing Two," "Top20from", DateTime.Now, DateTime.Now, long. Parse ("a"), "DNF", 2, Decimal. Parse ("0.95"), decimal. Parse ("1.9"), "No need to harvest address");

?

Select1

DataTable dt = Ordermng.select (TID);

?

Update

Ordermng.update (Tid, "done", 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 # Usage