当前位置：首页 > 行业动态 > 正文

c 读写数据库 access

C语言读写Access数据库需借助ODBC或OLE DB等技术实现。

在C语言中读写Access数据库，通常采用ODBC（Open Database Connectivity）驱动的方式，这种方法不仅兼容性强，而且灵活性高，适用于多种数据库操作场景，以下是详细的步骤和示例代码：

一、配置ODBC数据源

1、打开ODBC数据源管理器：在Windows系统中，通过控制面板找到“管理工具”，然后选择“ODBC数据源（32位或64位）”。

c 读写数据库 access

2、添加数据源：在“用户DSN”或“系统DSN”标签页中，点击“添加”按钮，选择“Microsoft Access Driver (*.mdb, *.accdb)”作为驱动程序。

c 读写数据库 access

3、配置数据源：在弹出的窗口中，填写数据源名称（DSN），并选择要连接的Access数据库文件（.mdb或.accdb格式）。

二、在C语言中使用ODBC API

初始化ODBC环境

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
SQLRETURN ret;
void initODBC() {
    // 初始化环境句柄
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC environment handlen");
        exit(EXIT_FAILURE);
    }
    // 设置ODBC版本
    ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to set ODBC versionn");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
}

连接数据库

void connectToDatabase(const char *dsn) {
    // 分配连接句柄
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC connection handlen");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    // 连接到数据库
    ret = SQLConnect(hDbc, (SQLCHAR *)dsn, SQL_NTS, NULL, 0, NULL, 0);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to connect to the databasen");
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    } else {
        printf("Successfully connected to the Access databasen");
    }
}

执行SQL查询

void executeQuery(const char *query) {
    // 分配语句句柄
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC statement handlen");
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    // 执行SQL查询
    ret = SQLExecDirect(hStmt, (SQLCHAR *)query, SQL_NTS);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to execute SQL queryn");
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
}

读取查询结果

void fetchResults() {
    SQLCHAR columnName[128];
    SQLCHAR columnValue[128];
    SQLSMALLINT columns;
    SQLSMALLINT columnNameLength;
    SQLSMALLINT columnValueLength;
    // 获取列数
    ret = SQLNumResultCols(hStmt, &columns);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to get number of columnsn");
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    // 读取并打印每列的数据
    for (int i = 1; i <= columns; i++) {
        ret = SQLDescribeCol(hStmt, i, columnName, sizeof(columnName), &columnNameLength, NULL, 0, NULL, NULL);
        if (ret == SQL_SUCCESS) {
            printf("Column %d: %sn", i, columnName);
        } else {
            fprintf(stderr, "Failed to describe column %dn", i);
        }
    }
    // 循环读取行数据
    while ((ret = SQLFetch(hStmt)) == SQL_SUCCESS) {
        for (int i = 1; i <= columns; i++) {
            ret = SQLGetData(hStmt, i, SQL_C_CHAR, columnValue, sizeof(columnValue), NULL);
            if (ret == SQL_SUCCESS) {
                printf("%st", columnValue);
            } else {
                fprintf(stderr, "Failed to get data from column %dn", i);
            }
        }
        printf("n");
    }
}

断开连接并清理资源

void disconnectAndCleanup() {
    // 断开与数据库的连接
    SQLDisconnect(hDbc);
    // 释放连接句柄
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    // 释放环境句柄
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}

三、完整示例代码整合

将上述各个部分整合到一起，形成一个完整的C程序，用于连接Access数据库、执行查询并读取结果：

c 读写数据库 access

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
SQLRETURN ret;
void initODBC() {
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC environment handlen");
        exit(EXIT_FAILURE);
    }
    ret = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to set ODBC versionn");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
}
void connectToDatabase(const char *dsn) {
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC connection handlen");
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    ret = SQLConnect(hDbc, (SQLCHAR *)dsn, SQL_NTS, NULL, 0, NULL, 0);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to connect to the databasen");
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    } else {
        printf("Successfully connected to the Access databasen");
    }
}
void executeQuery(const char *query) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to allocate ODBC statement handlen");
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    ret = SQLExecDirect(hStmt, (SQLCHAR *)query, SQL_NTS);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to execute SQL queryn");
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
}
void fetchResults() {
    SQLCHAR columnName[128];
    SQLCHAR columnValue[128];
    SQLSMALLINT columns;
    SQLSMALLINT columnNameLength;
    SQLSMALLINT columnValueLength;
    ret = SQLNumResultCols(hStmt, &columns);
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "Failed to get number of columnsn");
        SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        SQLDisconnect(hDbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
        SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
        exit(EXIT_FAILURE);
    }
    for (int i = 1; i <= columns; i++) {
        ret = SQLDescribeCol(hStmt, i, columnName, sizeof(columnName), &columnNameLength, NULL, 0, NULL, NULL);
        if (ret == SQL_SUCCESS) {
            printf("Column %d: %sn", i, columnName);
        } else {
            fprintf(stderr, "Failed to describe column %dn", i);
        }
    }
    while ((ret = SQLFetch(hStmt)) == SQL_SUCCESS) {
        for (int i = 1; i <= columns; i++) {
            ret = SQLGetData(hStmt, i, SQL_C_CHAR, columnValue, sizeof(columnValue), NULL);
            if (ret == SQL_SUCCESS) {
                printf("%st", columnValue);
            } else {
                fprintf(stderr, "Failed to get data from column %dn", i);
            }
        }
        printf("n");
    }
}
void disconnectAndCleanup() {
    SQLDisconnect(hDbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
int main() {
    initODBC();
    connectToDatabase("DSN=MyAccessDB;"); // 替换为实际的DSN字符串
    executeQuery("SELECT * FROM MyTable"); // 替换为实际的查询语句
    fetchResults();
    disconnectAndCleanup();
    return 0;
}
access数据库c语言数据库操作

相关文章