Ado 连接数据库 Sql Server2000 及基本数据库操作

ADOConn.h

 

// ADOConn.h: interface for the CADOConn class.
//
/**///////////////////////////////////////////////////////////////////////

#if !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)
#define AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_

#include <ole2.h>
//#include <stdio.h>
//#include <conio.h>


#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#include "sqlext.h"
#import "c:Program FilesCommon FilesSystemadomsado15.dll"no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")

class CADOConn 
...{
public:
    CADOConn();
    virtual ~CADOConn();
public:
    //ADODB::_ConnectionPtr   m_pConnect   
    //_ConnectionPtr m_pConnection;
    //_RecordsetPtr  m_pRecordset;
    
public:
    int FindRecordSet(CString m_sql,CString strField,CString str);
    int DeleteRecordSet(CString m_sql);
    //初始化——连接数据库
    static void OnInitDBConnect();
    //执行查询
    static _RecordsetPtr& GetRecordSet(_bstr_t bstrSQL);
    //Insetr Update _variant_t
    static BOOL ExecuteSQL(_bstr_t bstrSQL);
    void ExitConnect();
    //获得表长
    int GetRecordCount(CString strTable, int &nCount, CString strCondition = _T(""));
    //sql语句,待查属性列,查询结果
    static int SelectRecordSet(CString m_sql,CString strField,CString &strResult);
    //修改属性值
    int UpdateRecordSet(CString m_sql, CString strField, CString str);
    int InsertRecord(CString strTable, int nCount, CString *strFields,    CString *strValue, 
        CString strkeyfield = _T(""), CString strkey = _T(""));
    
};

#endif // !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)

 

ADOConn.cpp

 

// ADOConn.cpp: implementation of the CADOConn class.
//
/**///////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "MyClient.h"
#include "ADOConn.h"

//#include "ExamTest.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

_ConnectionPtr m_pConnection;
_RecordsetPtr  m_pRecordset;
extern CMyClientApp theApp;
/**/////////////////////////////////////////////////////////////////////
// Construction/Destruction
/**///////////////////////////////////////////////////////////////////////


CADOConn::CADOConn()
...{

}

CADOConn::~CADOConn()
...{

}

void CADOConn::OnInitDBConnect()
...{    
    CString strUser;
    CString strPwd;
    CString strservername;
    CString strdbname;
    CString strConnection;
    
    strUser = theApp.m_strDBUserApp;
    strPwd = theApp.m_strDBPwdApp;
    strConnection = "Provider=SQLOLEDB.1;Server=" + theApp.m_strServerApp+ ";Database=TroubleCheck";
    
    ::CoInitialize(NULL);

    try
    ...{

        m_pConnection.CreateInstance(__uuidof(Connection));//******************
        _bstr_t strConnect = (_bstr_t)strConnection;
    
        m_pConnection->Open(strConnect,(_bstr_t)strUser,(_bstr_t)strPwd,adModeUnknown);//****************
        theApp.isDBOK = TRUE;
    }

    catch(_com_error e)
    ...{
        //AfxMessageBox(e.Description()+"系统退出!");
        //exit(0);//***********************************
        AfxMessageBox("数据库连接失败，请重新配置连接数据库!");
        ::SendMessage(theApp.hwndmainDlg,WM_SETDBSERVER,0,0);
    }
}

_RecordsetPtr& CADOConn::GetRecordSet(_bstr_t bstrSQL)
...{
    try
    ...{
        
        if (m_pConnection == NULL)
        ...{
            OnInitDBConnect();
        }
        
        m_pRecordset.CreateInstance(_uuidof(Recordset));
        m_pRecordset->Open(bstrSQL,(_variant_t)m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
    }
    catch (_com_error e)
    ...{
        AfxMessageBox(e.Description()+"系统退出！");
        exit(0);
        //AfxMessageBox("2");
    }
    return m_pRecordset;
//     _variant_t var;
}

BOOL CADOConn::ExecuteSQL(_bstr_t bstrSQL)//************************
...{
    try
    ...{
        if (m_pConnection == NULL)
        ...{
            //AfxMessageBox("自动连接!");
            OnInitDBConnect();
        }
        m_pConnection->Execute(bstrSQL ,NULL,adCmdText);
    }
    catch (_com_error e)
    ...{
        AfxMessageBox(e.Description()+"系统退出！");
        exit(0);
    }
    return true;
}

void CADOConn::ExitConnect()
...{    
    if (m_pRecordset != NULL)
    ...{    
        m_pRecordset->Close();
        m_pConnection->Close();
        ::CoUninitialize();
    }
}


int CADOConn::SelectRecordSet(CString m_sql,CString strField,CString &strResult)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;

    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord != NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{
                varTemp = m_pRecord->GetCollect((_bstr_t)strField);
                if (varTemp.vt == VT_NULL)
                ...{
                    strResult = _T("");
                }
                strResult = (LPCSTR)_bstr_t(varTemp);
                //strResult.TrimRight();
                m_pRecord->MoveNext();        
            }
        }
        else
            return 0;
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败，请检查数据库!");
        return 0;
    }
    return 1;
}


int CADOConn::UpdateRecordSet(CString m_sql, CString strField, CString str)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;
    
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{    
                m_pRecord->PutCollect((_bstr_t)strField,_variant_t(str));
                m_pRecord->Update();
                m_pRecord->MoveNext();        
            }
        }
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败，请检查数据库!");
        return 0;
    }
    return 1;
}

int CADOConn::FindRecordSet(CString m_sql, CString strField, CString str)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;
    CString strTemp;
    
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{    
                varTemp = m_pRecord->GetCollect((_bstr_t)strField);
                if (varTemp.vt == VT_NULL)
                ...{
                    strTemp = _T("");
                }
                strTemp = (LPCSTR)_bstr_t(varTemp);
                strTemp.TrimRight();
                if (!strcmp(strTemp,str))
                    return 1;
                m_pRecord->MoveNext();        
            }
        }
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败，请检查数据库!");
        return 0;
    }
    return 1;
}

int CADOConn::DeleteRecordSet(CString m_sql)
...{
    if(!ExecuteSQL(_bstr_t(m_sql)))
    ...{
        return 0;
    }
    else
    ...{
        AfxMessageBox("删除成功");
        return 1;
    }
}

int CADOConn::GetRecordCount(CString strTable, int &nCount, CString strCondition)
...{
    _RecordsetPtr m_pRecord = NULL;
    int nCounter = 0;
    CString m_sql;
    m_sql = "select * from " + strTable + strCondition;
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{
                m_pRecord->MoveNext();
                nCounter++;
            }
        }
        nCount = nCounter;
    }
    catch (...) 
    ...{
        AfxMessageBox("数据库错误");
        return 0;
    }
    return 1;
}

//return -1:主键冲突/////////////////注意m_Storedata=VT_NULL的情况，要做处理
//strTable:表名
//nCount:属性列数
//strFields:属性列名
//strValue:属性列值
//strKeyfield:主键名
//strKey:主键属性值
int CADOConn::InsertRecord(CString strTable, int nCount, CString *strFields,
                            CString *strValue,CString strKeyfield, CString strKey)
...{

    CString m_sql;
    _RecordsetPtr m_pRecord;
    _variant_t m_Storedata;
    try
    ...{
        if (strKeyfield != "")
        ...{
            m_sql = "select * from " + strTable;
            m_pRecord = GetRecordSet((_bstr_t)m_sql);
            if (m_pRecord!=NULL)
            ...{
                if (!m_pRecord->adoBOF)
                ...{
                    m_pRecord->MoveFirst();
                }
                
                while (!m_pRecord->adoEOF)
                ...{
                    m_Storedata = m_pRecord->GetCollect((_bstr_t)strKeyfield);
                    if ((LPCSTR)_bstr_t(m_Storedata)==strKey)
                    ...{
                        return 0;
                    }
                    m_pRecord->MoveNext();
                }
            }    
        }
        
        m_sql = "insert into " + strTable + " (";
        for (int i=0; i<nCount-1; i++)
        ...{
            m_sql += strFields[i] + ",";
        }
        m_sql += strFields[nCount-1] + ") ";
        m_sql += "values (";
        for (i=0; i<nCount-1; i++)
        ...{
            m_sql += strValue[i] + ",";
        }
        m_sql += strValue[nCount-1] + ") ";
        
        ExecuteSQL((_bstr_t)m_sql);
    }
    catch (...) 
    ...{
        AfxMessageBox("数据库错误");
        return 0;
    }
    return 1;
}