编程访问带有varbinrany参数的存储过程（C#与ado版）

存储过程：

CREATE  PROCEDURE Zzd

@A1 varbinary(8000)
AS

GO

 

C#:
   SqlConnection cn=null;
   SqlCommand    cmd=null;
   try
   {
    cn=new SqlConnection();
    cn.ConnectionString="data source=192.168.0.199;initial catalog=haha;user id=sa;password=sa";
    cn.Open();
    cmd=cn.CreateCommand();
    cmd.CommandType=CommandType.Text;
    cmd.CommandText="exec zzd @A1";

    SqlParameter param= new SqlParameter();
    param.ParameterName="@A1";
    param.DbType=DbType.Binary;
    param.Direction=ParameterDirection.Input;
    byte [] a=new byte[200];
    SqlBinary bin=new SqlBinary(a);
    param.Value=bin;
    cmd.Parameters.Add(param);

    cmd.ExecuteNonQuery();
   }
   catch(Exception e1)
   {
    string a11=e1.Message;
   }

   if(cmd!=null)
    cmd=null;
   if(cn!=null)
   {
    cn.Close();
    cn=null;
   }

Ado:

 CoInitialize(NULL);
 {
  _ConnectionPtr cn;
  _CommandPtr    cmd;
  _ParameterPtr  para;
  SAFEARRAY *psa=NULL;
  para.CreateInstance(__uuidof(Parameter));
   try
   {
    cn.CreateInstance(__uuidof(Connection));
    cmd.CreateInstance(__uuidof(Command));
    para.CreateInstance(__uuidof(Parameter));

    cn->ConnectionString="Provider=SQLOLEDB.1;User ID=sa;password=sa;Initial Catalog=haha;Data Source=192.168.0.199";
    cn->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
    cmd->ActiveConnection=cn;
    cmd->CommandType=adCmdText;
    cmd->CommandText="exec zzd ?";

    //para->Name=_bstr_t("@A1");
    para->Type=adVarBinary;
    para->Size=8000;
    para->Direction=adParamInput;

    SAFEARRAY *psa;
    SAFEARRAYBOUND rgsabound[1];
    rgsabound[0].lLbound = 0;
    rgsabound[0].cElements = 100;
    psa = SafeArrayCreate(VT_UI1, 1, rgsabound); ///创建SAFEARRAY对象
    char  pChar='C';
            
    //复制数据
    for (long i = 0; i < (long)100; i++)
     SafeArrayPutElement (psa, &i, &pChar);

    VARIANT varBLOB;
    varBLOB.vt = VT_ARRAY | VT_UI1;//varBLOB的类型设置为BYTE类型的数组
    varBLOB.parray = psa;
    para->Value=varBLOB;
    cmd->Parameters->Append(para);
    cmd->Execute(NULL, NULL, adCmdText);
   }
   catch (_com_error &e)
   {
    _bstr_t bstrError = e.Description();
    CString m_strError = (LPCTSTR)bstrError;

   }
   if(psa!=NULL)
   {
    SafeArrayDestroy(psa);
   }
   if(cmd!=NULL)
   {
    cmd=NULL;
   }
   if(cn!=NULL)
   {
    cn->Close();
    cn=NULL;
   }
 }
 CoUninitialize();