sqlite3 program

DBConnectAppDelegate.h
#import
@class DBConnectViewController;
@interface DBConnectAppDelegate : NSObject {
UIWindow *window;
DBConnectViewController *viewController;
}
@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet DBConnectViewController *viewController;
@end

DBConnectAppDelegate.m
#import "DBConnectAppDelegate.h"
#import "DBConnectViewController.h"
#import "DBStudent.h"
#import "Employee.h"
#import "Department.h"

@implementation DBConnectAppDelegate

@synthesize window;
@synthesize viewController;

#pragma mark -
#pragma mark Application lifecycle

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

// Override point for customization after application launch.
[DBStudent createDB];
//[DBStudent createTable];
NSMutableArray *arrEmpDepts = [DBStudent getEmployeeDept];
NSMutableArray *arrEmployees = [arrEmpDepts objectAtIndex:0];
for(Employee *emp in arrEmployees)
{
NSLog(@"ename is %@",emp.ename);
NSLog(@"esal is %f",emp.esal);
}
NSMutableArray *arrDepts = [arrEmpDepts objectAtIndex:1];
for(Department *dept in arrDepts)
{
NSLog(@"dept name is %@",dept.deptName);
NSLog(@"dept location is %@",dept.deptLocation);
}

// Add the view controller's view to the window and display.
[window addSubview:viewController.view];
[window makeKeyAndVisible];

return YES;
}
@end


DBConnectViewController.h
#import
#import "Student.h"

@interface DBConnectViewController : UIViewController {

IBOutlet UITextField *txtName,*txtMarks,*txtDOB,*txtMobileNo;
IBOutlet UITableView *tableStudents;
NSMutableArray *arrStudents;
Student *selectedStudent;
}
-(IBAction)saveStudent;
-(IBAction)deleteStudent;
@end

DBConnectViewController.m

#import "DBConnectViewController.h"
#import "DBStudent.h"

@implementation DBConnectViewController

-(IBAction)saveStudent{

Student *student = [[Student alloc]init];
student.Sname = txtName.text;
student.Marks = [txtMarks.text floatValue];
student.DOB = txtDOB.text;
student.MobileNo = txtMobileNo.text;

if(selectedStudent == nil)
{
BOOL exists = [DBStudent checkRecordExistsOrNot:student];
if(exists == YES)
{
UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"alert" message:@"record already exists" delegate:self cancelButtonTitle:@"ok" otherButtonTitles:nil];
alert.tag = 20;
[alert show];
return;
}
[DBStudent insertStudent:student];
}
else{

selectedStudent.Sname = txtName.text;
selectedStudent.Marks = [txtMarks.text floatValue];
selectedStudent.DOB = txtDOB.text;
selectedStudent.MobileNo = txtMobileNo.text;
[DBStudent updateStudent:selectedStudent];
}

arrStudents = [DBStudent getStudents];
[tableStudents reloadData];

txtName.text = @"";
txtMarks.text = @"";
txtDOB.text = @"";
txtMobileNo.text = @"";
}
-(IBAction)deleteStudent{

[DBStudent deleteStudent:selectedStudent.Sid];
arrStudents = [DBStudent getStudents];
[tableStudents reloadData];

}

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

[textField resignFirstResponder];
return YES;
}

#pragma tableview datasource methods

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

return [arrStudents count];
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{

NSString *identifier = @"cell";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:identifier];
if(cell == nil)
{
cell = [[UITableViewCell alloc]initWithStyle:UITableViewCellStyleValue1 reuseIdentifier:identifier];
}
Student *st = [arrStudents objectAtIndex:indexPath.row];
cell.textLabel.text = st.Sname;
return cell;

}


#pragma UITableViewDelegate methods
- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath{

selectedStudent = [arrStudents objectAtIndex:indexPath.row];
txtName.text = selectedStudent.Sname;
txtMarks.text = [NSString stringWithFormat:@"%f", selectedStudent.Marks];
txtDOB.text = selectedStudent.DOB;
txtMobileNo.text = selectedStudent.MobileNo;
}
- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath{

selectedStudent = [arrStudents objectAtIndex:indexPath.row];
UIAlertView *alertDelete = [[UIAlertView alloc]initWithTitle:@"Delete" message:@"Do you want to delete the record" delegate:self cancelButtonTitle:@"NO" otherButtonTitles:@"YES",nil];
alertDelete.tag = 10;
[alertDelete show];

}
- (void)alertView:(UIAlertView *)alertView clickedButtonAtIndex:(NSInteger)buttonIndex{

if(alertView.tag ==10)
{
if(buttonIndex ==1)
{
[self deleteStudent];
}
}

}

- (void)viewDidLoad {
[super viewDidLoad];
arrStudents = [DBStudent getStudents];
tableStudents.editing = YES;
}
@end
DBStudent.h

#import
#import
#import "Student.h"

@interface DBStudent : NSObject {

}
+(BOOL)createDB;
+(BOOL)insertStudent:(Student*)st;
+(NSMutableArray*)getStudents;
+(BOOL)updateStudent:(Student*)st;
+(BOOL)deleteStudent:(int)sid;
+(BOOL)checkRecordExistsOrNot:(Student*)st;
+(NSMutableArray*)getEmployeeDept;
+(BOOL)createTable;
@end


DBStudent.m

#import "DBStudent.h"
#import "Employee.h"
#import "Department.h"


@implementation DBStudent

+(BOOL)createDB{

NSString *strSourcePath = [[NSBundle mainBundle]pathForResource:@"Student15" ofType:@"sqlite"];

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

NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

NSFileManager *fileManager = [[NSFileManager alloc]init];

if([fileManager fileExistsAtPath:strDestPath]==NO)
{
NSError *err;
[fileManager copyItemAtPath:strSourcePath toPath:strDestPath error:&err];
return YES;
}
else{
NSLog(@"file already exists");
return NO;

}
return NO;
}
+(BOOL)createTable{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"CREATE Table Employee(eno int primarykey autoinc,ename varchar(50),esal float,edoj date)"];
void* v;
char* errmsg;
if(sqlite3_exec(dbStudent, [query UTF8String], 0, v, &errmsg)==SQLITE_OK)
{
NSLog(@"table crated successfully");
return YES;
}
else{

NSLog(@"table creation has problem %@",errmsg);
return NO;
}

}
return NO;


}
+(BOOL)insertStudent:(Student*)st{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"INSERT INTO Student(Sname,Marks,birth,mobile) VALUES('%@',%f,'%@','%@')",st.Sname,st.Marks,st.DOB,st.MobileNo];
void* v;
char* errmsg;
if(sqlite3_exec(dbStudent, [query UTF8String], 0, v, &errmsg)==SQLITE_OK)
{
NSLog(@"query executed successfully");
return YES;
}
else{

NSLog(@"insertion has problem %@",errmsg);
return NO;
}
}
return NO;
}
+(BOOL)updateStudent:(Student*)st{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"UPDATE Student SET Sname = '%@',Marks = %f,Birth= '%@',Mobile='%@' WHERE Sid = %d",st.Sname,st.Marks,st.DOB,st.MobileNo,st.Sid];
void* v;
char* errmsg;
if(sqlite3_exec(dbStudent, [query UTF8String], 0, v, &errmsg)==SQLITE_OK)
{
NSLog(@"record updated successfully");
return YES;
}
else{

NSLog(@"updation has problem %@",errmsg);
return NO;
}

}
return NO;

}
+(BOOL)deleteStudent:(int)sid{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"DELETE FROM Student WHERE Sid = %d",sid];
void* v;
char* errmsg;
if(sqlite3_exec(dbStudent, [query UTF8String], 0, v, &errmsg)==SQLITE_OK)
{
NSLog(@"record deleted successfully");
return YES;
}
else{

NSLog(@"deletion has problem %@",errmsg);
return NO;
}
}
return NO;
}

+(NSMutableArray*)getStudents{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

NSMutableArray *arrStudents;

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"SELECT * FROM Student"];
char* errmsg;
sqlite3_stmt *studentStmt;
if(sqlite3_prepare_v2(dbStudent, [query UTF8String], -1, &studentStmt, nil)==SQLITE_OK)
{
arrStudents = [[NSMutableArray alloc]init];
while(sqlite3_step(studentStmt)==SQLITE_ROW)
{
int sid = sqlite3_column_int(studentStmt, 0);
NSString *sname = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 1)];
float marks = sqlite3_column_double(studentStmt, 2);
NSString *dob = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 3)];
NSString *mobileno = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 4)];

Student *st = [[Student alloc]init];
st.Sid = sid;
st.Sname = sname;
st.Marks = marks;
st.DOB = dob;
st.MobileNo = mobileno;

[arrStudents addObject:st];
[st release];
}
}
}
return arrStudents;
}
+(BOOL)checkRecordExistsOrNot:(Student*)st{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

NSMutableArray *arrStudents;

sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"SELECT * FROM student where sname = '%@' and mobile = '%@'",st.Sname,st.MobileNo];
char* errmsg;
sqlite3_stmt *studentStmt;
if(sqlite3_prepare_v2(dbStudent, [query UTF8String], -1, &studentStmt, nil)==SQLITE_OK)
{
arrStudents = [[NSMutableArray alloc]init];
while(sqlite3_step(studentStmt)==SQLITE_ROW)
{
return YES;
}
}
}
return NO;
}

+(NSMutableArray*)getEmployeeDept{

NSArray *arrDocPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *strDestPath = [NSString stringWithFormat:@"%@/Student15.sqlite",[arrDocPath objectAtIndex:0]];

NSMutableArray *arrEmployees;
NSMutableArray *arrDepts;
sqlite3 *dbStudent;
if(sqlite3_open([strDestPath UTF8String], &dbStudent)==SQLITE_OK)
{
NSString *query = [NSString stringWithFormat:@"Select * From Employee,Department where Employee.DeptId = Department.DeptId"];
char* errmsg;
sqlite3_stmt *studentStmt;
if(sqlite3_prepare_v2(dbStudent, [query UTF8String], -1, &studentStmt, nil)==SQLITE_OK)
{
arrEmployees = [[NSMutableArray alloc]init];
arrDepts = [[NSMutableArray alloc]init];
while(sqlite3_step(studentStmt)==SQLITE_ROW)
{
int eno = sqlite3_column_int(studentStmt, 0);
NSString *ename = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 1)];
float esal = sqlite3_column_double(studentStmt, 2);
NSString *edoj = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 3)];
int edeptId = sqlite3_column_int(studentStmt, 4);

int deptId = sqlite3_column_int(studentStmt, 5);
NSString *deptName = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 6)];
NSString *deptLoc = [NSString stringWithUTF8String:sqlite3_column_text(studentStmt, 7)];

Employee *emp = [[Employee alloc]init];
emp.eno = eno;
emp.ename = ename;
emp.esal = esal;
emp.edoj = edoj;
emp.deptId = edeptId;

[arrEmployees addObject:emp];

Department *dept = [[Department alloc]init];
dept.deptId = deptId;
dept.deptName = deptName;
dept.deptLocation = deptLoc;

[arrDepts addObject:dept];
[dept release];

[emp release];
}
}
}
NSMutableArray *arrEmpDept = [[NSMutableArray alloc]initWithObjects:arrEmployees,arrDepts,nil];
return arrEmpDept;

}
@end


Student.h

#import


@interface Student : NSObject {

int Sid;
NSString *Sname,*DOB,*MobileNo;
float Marks;
}
@property(nonatomic)int Sid;
@property(nonatomic,retain)NSString *Sname,*DOB,*MobileNo;
@property(nonatomic)float Marks;
@end


Student.m

#import "Student.h"


@implementation Student

@synthesize Sid,Sname,DOB,MobileNo,Marks;

@end

Employee.h

#import


@interface Employee : NSObject {

int eno;
NSString *ename;
float esal;
NSString *edoj;
int deptId;
}
@property(nonatomic)int eno;
@property(nonatomic,retain)NSString *ename;
@property(nonatomic)float esal;
@property(nonatomic,retain)NSString *edoj;
@property(nonatomic)int deptId;
@end


Employee.m

#import "Employee.h"

@implementation Employee

@synthesize eno;
@synthesize ename;
@synthesize esal;
@synthesize edoj;
@synthesize deptId;

@end

Department.h

#import

@interface Department : NSObject {

int deptId;
NSString *deptName,*deptLocation;
}
@property(nonatomic)int deptId;
@property(nonatomic,retain)NSString *deptName,*deptLocation;
@end


Department.m

#import "Department.h"

@implementation Department

@synthesize deptId;
@synthesize deptName,deptLocation;
@end