What's the simplest way to execute a query in Visual C++

I'm using Visual C++ 2005 and would like to know the simplest way to connect to a MS SQL Server and execute a query.

I'm looking for something as simple as ADO.NET's SqlCommand class with it's ExecuteNonQuery(), ExecuteScalar() and ExecuteReader().

Sigh offered an answer using CDatabase and ODBC.

Can anybody demonstrate how it would be done using ATL consumer templates for OleDb?

Also what about returning a scalar value from the query?

From stackoverflow jmatthias

• Try the Microsoft Enterprise Library. A version should be available here for C++. The SQlHelper class impliments the methods you are looking for from the old ADO days. If you can get your hands on version 2 you can even use the same syntax.

  From Rob Allen

• With MFC use CDatabase and ExecuteSQL if going via a ODBC connection.

  CDatabase db(ODBCConnectionString);
  db.Open();
  db.ExecuteSQL(blah);
  db.Close();
  

  From Sigh

• I used this recently:

  #include <ole2.h>
  #import "msado15.dll" no_namespace rename("EOF", "EndOfFile")
  #include <oledb.h> 
  void CMyDlg::OnBnClickedButton1()
  {
      if ( FAILED(::CoInitialize(NULL)) )
       return;
      _RecordsetPtr pRs = NULL;
  
      //use your connection string here
      _bstr_t strCnn(_T("Provider=SQLNCLI;Server=.\\SQLExpress;AttachDBFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\db\\db.mdf;Database=mydb;Trusted_Connection=Yes;MARS Connection=true"));
      _bstr_t a_Select(_T("select * from Table"));
  
  
      try {
  
              pRs.CreateInstance(__uuidof(Recordset));
              pRs->Open(a_Select.AllocSysString(), strCnn.AllocSysString(), adOpenStatic, adLockReadOnly, adCmdText);
  
              //obtain entire restult as comma separated text:
              CString text((LPCWSTR)pRs->GetString(adClipString, -1, _T(","), _T(""), _T("NULL")));
  
              //iterate thru recordset:
              long count = pRs->GetRecordCount();
  
              COleVariant var;
              CString strColumn1;
              CString column1(_T("column1_name")); 
  
              for(int i = 1; i <= count; i++)
              {
                  var = pRs->GetFields()->GetItem(column1.AllocSysString())->GetValue();
                  strColumn1 = (LPCTSTR)_bstr_t(var);
              }
      }
      catch(_com_error& e) {
              CString err((LPCTSTR)(e.Description()));
              MessageBox(err, _T("error"), MB_OK);
              _asm nop; //
      }
     // Clean up objects before exit.
     if (pRs)
         if (pRs->State == adStateOpen)
                pRs->Close();
  
  
  
       ::CoUninitialize();
  }
  

  From rec

• You should be able to use OTL for this. It's pretty much:

  #define OTL_ODBC_MSSQL_2008 // Compile OTL 4/ODBC, MS SQL 2008
  //#define OTL_ODBC // Compile OTL 4/ODBC. Uncomment this when used with MS SQL 7.0/ 2000
  #include <otlv4.h> // include the OTL 4.0 header file
  #include <stdio>
  
  int main()
  {
    otl_connect db; // connect object
    otl_connect::otl_initialize(); // initialize ODBC environment
    try
    {
      int myint;
  
      db.rlogon("scott/tiger@mssql2008"); // connect to the database
      otl_stream select(10, "select someint from test_tab", db);
  
      while (!select.eof())
      {
        select >> myint;
        std::cout<<"myint = " << myint << std::endl;
      }
    }
    catch(otl_exception& p)
    {
      std::cerr << p.code << std::endl;     // print out error code
      std::cerr << p.sqlstate << std::endl; // print out error SQLSTATE
      std::cerr << p.msg << std::endl;      // print out error message
      std::cerr << p.stm_text << std::endl; // print out SQL that caused the error
      std::cerr << p.var_info << std::endl; // print out the variable that caused the error
    }
  
    db.logoff(); // disconnect from the database
  
    return 0;
  }
  

  The nice thing about OTL, IMO, is that it's very fast, portable (I've used it on numerous platforms), and connects to a great many different databases.

  From Zathrus