SQLite
can be used in iOS for managing information. It uses sqlite concerns,
which creates it much simpler for those who know SQL.
Steps Involved
Step 1. Create a simple View based application.
Step 2. Select your project file, then select targets and then add libsqlite3.dylib library in choose frameworks.
Step 3. Create a new file by selecting File-> New -> File... -> select Objective C class and click next.
Step 4. Name the class as DBManager with "sub class of" as NSObject.
Step 5. Select create.
Step 6. Update DBManager.h as follows −
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#define DEBUG_DB_RES YES
#import <sqlite3.h>
#define DEBUG_DB_RES YES
@class DBManager;
+ (DBManager *)sharedDatabase;
- (BOOL)createDB;
- (sqlite3 *)Database;
- (BOOL)openDBConnection;
- (BOOL)closeDBConnection;
- (BOOL)insertIntoDBWithQuery:(NSString *)query;
- (BOOL)updateIntoDBWithQuery:(NSString *)query;
- (BOOL)createDB;
- (sqlite3 *)Database;
- (BOOL)openDBConnection;
- (BOOL)closeDBConnection;
- (BOOL)insertIntoDBWithQuery:(NSString *)query;
- (BOOL)updateIntoDBWithQuery:(NSString *)query;
Step 7. Update DBManager.m as follows −
#import "DBManager.h"
@interface DBManager () {
NSString *databasePath;
}
@end
@implementation DBManager
static sqlite3 *database = nil;
//static sqlite3_stmt *statement = nil;
NSString *databasePath;
}
@end
@implementation DBManager
static sqlite3 *database = nil;
//static sqlite3_stmt *statement = nil;
+ (DBManager *)sharedDatabase {
static DBManager *sharedInstance;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
if (!sharedInstance) {
sharedInstance = [[super alloc]init];
}
});
return sharedInstance;
}
- (void)dealloc {
[self closeDBConnection];
}
static DBManager *sharedInstance;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
if (!sharedInstance) {
sharedInstance = [[super alloc]init];
}
});
return sharedInstance;
}
- (void)dealloc {
[self closeDBConnection];
}
- (BOOL)createDB{
// Build the path to the database file
databasePath = [self getDBPath];
if (DEBUG_DB_RES)
NSLog(@"%@",databasePath);
NSFileManager *filemgr = [NSFileManager defaultManager];
if (![filemgr fileExistsAtPath:databasePath]) {
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK) {
_isConnected = YES;
[self createTables];
sqlite3_close(database);
_isConnected = NO;
if (DEBUG_DB_RES)
NSLog(@"Database created successfully.");
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to create database.");
return NO;
}
}
return YES;
}
// Build the path to the database file
databasePath = [self getDBPath];
if (DEBUG_DB_RES)
NSLog(@"%@",databasePath);
NSFileManager *filemgr = [NSFileManager defaultManager];
if (![filemgr fileExistsAtPath:databasePath]) {
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK) {
_isConnected = YES;
[self createTables];
sqlite3_close(database);
_isConnected = NO;
if (DEBUG_DB_RES)
NSLog(@"Database created successfully.");
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to create database.");
return NO;
}
}
return YES;
}
- (NSString *)getDBPath {
if (self.DBName && self.DBName.length > 0) {
// Get the documents directory
NSString *docsDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
// Build the path to the database file
return [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:self.DBName]];
}
return nil;
}
- (BOOL)openDBConnection {
databasePath = [self getDBPath];
if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
_isConnected = YES;
if (DEBUG_DB_RES)
NSLog(@"Database opened successfully.");
return YES;
}
if (DEBUG_DB_RES)
NSLog(@"Failed to open database.");
return NO;
}
if (self.DBName && self.DBName.length > 0) {
// Get the documents directory
NSString *docsDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
// Build the path to the database file
return [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:self.DBName]];
}
return nil;
}
- (BOOL)openDBConnection {
databasePath = [self getDBPath];
if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
_isConnected = YES;
if (DEBUG_DB_RES)
NSLog(@"Database opened successfully.");
return YES;
}
if (DEBUG_DB_RES)
NSLog(@"Failed to open database.");
return NO;
}
- (BOOL)closeDBConnection {
if (sqlite3_close(database) == SQLITE_OK) {
_isConnected = NO;
if (DEBUG_DB_RES)
NSLog(@"Database closed successfully.");
return YES;
}
NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));
if (DEBUG_DB_RES)
NSLog(@"Failed to close database.");
return NO;
}
- (sqlite3 *)Database {
return database;
}
if (sqlite3_close(database) == SQLITE_OK) {
_isConnected = NO;
if (DEBUG_DB_RES)
NSLog(@"Database closed successfully.");
return YES;
}
NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));
if (DEBUG_DB_RES)
NSLog(@"Failed to close database.");
return NO;
}
- (sqlite3 *)Database {
return database;
}
- (BOOL)createTables {
if (_isConnected) {
NSMutableArray *Queries = [[NSMutableArray alloc] init];
[Queries addObject:@"CREATE TABLE IF NOT EXISTS User_Master (UID INTEGER PRIMARY KEY AUTOINCREMENT,USER_IMAGENAME TEXT NOT NULL, USER_FNAME VARCHAR(30) NOT NULL,USER_LNAME VARCHAR(30) NOT NULL,USER_EMAIL VARCHAR(255) NOT NULL,USER_PASS VARCHAR(200) NOT NULL,USER_CPASS VARCHAR(200) NOT NULL,USER_MOBILE INTEGER NOT NULL,USER_STATE TEXT NOT NULL,USER_CITY TEXT NOT NULL,USER_PINCODE INTEGER NOT NULL,USER_ADDRESS TEXT NOT NULL,USER_GENDER INT(1) NOT NULL,USER_DOB DATE NOT NULL)"];
for (int i = 0; i < [Queries count]; i++) {
if (![self createTableWithQuery:[Queries objectAtIndex:i]])
return false;
}
return true;
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return false;
}
if (_isConnected) {
NSMutableArray *Queries = [[NSMutableArray alloc] init];
[Queries addObject:@"CREATE TABLE IF NOT EXISTS User_Master (UID INTEGER PRIMARY KEY AUTOINCREMENT,USER_IMAGENAME TEXT NOT NULL, USER_FNAME VARCHAR(30) NOT NULL,USER_LNAME VARCHAR(30) NOT NULL,USER_EMAIL VARCHAR(255) NOT NULL,USER_PASS VARCHAR(200) NOT NULL,USER_CPASS VARCHAR(200) NOT NULL,USER_MOBILE INTEGER NOT NULL,USER_STATE TEXT NOT NULL,USER_CITY TEXT NOT NULL,USER_PINCODE INTEGER NOT NULL,USER_ADDRESS TEXT NOT NULL,USER_GENDER INT(1) NOT NULL,USER_DOB DATE NOT NULL)"];
for (int i = 0; i < [Queries count]; i++) {
if (![self createTableWithQuery:[Queries objectAtIndex:i]])
return false;
}
return true;
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return false;
}
- (BOOL)createTableWithQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
if (DEBUG_DB_RES)
NSLog(@"Table created successfully.\n%@", query);
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to create table. Error : %@", [[NSString alloc] initWithUTF8String:errMsg]);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
if (_isConnected) {
char *errMsg;
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
if (DEBUG_DB_RES)
NSLog(@"Table created successfully.\n%@", query);
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to create table. Error : %@", [[NSString alloc] initWithUTF8String:errMsg]);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
- (BOOL)executeQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to execute query. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to execute query. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
- (BOOL)updateIntoDBWithQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to update row into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
- (BOOL)deleteFromDBWithQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to delete row(s) into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to update row into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
- (BOOL)deleteFromDBWithQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to delete row(s) into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
- (BOOL)insertIntoDBWithQuery:(NSString *)query {
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to insert row into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
if (_isConnected) {
char *errMsg;
if (DEBUG_DB_RES)
NSLog(@"Query String : %@", query);
@synchronized(self) {
if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
return YES;
}
else {
if (DEBUG_DB_RES)
NSLog(@"Failed to insert row into table. Error : %s", errMsg);
}
}
}
else {
if (DEBUG_DB_RES)
NSLog(@"DB connection is not open.");
}
return NO;
}
Step 9. Create IBOutlets for the above text fields.
Step 10. Create IBAction for the above buttons.
Step 11. Update ViewController.h as follows −
{
AMGlobal *objGlobal;
}
AMGlobal *objGlobal;
}
if (objGlobal.isEdit) {
if ([self isValidate]) {
[objDBManager updateIntoDBWithQuery:[NSString stringWithFormat:@"UPDATE User_Master SET USER_IMAGENAME = \"%@\",USER_FNAME = \"%@\",USER_LNAME = \"%@\",USER_EMAIL = \"%@\" ,USER_MOBILE = \"%@\",USER_STATE = \"%@\",USER_CITY = \"%@\",USER_PINCODE = \"%d\",USER_ADDRESS = \"%@\",USER_GENDER = \"%d\",USER_DOB = \"%@\" WHERE UID = \"%@\"",imageName,firstName.text,lastName.text,email.text,mobileNumber.text,[objLoginUser state],[objLoginUser city],[objLoginUser changpincode],[objLoginUser address],genderType,dob.text,[objLogInUser userid]]];
alertMessage = @"Update Successfully";
ischangeImage = NO;
[AMAlert showAlertWithTitle:alertTitle Message:alertMessage];
}
}else{
if ([self isValidate]) {
if(result) {
[objDBManager insertIntoDBWithQuery:[NSString stringWithFormat:@"INSERT INTO User_Master (USER_IMAGENAME,USER_FNAME,USER_LNAME,USER_EMAIL,USER_PASS,USER_CPASS,USER_MOBILE,USER_STATE,USER_CITY,USER_PINCODE,USER_ADDRESS,USER_GENDER,USER_DOB)VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%d\",\"%@\",\"%d\",\"%@\")",imageName,firstName.text,lastName.text,email.text,password.text,cPassword.text,mobileNumber.text,[objRegister state],[objRegister city],[[objRegister pincode]intValue],[objRegister address],genderType,dob.text]];
alertMessage = @"Register Successfully";
[AMAlert showAlertWithTitle:alertTitle Message:alertMessage];
}
}
}
if ([self isValidate]) {
[objDBManager updateIntoDBWithQuery:[NSString stringWithFormat:@"UPDATE User_Master SET USER_IMAGENAME = \"%@\",USER_FNAME = \"%@\",USER_LNAME = \"%@\",USER_EMAIL = \"%@\" ,USER_MOBILE = \"%@\",USER_STATE = \"%@\",USER_CITY = \"%@\",USER_PINCODE = \"%d\",USER_ADDRESS = \"%@\",USER_GENDER = \"%d\",USER_DOB = \"%@\" WHERE UID = \"%@\"",imageName,firstName.text,lastName.text,email.text,mobileNumber.text,[objLoginUser state],[objLoginUser city],[objLoginUser changpincode],[objLoginUser address],genderType,dob.text,[objLogInUser userid]]];
alertMessage = @"Update Successfully";
ischangeImage = NO;
[AMAlert showAlertWithTitle:alertTitle Message:alertMessage];
}
}else{
if ([self isValidate]) {
if(result) {
[objDBManager insertIntoDBWithQuery:[NSString stringWithFormat:@"INSERT INTO User_Master (USER_IMAGENAME,USER_FNAME,USER_LNAME,USER_EMAIL,USER_PASS,USER_CPASS,USER_MOBILE,USER_STATE,USER_CITY,USER_PINCODE,USER_ADDRESS,USER_GENDER,USER_DOB)VALUES (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%d\",\"%@\",\"%d\",\"%@\")",imageName,firstName.text,lastName.text,email.text,password.text,cPassword.text,mobileNumber.text,[objRegister state],[objRegister city],[[objRegister pincode]intValue],[objRegister address],genderType,dob.text]];
alertMessage = @"Register Successfully";
[AMAlert showAlertWithTitle:alertTitle Message:alertMessage];
}
}
}
Step 11. Update Appdelegate.m
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
// [[UIApplication sharedApplication] setIdleTimerDisabled:YES];
// Override point for customization after application launch.
[[DBManager sharedDatabase] setDBName:@"myDb"];
[[DBManager sharedDatabase] createDB];
[[DBManager sharedDatabase] openDBConnection];
[[AMFilemanager sharedInstance] setImageDirectory:@"AMImage"];
[[AMRechabilityStatus getInstance] loadRechability];
return YES;
}
// [[UIApplication sharedApplication] setIdleTimerDisabled:YES];
// Override point for customization after application launch.
[[DBManager sharedDatabase] setDBName:@"myDb"];
[[DBManager sharedDatabase] createDB];
[[DBManager sharedDatabase] openDBConnection];
[[AMFilemanager sharedInstance] setImageDirectory:@"AMImage"];
[[AMRechabilityStatus getInstance] loadRechability];
return YES;
}
Emoticon Emoticon