(49) Sqlite (create table and retrieve data from sqlite database and display in tableview)Reading data from DB. ADD/Update/Delete Operation

Student.h

#import "sqlite3.h"

@property (nonatomic, readwrite) NSInteger  StudentId,

                                            Age;

@property (nonatomic, retain) NSString  *Name,

                                        *Perc;

+ (NSMutableArray *)readRecords;

+ (NSInteger)addRecord:(Student *)sObj;

+ (BOOL)editRecord:(Student *)sObj;

+ (BOOL)deleteRecord:(Student *)sObj;

Student.m

@synthesize StudentId,

            Age,

            Name,

            Perc;

+ (NSMutableArray *)readRecords

{

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

   

    NSString *query = [NSString stringWithFormat:@"SELECT * FROM student ORDER BY Name ASC"];

           

            sqlite3 *db;

   

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

    NSString *documentsDir = [documentPaths objectAtIndex:0];

    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"test.sqlite"];

   

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

            {

                        sqlite3_stmt *compiledStatement;

       

        if(sqlite3_prepare_v2(db,[query UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)

                        {

                                    while(sqlite3_step(compiledStatement) == SQLITE_ROW)

                                    {

                                                Student *currentSObj = [[Student alloc] init];

                

                currentSObj.StudentId = sqlite3_column_int(compiledStatement, 0);

               

                currentSObj.Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];

               

                currentSObj.Age = sqlite3_column_int(compiledStatement, 2);

               

                currentSObj.Perc = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];

               

                [recordArr addObject:currentSObj];

                                    }

                        }

                        sqlite3_finalize(compiledStatement);

            }

            sqlite3_close(db);

   

    return recordArr;

}

+ (NSInteger)addRecord:(Student *)sObj

{

    NSInteger retVal = false;

   

    NSString *query;

   

    query = [NSString stringWithFormat:

             @"INSERT INTO student (Name, Age, Perc) VALUES ('%@', '%d', '%@')",

             sObj.Name,

             sObj.Age,

             sObj.Perc            

             ];

   

    sqlite3 *db;

   

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

    NSString *documentsDir = [documentPaths objectAtIndex:0];

    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"test.sqlite"];

   

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

            {

        sqlite3_stmt *compiledStatement;

       

        if(sqlite3_prepare_v2(db,[query UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)

                        {

            sqlite3_step(compiledStatement);

           

            retVal = true;

                        }

       

                        sqlite3_finalize(compiledStatement);

       

        if(retVal)

            retVal = sqlite3_last_insert_rowid(db);

    }

            sqlite3_close(db);

   

    return retVal;

}

+ (BOOL)editRecord:(Student *)sObj

{

    BOOL retVal = false;

   

    NSString *query;

   

    query = [NSString stringWithFormat:

             @"UPDATE student SET Name = '%@', Age = '%d', Perc = '%@' WHERE StudentId = '%d'",

             sObj.Name,

             sObj.Age,

             sObj.Perc,

             sObj.StudentId

             ];

   

    sqlite3 *db;

   

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

    NSString *documentsDir = [documentPaths objectAtIndex:0];

    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"test.sqlite"];

   

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

            {

        sqlite3_stmt *compiledStatement;

       

        if(sqlite3_prepare_v2(db,[query UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)

                        {

            sqlite3_step(compiledStatement);

           

            retVal = true;

                        }

       

                        sqlite3_finalize(compiledStatement);

            }

            sqlite3_close(db);

   

    return retVal;

}

+ (BOOL)deleteRecord:(Student *)sObj

{

    BOOL retVal = false;

   

    NSString *query = [NSString stringWithFormat:@"DELETE FROM student WHERE StudentId = '%d'", sObj.StudentId];

   

    sqlite3 *db;

   

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

    NSString *documentsDir = [documentPaths objectAtIndex:0];

    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"test.sqlite"];

   

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

    {

        sqlite3_stmt *compiledStatement;

       

        if(sqlite3_prepare_v2(db,[query UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)

            sqlite3_step(compiledStatement);

       

        sqlite3_finalize(compiledStatement);

       

        retVal = true;

    }

    sqlite3_close(db);

   

    return retVal;

}

AppDelegate.h

@property (strong, nonatomic) IBOutlet UIWindow *window;

@property (strong, nonatomic) IBOutlet UINavigationController *nav;

AppDelegate.m

@synthesize nav;

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

{

    [self checkDatabase];

   

    self.window.rootViewController = nav;

    [self.window makeKeyAndVisible];

    return YES;

}

- (void)checkDatabase

{

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

            NSString *documentsDir = [documentPaths objectAtIndex:0];

            NSString *databasePath = [documentsDir stringByAppendingPathComponent:@"test.sqlite"];

            NSString *databaseName = @"test.sqlite";

            NSFileManager *fileManager = [NSFileManager defaultManager];

           

            if(![fileManager fileExistsAtPath:databasePath])

            {

                        NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

                        [fileManager removeItemAtPath:databasePath error:nil];

                        [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

    }

            else

            {

                        NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

                        [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

    }

}

ViewController.h

#import <UIKit/UIKit.h>

@interface ip22ViewController : UIViewController <UITableViewDataSource, UITableViewDelegate>

{

    NSMutableArray *studentRecordsArr;

}

@property (nonatomic, retain) IBOutlet UIBarButtonItem *addBarBtn;

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

- (IBAction)addRecBtnPressed:(id)sender;

@end

ViewController.m

 

#import "Student.h"

#import "StudentCellViewController.h"

@synthesize addBarBtn,

            recordTblView;

- (IBAction)addRecBtnPressed:(id)sender

{

    Student *tempSObj = [[Student alloc] init];

    tempSObj.Name = @"Alex";

    tempSObj.Age = 16;

    tempSObj.Perc = @"90.80";

   

    if(![Student addRecord:tempSObj])

    {

        [[[UIAlertView alloc] initWithTitle:@"Warning!"

                                    message:@"Details has not been saved.\nPlease verify your data."

                                   delegate:self

                          cancelButtonTitle:@"OK"

                          otherButtonTitles:nil] show];

    }

   

   

    [self initAndLoadTable];

}

- (void)viewDidLoad

{

    [super viewDidLoad];

   

    self.navigationItem.rightBarButtonItem = addBarBtn;

   

    [self initAndLoadTable];

}

- (void)initAndLoadTable

{

    studentRecordsArr = [Student readRecords];

    [recordTblView reloadData];

}

- (void)didReceiveMemoryWarning

{

    [super didReceiveMemoryWarning];

    // Dispose of any resources that can be recreated.

}

#pragma mark - Table Methods

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

{

    return 100;

}

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

{

    return studentRecordsArr.count;

}

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

{

    static NSString *CellIdentifier = @"Cell";

   

    UITableViewCell *cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];

   

    Student *sObj = [studentRecordsArr objectAtIndex:indexPath.row];

   

    StudentCellViewController *sVC = [[StudentCellViewController alloc] init];

    sVC.studentObj = sObj;

    [cell.contentView addSubview:sVC.view];

   

    return cell;

}

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

{

    Student *sObj = [studentRecordsArr objectAtIndex:indexPath.row];

   

    if(![Student deleteRecord:sObj])

    {

        [[[UIAlertView alloc] initWithTitle:@"Warning!"

                                    message:@"Something went wrong!"

                                   delegate:self

                          cancelButtonTitle:@"OK"

                          otherButtonTitles:nil] show];

    }

    else

    {

        [studentRecordsArr removeObjectAtIndex:indexPath.row];

       

       

        [recordTblView beginUpdates];

        [recordTblView deleteRowsAtIndexPaths:[[NSArray alloc] initWithObjects:indexPath, nil] withRowAnimation:UITableViewRowAnimationMiddle];

        [recordTblView endUpdates];

    }

}

StudentCellViewController.h

@class Student;

@interface StudentCellViewController : UIViewController

@property (nonatomic, retain) IBOutlet UILabel  *NameLbl,

                                                *AgeLbl,

                                                *PercLbl;

@property (nonatomic, retain) Student *studentObj;

StudentCellViewController.m

 

#import "StudentCellViewController.h"

#import "Student.h"

@implementation StudentCellViewController

@synthesize NameLbl,

            AgeLbl,

            PercLbl;

@synthesize studentObj;

- (void)viewDidLoad

{

    [super viewDidLoad];

   

    NameLbl.text = studentObj.Name;

    AgeLbl.text = [NSString stringWithFormat:@"%dyr", studentObj.Age];

    PercLbl.text = [NSString stringWithFormat:@"%.2f%%", studentObj.Perc.doubleValue];   

}