CeDB AdoCE 예제

// DBComm.cpp : implementation file
//

#include "stdafx.h"
#include "DBTest.h"
#include "DBComm.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

#define TYPE_STR 0
#define TYPE_INT 1

/////////////////////////////////////////////////////////////////////////////
// CDBComm
const IID IID_Field = {0x113033F4,0xF682,0x11D2,{0xBB,0x62,0x00,0xC0,0x4F,0x68,0x0A,0xCC}};
const IID IID_Fields = {0x113033F5,0xF682,0x11D2,{0xBB,0x62,0x00,0xC0,0x4F,0x68,0x0A,0xCC}};
const IID IID__Recordset = {0x113033F6,0xF682,0x11D2,{0xBB,0x62,0x00,0xC0,0x4F,0x68,0x0A,0xCC}};
const IID IID__Connection = {0x113033DE,0xF682,0x11D2,{0xBB,0x62,0x00,0xC0,0x4F,0x68,0x0A,0xCC}};

CDBComm::CDBComm()
{
    m_pRecordset    = NULL;
    m_pConnection    = NULL;

    m_pos            = 0;
    m_bCreate        = FALSE;

    m_szConn        = L"";
}

CDBComm::~CDBComm()
{
}


BEGIN_MESSAGE_MAP(CDBComm, CWnd)
    //{{AFX_MSG_MAP(CDBComm)
        // NOTE - the ClassWizard will add and remove mapping macros here.
    //}}AFX_MSG_MAP
END_MESSAGE_MAP()


/////////////////////////////////////////////////////////////////////////////
// CDBComm message handlers

BOOL CDBComm::OpenDB(CString szDatabase, HWND phWnd)
{    
    hWnd = phWnd;

    HRESULT hr = S_OK;
    CLSID    clsid;

    hr = CLSIDFromProgID(_T("ADOCE.Connection.3.1"),&clsid);
    hr = ::CoCreateInstance(clsid,NULL, CLSCTX_ALL
                            , IID__Connection,(void**)&m_pConnection);
    if(FAILED(hr))
    {
        ::MessageBox(hWnd, _T("Create Connetion fail"), L"Database", NULL);
        return FALSE;
    }

    CString        strConn;
    COleVariant varConn;
    strConn.Format(L"provider=cedb; data source=%s", szDatabase);
    varConn.SetString(strConn, VT_BSTR);
    m_szConn    = strConn;

    hr = m_pConnection->Open(varConn.bstrVal, L"", L"", -1);

    if(FAILED(hr))
    {
        ::MessageBox(hWnd, _T("Open Database fail"), L"Database", NULL);

        hr = m_pConnection->Open(TEXT("provider=cedb"),TEXT(""),TEXT(""),-1);
        if(FAILED(hr))
        {
            ::MessageBox(hWnd, _T("Open (provider=cedb) fail."), L"Database", NULL);
            return FALSE;
        }

        CString strSQL;
        strSQL.Format(L"Create Database '%s'", szDatabase);
        hr = m_pConnection->Execute(strSQL.GetBuffer(0), NULL, adCmdText, NULL);

        if (FAILED(hr)) {
            ::MessageBox(hWnd, _T("Create Database fail"), L"Database", NULL);
            return FALSE;
        }
        else {
            m_bCreate    = TRUE;
            m_pConnection->Close();
        }

        hr = m_pConnection->Open(varConn.bstrVal, L"", L"", -1);
        if (FAILED(hr)) {
            ::MessageBox(hWnd, _T("Open Database fail"), L"Database", NULL);
            return FALSE;
        }
    }

    return TRUE;
}

void CDBComm::CloseDB()
{
    m_pConnection->Close();
    m_pRecordset->Close();

    m_pConnection->Release();
    m_pRecordset->Release();

    m_pConnection    = NULL;
    m_pRecordset    = NULL;
}

BOOL CDBComm::EXEC(CString strSQL)
{
    HRESULT hr;

    hr = m_pConnection->Execute(strSQL.GetBuffer(0), NULL, adCmdText, NULL);
    
    if(FAILED(hr)){
        ::MessageBox(hWnd, L"incorrect SQL", L"Database", NULL);
        return FALSE;
    }

    return TRUE;
}

BOOL CDBComm::DataUpdate(CString szTable, CString szFields, CString szValues, CString szWhere)
{
    CString strSQL;
    strSQL.Format(L"SELECT %s FROM %s WHERE %s", szFields, szTable, szWhere);
    
    CString strFields[10], strValues[10];

    int nFd = 0, nVal = 0;

    while (1) {
        int num = 0, num2 = 0;
        num = szFields.Find(L",", 0);
        num2 = szValues.Find(L",", 0);
        if (num != -1) {
            strFields[nFd] = szFields.Left(num);
            szFields = szFields.Mid(num+1);
            szFields.TrimLeft();

            strValues[nFd] = szValues.Left(num2);            
            strValues[nFd].TrimLeft('\'');
            strValues[nFd].TrimRight('\'');
            szValues = szValues.Mid(num2+1);
            szValues.TrimLeft();
            nFd++;
        }        
        else {
            strFields[nFd] = szFields;

            strValues[nFd] = szValues;
            strValues[nFd].TrimLeft('\'');
            strValues[nFd].TrimRight('\'');
            nFd++;
            break;
        }
    }
    
    HRESULT hr = S_OK;
    CLSID clsid;

    hr = CLSIDFromProgID(_T("ADOCE.Recordset.3.1"),&clsid);
    hr = ::CoCreateInstance(clsid,NULL,    CLSCTX_ALL
                            , IID__Recordset,(void**)&m_pRecordset);
    if(FAILED(hr))
    {
        ::MessageBox(hWnd, L"Create Recordset fail.", L"Database", NULL);
        CloseCursor();
        return FALSE;
    }

    COleVariant varSQL;
    COleVariant varConn;

    CursorTypeEnum cte = adOpenDynamic;
    LockTypeEnum lte = adLockOptimistic;
    long option = adCmdText;

    varSQL.SetString(strSQL,VT_BSTR);
    varConn.SetString(m_szConn,VT_BSTR);

    long nState;
    m_pRecordset->get_State(&nState);
    if(nState==1)m_pRecordset->Close();

    hr = m_pRecordset->Open(varSQL,varConn,cte,lte,option);
    if(FAILED(hr)){
        ::MessageBox(hWnd, L"Open Recordset fail", L"Database", NULL);
        CloseCursor();
        return FALSE;
    }

    long lcount;
    hr = m_pRecordset->get_RecordCount(&lcount);
    if(lcount==0){
        CloseCursor();
        return FALSE;
    }
    
    IADOCEField        *field    = NULL;
    IADOCEFields    *fields    = NULL;

    COleVariant varFields;
    COleVariant varValue;
    CString strOutput;

    long nfc;

    for(int i=0;i<lcount;i++){
        hr = m_pRecordset->get_Fields(&fields);
        fields->get_Count(&nfc);
        for (int j = 0; j < nfc; j++){
            varFields.SetString(strFields[j], VT_BSTR);
            hr = fields->get_Item(varFields,&field);
            varValue.SetString(strValues[j], VT_BSTR);
            hr = m_pRecordset->Update(varFields, varValue);
        }
        hr = m_pRecordset->MoveNext();        
    }

    field->Release();
    fields->Release();
    field = NULL;
    fields = NULL;

    CloseCursor();

    return TRUE;
}

long CDBComm::Max_Count(CString strSQL)
{    
    long max_cnt    = -1;

    HRESULT hr = S_OK;
    CLSID clsid;

    hr = CLSIDFromProgID(_T("ADOCE.Recordset.3.1"),&clsid);
    hr = ::CoCreateInstance(clsid,NULL,    CLSCTX_ALL
                            , IID__Recordset,(void**)&m_pRecordset);
    if(FAILED(hr))
    {
        ::MessageBox(hWnd, L"Create Recordset fail", L"Database", NULL);
        CloseCursor();
        return max_cnt;
    }

    COleVariant varSQL;
    COleVariant varConn;

    CursorTypeEnum cte = adOpenDynamic;
    LockTypeEnum lte = adLockOptimistic;
    long option = adCmdText;

    varSQL.SetString(strSQL,VT_BSTR);
    varConn.SetString(m_szConn,VT_BSTR);

    long nState;
    m_pRecordset->get_State(&nState);
    if(nState==1)m_pRecordset->Close();

    hr = m_pRecordset->Open(varSQL,varConn,cte,lte,option);
    if(FAILED(hr)){
        ::MessageBox(hWnd, L"Open Recordset fail", L"Database", NULL);
        CloseCursor();
        return max_cnt;
    }

    hr = m_pRecordset->get_RecordCount(&max_cnt);

    hr = m_pRecordset->Close();
    m_pRecordset->Release();
    m_pRecordset    = NULL;

    return max_cnt;
}

long CDBComm::GetPosition(CString strSQL, CString findField, CString findValue)
{    
    m_lCount    = 0;
    m_nCur        = 0;

    long    nPos    = -1;
    int        nType    = TYPE_STR;

    if (findValue.Find(L'\'', 0) != -1) {
        nType = TYPE_STR;
        findValue.TrimLeft(L'\'');
        findValue.TrimRight(L'\'');
    }
    else
        nType = TYPE_INT;
    
    HRESULT hr = S_OK;
    CLSID clsid;

    hr = CLSIDFromProgID(_T("ADOCE.Recordset.3.1"),&clsid);
    hr = ::CoCreateInstance(clsid,NULL,    CLSCTX_ALL
                            , IID__Recordset,(void**)&m_pRecordset);
    if(FAILED(hr))
    {
        ::MessageBox(hWnd, L"Create Recordset fail", L"Database", NULL);
        CloseCursor();
        return nPos;
    }

    COleVariant varSQL;
    COleVariant varConn;

    CursorTypeEnum cte = adOpenDynamic;
    LockTypeEnum lte = adLockOptimistic;
    long option = adCmdText;

    varSQL.SetString(strSQL,VT_BSTR);
    varConn.SetString(m_szConn,VT_BSTR);

    long nState;
    m_pRecordset->get_State(&nState);
    if(nState==1)m_pRecordset->Close();

    hr = m_pRecordset->Open(varSQL,varConn,cte,lte,option);
    if(FAILED(hr)){
        ::MessageBox(hWnd, L"Open Recordet fail", L"Database", NULL);
        CloseCursor();
        return nPos;
    }

    hr = m_pRecordset->get_RecordCount(&m_lCount);
    if(m_lCount==0){
        CloseCursor();
        return nPos;
    }

    IADOCEField        *field    = NULL;
    IADOCEFields    *fields    = NULL;

    COleVariant varFields;
    COleVariant varValue;

    CString output;

    for ( int i = m_nCur ; i < m_lCount ; i++) {
        hr = m_pRecordset->get_Fields(&fields);
        varFields.SetString(findField,VT_BSTR);
        hr = fields->get_Item(varFields,&field);
        hr = field->get_Value(&varValue);

        switch(nType) {
        case TYPE_STR:
            output = varValue.bstrVal;
            break;
        case TYPE_INT:
            output.Format(L"%d", varValue.intVal);
            break;
        }
        
        if (findValue == output) {
            hr = m_pRecordset->get_AbsolutePosition(&nPos);
            break;
        }
        
        hr = m_pRecordset->MoveNext();
    }

    field->Release();
    fields->Release();
    field    = NULL;
    fields    = NULL;

    CloseCursor();

    return nPos;
}

BOOL CDBComm::OpenCursor(CString strSQL)
{
    m_lCount    = 0;
    m_nCur        = 0;
    
    HRESULT hr = S_OK;
    CLSID clsid;

    hr = CLSIDFromProgID(_T("ADOCE.Recordset.3.1"),&clsid);
    hr = ::CoCreateInstance(clsid,NULL,    CLSCTX_ALL
                            , IID__Recordset,(void**)&m_pRecordset);
    if(FAILED(hr))
    {
        ::MessageBox(hWnd, L"Create Recordset fail", L"Database", NULL);
        CloseCursor();
        return FALSE;
    }

    COleVariant varSQL;
    COleVariant varConn;

    CursorTypeEnum cte = adOpenDynamic;
    LockTypeEnum lte = adLockOptimistic;
    long option = adCmdText;

    varSQL.SetString(strSQL,VT_BSTR);
    varConn.SetString(m_szConn,VT_BSTR);

    long nState;
    m_pRecordset->get_State(&nState);
    if(nState==1)m_pRecordset->Close();

    hr = m_pRecordset->Open(varSQL,varConn,cte,lte,option);
    if(FAILED(hr)){
        ::MessageBox(hWnd, L"Open Recordet fail", L"Database", NULL);
        CloseCursor();
        return FALSE;
    }

    hr = m_pRecordset->get_RecordCount(&m_lCount);
    if(m_lCount==0){
        CloseCursor();
        return FALSE;
    }

    return TRUE;
}

void CDBComm::CloseCursor()
{
    HRESULT hr = S_OK;

    hr = m_pRecordset->Close();
    m_pRecordset->Release();
    m_pRecordset    = NULL;

    m_lCount    = 0;
    m_nCur        = 0;
}

BOOL CDBComm::List_Fetch()
{    
    HRESULT hr = S_OK;

    IADOCEField        *field    = NULL;
    IADOCEFields    *fields    = NULL;

    COleVariant varFields;
    COleVariant varValue;
    COleVariant varNo, varName, varAge, varNote;    
    CString strOutput;

    if (m_nCur < m_lCount) {
        hr = m_pRecordset->get_Fields(&fields);

        varFields.SetString(_T("nNo"),VT_BSTR);
        hr = fields->get_Item(varFields,&field);
        hr = field->get_Value(&varNo);

        varFields.SetString(_T("szName"),VT_BSTR);
        hr = fields->get_Item(varFields,&field);
        hr = field->get_Value(&varName);

        varFields.SetString(_T("nAge"),VT_BSTR);
        hr = fields->get_Item(varFields,&field);
        hr = field->get_Value(&varAge);

        varFields.SetString(_T("szNote"),VT_BSTR);
        hr = fields->get_Item(varFields,&field);
        hr = field->get_Value(&varNote);

        m_no        = varNo.intVal;
        m_name        = varName.bstrVal;
        m_age        = varAge.intVal;
        m_note        = varNote.bstrVal;
        
        hr = m_pRecordset->MoveNext();
        
        m_nCur++;
        return TRUE;
    }
    field    = NULL;
    fields    = NULL;

    CloseCursor();
    
    return FALSE;
}

BOOL CDBComm::SelectData()
{
    HRESULT hr = S_OK;

    IADOCEField        *field    = NULL;
    IADOCEFields    *fields    = NULL;

    COleVariant varFields;
    COleVariant varValue;
    COleVariant varNo, varName, varAge, varNote;
    CString strOutput;
    
    hr = m_pRecordset->get_AbsolutePosition(&m_pos);
    hr = m_pRecordset->get_Fields(&fields);

    varFields.SetString(_T("nNo"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varNo);

    varFields.SetString(_T("szName"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varName);

    varFields.SetString(_T("nAge"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varAge);

    varFields.SetString(_T("szNote"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varNote);        

    m_no        = varNo.intVal;
    m_name        = varName.bstrVal;
    m_age        = varAge.intVal;
    m_note        = varNote.bstrVal;
        
    field    = NULL;
    fields    = NULL;
    CloseCursor();

    if (m_lCount > 1) return FALSE;
    else return TRUE;
}

BOOL CDBComm::Move_Position(long nPos, UINT PosType)
{
    BOOL bFlag = TRUE;

    HRESULT hr = S_OK;

    IADOCEField        *field    = NULL;
    IADOCEFields    *fields    = NULL;

    COleVariant varFields;
    COleVariant varValue;
    COleVariant varStart;
    COleVariant varNo, varName, varAge, varNote, varFName;

    varStart.intVal = (int)m_pos;
    
    m_pRecordset->Move(nPos-1, varStart);

    switch(PosType) {
    case MOVE_FIRST:
        hr = m_pRecordset->MoveFirst();
        break;
    case MOVE_PREVIOUS:
        if (nPos != 1) m_pRecordset->MovePrevious();
        else bFlag    = FALSE;
        break;            
    case MOVE_NEXT:
        if (nPos != m_lCount) hr = m_pRecordset->MoveNext();
        else bFlag    = FALSE;
        break;
    case MOVE_LAST:
        hr = m_pRecordset->MoveLast();
        break;
    case MOVE_STAY:
        if ( m_lCount < 1) bFlag    = FALSE;
        else if (nPos > m_lCount)
            m_pRecordset->MoveLast();
        break;
    }
    ///// data /////
    hr = m_pRecordset->get_AbsolutePosition(&m_pos);
    hr = m_pRecordset->get_Fields(&fields);

    varFields.SetString(_T("nNo"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varNo);
    
    varFields.SetString(_T("szName"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varName);

    varFields.SetString(_T("nAge"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varAge);

    varFields.SetString(_T("szNote"),VT_BSTR);
    hr = fields->get_Item(varFields,&field);
    hr = field->get_Value(&varNote);        

    m_no        = varNo.intVal;
    m_name        = varName.bstrVal;
    m_age        = varAge.intVal;
    m_note        = varNote.bstrVal;
        
    field    = NULL;
    fields    = NULL;
    CloseCursor();
    
    return bFlag;
}


#if !defined(AFX_DBCOMM_H__AEFB388D_6313_4DC2_87FE_48D6F2D4E8CB__INCLUDED_)
#define AFX_DBCOMM_H__AEFB388D_6313_4DC2_87FE_48D6F2D4E8CB__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
// DBComm.h : header file
//
#include "ADOCE31.h"

#define MOVE_FIRST        0
#define MOVE_PREVIOUS    1
#define MOVE_NEXT        2
#define MOVE_LAST        3
#define MOVE_STAY        4
/////////////////////////////////////////////////////////////////////////////
// CDBComm window

class CDBComm : public CWnd
{
// Construction
public:
    CDBComm();

// Attributes
public:

// Operations
public:

// Overrides
    // ClassWizard generated virtual function overrides
    //{{AFX_VIRTUAL(CDBComm)
    //}}AFX_VIRTUAL

// Implementation
public:
    BOOL OpenCursor(CString strSQL);
    long GetPosition(CString strSQL, CString findField, CString findValue);
    long Max_Count(CString strSQL);
    BOOL DataUpdate(CString szTable, CString szFields, CString szValues, CString szWhere);
    BOOL EXEC(CString strSQL);
    void CloseDB();
    BOOL OpenDB(CString szDatabase, HWND phWnd);

protected:
    void CloseCursor();
    
protected:
    int                    m_nCur;
    
    CString                m_szConn;
    IADOCERecordset*    m_pRecordset;
    IADOCEConnection*    m_pConnection;

public:
    long                m_pos;
    BOOL                m_bCreate;
    long                m_lCount; // OpenCursor Max Count

    // ↓ User member
public:
    CString m_note;
    int        m_age;
    CString m_name;
    int        m_no;

    HWND    hWnd;

public:
    BOOL Move_Position(long nPos, UINT PosType);
    BOOL SelectData();
    BOOL List_Fetch();
    virtual ~CDBComm();

    // Generated message map functions
protected:
    //{{AFX_MSG(CDBComm)
        // NOTE - the ClassWizard will add and remove member functions here.
    //}}AFX_MSG
    DECLARE_MESSAGE_MAP()
};

/////////////////////////////////////////////////////////////////////////////

//{{AFX_INSERT_LOCATION}}
// Microsoft Visual C++ will insert additional declarations immediately before the previous line.

#endif // !defined(AFX_DBCOMM_H__AEFB388D_6313_4DC2_87FE_48D6F2D4E8CB__INCLUDED_)