通讯录管理系统（C+ODBC+SQL Server 2000）

#include<stdio.h>#include<stdlib.h>//include system() function#include<windows.h>#include<sql.h>#include<sqlext.h>#include<sqltypes.h>void display_contact(void);void add_contact(void);void search_contact(void);void update_contact(void);void delete_contact(void);SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//bufferSQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_wordint main(){system("title Phone_number_manager");int choice=0;int flag=1;//indicate exitting systemchar *menu[6]={"添加联系人","查找联系人","修改联系人","删除联系人","显示所有联系人","退出系统"};while(flag){//if flag equal 0,exit systemprintf("  Phone number manager  \n");printf("*************************\n");for(int i=0;i<6;i++)printf("%d.%s\n",i+1,menu[i]);//printf("%d.%s\n",i+1,*(menu+i));printf("Please input the number of your choice:\n");scanf("%d",&choice);switch(choice){case 1:add_contact();   system("pause");   system("cls");   break;case 2:search_contact();               system("pause");   system("cls");   break;case 3:update_contact();               system("pause");   system("cls");   break;case 4:delete_contact();               system("pause");   system("cls");   break;case 5:display_contact();   system("pause");   system("cls");   break;case 6:flag=0;       system("cls");   break;default:system("cls");    printf("input error:please input the number of your choice again:\n");    }}return 0;}/************************************************************************************************/void add_contact(){//添加联系人SQLRETURN ret;SQLHENV henv;//SQLHANDLE henvSQLHDBC hdbc;//SQLHANDLE hdbcSQLHSTMT hstmt;//SQLHANDLE hstmtret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"112358",SQL_NTS);//连接数据库    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄   //SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//buffer   //SQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_word   SQLCHAR sql[]="INSERT INTO contacts VALUES(?,?,?,?,?,?,?)";   SQLINTEGER P = SQL_NTS;   printf("请输入姓名：\n");   scanf("%s",name);   printf("请输入工作单位：\n");   scanf("%s",company);   printf("请输入手机号码：\n");   scanf("%s",phone);   printf("请输入办公室电话号码：\n");   scanf("%s",office_tel);   printf("请输入家庭电话号码：\n");   scanf("%s",family_tel);   printf("请输入email：\n");   scanf("%s",email);   printf("请输入组别（亲人、朋友、同事、陌生人）：\n");   scanf("%s",groups);   ret=SQLPrepare(hstmt,sql,SQL_NTS);//准备SQL语句   ret=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,name,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,company,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,phone,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,office_tel,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,family_tel,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,email,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,groups,50,&P);//绑定参数   ret=SQLExecute(hstmt);//执行SQL语句   if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)  printf("添加成功！\n");   else printf("添加失败！\n");     display_contact();              SQLDisconnect(hdbc);//断开与数据库的连接}    else printf("连接数据库失败!\n");SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄}/******************************************************************************************************************/void display_contact(){//显示所有联系人    SQLRETURN ret;SQLHENV henv;//SQLHANDLE henvSQLHDBC hdbc;//SQLHANDLE hdbcSQLHSTMT hstmt;//SQLHANDLE hstmtret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"112358",SQL_NTS);//连接数据库    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄       SQLCHAR sql[]="SELECT * FROM contacts";   //SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//buffer   //SQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_word   ret=SQLExecDirect(hstmt,sql,SQL_NTS);//直接执行SQL语句       if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){       SQLBindCol(hstmt,1,SQL_C_CHAR,name,50,&len_nam);//通过列绑定获取数据   SQLBindCol(hstmt,2,SQL_C_CHAR,company,50,&len_com);   SQLBindCol(hstmt,3,SQL_C_CHAR,phone,50,&len_pho);   SQLBindCol(hstmt,4,SQL_C_CHAR,office_tel,50,&len_off);   SQLBindCol(hstmt,5,SQL_C_CHAR,family_tel,50,&len_fam);   SQLBindCol(hstmt,6,SQL_C_CHAR,email,50,&len_ema);   SQLBindCol(hstmt,7,SQL_C_CHAR,groups,50,&len_gro);   printf("姓名       公司           手机号码         办公室电话 家庭电话   email                组别\n");   printf("******************************************************************************************\n");   ret=SQLFetch(hstmt);//移动光标   if(ret==SQL_NO_DATA) printf("未找到该联系人！\n");   while(ret!=SQL_NO_DATA){//遍历结果集      printf("%-10s %-15s %-15s %-10s %-10s %-20s %-10s\n",name,company,phone,office_tel,family_tel,email,groups);     ret=SQLFetch(hstmt);   }           printf("******************************************************************************************\n");   SQLINTEGER number_row;   ret=SQLRowCount(hstmt,&number_row);//查询被影响的行数   if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)      printf("通讯录中共有%d个联系人\n",number_row);   else printf("查询结果集记录个数失败！\n");   }else printf("查询数据库操作失败！\n");                     SQLDisconnect(hdbc);//断开与数据库的连接}    else printf("连接数据库失败!\n");SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄}/************************************************************************************************************************/void search_contact(){//查找联系人    SQLRETURN ret;SQLHENV henv;//SQLHANDLE henvSQLHDBC hdbc;//SQLHANDLE hdbcSQLHSTMT hstmt;//SQLHANDLE hstmtret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"112358",SQL_NTS);//连接数据库    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄   //SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//buffer   //SQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_word   SQLCHAR sql[]="SELECT * FROM contacts WHERE name = ? ";   SQLCHAR myname[50];   SQLINTEGER P = SQL_NTS;   printf("请输入要查找联系人的姓名：\n");   scanf("%s",myname);   ret=SQLPrepare(hstmt,sql,SQL_NTS);//准备SQL语句   ret=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,myname,50,&P);//绑定参数   ret=SQLExecute(hstmt);//执行SQL语句   if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   SQLBindCol(hstmt,1,SQL_C_CHAR,name,50,&len_nam);//通过列绑定获取数据   SQLBindCol(hstmt,2,SQL_C_CHAR,company,50,&len_com);   SQLBindCol(hstmt,3,SQL_C_CHAR,phone,50,&len_pho);   SQLBindCol(hstmt,4,SQL_C_CHAR,office_tel,50,&len_off);   SQLBindCol(hstmt,5,SQL_C_CHAR,family_tel,50,&len_fam);   SQLBindCol(hstmt,6,SQL_C_CHAR,email,50,&len_ema);   SQLBindCol(hstmt,7,SQL_C_CHAR,groups,50,&len_gro);   printf("姓名       公司           手机号码         办公室电话 家庭电话   email                组别\n");   printf("******************************************************************************************\n");   ret=SQLFetch(hstmt);//移动光标   if(ret==SQL_NO_DATA) printf("未找到该联系人！\n");   while(ret!=SQL_NO_DATA){//遍历结果集      printf("%-10s %-15s %-15s %-10s %-10s %-20s %-10s\n",name,company,phone,office_tel,family_tel,email,groups);     ret=SQLFetch(hstmt);   }     }   else printf("准备执行查询失败！\n");                 SQLDisconnect(hdbc);//断开与数据库的连接}    else printf("连接数据库失败!\n");SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄}/************************************************************************************************************************/void update_contact(){    SQLRETURN ret;SQLHENV henv;//SQLHANDLE henvSQLHDBC hdbc;//SQLHANDLE hdbcSQLHSTMT hstmt;//SQLHANDLE hstmtret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"112358",SQL_NTS);//连接数据库    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄   //SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//buffer   //SQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_word   SQLCHAR sql[]="UPDATE contacts SET company=?,phone=?,office_tel=?,family_tel=?,email=?,groups=? WHERE name=?";   SQLINTEGER P = SQL_NTS;   printf("请输入要修改的联系人姓名：\n");   scanf("%s",name);   printf("请输入工作单位：\n");   scanf("%s",company);   printf("请输入手机号码：\n");   scanf("%s",phone);   printf("请输入办公室电话号码：\n");   scanf("%s",office_tel);   printf("请输入家庭电话号码：\n");   scanf("%s",family_tel);   printf("请输入email：\n");   scanf("%s",email);   printf("请输入组别（亲人、朋友、同事、陌生人）：\n");   scanf("%s",groups);   ret=SQLPrepare(hstmt,sql,SQL_NTS);//准备SQL语句   ret=SQLBindParameter(hstmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,name,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,company,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,phone,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,office_tel,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,family_tel,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,email,50,&P);//绑定参数   ret=SQLBindParameter(hstmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,groups,50,&P);//绑定参数   ret=SQLExecute(hstmt);//执行SQL语句   if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)  printf("修改成功！\n");   else printf("修改失败！\n");                SQLDisconnect(hdbc);//断开与数据库的连接}    else printf("连接数据库失败!\n");SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄}/***********************************************************************************************************/void delete_contact(){//删除联系人    SQLRETURN ret;SQLHENV henv;//SQLHANDLE henvSQLHDBC hdbc;//SQLHANDLE hdbcSQLHSTMT hstmt;//SQLHANDLE hstmtret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);//设置环境属性ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄ret=SQLConnect(hdbc,(SQLCHAR*)"phonesql",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"112358",SQL_NTS);//连接数据库    if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO){   ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);//申请SQL语句句柄   //SQLCHAR name[50],company[50],phone[50],office_tel[50],family_tel[50],email[50],groups[50];//buffer   //SQLINTEGER len_nam,len_com,len_pho,len_off,len_fam,len_ema,len_gro;//length_now_word   SQLCHAR sql[]="DELETE FROM contacts WHERE name = ? ";   SQLCHAR myname[50];   SQLINTEGER P = SQL_NTS;   printf("请输入要删除联系人的姓名：\n");   scanf("%s",myname);   ret=SQLPrepare(hstmt,sql,SQL_NTS);//准备SQL语句   ret=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,50,0,myname,50,&P);//绑定参数   ret=SQLExecute(hstmt);//执行SQL语句   if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)  printf("删除成功！\n");    else printf("删除失败！\n");               SQLDisconnect(hdbc);//断开与数据库的连接}    else printf("连接数据库失败!\n");SQLFreeHandle(SQL_HANDLE_DBC,hdbc);//释放连接句柄SQLFreeHandle(SQL_HANDLE_ENV,henv);//释放环境句柄}