Starry Starry Night, I can feel the Glory of Moon which is looking at me from my Window.. :)
Enjoying Punk Rock "Boulevard Of Broken Dreams" and I am going to write one more Article
about one of the important key feature in iOS.
How to use SQLite Database in our iPhone application with Insert, Select, Search and Delete operation programmatically?
So stay with me, and do it step by step by .. don't forget to drop me a message or comment.
At the end of this Tutorial you can find Complete Source code zip file.
Before starting You may like to know our Database details
-------------------------------------------------
| Database Name : contacts.db |
| Database Table Name : CONTACTS |
| Table Columns Name : NAME, ADDRESS, PHONE |
-------------------------------------------------
iPhone Simulator Sqlite DB file you can find on this path
/Users/RDCMac/Library/Application Support/iPhone Simulator/6.1/Applications/0975F545-E4D2-40E8-97C6-0EA7F8995315/Documents/contacts.db
Note : This Application Developed Using : iOS 6 , Xcode 4.6, and MAC OS X Lion 10.8.1
PHASE - I (Create New Project)
So, Let's Fire Xcode!!
Go to File --> New --> Project
Now we can see a Pop up Window for selecting our application templet
So make sure you select Application in iOS option on your Left hand side.
then select Single View Application as shown in below picture and Go for Next..
In the Next Window we need to put Our Project Details this way
---------------------------------------------------------------
| Product Name : SQLiteDemo |
| Organization Name :RDCWorld |
| Company Identifier : com.rdcworld |
| |
| Class Prefix : (leave it blank for now) |
| |
| Devices : iPhone |
----------------------------------------------------------------
Note : Don't forget to Make Tick mark on "Use Automatic Reference Counting" option.
Go to Next --> Create.
Now you can see Xcode default dashboard with our newly created project .
PHASE - II (Add Extra Frameworks/ Lib and Create required Files)
to use the Sqlite database we need to add Sqlite Lib to our Project.
procedure is same as we add framework to our project.
1. select Project (Blue Icon) go to --> Build Phased --> Link Binary With Libraries option --> click (+) and you will get popup window, so look for "libsqlite3.dylib file".
after adding successful you can see like this
you can see ibsqlite3.dylib file added to our project, drag this newly added file to the Frameworks folder,
Now We will create two Important Classes
*Model Class for storing our object
*DBHelper Class for Writing all SQLite Code in a single file, so we can use in whole app.
So first create two Groups/Folders for storing these classes.
1. For Model Classes
1.1 Create new group : Right click on project -->New Group
new you can see new folder has been created, double click on it give name as "Models"
1.2 Create Model Class to store Contact Object
1.2.1 Right Click on Models folder -->New File
in the pop window select "Objective-C class" Templet in Cocoa Touch for iOS option.
1.2.2 In the next window Fill the values as written below
Class : ContactObject
Subclass of : NSObject
Note : Un -check both checkboxes if they are checked (we don't need any XIB here)
then go for create.
now we can see two new files added to our project
ContactObject.h
ContactObject.m
2. For DBHelper Class (procedure is same as we created Model class)
2.1 Create new group : Right click on project -->New Group
new you can see new folder has been created, double click on it give name as "DBHelper"
2.2 Create Helper Class to handle all SQL code
2.2.1 Right Click on DBHelper folder -->New File
in the pop window select "Objective-C class" Templet in Cocoa Touch for iOS option.
2.2.2 In the next window Fill the values as written below
Class : DBHelper
Subclass of : NSObject
Note : Un -check both checkboxes if they are checked (we don't need any XIB here)
then go for create.
now we can see two new files added to our project
DBHelper.h
DBHelper.m
So, this time our project structure is look like
PHASE - II (Design UI)
We need to add some UITextFields, UIButtons,UILabel and Title Bar on our screen to get it work.
So, Just open ViewController.xib file you can see default blank layout
1. let's drag UINavigationBar from Object library double click on it and give title as "SQLite DB : Contact Info", (we will use this for just showing app Title)
2. TextFields :
2.1 now drag TextFields for Taking input Name ,adjust full width, then go to Attribute Inspector and update Placeholder value as "enter name here"
2.2 do same for next TextField for Taking input Address and change its Placeholder value as "enter address here"
2.3. drag one more TextField for Taking input Phone No and change its Placeholder value as "enter 10 digit phone no"
3. UILabel : drag one label and double click on it, change text as "Note : for search and delete only enter name"
4. UIButtons: drag 4 Buttons and double click and give them name as Save, View All, Search, and Delete respectively .
Now adjust all these items on your Layout as per shown in below screen shot.
PHASE - III (Create IBOutlets and IBAction )
We are going to create IBOutlet for UITextFields and IBAction methods for UIButtons .
So just open ViewController.xib
Okay, Now select Assistant Editor on Top Right side
You can see our ViewController.xib (Left side) + ViewController.h (Right side) opened together.
1. Create IBOutlets
1.1 Select UITextField (which says - 'enter name here') --> Right Click on it
1.2 Click on "New Referencing outlet" option and drag cursor to ViewController.h(right side) file, when your cursor is between @interface and @end you can see like this.
1.3 Now you will get Popup Window
just put Name : "nameTextField" and click on Connect.
you can see below line added in our ViewController's header file
@property (weak, nonatomic) IBOutlet UITextField *nameTextField;
1.4 do the same for next two TextFields and give them name as "addressTextField" and "phoneNoTextField" respectively
this time you can see two IBOutlets created in header file
@property (weak, nonatomic) IBOutlet UITextField *addressTextField;
@property (weak, nonatomic) IBOutlet UITextField *phoneNoTextField;
2. Create IBAction methods
2.1 Select UIButton (which says -'Save') --> Right Click on it
2.2 Click on Touchup Inside in "Sent Events" option and drag cursor just below to new created IBOutlets, and put the method name "" in pop window
you can see new method is added in our ViewController's header file
- (IBAction)saveContactInfo:(id)sender;
2.3 do the same for next three Buttons and give methods name as "viewAllContactsInfo","searchContactInfo" and "deleteContactInfo" respectively.
this time you can see three new IBAction methods created in header file
- (IBAction)viewAllContactsInfo:(id)sender;
- (IBAction)searchContactInfo:(id)sender;
- (IBAction)deleteContactInfo:(id)sender;
Done!! now back to Standard Editor
PHASE - IV (Hook Up Delegate with File Owner )
Here we need to hook up our TextFields Delegate to File Owner, so we can use its method to hand Keypad.
when use Tap Return key while typing then keypad should go back.
1. Open ViewController.xib
2. Select UITextField (which says - 'enter name here') --> go the Connection Inspector --> drag cursor from Delegate to File owner (Right to Left)
as shown in below screen shot
3. repeat same for next two Text Fields (Address, Phone No)
Note : you can achieve same by doing it programmatically in ViewController's viewDidLoad method.
nameTextField.delegate =self;
addressTextField.delegate=self;
phoneNoTextField.delegate=self;
PHASE - V (Writing Code)
~ ~ ~ ~ ~ ~ ~ ~ ~ MODEL Class (ContactObject) ~ ~ ~ ~ ~ ~ ~ ~ ~
First is First!, We will pull data from Database table and put into Our Model Class (Object Class) and then in the Controller we will get it from Model and show with UI.
So, Let's Save application, and open ContactObject.h file
1. declare variables with @property to store ContactObject's data
@property (nonatomic,retain) NSString *name;
@property (nonatomic,retain) NSString *address;
@property (nonatomic,retain) NSString *phoneNO;
2. declare init method to initialize our ContactObject
-(id)initContactObjectByName:(NSString *)cName Address:(NSString *)cAddress PhoneNo:(NSString *)cPhoneNo;
So Finally our ContactObject.h file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// ContactObject.h
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import <Foundation/Foundation.h>
@interface ContactObject : NSObject
// Variables to hold the data of ContactObject.
@property (nonatomic,retain) NSString *name;
@property (nonatomic,retain) NSString *address;
@property (nonatomic,retain) NSString *phoneNO;
//Method Declaration to initialize the ContactObject.
-(id)initContactObjectByName:(NSString *)cName Address:(NSString *)cAddress PhoneNo:(NSString *)cPhoneNo;
@end
-----------------------------------------------------------------------------------------------------------------------------------------
now open ContactObject's implementation class ContactObject.m
1. add synthesize (for all declared variable in header file with @property) just below to @implementation
@synthesize name;
@synthesize address;
@synthesize phoneNO;
2. update init method body
-(id) initContactObjectByName:(NSString *)cName Address:(NSString *)cAddress PhoneNo:(NSString *)cPhoneNo{
self = [super init];
self.name = cName;
self.address = cAddress;
self.phoneNO = cPhoneNo;
return self;
}
So Finally our ContactObject.m file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// ContactObject.m
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import "ContactObject.h"
@implementation ContactObject
@synthesize name;
@synthesize address;
@synthesize phoneNO;
-(id) initContactObjectByName:(NSString *)cName Address:(NSString *)cAddress PhoneNo:(NSString *)cPhoneNo{
self = [super init];
self.name = cName;
self.address = cAddress;
self.phoneNO = cPhoneNo;
return self;
}
@end
-----------------------------------------------------------------------------------------------------------------------------------------
~ ~ ~ ~ ~ ~ ~ ~ ~ HELPER Class (DBHelper) ~ ~ ~ ~ ~ ~ ~ ~ ~
It's time to write Heart of the Application. we will write the code :-
* how to create New Database, Table, and then store data, search item, delete etc.
So, tight your seatbelt, we are going for iDrive.. ;)
open your DBHelper.h class
1. do you remember we added Sqlite lib to our project, time to import that here
#import <sqlite3.h>
2. also we will use model class here so add @class just below to import statement .
@class ContactObject;
3. create instance variables for Sqlite class and String for Database file path.
sqlite3 *db;
NSString *databasePath;
4. declare static method to initialize our database
+(DBHelper *)initializeDatabase;
5. declare instance methods for Insert, Search, delete and select all contacts info.
-(void) insertContactInfoName :(NSString *)name Address:(NSString *)address PhoneNo:(NSString *)phoneNO;
-(void) selectAllContacts;
-(ContactObject *) searchContactInfoByName :(NSString *) name;
-(void) deleteContactInfoByName:(NSString *) name;
So Finally our DBHelper.h file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// DBHelper.h
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@class ContactObject;
@interface DBHelper : NSObject{
sqlite3 *db;
NSString *databasePath;
}
+(DBHelper *)initializeDatabase;
-(void) insertContactInfoName :(NSString *)name Address:(NSString *)address PhoneNo:(NSString *)phoneNO;
-(void) selectAllContacts;
-(ContactObject *) searchContactInfoByName :(NSString *) name;
-(void) deleteContactInfoByName:(NSString *) name;
@end
-----------------------------------------------------------------------------------------------------------------------------------------
Bravo!! Bravo!!.. keep moving like a RockStar \m/
open DBHelper's implementation class DBHelper.m
1. add import statement for our model class because in header file just added @class only
#import "ContactObject.h"
2. declare static variable for DBHelper
static DBHelper *dbHelper;
3. write the body for initialize method
+(DBHelper *)initializeDatabase{
if (dbHelper == nil) {
//init method's body we will provide below
dbHelper = [[DBHelper alloc] init];
}
return dbHelper;
}
-(id) init{
self = [super init];
if (self) {
[self createDatabase];
}
return self;
}
4. now here is the method to Create new Database and Table
-(void) createDatabase {
NSArray *dirPaths;
NSString *docsDirPath;
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDirPath = [dirPaths objectAtIndex:0];
// Build the path to the database file
databasePath = [[NSString alloc] initWithString: [docsDirPath stringByAppendingPathComponent: @"contacts.db"]];
NSLog(@"database path is : %@",databasePath);
//Now create the database
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK)
{
char *errMsg;
//use below code if you want ID in your table and update furhter code
//const char *createQuery = "CREATE TABLE IF NOT EXISTS CONTACTS(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
const char *createQuery = "CREATE TABLE IF NOT EXISTS CONTACTS(NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
if (sqlite3_exec(db, createQuery, NULL, NULL, &errMsg) != SQLITE_OK)
{
NSLog(@"Failed to create table");
}
sqlite3_close(db);
} else {
NSLog(@"Failed to open/create database");
}
}
else{
NSLog(@"Database already exists");
}
}
Let me describe what we are doing in above method.
4.1 first of all we need to get the path of our device document directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDirPath = [dirPaths objectAtIndex:0];
4.2 then we create our database path (database file name is : contacts.db)
databasePath = [[NSString alloc] initWithString: [docsDirPath stringByAppendingPathComponent: @"contacts.db"]];
4.3 and finally we created our database and Table using NSFileManager using create table query
const char *createQuery = "CREATE TABLE IF NOT EXISTS CONTACTS(NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
Note: Here I have commented line for Creating Table with ID column,Coz I wanted to keep stuff simple.
you can create Table with ID and different datatypes like Text, Integer etc.
5. Now create insertContactInfoName method to Insert values in Database
-(void) insertContactInfoName:(NSString *)name Address:(NSString *)address PhoneNo:(NSString *)phoneNO{
if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK)
{
//create insertStatement
sqlite3_stmt *insertStatement;
//create insertQuery
const char *insertQuery = "INSERT INTO CONTACTS (name,address,phone) VALUES (?,?,?)";
if(sqlite3_prepare_v2(db, insertQuery, -1, &insertStatement, NULL) != SQLITE_OK)
{
NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(db));
}
// insert values
sqlite3_bind_text(insertStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insertStatement, 2, [address UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insertStatement, 3, [phoneNO UTF8String], -1, SQLITE_TRANSIENT);
if (sqlite3_step(insertStatement )== SQLITE_DONE) {
NSLog(@"Contact sucessfully saved : new item id is %lld",sqlite3_last_insert_rowid(db));
}
else{
NSLog(@"Failed to add contact");
}
sqlite3_finalize(insertStatement);
}
sqlite3_close(db);
}
Here What we did is, Open the database,Created one statement, created SQL query then executed it by Prepare command and Got the result as newly created row's Id.
Note: don't forget to open the database before doing anything and close the database when you done!!
sqlite3_close(db);
All below methods are doing SQL work in same way so keep in mind these things
-----------------------------------------------
| ⁃ Open the database |
| ⁃ Create Statement |
| ⁃ Create SQL query |
| ⁃ Execute Query |
| ⁃ Get the result |
| ⁃ Close the database |
-----------------------------------------------
6. now write selectAllContacts method body for select All contacts information
-(void) selectAllContacts{
if (sqlite3_open([databasePath UTF8String] , &db) == SQLITE_OK){
sqlite3_stmt *selectStatement;
NSString *selectAllQuery = [NSString stringWithFormat:@"SELECT * FROM CONTACTS"];
NSLog(@"select All Query is = %@",selectAllQuery);
if (sqlite3_prepare_v2(db ,[selectAllQuery UTF8String] , -1, &selectStatement, NULL) == SQLITE_OK)
{
while (sqlite3_step(selectStatement) == SQLITE_ROW)
{
char *cName = (char *) sqlite3_column_text(selectStatement, 0);
char *cAddress = (char *) sqlite3_column_text(selectStatement, 1);
char *cPhone = (char *) sqlite3_column_text(selectStatement, 2);
NSString *name = [[NSString alloc] initWithUTF8String:cName];
NSString *address = [[NSString alloc] initWithUTF8String:cAddress];
NSString *phone = [[NSString alloc] initWithUTF8String:cPhone];
NSLog(@" Name = %@, Address = %@, PhoneNO = %@", name,address,phone);
}
sqlite3_finalize(selectStatement);
}
sqlite3_close(db);
}
}
7. now write searchContactInfoByName method body for search contacts information by Name.
-(ContactObject *) searchContactInfoByName:(NSString *)name{
ContactObject *aContactObject;
if (sqlite3_open([databasePath UTF8String] , &db) == SQLITE_OK){
NSString *searchQuery = [[NSString alloc] initWithFormat:@"SELECT distinct name, address, phone FROM CONTACTS WHERE name = '%@'",name];
NSLog(@"search Query is : %@",searchQuery);
sqlite3_stmt *searchStatement;
if (sqlite3_prepare_v2(db, [searchQuery UTF8String], -1, &searchStatement, nil) == SQLITE_OK) {
while (sqlite3_step(searchStatement) == SQLITE_ROW) {
char *cName = (char *) sqlite3_column_text(searchStatement, 0);
char *cAddress = (char *) sqlite3_column_text(searchStatement, 1);
char *cPhone = (char *) sqlite3_column_text(searchStatement, 2);
NSString *name = [[NSString alloc] initWithUTF8String:cName];
NSString *address = [[NSString alloc] initWithUTF8String:cAddress];
NSString *phone = [[NSString alloc] initWithUTF8String:cPhone];
NSLog(@"We found Address = %@ Phone = %@",address,phone);
//put all results into object (our model)
aContactObject = [[ContactObject alloc] initContactObjectByName:name Address:address PhoneNo:phone];
}
sqlite3_finalize(searchStatement);
}
else{
NSLog(@" The error is %s",sqlite3_errmsg(db));
}
sqlite3_close(db);
}
return aContactObject;
}
8. at last we you write deleteContactInfoByName method body for search contacts information by Name.
-(void) deleteContactInfoByName:(NSString *)name{
if (sqlite3_open([databasePath UTF8String], &db ) == SQLITE_OK) {
sqlite3_stmt *deleteStatement;
NSString *deleteQuery = [NSString stringWithFormat:@"DELETE FROM CONTACTS WHERE name = ?"];
NSLog(@"Delete query is = %@",deleteQuery);
if (sqlite3_prepare_v2(db ,[deleteQuery UTF8String], -1, &deleteStatement, NULL) == SQLITE_OK){
//When binding parameters, index starts from 1 and not zero.
sqlite3_bind_text(deleteStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
if (SQLITE_DONE == sqlite3_step(deleteStatement)){
NSLog(@"contact deleted successfully");
}
}
else{
NSAssert1(0, @"Error while creating delete statement. '%s'", sqlite3_errmsg(db));
}
}
}
So Finally our DBHelper.m file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// DBHelper.m
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import "DBHelper.h"
#import "ContactObject.h"
@implementation DBHelper
#pragma mark - DATABASE - INITILIZE
static DBHelper *dbHelper;
+(DBHelper *)initializeDatabase{
if (dbHelper == nil) {
//init method's body we will provide below
dbHelper = [[DBHelper alloc] init];
}
return dbHelper;
}
-(id) init{
self = [super init];
if (self) {
[self createDatabase];
}
return self;
}
#pragma mark - SQLite DATABASE INFO
/*
*
* Database Name : contacts.db
* Database File Path : docsDirPath (log this string value)
* Database Table Name : CONTACTS
* Table Columns Name : NAME, ADDRESS, PHONE
*
*/
#pragma mark - DATABASE - CREATE
-(void) createDatabase {
NSArray *dirPaths;
NSString *docsDirPath;
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDirPath = [dirPaths objectAtIndex:0];
// Build the path to the database file
databasePath = [[NSString alloc] initWithString: [docsDirPath stringByAppendingPathComponent: @"contacts.db"]];
NSLog(@"database path is : %@",databasePath);
//Now create the database
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK)
{
char *errMsg;
//use below code if you want ID in your table and update furhter code
//const char *createQuery = "CREATE TABLE IF NOT EXISTS CONTACTS(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
const char *createQuery = "CREATE TABLE IF NOT EXISTS CONTACTS(NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
if (sqlite3_exec(db, createQuery, NULL, NULL, &errMsg) != SQLITE_OK)
{
NSLog(@"Failed to create table");
}
sqlite3_close(db);
} else {
NSLog(@"Failed to open/create database");
}
}
else{
NSLog(@"Database already exists");
}
}
#pragma mark - DATABASE -INSERT
-(void) insertContactInfoName:(NSString *)name Address:(NSString *)address PhoneNo:(NSString *)phoneNO{
if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK)
{
//create insertStatement
sqlite3_stmt *insertStatement;
//create insertQuery
const char *insertQuery = "INSERT INTO CONTACTS (name,address,phone) VALUES (?,?,?)";
if(sqlite3_prepare_v2(db, insertQuery, -1, &insertStatement, NULL) != SQLITE_OK)
{
NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(db));
}
// insert values
sqlite3_bind_text(insertStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insertStatement, 2, [address UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insertStatement, 3, [phoneNO UTF8String], -1, SQLITE_TRANSIENT);
if (sqlite3_step(insertStatement )== SQLITE_DONE) {
NSLog(@"Contact sucessfully saved : new item id is %lld",sqlite3_last_insert_rowid(db));
}
else{
NSLog(@"Failed to add contact");
}
sqlite3_finalize(insertStatement);
}
sqlite3_close(db);
}
#pragma mark - DATABASE -SELECT ALL DATA
-(void) selectAllContacts{
if (sqlite3_open([databasePath UTF8String] , &db) == SQLITE_OK){
sqlite3_stmt *selectStatement;
NSString *selectAllQuery = [NSString stringWithFormat:@"SELECT * FROM CONTACTS"];
NSLog(@"select All Query is = %@",selectAllQuery);
if (sqlite3_prepare_v2(db ,[selectAllQuery UTF8String] , -1, &selectStatement, NULL) == SQLITE_OK)
{
while (sqlite3_step(selectStatement) == SQLITE_ROW)
{
char *cName = (char *) sqlite3_column_text(selectStatement, 0);
char *cAddress = (char *) sqlite3_column_text(selectStatement, 1);
char *cPhone = (char *) sqlite3_column_text(selectStatement, 2);
NSString *name = [[NSString alloc] initWithUTF8String:cName];
NSString *address = [[NSString alloc] initWithUTF8String:cAddress];
NSString *phone = [[NSString alloc] initWithUTF8String:cPhone];
NSLog(@" Name = %@, Address = %@, PhoneNO = %@", name,address,phone);
}
sqlite3_finalize(selectStatement);
}
sqlite3_close(db);
}
}
#pragma mark - DATABASE - SEARCH
//this method will returns an Object which contains one person's Contact info details
-(ContactObject *) searchContactInfoByName:(NSString *)name{
ContactObject *aContactObject;
if (sqlite3_open([databasePath UTF8String] , &db) == SQLITE_OK){
NSString *searchQuery = [[NSString alloc] initWithFormat:@"SELECT distinct name, address, phone FROM CONTACTS WHERE name = '%@'",name];
NSLog(@"search Query is : %@",searchQuery);
sqlite3_stmt *searchStatement;
if (sqlite3_prepare_v2(db, [searchQuery UTF8String], -1, &searchStatement, nil) == SQLITE_OK) {
while (sqlite3_step(searchStatement) == SQLITE_ROW) {
char *cName = (char *) sqlite3_column_text(searchStatement, 0);
char *cAddress = (char *) sqlite3_column_text(searchStatement, 1);
char *cPhone = (char *) sqlite3_column_text(searchStatement, 2);
NSString *name = [[NSString alloc] initWithUTF8String:cName];
NSString *address = [[NSString alloc] initWithUTF8String:cAddress];
NSString *phone = [[NSString alloc] initWithUTF8String:cPhone];
NSLog(@"We found Address = %@ Phone = %@",address,phone);
//put all results into object (our model)
aContactObject = [[ContactObject alloc] initContactObjectByName:name Address:address PhoneNo:phone];
}
sqlite3_finalize(searchStatement);
}
else{
NSLog(@" The error is %s",sqlite3_errmsg(db));
}
sqlite3_close(db);
}
return aContactObject;
}
#pragma mark - DATABASE - DELETE
-(void) deleteContactInfoByName:(NSString *)name{
if (sqlite3_open([databasePath UTF8String], &db ) == SQLITE_OK) {
sqlite3_stmt *deleteStatement;
NSString *deleteQuery = [NSString stringWithFormat:@"DELETE FROM CONTACTS WHERE name = ?"];
NSLog(@"Delete query is = %@",deleteQuery);
if (sqlite3_prepare_v2(db ,[deleteQuery UTF8String], -1, &deleteStatement, NULL) == SQLITE_OK){
//When binding parameters, index starts from 1 and not zero.
sqlite3_bind_text(deleteStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
if (SQLITE_DONE == sqlite3_step(deleteStatement)){
NSLog(@"contact deleted successfully");
}
}
else{
NSAssert1(0, @"Error while creating delete statement. '%s'", sqlite3_errmsg(db));
}
}
}
@end
-----------------------------------------------------------------------------------------------------------------------------------------
~ ~ ~ ~ ~ ~ ~ ~ ~ Controller Class (ViewController) ~ ~ ~ ~ ~ ~ ~ ~ ~
We are about to Accomplish our goal, So, stay tune with RDC.
This time we use all the hard-work we have done in Model or Helper classes.
Open ViewController.h file
1. we need to use Model class instance hence add @class for that
@class ContactObject;
2. add protocol UITextFieldDelegate because we will enable Keypad Go back when user hit Return Key on any TextFiled
@interface ViewController : UIViewController<UITextFieldDelegate>
2. You can see some IBOutlets and IBActions methods we already Added By Xcode, we did it From UI.
3. declare property for our Model class instance
@property (nonatomic, retain) ContactObject *aContactObject;
4. declare property of TextField class instance, we will use this, for which TextFiled is active for sending keypad back.
@property (nonatomic, retain) UITextField *active_text;
Good! save this File.
So Finally our ViewController.h file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// ViewController.h
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import <UIKit/UIKit.h>
@class ContactObject;
@interface ViewController : UIViewController<UITextFieldDelegate>
@property (weak, nonatomic) IBOutlet UITextField *nameTextField;
@property (weak, nonatomic) IBOutlet UITextField *addressTextField;
@property (weak, nonatomic) IBOutlet UITextField *phoneNoTextField;
- (IBAction)saveContactInfo:(id)sender;
- (IBAction)viewAllContactsInfo:(id)sender;
- (IBAction)searchContactInfo:(id)sender;
- (IBAction)deleteContactInfo:(id)sender;
@property (nonatomic, retain) ContactObject *aContactObject;
@property (nonatomic, retain) UITextField *active_text;
@end
-----------------------------------------------------------------------------------------------------------------------------------------
Now come to ViewController.m file , open it
1. add #import statement for Model class and DBHelper, we are going to use them in this
#import "DBHelper.h"
#import "ContactObject.h"
2. add synthesize (for all declared variable in header file with @property) just below to @implementation
@synthesize nameTextField;
@synthesize addressTextField;
@synthesize phoneNoTextField;
@synthesize aContactObject;
@synthesize active_text;
2. update the body of saveContactInfo method
- (IBAction)saveContactInfo:(id)sender {
NSString *name = nameTextField.text;
NSString *address = addressTextField.text;
NSString *phone = phoneNoTextField.text;
if ([name length] >1 && [address length]>1 && [phone length]>1) {
[[DBHelper initializeDatabase] insertContactInfoName:name Address:address PhoneNo:phone];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@""
message:@"please! enter text" delegate:self
cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
}
}
3. update the body of viewAllContactsInfo method
- (IBAction)viewAllContactsInfo:(id)sender {
//for all the results we printed on console in DBHelper's file method itself
[[DBHelper initializeDatabase] selectAllContacts];
}
4. update the body of searchContactInfo method
- (IBAction)searchContactInfo:(id)sender {
NSString *name = nameTextField.text;
if ([name length] >1) {
aContactObject =[[DBHelper initializeDatabase] searchContactInfoByName:name];
NSString *name = aContactObject.name;
NSString *address = aContactObject.address;
NSString *phone = aContactObject.phoneNO;
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Search Result"
message:[NSString stringWithFormat:@"We found Name : %@, Address : %@, Phone No : %@",name,address,phone]
delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@""
message:@"please! enter text" delegate:self cancelButtonTitle:@"OK"
otherButtonTitles:nil, nil];
[alert show];
}
}
5. update the body of deleteContactInfo method
- (IBAction)deleteContactInfo:(id)sender {
if ([nameTextField.text length] >1 ) {
[[DBHelper initializeDatabase] deleteContactInfoByName:nameTextField.text];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"" message:@"please! enter text"
delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
}
}
6. now we will implement UITextFiled's Delegate method to handle Keypad Go Back when user Hit Return key
-(void)textFieldDidBeginEditing:(UITextField *)textField{
self.active_text = textField;
}
-(BOOL)textFieldShouldReturn:(UITextField *)textField{
[textField resignFirstResponder];
return TRUE;
}
So Finally our ViewController.m file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// ViewController.m
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import "ViewController.h"
#import "DBHelper.h"
#import "ContactObject.h"
@interface ViewController ()
@end
@implementation ViewController
@synthesize nameTextField;
@synthesize addressTextField;
@synthesize phoneNoTextField;
@synthesize aContactObject;
@synthesize active_text;
#pragma mark - ViewController's LifeCycle methods
- (void)viewDidLoad
{
[super viewDidLoad];
//optional : if you having problem with TextField's Delegate Hookup in UI
//nameTextField.delegate =self;
//addressTextField.delegate=self;
//phoneNoTextField.delegate=self;
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
}
#pragma mark - Buttons click methods
- (IBAction)saveContactInfo:(id)sender {
NSString *name = nameTextField.text;
NSString *address = addressTextField.text;
NSString *phone = phoneNoTextField.text;
if ([name length] >1 && [address length]>1 && [phone length]>1) {
[[DBHelper initializeDatabase] insertContactInfoName:name Address:address PhoneNo:phone];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@""
message:@"please! enter text" delegate:self
cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
}
}
- (IBAction)viewAllContactsInfo:(id)sender {
//for all the results we printed on console in DBHelper's file method itself
[[DBHelper initializeDatabase] selectAllContacts];
}
- (IBAction)searchContactInfo:(id)sender {
NSString *name = nameTextField.text;
if ([name length] >1) {
aContactObject =[[DBHelper initializeDatabase] searchContactInfoByName:name];
NSString *name = aContactObject.name;
NSString *address = aContactObject.address;
NSString *phone = aContactObject.phoneNO;
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Search Result"
message:[NSString stringWithFormat:@"We found Name : %@, Address : %@, Phone No : %@",name,address,phone]
delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@""
message:@"please! enter text" delegate:self cancelButtonTitle:@"OK"
otherButtonTitles:nil, nil];
[alert show];
}
}
- (IBAction)deleteContactInfo:(id)sender {
if ([nameTextField.text length] >1 ) {
[[DBHelper initializeDatabase] deleteContactInfoByName:nameTextField.text];
nameTextField.text =@"";
addressTextField.text=@"";
phoneNoTextField.text=@"";
}
else{
UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"" message:@"please! enter text"
delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil, nil];
[alert show];
}
}
#pragma mark - UITextField Delegate methods for Keypad Return key
-(void)textFieldDidBeginEditing:(UITextField *)textField{
self.active_text = textField;
}
-(BOOL)textFieldShouldReturn:(UITextField *)textField{
[textField resignFirstResponder];
return TRUE;
}
@end
-----------------------------------------------------------------------------------------------------------------------------------------
~ ~ ~ ~ ~ ~ ~ ~ ~ Application Delegate Class (AppDelegate) ~ ~ ~ ~ ~ ~ ~ ~ ~
Make sure your AppDelegate file code should be default
Finally our AppDelegate.h file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// AppDelegate.h
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import <UIKit/UIKit.h>
@class ViewController;
@interface AppDelegate : UIResponder <UIApplicationDelegate>
@property (strong, nonatomic) UIWindow *window;
@property (strong, nonatomic) ViewController *viewController;
@end
-----------------------------------------------------------------------------------------------------------------------------------------
Finally our AppDelegate.m file look like
-----------------------------------------------------------------------------------------------------------------------------------------
//
// AppDelegate.m
// SQLiteDemo
//
// Created by RDC on 3/7/13.
// Copyright (c) 2013 RDCWorld. All rights reserved.
//
#import "AppDelegate.h"
#import "ViewController.h"
@implementation AppDelegate
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
self.viewController = [[ViewController alloc] initWithNibName:@"ViewController" bundle:nil];
self.window.rootViewController = self.viewController;
[self.window makeKeyAndVisible];
return YES;
}
//for now leave rest all empty methods..
@end
-----------------------------------------------------------------------------------------------------------------------------------------
Okay wrap it up this application. let's Run it.
Here is the output
You can Find the ViewAll results in Console
Cheers!! we did it.
You can find complete project source code zip file here : SQLiteDemo.zip (81.24 KB)
I Would love to here your thoughts !!
Hi RDC,
ReplyDeleteCould you please tell me the major differences between this SQlite example and Core Data Demo examples.Because both are looking same. so...
plz don't mind if there is anything wrong in my question.
ReplyDelete