adocore.cpp

vc ADO 连接数据库

        {
            MfcErrorEx( 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( Rs1.m_lpDispatch   != NULL ) LOGQ( Rs1.Close();   )
        if( Conn1.m_lpDispatch != NULL ) LOGQ( Conn1.Close(); )

        // Force Release of objects;
        if( Flds1.m_lpDispatch   != NULL ) LOGQ( Flds1.ReleaseDispatch();   )
        if( Rs1.m_lpDispatch     != NULL ) LOGQ( Rs1.ReleaseDispatch();     )
        if( Param1.m_lpDispatch  != NULL ) LOGQ( Param1.ReleaseDispatch();  )
        if( Params1.m_lpDispatch != NULL ) LOGQ( Params1.ReleaseDispatch(); )
        if( Cmd1.m_lpDispatch    != NULL ) LOGQ( Cmd1.ReleaseDispatch();    )
        if( Conn1.m_lpDispatch   != NULL ) LOGQ( Conn1.ReleaseDispatch();   )
    }


    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.  

        _Connection  Conn1;
        COleException   e;

        // 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.CreateDispatch( "ADODB.Connection.1.5", &e );
            Conn1.Open( strEmpty, strEmpty, strEmpty, -1 );

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

            // Successful Shutdown
            List1.AddString( "*** (Unexpected) Success! ***" );
        }
        // Catch Blocks
        catch( CException *e )
        {
            MfcErrorEx( 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.m_lpDispatch != NULL ) LOGQ( Conn1.Close(); )

        // Force Release of objects;
        if( Conn1.m_lpDispatch   != NULL ) LOGQ( Conn1.ReleaseDispatch();   )
    }


    void InputOutputReturnParams( CHorzListBox &List1 )
    {  
        _Connection     Conn1;
        _Command        Cmd1;
        Parameters      Params1;  
        _Parameter      Param1;
        Fields          Flds1;
        Field           Fld1;

        CString         strTmp;
        CString         SQLServerName;

        CString         strSQLServerConnect;

        COleException   e;

        // 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;

                strSQLServerConnect  = (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.CreateDispatch( "ADODB.Connection.1.5", &e );
            Conn1.SetConnectionString( strSQLServerConnect );
            Conn1.Open( strEmpty, strEmpty, strEmpty, -1 );

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

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

            // Drop Procedure
            Conn1.Execute( strSQLDrop, &vtEmpty, adCmdText);

            // Create Procedure
            Conn1.Execute( strSQLCreate, &vtEmpty, adCmdText);

            //-----------------------------------
            // Open Parameterized Command Object
            //-----------------------------------

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

            // Create Command Object (1.5 Version )
            Cmd1.CreateDispatch ( "ADODB.Command.1.5", &e );
            Cmd1.SetRefActiveConnection( (LPDISPATCH) Conn1 );
            Cmd1.SetCommandText( strStoredProc );
            Cmd1.SetCommandType( (long) adCmdStoredProc );

            // Fill Parameters Collection
            List1.AddString( "...refreshing parameters collection off stored procedure" );
            Params1 = Cmd1.GetParameters();
            Params1.Refresh();
            Param1 = Params1.GetItem( COleVariant( (long) 1 ) );
            Param1.SetValue( COleVariant( (long) 10 ) );

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

                // Open Recordset
                Rs1 = Cmd1.Execute( &vtEmpty, &vtEmpty2, adCmdStoredProc );
 
                // Dump the recordset
                List1.AddString( "...Dumping Recordset" );
                Flds1 = Rs1.GetFields();
                while ( Rs1.GetEof() == FALSE )
                {
                    CString s;
                    strTmp = "\t";

                    // Refresh Fields Collection
                    Flds1.Refresh();

                    for( long l = 0; l < Flds1.GetCount(); l++ )
                    {
                        Fld1 = Flds1.GetItem( COleVariant((long) l ));

                        if( l != Flds1.GetCount() - 1 )
                            s.Format( "%s | ", CrackStrVariant( Fld1.GetValue() ) );
                        else
                            s.Format( "%s",    CrackStrVariant( Fld1.GetValue() ) );

                        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." );

            Params1 = Cmd1.GetParameters();

            // Get first parameter
            Param1 = Params1.GetItem( COleVariant( (long) 0 ) );
            strTmp.Format( "\tRetVal Param = %s", CrackStrVariant( Param1.GetValue() ) );
            List1.AddString( strTmp );

            // Get second parameter
            Param1 = Params1.GetItem( COleVariant( (long) 1 ) );
            strTmp.Format( "\tInput  Param = %s", CrackStrVariant( Param1.GetValue() ) );
            List1.AddString( strTmp );

            // Get third parameter
            Param1 = Params1.GetItem( COleVariant( (long) 2 ) );
            strTmp.Format( "\tOutput Param = %s", CrackStrVariant( Param1.GetValue() ) );
            List1.AddString( strTmp );

            // Successful Shutdown
            List1.AddString( "*** Success! ***" );
        }
        // Catch Blocks
        catch( CException *e )
        {
            MfcErrorEx( 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.m_lpDispatch != NULL ) LOGQ( Conn1.Close(); )

        // Force Release of objects;
        if( Flds1.m_lpDispatch   != NULL ) LOGQ( Flds1.ReleaseDispatch();   )
        if( Param1.m_lpDispatch  != NULL ) LOGQ( Param1.ReleaseDispatch();  )
        if( Params1.m_lpDispatch != NULL ) LOGQ( Params1.ReleaseDispatch(); )
        if( Cmd1.m_lpDispatch    != NULL ) LOGQ( Cmd1.ReleaseDispatch();    )
        if( Conn1.m_lpDispatch   != NULL ) LOGQ( Conn1.ReleaseDispatch();   )
    }