Use transactions to operate SQLite databases and some common SQL statements

Use the begintransaction () method of sqlitedatabase to start a transaction. When the program runs to the endtransaction () method, it checks whether the transaction flag is successful. If the program runs to the endtransaction () method () previously, the settransactionsuccessful () method was called to set the transaction flag to successful, then the transaction is committed. If the settransactionsuccessful () method is not called, the transaction is rolled back. Example: sqlitedatabase DB = ....;
DB. begintransaction (); // start the transaction
Try {
Db.exe csql ("insert into person (name, age) values (?,?) ", New object [] {" Chuanzhi podcast ", 4 });
Db.exe csql ("Update person set name =? Where personid =? ", New object [] {" Chuanzhi ", 1 });
DB. settransactionsuccessful (); // call this method to submit the current transaction when it is executed to endtransaction (). If this method is not called, the transaction will be rolled back.
} Finally {
DB. endtransaction (); // The transaction identifier determines whether to commit or roll back the transaction.
}
DB. Close ();
The preceding two SQL statements are executed in the same transaction.

Some common SQL statements:

Create Table person (_ id integer primary key autoincrement, name varchar (20 ))
SQLite can parse most standard SQL statements, such:
Query statement: Select * from table name where Condition Clause group by grouping clause having... order by sorting clause
For example, select * From person
Select * From person order by ID DESC
Select name from person group by name having count (*)> 1
Paging SQL is similar to MySQL. The following SQL statement obtains five records and skips the first three records.
Select * from account limit 5 offset 3 or select * from account limit 3, 5
Insert statement: insert into Table Name (Field List) values (Value List ). For example, insert into person (name, age) values ('chuanzhi ', 3)
Update statement: Update table name: Set field name = value: where Condition Clause. For example, update person set name = 'chuanzhi 'Where id = 10
Delete statement: delete from table name where Condition Clause. For example, delete from person where id = 10

Create a database table

Db.exe csql ("create table person (_ id integer primary key autoincrement, name varchar (20), amount integer)"); // execute an SQL statement with changes

Delete Database

Db.exe csql ("Drop table if exists person ");

Insert a data entry

Db.exe csql ("insert into person (name, amount) values (?,?) ", New object [] {person. getname (), person. getamount ()});

Update a piece of data

Db.exe csql ("Update person set name =? Where _ id =? ", New object [] {person. getname (), person. GETID ()});

Delete a data entry

Db.exe csql ("delete from person where _ id =? ", New object [] {ID. tostring ()});

Query a piece of data

Cursor cursor = dB. rawquery ("select * From person where _ id =? ", New string [] {ID. tostring ()});

Paging

Cursor cursor = dB. rawquery ("select * From person limit ?,? ", New string [] {offset. tostring (), maxresult. tostring ()});

Return cursor

DB. rawquery ("select _ id as _ id, name, amount from person limit ?,? ", New string [] {offset. tostring (), maxresult. tostring ()});

Total number

DB. rawquery ("select count (*) from person", null );