最近做了一个数据库Application,开发过程中遇到了一些问题。费了好大力气,才解决了。
当中用到了ADO链库技术,现将自己的感想,写在下面!只是个人见解,仅供讨论。
下面是程序中的关键代码,觉得还不错。执行成功了,但是还不非常“鲁棒”。
BOOL CADOListApp::InitInstance()
{
AfxEnableControlContainer();
// Standard initialization
// If you are not using these features and wish to reduce the size
// of your final executable, you should remove from the following
// the specific initialization routines you do not need.
#ifdef _AFXDLL
Enable3dControls(); // Call this when using MFC in a shared DLL
#else
Enable3dControlsStatic(); // Call this when linking to MFC statically
#endif
/***********************************************************************************************************/
/***************/
/* 连接数据库操作*/
/**************/
if (!AfxOleInit())//初始化库环境
{
AfxMessageBox("OLE/COM初始化失败");
return FALSE;
}
_bstr_t str="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=.";
m_pConnection.CreateInstance(__uuidof(Connection));
try
{
// 打开本地Access库mydesign.mdb 连接数据库
if (m_pConnection->Open(str,"","",adModeUnknown) == S_OK)
{//master ->emp (int ,char,char)//数据库的信息
// AfxMessageBox("数据库连接成功!");
}
}
catch(_com_error e)///捕捉异常
{
CString errormessage;
errormessage.Format("连接数据库失败!/r/n错误信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///显示错误信息
}
/********************************************************************************************************88*/
// Change the registry key under which our settings are stored.
// TODO: You should modify this string to be something appropriate
// such as the name of your company or organization.
SetRegistryKey(_T("Local AppWizard-Generated Applications"));
LoadStdProfileSettings(); // Load standard INI file options (including MRU)
// Register the application's document templates. Document templates
// serve as the connection between documents, frame windows and views.
CSingleDocTemplate* pDocTemplate;
pDocTemplate = new CSingleDocTemplate(
IDR_MAINFRAME,
RUNTIME_CLASS(CADOListDoc),
RUNTIME_CLASS(CMainFrame), // main SDI frame window
RUNTIME_CLASS(CADOListView));
AddDocTemplate(pDocTemplate);
// Parse command line for standard shell commands, DDE, file open
CCommandLineInfo cmdInfo;
ParseCommandLine(cmdInfo);
// Dispatch commands specified on the command line
if (!ProcessShellCommand(cmdInfo))
return FALSE;
// The one and only window has been initialized, so show and update it.
m_pMainWnd->ShowWindow(SW_SHOW);
m_pMainWnd->UpdateWindow();
return TRUE;
}
void CADOListView::OnInitReport()//初始化列表标题
{
m_List.InsertColumn(0,_T("ENo"),LVCFMT_LEFT,80,-1);
m_List.InsertColumn(1,_T("EName"),LVCFMT_LEFT,80,-1);
m_List.InsertColumn(2,_T("EWork"),LVCFMT_LEFT,80,-1);
m_List.SetExtendedStyle(LVS_EX_FULLROWSELECT |LVS_EX_GRIDLINES);
}
void CADOListView::OnShowTable()//显示数据库数据到列表
{
m_List.DeleteAllItems();//清空列表
theApp.m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
theApp.m_pRecordset->Open("select * from emp",theApp.m_pConnection.GetInterfacePtr(),
adOpenDynamic,//动态
adLockOptimistic,//乐观封锁法
adCmdText);//文本查询
}
catch (_com_error e)
{
CString strErr="Select语句执行失败!";
AfxMessageBox(e.ErrorMessage()+strErr);
}
_variant_t vat;
CString ENo,EName,EWork;
//CString strDomainName;
while (!(theApp.m_pRecordset->adoBOF) && !theApp.m_pRecordset->adoEOF)
{//获取一个字段
vat = theApp.m_pRecordset->GetCollect("职工号");
if (vat.vt != VT_NULL)
{
ENo = (LPCSTR)_bstr_t(vat);
ENo.TrimLeft();//清除左边的空格
ENo.TrimRight();//清除右边的空格
}
vat = theApp.m_pRecordset->GetCollect("职工姓名");
if (vat.vt != VT_NULL)
{
EName = (LPCSTR)_bstr_t(vat);
EName.TrimLeft();
}
vat = theApp.m_pRecordset->GetCollect("工作");
if (vat.vt != VT_NULL)
{
EWork = (LPCSTR)_bstr_t(vat);
EWork.TrimLeft();
}
int nItem = m_List.InsertItem(0,ENo);
m_List.SetItem(nItem,1,LVIF_TEXT,EName,0, LVIS_SELECTED, LVIS_SELECTED, 0);
m_List.SetItem(nItem,2,LVIF_TEXT,EWork,0, LVIS_SELECTED, LVIS_SELECTED, 0);
theApp.m_pRecordset-> MoveNext();
}
theApp.m_pRecordset->Close();
}
void CADOListView::OnButtonDel()
{
// TODO: Add your control notification handler code here
CString strSql,strN,strJ;
int ID;
UpdateData(true);//从控件传到变量
//strN=m_strName;
//strJ=m_strJob;
ID=m_ID;
if(m_ID==0)
{
MessageBox("请输入员工号!");
return;
}
strSql.Format("delete from emp where 职工号=%d",ID);
if(theApp.m_pConnection->Execute((_bstr_t)strSql,NULL,adCmdText))
{
MessageBox("删除成功!");
}
m_strName="";
m_strJob="";
m_ID=0;
UpdateData(FALSE);//清空编辑框
OnShowTable();//重新显示表内容
}
void CADOListView::OnButtonAdd()
{
// TODO: Add your control notification handler code here
CString strSql,strN,strJ;
int ID;
UpdateData(true);//从控件传到变量
strN=m_strName;
strJ=m_strJob;
ID=m_ID;
if(ID==0||strJ==""||strN=="")
{
MessageBox("请输入员工完整信息!");
return;
}
strSql.Format("insert into emp values(%d,'%s','%s')",ID,strN,strJ);
theApp.m_pConnection->Execute((_bstr_t)strSql,NULL,adCmdText);
m_strName="";
m_strJob="";
m_ID=0;
UpdateData(FALSE);清空编辑框
OnShowTable();//重新显示表内容
}
void CADOListView::OnButtonModi()
{
// TODO: Add your control notification handler code here
CString strSql,strN,strJ;
int ID;
UpdateData(true);//从控件传到变量
strN=m_strName;
strJ=m_strJob;
ID=m_ID;
if(ID==0||strJ==""||strN=="")
{
MessageBox("请输入要修改的内容");
return;
}
strSql.Format("update emp set 职工姓名='%s',工作='%s' where 职工号=%d",strN,strJ,ID);
theApp.m_pConnection->Execute((_bstr_t)strSql,NULL,adCmdText);
m_strName="";
m_strJob="";
m_ID=0;
UpdateData(FALSE);清空编辑框
OnShowTable();//重新显示表内容
}
void CADOListView::OnClickList1(NMHDR* pNMHDR, LRESULT* pResult) //单击表中行
{
// TODO: Add your control notification handler code here
POSITION pos = m_List.GetFirstSelectedItemPosition();
int m_nIndex = m_List.GetNextSelectedItem(pos); // 得到项目索引
CString strENo,strEName,strEWork;
//CString strMusicName,strSomeOne,strSomeThing;
strENo = m_List.GetItemText(m_nIndex,0);
strEName = m_List.GetItemText(m_nIndex,1);
strEWork = m_List.GetItemText(m_nIndex,2);
((CEdit*)GetDlgItem(IDC_EDIT1))->SetWindowText(strENo);
((CEdit*)GetDlgItem(IDC_EDIT2))->SetWindowText(strEName);
((CEdit*)GetDlgItem(IDC_EDIT3))->SetWindowText(strEWork );
*pResult = 0;
}
void CADOListView::OnDblclkList1(NMHDR* pNMHDR, LRESULT* pResult)
{
// TODO: Add your control notification handler code here
POSITION pos = m_List.GetFirstSelectedItemPosition();
int m_nIndex = m_List.GetNextSelectedItem(pos); // 得到项目索引
CString strENo,strEName,strEWork;
//CString strMusicName,strSomeOne,strSomeThing;
strENo = m_List.GetItemText(m_nIndex,0);
strEName = m_List.GetItemText(m_nIndex,1);
strEWork = m_List.GetItemText(m_nIndex,2);
MessageBox("员工号:"+strENo+"/n"
+"员工姓名:"+strEName+"/n"
+"员工工作:"+strEWork);
*pResult = 0;
}