网络编程9.10

使用数据库完成工人管理系统:

ubuntu@ubuntu:DB$ 
ubuntu@ubuntu:DB$ cat 2.c
#include <myhead.h>
#include <sqlite3.h>
#include <string.h>typedef struct
{int id;char name[20];double salary;
} Worker;int do_insert(sqlite3 *ppDb)
{Worker work;printf("请输入工号：");scanf("%d", &work.id);getchar(); // 清除缓冲区printf("请输入姓名：");fgets(work.name, sizeof(work.name), stdin);work.name[strcspn(work.name, "\n")] = 0; // 移除换行符printf("请输入薪水：");scanf("%lf", &work.salary);char *errmsg = NULL;char sqlite[200];snprintf(sqlite, sizeof(sqlite), "INSERT INTO Work VALUES(%d, '%s', %lf);",work.id, work.name, work.salary);if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);return -1;}printf("添加工人信息成功\n");return 0;
}int do_delete(sqlite3 *ppDb)
{char name[20];printf("请输入要删除工人的姓名：");fgets(name, sizeof(name), stdin);name[strcspn(name, "\n")] = 0; // 移除换行符char *errmsg = NULL;char sqlite[200];snprintf(sqlite, sizeof(sqlite), "DELETE FROM Work WHERE name='%s';", name);if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);return -1;}printf("删除工人信息成功\n");return 0;
}int do_update(sqlite3 *ppDb)
{char name[20];double new_salary;printf("请输入要修改工人的姓名：");fgets(name, sizeof(name), stdin);name[strcspn(name, "\n")] = 0; // 移除换行符printf("请输入新的薪水：");scanf("%lf", &new_salary);getchar(); // 清除缓冲区char *errmsg = NULL;char sqlite[200];snprintf(sqlite, sizeof(sqlite), "UPDATE Work SET salary=%lf WHERE name='%s';", new_salary, name);if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);return -1;}printf("修改工人信息成功\n");return 0;
}int callback(void *arg, int n, char **msgtext, char **msgtable)
{int i;for(i = 0; i < n; i++){printf("%s\t", msgtable[i]);}putchar(10);for(i = 0; i < n; i++){printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");}putchar(10);return 0;
}int do_seek(sqlite3 *ppDb)
{char key[20];printf("请输入你要查找的工人姓名：");fgets(key, sizeof(key), stdin);key[strcspn(key, "\n")] = 0; // 移除换行符char *errmsg = NULL;char sqlite[200];snprintf(sqlite, sizeof(sqlite), "SELECT * FROM Work WHERE name='%s';", key);if(sqlite3_exec(ppDb, sqlite, callback, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);return -1;}return 0;
}int show_all(void *arg, int n, char **msgtext, char **msgtable)
{static int row = 0;int i;if (row == 0) {for(i = 0; i < n; i++){printf("%s\t", msgtable[i]);}printf("\n");}for(i = 0; i < n; i++){printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");}printf("\n");row++;return 0;
}int do_showall(sqlite3 *ppDb)
{char *errmsg = NULL;const char *sqlite = "SELECT * FROM Work;";if(sqlite3_exec(ppDb, sqlite, show_all, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);return -1;}return 0;
}void menu(sqlite3 *ppDb)
{int n;printf("\n\t\t\t*********肖申克的救赎*********\n");printf("\t\t\t1、插入一个工人信息\n");printf("\t\t\t2、删除一个工人信息\n");printf("\t\t\t3、修改一个工人信息\n");printf("\t\t\t4、查找一个工人信息\n");printf("\t\t\t5、输出所有工人信息\n");printf("\t\t\t0、退出\n");printf("请输入你的选择：");scanf("%d", &n);getchar(); // 清除缓冲区switch(n){case 1:do_insert(ppDb);break;case 2:do_delete(ppDb);break;case 3:do_update(ppDb);break;case 4:do_seek(ppDb);break;case 5:do_showall(ppDb);break;case 0:exit(0);default:printf("输入有误，请重新输入\n");}
}int main(int argc, const char *argv[])
{sqlite3 *ppDb;if(sqlite3_open("./hhh.db", &ppDb) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);return -1;}printf("数据库打开成功\n");char *errmsg = NULL;const char *sqlite = "CREATE TABLE IF NOT EXISTS Work(id INT, name TEXT, salary REAL);";if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK){printf("错误码：%d 错误信息：%s, 出错行是：%d\n",sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);printf("错误信息：%s\n", errmsg);sqlite3_free(errmsg);sqlite3_close(ppDb);return -1;}while(1){menu(ppDb);}sqlite3_close(ppDb);printf("数据库关闭成功\n");return 0;
}