ios database application tutorial

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



First add sqlite3 framework to your project.

BookListViewController.h

[sourcecode language="objc"]
//
//  BookListViewController.h
//  BookList
//
//  Created by Snow Leopard User on 19/04/2012.
//  Copyright 2012 __MyCompanyName__. All rights reserved.
//

#import <UIKit/UIKit.h>
#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

[/sourcecode]


BookListViewController.m

[sourcecode language="objc"]
//
// 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

[/sourcecode]