C++ ADO封装实现SQLserver参数化的增删查改代码，C++ ADO封装实现SQLserver参数化的增删查改代码

以下是C++ ADO封装实现SQL Server参数化的增删查改代码示例：

#include <windows.h>
#include <iostream>
#include <comdef.h>
#include <string>
#include <vector>
#include <atlconv.h>
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")

using namespace std;

class CSqlConn
{
public:
    CSqlConn();
    ~CSqlConn();
    bool Open(string strDbName, string strUser, string strPwd, string strServer = "localhost", string strProvider = "SQLOLEDB");
    bool Close();
    bool ExecuteSql(string strSql);
    bool ExecuteSqlParams(string strSql, vector<pair<int, string>> vecParams);
    bool Query(string strSql, _RecordsetPtr& pRecordset);

private:
    _ConnectionPtr m_pConn;
};

CSqlConn::CSqlConn()
{
    CoInitialize(NULL);
}

CSqlConn::~CSqlConn()
{
    Close();
    CoUninitialize();
}

bool CSqlConn::Open(string strDbName, string strUser, string strPwd, string strServer, string strProvider)
{
    try
    {
        HRESULT hr = m_pConn.CreateInstance(__uuidof(Connection));
        if (FAILED(hr)) throw _com_error(hr);

        CStringW strConn;
        strConn.Format(L"Provider=%s;Data Source=%s;Initial Catalog=%s;User ID=%s;Password=%s;",
            CA2W(strProvider.c_str()), CA2W(strServer.c_str()), CA2W(strDbName.c_str()), CA2W(strUser.c_str()), CA2W(strPwd.c_str()));

        hr = m_pConn->Open(_bstr_t(strConn), "", "", adConnectUnspecified);
        if (FAILED(hr)) throw _com_error(hr);
    }
    catch (_com_error e)
    {
        wcout << "Failed to open database: " << e.ErrorMessage() << endl;
        return false;
    }

    return true;
}

bool CSqlConn::Close()
{
    try
    {
        if (m_pConn != NULL)
        {
            m_pConn->Close();
            m_pConn.Release();
        }
    }
    catch (_com_error e)
    {
        wcout << "Failed to close database: " << e.ErrorMessage() << endl;
        return false;
    }

    return true;
}

bool CSqlConn::ExecuteSql(string strSql)
{
    try
    {
        m_pConn->Execute(_bstr_t(strSql.c_str()), NULL, adCmdText);
    }
    catch (_com_error e)
    {
        wcout << "Failed to execute sql: " << e.ErrorMessage() << endl;
        return false;
    }

    return true;
}

bool CSqlConn::ExecuteSqlParams(string strSql, vector<pair<int, string>> vecParams)
{
    try
    {
        _CommandPtr pCmd;
        pCmd.CreateInstance(__uuidof(Command));

        pCmd->CommandText = _bstr_t(strSql.c_str());
        pCmd->CommandType = adCmdText;

        for (auto param : vecParams)
        {
            _ParameterPtr pParam = pCmd->CreateParameter("", param.first, adParamInput, param.second.length(), param.second.c_str());
            pCmd->Parameters->Append(pParam);
        }

        pCmd->ActiveConnection = m_pConn;
        pCmd->Execute(NULL, NULL, adCmdText);
    }
    catch (_com_error e)
    {
        wcout << "Failed to execute sql with params: " << e.ErrorMessage() << endl;
        return false;
    }

    return true;
}

bool CSqlConn::Query(string strSql, _RecordsetPtr& pRecordset)
{
    try
    {
        pRecordset.CreateInstance(__uuidof(Recordset));
        pRecordset->CursorLocation = adUseClient;
        pRecordset->Open(_bstr_t(strSql.c_str()), m_pConn.GetInterfacePtr(), adOpenStatic, adLockReadOnly, adCmdText);
    }
    catch (_com_error e)
    {
        wcout << "Failed to execute query: " << e.ErrorMessage() << endl;
        return false;
    }

    return true;
}

使用示例：

CSqlConn sqlConn;
if (sqlConn.Open("MyDb", "sa", "123456"))
{
    // 增
    sqlConn.ExecuteSql("INSERT INTO MyTable (Name, Age) VALUES ('Tom', 18)");

    // 删
    sqlConn.ExecuteSql("DELETE FROM MyTable WHERE Age > 20");

    // 改
    sqlConn.ExecuteSql("UPDATE MyTable SET Age = 20 WHERE Name = 'Tom'");

    // 查
    _RecordsetPtr pRecordset;
    if (sqlConn.Query("SELECT * FROM MyTable", pRecordset))
    {
        while (!pRecordset->adoEOF)
        {
            wcout << "Name: " << (LPCWSTR)(_bstr_t)pRecordset->GetCollect("Name") << ", ";
            wcout << "Age: " << pRecordset->GetCollect("Age") << endl;

            pRecordset->MoveNext();
        }
    }
}

sqlConn.Close();

其中，ExecuteSqlParams函数用于支持参数化查询，可以避免SQL注入攻击。参数vecParams是一个pair类型的vector，每个pair表示一个参数的类型和值，例如：

vector<pair<int, string>> vecParams;
vecParams.push_back(make_pair(adInteger, "123"));
vecParams.push_back(make_pair(adVarChar, "Tom"));

sqlConn.ExecuteSqlParams("INSERT INTO MyTable (Id, Name) VALUES (?, ?)", vecParams);