Hello Everyone,
This tutorial is about saving and retrieving and deleting data from Sqlite Database in an iOS application.
To get this done we need the following steps to be followed:
1. We need to crate a database file from Sqlite manager add-on in Mozilla Firefox.
2. After creating the Database we need to create an Sqlite file from Sqlite manager add-on in Mozilla Firefox.
3. After creating the Sqlite file, we need to create a table and add the columns we need to perform these operations that we intent to do.
4. After that we need to drag and drop the Sqlite file in to our x-code project.
5. After that add libsqlite3.dylib and libsqlite3.0.dylib to the x-code project
6. When coming to the coding part we need a NSObject Class where we create methods to perform these operations and then call these methods in appropriate classes that we need. Name the class SqlDatabase .h and .m respectively.
Now Let us see the coding part that performs these operations.
Our SqlDatabase.h file looks like this
import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface SqlDatabase : NSObject
{
}
+(NSInteger)insertData:(NSMutableArray *)dataArr;
+(NSMutableArray *)getData;
+(void)tablerowsDelete:(NSString *)name;
@end
In SqlDatabase.m we need to implement the code for saving and retrieving data
Our SqlDatabase.m file looks like this
#import “SqlDatabase.h”
sqlite3 *dataBase=nil;
NSString *dbPath=nil;
@implementation SqlDatabase
// checking if the sqlite file exists in the Document directory and creating it if not created already. //
+(NSString *)getDataBasePath
{
NSArray *docDir = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *DBfolder = [NSString stringWithFormat:@”%@/AllDataBases”,[docDir objectAtIndex:0]];
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:DBfolder]) {
[fileManager createDirectoryAtPath:DBfolder withIntermediateDirectories:YES attributes:nil error:nil];
}
dbPath = [DBfolder stringByAppendingPathComponent:@”/Database.sqlite”];
if (![fileManager fileExistsAtPath:dbPath]) {
[fileManager copyItemAtPath:[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@”Database.sqlite”] toPath:dbPath error:nil];
}
return dbPath;
}
// Inserting Data into Sqlite Database //
+(NSInteger)insertData:(NSMutableArray *)dataArr
{
dbPath=[self getDataBasePath];
if (sqlite3_open([dbPath UTF8String], &dataBase) == SQLITE_OK)
{
sqlite3_stmt *statement=nil;
NSString *name = [dataArr objectAtIndex:0] ;
NSString *sex = [dataArr objectAtIndex:1];
NSString *age = [dataArr objectAtIndex:2];
NSString *nationality = [dataArr objectAtIndex:3];
NSString *phone = [dataArr objectAtIndex:4];
NSString *occupation = [dataArr objectAtIndex:5];
if(statement == nil)
{
const char *sql = “INSERT INTO myDetailTable(Name,Sex,Age,Nationality,Phone,Occupation) VALUES(?,?,?,?,?,?)”;
if(sqlite3_prepare_v2(dataBase, sql, -1, &statement, NULL) != SQLITE_OK)
{
NSUInteger i=sqlite3_last_insert_rowid(dataBase);
sqlite3_close(dataBase);
return i;
}
sqlite3_bind_text(statement, 1,name==nil?””:[name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 2,sex==nil?””:[sex UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 3,age==nil?””:[age UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 4,nationality==nil?””:[nationality UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 5,phone==nil?””:[phone UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 6,occupation==nil?””:[occupation UTF8String], -1, SQLITE_TRANSIENT);
int k=sqlite3_step(statement);
if(SQLITE_DONE !=k )
{
//NSAssert(0, @”Error while inserting data.);
return -1;
}
sqlite3_finalize(statement);
}
}
NSUInteger i=sqlite3_last_insert_rowid(dataBase);
sqlite3_close(dataBase);
return i;
}
// Retrieving data from Sqlite Database //
+(NSMutableArray *)getData;
{
NSMutableArray *temp = [[NSMutableArray alloc] init];
dbPath=[self getDataBasePath];
NSMutableDictionary *item;
if (sqlite3_open([dbPath UTF8String], &dataBase) == SQLITE_OK)
{
NSString *qs=@”select * from myDetailTable”;
const char *sql=[qs UTF8String];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(dataBase, sql, -1, &selectstmt, NULL) == SQLITE_OK)
{
while(sqlite3_step(selectstmt) == SQLITE_ROW)
{
item=[[NSMutableDictionary alloc] init];
[item setObject:[NSString stringWithUTF8String: (char *)sqlite3_column_text(selectstmt, 0)] forKey:@”name”];
[item setObject:[NSString stringWithUTF8String: (char *)sqlite3_column_text(selectstmt, 1)] forKey:@”sex”];
[item setObject:[NSString stringWithUTF8String: (char *)sqlite3_column_text(selectstmt, 2)] forKey:@”age”];
[item setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)] forKey:@”nationality”];
[item setObject:[NSString stringWithUTF8String:(char*)sqlite3_column_text(selectstmt, 4)] forKey:@”phone”];
[item setObject:[NSString stringWithUTF8String:(char*)sqlite3_column_text(selectstmt, 5)] forKey:@”occupation”];
}
[temp addObject:item];
sqlite3_finalize(selectstmt);
}
}
sqlite3_close(dataBase);
return temp;
}
// Deleting Table rows from Table in Sqlite Database //
+(void)tablerowsDelete:(NSString *)name
{
NSString *dbPath3=[self getDataBasePath];
if (sqlite3_open([dbPath3 UTF8String], &dataBase) == SQLITE_OK)
{
NSString *qs=[NSString stringWithFormat:@”Delete from myDetailTable where Fullname like ‘%@'”,name];
const char *sql=[qs UTF8String];
sqlite3_stmt *selectstmt;
if (sqlite3_prepare_v2(dataBase, sql, -1, &selectstmt, nil) != SQLITE_OK)
{
/**this one will execute when there is error in your query**/
}
else
{
/************just add this one****************/
sqlite3_step(selectstmt);
}
sqlite3_finalize(selectstmt);
}
sqlite3_close(dataBase);
}
Now in the ViewController.m we need to import the NSObject class.
Then Our ViewController.m looks like
#import “ViewController.h”
#import “SqlDatabase.h” // importing the SqlDatabase Class
@interface ViewController ()
{
NSMutableArray *insertArray; // array for inserting the data into Sqlite database
NSMutableArray *fetchingArray; // array for retrieving the data from Sqlite database
}
@end
@implementation ViewController
– (void)viewDidLoad
{
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
fetchingArray=[[NSMutableArray alloc]init];
insertArray=[[NSMutableArray alloc]initWithObjects:@”KiranKumar”,@”Male”,@”26″,@”Indian”,@”08569884750″,@”Engineer”, nil];
// This line indicates the calling of inseting data method in SqlDatabase class which performs the insertion of data into Sqlite Database File
[SqlDatabase insertData:insertArray];
// This line indicates the calling of retrieving data method in SqlDatabase class which performs the retrieving of data from Sqlite Database File
fetchingArray= [SqlDatabase getData];
// This line indicates the calling of deleting data method in SqlDatabase class which performs the deleting of data from Sqlite Database File
// [SqlDatabase tablerowsDelete:@”Name”];
}