文件名MFCconnect
数据库连接
预定义_CRT_SECURE_NO_WARNINGS
设置多字节字符集
-----------------------------
MFCconnect.cpp
//InitInstance()
if (!m_Database.ConnectDataBase())
{
AfxMessageBox("数据库连接失败!");
return FALSE;
}
if (!m_Database.ConnectDataBase())
{
AfxMessageBox("数据库连接失败!");
return FALSE;
}
// AfxEnableControlContainer();之前
------------------------------
pch.h
#include <afxwin.h>
#import "C:\Program Files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")
-----------
创建类CDatabaseSet
CDatabaseSet.h
#include "pch.h"
class CDatabaseSet
{
public:
BOOL ConnectDataBase();
_ConnectionPtr m_pConnection;
CDatabaseSet();
virtual ~CDatabaseSet();
CDatabaseSet.cpp
#include "pch.h"
#include "CDatabaseSet.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#define new DEBUG_NEW
#endif
CDatabaseSet::CDatabaseSet()
{
m_pConnection = NULL;
}
CDatabaseSet::~CDatabaseSet()
{
if (m_pConnection != NULL)
{
m_pConnection.Release();
m_pConnection = NULL;
}
}
BOOL CDatabaseSet::ConnectDataBase()
{
CString szTempStr, szConnStr;
_ConnectionPtr pConnection;
szConnStr = "Driver=SQL Server;Server=(local);Database=software;UID=sa;PWD=123456";
_bstr_t bstrSRC(szConnStr);
if (FAILED(pConnection.CreateInstance("ADODB.Connection")))
{
return FALSE;
}
HRESULT hr;
pConnection->ConnectionTimeout = 3;
pConnection->CommandTimeout = 3000;
hr = pConnection->Open(bstrSRC, "", "", adModeUnknown);
if (SUCCEEDED(hr))
{
if (m_pConnection != NULL)
{
if (m_pConnection->State == adStateOpen)
{
m_pConnection->Close();
}
m_pConnection.Release();
}
m_pConnection = pConnection;
return TRUE;
}
return FALSE;
}
----------------------------------
MFCconnect.h
#include "DatabaseSet.h"
public:
CDatabaseSet m_Database;
----------------------
绘制界面
IDD_EMP
添加类EmpInfoDlg
IDD_MFCCONNECT_DIALOG
BUTTON和LIST Control要添加变量
--------------------------
pch.h
#include "targetver.h"
#define _ATL_CSTRING_EXPLICIT_CONSTRUCTORS // 某些 CString 构造函数将是显式的
// 关闭 MFC 对某些常见但经常可放心忽略的警告消息的隐藏
#define _AFX_ALL_WARNINGS
#include <afxwin.h> // MFC 核心组件和标准组件
#include <afxext.h> // MFC 扩展
#include "framework.h"
-------------------------------
MFCconnectDlg.cpp
#include "CEmpInfoDlg.h"
// TODO: 在此添加额外的初始化代码
InitListHeader();
//添加表头
void CMFCconnectDlg::InitListHeader()
{
m_lcShowList.SetExtendedStyle(LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES | LVS_EX_CHECKBOXES);
m_lcShowList.InsertColumn(0, _T("序号"), LVCFMT_IMAGE | LVCFMT_LEFT);
m_lcShowList.SetColumnWidth(0, 100);
m_lcShowList.InsertColumn(1, _T("员工号"));
m_lcShowList.SetColumnWidth(1, 200);
m_lcShowList.InsertColumn(2, _T("员工姓名"));
m_lcShowList.SetColumnWidth(2, 205);
m_lcShowList.InsertColumn(3, _T("性别"));
m_lcShowList.SetColumnWidth(3, 150);
m_lcShowList.InsertColumn(4, _T("年龄"));
m_lcShowList.SetColumnWidth(4, 150);
}
//添加按钮
void CMFCconnectDlg::OnBnClickedAddEmpBtn()
{
// TODO: 在此添加控件通知处理程序代码
CEmpInfoDlg* pDlg = new CEmpInfoDlg(0);
pDlg->Create(IDD_EMP, this);
pDlg->ShowWindow(SW_SHOW);
}
//查询按钮
void CMFCconnectDlg::OnBnClickedQueryEmpBtn()
{
// TODO: 在此添加控件通知处理程序代码
CString strEmpNo, strEmpName;
// 查询条件-员工号
GetDlgItem(IDC_EDIT_EMP_NUM)->GetWindowText(strEmpNo);
strEmpNo.Trim();
// 查询条件-员工姓名
GetDlgItem(IDC_EDIT_EMP_NAME)->GetWindowText(strEmpName);
strEmpName.Trim();
m_lcShowList.DeleteAllItems();
QueryAllSpecialEmpInfo(strEmpNo, strEmpName);
}
//查询函数
BOOL CMFCconnectDlg::QueryAllSpecialEmpInfo(CString strEmpNo, CString strEmpName)
{
_RecordsetPtr pRs;
_variant_t var1, var2, var3, var4;
if (FAILED(pRs.CreateInstance("ADODB.Recordset")))
{
return FALSE;
}
CString szSRCstr("SELECT * FROM EMPLOYEE Where ");
if ((strEmpNo == "") && (strEmpName != ""))
{
szSRCstr += "EmpName = '" + strEmpName + "'";
}
else if ((strEmpNo != "") && (strEmpName == ""))
{
szSRCstr += "EmpNo = '" + strEmpNo + "'";
}
else
{
szSRCstr += "EmpName = '" + strEmpName + "' AND ";
szSRCstr += "EmpNo = '" + strEmpNo + "'";
}
_bstr_t bstrSRC(szSRCstr);
try
{
if (FAILED(pRs->Open(bstrSRC, _variant_t((IDispatch*)theApp.m_Database.m_pConnection, true), adOpenStatic, adLockOptimistic, adCmdText)))
{
pRs.Release();
return FALSE;
}
else
{
if (pRs->GetRecordCount() == 0)
{
pRs->Close();
pRs.Release();
return FALSE;
}
if (FAILED(pRs->MoveFirst()))
{
pRs->Close();
pRs.Release();
return FALSE;
}
while (!(pRs->adoEOF))
{
var1 = pRs->GetCollect("EmpNo");
var2 = pRs->GetCollect("EmpName");
var3 = pRs->GetCollect("EmpSex");
var4 = pRs->GetCollect("EmpAge");
// 写入表里
int iOrder = m_lcShowList.GetItemCount();
CString szTempStr;
szTempStr.Format(_T("%d"), iOrder);
m_lcShowList.InsertItem(iOrder, szTempStr);
szTempStr.Format(_T("%d"), iOrder + 1);
m_lcShowList.SetItemText(iOrder, 0, szTempStr);
m_lcShowList.SetItemText(iOrder, 1, (char*)_bstr_t(var1));
m_lcShowList.SetItemText(iOrder, 2, (char*)_bstr_t(var2));
m_lcShowList.SetItemText(iOrder, 3, (char*)_bstr_t(var3));
m_lcShowList.SetItemText(iOrder, 4, (char*)_bstr_t(var4));
pRs->MoveNext();
}
}
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
if (pRs->State == adStateOpen)
{
pRs->Close();
}
pRs.Release();
return FALSE;
}
if (pRs->State == adStateOpen)
{
pRs->Close();
}
pRs.Release();
return TRUE;
}
//修改按钮
void CMFCconnectDlg::OnBnClickedUpdateEmpBtn()
{
// TODO: 在此添加控件通知处理程序代码
// 获取打对勾项
CString strEmpNum = "";
int item = m_lcShowList.GetItemCount() - 1;
while (item >= 0)
{
if (m_lcShowList.GetCheck(item))
{
strEmpNum = m_lcShowList.GetItemText(item, 1);
//
CString strEmpName = m_lcShowList.GetItemText(item, 2);
CString strEmpSex = m_lcShowList.GetItemText(item, 3);
CString strEmpAge = m_lcShowList.GetItemText(item, 4);
CEmpInfoDlg* pDlgUpdate = new CEmpInfoDlg(1, strEmpNum, strEmpName, strEmpSex, atoi(strEmpAge), this);
pDlgUpdate->Create(IDD_EMP, this);
pDlgUpdate->ShowWindow(SW_SHOW);
// 把当前信息填到对话框里
pDlgUpdate->FillCurrInfo(strEmpNum, strEmpName, strEmpSex, strEmpAge);
}
item--;
}
}
//删除按钮
void CMFCconnectDlg::OnBnClickedDeleteEmpBtn()
{
// TODO: 在此添加控件通知处理程序代码
// 获取打对勾项
CString strEmpNo = "";
int item = m_lcShowList.GetItemCount() - 1;
while (item >= 0)
{
if (m_lcShowList.GetCheck(item))
{
strEmpNo = m_lcShowList.GetItemText(item, 1);
// 数据库中删除勾选项
theApp.m_Database.DeleteOneEmpInfo(strEmpNo);
}
item--;
}
OnBnClickedUpdateEmpBtn();
}
-------------------------------------
EmpInfoDlg.h
public:
//EmpInfoDlg(CWnd* pParent = nullptr);
CEmpInfoDlg(int nOp); // 增加构造函数
CEmpInfoDlg(int nOp,
CString strEmpNum,
CString strEmpName,
CString strEmpSex,
int nEmpAge,
void* pMainPointer); // 修改构造函数
//virtual ~EmpInfoDlg();
public:
int m_nOp; // 操作名称 0:增 1:改
CString m_strEmpNum;
CString m_strEmpName;
CString m_strEmpSex;
int m_nEmpAge;
void* m_pMainPointer;
void FillCurrInfo(CString strEmpNo,
CString strEmpName,
CString strEmpSex,
CString strEmpAge
);
public:
afx_msg void OnBnClickedRejectBtn();
afx_msg void OnClose();
virtual void PostNcDestroy();
};
---------------------------
EmpInfoDlg.cpp
#include "EmpInfoDlg.h"
#include "MFCconnectDlg.h"
void EmpInfoDlg::FillCurrInfo(CString strEmpNo, CString strEmpName, CString strEmpSex, CString strEmpAge)
{
GetDlgItem(IDC_EDIT_INFODLG_EMP_NUM)->SetWindowText(strEmpNo);
GetDlgItem(IDC_EDIT_INFODLG_EMP_NAME)->SetWindowText(strEmpName);
GetDlgItem(IDC_EDIT_INFODLG_EMP_SEX)->SetWindowText(strEmpSex);
GetDlgItem(IDC_EDIT_INFODLG_EMP_AGE)->SetWindowText(strEmpAge);
}
//确定按钮
void EmpInfoDlg::OnBnClickedButton2()
{
// TODO: 在此添加控件通知处理程序代码
CString strTemp = "";
if (m_nOp == 0) // 新增
{
获取用户输入的信息//
// 员工号
GetDlgItem(IDC_EDIT_INFODLG_EMP_NUM)->GetWindowText(m_strEmpNum);
m_strEmpNum.Trim();
if (m_strEmpNum == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工姓名
GetDlgItem(IDC_EDIT_INFODLG_EMP_NAME)->GetWindowText(m_strEmpName);
m_strEmpName.Trim();
if (m_strEmpName == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工性别
GetDlgItem(IDC_EDIT_INFODLG_EMP_SEX)->GetWindowText(m_strEmpSex);
m_strEmpSex.Trim();
if (m_strEmpSex == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工年龄
GetDlgItem(IDC_EDIT_INFODLG_EMP_AGE)->GetWindowText(strTemp);
strTemp.Trim();
if (strTemp == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
else
{
m_nEmpAge = atoi(strTemp);
}
// 写入数据库
if (theApp.m_Database.InsertOneEmpInfo(m_strEmpNum, m_strEmpName, m_strEmpSex, m_nEmpAge))
{
AfxMessageBox("增加成功!");
}
else
{
AfxMessageBox("增加失败!");
}
}
else if (m_nOp == 1) // 修改
{
// 获取用户修改的信息
获取用户输入的信息//
// 员工号
GetDlgItem(IDC_EDIT_INFODLG_EMP_NUM)->GetWindowText(m_strEmpNum);
m_strEmpNum.Trim();
if (m_strEmpNum == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工姓名
GetDlgItem(IDC_EDIT_INFODLG_EMP_NAME)->GetWindowText(m_strEmpName);
m_strEmpName.Trim();
if (m_strEmpName == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工性别
GetDlgItem(IDC_EDIT_INFODLG_EMP_SEX)->GetWindowText(m_strEmpSex);
m_strEmpSex.Trim();
if (m_strEmpSex == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
// 员工年龄
GetDlgItem(IDC_EDIT_INFODLG_EMP_AGE)->GetWindowText(strTemp);
strTemp.Trim();
if (strTemp == "")
{
AfxMessageBox("员工信息不能为空!");
return;
}
else
{
m_nEmpAge = atoi(strTemp);
}
// 更新至数据库
if (theApp.m_Database.UpdateOneEmpInfo(m_strEmpNum, m_strEmpName, m_strEmpSex, m_nEmpAge))
{
// 更新下列表
((CJSTestDlg*)m_pMainPointer)->OnBnClickedQueryEmpBtn();
AfxMessageBox("修改成功!");
}
else
{
AfxMessageBox("修改失败!");
}
}
}
void CEmpInfoDlg::OnClose()
{
// TODO: 在此添加消息处理程序代码和/或调用默认值
DestroyWindow();
//CDialogEx::OnClose();
}
void CEmpInfoDlg::PostNcDestroy()
{
// TODO: 在此添加专用代码和/或调用基类
CDialogEx::PostNcDestroy();
delete this;
}
---------------------------------------
CDatabaseSet.cpp
//插入函数
BOOL CDatabaseSet::InsertOneEmpInfo(CString strEmpNo, CString strEmpName, CString strEmpSex, int nEmpAge)
{
HRESULT hr;
_CommandPtr pCommand;
hr = pCommand.CreateInstance(__uuidof(Command));
if (FAILED(hr) == TRUE)
{
return FALSE;
}
CString szSQLText = _T("INSERT INTO EMPLOYEE(EmpNo,EmpName,EmpSex,EmpAge) VALUES(");
CString szSQLTextValues;
szSQLTextValues += _T("'") + strEmpNo + _T("',");
szSQLTextValues += _T("'") + strEmpName + _T("',");
szSQLTextValues += _T("'") + strEmpSex + _T("',");
CString strAge;
strAge.Format("%d", nEmpAge);
szSQLTextValues += strAge + _T(")");
szSQLText += szSQLTextValues;
try
{
_bstr_t CmdText = szSQLText;
pCommand->ActiveConnection = m_pConnection;
pCommand->CommandText = CmdText;
pCommand->CommandType = adCmdText;
pCommand->Execute(NULL, NULL, adCmdUnknown);
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
return FALSE;
}
pCommand.Release();
return TRUE;
}
BOOL CDatabaseSet::UpdateOneEmpInfo(CString strEmpNo, CString strEmpName, CString strEmpSex, int nEmpAge)
{
HRESULT hr;
_CommandPtr pCommand;
hr = pCommand.CreateInstance(__uuidof(Command));
if (FAILED(hr) == TRUE)
{
return FALSE;
}
CString strAge;
strAge.Format("%d", nEmpAge);
CString szSQLText = _T("UPDATE EMPLOYEE SET EmpName = ");
szSQLText += _T("'") + strEmpName + _T("',");
szSQLText += _T(" EmpSex = '") + strEmpSex + _T("',");
szSQLText += _T(" EmpAge = ") + strAge;
szSQLText += _T(" WHERE EmpNo = ");
szSQLText += _T("'") + strEmpNo + _T("'");
try
{
_bstr_t CmdText = szSQLText;
pCommand->ActiveConnection = m_pConnection;
pCommand->CommandText = CmdText;
pCommand->CommandType = adCmdText;
pCommand->Execute(NULL, NULL, adCmdUnknown);
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
return FALSE;
}
pCommand.Release();
return TRUE;
}
BOOL CDatabaseSet::DeleteOneEmpInfo(CString strEmpNo)
{
_CommandPtr commPtr;
HRESULT hr = commPtr.CreateInstance(__uuidof(Command));
if (FAILED(hr) == TRUE)
{
return FALSE;
}
CString szSQLText = ("DELETE FROM EMPLOYEE WHERE EmpNo = '") + strEmpNo + ("'");
try
{
_bstr_t CmdText = szSQLText;
commPtr->ActiveConnection = m_pConnection;
commPtr->CommandText = CmdText;
commPtr->CommandType = adCmdText;
commPtr->Execute(NULL, NULL, adCmdUnknown);
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
return FALSE;
}
commPtr.Release();
return TRUE;
}
-----------------------------
CDatabaseSet.h
public:
BOOL InsertOneEmpInfo(CString strEmpNo, CString strEmpName, CString strEmpSex, int nEmpAge);
BOOL UpdateOneEmpInfo(CString strEmpNo, CString strEmpName, CString strEmpSex, int nEmpAge);
BOOL DeleteOneEmpInfo(CString strEmpNo);
};
----------------------------------
MFCconnectDlg.h
public:
void InitListHeader(); // 初始化列表表头
BOOL QueryAllSpecialEmpInfo(CString strEmpNo, CString strEmpName); // 条件查询