IOS sqlite資料庫增刪改查，iossqlite

IOS sqlite資料庫增刪改查，iossqlite
1.簡介簡單封裝sqlite資料庫操作類 BaseDB 用於完成對sqlite的增刪改查，使用前先匯入libsqlite3.0.dylib庫2.BaseDB.h

////  BaseDB.h//  SqliteDemo////  Created by 趙超 on 14-8-26.//  Copyright (c) 2014年 趙超. All rights reserved.//#import <Foundation/Foundation.h>#import "sqlite3.h"@interface BaseDB : NSObject/** *  建立一個表 *  sql:執行的SQL語句 *  dataName:資料庫名稱 */-(void) createTable:(NSString*)sql dataBaseName:(NSString*) dataName;/** *  執行SQL語句，主要完成增加、修改、刪除 *  sql:執行的SQL語句 *  params:SQL語句中的參數 *  dataName:資料庫名稱 */-(BOOL) execSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;/** *  選擇資料 *  sql:查詢的SQL語句 *  params:查詢SQL語句中的參數 *  dataName:查詢資料庫名稱 */-(NSMutableArray*) selectSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;@end

建立的資料庫檔案位於 /Users/zhaochao/Library/Application Support/iPhone Simulator/7.1/Applications/07D17328-B63C-4D87-9B6C-03AA5CD681EA/Documents/zhaochao.sqlite，就是 NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name]; 這個目錄。檔案可以直接用SQLiteManager軟體開啟，也可以在firefox瀏覽器中安裝sqlitemanager外掛程式開啟，如
3.BaseDB.m

////  BaseDB.m//  SqliteDemo////  Created by 趙超 on 14-8-26.//  Copyright (c) 2014年 趙超. All rights reserved.//#import "BaseDB.h"@implementation BaseDB/* *  擷取沙箱目錄 *  name:追加的目錄aa * */-(NSString*) DataBaseName:(NSString *) name {     NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name];    return fileName;}/** *  選擇資料 *  sql:查詢的SQL語句 *  params:查詢SQL語句中的參數 *  dataName:查詢資料庫名稱 */ -(NSMutableArray*) selectSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{        sqlite3 *sqlite=nil;    sqlite3_stmt *stmt=nil;    //開啟資料庫    NSString *fileName=[self DataBaseName:dataName];    int result= sqlite3_open([fileName UTF8String], &sqlite);    if (result!=SQLITE_OK) {        NSLog(@"開啟失敗");        return nil;    }    const char* sqlCh=[sql UTF8String];    //編譯SQL語句    sqlite3_prepare_v2(sqlite, sqlCh, -1, &stmt, NULL);    //綁定參數    for (int i=0; i<params.count; i++) {        NSString *param=[params objectAtIndex:i];        sqlite3_bind_text(stmt, i+1, [param UTF8String], -1, NULL);    }    //執行查詢語句    result=sqlite3_step(stmt);        NSMutableArray *resultData=[NSMutableArray array];    //遍曆結果    while (result==SQLITE_ROW) {        NSMutableDictionary *resultRow=[NSMutableDictionary dictionary];        //擷取欄位個數        int col_count = sqlite3_column_count(stmt);        for (int i=0; i<col_count; i++) {             //擷取欄位名稱            const char*columName=sqlite3_column_name(stmt,i);            //擷取欄位值            char* columValue=(char*) sqlite3_column_text(stmt, i);            NSString  *columkeyStr=[NSString stringWithCString:columName encoding:NSUTF8StringEncoding];            NSString *columValueStr=[NSString stringWithCString:columValue encoding:NSUTF8StringEncoding];            [resultRow setObject:columValueStr forKey:columkeyStr];        }        [resultData addObject:resultRow];        result=sqlite3_step(stmt);    }    //關閉資料庫控制代碼    sqlite3_finalize(stmt);    //關閉資料庫    sqlite3_close(sqlite);    NSLog(@"查詢完！");    return  resultData;}/** *  執行SQL語句，主要完成增加、修改、刪除 *  sql:執行的SQL語句 *  params:SQL語句中的參數 *  dataName:資料庫名稱 */-(BOOL) execSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{    sqlite3 *sqlite=nil;    sqlite3_stmt *stmt=nil;    //開啟資料庫    NSString *fileName=[self DataBaseName:dataName];    int result= sqlite3_open([fileName UTF8String], &sqlite);    if (result!=SQLITE_OK) {        NSLog(@"開啟失敗");        return NO;    }    const char* sqlCh=[sql UTF8String];    //編譯SQL語句    sqlite3_prepare_v2(sqlite, sqlCh, -1, &stmt, NULL);    //綁定參數    for (int i=0; i<params.count; i++) {        NSString *parm=[params objectAtIndex:i];        sqlite3_bind_text(stmt, i+1, [parm UTF8String], -1, NULL);    }    //執行SQL    result=sqlite3_step(stmt);    if (result==SQLITE_ERROR || result==SQLITE_MISUSE) {        NSLog(@"執行SQL語句失敗");        sqlite3_close(sqlite);        return  NO;    }    //關閉資料庫控制代碼    sqlite3_finalize(stmt);    //關閉資料庫    sqlite3_close(sqlite);    NSLog(@"執行成功！");    return YES;}/** *  建立一個表 *  sql:執行的SQL語句 *  dataName:資料庫名稱 */-(void)createTable:(NSString *)sql dataBaseName:(NSString *)dataName{    sqlite3 *sqlite=nil;    NSString *fileName=[self DataBaseName:dataName];    //開啟資料庫    int result= sqlite3_open([fileName UTF8String], &sqlite);    if (result!=SQLITE_OK) {        NSLog(@"開啟失敗");    }else{        const char* sqlCh=[sql UTF8String];        char* error;        //執行SQL        int result=sqlite3_exec(sqlite, sqlCh, NULL, NULL, &error);        if (result!=SQLITE_OK) {            NSLog(@"建立失敗");            NSLog(@"%s",error);            sqlite3_close(sqlite);            return ;        }        //關閉資料庫        sqlite3_close(sqlite);        NSLog(@"建立成功");    }    }@end

4.調用格式

        BaseDB *db=[[BaseDB alloc] init];    //建立表    NSString *dbCreate=@"create table zhaochao( username text primary key,userPasswd test)";    NSString *dbName=@"zhaochao.sqlite";   // [db createTable:dbCreate dataBaseName:dbName];        //添加資料    NSString *insertTable=@"insert into zhaochao (username,userPasswd) values (?,?)";    NSArray  *insertParmas=@[@"acasdfaa",@"bb"];   // [db execSql:insertTable parmas:insertParmas dataBaseName:@"zhaochao.sqlite"];        //修改資料    NSString *updateTable=@"update zhaochao set username=? where username=?";    NSArray  *updateParams=@[@"admin",@"zhaochao"];   // [db execSql:updateTable parmas:updateParams dataBaseName:@"zhaochao.sqlite"];        //刪除資料    NSString *deleteTable=@"delete from zhaochao where username=?";    NSArray   *deleteParams=@[@"aa"];   // [db execSql:deleteTable parmas:deleteParams dataBaseName:@"zhaochao.sqlite"];        //查詢資料    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，程式怎實現與SQLITE資料庫的串連?以及增刪改查的功可以？

先下載ADO.NET2.0 Provider for SQLite。下載binaries zip版就可以了。下載完後解壓縮，可以在bin目錄下找到System.Data.SQLite.DLL。在vs2008中用Add Reference功能把System.Data.SQLite.DLL加到工程裡就可以了。運行下面代碼試試：
string datasource = "e:/tmp/test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//串連資料庫
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";//設定密碼，SQLite ADO.NET實現了資料庫密碼保護
conn.ConnectionString = connstr.ToString();
conn.Open();
//建立表
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();
//插入資料
sql = "INSERT INTO test VALUES('a','b')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出資料
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());...餘下全文>>
 
用SQL語句隨便寫一條資料庫增刪改查語句

表名: person
欄位: id, name, age
1 張三 20
2 李四 22
3 王五 23

查詢: select id,name,age from person;
刪除: delete from person where id=1 (刪除ID=1的那條資料,)
delete from person (刪除person表中的所有資料);
修改: update person set name="劉德華" where id=2; (就會李四的名字改成劉德華);
增加: insert into person values(4,'趙六',24);