adocore.cpp

vc ADO 连接数据库

        }
        catch(...)
        {
            UnknownErrorEx( List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        // Close any open objects
        if( Rs1                != NULL ) LOGQ( Rs1->Close();   )
        if( Conn1              != NULL ) LOGQ( Conn1->Close(); )
    }


    void CodeTemplate( CHorzListBox &List1 )
    {  
       // Using the helper routines, this shows what a typical
       // code fragment using ADO would need in order to provide
       // accurate error handling.  

        ADODB::_ConnectionPtr  Conn1;
   
        // Trap any error/exception
        try
        {
            //----------------------------------
            // (Fail To) Open Connection Object
            //----------------------------------
            
            // Warm & Fuzzy for user
            List1.ResetContent();
            List1.AddString( "ADO Code Template..." );
            List1.AddString( "\t...Deliberately opening conneciton object with no connection information to generate an error" );
   
            // Create Connection Object(1.5 Version)
            Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
            Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

            //----------------------
            // YOUR CODE GOES HERE!
            //----------------------

            // Successful Shutdown
            List1.AddString( "*** (Unexpected) Success! ***" );
        }
        // Catch Blocks
        catch( CException *e )
        {
            MfcErrorEx( e, List1, Conn1 );
        }
        catch( _com_error &e )
        {
            ImportErrorEx( e, List1, Conn1 );
        }
        catch( SEH_Exception &e )
        {
            Win32ErrorEx( e, List1, Conn1 );
        }
        catch(...)
        {
            UnknownErrorEx( List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        // Close any open objects
        if( Conn1              != NULL ) LOGQ( Conn1->Close(); )
    }


    void InputOutputReturnParams( CHorzListBox &List1 )
    {  

        ADODB::_ConnectionPtr  Conn1;
        ADODB::_CommandPtr     Cmd1;
        ADODB::ParametersPtr   *Params1 = NULL;   // Not an instance of a smart pointer.
        ADODB::_ParameterPtr   Param1;
        ADODB::_RecordsetPtr   Rs1;

        CString         strTmp;
        CString         SQLServerName;
            
        _bstr_t         bstrSQLServerConnect;

        // Trap any error/exception
        try
        {
            //-------------------------------------
            // Determine SQL Server to Connect too
            //-------------------------------------

            CInputBox   dlg;

            if( dlg.DoModal() == IDOK )
            {
                if( dlg.m_Path.GetLength() == 0 )
                    return;

                CString strTmp;


                strTmp.Format( "driver={sql server};"
                               "server=%s;"
                               "Database=pubs;UID=sa;PWD=;",
                               dlg.m_Path );

                SQLServerName = dlg.m_Path;

                bstrSQLServerConnect = (LPCTSTR) strTmp;
            }

            //------------------------
            // Open Connection Object
            //------------------------
            // Warm & Fuzzy for user
            List1.ResetContent();
            List1.AddString( "Demonstrating Return, Input and Output parameters..." );
            strTmp.Format( "\t...Assumes SQL Server named %s", SQLServerName );
            List1.AddString( strTmp );
            List1.AddString( "\t...With Error Handling Using Connection Object" );
            List1.AddString( "\t...uses stored procedure sp_AdoTest" );

            // Create Connection Object (1.5 Version)
            Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
            Conn1->ConnectionString = bstrSQLServerConnect;
            Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

           //--------------------------
           // Create Stored Procedures
           //--------------------------

            List1.AddString( "Creating Stored Procedure..." );

            // Drop Procedure
            Conn1->Execute( bstrSQLDrop, &vtEmpty, ADODB::adCmdText);
        
            // Create Procedure
            Conn1->Execute( bstrSQLCreate, &vtEmpty, ADODB::adCmdText);
        
            //-----------------------------------
            // Open Parameterized Command Object
            //-----------------------------------

            List1.AddString( "Opening a ForwardOnly Recordset from a Parameterized Command Object..." );
            List1.AddString( "...creating command object" );

            // Create Command Object
            Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
            Cmd1->ActiveConnection = Conn1;
            Cmd1->CommandText      = _bstr_t( bstrStoredProc );
            Cmd1->CommandType      = ADODB::adCmdStoredProc;

            // Fill Parameters Collection
            List1.AddString( "...refreshing parameters collection off stored procedure" );
            Cmd1->Parameters->Refresh();
            Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (long) 10 );

            // Limit scope of Recordset object (so later we can easily fetch ret/out params)
            List1.AddString( "...opening Recordset" );

            // Open Recordset
            Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdUnknown );

            // Dump the recordset
            List1.AddString( "...Dumping Recordset" );
            while ( Rs1->adoEOF == VARIANT_FALSE )
            {
                CString s;
                strTmp = "\t";

                // Dump Fields Collection
                for( long l = NULL; l < Rs1->Fields->Count; l++ )
                {
                    if( l != Rs1->Fields->Count - 1 )
                        s.Format( "%s | ", CrackStrVariant( Rs1->Fields->Item[ _variant_t( l ) ]->Value ) );
                    else
                        s.Format( "%s",    CrackStrVariant( Rs1->Fields->Item[ _variant_t( l ) ]->Value ) );

                    strTmp += s;
                }

                // Add record
                List1.AddString( strTmp );

                // MoveNext
                Rs1->MoveNext();
            }

            Rs1->Close();
        
            // Display Parameters Collection (with caveat for user)
            List1.AddString( "...It is strictly Driver/Provider dependent whether you have to close" );
            List1.AddString( "the recordset to retrieve output/return parameters.  With the release of" );
            List1.AddString( "of the SQL Server ODBC Driver with ODBC 3.X, you have to close the recordset." );
            List1.AddString( "Previous versions of this driver did not have this requirement, which itself" );
            List1.AddString( "came about as part of a bug fix in previous versions of the driver." );

            // Get first parameter
            strTmp.Format( "\tRetVal Param = %s", CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 0L ) ]->Value ) );
            List1.AddString( strTmp );

            // Get second parameter
            strTmp.Format( "\tInput  Param = %s", CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 1L ) ]->Value ) );
            List1.AddString( strTmp );

            // Get third parameter
            strTmp.Format( "\tOutput Param = %s", CrackStrVariant( (tagVARIANT) Cmd1->Parameters->Item[ _variant_t( 2L ) ]->Value ) );
            List1.AddString( strTmp );

            // Successful Shutdown
            List1.AddString( "*** Success! ***" );
        }
        // Catch Blocks
        catch( CException *e )
        {
            MfcErrorEx( e, List1, Conn1 );
        }
        catch( _com_error &e )
        {
            ImportErrorEx( e, List1, Conn1 );
        }
        catch( SEH_Exception &e )
        {
            Win32ErrorEx( e, List1, Conn1 );
        }
        catch(...)
        {
            UnknownErrorEx( List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        // Close any open objects
        if( Conn1              != NULL ) LOGQ( Conn1->Close(); )
    }