ADO编程-Command对象

ADO编程-Command对象

标签（空格分隔）： 数据库

---

Command对象定义了将对数据源执行的指定命令。该对象的常用属性和方法如下：

ActiveConnection属性：该属性指定Command对象所属的Connection对象。

CommandText属性：该属性包含发送给数据提供者的命令文本，根据DBMS的不同，数据提供者能够执行的命令格式也有所不同。该属性的值为包含数据提供者命令(可以是SQL，表的名字或存储过程名）的字符串。其中，SQL语句必须是提供者查询处理程序支持的特定语法或版本(例如，使用SQL Server时，该属性的字符串必须符合SQL Server的T-SQL语言语法）

CommandType属性：指示Command对象的属性。

CommandTypeEnum
{
    adCmdUnspecified = -1,
    adCmdUnknown = 8,
    adCmdText = 1,          //SQL 语句
    adCmdTable = 2,         //表名
    adCmdStoredProc = 4,    //存储过程    
    adCmdFile = 256,        //文件
    adCmdTableDirect = 512
};

CommandTimeout属性： 命令超时值
Parameters属性： 命令参数（存储过程参数必须通过此参数传递）

Execute方法：执行CommandText属性中指定的查询，sql语句或存储过程。

 _RecordsetPtr Execute (
        VARIANT * RecordsAffected,  //影响的函数
        VARIANT * Parameters,       //参数
        long Options );             //与CommandType相同

CreateParameter方法：创建参数

 _ParameterPtr CreateParameter (
        _bstr_t Name,               //参数名
        enum DataTypeEnum Type,     //参数类型
        enum ParameterDirectionEnum Direction,  //参数方向，输入或者输出
        long Size,                              //参数字节数，使用sizeof（计算）
        const _variant_t & Value = vtMissing ); //参数值

DataTypeEnum定义如下其具体类型与数据库或者C++里面类型对应，可根据位数将数据库中数据类型与c++中对应起来，使用，不在详细讲述

DataTypeEnum
{
    adEmpty = 0,
    adTinyInt = 16, 
    adSmallInt = 2, 
    adInteger = 3,
    adBigInt = 20,
    adUnsignedTinyInt = 17,
    adUnsignedSmallInt = 18,
    adUnsignedInt = 19,
    adUnsignedBigInt = 21,
    adSingle = 4,
    adDouble = 5,
    adCurrency = 6,
    adDecimal = 14,
    adNumeric = 131,
    adBoolean = 11,
    adError = 10,
    adUserDefined = 132,
    adVariant = 12,
    adIDispatch = 9,
    adIUnknown = 13,
    adGUID = 72,
    adDate = 7,
    adDBDate = 133,
    adDBTime = 134,
    adDBTimeStamp = 135,
    adBSTR = 8,
    adChar = 129,
    adVarChar = 200,
    adLongVarChar = 201,
    adWChar = 130,
    adVarWChar = 202,
    adLongVarWChar = 203,
    adBinary = 128,
    adVarBinary = 204,
    adLongVarBinary = 205,
    adChapter = 136,
    adFileTime = 64,
    adPropVariant = 138,
    adVarNumeric = 139,
    adArray = 8192
};

ParameterDirectionEnum 定义如下

ParameterDirectionEnum
{
    adParamUnknown = 0,
    adParamInput = 1,   //输入参数
    adParamOutput = 2,  //输出参数
    adParamInputOutput = 3, //输入输出参数
    adParamReturnValue = 4  //返回值
};

用例

// DataBaseEngine.cpp : 定义控制台应用程序的入口点。
//

#include "stdafx.h"
#include "DataBase.h"

#import "C:\Program Files\Common Files\system\ado\msado15.dll" \
no_namespace rename("EOF", "adoEOF") rename("BOF", "adoBOF")

int main()
{
    ::CoInitialize(NULL);
    _ConnectionPtr  m_pConnection;
    _RecordsetPtr   m_pRecordSet;
    _CommandPtr     m_pCommand;
    try
    {
        m_pConnection.CreateInstance(__uuidof(Connection));
        m_pRecordSet.CreateInstance(__uuidof(Recordset));
        m_pCommand.CreateInstance(__uuidof(Command));
        assert(NULL != m_pConnection);
        assert(NULL != m_pRecordSet);
        if (NULL == m_pRecordSet
            || NULL == m_pConnection
            || NULL == m_pCommand)
        {
            cout << "创建实例失败" << endl;
            return -1;
        }

        m_pConnection->CommandTimeout = 5;
        m_pConnection->ConnectionString = "Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=sample_db;Data Source=127.0.0.1";
        m_pConnection->Open("","", "", adConnectUnspecified);
        assert(m_pConnection->GetState() == adStateOpen);
        if (m_pConnection->GetState() != adStateOpen)
        {
            cout << "连接失败" << endl;
        }


        执行SQL语句
        m_pCommand->ActiveConnection = m_pConnection;
        //m_pCommand->CommandText = "Select * from AccountsInfo";
        //m_pCommand->CommandTimeout = 1;
        //m_pCommand->ActiveConnection = m_pConnection;
        //m_pRecordSet = m_pCommand->Execute(NULL, NULL, adCmdText);


        //cout << "\n执行SQL语句过程查询结果集" << endl;
        //while (!m_pRecordSet->GetadoEOF())
        //{
        //  szNickName = (_bstr_t)m_pRecordSet->GetCollect("NickName");
        //  nSex = (int)m_pRecordSet->GetCollect("Sex");
        //  cout << szNickName << "   " << nSex << endl;
        //  m_pRecordSet->MoveNext();
        //}

        if (m_pRecordSet->GetState() != adStateClosed)
        {
            m_pRecordSet->Close();
        }
        string szNickName;
        int nSex = 0;
        //执行存储过程
        m_pCommand->CommandText = "QueryFromName";
        string szName = "东东";
        string szOutput(128,'\0');
        m_pCommand->Parameters->Append(m_pCommand->CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, sizeof(long), _variant_t((long)0)));
        m_pCommand->Parameters->Append(m_pCommand->CreateParameter("@strUserName", adChar, adParamInput, szName.length(), _variant_t((_bstr_t)szName.c_str())));
        m_pCommand->Parameters->Append(m_pCommand->CreateParameter("@strErrorDescribe", adChar, adParamOutput, szOutput.length(), _variant_t((_bstr_t)szOutput.c_str())));
        m_pRecordSet = m_pCommand->Execute(NULL, NULL, adCmdStoredProc);

        //打印结果
        cout << "\n执行存储过程查询结果集" << endl;
        while (!m_pRecordSet->GetadoEOF())
        {
            szNickName = (_bstr_t)m_pRecordSet->GetCollect("NickName");
            nSex = (int)m_pRecordSet->GetCollect("Sex");
            cout << szNickName << "   " << nSex << endl;
            m_pRecordSet->MoveNext();

        }

        cout << "\n返回结果" << endl;
        //获取返回值
        _ParameterPtr Parameter;
        int nRes = 0;
        long lParameterCount = m_pCommand->Parameters->Count;
        cout << "lParameterCount: " << lParameterCount << endl;
        for (long i = 0; i < lParameterCount; i++)
        {
            Parameter = m_pCommand->Parameters->Item[i];
            cout << Parameter->Name << endl;
            if (Parameter->Direction == adParamReturnValue) {
                nRes = (long)Parameter->Value.lVal;
                break;
            }
        }

        //获取输出参数
        szOutput = (char*)(_bstr_t)m_pCommand->Parameters->Item["@strErrorDescribe"]->Value;

        cout << "nDesc : " << szOutput << endl;
        cout << "nRes : " << nRes << endl;
    }
    catch (_com_error& e)
    {
        cout << e.Description() << endl;
    }

//  m_pRecordSet->Close();
    m_pConnection->Close();
    if(NULL != m_pRecordSet)
    {
        m_pRecordSet.Release();
        m_pConnection.Release();
    }

    ::CoUninitialize();
    return 0;
}