adodlg.cpp

ADO连接数据库

// ADODlg.cpp : 实现文件
//

#include "stdafx.h"
#include "ADO.h"
#include "ADODlg.h"
#include "SpreadSheet.h"
#include ".\adodlg.h"


#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// 用于应用程序“关于”菜单项的 CAboutDlg 对话框

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// 对话框数据
	enum { IDD = IDD_ABOUTBOX };

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV 支持

// 实现
protected:
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
END_MESSAGE_MAP()


// CADODlg 对话框



CADODlg::CADODlg(CWnd* pParent /*=NULL*/)
	: CDialog(CADODlg::IDD, pParent)
	, m_Num(0)
{
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CADODlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST, m_List);
}

BEGIN_MESSAGE_MAP(CADODlg, CDialog)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	//}}AFX_MSG_MAP
	ON_BN_CLICKED(IDC_ADD, OnBnClickedAdd)
	ON_BN_CLICKED(IDC_MODIFY, OnBnClickedModify)
	ON_BN_CLICKED(IDC_DEL, OnBnClickedDel)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST, OnLvnItemchangedList)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_RUSH, OnBnClickedRush)
	ON_BN_CLICKED(IDC_EXPORT, OnBnClickedExport)
END_MESSAGE_MAP()


// CADODlg 消息处理程序
#define NUM_COLUMNS 3

static _TCHAR *_gszColumnLabel[NUM_COLUMNS] =
{
	_T("名称"), _T("年龄"),_T("性别")
};

static int _gnColumnWidth[NUM_COLUMNS] =
{
	120, 120,120,
};
IMPLEMENT_DYNAMIC(CADODlg, CDialog)

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

	// 将\“关于...\”菜单项添加到系统菜单中。

	// IDM_ABOUTBOX 必须在系统命令范围内。
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// 设置此对话框的图标。当应用程序主窗口不是对话框时，框架将自动
	//  执行此操作
	SetIcon(m_hIcon, TRUE);			// 设置大图标
	SetIcon(m_hIcon, FALSE);		// 设置小图标

	if (!AfxOleInit())
	{
		return false ;
	}
	

	if (!OpenDB())
	{
		return FALSE;
	}

	// TODO: 在此添加额外的初始化代码
	DWORD dwStyle = m_List.GetExtendedStyle();
	dwStyle |= LVS_EX_FULLROWSELECT;
	m_List.SetExtendedStyle(dwStyle);

	// 设置表头
	int			i ;
	LV_COLUMN	lvc ;

	lvc.mask = LVCF_FMT | LVCF_WIDTH | LVCF_TEXT | LVCF_SUBITEM ;
	for(i = 0; i<NUM_COLUMNS; i++)
	{
		lvc.iSubItem = i ;
		lvc.pszText = _gszColumnLabel[i] ;
		lvc.cx = _gnColumnWidth[i] ;
		lvc.fmt = LVCFMT_LEFT ;
		m_List.InsertColumn(i, &lvc);
	}
	
	GetInfo();

	return TRUE;  // 除非设置了控件的焦点，否则返回 TRUE
}

void CADODlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// 如果向对话框添加最小化按钮，则需要下面的代码
//  来绘制该图标。对于使用文档/视图模型的 MFC 应用程序，
//  这将由框架自动完成。

void CADODlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // 用于绘制的设备上下文

		SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0);

		// 使图标在工作矩形中居中
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// 绘制图标
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

//当用户拖动最小化窗口时系统调用此函数取得光标显示。
HCURSOR CADODlg::OnQueryDragIcon()
{
	return static_cast<HCURSOR>(m_hIcon);
}

void CADODlg::OnBnClickedAdd()
{
	// TODO: 在此添加控件通知处理程序代码
	int i,j;

	CString strName,strAge,strSex;
	((CEdit*)GetDlgItem(IDC_NAME))->GetWindowText(strName);

	if (strcmp(strName,_T("")) == 0)
	{
		AfxMessageBox("姓名不能为空");
		return ;
	}
	((CEdit*)GetDlgItem(IDC_AGE))->GetWindowText(strAge);
	i = atoi(strAge);

	((CEdit*)GetDlgItem(IDC_SEX))->GetWindowText(strSex);

	CString tempeg = "女";
	CString tempex = "男";

	if ((strcmp(strSex,tempex) != 0)&&(strcmp(strSex,tempeg) != 0))
	{
		AfxMessageBox("请输入男或女!");
		return ;
	}

	if (strcmp(strSex ,tempex) == 0)
	{
		j = 0;
	}
	else
	{
		j = 1;
	}

	UpdateData();
	_RecordsetPtr	m_pRecordset;
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	try
	{
		m_pRecordset->Open("SELECT * FROM Stu",                // 查询DemoTable表中所有字段
			m_pConnection.GetInterfacePtr(),	 // 获取库接库的IDispatch指针
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  
    
	m_pRecordset->AddNew();
	m_pRecordset->PutCollect("Name",_variant_t(strName));
	m_pRecordset->PutCollect("Old",_variant_t(i));
	m_pRecordset->PutCollect("Sex", _variant_t(j));
	m_pRecordset->Update();

	m_pRecordset->Close();
	m_pRecordset = NULL;

	STU_INFO Info;
	strcpy(Info.name,strName);
	Info.old = i ;
	Info.sex = j ;

	ShowInfo(TRUE,m_Num,&Info);
}

void CADODlg::OnBnClickedModify()
{
	// TODO: 在此添加控件通知处理程序代码
	long        nCurSelRow;
	CString     strSql,temp;
	int i,j;

	nCurSelRow = m_List.GetNextItem(-1,LVNI_SELECTED);
	temp = m_List.GetItemText(nCurSelRow,0);

	CString strName,strAge,strSex;
	((CEdit*)GetDlgItem(IDC_NAME))->GetWindowText(strName);
     if (strcmp(temp,strName) != 0)
     {
		 AfxMessageBox("不能修改名字！");
		 return ;
     }

	((CEdit*)GetDlgItem(IDC_AGE))->GetWindowText(strAge);
	i = atoi(strAge);

	((CEdit*)GetDlgItem(IDC_SEX))->GetWindowText(strSex);

	CString tempeg = "女";
	CString tempex = "男";

	if ((strcmp(strSex,tempex) != 0)&&(strcmp(strSex,tempeg) != 0))
	{
		AfxMessageBox("请输入男或女！");
		return ;
	}

    if (strcmp(strSex ,tempex) == 0)
    {
        j = 0;
    }
	else
	{
		j = 1;
	}

	UpdateData();

	try
	{		
		strSql.Format("update Stu set Old=%d,Sex=%d Where Name = '%s'",i,j,temp);
		_variant_t vAffected;
		m_pConnection->Execute(_bstr_t(strSql),&vAffected,adCmdText);

	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  
    
	STU_INFO Info;
	strcpy(Info.name,temp);
	Info.old = i ;
	Info.sex = j ;

	ShowInfo(FALSE,nCurSelRow,&Info);

}

void CADODlg::OnBnClickedDel()
{
	// TODO: 在此添加控件通知处理程序代码
	long        nCurSelRow;
    CString     strSql,temp;
	
	nCurSelRow = m_List.GetNextItem(-1,LVNI_SELECTED);
    temp = m_List.GetItemText(nCurSelRow,0);

	_variant_t var;
	_RecordsetPtr pName;
	pName.CreateInstance(__uuidof(Recordset));

	try
	{		
		strSql.Format("SELECT * FROM Stu Where Name = '%s'",temp);

		pName->Open(_variant_t(strSql),m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  

    pName->Delete(adAffectCurrent);

	m_List.DeleteItem(nCurSelRow) ;

	m_Num--;

}

BOOL CADODlg::GetInfo(void)
{
	_RecordsetPtr	m_pRecordset;
	m_pRecordset.CreateInstance(__uuidof(Recordset));

	_variant_t var;
	STU_INFO Info;
	CString temp;
	int i = 0;
	// 在ADO操作中建议语句中要常用try...catch()来捕获错误信息，
	// 因为它有时会经常出现一些意想不到的错误。jingzhou xu
	try
	{
		m_pRecordset->Open("SELECT * FROM Stu",m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return FALSE;
	}
    
    while (!m_pRecordset->adoEOF)
    {
		var = m_pRecordset->GetCollect("Name");
		if(var.vt != VT_NULL)
			temp = (LPCSTR)_bstr_t(var);
        strcpy(Info.name,temp);

		var = m_pRecordset->GetCollect("Old");
		if(var.vt != VT_NULL)
			temp = (LPCSTR)_bstr_t(var);
        Info.old = atoi(temp.GetBuffer());
		temp.ReleaseBuffer();

		var = m_pRecordset->GetCollect("Sex");
		if(var.vt != VT_NULL)
			temp = (LPCSTR)_bstr_t(var);
		Info.sex = atoi(temp.GetBuffer());
		temp.ReleaseBuffer();
	     
		ShowInfo(TRUE,i,&Info);

		i++;
		m_Num++;

		m_pRecordset->MoveNext();

    }

	m_pRecordset->Close();
	m_pRecordset = NULL ;

	return TRUE;
}

void CADODlg::ShowInfo(bool bNew,int nRow,PSTU_INFO pInfo)
{
	LV_ITEM			lvi;

	CString         str ;

	// 名称
	lvi.mask = LVIF_TEXT ;
	lvi.iItem = nRow;
	lvi.iSubItem = 0;	
	lvi.pszText = pInfo->name ;	
	if ( bNew == TRUE )
		m_List.InsertItem(&lvi);
	else
		m_List.SetItem(&lvi);

	// 封装模式
	lvi.iSubItem = 1 ;
	CString temp;
	temp.Format("%d",pInfo->old);
	lvi.pszText = (LPTSTR)(LPCTSTR)temp ;
	m_List.SetItem(&lvi);

	lvi.iSubItem = 2 ;
	switch(pInfo->sex)
	{
	case 1:
		lvi.pszText = "女";
		break;
	case 0 :
		lvi.pszText = "男";
		break;
	}	
	m_List.SetItem(&lvi);

	m_List.SetItemData(nRow, (DWORD_PTR)pInfo) ;
	m_List.SetFocus() ;
	return ;
}

void CADODlg::OnLvnItemchangedList(NMHDR *pNMHDR, LRESULT *pResult)
{
	LPNMLISTVIEW pNMLV = reinterpret_cast<LPNMLISTVIEW>(pNMHDR);
	// TODO: 在此添加控件通知处理程序代码
     CString temp;
     if (pNMLV->uNewState != 0)
     {
        // pInfo = (PSTU_INFO)m_List.GetItemData(pNMLV->iItem) ;

        temp = m_List.GetItemText(pNMLV->iItem,0); 
 		((CEdit*)GetDlgItem(IDC_NAME))->SetWindowText(temp) ;
        
		temp = m_List.GetItemText(pNMLV->iItem,1);
 		((CEdit*)GetDlgItem(IDC_AGE))->SetWindowText(temp) ;
 		
		temp = m_List.GetItemText(pNMLV->iItem,2);
 		((CEdit*)GetDlgItem(IDC_SEX))->SetWindowText(temp) ;
 
     }
	*pResult = 0;
}

BOOL CADODlg::OpenDB(void)
{
	m_pConnection.CreateInstance(__uuidof(Connection));
	try                 
	{	
		// 打开本地Access库Demo.mdb
		m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=student.mdb","","",adModeUnknown);
	}
	catch(_com_error e)
	{
		AfxMessageBox("数据库连接失败，确认数据库student.mdb是否在当前路径下!");
		return FALSE;
	}
	return TRUE;
}

BOOL CADODlg::CloseDB(void)
{
	if(m_pConnection->State)
		m_pConnection->Close();
	m_pConnection= NULL;  

	return TRUE;
}
void CADODlg::OnDestroy()
{
	CDialog::OnDestroy();

	// TODO: 在此处添加消息处理程序代码
	 CloseDB();
}

void CADODlg::OnBnClickedRush()
{
	// TODO: 在此添加控件通知处理程序代码
	m_List.DeleteAllItems();
	GetInfo();
}

void CADODlg::OnBnClickedExport()
{
	// TODO: 在此添加控件通知处理程序代码
	if ( MessageBox("是否要进行学生表导出操作？", "提示", MB_OKCANCEL | MB_ICONWARNING) == IDCANCEL )
		return ;

	CFileDialog			dlg(FALSE, _T("xls"), NULL, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, "Excel File(*.xls)|*.xls|", NULL) ;
	CStringArray		Rows ;
	CString				str ;

	if ( dlg.DoModal() == IDCANCEL )
		return ;

	// 如果文件存在，则删除
	DeleteFile(dlg.GetPathName()) ;

	// 将数据写入Excel文件中
	// 不支持备份
	CSpreadSheet    SS(dlg.GetPathName(), "学生表", false) ;

	SS.BeginTransaction();

	// 加入标题
	Rows.RemoveAll();
	Rows.Add("名称");
	Rows.Add("年龄");
	Rows.Add("性别");
	SS.AddHeaders(Rows);

	// 加入数据
	for(int i = 0; i < m_List.GetItemCount(); i++) 
	{
		Rows.RemoveAll();

		Rows.Add(m_List.GetItemText(i, 0));
		Rows.Add(m_List.GetItemText(i, 1));
		Rows.Add(m_List.GetItemText(i, 2));
	
		SS.AddRow(Rows);
	}

	SS.Commit() ;
}