iOS - SQLite Database DBManager File

iOS - SQLite Database DBManager File

22:29
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
@class DBManager;
+ (DBManager *)sharedDatabase;
- (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;
+ (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];
}
- (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;
}
- (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;
}
- (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;
}
- (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;
}
- (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;
}
- (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;
}
- (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;
}
- (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;
}

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;
}
  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];
            }
        }
    }
 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;
}
Subscribe to: Post Comments ( Atom )