Using SQLite3 as a Data Source in an Xcode Project (Objective-C)

Introduction

One important aspect of almost any application is the data source that provides or store data to the UI elements. In this article we'll wire up the most widely deployed SQL database engine in the world, SQLite, to the IGGridView. In addition you'll learn how to execute a SELECT statement on a SQLite3 database, and turn the results into NSMutableArray of custom objects.

The illustration below will be the final result.

 

Requirements

This article requires the use of the frameworks listed below.

• CoreText
• libsqlite3.dylib
• QuartzCore
• IG (the trial for NucliOS can be found here)

Create the Custom Object

Before retrieving the SQL data, we need to create the object model that will store data from each row returned from the SELECT statement. The 'Employees' table used in this example contains these fields:

• Index (primary key)
• Name (text)
• Email (text)
• Department (text)

Using this information about the fields we can create a simple NSObject to hold these values.

@interface EmployeeObject : NSObject
@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *email;
@property (nonatomic, retain) NSString *department;
@end

 

@implementation EmployeeObject
@synthesize department, email, name;
@end

Note:The 'Index' field is not stored in our object because we will not being using it for anything useful in this article.

Obtaining Data from the Database

Now that we have our custom object ready for the returned results we can focus on retrieving the path to the SQLite database file and executing a SELECT statement on the 'Employees' table. First we'll create a method to retrieve the path to our database file that's included in the Xcode project.

-(NSString *)getDatabasePath
{
    return [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"BasicData.db"];
}

Next, we'll create the method that takes the database path as an parameter and executes a SELECT statement on the 'Employees' table. We then loop through the rows and insert the results into the custom object, after which is placed into an NSMutableArray.

-(void)getSQLiteData:(NSString *)dbPath
{
    _sqliteData = [[NSMutableArray alloc] init];
    sqlite3 *database;
    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        const char *sql = "select Name, Email, Department from Employees";
        sqlite3_stmt *selectStatement;
        if(sqlite3_prepare_v2(database, sql, -1, &selectStatement, NULL) == SQLITE_OK) {
            while(sqlite3_step(selectStatement) == SQLITE_ROW) {
                EmployeeObject *employeeObject = [[EmployeeObject alloc] init];
                employeeObject.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 0)];
                employeeObject.email = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 1)];
                employeeObject.department = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectStatement, 2)];
                [_sqliteData addObject:employeeObject];
            }
        }
    }
    sqlite3_close(database);
}

Wiring up the Data

With the methods created to obtain our data, we can now begin wiring up the data to the IGGridView. This article will use the viewDidLoad method for wiring the data. First, we'll set the view backgroundColor and call the getSQLiteData method to fill the NSMutableArray.

self.view.backgroundColor = [UIColor whiteColor];
[self getSQLiteData:[self getDatabasePath]];

Next we'll create the IGGridViewinstance and add it's view to the view controller's subviews.

_gridView = [[IGGridView alloc] initWithFrame:self.view.bounds style:IGGridViewStyleDefault];
_gridView.autoresizingMask = UIViewAutoresizingFlexibleHeight|UIViewAutoresizingFlexibleWidth;
_gridView.headerHeight = 0;
[self.view addSubview:_gridView];

Finally, we'll create the grid's IGGridViewDataSourceHelper instance, assigning our NSMutableArray as the data and the 'Department' field as the groupingKey. After which, the data source helper is assigned to the grid's dataSourceproperty.

_dsh = [[IGGridViewDataSourceHelper alloc] init];
_dsh.data = _sqliteData;
_dsh.groupingKey = @"department";
_gridView.dataSource = _dsh;

Download the Source

The Xcode project source code for this article can be downloaded by clicking this link. You will need to add the IG.framework to the Xcode project after you install the NucliOS trial.

A much easier to read format of this post can be found here.

By Torrey Betts