数据库(sql)操作

来源：互联网发布：游戏编程招聘编辑：程序博客网时间：2024/06/05 12:48

//创建一个连接实例 // _ConnectionPtr m_db;
::CoInitialize(NULL); //使用COM库函数(如ADO)都必须加这句,用完后加UnCoInitialize
ServerName=ReadINI("ServerName"); //读取INI,自定义
DataBaseName=ReadINI("DataBaseName");
UserName=ReadINI("UserName");
PWD=ReadINI("PWD");
try
{
  m_db.CreateInstance(__uuidof(Connection));
  CString strConn="driver={SQL Server};Server="+ServerName+";DATABASE="+DataBaseName+";UID="+UserName+";Pwd="+PWD;
  _bstr_t bstrConn(strConn);
  m_db->Open(bstrConn,"","",0);
}
catch(...)
{
        AfxMessageBox("连接数据库失败,请检查配置");
}

//用连接实例m_db执行update或delete语句
if(IDYES==MessageBox("的确要删除吗?",NULL,MB_YESNO))
{
  UpdateData();
  try
  {
   CString strSql="DELETE FROM tblTest where N_ID=" + strID;
   _bstr_t bstrQuery(strSql);
   _variant_t vRecsAffected(0L);
   m_db->Execute(bstrQuery, &vRecsAffected, adOptionUnspecified);
  }
  catch (...)
  {
   MessageBox("删除失败!");
  }
}

//创建一个记录集实例并执行select语句
  try
  {
   _variant_t var;
   CString strID("");
   _RecordsetPtr rs;
   rs.CreateInstance(__uuidof(Recordset));
   rs->Open("select * from tblTest", m_db.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText); //m_db为连接实例,之前已创建
   while (!rs->adoEOF)
   {
    var=rs->GetCollect("id");
    if(var.vt!=VT_NULL)
     strID=(LPCSTR)_bstr_t(var);
    strID.TrimLeft();
    strID.TrimRight();
    ....
    rs->MoveNext();
   }
  }
  catch (...)
  {
   ....
  }

//判断连接实例m_db的状态
if (m_db->GetState() == (adStateOpen||adStateConnecting||adStateExecuting||adStateFetching))
{
m_db->Close();
m_db = NULL;
....
}
else if (m_db->GetState() == adStateClosed)
{
m_db = NULL;
....
}

//执行存储过程
if (db->m_IsConnection) //连接数据库状态下
{

  try
  {
   _CommandPtr cmd; //命令实例
   cmd.CreateInstance(__uuidof(Command));
   cmd->ActiveConnection=db->m_pConnection; //事先创建的连接实例
   cmd->CommandType=adCmdStoredProc; //存储过程类型
   cmd->CommandText=_bstr_t("ProTest"); //存储过程名

   _variant_t vsim,vname,vaorv,vuptime;

   vsim=_variant_t(_bstr_t(ic_info.strSim)); //ic_info为已定义结构体
   vname=_variant_t(_bstr_t(ic_info.strName));
   vaorv=_variant_t(_bstr_t(ic_info.strAorV));
   vuptime=_variant_t(_bstr_t(ic_info.strTime));

   _ParameterPtr mp_sim,mp_name,mp_aorv,mp_uptime; //参数实例

   mp_sim.CreateInstance(__uuidof(Parameter));
   mp_name.CreateInstance(__uuidof(Parameter));
   mp_aorv.CreateInstance(__uuidof(Parameter));
   mp_uptime.CreateInstance(__uuidof(Parameter));

   mp_sim=cmd->CreateParameter
    (
    _bstr_t("var1"), //变量1
    adChar,   //类型,与存储过程的参数对应
    adParamInput, //输入参数,与存储过程参数对应
    12, //参数长度,与存储过程参数对应
    vsim
    );
   cmd->Parameters->Append(mp_sim);

   mp_name=cmd->CreateParameter
    (
    _bstr_t("var2"),
    adVarChar,
    adParamInput,
    20,
    vname
    );
   cmd->Parameters->Append(mp_name);

   mp_aorv=cmd->CreateParameter
    (
    _bstr_t("var3"),
    adVarChar,
    adParamInput,
    3,
    vaorv
    );
   cmd->Parameters->Append(mp_aorv);

   mp_uptime=cmd->CreateParameter
    (_bstr_t("var4"),
    adVarChar,
    adParamInput,
    20,
    vuptime
    );
   cmd->Parameters->Append(mp_uptime);

   _variant_t vNull;
   vNull.vt=VT_ERROR;
   vNull.scode=DISP_E_PARAMNOTFOUND;
   cmd->Execute(&vNull,&vNull,adCmdStoredProc); //执行
   }
   catch(...)
   {
   TRACE("ERROR/n");
    return FALSE;
   }
}