SkDatabase

/

//  SKDatabase.h

//  Version 1.1

//

//  Created by Shannon Appelcline on 9/11/08.

//  Copyright 2008 __MyCompanyName__. All rights reserved.

//

#import <UIKit/UIKit.h>

#import <sqlite3.h>

@protocol SKDatabaseDelegate <NSObject>

@optional

– (void)databaseTableWasUpdated:(NSString *)table;

@end

@interface SKDatabase : NSObject {

id<SKDatabaseDelegate> delegate;

sqlite3 *dbh;

BOOL dynamic;

}

@property id<SKDatabaseDelegate> delegate;

@property sqlite3 *dbh;

@property BOOL dynamic;

– (id)initWithFile:(NSString *)dbFile;

– (id)initWithDynamicFile:(NSString *)dbFile;

– (void)close;

– (sqlite3_stmt *)prepare:(NSString *)sql;

– (id)lookupColForSQL:(NSString *)sql;

– (NSDictionary *)lookupRowForSQL:(NSString *)sql;

– (NSArray *)lookupAllForSQL:(NSString *)sql;

– (NSMutableArray *)selectAll_1:(NSString *)where forTable:(NSString *)table;

– (int)lookupCountWhere:(NSString *)where forTable:(NSString *)table;

– (int)lookupMax:(NSString *)key Where:(NSString *)where forTable:(NSString *)table;

– (int)lookupSum:(NSString *)key Where:(NSString *)where forTable:(NSString *)table;

– (void)insertArray:(NSArray *)dbData forTable:(NSString *)table;

– (void)insertDictionary:(NSDictionary *)dbData forTable:(NSString *)table;

– (void)updateArray:(NSArray *)dbData forTable:(NSString *)table;

– (void)updateArray:(NSArray *)dbData forTable:(NSString *)table where:(NSString *)where;

– (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table;

– (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table where:(NSString *)where;

– (void)updateSQL:(NSString *)sql forTable:(NSString *)table;

– (void)deleteWhere:(NSString *)where forTable:(NSString *)table;

– (BOOL)runDynamicSQL:(NSString *)sql forTable:(NSString *)table;

– (void)deleteAllRowsForTable:(NSString *)table;

@end

#################################################

//

//  SKDatabase.m

//  Version 1.1

//

//  Created by Shannon Appelcline on 9/11/08.

//  Copyright 2008 __MyCompanyName__. All rights reserved.

//

#import “SKDatabase.h”

@implementation SKDatabase

@synthesize delegate;

@synthesize dbh;

@synthesize dynamic;

// Two ways to init: one if you’re just SELECTing from a database, one if you’re UPDATing

// and or INSERTing

– (id)initWithFile:(NSString *)dbFile {

if (self = [super init]) {

NSString *paths = [[NSBundle mainBundle] resourcePath];

NSString *path = [paths stringByAppendingPathComponent:dbFile];

int result = sqlite3_open([path UTF8String], &dbh);

NSAssert1(SQLITE_OK == result, NSLocalizedStringFromTable(@”Unable to open the sqlite database (%@).”, @”Database”, @””), [NSString stringWithUTF8String:sqlite3_errmsg(dbh)]);

self.dynamic = NO;

}

return self;

}

– (id)initWithDynamicFile:(NSString *)dbFile {

if (self = [super init]) {

NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *docDir = [docPaths objectAtIndex:0];

NSString *docPath = [docDir stringByAppendingPathComponent:dbFile];

NSFileManager *fileManager = [NSFileManager defaultManager];

if (![fileManager fileExistsAtPath:docPath]) {

NSString *origPaths = [[NSBundle mainBundle] resourcePath];

NSString *origPath = [origPaths stringByAppendingPathComponent:dbFile];

NSError *error;

int success = [fileManager copyItemAtPath:origPath toPath:docPath error:&error];

NSAssert1(success,[NSString stringWithString:@”Failed to copy database into dynamic location”],error);

}

int result = sqlite3_open([docPath UTF8String], &dbh);

NSAssert1(SQLITE_OK == result, NSLocalizedStringFromTable(@”Unable to open the sqlite database (%@).”, @”Database”, @””), [NSString stringWithUTF8String:sqlite3_errmsg(dbh)]);

self.dynamic = YES;

}

return self;

}

// Users should never need to call prepare

– (sqlite3_stmt *)prepare:(NSString *)sql {

const char *utfsql = [sql UTF8String];

sqlite3_stmt *statement;

if (sqlite3_prepare([self dbh],utfsql,-1,&statement,NULL) == SQLITE_OK) {

return statement;

} else {

return 0;

}

}

// Three ways to lookup results: for a variable number of responses, for a full row

// of responses, or for a singular bit of data

– (NSArray *)lookupAllForSQL:(NSString *)sql {

sqlite3_stmt *statement;

id result;

NSMutableArray *thisArray = [NSMutableArray arrayWithCapacity:4];

if ((statement = [self prepare:sql])) {

while (sqlite3_step(statement) == SQLITE_ROW) {

NSMutableDictionary *thisDict = [NSMutableDictionary dictionaryWithCapacity:4];

for (int i = 0 ; i < sqlite3_column_count(statement) ; i++) {

if (sqlite3_column_decltype(statement,i) != NULL &&

strcasecmp(sqlite3_column_decltype(statement,i),“Boolean”) == 0) {

result = [NSNumber numberWithBool😦BOOL)sqlite3_column_int(statement,i)];

} else if (sqlite3_column_type(statement, i) == SQLITE_TEXT) {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,i)];

} else if (sqlite3_column_type(statement,i) == SQLITE_INTEGER) {

result = [NSNumber numberWithInt😦int)sqlite3_column_int(statement,i)];

} else if (sqlite3_column_type(statement,i) == SQLITE_FLOAT) {

result = [NSNumber numberWithFloat😦float)sqlite3_column_double(statement,i)];

} else {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,i)];

}

if (result) {

[thisDict setObject:result

forKey:[NSString stringWithUTF8String:sqlite3_column_name(statement,i)]];

}

}

[thisArray addObject:[NSDictionary dictionaryWithDictionary:thisDict]];

}

}

sqlite3_finalize(statement);

return thisArray;

}

– (NSDictionary *)lookupRowForSQL:(NSString *)sql {

sqlite3_stmt *statement;

id result;

NSMutableDictionary *thisDict = [NSMutableDictionary dictionaryWithCapacity:4];

if ((statement = [self prepare:sql])) {

if (sqlite3_step(statement) == SQLITE_ROW) {

for (int i = 0 ; i < sqlite3_column_count(statement) ; i++) {

if (strcasecmp(sqlite3_column_decltype(statement,i),“Boolean”) == 0) {

result = [NSNumber numberWithBool😦BOOL)sqlite3_column_int(statement,i)];

} else if (sqlite3_column_type(statement, i) == SQLITE_TEXT) {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,i)];

} else if (sqlite3_column_type(statement,i) == SQLITE_INTEGER) {

result = [NSNumber numberWithInt😦int)sqlite3_column_int(statement,i)];

} else if (sqlite3_column_type(statement,i) == SQLITE_FLOAT) {

result = [NSNumber numberWithFloat😦float)sqlite3_column_double(statement,i)];

} else {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,i)];

}

if (result) {

[thisDict setObject:result

forKey:[NSString stringWithUTF8String:sqlite3_column_name(statement,i)]];

}

}

}

}

sqlite3_finalize(statement);

return thisDict;

}

– (id)lookupColForSQL:(NSString *)sql {

sqlite3_stmt *statement;

id result;

if ((statement = [self prepare:sql])) {

if (sqlite3_step(statement) == SQLITE_ROW) {

if (strcasecmp(sqlite3_column_decltype(statement,0),“Boolean”) == 0) {

result = [NSNumber numberWithBool😦BOOL)sqlite3_column_int(statement,0)];

} else if (sqlite3_column_type(statement, 0) == SQLITE_TEXT) {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,0)];

} else if (sqlite3_column_type(statement,0) == SQLITE_INTEGER) {

result = [NSNumber numberWithInt😦int)sqlite3_column_int(statement,0)];

} else if (sqlite3_column_type(statement,0) == SQLITE_FLOAT) {

result = [NSNumber numberWithDouble😦double)sqlite3_column_double(statement,0)];

} else {

result = [NSString stringWithUTF8String😦char *)sqlite3_column_text(statement,0)];

}

}

}

sqlite3_finalize(statement);

return result;

}

// Select All

-(NSMutableArray *)selectAll_1:(NSString *)where forTable:(NSString *)table

{

NSString *sql = [NSString stringWithFormat:@”SELECT * FROM %@ WHERE %@”,table,where];    

sqlite3_stmt *statement;

if ((statement = [self prepare:sql])) {

NSMutableDictionary *dict;

NSMutableArray *DataArr = [[NSMutableArray alloc] init];

while(sqlite3_step(statement) == SQLITE_ROW)

{

dict = [[NSMutableDictionary alloc] init];

int ID = (sqlite3_column_int(statement,0));

NSString *nm = [NSString stringWithUTF8String😦const char *)sqlite3_column_text(statement,1)];

// NSString *nm1 = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(statement,0)];

[dict setObject:[NSString stringWithFormat:@”%d”, ID] forKey:@”CatId”];

[dict setObject:nm forKey:@”CatName”];

[DataArr addObject:dict];

//[dict release];

}

return DataArr;

}

return nil;

}

// Simple use of COUNTS, MAX, etc.

– (int)lookupCountWhere:(NSString *)where forTable:(NSString *)table {

int tableCount = 0;

NSString *sql = [NSString stringWithFormat:@”SELECT COUNT(*) FROM %@ WHERE %@”,

table,where];    

sqlite3_stmt *statement;

if ((statement = [self prepare:sql]))

    {

if (sqlite3_step(statement) == SQLITE_ROW) {

tableCount = sqlite3_column_int(statement,0);

}

}

sqlite3_finalize(statement);

return tableCount;

}

– (int)lookupMax:(NSString *)key Where:(NSString *)where forTable:(NSString *)table {

int tableMax = 0;

NSString *sql = [NSString stringWithFormat:@”SELECT MAX(%@) FROM %@ WHERE %@”,

key,table,where];    

sqlite3_stmt *statement;

if ((statement = [self prepare:sql])) {

if (sqlite3_step(statement) == SQLITE_ROW) {

tableMax = sqlite3_column_int(statement,0);

}

}

sqlite3_finalize(statement);

return tableMax;

}

– (int)lookupSum:(NSString *)key Where:(NSString *)where forTable:(NSString *)table {

int tableSum = 0;

NSString *sql = [NSString stringWithFormat:@”SELECT SUM(%@) FROM %@ WHERE %@”,

key,table,where];    

sqlite3_stmt *statement;

if ((statement = [self prepare:sql])) {

if (sqlite3_step(statement) == SQLITE_ROW) {

tableSum = sqlite3_column_int(statement,0);

}

}

sqlite3_finalize(statement);

return tableSum;

}

// INSERTing and UPDATing

– (void)insertArray:(NSArray *)dbData forTable:(NSString *)table {

NSLog(@”%@”,dbData);

for(int i=0;i<[dbData count];i++)

{

NSDictionary *dict=[dbData objectAtIndex:i];

NSMutableString *sql = [NSMutableString stringWithCapacity:16];

[sql appendFormat:@”INSERT INTO %@ (“,table];

NSArray *dataKeys = [dict allKeys];

for (int i = 0 ; i < [dataKeys count] ; i++) {

[sql appendFormat:@”%@”,[dataKeys objectAtIndex:i]];

if (i + 1 < [dataKeys count]) {

[sql appendFormat:@”, “];

}

}

[sql appendFormat:@”) VALUES(“];

for (int i = 0 ; i < [dataKeys count] ; i++) {

//if ([[dict objectForKey:[dataKeys objectAtIndex:i]] intValue]) {

// [sql appendFormat:@”%@”,[dict objectForKey:[dataKeys objectAtIndex:i]]];

//} else {

[sql appendFormat:@”‘%@'”,[dict objectForKey:[dataKeys objectAtIndex:i]]];

//}

if (i + 1 < [dict count]) {

[sql appendFormat:@”, “];

}

}

[sql appendFormat:@”)”];

NSLog(sql);

[self runDynamicSQL:sql forTable:table];

}

}

– (void)insertDictionary:(NSDictionary *)dbData forTable:(NSString *)table {

NSMutableString *sql = [NSMutableString stringWithCapacity:16];

[sql appendFormat:@”INSERT INTO %@ (“,table];

NSArray *dataKeys = [dbData allKeys];

for (int i = 0 ; i < [dataKeys count] ; i++) {

[sql appendFormat:@”%@”,[dataKeys objectAtIndex:i]];

if (i + 1 < [dbData count]) {

[sql appendFormat:@”, “];

}

}

[sql appendFormat:@”) VALUES(“];

for (int i = 0 ; i < [dataKeys count] ; i++) {

//NSString *tempStr = [dbData objectForKey:[dataKeys objectAtIndex:i]];

//int temp = [tempStr intValue];

//if (temp)

//{

//[sql appendFormat:@”%@”,[dbData objectForKey:[dataKeys objectAtIndex:i]]];

//} else {

[sql appendFormat:@”‘%@'”,[dbData objectForKey:[dataKeys objectAtIndex:i]]];

//}

if (i + 1 < [dbData count]) {

[sql appendFormat:@”, “];

}

}

[sql appendFormat:@”)”];

NSLog(sql);

[self runDynamicSQL:sql forTable:table];

}

– (void)updateArray:(NSArray *)dbData forTable:(NSString *)table {

[self updateArray:dbData forTable:table where:NULL];

}

– (void)updateArray:(NSArray *)dbData forTable:(NSString *)table where:(NSString *)where {

NSMutableString *sql = [NSMutableString stringWithCapacity:16];

[sql appendFormat:@”UPDATE %@ SET “,table];

for (int i = 0 ; i < [dbData count] ; i++) {

if ([[[dbData objectAtIndex:i] objectForKey:@”value”] intValue]) {

[sql appendFormat:@”%@=%@”,

[[dbData objectAtIndex:i] objectForKey:@”key”],

[[dbData objectAtIndex:i] objectForKey:@”value”]];

} else {

[sql appendFormat:@”%@=’%@'”,

[[dbData objectAtIndex:i] objectForKey:@”key”],

[[dbData objectAtIndex:i] objectForKey:@”value”]];

}

if (i + 1 < [dbData count]) {

[sql appendFormat:@”, “];

}

}

if (where != NULL) {

[sql appendFormat:@” WHERE %@”,where];

} else {

[sql appendFormat:@” WHERE 1″,where];

}

[self runDynamicSQL:sql forTable:table];

}

– (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table {

[self updateDictionary:dbData forTable:table where:NULL];

}

– (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table where:(NSString *)where {

NSMutableString *sql = [NSMutableString stringWithCapacity:16];

[sql appendFormat:@”UPDATE %@ SET “,table];

NSArray *dataKeys = [dbData allKeys];

for (int i = 0 ; i < [dataKeys count] ; i++) {

// if ([[dbData objectForKey:[dataKeys objectAtIndex:i]] intValue]) {

// [sql appendFormat:@”%@=’%@'”,

// [dataKeys objectAtIndex:i],

// [dbData objectForKey:[dataKeys objectAtIndex:i]]];

// } else {

[sql appendFormat:@”%@=’%@'”,

[dataKeys objectAtIndex:i],

[dbData objectForKey:[dataKeys objectAtIndex:i]]];

if (i + 1 < [dbData count]) {

[sql appendFormat:@”, “];

}

}

if (where != NULL) {

[sql appendFormat:@” WHERE %@”,where];

}

NSLog(@”%@”,sql);

[self runDynamicSQL:sql forTable:table];

}

– (void)updateSQL:(NSString *)sql forTable:(NSString *)table {

[self runDynamicSQL:sql forTable:table];

}

– (void)deleteWhere:(NSString *)where forTable:(NSString *)table {

NSString *sql = [NSString stringWithFormat:@”DELETE FROM %@ WHERE %@”,

table,where];

    

    NSLog(@”%@”,sql);

[self runDynamicSQL:sql forTable:table];

}

– (void)deleteAllRowsForTable:(NSString *)table {

NSString *sql = [NSString stringWithFormat:@”DELETE FROM %@”,table];

[self runDynamicSQL:sql forTable:table];

}

// INSERT/UPDATE/DELETE Subroutines

– (BOOL)runDynamicSQL:(NSString *)sql forTable:(NSString *)table {

int result;

NSAssert1(self.dynamic == 1,[NSString stringWithString:@”Tried to use a dynamic function on a static database”],NULL);

sqlite3_stmt *statement;

if ((statement = [self prepare:sql])) {

result = sqlite3_step(statement);

    }

sqlite3_finalize(statement);

if (result)

{

if (self.delegate != NULL && [self.delegate respondsToSelector:@selector(databaseTableWasUpdated:)])

{

[delegate databaseTableWasUpdated:table];

}

return YES;

} else {

return NO;

}

}

// requirements for closing things down

– (void)dealloc {

[self close];

//[delegate release];

//[super dealloc];

}

– (void)close {

if (dbh) {

sqlite3_close(dbh);

}

}

@end

//

//  ViewController.h

//  SKDATABASE

//

//  Created by Moweb_11 on 06/01/15.

//  Copyright (c) 2015 Moweb_11. All rights reserved.

//

#import <UIKit/UIKit.h>

@interface ViewController : UIViewController{

    

    

    IBOutlet UITextField *txtId;

    IBOutlet UITextField *txtNm;

    IBOutlet UITextField *txtAge;

    

    IBOutlet UITableView *tblData;

    

    NSArray *arryData;

}

– (IBAction)mthSave:(id)sender;

@end

//

//  ViewController.m

//  SKDATABASE

//

//  Created by Moweb_11 on 06/01/15.

//  Copyright (c) 2015 Moweb_11. All rights reserved.

//

#import “ViewController.h”

#import “AppDelegate.h”

#import “SKDatabase.h”

@interface ViewController ()

@end

@implementation ViewController

– (void)viewDidLoad {

    [super viewDidLoad];

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

    

    

    

}

– (IBAction)mthSave:(id)sender{

    

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

    

    // Insert record

   // NSString *queryInsert=[NSString stringWithFormat:@”insert into tblEmp values(‘%@’, ‘%@’, ‘%@’)”, txtId.text, txtNm.text,txtAge.text];

    

    

    NSMutableDictionary *dictdata=[[NSMutableDictionary alloc]init];

    [dictdata setObject:txtId.text forKey:@”ID”];

    [dictdata setObject:txtNm.text forKey:@”Name”];

    [dictdata setObject:txtAge.text forKey:@”Age”];

    

    NSMutableArray  *arryData1 = [[NSMutableArray alloc] initWithObjects:dictdata, nil];

    

    [appDelegate.objSkData insertArray:arryData1 forTable:@”tblEmp”];

    

    

     arryData=[appDelegate.objSkData lookupAllForSQL:@”Select * from tblEmp”];

    NSLog(@”DATA%@”,arryData);

    [tblData reloadData];

}

#pragma mark – UITableView method implementation

-(NSInteger)numberOfSectionsInTableView:(UITableView *)tableView{

    return 1;

}

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

    return [arryData count];

}

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

    // Dequeue the cell.

    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:@”idCellRecord”];

    

    if (cell==nil)

    {

        cell=[[UITableViewCell alloc]initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:@”idCellRecord”];

    }

   // NSInteger indexOfFirstname = [self.dbManager.arrColumnNames indexOfObject:@”ID”];

   // NSInteger indexOfLastname = [self.dbManager.arrColumnNames indexOfObject:@”Name”];

    //NSInteger indexOfAge = [self.dbManager.arrColumnNames indexOfObject:@”Age”];

    

    // Set the loaded data to the appropriate cell labels.

    cell.textLabel.text = [NSString stringWithFormat:@”%@”,[[arryData objectAtIndex:indexPath.row] objectForKey:@”Age”]];

    

    cell.detailTextLabel.text = [NSString stringWithFormat:@”%@”,[[arryData objectAtIndex:indexPath.row] objectForKey:@”ID”]];

    cell.detailTextLabel.textColor = [UIColor blackColor];

    tableView.backgroundColor= [UIColor redColor];

    cell.backgroundColor = [UIColor redColor];

    return cell;

}

-(void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath{

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

    if (editingStyle == UITableViewCellEditingStyleDelete) {

        // Delete the selected record.

        // Find the record ID.

        int recordIDToDelete = [[[arryData objectAtIndex:indexPath.row] objectForKey:@”ID”] intValue];

        

        // Prepare the query.

        NSString *query = [NSString stringWithFormat:@”ID=%d”, recordIDToDelete];

        

        // Execute the query.

        //[self.dbManager executeQuery:query];

        

        [appDelegate.objSkData deleteWhere:query forTable:@”tblEmp”];

        

    

        arryData=[[NSArray alloc]init];

        

        

        arryData=[appDelegate.objSkData lookupAllForSQL:@”Select * from tblEmp”];

        // Reload the table view.

        [tblData reloadData];

    }

}

-(CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath{

    return 60.0;

}

– (void)didReceiveMemoryWarning {

    [super didReceiveMemoryWarning];

    // Dispose of any resources that can be recreated.

}

@end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s