Add, delete, modify, and query IOS sqlite Databases

Add, delete, modify, and query IOS sqlite Databases
1. Introduction: simple encapsulation of the sqlite database operation class BaseDB is used to add, delete, modify, and query sqlite. before using it, import libsqlite3.0.dylib library 2. BaseDB. h.

//// BaseDB. h // SqliteDemo /// Created by Zhao Chao on 14-8-26. // Copyright (c) 2014 Zhao Chao. all rights reserved. // # import <Foundation/Foundation. h> # import "sqlite3.h" @ interface BaseDB: NSObject/*** create a table * SQL: executed SQL statement * dataName: database name */-(void) createTable :( NSString *) SQL dataBaseName :( NSString *) dataName;/*** run the SQL statement to add, modify, and delete the SQL statement * params: * dataName: database name */-(BOOL) execSql :( NSString *) SQL parmas :( NSArray *) params dataBaseName :( NSString *) dataName; /*** select data ** SQL: The queried SQL statement * params: query the parameter * dataName: query database name */-(NSMutableArray *) selectSql :( NSString *) SQL parmas :( NSArray *) params dataBaseName :( NSString *) dataName; @ end

The created database file is located at/Users/zhaochao/Library/Application Support/iPhone Simulator/7.1/Applications/07D17328-B63C-4D87-9B6C-03AA5CD681EA/Documents/zhaochao. sqlite is the NSString * fileName = [NSHomeDirectory () stringByAppendingFormat: @ "/Documents/% @", name]; directory. The file can be opened directly with the SQLiteManager software, or the sqlitemanager plug-in can be installed in firefox, as shown in
3. BaseDB. m

//// BaseDB. m // SqliteDemo /// Created by Zhao Chao on 14-8-26. // Copyright (c) 2014 Zhao Chao. all rights reserved. // # import "BaseDB. h "@ implementation BaseDB/** get the sandbox directory * name: append directory aa **/-(NSString *) DataBaseName :( NSString *) name {NSString * fileName = [NSHomeDirectory () stringByAppendingFormat: @ "/Documents/% @", name]; return fileName;}/*** select data * SQL: query SQL statement * params: query the parameter * dataName: query database name */-(NSMut AbleArray *) selectSql :( NSString *) SQL parmas :( NSArray *) params dataBaseName :( NSString *) dataName {sqlite3 * sqlite = nil; sqlite3_stmt * stmt = nil; // open the database NSString * fileName = [self DataBaseName: dataName]; int result = sqlite3_open ([fileName UTF8String], & sqlite); if (result! = SQLITE_ OK) {NSLog (@ "failed to open"); return nil;} const char * sqlCh = [SQL UTF8String]; // compile the SQL statement sqlite3_prepare_v2 (sqlite, sqlCh, -1, & stmt, NULL); // bind the for (int I = 0; I <params. count; I ++) {NSString * param = [params objectAtIndex: I]; sqlite3_bind_text (stmt, I + 1, [param UTF8String],-1, NULL );} // execute the query statement result = sqlite3_step (stmt); NSMutableArray * resultData = [NSMutableArray array]; // traverse the result while (result = SQ LITE_ROW) {NSMutableDictionary * resultRow = [NSMutableDictionary dictionary]; // obtain the number of fields int col_count = sqlite3_column_count (stmt); for (int I = 0; I <col_count; I +) {// obtain the field name const char * columName = sqlite3_column_name (stmt, I); // obtain the field value char * columValue = (char *) sqlite3_column_text (stmt, I ); NSString * columkeyStr = [NSString stringWithCString: columName encoding: NSUTF8StringEncoding]; NSString * columValueSt R = [NSString stringWithCString: columValue encoding: sequence]; [resultRow setObject: columValueStr forKey: columkeyStr];} [resultData addObject: resultRow]; result = sqlite3_step (stmt );} // close the database handle sqlite3_finalize (stmt); // close the database sqlite3_close (sqlite); NSLog (@ "Query complete! "); Return resultData;}/*** execute the SQL statement to add, modify, and delete the * SQL: executed SQL statement * params: The parameter * dataName: database name */-(BOOL) execSql :( NSString *) SQL parmas :( NSArray *) params dataBaseName :( NSString *) dataName {sqlite3 * sqlite = nil; sqlite3_stmt * stmt = nil; // open the database NSString * fileName = [self DataBaseName: dataName]; int result = sqlite3_open ([fileName UTF8String], & sqlite); if (result! = SQLITE_ OK) {NSLog (@ "failed to open"); return NO;} const char * sqlCh = [SQL UTF8String]; // compile the SQL statement sqlite3_prepare_v2 (sqlite, sqlCh, -1, & stmt, NULL); // bind the for (int I = 0; I <params. count; I ++) {NSString * parm = [params objectAtIndex: I]; sqlite3_bind_text (stmt, I + 1, [parm UTF8String],-1, NULL );} // Execute SQL result = sqlite3_step (stmt); if (result = SQLITE_ERROR | result = SQLITE_MISUSE) {NSLog (@ "SQL statement execution failed"); sqlit E3_close (sqlite); return NO;} // closes the database handle sqlite3_finalize (stmt); // closes the database sqlite3_close (sqlite); NSLog (@ "execution successful! "); Return YES;}/*** create a table * SQL: executed SQL statement * dataName: database name */-(void) createTable :( NSString *) SQL dataBaseName :( NSString *) dataName {sqlite3 * sqlite = nil; NSString * fileName = [self DataBaseName: dataName]; // open the database int result = sqlite3_open ([fileName UTF8String], & sqlite); if (result! = SQLITE_ OK) {NSLog (@ "failed to open");} else {const char * sqlCh = [SQL UTF8String]; char * error; // Execute SQL int result = sqlite3_exec (sqlite, sqlCh, NULL, NULL, & error); if (result! = SQLITE_ OK) {NSLog (@ "creation failed"); NSLog (@ "% s", error); sqlite3_close (sqlite); return ;} // close the database sqlite3_close (sqlite); NSLog (@ "created successfully"); }}@ end

4. Call format

BaseDB * db = [[BaseDB alloc] init]; // create a table NSString * dbCreate = @ "create table zhaochao (username text primary key, userPasswd test )"; NSString * dbName = @ "zhaochao. sqlite "; // [db createTable: dbCreate dataBaseName: dbName]; // Add data NSString * insertTable = @" insert into zhaochao (username, userPasswd) values (?,?) "; NSArray * insertParmas = @ [@" acasdfaa ", @" bb "]; // [db execSql: insertTable parmas: insertParmas dataBaseName: @" zhaochao. sqlite "]; // modify the data NSString * updateTable = @" update zhaochao set username =? Where username =? "; NSArray * updateParams = @ [@" admin ", @" zhaochao "]; // [db execSql: updateTable parmas: updateParams dataBaseName: @" zhaochao. sqlite "]; // delete data NSString * deleteTable = @" delete from zhaochao where username =? "; NSArray * deleteParams = @ [@" aa "]; // [db execSql: deleteTable parmas: deleteParams dataBaseName: @" zhaochao. sqlite "]; // query data NSString * selectTable = @" select username, userPasswd from zhaochao where userPasswd =? "; NSString * selectParam = @ [@" bb "]; NSArray * result = [db selectSql: selectTable parmas: selectParam dataBaseName: @" zhaochao. sqlite "]; for (int I = 0; I <result. count; I ++) {NSMutableDictionary * arr = [result objectAtIndex: I]; NSLog (@ "% @", arr );}

C # NET, how does a program connect to the SQLITE database? And can I add, delete, modify, and query tasks?

Download ADO. NET2.0 Provider for SQLite first. Download binaries zip. After downloading and decompress the package, you can find System. Data. SQLite. DLL in the bin directory. Use the Add Reference function in vs2008 to Add System. Data. SQLite. DLL to the project. Run the following code:
String datasource = "e:/tmp/test. db ";
System. Data. SQLite. SQLiteConnection. CreateFile (datasource );
// Connect to the database
System. Data. SQLite. SQLiteConnection conn = new System. Data. SQLite. SQLiteConnection ();
System. Data. SQLite. SQLiteConnectionStringBuilder connstr = new System. Data. SQLite. SQLiteConnectionStringBuilder ();
Connstr. DataSource = datasource;
Connstr. Password = "admin"; // set the Password. SQLite ADO. NET implements database Password protection.
Conn. ConnectionString = connstr. ToString ();
Conn. Open ();
// Create a table
System. Data. SQLite. SQLiteCommand cmd = new System. Data. SQLite. SQLiteCommand ();
String SQL = "CREATE TABLE test (username varchar (20), password varchar (20 ))";
Cmd. CommandText = SQL;
Cmd. Connection = conn;
Cmd. ExecuteNonQuery ();
// Insert data
SQL = "insert into test VALUES ('A', 'B ')";
Cmd. CommandText = SQL;
Cmd. ExecuteNonQuery ();
// Retrieve data
SQL = "SELECT * FROM test ";
Cmd. CommandText = SQL;
System. Data. SQLite. SQLiteDataReader reader = cmd. ExecuteReader ();
StringBuilder sb = new StringBuilder ();
While (reader. Read ())
{
Sb. Append ("username:"). Append (reader. GetString (0). Append ("\ n ")
. Append ("password:"). Append (reader. GetString (1 ));
}
MessageBox. Show (sb. ToString ();... the remaining full text>

Use SQL statements to add, delete, modify, and query a database.

Table Name: person
Field: id, name, age
1 Zhang San 20
2 Li Si 22
3 Wang Wu 23

Query: select id, name, age from person;
Delete: delete from person where id = 1 (delete the data with ID = 1 ,)
Delete from person (delete all data in the person table );
Modify: update person set name = "" where id = 2; (Change Li Si's name to Liu Dehua );
Add: insert into person values (4, 'zhao liu', 24 );