Thursday 19 December 2013

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];   
}

No comments:

Post a Comment

Comment