How to Adding data using SQLite for iPhone

This is the Sqlite database app. Some times your app may have the need to store data in some kind of database. In iPhone application you can do so using SQLite.

This is the Sqlite database app. Some times your app may have the need to store data in some kind of database. In iPhone application you can do so using SQLite.

Step 1: Open the Xcode and create a new Xcode project using Navigation base application template. Give the application name “SQL”. As shown in the figure below:



Step 2: Expand classes and notice Interface Builder created the RootViewController.h and RootViewController.m class for you. Expand Resources and notice the template generated a separate nib, RootViewController.xib.

Step 3: To create database in sqlite follow the below steps:
Now that the terminal is open let’s create the database. This is done with the command:

sqlite3 Datalist.sqlite
SQLite3 will now start and load the Datalist.sqlite database. By default the database is empty and contains no tables.
We only need to create one table. We will create a table called Datalist by typing the following statement:

CREATE TABLE Datalist(pk INTEGER PRIMARY KEY, name VARCHAR(25), age INTEGER);
One thing to note here is the pk field. It is the primary key of the table.
Now that our table has been created, let’s add some data. Type the following commands below.

INSERT INTO Datalist(name,age) VALUES(‘vishal’,29);
INSERT INTO Datalist(name,age) VALUES(‘nilesh’,30);
INSERT INTO Datalist(name,age) VALUES(‘vinod’,28);
INSERT INTO Datalist(name,age) VALUES(‘amrita’,25);
Your terminal window will look something as shown below:



Now go back to XCode. Do a Control-Click (right click) on the folder named Resources. Click Add -> Existing Files… and browse to your Datalist.sqlite file and click Add. It will then prompt you with a screen as shown below:



Step 4: Now that we have added the database, we need to load the Objective C libraries so we can use it. Do a control-click (right click) on the Frameworks folder. Click Add -> Existing Frameworks. So in the search bar type in libsqlite3. The file we are looking for is called libsqlite3.0.dylib.



Step 5: We need to add another file. Right-click on the Classes folder and choose Add -> New File. Under Cocoa Touch Class category choose Objective-C class. Name it SqlA.h and SqlA.m file.
This will be a very simple class that will take our dummy data file, read it in as an NSArray and provide some utility methods to access the data from the file.

@interface SqlA: NSObject {
NSInteger age;
NSString *name;
//Internal variables to keep track of the state of the object.
BOOL isDirty;
BOOL isDetailViewHydrated;
}

@property (nonatomic, readonly) NSInteger age;
@property (nonatomic, copy) NSString *name;

@property (nonatomic, readwrite) BOOL isDirty;
@property (nonatomic, readwrite) BOOL isDetailViewHydrated;

//Static methods.
+ (void) getInitialDataToDisplay:(NSString *)dbPath;
+ (void) finalizeStatements;

//Instance methods.
- (id) initWithPrimaryKey:(NSInteger)pk;

@end

Step 6: Open the SqlA.m file and make the following changes in the file.

#import "SqlA.h"
static sqlite3 *database = nil;

@implementation SqlA

@synthesize name,age,isDirty, isDetailViewHydrated;

+ (void) getInitialDataToDisplay:(NSString *)dbPath {

SQLAppDelegate *appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

const char *sql = "select name,age from Datalist";
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

while(sqlite3_step(selectstmt) == SQLITE_ROW) {

NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
SqlA *coffeeObj = [[SqlA alloc] initWithPrimaryKey:primaryKey];
coffeeObj.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];

coffeeObj.isDirty = NO;

[appDelegate.coffeeArray addObject:coffeeObj];
[coffeeObj release];
}
}
}
else
sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}

+ (void) finalizeStatements {
if(database) sqlite3_close(database);
}

- (id) initWithPrimaryKey:(NSInteger) pk {
[super init];
age = pk;

isDetailViewHydrated = NO;

return self;
}

Step 7: Open the RootViewController.h file and make the following changes in the file.

@class SqlA;
@interface RootViewController : UITableViewController {
SQLAppDelegate *appDelegate;
}
@end

Step 8: Open the RootViewController.m file and make the following changes in the file.

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
return [appDelegate.coffeeArray count];
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];
}
//Get the object from the array.
SqlA *coffeeObj = [appDelegate.coffeeArray objectAtIndex:indexPath.row];

//Set the coffename.
cell.text = coffeeObj.name;

// Set up the cell
return cell;
}

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
// Navigation logic — create and push a new view controller
}
- (void)viewDidLoad {
[super viewDidLoad];

self.navigationItem.rightBarButtonItem = self.editButtonItem;

appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

self.title = @"Name List";
}

Step 9: Open the SQLAppDelegate.h file and make the following changes in the file.

@class SqlA;
@interface SQLAppDelegate : NSObject {
UIWindow *window;
UINavigationController *navigationController;

//To hold a list of Coffee objects
NSMutableArray *coffeeArray;
}

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet UINavigationController *navigationController;

@property (nonatomic, retain) NSMutableArray *coffeeArray;

- (void) copyDatabaseIfNeeded;
- (NSString *) getDBPath;

Step 10: Open the SQLAppDelegate.m file and make the following changes in the file.

- (void) copyDatabaseIfNeeded {

//Using NSFileManager we can perform many file system operations.
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDBPath];
BOOL success = [fileManager fileExistsAtPath:dbPath];

if(!success) {
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Datalist.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

if (!success)
NSAssert1(0, @"Failed to create writable database file with message ‘%@’.", [error localizedDescription]);
}
}
- (NSString *) getDBPath {
//Search for standard documents using NSSearchPathForDirectoriesInDomains
//First Param = Searching the documents directory
//Second Param = Searching the Users directory and not the System
//Expand any tildes and identify home directories.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"Datalist.sqlite"];
}

Step 11: Now compile and Run the code and get the final output.



You can download source code from here SQL