// ADOAccessDlg.cpp : implementation file
//

#include "stdafx.h"
#include "ADOAccess.h"
#include "ADOAccessDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CADOAccessDlg dialog

CADOAccessDlg::CADOAccessDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CADOAccessDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CADOAccessDlg)
	m_strSql = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CADOAccessDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CADOAccessDlg)
	DDX_CBString(pDX, IDC_CMB_SQL, m_strSql);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CADOAccessDlg, CDialog)
	//{{AFX_MSG_MAP(CADOAccessDlg)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_BTNCREATEDB, OnBtncreatedb)
	ON_BN_CLICKED(IDC_BTNOPENDB, OnBtnopendb)
	ON_BN_CLICKED(IDC_BTNEXEC, OnBtnexec)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CADOAccessDlg message handlers

BOOL CADOAccessDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	CenterWindow(GetDesktopWindow());	// center to the hpc screen

	//初始化COM庫
	CoInitializeEx(NULL,COINIT_MULTITHREADED);

	//建立與Access數據庫連接
    if (!CreateConnection())
    {
	   AfxMessageBox(_T("建立連接失敗"));
	   return FALSE;
    }

   	//設置人列表框標題	
	CListCtrl * pListCtrl = (CListCtrl*)GetDlgItem(IDC_LST_VIEW);

	CRect rt;
    pListCtrl->GetClientRect(&rt);
	pListCtrl->InsertColumn(0,_T("姓名"), LVCFMT_LEFT, rt.Width() * 0.5);
    pListCtrl->InsertColumn(1,_T("年齡"), LVCFMT_LEFT, rt.Width() * 0.5);
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CADOAccessDlg::OnDestroy() 
{
	//關閉連接
	CloseConnection();
	
	//釋放COM庫
    CoUninitialize();	
	CDialog::OnDestroy();
}



//與ADO數據庫建立連接
BOOL CADOAccessDlg::CreateConnection()
{
	CLSID	tClsid;
	HRESULT hr;

	//1,得到ADO連接對象對應ClassID
	hr = CLSIDFromProgID( g_szADOCE30ConnProgID, &tClsid );
	
	if (FAILED(hr))
	{
		return FALSE;
	}
	
    //2,創建ADO連接對象
	hr = CoCreateInstance (tClsid, NULL,
		CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER,
		IID__Connection, (LPVOID *)&m_pADOCEConn);
	
	if((!m_pADOCEConn) || FAILED(hr))
	{
		return FALSE;
	}
		
	//3.設置連接數據庫的Provider
	hr = m_pADOCEConn->put_Provider(TEXT("cedb"));
	if FAILED(hr)
	{
		return FALSE;
	}

	//4.得到記錄集對象的ClassID
	hr = CLSIDFromProgID( g_szADOCE30RSProgID, &tClsid );
	if FAILED(hr)
	{
		return FALSE;
	}

	//5.創建結果集對象
	hr = CoCreateInstance (tClsid, NULL,
		CLSCTX_INPROC_SERVER | CLSCTX_LOCAL_SERVER,
		IID__Recordset,(LPVOID *)&m_pADOCERS);
				
	if((!m_pADOCERS) || FAILED(hr))
	{
		return FALSE;
	}
								
	return TRUE;

}

//關閉與Access數據庫連接
void CADOAccessDlg::CloseConnection()
{
	if (m_pADOCEConn != NULL)
	{
		m_pADOCEConn->Close();
		m_pADOCEConn->Release();
		m_pADOCEConn = NULL;
	}

	if (m_pADOCERS != NULL)
	{
		m_pADOCERS->Close();
        m_pADOCERS->Release();
		m_pADOCERS = NULL;
	}
}

/*
函數名稱：新建數據庫
入口參數：szDbName表示要新建的數據庫名稱
出口參數：無
返 回 值：TRUE:新建數據庫成功;FALSE:新建數據庫失敗
*/
BOOL CADOAccessDlg::CreateDatabase(LPCTSTR szDbName)
{
	VARIANT varTSQL,varEmpty; 
	HRESULT hr;
    TCHAR szSqlBuf[1024];
	wcscpy(szSqlBuf,_T("CREATE DATABASE '"));
	wcscat(szSqlBuf,szDbName);
	wcscat(szSqlBuf,_T("'"));

	VariantInit(&varTSQL);
	VariantInit(&varEmpty);
	
	varTSQL.bstrVal = SysAllocString(szSqlBuf);
	varTSQL.vt = VT_BSTR;
    
	//創建數據庫
    hr=m_pADOCERS->Open(varTSQL,varEmpty,MSADOCE::adOpenDynamic,MSADOCE::adLockOptimistic,MSADOCE::adCmdText);
	if(FAILED(hr))
	{
		return FALSE;
	}
	m_pADOCERS->Close();
	return TRUE;
}


/*
函數名稱：打開數據庫
入口參數：szDbName表示要打開的數據庫名稱
出口參數：無
返 回 值：TRUE:打開數據庫成功;FALSE:打開數據庫失敗
*/
BOOL CADOAccessDlg::OpenDatabase(LPCTSTR szDbName)
{
	HRESULT hr;
	VARIANT varConn1;


    //與指定的數據庫建立連接
	hr = m_pADOCEConn->Open(LPTSTR(szDbName),TEXT(""), TEXT(""),MSADOCE::adOpenUnspecified);
	if FAILED(hr)
	{
		return FALSE;
	}

	VariantInit(&varConn1);
	varConn1.pdispVal=m_pADOCEConn;
	varConn1.vt=VT_DISPATCH;
	//設置結果集對象到已建立的連接上
	hr=m_pADOCERS->put_ActiveConnection(varConn1);
	if (FAILED(hr))
	{
		return FALSE;
	}

	return TRUE;
}

//新建數據庫按鈕單擊事件代碼
void CADOAccessDlg::OnBtncreatedb() 
{
   CString dbFileName;

   TCHAR szFilters[]=_T("Access File (*.cdb)|*.cdb||");
   
   //打開文件保存對話框
   CFileDialog fileDlg (FALSE, _T("cdb"),NULL,OFN_PATHMUSTEXIST,szFilters,this);
   if (fileDlg.DoModal() == IDOK)
   {
	   dbFileName = fileDlg.GetPathName();
   }
   else
   {
	   return;
   }

   //
   if (!CreateDatabase(dbFileName))
   {
	   AfxMessageBox(_T("創建數據庫失敗"));
	   return;
   }	
}

//打開數據庫按鈕單擊事件代碼
void CADOAccessDlg::OnBtnopendb() 
{
   CString dbFileName;

   TCHAR szFilters[]=_T("Access File (*.cdb)|*.cdb||");
 
   //調用，打開文件對話框，選擇Access數據庫文件
   CFileDialog fileDlg (TRUE, _T("cdb"),_T("*.cdb"),OFN_FILEMUSTEXIST,szFilters,this);
   if (fileDlg.DoModal() == IDOK)
   {
	   dbFileName = fileDlg.GetPathName();
   }
   else
   {
	   return;
   }

   //打開數據庫
   if (!OpenDatabase(dbFileName))
   {
	   AfxMessageBox(_T("打開數據庫失敗"));
	   return;
   }	
}

//將表中的記錄在列表視圖上顯示
void CADOAccessDlg::AddRecordToView()
{
	HRESULT hr;
	short iEndOfFile;
	VARIANT			varData,varFieldSelect;
	TCHAR*			pszName;
	TCHAR*			pszAge;	
	pszName = (TCHAR*)malloc(40);
	pszAge = (TCHAR*)malloc(40);
    hr = m_pADOCERS->MoveFirst();
	//判斷記錄集指針是否到達尾部
	hr = m_pADOCERS->get_EOF(&iEndOfFile);
	int k=0;
	//設置學生列表框標題	
	CListCtrl * pListCtrl = (CListCtrl*)GetDlgItem(IDC_LST_VIEW);
	while(!iEndOfFile)
	{
			//得到表中字段集對象
			hr = m_pADOCERS->get_Fields(&m_pADOCEFields);
			varFieldSelect.uiVal = 0;
			varFieldSelect.vt = VT_UI2;
			hr = m_pADOCEFields->get_Item(varFieldSelect, &m_pADOCEField);
			hr = m_pADOCEField->get_Value(&varData);
			lstrcpy(pszName, varData.bstrVal);
			
			//得到字段對象
			varFieldSelect.uiVal = 1;
			varFieldSelect.vt = VT_I4;
			hr = m_pADOCEFields->get_Item(varFieldSelect, &m_pADOCEField);
			hr = m_pADOCEField->get_Value(&varData);
		    _itow(varData.lVal,pszAge,10);
			
			//向列表框中添加人信息
			pListCtrl->InsertItem(k,_T("Test"));
			//添加人名
			pListCtrl->SetItemText(k,0,pszName);
			//添加人年齡
			pListCtrl->SetItemText(k,1,pszAge);
			
			//將記錄集指針向后移動一個
			m_pADOCERS->MoveNext();
			m_pADOCERS->get_EOF(&iEndOfFile);
			k++;
	}
	free(pszName);
	free(pszAge);
}

//執行按鈕單擊事件代碼
void CADOAccessDlg::OnBtnexec() 
{
	HRESULT hr;
	VARIANT varStrSql,varEmpty;
	VariantInit(&varStrSql);
	VariantInit(&varEmpty);
	UpdateData(TRUE);
	varStrSql.bstrVal = SysAllocString(m_strSql);
	varStrSql.vt = VT_BSTR;
	hr = m_pADOCERS->Open(varStrSql,varEmpty,MSADOCE::adOpenDynamic,MSADOCE::adLockOptimistic,MSADOCE::adCmdText);
	SysFreeString(varStrSql.bstrVal);
						
	if (FAILED(hr))
	{
		AfxMessageBox(_T("執行失敗"));
	}
	long iCount = 0;
	hr = m_pADOCERS->get_RecordCount(&iCount);
	AddRecordToView();
	m_pADOCERS->Close();
}