ios database application tutorial

This is an ios application which demonstrates accessing data tier for common data manipulation tasks. Today we are going to build a simple book list application. This is the application interface.

First add sqlite3 framework to your project.

BookListViewController.h

 //
//  BookListViewController.h
//  BookList
//
//  Created by Snow Leopard User on 19/04/2012.
//  Copyright 2012 __MyCompanyName__. All rights reserved.
//

#import 
#import "/usr/include/sqlite3.h"

@interface BookListViewController : UIViewController {

UITextField *txtBid;
UITextField *txtBname;
UITextField *txtBauthor;

UIButton *btnSave;
UIButton *btnFind;

UILabel *status;
NSString *databasepath;
sqlite3 *booksDB;

}
@property (nonatomic,retain) IBOutlet UITextField *txtBid;
@property (nonatomic,retain) IBOutlet UITextField *txtBname;
@property (nonatomic,retain) IBOutlet UITextField *txtBauthor;
@property (nonatomic,retain) IBOutlet UIButton *btnSave;
@property (nonatomic,retain) IBOutlet UIButton *btnFind;

@property (nonatomic,retain) IBOutlet UILabel *status;

-(IBAction) Save;
-(IBAction) Find;

@end

BookListViewController.m

//
//  BookListViewController.m
//  BookList
//
//  Created by Snow Leopard User on 19/04/2012.
//  Copyright 2012 __MyCompanyName__. All rights reserved.
//

#import "BookListViewController.h"

@implementation BookListViewController
@synthesize txtBauthor,txtBid,txtBname,btnFind,btnSave,status;

-(IBAction) Save
{
    sqlite3_stmt *statement;
    const char *dbpath=[databasepath UTF8String];

    if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK)
    {
        NSString *insertSQL=[NSString stringWithFormat:@"INSERT INTO BOOKS(BookID,BookName,Author) VALUES(\"%@\",\"%@\",\"%@\")",txtBid.text,txtBname.text,txtBauthor.text];

        const char *insert_stmt=[insertSQL UTF8String];

        sqlite3_prepare_v2(booksDB, insert_stmt, -1, &statement, NULL);

        if(sqlite3_step(statement)==SQLITE_DONE)
        {
            status.text=@"Book Added";
            txtBid.text=@"";
            txtBauthor.text=@"";
            txtBname.text=@"";

        }else
        {
            status.text=@"Failed to add Book";
        }
        sqlite3_finalize(statement);
        sqlite3_close(booksDB);
    }

}

-(IBAction) Find
{
    const char *dbpath=[databasepath UTF8String];
    sqlite3_stmt *statement;

    if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK)
    {
        NSString *querySQL=[NSString stringWithFormat:@"SELECT BookID,BookName FROM books WHERE BookID=\"%@\"",txtBid.text];

        const char *query_stmt=[querySQL UTF8String];

        if(sqlite3_prepare_v2(booksDB, query_stmt, -1, &statement, NULL)==SQLITE_OK)
        {
            if(sqlite3_step(statement)==SQLITE_ROW)
            {
                NSString *addressField=[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(statement, 0)];

                txtBname.text=addressField;

                NSString *phoneField=[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(statement, 1)];

                txtBname.text=phoneField;

                status.text=@"Match Found";
                txtBname.text=@"";
                txtBauthor.text=@"";

            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(booksDB);

    }

}

- (void)dealloc
{
    [txtBid release];
    [txtBname release];
    [txtBauthor release];

    [super dealloc];
}

- (void)didReceiveMemoryWarning
{
    // Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];

    // Release any cached data, images, etc that aren't in use.
}

#pragma mark - View lifecycle

// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad
{

    NSString *docsDir;
    NSArray *dirPaths;

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir =[dirPaths objectAtIndex:0];

    databasepath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"books.db"]];

    NSFileManager *filemgr=[NSFileManager defaultManager];

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

        if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt="CREATE TABLE IF NOT EXISTS BOOKS(ID INTEGER PRIMARY KEY AUTOINCREMENT,BookID TEXT,BookName TEXT,Author TEXT)";

            if(sqlite3_exec(booksDB, sql_stmt, NULL, NULL, &errMsg)!=SQLITE_OK)
            {
                status.text=@"Failed to create table";
            }
            sqlite3_close(booksDB);

        }else
        {
            status.text=@"Failed to Open/Create Database";
        }

    }
    [filemgr release];

    [super viewDidLoad];
}

- (void)viewDidUnload
{

    [super viewDidUnload];

    self.txtBid=NULL;
    self.txtBname=NULL;
    self.txtBauthor=NULL;
    self.status=NULL;
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}

- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
    // Return YES for supported orientations
    return (interfaceOrientation == UIInterfaceOrientationPortrait);
}

@end