SQLite database provides advanced functions for adding, removing, updating records into a database. It also allows performing some of the advanced levels of operations like database migration, Inner join, a grouping of tables etc.

Its normally used to store large records into the application such as managing user list, or offline records of web-based data to make sure it supports an offline load of the data in case of no internet connection.

Running with SQL functions is as faster as any other database storage options & its more convenient options too. The reason is it provides advance inserting & deleting features & clauses which allows developers to perform complex operations over a local database.

So here are some basic functions which we normally use in iOS Application for managing local database.

Save record :

– (void) doSaveMyData  {

sqlite3 *database;

[self CheckConnection];

if (sqlite3_open([dbpath UTF8String], &database) == SQLITE_OK)  {

NSString *query = [NSString stringWithFormat:@”insert into myTable values(‘%@’, ‘%@’)”, value1, value2];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

For Check Database Connection (This will commonly used before any database operation)

-(void)CheckConnection {

NSArray *docpath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *docdir = [docpath objectAtIndex:0];

dbpath = [docdir stringByAppendingPathComponent:database];

BOOL success;

NSFileManager *fm = [NSFileManager defaultManager];

success = [fm fileExistsAtPath:dbpath];

if(success) {

return;   }

NSString *dbPathFromApp = [[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:database];

[fm copyItemAtPath:dbPathFromApp toPath:dbpath error:nil];

}

Get list of records :

-(void)doGetRecords:(NSString*)strCatID {

[self CheckConnection];

if (arrList.count > 0) {

[arrList removeAllObjects];

arrList = nil;

}

arrOutFitsList = [[NSMutableArray alloc] init];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”select * from myTable where cat_id = ‘%@””,strCatID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_ROW) {

DetailDict *obj = [DetailDict new];

obj.id = [NSString stringWithFormat:@”%d”,(int)sqlite3_column_int(cmpsqlstmt, 0)];

obj.Tag = [NSString stringWithUTF8String:(char*)sqlite3_column_text(cmpsqlstmt, 1)];

[arrOutFitsList addObject:obj];

}

sqlite3_finalize(cmpsqlstmt);

}

sqlite3_close(database);

}

Update record :

-(void)doUpdaterecord {

[self CheckConnection];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”update myTable set cat_id=’%@’, Tag=’%@'”, self.objDetailsDict.cat_id, self.objDetailsDict.Tag];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

Remove Record :

-(void)doRemoveRecord:(NSString*)stID {

[self CheckConnection];

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”DELETE FROM myTable WHERE id=’%@'”,stID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpSqlStmt;

sqlite3_prepare_v2(database, sqlStmt, -1, &cmpSqlStmt, NULL);

sqlite3_step(cmpSqlStmt);

sqlite3_finalize(cmpSqlStmt);

}

sqlite3_close(database);

}

Count Number of Records :

-(int)doCountToalRecords:(NSString *)ID {

[self CheckConnection];

int totalIS = 0;

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = [NSString stringWithFormat:@”SELECT Count(*) FROM myTable where cat_id = ‘%@'”,ID];

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_ROW) {

totalIS = sqlite3_column_int(cmpsqlstmt, 0);

}

}

sqlite3_finalize(cmpsqlstmt);

sqlite3_close(database);

}

return totalIS;

}

Add new field to existing table :

-(void)doAddNewFieldToTable {

sqlite3 *database;

if (sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) {

NSString *query = @”ALTER TABLE myTable ADD COLUMN newField Varchar”;

const char *sqlStmt = [query UTF8String];

sqlite3_stmt *cmpsqlstmt;

if (sqlite3_prepare_v2(database, sqlStmt, -1, &cmpsqlstmt, NULL) == SQLITE_OK) {

while (sqlite3_step(cmpsqlstmt) == SQLITE_DONE) {

NSLog(@”Success”);

}

}

sqlite3_finalize(cmpsqlstmt);

sqlite3_close(database);

}

}

So in this blog we have covered up all basic operations performed in sqlite database such as inserting, fetching, updating, removing records & also for counting number of records, adding new field to existing table while working with Objective C in iOS Application.

Share
Share