C++连接SqlServer

C++连接SqlServer

连接外加查询

sqlConnection.h

#pragma once
#ifndef SQLCONNECTION_H
#define SQLCONNECTION_H

#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <fstream>
#include <string>

// 参数编写成结构体
struct DatabaseConnectionParams {
	std::wstring driver;
	std::wstring server;
	std::wstring database;
	std::wstring username;
	std::wstring password;
	std::wstring table;
};

void queryAndPrint(SQLHDBC hDbc, const SQLWCHAR* query);

void sqlConnectionQueryAndPrint(const DatabaseConnectionParams& params);

std::string wstring_to_string(const std::wstring& wstr);



#endif // !sqlConnection.h

sqlConnection.cpp

#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <fstream>
#include <string>
#include "sqlConnection.h"

std::string wstring_to_string(const std::wstring& wstr) {
    int len = WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, nullptr, 0, nullptr, nullptr);
    std::string str(len, 0);
    WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, &str[0], len, nullptr, nullptr);
    return str;
}

void queryAndPrint(SQLHDBC hDbc, const SQLWCHAR* query) {
    SQLHSTMT hStmt;
    SQLRETURN retcode;

    // 分配语句句柄
    SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

    // 执行查询
    retcode = SQLExecDirect(hStmt, (SQLWCHAR*)query, SQL_NTS);
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        SQLSMALLINT numCols;
        // 获取列数
        SQLNumResultCols(hStmt, &numCols);

        // 打开文件以写入
        std::ofstream csvFile("output.csv");
        if (!csvFile.is_open()) {
            std::cerr << "Failed to open output.csv\n";
            SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
            return;
        }

        // 打印列名并写入 CSV 文件
        for (SQLSMALLINT i = 1; i <= numCols; i++) {
            SQLWCHAR colName[128];
            SQLSMALLINT colNameLen;
            SQLSMALLINT colType;
            SQLULEN colSize;
            SQLSMALLINT colDecimalDigits;

            SQLDescribeCol(hStmt, i, colName, sizeof(colName) / sizeof(SQLWCHAR), &colNameLen, &colType, &colSize, &colDecimalDigits, NULL);
            std::wstring wColName(colName, colNameLen);
            std::wcout << wColName << L"\t";  // 使用 std::wcout 输出宽字符串

            // 先将 wColName 转换为 std::string
            std::string colNameStr = wstring_to_string(wColName);
            csvFile << colNameStr << (i < numCols ? "," : "\n");
        }

        // 打印结果集并写入 CSV 文件
        while ((retcode = SQLFetch(hStmt)) != SQL_NO_DATA) {
            for (SQLSMALLINT i = 1; i <= numCols; i++) {
                SQLCHAR buffer[256];
                SQLLEN indicator;
                SQLGetData(hStmt, i, SQL_C_CHAR, buffer, sizeof(buffer), &indicator);

                if (indicator == SQL_NULL_DATA) {
                    std::cout << "NULL\t";
                    csvFile << "NULL" << (i < numCols ? "," : "\n");
                }
                else {
                    std::cout << buffer << "\t";
                    csvFile << buffer << (i < numCols ? "," : "\n");
                }
            }
            std::cout << std::endl;
        }

        // 关闭 CSV 文件
        csvFile.close();
    }
    else {
        // 错误处理
        SQLWCHAR sqlState[6];
        SQLINTEGER nativeError;
        SQLWCHAR message[256];
        SQLSMALLINT messageLength;

        SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, 1, sqlState, &nativeError, message, sizeof(message) / sizeof(SQLWCHAR), &messageLength);
        std::cout << "Query failed...\n";
        std::cout << "SQL State: " << sqlState << ", Native Error: " << nativeError << ", Message: " << message << "\n";
    }

    // 释放语句句柄
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}

void sqlConnectionQueryAndPrint(const DatabaseConnectionParams& params) {
    SQLHENV hEnv = NULL;
    SQLHDBC hDbc = NULL;
    SQLRETURN retcode;

    // 分配环境句柄
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
    SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

    // 分配连接句柄
    SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    // 构建连接字符串
    SQLWCHAR connectionString[1024];
    swprintf(
        connectionString, sizeof(connectionString) / sizeof(SQLWCHAR),
        L"DRIVER={%s};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;",
        params.driver.c_str(), 
        params.server.c_str(), 
        params.database.c_str(),
        params.username.c_str(), 
        params.password.c_str());


    //// 定义连接字符串
    //SQLWCHAR connectionString[] = L"DRIVER={ODBC Driver 17 for SQL Server};"
    //    L"SERVER=bc;" // 替换为你的服务器名称
    //    L"DATABASE=TechDataBase;" // 替换为你的数据库名称
    //    L"UID=su;" // 替换为你的用户名
    //    L"PWD=123456;"; // 替换为你的密码

    // 使用 SQLDriverConnect 连接
    retcode = SQLDriverConnect(hDbc, NULL, connectionString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        std::cout << "Connection successful!\n";

        // 查询并打印所有列的数据
        std::wstring query = L"SELECT * FROM " + params.table;

        //queryAndPrint(hDbc, query.c_str()); // 替换为你的表名
    }
    else {
        SQLWCHAR sqlState[6];
        SQLINTEGER nativeError;
        SQLWCHAR message[256];
        SQLSMALLINT messageLength;

        SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, 1, sqlState, &nativeError, message, sizeof(message) / sizeof(SQLWCHAR), &messageLength);
        std::cout << "Connection failed...\n";
        std::cout << "SQL State: " << sqlState << ", Native Error: " << nativeError << ", Message: " << message << "\n";
    }

    // 断开连接
    SQLDisconnect(hDbc);
    // 释放句柄
    SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

}

int main() {
    DatabaseConnectionParams params = {
        L"ODBC Driver 17 for SQL Server",
        L"localhost",
        L"TechDataBase",
        L"su",
        L"123456",
        L"dbo.pass_11"
    };

    sqlConnectionQueryAndPrint(params);
    return 0;
}