（数据库）ADO VC++之四书五经

     最近做了一个数据库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;

}

望诸君雅正！