VS2019 MFC ODBC 操作SQL SERVER 数据库

【注意】

        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);
}