Database handing in Sqlite in iPhone application.

These methods are used for Insert, Update, Select Records from iPhone Sqlite Database.

You can download source code from here Download.

Database.h file contain this code.

#import
#import
#import "DataBase.h"
#import "SaveEmployeeInfo.h"

@interface DataBase : NSObject {

}
+(NSMutableArray *)selectEmployeeInfo;
+(BOOL)insertEmployeeInfo:(SaveEmployeeInfo *)dboObject;
+(BOOL)updateEmployeeInfo :(NSMutableArray *)arrEmployeeInfo;

-(void)createEditableCopyOfDatabaseIfNeeded;
-(void)initializeDatabase;

@end


Database.m file contain this code.

#import "DataBase.h"
#import "SaveEmployeeInfo.h"
#define databaseName @"Employee.sqlite"

@implementation DataBase
static sqlite3 *sdatabase = nil;
static NSString *databasePath;

// Creates a writable copy of the bundled default database in the application Documents directory.
-(void)createEditableCopyOfDatabaseIfNeeded {
   
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:databaseName];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) return;
         // The writable database does not exist, so copy the default to the appropriate location.
   
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

// Open the database connection and retrieve minimal information for all objects.
-(void)initializeDatabase {
   
    // The database is stored in the application bundle.
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    databasePath = [documentsDirectory stringByAppendingPathComponent:databaseName];
   
    NSLog(@"Database Path : %@", databasePath);
   
    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([databasePath UTF8String], &sdatabase) == SQLITE_OK) {
       
    }
    else {
        sqlite3_close(sdatabase);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(sdatabase));
        }
}
//*************************************************************************

+(BOOL)insertEmployeeInfo:(SaveEmployeeInfo *)dboObject {

    BOOL success = NO;
    sqlite3_stmt *insert_statement = nil;
    if (dboObject == nil) {
        return success = NO;
    }

    const char *insertsql = "insert into employee (emp_first_name, emp_last_name, qualification, technology) VALUES (?,?,?,?)";
   
    if (insert_statement == nil) {

        if (sqlite3_prepare_v2(sdatabase, insertsql, -1, &insert_statement, NULL) != SQLITE_OK) {
       
            NSAssert1(0, @"Error: failed to perpare statement with message '%s'.", sqlite3_errmsg(sdatabase));
            return success = NO;
        }
        else {
            //sqlite3_bind_text(insert_statement, 1, [dboObject.Pk_id UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 1, [dboObject.emp_first_name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 2, [dboObject.emp_last_name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 3, [dboObject.qualification UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(insert_statement, 4, [dboObject.technologies UTF8String], -1, SQLITE_TRANSIENT);
           
            double retValue = sqlite3_step(insert_statement);
            if (retValue == SQLITE_DONE)
                success = YES;
            }
        }
    sqlite3_reset(insert_statement);
    sqlite3_finalize(insert_statement);
    insert_statement = nil;
    return success;
}

+(NSMutableArray *)selectEmployeeInfo {
   
    NSMutableArray *arrResult = [[NSMutableArray alloc]init];
   
    const char *sql = "Select * from employee";
    sqlite3_stmt *select_statement = nil;
    // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
    // The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.       
    if (sqlite3_prepare_v2(sdatabase, sql, -1, &select_statement, NULL) != SQLITE_OK) {
       
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(sdatabase));
    }
    else {
        while (sqlite3_step(select_statement) == SQLITE_ROW) {
        // The second parameter indicates the column index into the result set.
       
            char *FirstName;
            char *LastName;
            char *Qualification;
            char *Technology;
       
            if(sqlite3_column_text(select_statement, 0) != NULL) {
               
                FirstName = sqlite3_column_text(select_statement, 0);
                LastName = sqlite3_column_text(select_statement, 1);
                Qualification = sqlite3_column_text(select_statement, 2);
                Technology = sqlite3_column_text(select_statement, 3);           
               
                NSString *strFirstName = [NSString stringWithUTF8String:FirstName];
                NSString *strLastName = [NSString stringWithUTF8String:LastName];
                NSString *strQualification = [NSString stringWithUTF8String:Qualification];
                NSString *strTechnology = [NSString stringWithUTF8String:Technology];
                NSMutableDictionary *dictResult = [[NSMutableDictionary alloc]initWithObjectsAndKeys:strFirstName, @"first_name", strLastName, @"last_name", strQualification, @"qualification", strTechnology, @"technology", nil];
                [arrResult addObject:dictResult];
            }
        }
    }   
   
    // "Finalize" the statement - releases the resources associated with the statement.
    sqlite3_finalize(select_statement);
    select_statement = nil;
    [arrResult retain];
    return arrResult;   
}

+(BOOL)updateEmployeeInfo :(NSMutableArray *)arrEmployeeInfo {

    Boolean result = FALSE;
    static sqlite3_stmt *update_statement = nil;
   
    if (update_statement == nil)
    {

        const char *sql = "update employee set emp_first_name = ?, emp_last_name = ?, qualification = ?, technology = ? where rowid = 1";
       
        if (sqlite3_prepare_v2(sdatabase, sql, -1, &update_statement, NULL) != SQLITE_OK)
        {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(sdatabase));       
        }   
        else
        {
            sqlite3_bind_text(update_statement, 1, [[arrEmployeeInfo objectAtIndex:0] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(update_statement, 2, [[arrEmployeeInfo objectAtIndex:1] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(update_statement, 3, [[arrEmployeeInfo objectAtIndex:2] UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(update_statement, 4, [[arrEmployeeInfo objectAtIndex:3] UTF8String], -1, SQLITE_TRANSIENT);
        }
    }           
    int success = sqlite3_step(update_statement);   
    if (success != SQLITE_DONE)
    {
        result = FALSE;
    }
    else
    {       
        result = TRUE;   
    }
   
    sqlite3_reset(update_statement);
    sqlite3_finalize(update_statement);
    update_statement=nil;
    return result;
}

@end