基于sqlite3实现的简单通讯录程序（控制台）

sqlite3_close()

3. 通讯录程序

一个简单的通讯录程序，主要是为了练手，熟悉Slqite3的使用。

数据库：contact.db，只有一张表info，保存联系人信息（姓名，年龄，关系，手机号）

程序中bug较多，但却涵盖了sqlite3的数据库常用操作：创建数据库、创建表、增、删、改、查等。

/************************************************************************
 * 名  称：contact_using_sqlite3.c
 * 功  能：一个简单的通讯录程序，主要是为了练习sqlite3的使用
 * 描  述：使用sqlite3，建立数据库contact,其中有表一张
          info(name varchar(10),age smallint,relation varchar(10),phone varchar(11))
          包括如下功能：
             1. 查看通讯录
             2. 增加联系人
             3. 删除联系人
             4. 修改联系人信息
             5. 查找联系人
 * 作  者：JarvisChu
 * 时  间：2011-8-22
 * 修  订：2011-8-24，完成修改和删除；
 ************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

sqlite3* db = NULL;               //the database


//show the menu on the screen,and return user's choice
int showMenu(){
    int choice = 0;
    while(1){
        printf("*****************************************************n");
        printf("                      Welcome                        n");
        printf("1. Display All          2. Add   Contactn");
        printf("3. Delete  Contact      4. Alter Contactn");
        printf("5. Find    Contactn");
        printf("Your choice is:");
        scanf("%d",&choice);
        if(choice == 1 || choice == 2 ||
           choice == 3 || choice == 4 ||
           choice == 5){
            return choice;
        }
        else{
            printf("Wrong! Again!n");
        }
    }
    return 0;
}

//show all records in db
void displayAll(){
    int i = 0;
    int j = 0;
    int index = 0;
    int ret = 0;
    int row = 0;
    int column = 0;
    char* sql = NULL;
    char** resultSet = NULL;     //store the query result

    sql = (char*)malloc(sizeof(char)*20);
    strcpy(sql,"select * from info;");

    ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
    if(ret != SQLITE_OK){
        fprintf(stderr,"select records errn");
    }

    printf("There are %d Contact:n",row);
    index = 0;
    for(i=0;i<=row;i++){
        for(j=0;j<column;j++){
            printf("%-11s",resultSet[index++]);
        }
        printf("n");
    }

    sqlite3_free_table(resultSet);
    free(sql);

}

//add contact
void addContact(){
    int ret = 0;
    char* name = NULL;
    int age = 0;
    char* relation = NULL;
    char* phone = NULL;
    char* sql = NULL;

    name = (char*)malloc(sizeof(char)*10);
    relation = (char*)malloc(sizeof(char)*10);
    phone = (char*)malloc(sizeof(char)*12);
    sql = (char*)malloc(sizeof(char)*64);

    printf("input (name age relation phone):");
    scanf("%s %d %s %s",name,&age,relation,phone);
    //printf("%s， %d， %s，%sn",age,phone);


    sprintf(sql,"insert into info values('%s',%d,'%s','%s');",phone);
    //printf("%sn",sql);
    ret = sqlite3_exec(db,0);
    if(ret != SQLITE_OK){
        printf("failed!n");
    }
    else{
        printf("ok!n");
    }
    free(name);
    free(relation);
    free(phone);
    free(sql);

}

//find Contact
void findContact(){
    int i,j,index;
    int ret  = 0;
    int row = 0;
    int column = 0;
    char* name = NULL;
    char* sql = NULL;
    char** resultSet = NULL;

    name = (char*)malloc(sizeof(char)*10);
    printf("Input the name you want to find:");
    scanf("%s",name);

    sql = (char*)malloc(sizeof(char)*64);
    sprintf(sql,"select * from info where name = '%s'",name);

    ret = sqlite3_get_table(db,"select err:%sn",sqlite3_errmsg(db));
        return;
    }
    index = 0;
    if(row>0){
        for(i=0;i<=row;i++){
            for(j=0;j<column;j++){
                printf("%-11s",resultSet[index++]);
            }
            printf("n");
        }
    }
    else{
        printf("no such person!n");
    }
}

//alertContact()
void alterContact(){
    //first,find the contact info to be altered.
    int i,index;
    int ret  = 0;
    int row = 0;
    int column = 0;
    int age = 0;
    char* name = NULL;
    char* relation = NULL;
    char* phone = NULL;
    char* sql = NULL;
    char** resultSet = NULL;

    name = (char*)malloc(sizeof(char)*10);
    printf("Input the name you want to alter:");
    scanf("%s",name);

    sql = (char*)malloc(sizeof(char)*128);
    sprintf(sql,resultSet[index++]);
            }
            printf("n");
        }
        sqlite3_free_table(resultSet);

        //exist,then alter
        relation = (char*)malloc(sizeof(char)*10);
        phone = (char*)malloc(sizeof(char)*12);

        printf("input the new data (age relation phone):");
        scanf("%d %s %s",phone);
        //printf(" %d， %s，%sn",phone);


        sprintf(sql,"update info set age=%d,relation='%s',phone='%s' where name='%s';",phone,name);
        //printf("%sn",sql);
        ret = sqlite3_exec(db,0);
        if(ret != SQLITE_OK){
            printf("failed!n");
        }
        else{
            printf("ok!n");
        }
        free(relation);
        free(phone);
    }
    else{
        printf("no such person!n");
    }
    free(sql);
    free(name);

}

//delete Contact
void deleteContact(){
    int ret = 0;
    char* name = NULL;
    char* sql = NULL;

    name = (char*)malloc(sizeof(char)*10);
    sql = (char*)malloc(sizeof(char)*64);

    printf("Input the name of contact you want to delete:");
    scanf("%s",name);

    sprintf(sql,"delete from info where name='%s';",name);

    //to be simple,there will be no warning if the contact does not exist
    ret = sqlite3_exec(db,0);
    if(ret != SQLITE_OK){
        printf("delete err:%s",sqlite3_errmsg(db));
    }
    else{
        printf("ok!");
    }

    free(name);
    free(sql);

}


int main()
{
    int ret = 0;
    int choice = 0;
    int ch = 0;
    char* errmsg = NULL;
    char* sql = NULL;

    //open the db if exist or create it
    ret = sqlite3_open("contact.db",&db);
    if(ret){
        fprintf(stderr,"Cannot open database:%sn",sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
    else{
        printf("Open database successfully...n");
    }

    //create the table info if not exists
    sql = (char*)malloc(sizeof(char)*128);//
    //strcpy(sql,);
    //printf("Copy sql successfullyn");
    ret = sqlite3_exec(db,"create table if not exists info(
                    name varchar(10) primary key,
                    age smallint,
                    relation varchar(10),
                    phone varchar(11));",&errmsg);
    if(ret != SQLITE_OK){
        //printf("Create table errorn");
        fprintf(stderr,"Create table err:%sn",sqlite3_errmsg(db));
    }
    //printf("Create table successfullyn");

    //insert some initial records,//it will cause a err if not the frist time,but that does not matter
    strcpy(sql,"insert into info values('zhu',22,'本人','15109217871');");

    ret = sqlite3_exec(db,&errmsg);
    if(ret != SQLITE_OK){
        fprintf(stderr,"Insert record err:%sn",sqlite3_errmsg(db));
    }
    free(sql);
    //printf("Insert  record successfullyn");

    //main menu
    while(1){
        choice = showMenu();                        //show the menu and get the user's choose
        switch(choice){
            case 1:
                displayAll();                       //show all records in db
                break;
            case 2:
                addContact();
                break;
            case 3:
                deleteContact();
                break;
            case 4:
                alterContact();
                break;
            case 5:
                findContact();
                break;
            default:
                break;
        }

        //if back to main menu or not
        printf("nBack to Menu 1(yes) / 0(no)?");
        scanf("%d",&ch);
        if(ch == 0){
            break;
        }
        //printf("33[2J");
        system("clear");
    }

    sqlite3_close(db);
    return 0;
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!