【注意】
VS2019取消了之前老版本的SQL数据库向导。因此需要像VS2019以前版本一样操作数据库,就要单独写一个继承于CRecordset的类来完成向数据库添加数据、删除数据、更新数据、数据排序、数据过滤等操作。
【继承类CDataBaseRec.h】
#pragma once
#include<afxdb.h>
class CDataBaseRec : public CRecordset
{
public:
CDataBaseRec(CDatabase* pDatabase = NULL);
CString mb_Name;
CString mb_PassWord;
CString mb_UserType;
public:
virtual CString GetDefaultConnect();
virtual CString GetDefaultSQL();
virtual void DoFieldExchange(CFieldExchange* pFX);
};
【继承类CDataBaseRec.cpp】
#include "pch.h"
#include "CDataBaseRec.h"
CDataBaseRec::CDataBaseRec(CDatabase* pdb) :CRecordset(pdb)
{
mb_Name = L"";
mb_PassWord = L"";
mb_UserType = L"";
m_nFields = 3;
m_nDefaultType = dynaset;
}
CString CDataBaseRec::GetDefaultConnect()
{
return _T("ODBC;DSN=记录集;UID=账户;PWD=密码");//根据自己数据库连接属性填写
}
CString CDataBaseRec::GetDefaultSQL()
{
return _T("[Student]");
}
void CDataBaseRec::DoFieldExchange(CFieldExchange* pFX)
{
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX, _T("[UserName]"), mb_Name);
RFX_Text(pFX, _T("[UserPassWord]"), mb_PassWord);
RFX_Text(pFX, _T("[UserType]"), mb_UserType);
}
【应用程序H文件】
#include"CDataBaseRec.h"
#include<afxdb.h>
#pragma once
class CMFCApplicationSqlDemoDlg : public CDialogEx
{
public:
CMFCApplicationSqlDemoDlg(CWnd* pParent = nullptr);
#ifdef AFX_DESIGN_TIME
enum { IDD = IDD_MFCAPPLICATIONSQLDEMO_DIALOG };
#endif
protected:
virtual void DoDataExchange(CDataExchange* pDX);
public:
CDatabase CLogo;
CDataBaseRec *CUserTable;
protected:
HICON m_hIcon;
virtual BOOL OnInitDialog();
afx_msg void OnSysCommand(UINT nID, LPARAM lParam);
afx_msg void OnPaint();
afx_msg HCURSOR OnQueryDragIcon();
DECLARE_MESSAGE_MAP()
public:
CString m_User;
CString m_PassWord;
afx_msg void OnBnClickedButton1();
CString m_UserType;
afx_msg void OnBnClickedButton2();
afx_msg void OnBnClickedButton4();
afx_msg void OnBnClickedButton3();
afx_msg void OnBnClickedButton5();
afx_msg void OnBnClickedButton6();
afx_msg void OnBnClickedButton7();
afx_msg void OnBnClickedButton8();
afx_msg void OnBnClickedButton9();
};
【应用程序CPP文件】
#include "pch.h"
#include "framework.h"
#include "MFCApplicationSqlDemo.h"
#include "MFCApplicationSqlDemoDlg.h"
#include "afxdialogex.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#endif
class CAboutDlg : public CDialogEx
{
public:
CAboutDlg();
#ifdef AFX_DESIGN_TIME
enum { IDD = IDD_ABOUTBOX };
#endif
protected:
virtual void DoDataExchange(CDataExchange* pDX);
protected:
DECLARE_MESSAGE_MAP()
};
CAboutDlg::CAboutDlg() : CDialogEx(IDD_ABOUTBOX)
{
}
void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
CDialogEx::DoDataExchange(pDX);
}
BEGIN_MESSAGE_MAP(CAboutDlg, CDialogEx)
END_MESSAGE_MAP()
CMFCApplicationSqlDemoDlg::CMFCApplicationSqlDemoDlg(CWnd* pParent /*=nullptr*/)
: CDialogEx(IDD_MFCAPPLICATIONSQLDEMO_DIALOG, pParent)
, m_User(_T(""))
, m_PassWord(_T(""))
, m_UserType(_T(""))
{
m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
CUserTable = nullptr;
}
void CMFCApplicationSqlDemoDlg::DoDataExchange(CDataExchange* pDX)
{
CDialogEx::DoDataExchange(pDX);
DDX_Text(pDX, IDC_EDIT1, m_User);
DDX_Text(pDX, IDC_EDIT2, m_PassWord);
DDX_Text(pDX, IDC_EDIT3, m_UserType);
}
BEGIN_MESSAGE_MAP(CMFCApplicationSqlDemoDlg, CDialogEx)
ON_WM_SYSCOMMAND()
ON_WM_PAINT()
ON_WM_QUERYDRAGICON()
ON_BN_CLICKED(IDC_BUTTON1, &CMFCApplicationSqlDemoDlg::OnBnClickedButton1)
ON_BN_CLICKED(IDC_BUTTON2, &CMFCApplicationSqlDemoDlg::OnBnClickedButton2)
ON_BN_CLICKED(IDC_BUTTON4, &CMFCApplicationSqlDemoDlg::OnBnClickedButton4)
ON_BN_CLICKED(IDC_BUTTON3, &CMFCApplicationSqlDemoDlg::OnBnClickedButton3)
ON_BN_CLICKED(IDC_BUTTON5, &CMFCApplicationSqlDemoDlg::OnBnClickedButton5)
ON_BN_CLICKED(IDC_BUTTON6, &CMFCApplicationSqlDemoDlg::OnBnClickedButton6)
ON_BN_CLICKED(IDC_BUTTON7, &CMFCApplicationSqlDemoDlg::OnBnClickedButton7)
ON_BN_CLICKED(IDC_BUTTON8, &CMFCApplicationSqlDemoDlg::OnBnClickedButton8)
ON_BN_CLICKED(IDC_BUTTON9, &CMFCApplicationSqlDemoDlg::OnBnClickedButton9)
END_MESSAGE_MAP()
BOOL CMFCApplicationSqlDemoDlg::OnInitDialog()
{
CDialogEx::OnInitDialog();
ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
ASSERT(IDM_ABOUTBOX < 0xF000);
CMenu* pSysMenu = GetSystemMenu(FALSE);
if (pSysMenu != nullptr)
{
BOOL bNameValid;
CString strAboutMenu;
bNameValid = strAboutMenu.LoadString(IDS_ABOUTBOX);
ASSERT(bNameValid);
if (!strAboutMenu.IsEmpty())
{
pSysMenu->AppendMenu(MF_SEPARATOR);
pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
}
}
SetIcon(m_hIcon, TRUE);
SetIcon(m_hIcon, FALSE);
ShowWindow(SW_SHOWMAXIMIZED);
/*if (!CLogo.Open(_T("LogOn"), FALSE, FALSE, L"ODBC;"))*/
if (!CLogo.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=LogON;UID=用户名;PWD=密码")))
{
MessageBox(_T("数据库连接失败!"),_T("信息提示:"),MB_OK|MB_OKCANCEL|MB_ICONERROR);
}
else
{
MessageBox(_T("数据库连接成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL |MB_ICONINFORMATION);
CUserTable = new CDataBaseRec(&CLogo);
CUserTable->Open(CRecordset::dynaset, _T("SELECT UserName,UserPassWord ,UserType FROM CUserDataBase"));
if (!CUserTable->IsOpen())
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
else
{
MessageBox(_T("数据集打开成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
CUserTable->MoveFirst();
m_User = CUserTable->mb_Name;
m_PassWord = CUserTable->mb_PassWord;
m_UserType = CUserTable->mb_UserType;
UpdateData(FALSE);
}
}
return TRUE;
}
void CMFCApplicationSqlDemoDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
if ((nID & 0xFFF0) == IDM_ABOUTBOX)
{
CAboutDlg dlgAbout;
dlgAbout.DoModal();
}
else
{
CDialogEx::OnSysCommand(nID, lParam);
}
}
void CMFCApplicationSqlDemoDlg::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
{
CDialogEx::OnPaint();
}
}
HCURSOR CMFCApplicationSqlDemoDlg::OnQueryDragIcon()
{
return static_cast<HCURSOR>(m_hIcon);
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton1()
{
if (CUserTable->IsOpen())
{
CUserTable->MoveNext();
if (CUserTable->IsEOF())
{
CUserTable->MoveFirst();
}
m_User = CUserTable->mb_Name;
m_PassWord = CUserTable->mb_PassWord;
m_UserType = CUserTable->mb_UserType;
UpdateData(FALSE);
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton2()
{
if (CUserTable->IsOpen())
{
CUserTable->MovePrev();
if (CUserTable->IsBOF())
{
CUserTable->MoveLast();
}
m_User = CUserTable->mb_Name;
m_PassWord = CUserTable->mb_PassWord;
m_UserType = CUserTable->mb_UserType;
UpdateData(FALSE);
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton4()
{
if (CUserTable->IsOpen())
{
if (!CUserTable->IsEOF())
{
CUserTable->MoveLast();
CUserTable->AddNew();
CUserTable->SetFieldNull(NULL);
UpdateData(TRUE);
CUserTable->mb_Name = m_User;
CUserTable->mb_PassWord = m_PassWord;
CUserTable->mb_UserType = m_UserType;
if (CUserTable->CanUpdate())
{
CUserTable->Update();
UpdateData(FALSE);
MessageBox(_T("数据记录添加成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
}
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton3()
{
if (CUserTable->IsOpen())
{
if (!CUserTable->IsEOF())
CUserTable->Edit();
{
UpdateData(TRUE);
CUserTable->mb_Name = m_User;
CUserTable->mb_PassWord = m_PassWord;
CUserTable->mb_UserType = m_UserType;
if (CUserTable->CanUpdate())
{
CUserTable->Update();
UpdateData(FALSE);
MessageBox(_T("数据记录修改成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
}
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton5()
{
if (!CUserTable->IsEOF())
{
CUserTable->Delete();
CUserTable->Requery();
UpdateData(FALSE);
MessageBox(_T("数据记录删除成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton6()
{
if (!CUserTable->IsEOF())
{
CUserTable->m_strFilter.Format(_T("UserType!=%s"),_T("5"));
CUserTable->Requery();
UpdateData(FALSE);
MessageBox(_T("数据记录过滤成功!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton7()
{
if (!CUserTable->IsEOF())
{
CUserTable->m_strFilter = _T("");
CUserTable->Requery();
UpdateData(FALSE);
MessageBox(_T("数据记录过滤已取消!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
else
{
MessageBox(_T("数据集打开失败!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONERROR);
}
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton8()
{
CUserTable->m_strSort = L"UserName";
CUserTable->Requery();
UpdateData(FALSE);
MessageBox(_T("数据记录根据UserType升序排续完成!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}
void CMFCApplicationSqlDemoDlg::OnBnClickedButton9()
{
CUserTable->m_strSort = L"UserName desc";
CUserTable->Requery();
UpdateData(FALSE);
MessageBox(_T("数据记录根据UserType降序排续完成!"), _T("信息提示:"), MB_OK | MB_OKCANCEL | MB_ICONINFORMATION);
}