Visual Studio2022连接SQL Server数据库项目展示
本文基于Visual Studio2022和SQL Server2008通过ODBC将数据库与后端连接在一起。
一、设计要求及关系模式
1. 设计需求
- 读者基本信息的输入、查询、修改,包括借书证编号、读者姓名、读者性别、读者种类。
- 书籍类别标准的制定、输入、查询、修改,包括类别编号、类别名称。
- 书籍库存信息的输入、查询、修改,包括书籍编号、书籍名称、书籍类别、作者姓名、出版社名称、出版日期、登记日期。
- 借书信息的输入、查询、修改,包括读者借书证编号、书籍编号、借书日期。
- 还书信息的输入、查询、修改,包括借书证编号、书籍编号、还书日期。
- 超期还书罚款输入、查询、修改、删除,还书超出期限包括超出期限还书的读者借书证号,书籍编号,罚款金额。
2. 关系模式
- 书籍类别(种类编号,种类名称)。
- 读者(借书证编号,读者姓名,读者性别,读者种类,登记时期)。
- 书籍(书籍编号,书籍名称,书籍类别,书籍作者,出版社名称,出版日期,登记日期)。
- 借阅(借书证编号,书籍编号,读者借书时间)。
- 还书(借书证编号,书籍编号,读者还书时间)。
- 罚款(借书证编号,读者姓名,书籍编号,书籍名称,罚款金额,借阅时间)。
二、系统的数据流程图
三、重要功能
1. 显示图书
void showAll() {
Connect();
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book"), SQL_NTS);
ret = SQLExecute(hstmt);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR str1[10], str2[30], str3[30], str4[30], str5[30], str6[30], str7[30], str8[5];
SQLINTEGER len_str1, len_str2, len_str3, len_str4, len_str5, len_str6, len_str7, len_str8;
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
printf("%-10s%-20s%-20s%-30s%-30s%-20s%-20s%-15s\n", "书籍编号", "书籍名称", "书籍类别", "书籍作者", "出版社名称", "出版日期", "登记日期", "是否被借出");
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 50, &len_str1);
SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 50, &len_str2);
SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 50, &len_str3);
SQLGetData(hstmt, 4, SQL_C_CHAR, str4, 50, &len_str4);
SQLGetData(hstmt, 5, SQL_C_CHAR, str5, 50, &len_str5);
SQLGetData(hstmt, 6, SQL_C_CHAR, str6, 50, &len_str6);
SQLGetData(hstmt, 7, SQL_C_CHAR, str7, 50, &len_str7);
SQLGetData(hstmt, 8, SQL_C_CHAR, str8, 50, &len_str8);
printf("%-10s%-20s%-20s%-30s%-30s%-20s%-20s%-15s\n", str1, str2, str3, str4, str5, str6, str7, str8);
}
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
}
free();
}
2. 查找图书
void findBook() {
char field[50];
int database_score, c_score;
Connect();
printf("=======================================================\n");
printf("* [1]. 按书籍编号 *\n");
printf("* [2]. 按书籍名称 *\n");
printf("* [3]. 按书籍类别 *\n");
printf("* [4]. 按书籍作者 *\n");
printf("* [5]. 按出版社名称 *\n");
printf("* [0]. 退出此次操作 *\n");
printf("=======================================================\n");
int flag = 1, flag1 = 1, flag2 = 1;
char ch, temp;
printf("请输入编号:");
while (flag) {
scanf_s("%c", &ch);
flag2 = 1;
while (temp = getchar()) {
if (temp != '\n')
flag2 = 0;
if (temp == '\n')
break;
}
if (flag2) {
switch (ch) {
case '1': {
flag = 0;
printf("请输入书籍编号\n");
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book where b_id like ?"), SQL_NTS);
break;
}
case '2': {
flag = 0;
printf("请输入书籍名称\n");
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book where b_name like ?"), SQL_NTS);
break;
}
case '3': {
flag = 0;
printf("请输入书籍类别\n");
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book where book_style like ?"), SQL_NTS);
break;
}
case '4': {
flag = 0;
printf("请输入书籍作者\n");
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book where b_author like ?"), SQL_NTS);
break;
}
case '5': {
flag = 0;
printf("请输入出版社名称\n");
SQLPrepare(hstmt, (SQLWCHAR*)(L"select * from BMS.dbo.system_book where b_pub like ?"), SQL_NTS);
break;
}
case '0':
flag = 0;
flag1 = 0;
break;
default:
flag2 = 0;
break;
}
}
if (flag2 == 0)
printf("输入有误,请重新输入!\n");
}
if (flag1) {
scanf("%[^\n]", field);
getchar();
char fieldF[50] = "%", fieldT[2] = "%";
strcat(fieldF, field);
strcat(fieldF, fieldT);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(fieldF), 0, &fieldF, 100, NULL);
ret = SQLExecute(hstmt);
if (ret == SQL_SUCCESS)
{
SQLCHAR str1[10], str2[30], str3[30], str4[30], str5[30], str6[30], str7[30], str8[5];
SQLINTEGER len_str1, len_str2, len_str3, len_str4, len_str5, len_str6, len_str7, len_str8;
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
printf("%-10s%-20s%-20s%-30s%-30s%-30s%-20s%-15s\n", "书籍编号", "书籍名称", "书籍类别", "书籍作者", "出版社名称", "出版日期", "登记日期", "是否被借出");
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 50, &len_str1);
SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 50, &len_str2);
SQLGetData(hstmt, 3, SQL_C_CHAR, str3, 50, &len_str3);
SQLGetData(hstmt, 4, SQL_C_CHAR, str4, 50, &len_str4);
SQLGetData(hstmt, 5, SQL_C_CHAR, str5, 50, &len_str5);
SQLGetData(hstmt, 6, SQL_C_CHAR, str6, 50, &len_str6);
SQLGetData(hstmt, 7, SQL_C_CHAR, str7, 50, &len_str7);
SQLGetData(hstmt, 8, SQL_C_CHAR, str8, 50, &len_str8);
printf("%-10s%-20s%-20s%-30s%-30s%-30s%-20s%-15s\n", str1, str2, str3, str4, str5, str6, str7, str8);
}
printf("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n");
}
}
else
printf("成功退出此次操作\n");
free();
}
3. 借阅图书
void borrowBook() {
Connect();
char temp[10] = { 0 };
printf("请输入要借阅图书的书籍编号:");
scanf("%s", temp);
getchar();
SQLPrepare(hstmt, (SQLWCHAR*)(L"select isborrowed from BMS.dbo.system_book where b_id=?"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &temp, 100, NULL);
SQLExecute(hstmt);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
{
SQLCHAR str1[10];
SQLINTEGER len_str1;
while (SQLFetch(hstmt) != SQL_NO_DATA)
{
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 50, &len_str1);
}
if (strcmp(str1, "否") == 0) {
free();
Connect();
char input[20] = { 0 }, times[15];
printf("请输入借书证号:");
scanf("%s", input);
getchar();
timer(times);
SQLPrepare(hstmt, (SQLWCHAR*)(L"insert into BMS.dbo.borrow_record values(? , ? , ? )"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(input), 0, &input, 100, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(temp), 0, &temp, 100, NULL);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(times), 0, ×, 100, NULL);
ret=SQLExecute(hstmt);
if (ret == SQL_SUCCESS) {
free();
Connect();
SQLPrepare(hstmt, (SQLWCHAR*)(L"update BMS.dbo.system_book set isborrowed='是' where b_id=?"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &temp, 100, NULL);
ret=SQLExecute(hstmt);
if (ret == SQL_SUCCESS)
printf("书籍借阅成功\n");
else
printf("书籍借阅失败\n");
}
else
printf("书籍借阅失败\n");
}
else
printf("书籍已被借走,暂不可借\n");
}
free();
}
4. 归还图书
void returnBook() {
Connect();
char temp[10] = { 0 };
printf("请输入要归还图书的书籍编号:");
scanf("%s", temp);
getchar();
SQLPrepare(hstmt, (SQLWCHAR*)(L"select isborrowed from BMS.dbo.system_book where b_id=?"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &temp, 100, NULL);
SQLExecute(hstmt);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
SQLCHAR str1[10], str2[10];
SQLINTEGER len_str1, len_str2;
while (SQLFetch(hstmt) != SQL_NO_DATA) {
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 50, &len_str1);
}
if (strcmp(str1, "是") == 0) {
free();
Connect();
char input[20] = { 0 }, times[15];
printf("请输入借书证号:");
scanf("%s", input);
getchar();
if (ret == SQL_SUCCESS) {
SQLPrepare(hstmt, (SQLWCHAR*)(L"select r_id, b_id from BMS.dbo.borrow_record where b_id=?"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &temp, 100, NULL);
SQLExecute(hstmt);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
while (SQLFetch(hstmt) != SQL_NO_DATA) {
SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 50, &len_str1);
SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 50, &len_str2);
}
if (strcmp(str1, input) == 0 && strcmp(str2, temp) == 0) {
free();
Connect();
timer(times);
SQLPrepare(hstmt, (SQLWCHAR*)(L"insert into BMS.dbo.return_record values(? , ? , ? )"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(input), 0, &input, 100, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(temp), 0, &temp, 100, NULL);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(times), 0, ×, 100, NULL);
ret = SQLExecute(hstmt);
if (ret == SQL_SUCCESS) {
free();
Connect();
SQLPrepare(hstmt, (SQLWCHAR*)(L"update BMS.dbo.system_book set isborrowed='否' where b_id=?"), SQL_NTS);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, &temp, 100, NULL);
ret = SQLExecute(hstmt);
if (ret == SQL_SUCCESS)
printf("书籍归还成功\n");
else
printf("书籍归还失败\n");
}
else
printf("书籍归还失败\n");
}
}
}
}
else
printf("书籍未被借走\n");
}
free();
}
5. 数据库连接
void Connect() {
const char* SY1 = L"BMS";
unsigned char* SY = (unsigned char*)SY1;
const char* db21 = L"sa";
unsigned char* db2 = (unsigned char*)db21;
const char* pass1 = L"";
unsigned char* pass = (unsigned char*)pass1;
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, SQL_IS_INTEGER);
ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
ret = SQLConnect(hdbc, SY, SQL_NTS, db2, SQL_NTS, pass, SQL_NTS);
if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) {
printf("连接数据库失败!\n");
return;
}
ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
}
四、项目展示
1. 查询馆藏信息
2. 查询馆藏图书
3. 借阅馆藏图书
4. 归还馆藏图书
5. 查询借阅情况
6. 查询读者信息
7. 查询书籍类别
8. 查询未还信息
五、数据库连接方法
点击下面的链接查看如何使用Visual Studio2022连接SQL Server数据库项目展示
连接方法
六、项目源码获取
点击下面的链接可获得整个项目源代码,包括(C语言项目,SQL Server数据库文件)
图书馆管理系统