Sqlite CASE expression

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.
The optional expression that occurs in between the CASE keyword and the first WHEN keyword is called the "base" expression. There are two basic forms of the CASE expression: those with a base expression and those without.

In a CASE without a base expression, each WHEN expression is evaluated and the result treated as a boolean, starting with the leftmost and continuing to the right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression that evaluates to true. Or, if none of the WHEN expressions evaluate to true, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions are true, then the overall result is NULL.
A NULL result is considered untrue when evaluating WHEN terms.


For example, we consider a table 'Employee' with column 'Emp_id', 'Emp_name','Emp_DOB','Emp_Department'.
Assume, we already have some data on table.
So the task is to interchange  'Emp_Department'
Suppose only two department is Marketing and Production and we want to change Marketing employee to Production and Production to employee.
We can use two separate Update query statement for completion of above task.
But i am going to use CASE EXPRESSION.

Here simple query for interchange Emp_Department.
Update Employee set Emp_Department = case when Emp_Department = 'Marketing' then 'Production' else 'Marketing' end

Code
-(NSString *) getUserDBPath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
  
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"Employee.sqlite"];
}

-(void) updateTableDetails{
    //Get Database path
    NSString *userDBPath = [self getUserDBPath];
    sqlite3 *userDatabase;
   //Open Database
    if (sqlite3_open([userDBPath UTF8String], &userDatabase) != SQLITE_OK){
        NSLog(@"Error to open user DB = %s",sqlite3_errmsg(userDatabase));
        userDatabase = nil;
    }
    sqlite3_stmt *queryStmt;
    //Update query for Update
    NSString *query = @"Update Employee set Emp_Department = case when Emp_Department = 'Marketing' then 'Production' else 'Marketing' end";
   //Prepare Statement
    if (sqlite3_prepare_v2(userDatabase, query.UTF8String, -1, &queryStmt, NULL) != SQLITE_OK) {
        NSLog(@"Error to prepare stmt=%s",sqlite3_errmsg(userDatabase));
        sqlite3_finalize(queryStmt);
        sqlite3_close(userDatabase);
        queryStmt = nil;
        userDatabase = nil;
    }
    if (sqlite3_step(queryStmt) == SQLITE_DONE) {
        NSLog(@"success");
    }
    else{
        NSLog(@"Error to step stmt = %s",sqlite3_errmsg(userDatabase));
    }
    sqlite3_finalize(queryStmt);
    sqlite3_close(userDatabase);
    queryStmt = nil;
    userDatabase = nil;
}


For More Detail Click Here

Display Table View Cell From Bottom

For the better user interface some time we need to display UITabelViewCell from bottom of the UITableView.
And, when we add new cell, its insert in bottom of UITableView and Table View auto scroll up for proper visualization for newly added cell.
We can use UITableView's contentInset for  this kind of behavior of UItableView.
Whenever add new cell update contentInset of Tableview.

Code
 -(void) updateTableViewInset:(UITableView *)tableView{
//First find out total number of cell in Table View
    NSInteger numRows=[tableView numberOfRowsInSection:0];
//Get height of Table View
    CGFloat contentInsetTop=tableView.bounds.size.height;
    for (int i=0;i<numRows;i++) {
//Get cell height and subtract by contentInsetTop
        contentInsetTop -= [self tableView:tableView heightForRowAtIndexPath:[NSIndexPath indexPathForItem:i inSection:0]];
//Check if contentInsetTop reach to zero or less than zero
        if (contentInsetTop<=0) {
            contentInsetTop=0;
            break;
        }
    }
//update tableview contentInset.
    tableView.contentInset = UIEdgeInsetsMake(contentInsetTop, 0, 50, 0);
}

Animate UITextView With Text

//First set maximum height for UITextView
maximumHeight = 120;

//Add UITextView
UITextView *textView = [[UITextView alloc] initWithFrame:CGRectMake(10, 50, 300, 40)];
    textView.delegate = self;
    textView.font = [UIFont systemFontOfSize:14];
    [self.view addSubview:textView];

// Delegate of UItextView
-(void) textViewDidChange:(UITextView *)textView{

    UITextPosition* pos = textView.endOfDocument;//explore others like beginningOfDocument if you want to customize the behaviour
    CGRect currentRect = [textView caretRectForPosition:pos];

        CGRect  newFrame = textView.frame;
        newFrame.size.height = currentRect.size.height+currentRect.origin.y+5;
        if (newFrame.size.height>40 && newFrame.size.height<=maximumHeight) {
            [UIView beginAnimations:@"" context:nil];
            [UIView setAnimationDuration:0.1f];
            textView.frame = newFrame;
            [UIView commitAnimations];
        }
        else if (newFrame.size.height<40){
            newFrame.size.height=40;
            [UIView beginAnimations:@"" context:nil];
            [UIView setAnimationDuration:0.1f];
            textView.frame = newFrame;
            [UIView commitAnimations];
        }
    }