SQLite Tutorial Part 1

Credits:

Most of the codes are from Techotopia and I refer this tutorial from their SQLite tutorial 
For more information and detailed discussion, refer to their site. :D


This is what we will be trying to achieve in this first Tutorial. (Yes, I'm gonna have a Part 2 of SQLite Tutorial)

Prerequisite:

1. SQLite or MySQL
2. Creating Tabbed Views
3. Adding UITableView

1. Create a Single View Application Template.
2. Add SQLite dynamic library in Build Phases under "Link Binary With Libraries."

3. Add a New File, subclass of UITableViewController. In my case, I named it "ListViewController." We will use this class to show all the added entries from our database in to this table.

4. Add a UITabBarController variable in our AppDelegate.
@property (strong, nonatomic) UITabBarController *tabController; //Interface Section
@synthesize tabController; //Implementation Section


5. Edit AppDelegate's ApplicationDidFinishLaunchingWithOptions method:

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

    self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];

    // Override point for customization after application launch.

    

    tabController = [[UITabBarController alloc] init];

    self.viewController = [[ViewController  alloc] initWithNibName:@"ViewController" bundle:nil];

    ListViewController *listVC = [[ListViewController alloc] initWithStyle:UITableViewStylePlain];

    tabController. viewController = [[NSArray  alloc] initWithObjects:self.viewController, listVC, nil];

     self.window.rootViewController = self.tabController;

    [self.window makeKeyAndVisible];

    return YES;

}

6. #import <sqlite3.h> to ViewController and ListViewController classes.

Try to Build and Run your program to make sure that nothing's wrong when you imported sqlite3.

7. Let's create variables for our databasePath (NSString *) and our database (sqlite3 *) to ViewController and ListViewController classes.

@property (strong, nonatomic)  NSString *databasePath;

@property (nonatomic)  sqlite3 *myDatabase;

8. Add a textfield and a button to our ViewController.xib file. The user enters any message inside our textfield and when the user taps the button, it will be added to our database.

Create an IBOutlet property for our textfield and connect it to our object in our xib file. Remember also to synthesize the UITextField property.

@property (strong, nonatomic)  IBOutlet UITextField *textField; //Interface section

@synthesize textField; //Implementation section

9. Prepare an IBAction method for our button. 
- (IBAction) addTextToDatabase:(id) sender {
     //We will add the codes later. 
}

Connect this IBAction to our button object in our xib file.

10. Prepare and create our database if it has not existed yet. Call this method inside our viewDidLoad in ViewController.m (You may disregard the UIAlertView codes.)

- (void)prepareDatabase {

    // Get the documents directory

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

    NSString *docsDir = dirPaths[0];

    

    // Build the path to the database file. We declared databasePath on Step 7

   databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent:@"sampleDatabase.db"]];

    //NSLog(@"DB Path: %@", databasePath);

    

    NSFileManager *filemgr = [NSFileManager defaultManager];

    

    if ([filemgr fileExistsAtPath: databasePath ] == NO) {

        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {  //We declared myDatabase on Step 7

            char *errMsg;

            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS SAMPLETABLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, MESSAGE TEXT)";

/*
Name of Our Database: sampleDatabase.db
Name of Our Table: SAMPLETABLE
Columns in SAMPLTABLE: ID & Message
Datatype of Message: Text
*/

            

            if (sqlite3_exec(myDatabase, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {

                statusOfAddingToDB = @"Failed to create table"; //iVariable statusOfAddingToDB (NSString *) 

            } else {

                statusOfAddingToDB = @"Success in creating table";

            }

            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];

            [alert show];

            sqlite3_close(myDatabase);

        } else {

            statusOfAddingToDB = @"Failed to open/create database";

            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];

            [alert show];

        }

    }

}

Our viewDidLoad in ViewController.m must now look like this:

- (void)viewDidLoad {

    [super viewDidLoad];

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

    self.title = @"Add To DB"; //For the tab title of our TabController

    [self prepareDatabase];

}

11. Add Entry to Database

- (IBAction)addTextToDatabase:(id)sender {

    sqlite3_stmt    *statement;

    const char *dbpath = [databasePath UTF8String];

    

    if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {

        NSString *insertSQL = [NSString stringWithFormat:

                               @"INSERT INTO SAMPLETABLE (MESSAGE) VALUES (\"%@\")",

                               self.textField.text];

        

        const char*insert_stmt = [insertSQL UTF8String];

        sqlite3_prepare_v2(myDatabase, insert_stmt,  -1, &statement, NULL);

        if (sqlite3_step(statement) == SQLITE_DONE) {

            statusOfAddingToDB = [NSString stringWithFormat:@"Text added -- %@", textField.text];

        } else {

            statusOfAddingToDB = @"Failed to add contact";

        }

        

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"DB Status" message: statusOfAddingToDB delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil];

            [alert show];

        

        sqlite3_finalize(statement);

        sqlite3_close(myDatabase);

    }

}

12. I also added a UITextFieldDelegate to dismiss our keyboard after the user types in our textfield.

- (BOOL)textFieldShouldReturn:(UITextField *)tf {

    if (tf == textField) {

        [tf resignFirstResponder];

    }

    return NO;

}

ListViewController Class (UITableView)

I assume you did Steps 6 and 7 to ListViewController Class.

13. Add an NSMutableArray property to this class. Synthesize this property also. 

@property (strong, nonatomic) NSMutableArray *list; //Interface Section
@synthesize list; //Implementation Section

14. Add a tab title for our TabController in initWithStyle method of ListViewController.m.

- (id)initWithStyle:(UITableViewStyle)style {

    self = [super initWithStyle:style];

    if (self) {

        self.title = @"List"; //tab title

    }

    return self;

}

15. Let's get the data we want from our database.

- (void)getTextFomDB {

    NSString *docsDir;

    NSArray *dirPaths;

    

    // Get the documents directory

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    

    // Build the path to the database file

    databasePath = [[NSString alloc]

                    initWithString: [docsDir stringByAppendingPathComponent:

                                     @"sampleDatabase.db"]];

    

    const char *dbpath = [databasePath UTF8String];

    sqlite3_stmt    *statement;

    

    if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK)

    {

        //Select all from SAMPLETABLE. This includes the 'id' column and 'message' column.

        NSString *querySQL = @"SELECT * FROM SAMPLETABLE"; 

        const char *query_stmt = [querySQL UTF8String];

        

        if (sqlite3_prepare_v2(myDatabase, query_stmt, -1, &statement, NULL) == SQLITE_OK) {

            [list removeAllObjects];

            while (sqlite3_step(statement) == SQLITE_ROW) {

                NSString *text = [[NSString alloc]

                                  initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)]; //Num 1 means on what column. Column 0 = 'id' column while Column 1 = 'message' column in our query result.

                [list addObject:text];

                //NSLog(@"count: %d", [list count]);

            }

            sqlite3_finalize(statement);

        }

        sqlite3_close(myDatabase);

    }

}

16. We call this method getTextFromDB in our viewWillAppear method. So that every time this view will be shown, it will get the data again from our database. Then reloadData to our tableview.

- (void)viewWillAppear:(BOOL)animated {

    [self getTextFomDB];

    [self.tableView reloadData];

}

17. Hope you are already familiar with UITableview's delegate and datasource methods. Edit the following methods such that:

• numberOfSectionsInTableView == 1
• numberOfRowsInSection == total number of elements in our NSMutableArray
• cellForRowAtIndexPath --> set cell's textLabel = text in our NSMutableArray

SQLite Tutorial Part 2 will cover the following:

1. Having multiple columns in one (DB) table.
2. Getting all data in our (DB) table (with multiple columns)
3. Search and return data from our (DB) table.

Download Sample Project here.