第二十四天

/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co.,     Ltd. 
File name：
Author：Jerey_Jobs    Version:0.1    Date: 
Description：使用数据库编程
Funcion List: 
*****************************************************/
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>

void safe_flush(FILE *fp)
{
    int ch;
    while((ch = fgetc(fp)) != EOF && ch != '\n');
}

void menu()
{
    printf("******************************************\n");
    printf("*****          菜单                    ***\n");
    printf("*****       1.输入信息                 ***\n");   //输入信息
    printf("*****       2.查找                     ***\n");   //查找
    printf("*****       3.显示所有信息             ***\n");   //显示所有信息
    printf("*****       4.删除                     ***\n");   //删除
    printf("*****       5.删表退出                 ***\n");
    printf("*****       6.退出                     ***\n");
}

void create_table(sqlite3 * db)             //创建表
{
    char * sql;
    char * errmsg;
    int rec;

    sql = "create table if not exists  address_book (id integer primary key, name text, address text, tel text); ";
    rec = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

    if(rec != SQLITE_OK)
    {
        printf("create table error!\n");
        exit(-1);
    }
}

void inquire_nocd(sqlite3 * db)               //查找
{
    int id;
    char sql[100];
    char * errmsg;
    char ** azresult;
    int i;
    int rec;
    int n_col;
    int n_row;


    safe_flush(stdin);
    printf("input id:");
    scanf("%d", &id);

    sprintf(sql, "select name, address, tel from address_book where id = %d;",id);

    rec = sqlite3_get_table(db, sql, &azresult, &n_row, &n_col, &errmsg);

    if(rec != SQLITE_OK)
    {
        printf("inquire error!\n");
        exit(-1);
    }

    for(i = 0; i < n_col; i++)
    {
        printf("%10s", azresult[i]);
    }
    printf("\n");

    for( ; i < (n_row + 1) * n_col; i++)
    {
        printf("%10s", azresult[i]);
    }
    printf("\n");

    sqlite3_free_table(azresult);

}

void insert_record(sqlite3 * db)      //插入
{
    char sql[100];
    char *errmsg;
    int rec;
    int id;
    char name[20];
    char address[50];
    char tel[20];
    int i, n;


    safe_flush(stdin);
    printf("插入次数:");
    scanf("%d", &n);

    for(i = 0; i < n; i++)
    {
        printf("输入 id  name address 和 tel\n");
        scanf("%d%s%s%s", &id, name, address, tel);

        sprintf(sql, "insert into address_book (id, name, address, tel) values (%d, '%s', '%s', '%s');", id, name, address, tel);

        rec = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

        if(rec != SQLITE_OK)
        {
            printf("insert table error!\n");
            exit(-1);
        }
    }
}

int  display(sqlite3 * db)                          //显示所有信息
{
    char * sql;
    char * errmsg;
    char ** azresult;
    int n_col;
    int n_row;
    int i;
    int rec;

    sql = "select * from address_book;";

    rec = sqlite3_get_table(db, sql, &azresult, &n_row, &n_col, &errmsg);

    if(rec != SQLITE_OK)
    {
        printf("display error!\n");
        exit(-1);
    }

    for(i = 0; i < n_col; i++)
    {
        printf("%10s", azresult[i]);
    }
    printf("\n");

    for( ; i < (n_row + 1) * n_col; i++)
    {
        printf("%10s", azresult[i]);
        if((i + 1) % n_col == 0)
        {
            printf("\n");
        }
    }

    sqlite3_free_table(azresult);

    return 0;
}




void delete(sqlite3 * db)
{
    char sql[100];
    char * errmsg;
    int rec;
    int id;


    safe_flush(stdin);
    printf("input id:");
    scanf("%d", &id);

    sprintf(sql, "delete from address_book where id = %d", id);

    rec = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

    if(rec != SQLITE_OK)
    {
        printf("delete error!\n");
        exit(-1);
    }
    else
    {
        printf("delete success!\n");
    }
}

void drop_table(sqlite3 * db)
{
    char * sql;
    char * errmsg;
    int rec;
    sql = "drop table address_book;";

    rec = sqlite3_exec(db, sql, NULL, NULL, &errmsg);

    if(rec != SQLITE_OK)
    {
        printf("drop error!\n");
        exit(-1);
    }
}

int main()
{
    char dd;
    int flag = 1;
    int i;
    sqlite3 * db;
    int rec;

    menu();


    rec = sqlite3_open("database.db", &db);

    if(rec != SQLITE_OK)
    {

        printf("open error,%s\n", sqlite3_errmsg(db));
        exit(-1);
    }
    else
    {
        printf("open success!\n");
    }

    create_table(db);

    while(flag)
    {
        printf("选择服务(1~5):");
        scanf("%d", &i);
        //safe_flush(stdin);
        switch(i)
        {
            case 1:
                insert_record(db);
                break;
            case 2:
                inquire_nocd(db);
                break;
            case 3:
                display(db);
                break;
            case 4:
                delete(db);
                break;
            case 5:
                drop_table(db);
                flag = 0;
                break;
            default:
                flag = 0;
                break;
        }


        safe_flush(stdin);
        printf("继续 y or n?\n");
        scanf("%c", &dd);

        if(dd == 'n')
        {
            flag = 0;
        }
    }

    return 0;
}