SQLite Datbase in iPhone ~ Android & iPhone App Development

This is the simple iPhone project which is based on SQLite database.This is very simple way for using SQLite in out iPhone APP.
We have to add a SQLite framework in our project for using database. Do a control-click (right click) on the Frameworks folder. Click Add -> Existing Frameworks. So in the search bar type in libsqlite3. Now add libsqlite3.0.dylib.
Here i have done all the basic database operation

Add/insert
Delete
Update
Retrieve

Demp app is developed in Xcode 4.3.2 and IOS SDK 5

AppDelegate.h
===============
#import <UIKit/UIKit.h>
#import "sqlite3.h"
@class ViewController;

@interface AppDelegate : UIResponder <UIApplicationDelegate>{
    sqlite3 *contactDB;
    NSString *databasePath;
    NSString *relinkUserId;
}

@property (strong, nonatomic) UIWindow *window;

@property (strong, nonatomic) ViewController *viewController;

@end

Create the database structure in AppDelegate once

AppDelegate.m
===============
#import "AppDelegate.h"

#import "ViewController.h"

@implementation AppDelegate

@synthesize window = _window;
@synthesize viewController = _viewController;

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    [self createDb];
    self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
    // Override point for customization after application launch.
    self.viewController = [[ViewController alloc] initWithNibName:@"ViewController" bundle:nil];
    UINavigationController *navController = [[UINavigationController alloc] initWithRootViewController:self.viewController];

    self.window.rootViewController = navController;


    [self.window makeKeyAndVisible];
    return YES;
}

-(void)createDb{
    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"Test.db"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS STUDENT (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT,EMAIL TEXT)";

            if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog( @"Failed to create STUDENT table");
            }

            sqlite3_close(contactDB);

        } else {
            NSLog( @"Failed to open/create database");
        }
    }

}

@end
ViewController.h
================
#import <UIKit/UIKit.h>
#import "sqlite3.h"
@interface ViewController : UIViewController{
    IBOutlet UITextField *nameTf,*phoneTf,*emailTf,*addressTf;
    NSString *name,*phone,*email,*address,*databasePath;
     sqlite3 *contactDB;


}
@property(nonatomic,retain)NSString *name,*phone,*email,*address;
-(IBAction)save:(id)sender;

@end

ViewController.m
===================
How to save/insert data.

-(IBAction)save:(id)sender{

    name=nameTf.text;
    phone=phoneTf.text;
    email=emailTf.text;
    address=addressTf.text;

   databasePath=[self dataBasePath];
    // NSFileManager *filemgr = [NSFileManager defaultManager];
    sqlite3_stmt    *statement;

    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {

            NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO STUDENT (name, address, phone,email) VALUES (\"%@\", \"%@\", \"%@\",\"%@\")", name, address, phone,email];

            const char *insert_stmt = [insertSQL UTF8String];
            sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);



        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSInteger lastRowId = sqlite3_last_insert_rowid(contactDB);
            NSLog(@"lastRowId==%d",lastRowId);


        }else {
            NSLog(@"Failed to add contact");
            NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(contactDB));
        }

        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }

}

-(NSString *)dataBasePath{
    NSString *docsDir;
    NSArray *dirPaths;


    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

    // Build the path to the database file
    return [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"Test.db"]];

}

How to retrieve data from table
==========

-(void)readStudentlist{
   self.studentList=[[NSMutableArray alloc] init];
    // NSFileManager *filemgr = [NSFileManager defaultManager];
    const char *dbpath = [[self dataBasePath] UTF8String];
    if(sqlite3_open(dbpath , &contactDB) == SQLITE_OK) {
        // Setup the SQL Statement and compile it for faster access
        const char *sqlStatement = "select * from STUDENT order by NAME asc";
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(contactDB, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            // Loop through the results and add them to the feeds array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                // Read the data from the result row
                int rowID = sqlite3_column_int(compiledStatement, 0);
                //NSLog(@"primaryKey==%d",primaryKey);
                NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
                NSString *aAdd = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
                NSString *aPh = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
                NSString *aEmail = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];

                NSLog(@"%@",aName);

                CustomerVO *vo = [[CustomerVO alloc] init];
                vo.sName=aName;
                vo.sAddress=aAdd;
                vo.sPhone=aPh;
                vo.sEmail=aEmail;
                vo.sId=rowID;
                [self.studentList addObject:vo];
             }
        }
        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);

    }
    sqlite3_close(contactDB);

}

Delete a specific data
======================
-(void)deleteStudent:(int)rowId{

    const char *dbpath = [[self dataBasePath] UTF8String];
    sqlite3_stmt    *statement;
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *deleteSQL = [NSString stringWithFormat: @"delete from STUDENT where id=%d",rowId];

        const char *del_stmt = [deleteSQL UTF8String];

        sqlite3_prepare_v2(contactDB, del_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            //[self dismissModalViewControllerAnimated:YES];
            //NSInteger lastRowId = sqlite3_last_insert_rowid(contactDB);
            NSLog(@"deleted folder");

        } else {
            NSLog(@"Failed to add contact");
            NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(contactDB));
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

CustomerVO.h
=============
#import <Foundation/Foundation.h>
@interface CustomerVO : NSObject{
NSString *sName;
NSString *sAddress;
NSString *sPhone;
NSString *sEmail;
NSInteger sId;
}
@property (assign, nonatomic) NSInteger sId;
@property (nonatomic, retain) NSString *sName,*sAddress,*sPhone,*sEmail;

@end

CustomerVO.m
=============
#import "CustomerVO.h"
@implementation CustomerVO
@synthesize sName,sAddress,sEmail,sPhone,sId;
@end