articles

Home / DeveloperSection / Articles / iOS : Creating Sample App Using SQLite Database

iOS : Creating Sample App Using SQLite Database

Tarun Kumar3874 25-Sep-2015

 In this article we will learn how to create iPhone app using SQLite Database as an Back-End. We will also learn how to implement SQLite within our iPhone application and how to insert data into SQLite database using an input form and also displaying those data from a SQLite database in an UITableView.


Here we are assuming that you already know about SQLite Database, so we will only focus on our Contact Sample. Ok we will create a Contacts Sample as like as Contacts app which we see in our simple mobile phone contacts section where we store our contacts information(names, addresses and contacts) in a SQLite database. In our app we will use 'name, address, contact' fields with “Save” and “Find Contact” buttons. When we click on Save button a message will display that the contact successfully save or not.

Now, follow the steps to create our SQLite sample:


1. First of all Create a Single View application project with a name “SQLiteExample” and leave blank to class prefix and select iPhone in Devices and check mark both 'Use Storyborads' and 'Use Automatic Reference Counting'.


2. Now include the SQLite dynamic library (libsqlite3.dylib) during the link phase of the build process. Any type of failure to include this library will result in build errors. To add this library, select the target entry in the Xcode project navigator(the top entry with the product name) to display the General information panel. Now select the Build Phases tab to display the build information. The Link Binary with Libraries section lists the libraries and frameworks already included in the project. To add another library or framework click on the '+' button to display the full list. From this list search for, and then select libsqlite3.dylib and click Add button.


3. Now, goto the title bar of the screen and follow these steps, click on 'File' menu > 'New' > here click on 'File' > select 'Objective-C class' and click on 'Next' button > here name the class 'StoredRecords' and in Subclass of select 'NSObject' and click 'Next' > finally click on 'Create' button. We will use 'StoredRecords' class to store contact information so define some properties like 'Name', 'Address', 'Contact'. For example:

  1. @property(nonatomic,strong) NSString *Name;

    @property(nonatomic,strong) NSString *Address;

    @property(nonatomic,strong) NSString *Contact;

  2. Create a method with some parameters for initialization purpose at the time of object initialization, the method will look like this:

    -(id)initWithName:(NSString *)name andAddress:(NSString *)address

                                                              andContact:(NSString *)contact;

  1.  Now, create another file and named it 'SaveNewRecordViewController', this class is used as a viewcontroller where we provide user interface for saving Contact information. Create some outlets and also create reference variables of sqlite database and sqlite statement like this:

  2. sqlite3 *contactDB;

    sqlite3_stmt *statement;

    IBOutlet UITextField *name;

    IBOutlet UITextField *address;

    IBOutlet UITextField *contact;

    IBOutlet UILabel *status;

  1. We also create two methods for saving and finding records:

    - (IBAction)saveContact:(id)sender;

    - (IBAction)findContact:(id)sender;

    We also create a delegate here, this delegate we use for updating records in our contacts database as well as our contact viewcontroller, for example:


    @protocol Adddelegate <NSObject>

    -(void)record_saved:(StoredRecords *)obj;

    @end

  2.  Now, goto main storyboard and give the name in the title bar 'Contacts' and add a bar button with identifier 'Add' and set the 'TableView controller' into ViewController, here we set the code that opens the database and constructs a SQL SELECT statement to extract all the records if available and displaying them here, ViewController look like this:

  3. iOS : Creating Sample App Using SQLite Database

  4. now, add a new ViewController and using identity inspector set class 'SaveNewRecordViewController' on this viewcontroller, and add all the fields and buttons in this viewcontroller, the saving of contact data to the database is the responsibility of the saveData action method. This method will need to open the database file, extract the text from the three text fields and construct and execute a SQL INSERT statement to add this data as a record to the database ( after done, method will need to close the database) and after we implement the code that will clear the text fields ready for the next contact to be entered and after save record message will be display in the status label. We also use a Find button that is used to find contact, when we enter name and press Find button it will search the contact with its information and display it on related fields on the screen. (set navigation between both view controllers) and after it looks like this:

  5. iOS : Creating Sample App Using SQLite Database

  6. Now, open the AppDelegate implementation file and in the didFinishLaunchingWithOptions method we will check whether the database file already exists, if database file is not exists then create both the database file and the table within the database in which we store the contact information entered by the user. 

Here is our sample's Source Code:
AppDelegate.h


#import <UIKit/UIKit.h>

#import "sqlite3.h"

@interface AppDelegate : UIResponder< UIApplicationDelegate>{

sqlite3 *contactDB;

sqlite3_stmt *statement;

}

@property (strong, nonatomic) UIWindow *window;

@end

 AppDelegate.m

#import "AppDelegate.h"

#import "sqlite3.h"

@implementation AppDelegate

NSString *databasePath;

NSString *docsDir;

NSArray *dirPaths;

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions

{

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

 

    // Build the path to the database file

    databasePath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent: @"contact.db"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

 

    if([filemgr fileExistsAtPath: databasePath ] == NO)

    {

        [filemgr createFileAtPath:databasePath contents:nil attributes:nil];

    const char *dbpath = [databasePath UTF8String];

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

        {

            char *errMsg;

            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS

                                                   ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,

                                                   ADDRESS TEXT, CONTACT TEXT )";

            if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL,& errMsg) != SQLITE_OK)

            {

                NSLog(@"Failed to create table");

             }

             sqlite3_close(contactDB);

        } else {

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

        }

    } else {

        const char *dbpath = [databasePath UTF8String];

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

        {

            char *errMsg;

            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS

                                                   (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,

                                                   ADDRESS TEXT, CONTACT TEXT)";

 

            if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL,& errMsg) == SQLITE_OK)

            {

                 NSLog(@"Failed to create table");

             }

             NSLog(@"Insert failed: %s", sqlite3_errmsg(contactDB));

             sqlite3_close(contactDB);

        } else {

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

        }

        NSLog(@"Insert failed: %s", sqlite3_errmsg(contactDB));

    }

 

    // Override point for customization after application launch.

    return YES;

}

StoredRecords.h


#import <Foundation/Foundation.h>

@interface StoredRecords : NSObject

@property(nonatomic,strong) NSString *Name;

@property(nonatomic,strong) NSString *Address;

@property(nonatomic,strong) NSString *Contact;

-(id)initWithName:(NSString *)name andAddress:(NSString *)address andContact:(NSString *)contact;

-(void)show;

@end

StoredRecords.m


#import "SaveNewRecordViewController.h"

#import "StoredRecords.h"

@implementation StoredRecords

-(id)initWithName:(NSString *)name andAddress:(NSString *)address andContact:(NSString *)contact

{

self.Name = name;

self.Address = address;

self.Contact = contact;

returnself;

}

-(void)show

{

NSLog(@"Name: %@", self.Name);

NSLog(@"Address: %@", self.Address);

NSLog(@"Contact: %@", self.Contact);

}

@end

SaveNewRecordViewController.h

#import "StoredRecords.h"

#import <UIKit/UIKit.h>

#import "sqlite3.h"

 

@protocol Adddelegate <NSObject>

-(void)record_saved:(StoredRecords *)obj;

@end

 

@interface SaveNewRecordViewController : UIViewController<UITextFieldDelegate>

{

sqlite3 *contactDB;

sqlite3_stmt *statement;   

IBOutlet UITextField *name;

IBOutlet UITextField *address;

IBOutlet UITextField *contact;

IBOutlet UILabel *status;

}

@property(nonatomic,strong) UITextField *name;

@property(nonatomic,strong) UITextField *address;

@property(nonatomic,strong) UITextField *contact;

@property(nonatomic,strong) UILabel *status;

@property(nonatomic,assign) id<Adddelegate>delegate;

- (IBAction)saveContact:(id)sender;

- (IBAction)findContact:(id)sender;

@end

 SaveNewRecordViewController.m

#import "SaveNewRecordViewController.h"

#import "sqlite3.h"

 

@interface SaveNewRecordViewController ()

@end

@implementation SaveNewRecordViewController

@synthesize name;

@synthesize address;

@synthesize contact;

@synthesize status,delegate;

 

NSString *databasePath;

NSString *docsDir;

NSArray *dirPaths;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil

{

self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];

if (self) {

// Custom initialization

}

return self;

}

 

- (void)viewDidLoad

{

[super viewDidLoad];

// Do any additional setup after loading the view.

}

 

- (IBAction)saveContact:(id)sender

{

sqlite3_stmt *stmt;

const char *dbpath = [databasePath UTF8String];

 

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

{

NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO CONTACTS

(name, address, contact) VALUES

(\'%@\', \'%@\', \'%@\');", name.text,

address.text, contact.text];

const char *insert_stmt = [insertSQL UTF8String];

 

// inserting contact into SQLite database

sqlite3_prepare_v2(contactDB, insert_stmt, -1,& stmt, NULL);

 

if (sqlite3_step(stmt) == SQLITE_DONE)

{

StoredRecords *records = [StoredRecords new];

records.Name=name.text;

records.Address=address.text;

records.Contact=contact.text;

[self.delegate record_saved:records];

status.text = @"Contact added";

name.text = @"";

address.text = @"";

contact.text = @"";

} else {

status.text = @"Failed to add contact";

NSLog(@"Insert failed: %s", sqlite3_errmsg(contactDB));

}

sqlite3_finalize(stmt);

sqlite3_close(contactDB);

}

[self.view endEditing:YES];

}

 

- (IBAction)findContact:(id)sender

{

const char *dbpath = [databasePath UTF8String];

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

{

NSString *querySQL = [NSString stringWithFormat: @"SELECT address, contact FROM CONTACTS

WHERE name=\%@\", name.text];

const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(contactDB, 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)];

address.text = addressField;

NSString *contactField = [[NSString alloc] initWithUTF8String:

(const char *) sqlite3_column_text(statement, 1)];

contact.text = contactField;

status.text = @"Match found";

} else {

status.text = @"Match not found";

address.text = @"";

contact.text = @"";

}

sqlite3_finalize(statement);

}

sqlite3_close(contactDB);

}

[self.view endEditing:YES];

}

 

// called when textField editing will be done

- (void)textFieldDidEndEditing:(UITextField *)textField

{

[textField setBackgroundColor:[UIColor clearColor]];

}

// called when textField editing will be started

- (void)textFieldDidBeginEditing:(UITextField *)textField

{

[textField setBackgroundColor:[UIColor yellowColor]];

}

 

- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event

{

[self.view endEditing:YES];

}

-(void)viewDidUnload

{

self.name = nil;

self.address = nil;

self.contact = nil;

self.status = nil;

}

 

- (void)didReceiveMemoryWarning

{

[super didReceiveMemoryWarning];

// Dispose of any resources that can be recreated.

}

@end


ViewController.h

#import <UIKit/UIKit.h>

#import "AppDelegate.h"

#import "sqlite3.h"

#import "StoredRecords.h"

#import "SaveNewRecordViewController.h"

@interface ViewController : UIViewController<UITableViewDataSource,UITableViewDelegate>{

sqlite3 *contactDB;

sqlite3_stmt *stmt;

NSString *databasePath;

NSString *docsDir;

NSArray *dirPaths;

NSMutableArray *contactRecords;

}

@property(nonatomic,strong) NSString *databasePath;

@property(nonatomic,strong) NSString *docsDir;

@property(nonatomic,strong) NSArray *dirPaths;

@property(nonatomic,assign) SaveNewRecordViewController *saveobj;

@property(nonatomic, retain) IBOutlet UITableView *MyTableView;

 

@end

ViewController.m

#import "ViewController.h"

#import "AppDelegate.h"

 

@interface ViewController()<Adddelegate>

@end

@implementation ViewController

@synthesize databasePath,docsDir,dirPaths,saveobj;

StoredRecords *storedRecords;

int noOfRow;

 

- (void)viewDidLoad

{

[super viewDidLoad];

// Do any additional setup after loading the view, typically from a nib.

dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

docsDir = [dirPaths objectAtIndex:0];

saveobj.delegate=self;

 

// Build the path to the database file

databasePath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent: @"contact.db"]];

NSFileManager *filemgr = [NSFileManager defaultManager];

contactRecords = [[NSMutableArray alloc] init];

NSLog(@"ContactRecords: %d",[contactRecords count]);

if([filemgr fileExistsAtPath: databasePath ] == YES)

{

if (sqlite3_open([databasePath UTF8String],& contactDB) == SQLITE_OK)

{

NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM CONTACTS;"];

const char *select_stmt = [selectSQL UTF8String];

if(sqlite3_prepare_v2(contactDB, select_stmt, -1,& stmt, NULL)==SQLITE_OK)

{

while(sqlite3_step(stmt) == SQLITE_ROW)

{

noOfRow = sqlite3_column_int64(stmt, 0);

NSString *strName = [[NSString alloc] initWithUTF8String:

(const char *)sqlite3_column_text(stmt,1)];

NSString *strAddress = [[NSString alloc] initWithUTF8String:

(const char *) sqlite3_column_text(stmt,2)];

NSString *strContact = [[NSString alloc] initWithUTF8String:

(const char *) sqlite3_column_text(stmt,3)];

storedRecords = [[StoredRecords alloc]initWithName:strName andAddress:

strAddress andContact:strContact];

[contactRecords addObject:storedRecords];

}

}

}

NSLog(@"ContactRecords: %d",[contactRecords count]);

sqlite3_finalize(stmt);

sqlite3_close(contactDB);

} else {

NSLog(@"File not Exist");

}

}

 

-(NSInteger)numberOfSectionsInTableView:(UITableView *)tableView

{

// return the number of sections

return 1;

}

// delegate method of table

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section

{

NSLog(@"No of Row: %d",[contactRecords count]);

return [contactRecords count];

// return noOfRow;

}

 

-(void)record_saved:(StoredRecords *)obj

{

[contactRecords addObject:obj];

[self.MyTableView reloadData];

}

 

// delegate method of table

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath

{

static NSString *simpleTableIdentifier = @"Contact";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:simpleTableIdentifier];

if (cell == nil)

{

cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:

simpleTableIdentifier];

}

StoredRecords *records = [contactRecords objectAtIndex:indexPath.row];

cell.textLabel.text = records.Name;

cell.detailTextLabel.text = [NSString stringWithFormat:@"%@ | %@",records.Address,records.Contact];

return cell;

}

- (void)didReceiveMemoryWarning

{

[super didReceiveMemoryWarning];

// Dispose of any resources that can be recreated.

}

 

-(void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender

{

saveobj=[segue destinationViewController];

saveobj.delegate=self;

}

 

@end

Running the Application : now click on Run button located in the toolbar of the main Xcode project window, enter few contacts and pressing the SaveContact button to store the contact after successful execution it will display a message into status label. We can check our contact is available or not by clicking 'Contacts' button on the top navigation bar.

Here we providing all screen shots:

 

iOS : Creating Sample App Using SQLite Database

iOS : Creating Sample App Using SQLite Database

iOS : Creating Sample App Using SQLite Database

iOS : Creating Sample App Using SQLite Database


Leave Comment

Comments

Liked By