C语言通讯录版本三：数据库的使用

myhead.h

#ifndef MYHEAD_H_
#define MYHEAD_H_

#include 
#include 
#include 
#include

int create_table(sqlite3 * pdb);
void insert_record(sqlite3 * pdb);
void inquire_nocb(sqlite3 * pdb);
void delete_row(sqlite3 * pdb);
void updata(sqlite3 * pdb);
void find(sqlite3 *pdb);
void menu();  
void welcome();

#endif



 

function.c

#include "../../include/myhead.h"


void welcome()
{
    printf("*********************************************************************n");
    printf("*******************                               *******************n");
    printf("*******************          Welocome             *******************n");
    printf("*******************                               *******************n");
    printf("*********************************************************************n");
    printf("nnn");
    
}

void menu()
{
    printf("***************     welcome to use  *******************n");  
    printf("**1 添加通讯录成员                   2 显示通讯录成员**n");

	printf("**3 查询通讯录成员                   4 删除通讯录成员**n");

    printf("**5 修改通讯录成员                   6 退出通讯录系统**n");
    printf("*****************                    ******************n");
	printf("*******************************************************n");
}

int create_table(sqlite3 * pdb)
{
	char * errmsg = NULL;
    char * sql;
	int ret;

	sql = "create table if not exists mytable (id integer primary key,name text,age integer,telephone text);";

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

	if(SQLITE_OK != ret)
	{
		printf("create table failed! %sn",errmsg);
		return -1;
	}
	else
	{
		return SQLITE_OK;
	}
}

void insert_record(sqlite3 * pdb)
{
	char * errmsg = NULL;
    char sql[100];
	int ret;
	int id;
    int age;
    char telephone[30];
	char name[30];

	printf("please input id 1 name a age 20  telephone 13656891002:n");
	scanf("%d%s%d%s",&id,name,&age,telephone);

    if(strlen(telephone) != 11)
    {
        printf("please input again!n");
        printf("please input telephone 13656891002:n");
	    scanf("%s",telephone);
    }

	sprintf(sql,"insert into mytable(id,name,age,telephone) values (%d,'%s',%d,'%s');",id,name,age,telephone);

    ret = sqlite3_exec(pdb,sql,NULL,NULL,&errmsg);

	if(SQLITE_OK != ret)
	{
		printf("insert error 1! %sn",errmsg);
		exit(-1);
	}
    else
    {
        printf("insert successful ! n");
    }

}

void inquire_nocb(sqlite3 * pdb)
{
    char *sql = NULL;
	char ** azresult = NULL;
	char * errmsg = NULL;
	int n_row,n_col;
	int ret,i;

	sql = "select * from mytable;";

	ret = sqlite3_get_table(pdb,sql,&azresult,&n_row,&n_col,&errmsg);

	if(SQLITE_OK != ret)
	{
		printf("get tabble error! %sn",errmsg);
		exit(-1);
	}

    printf("n_row = %d  n_col = %dn",n_row,n_col); 

	printf("the result of inquire is:n");

	for(i = 0; i < (n_row + 1) * n_col ;i++)
	{
		printf("%st",azresult[i]);

		if((i + 1) % n_col == 0)
		{
			printf("n");
		}
	}

	sqlite3_free_table(azresult);
}

void delete_row(sqlite3 * pdb)
{
    char * errmsg = NULL;
    char sql[100];
	int id;
    
	printf("please input id you want to delete !n");
	scanf("%d",&id);
		
	sprintf(sql, "delete from mytable where id = %d", id);
	
	if(sqlite3_exec(pdb, sql, NULL, NULL, &errmsg ) != SQLITE_OK)
	{
		printf("%sn", errmsg);
	}
	else
	{
		printf("Delete successful !n");	
	}

}

void updata(sqlite3 * pdb)
{
    char * errmsg = NULL;
    char sql[100];
	int id;
    int age;
    char tel[30];
	char name[30];
    
	printf("please input id you want to update !n");
	scanf("%d",&id);
   // getchar();
    printf("please input the name a age 19 telephone 13656891111 !n");
    //scanf("%s%d%d",&id,name,&age,&telephone);
    scanf("%s",name);
   // getchar();
    scanf("%d",&age);
    //3
    scanf("%s",tel);

    if(strlen(tel) != 11)
    {
        printf("please input again!n");
        printf("please input tel 13656891002:n");
	    scanf("%s",tel);
    }
   
	//sprintf(sql,"update mytable set name = '%s' where id = %d;",name,id);
	
	sprintf(sql, "update mytable set name = '%s' ,age = %d ,telephone = '%s' where id = %d;",name,age,tel,id);
    //sprintf(sql, "update mytable set age = %d  where id = %d;",age,id);
    //sprintf(sql, "update mytable set telephone = %d where id = %d;",telephone,id);
    //sprintf(sql, "update mytable set name = '%s' ,age = %d  where id = %d;",name,age,id);
	
	if(sqlite3_exec(pdb, sql, NULL, NULL, &errmsg ) != SQLITE_OK)
	{
		printf("%sn", errmsg);
	}
	else
	{
		printf("uodate successfuln");	
	}
}

void find(sqlite3 *pdb)
{
    char * errmsg = NULL;
    char sql[100];
	int id;
    int i;

	char ** azresult = NULL;
	int n_row,n_col;
    
	printf("please input id you want to find !n");
	scanf("%d",&id);
		
	sprintf(sql, "select* from mytable  where id = %d;",id);
	if(sqlite3_get_table(pdb,sql,&azresult,&n_row,&n_col,&errmsg) != SQLITE_OK)
	{
		printf("%sn", errmsg);
	}
	else
	{
        for(i = 0; i < (n_row + 1) * n_col ;i++)
	    {
		    printf("%st",azresult[i]);

		    if((i + 1) % n_col == 0)
		    {
			    printf("n");
		    }
	    }
        if(n_col == 0)
        {
            printf("NO found n!");
        }
        else
        {
           printf("find successful !n"); 
        }

			
	}
}

main.c

#include "../../include/myhead.h"

int main()
{
    sqlite3 * pdb = NULL;
    int num;
	int ret;

	ret = sqlite3_open("mydatabase.db",&pdb);
    
    welcome();

    if(SQLITE_OK == ret)
	{
		printf("you have opened a sqlite3 database successfull!n");

        if(SQLITE_OK != create_table(pdb))
	    {
		    sqlite3_close(pdb);
		    return 0;
	    }
        else
        {
            printf("table is created !n");
        }

        while (1)
        {
            menu();
            printf("Please select an actionn");
            scanf("%d",&num);
        

            switch(num)
            {
                case 1:
                        insert_record(pdb);;
				        
                        break;
            
                case 2:
                        inquire_nocb(pdb);

                        break;

                case 3:
                        find(pdb);

                        break;

                case 4:
                        delete_row(pdb);

                        break;

                case 5:
                        updata(pdb);

                        break;
               case 6:
                        sqlite3_close(pdb);
                        printf("Exit successful !n");
                        exit(0);

                        break;

            }
                
        }
	}
	else
	{
		printf("can't open database! %sn",sqlite3_errmsg(pdb));
		sqlite3_close(pdb);
		exit(EXIT_FAILURE);
	}
    
    
	return 0;
    
}