Using stored procedures with ADO

ActiveX Data Objects (ADO) enables you to write a client application to access and manipulate data in a database server through a provider.

ADO’s primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint.

This sample project is for ADODB, an implementation of ADO optimized for use with Microsoft OLE DB providers, including the Microsoft ODBC provider for OLE DB.

Using this we can execute stored procedure, pass arguments and retrieve value. To use this sample you will have to create the two stored procedures mentioned below.

For using this project you need MFC 5.0 OR above + ADO in your machine.

{
   CString strTmp;
   CString m_sdatasource; // Data source name
   CString m_sUserID;     // User Id
   CString m_sPassword;   // Password
   // GET the above values from the user
   //Without creating Datasource we can use database by the following   code
   /* strTmp.Format( “driver={sql server};”
                           “server=%s;”
                           “Database=%s;””UID=%s;””PWD=%s;”,
                           m_server,m_sdatabase,m_sUserID,m_sPassword );*/
   strTmp.Format( “dsn=%s;””UID=%s;””PWD=%s;”,m_sdatasource,m_sUserID,m_sPassword );
   _bstr_t         bstrSQLServerConnect;
   _bstr_t bstrProc =( L”sp_StartByteImport” );; //Stored procedure name
   _variant_t Final;
   bstrSQLServerConnect = (LPCTSTR) strTmp;
   m_status=”Empty File”;
   _ConnectionPtr  Conn1; // connection object pointer
   _CommandPtr     Cmd1;  // command object pointer
   _RecordsetPtr   Rs1; // recordset object pointer
   bool            bvalid = false;
   try
   {
      Conn1.CreateInstance( __uuidof( Connection ) ); // Instantiating connection object
	  Conn1->ConnectionString = bstrSQLServerConnect; // giving the sqlconnection
	  Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty ); // open the connection object
	  Cmd1.CreateInstance( __uuidof( Command ) ); // creating command object
	  Cmd1->ActiveConnection = Conn1;             // giving the connection handle
	  Cmd1->CommandText      = _bstr_t( bstrProc ); // passing the stored procedue
	  Cmd1->CommandType      = adCmdStoredProc;     // type
	  Cmd1->Parameters->Refresh();                 // passing string value as argument to stored procedure
	  Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (LPCTSTR)m_sfilename );
	  Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown ); // executing the stored procedure and storing the recordset value
	  bvalid = true;
	  Final  = Rs1->Fields->GetItem( _variant_t( 0L ) )->Value; // getting the first column value of the result row
	  strTmp.Format( “%s”, CrackStrVariant( Final) ); // to see the value
	  // put your code to see all column values
   }
   catch( CException *e ) // trapping all error messages
   {
	  TCHAR    szCause[255];
      e->GetErrorMessage(szCause, 255);
	  m_status=szCause;
   }
   catch( _com_error &e )
   {
	m_status=e.ErrorMessage( );
   }
   catch(…)
   {
	m_status=”Error while executing the Import”;
   }
    //we need to create the stored procedures below before running the application
	//CREATE PROCEDURE sp_AddAccountingInfo @nfinal int, @pcDate datetime,
	//@pcURL varchar (250), @pcTop varchar (250),
	//@pcQueryString varchar (250), @pcBytes int, @pcRequests int AS
       /*
		Do your operation here
	*/
	//CREATE PROCEDURE sp_AddAccountingInfo
	//@nfinal int,
	//@pcDate datetime,
	//@pcURL varchar (250),
	//@pcTop varchar (250),
	//@pcQueryString varchar (250),
	//@pcBytes int,
	//@pcRequests int
	//AS
	/*
		Put your code here
	*/
}

Download demo project – [125 KB] KB