MFC ODBC Student Program Management System Example

Catalog

abstract

Emphasis of experiment

Program Flowchart

Database preparation

Local SQL Server table building

Remote MySQL Table Building

Interface Development

Login Dialog

1. Add icons to the dialog

2. Automatically focus to the first edit box when the form opens

3. Specific code implementation for login verification

4. Function implementation of pop-up in front of main form

Summary

primary dialog

Student Management Window

Initialization of Controls

List Control retrieves data from the database and displays it

Click on a list item to automatically populate the right edit box

Code implementation for inserting data

Code implementation for deleting data

Code implementation for modifying data

Code implementation for sorting by birthday

Code implementation of search

Empty the edit box and display all

summary

Complete project

abstract

This article is a demonstration of my "C++ Practice" course in the MFC student information management system example, basic tutorials, in detail shows how to develop a basic function of the MFC student management system step by step.

Emphasis of experiment

1. Simple use of local ODBC database connectors

2. Use of MFC Foundation Controls

Program Flowchart

 

Database preparation

Three tables are used in this example: the Student Table (Stu), the Course, and the Score.

This shows the table building process in both MySQL and SQL Server databases

Local SQL Server table building

Open SSMS, create a new query, create a new database SMSDB for this experiment

 

           

CREATE DATABASE SMSDB
use SMSDB
/*
New and Use Data SMSDB
*/

Create three new tables and note that there are several constraints and primary foreign key assignments

CREATE TABLE Stu(
School Number NCHAR(10) PRIMARY KEY NOT NULL,
Full name NCHAR(5),
major NCHAR(20) NOT NULL,
Gender NCHAR(2) default 'male' check (Gender in ('male','female'))NOT NULL,
Date of birth DATE NOT NULL
);

CREATE TABLE Course(
Course Number NCHAR(10) PRIMARY KEY NOT NULL,
Course Name NCHAR(20) NOT NULL,
credit SMALLINT NOT NULL
);

CREATE TABLE Score(
School Number NCHAR(10) FOREIGN KEY REFERENCES Stu(School Number) NOT NULL,
Course Number NCHAR(10) FOREIGN KEY REFERENCES Course(Course Number) NOT NULL,
achievement SMALLINT CHECK(achievement>=0 AND achievement<=100) NOT NULL
);

/*
New Three Tables
*/

Insert the original sample data (two for each table)

INSERT INTO Stu VALUES(312021001,'Zhang San','Computer Science and Technology','male','2000-1-12')
INSERT INTO Stu VALUES(312020001,'aaa','Building Environment and Energy Applications','female','1999-4-26')

INSERT INTO Course VALUES('9001','Data Structure and Algorithms',2.0);
INSERT INTO Course VALUES('9002','linear algebra',1.0);


INSERT INTO Score VALUES(312021001,9001,87);
INSERT INTO Score VALUES(312020001,9002,58);

Check raw data

SELECT Stu.School Number,Full name,Gender,Date of birth,major,Course.Course Number,Course Name,credit,achievement FROM Stu,Course,Score
WHERE Stu.School Number=Score.School Number AND Course.Course Number=Score.Course Number

Result

 

Remote MySQL Table Building

1. Putty logs on to the server remotely and starts MySQL

2. Add a remote login user to MySQL and authorize it (there are actually some steps left out here)

3. New database and corresponding tables

4. Insert raw data and check

(Actually, using cmd directly is also possible. The new Terminal version is also beautiful.)

  As for the local MySQL table building, there are also SQL statements above. It is not difficult to implement them, just skip them

Interface Development

This project is a dialog-based MFC project, which contains five main dialog boxes: main dialog box, login dialog box (LoginDlg), Student Management dialog box (StuDlg), CourseDlg, and ScoreDlg.

Login Dialog

  The password edit box is a password property

It is worth mentioning that:  

1. Add icons to the dialog

Define a variable in the header file first

HICON m_hIcon;

Then add in the constructor of the form's corresponding cpp file (IDI_ICON2 is the added ICON resource file ID)

m_hIcon = AfxGetApp()->LoadIcon(IDI_ICON2);

Finally, add it to the OnInitDlg() function of the form (no function can add this virtual function through the class wizard)

	SetIcon(m_hIcon, TRUE);			// Set Large Icon
	SetIcon(m_hIcon, FALSE);		// Set Small Icon

2. Automatically focus to the first edit box when the form opens

	// GetDlgItem(USERNAME)->SetFocus();
	// Don't know why it's useless
	// Ha Ha Ha Ha Ha Ha, ctrl+D set tab order 1 to get focus, it's simply rude

Setting tad order here also makes it easy for users to switch their editing habits using the Tab key

3. Specific code implementation for login verification

Message Response Function of Login Button

void Login::OnBnClickedOk()
{
	// Verify the account password entered by the user
	GetDlgItemText(USERNAME, m_username);
	GetDlgItemText(PASSWORD, m_password);
	if (m_username==L"" || m_password==L"")
	{
		MessageBox(L"User name or password cannot be empty!",L"Be careful");
		GetDlgItem(USERNAME)->SetFocus();
	}else if (m_username.Compare(username)!=0 || m_password.Compare(password)!=0)
	{
		MessageBox(L"Error in username or password, please re-enter!", L"Be careful");
		SetDlgItemText(USERNAME, L"");
		SetDlgItemText(PASSWORD, L"");
		GetDlgItem(USERNAME)->SetFocus();
	}
	else if (m_username.Compare(username) == 0 || m_password.Compare(password) == 0)
	{
		CDialogEx::OnOK();
	}
}

Message Response Function for Cancel Button

void Login::OnBnClickedCancel()
{
	exit(0);
	// CDialogEx::OnCancel();
}

In fact, this sentence is very important, clicking on the "red cross" will also call the OnCancel() function, ensuring that the user either "quit" or "red cross" will end the program directly, ensuring that the program only validates through one entry

4. Function implementation of pop-up in front of main form

	Login login;
	login.DoModal();

A modal dialog box pops up at the front of the initialization function in the main dialog box

Summary

In this case, the correct "user name" and "password" are all written in the "header file". In fact, there are many other extensible points in this form, such as drawing the background of the window, selecting the login type, remembering the account password, and verifying the account password information from the database.

primary dialog

The top left three buttons are the entries of three sub-dialogs, and the implementation is clicking to create a modal dialog

  It is worth noting that the data in the List Control control is a collection of three previously established tables, which are shown here only, while the three sub-modules do not implement operations on each table at all.

The code implementation of this window is left behind for the student management module

Student Management Window

The Student Management window is the most important form in the program (subjects and performance management are not actually written)

The list on the right is List Control, with two Radio Button s by gender, Date Time Picker by birth and Combo Box by profession.

Next, explain the implementation in turn

Initialization of Controls

Write next in the form's initialization function OnInitDlg

	Profess.AddString(L"Computer Science and Technology");
	Profess.AddString(L"Road and Bridge Engineering");
	Profess.AddString(L"Culture and News Communication");
	Profess.AddString(L"Logistics and Transportation");
	Profess.AddString(L"Theoretical physics");
	// Profess is a control variable bound to a professional drop-down box. The code above adds a field to the drop-down box

	stu_list.SetExtendedStyle(LVS_EX_CHECKBOXES);
	// stu_list is a list-bound control variable, and this sentence sets the list style: the check box with a tick at the top

	stu_list.InsertColumn(0, _T("School Number"), 0, 125);
	stu_list.InsertColumn(1, _T("Full name"), 0, 80);
	stu_list.InsertColumn(2, _T("major"), 0, 240);
	stu_list.InsertColumn(3, _T("Gender"), 0, 50);
	stu_list.InsertColumn(4, _T("Date of birth"), 0, 120);
	// Insert Header for List

Screenshot of the effect (actually at this point there should be no data in the list)

 

List Control retrieves data from the database and displays it

Entertainment, ODBC Operations Database

1. Configure the data source first

Win+S searches for ODBC data sources (if not installed, do not expand here, you can refer to other blogs)

  Add Data Source (MySQL example)

 

 

  The fields that need to be filled in from top to bottom are: data source name (optional), database host IP address (local 127.0.0.1), port (MySQL is 3306), MySQL user name, MySQL user password, specified database

When you're done, you can click the Test button to test it

 

  This counts as a successful configuration

2. Define in header file

	CDatabase m_db;

Initialize the database connection in the initialization function (here is the statement for remote connection to MySQL, the comment for local connection to SQL Server is omitted)

	try
	{
		// m_db.Open(NULL);// Pop up the data source selection dialog box
		// m_db.Open(NULL, FALSE, FALSE, _T("DSN=SQL_Server_ODBC;UID=sa;PWD=Ue!p41SQL;DATABASE=SMSDB"));
        // Local connection to SQL Server database
		// Or the ODBC data source

		m_db.Open(NULL, FALSE, FALSE, _T("DSN=MySQL_ODBC;UID=remote;PWD=MySQL.123;DATABASE=SMSDB"));
		// Remote connection to MySQL database
	}
	catch (const CDBException& e)
	{
		MessageBox(e.m_strError);
	}

	InitList();// Individually written function to refresh list data

Parse the SQL connection fields: data source name, database user name, password, database specified for use

3. Get the queried data from the database and insert it into the List

// Refresh List Data
void StuManageDlg::InitList() {

	stu_list.DeleteAllItems();// Empty old data on list control

	// Dataset object, incoming connection object
	CRecordset rs(&m_db);
	BOOL  ret = rs.Open(AFX_DB_USE_DEFAULT_TYPE, _T("SELECT * FROM Stu"));
	int row = 0;

	CString id, name, sex, birth, profess;
	while (!rs.IsEOF())
	{
		//Getting field data in a dataset
		rs.GetFieldValue((short)0, id);
		rs.GetFieldValue((short)1, name);
		rs.GetFieldValue((short)2, sex);
		rs.GetFieldValue((short)3, birth);
		rs.GetFieldValue((short)4, profess);

		//Insert a row into a list control
		stu_list.InsertItem(row, id);
		stu_list.SetItemText(row, 1, name);
		stu_list.SetItemText(row, 2, sex);
		stu_list.SetItemText(row, 3, birth);
		stu_list.SetItemText(row, 4, profess);

		//Move next row of data
		rs.MoveNext();
		row++;
	}

	//Close Recordset
	rs.Close();
}

Click on a list item to automatically populate the right edit box

That might not be intuitive, so plug in a demo video.

  Use the Class Wizard to generate a message response function (list is clicked), then edit the code

// Check Insert Record into Edit Box
void StuManageDlg::OnClickList1(NMHDR* pNMHDR, LRESULT* pResult)
{
	LPNMITEMACTIVATE pNMItemActivate = reinterpret_cast<LPNMITEMACTIVATE>(pNMHDR);

	if (-1 != pNMItemActivate->iItem)        // If iItem is not -1, a list item is selected   
	{
		// Assign the selected information to the selected variable (shown in the corresponding input box)
		stu_id = stu_list.GetItemText(pNMItemActivate->iItem, 0);
		stu_name = stu_list.GetItemText(pNMItemActivate->iItem, 1);
		profess = stu_list.GetItemText(pNMItemActivate->iItem, 2);
		CString stu_sex = stu_list.GetItemText(pNMItemActivate->iItem, 3);
		CString stu_bir = stu_list.GetItemText(pNMItemActivate->iItem, 4);

		// Empty the selected state of the button, and then decide to re-select it
		((CButton*)GetDlgItem(MALE))->SetCheck(0);
		((CButton*)GetDlgItem(FEMALE))->SetCheck(0);
		if (stu_sex.Compare(L"male")==0)
		{
			((CButton*)GetDlgItem(MALE))->SetCheck(1);
			OnBnClickedMale();
		}
		else if (stu_sex.Compare(L"female") == 0)
		{
			((CButton*)GetDlgItem(FEMALE))->SetCheck(1);
			OnBnClickedFemale();
		}

		//Parse the birthday string and then initialize the control
		int iIndex = stu_bir.Find('-');

		int iReverseIndex = stu_bir.ReverseFind('-');

		CString strYear = stu_bir.Left(iIndex);
		CString strDay = stu_bir.Right(stu_bir.GetLength() - iReverseIndex - 1);
		CString strMonth = stu_bir.Mid(iIndex + 1, (iReverseIndex - iIndex - 1));

		COleDateTime time(_ttoi(strYear), _ttoi(strMonth), _ttoi(strDay), 0, 0, 0);
		Date.SetTime(time);//    CDateTimeCtrl m_DateCtrl;

		// Refresh variable values to controls
		UpdateData(false);
	}

	*pResult = 0;
}

Fill in two gender button clicked events

void StuManageDlg::OnBnClickedMale()
{
	stu_sex = L"male";
}

void StuManageDlg::OnBnClickedFemale()
{
	stu_sex = L"female";
}

Code implementation for inserting data

// insert data
void StuManageDlg::OnBnClickedInsert()
{
	Profess.GetWindowTextW(profess);
	CTime t;
	Date.GetTime(t);
	stu_bir = t.Format("%Y-%m-%d");
	/*int nIndex = Profess.GetCurSel();
	Profess.GetLBText(nIndex, profess);*/

	//Synchronize control values to variables
	UpdateData(true);

	if (stu_id.IsEmpty() || stu_name.IsEmpty() || stu_sex.IsEmpty()) {
		MessageBox(_T("Inserted data cannot be empty!"));
		return;
	}

	try {
		CString sql;
		sql.Format(_T("INSERT INTO Stu VALUES(%s,'%s','%s','%s','%s')"), stu_id, stu_name, profess,stu_sex,stu_bir);
		m_db.ExecuteSQL(sql);

		//Refresh Query
		InitList();

		MessageBox(_T("Insert data successful!"));

	}
	catch (CDBException* e) {
		MessageBox(e->m_strError);
	}
}

Code implementation for deleting data

// Delete Information
void StuManageDlg::OnBnClickedDeleteb()
{
	//Synchronize control values to variables
	UpdateData(true);

	if (stu_id.IsEmpty()) {
		MessageBox(_T("Number can't be empty!"));
		return;
	}

	try {
		CString sql;
		sql.Format(_T("DELETE FROM Stu WHERE School Number=%s"), stu_id);
		m_db.ExecuteSQL(sql);

		//Refresh Query
		InitList();

		MessageBox(_T("Delete data successful!"));

	}
	catch (CDBException* e) {
		MessageBox(e->m_strError);
	}

	OnBnClickedClear();
}

Code implementation for modifying data

// Modify Information
void StuManageDlg::OnBnClickedUpdate()
{
	Profess.GetWindowTextW(profess);
	CTime t;
	Date.GetTime(t);
	stu_bir = t.Format("%Y-%m-%d");
	CString new_id;
	GetDlgItemTextW(ID, new_id);
	GetDlgItemTextW(NAME, stu_name);
	//Synchronize control values to variables
	UpdateData(true);

	if (stu_id.IsEmpty() || stu_name.IsEmpty() || profess.IsEmpty() || stu_sex.IsEmpty() || stu_bir.IsEmpty()) {
		MessageBox(_T("Modified data cannot be empty!"));
		return;
	}

	if (stu_id != new_id)
	{
		MessageBox(_T("The number cannot be changed!"));
		return;
	}

	try {
		CString sql;
		sql.Format(_T("UPDATE Stu SET Full name='%s',major='%s',Gender='%s' ,Date of birth='%s' WHERE School Number=%s "), stu_name, profess, stu_sex,stu_bir,stu_id);
		m_db.ExecuteSQL(sql);

		//Refresh Query
		InitList();

		MessageBox(_T("Successful data modification!"));

	}
	catch (CDBException* e) {
		MessageBox(e->m_strError);
	}
}

Code implementation for sorting by birthday

// sort
void StuManageDlg::OnBnClickedSort()
{
	stu_list.DeleteAllItems();// Empty old data on list control

// Dataset object, incoming connection object
	CRecordset rs(&m_db);
	BOOL  ret = rs.Open(AFX_DB_USE_DEFAULT_TYPE, _T("SELECT * FROM Stu ORDER by Date of birth"));
	int row = 0;

	CString id, name, sex, birth, profess;
	while (!rs.IsEOF())
	{
		//Getting field data in a dataset
		rs.GetFieldValue((short)0, id);
		rs.GetFieldValue((short)1, name);
		rs.GetFieldValue((short)2, sex);
		rs.GetFieldValue((short)3, birth);
		rs.GetFieldValue((short)4, profess);

		//Insert a row into a list control
		stu_list.InsertItem(row, id);
		stu_list.SetItemText(row, 1, name);
		stu_list.SetItemText(row, 2, sex);
		stu_list.SetItemText(row, 3, birth);
		stu_list.SetItemText(row, 4, profess);

		//Move next row of data
		rs.MoveNext();
		row++;
	}

	//Close Recordset
	rs.Close();
}

Code implementation of search

// search
void StuManageDlg::OnBnClickedSearch()
{
	GetDlgItemText(SearchInput, search_input);
	if (search_input == L"")
	{
		MessageBox(L"Search item cannot be empty!",L"Tips");
		GetDlgItem(SearchInput)->SetFocus();
	}
	else
	{
		stu_list.DeleteAllItems();// Empty old data on list control
		CString sql = L"SELECT * FROM Stu WHERE School Number LIKE '" + search_input + L"'";
		// Dataset object, incoming connection object
		CRecordset rs(&m_db);
		BOOL  ret = rs.Open(AFX_DB_USE_DEFAULT_TYPE, sql);
		int row = 0;

		CString id, name, sex, birth, profess;
		while (!rs.IsEOF())
		{
			//Getting field data in a dataset
			rs.GetFieldValue((short)0, id);
			rs.GetFieldValue((short)1, name);
			rs.GetFieldValue((short)2, sex);
			rs.GetFieldValue((short)3, birth);
			rs.GetFieldValue((short)4, profess);

			//Insert a row into a list control
			stu_list.InsertItem(row, id);
			stu_list.SetItemText(row, 1, name);
			stu_list.SetItemText(row, 2, sex);
			stu_list.SetItemText(row, 3, birth);
			stu_list.SetItemText(row, 4, profess);

			//Move next row of data
			rs.MoveNext();
			row++;
		}

		//Close Recordset
		rs.Close();

		// MessageBox(sql);

Sorting and searching are very simple and simple here

Empty the edit box and display all

// Empty Edit Box
void StuManageDlg::OnBnClickedClear()
{
	GetDlgItem(ID)->SetWindowText(L"");
	CWnd::SetDlgItemTextW(NAME, L"");
	Profess.SetWindowTextW(L"");

	((CButton*)GetDlgItem(MALE))->SetCheck(0);
	((CButton*)GetDlgItem(FEMALE))->SetCheck(0);

	COleDateTime Now = COleDateTime::GetCurrentTime();
	Date.SetTime(Now);
}
void StuManageDlg::OnBnClickedShow()
{
	InitList();
}

summary

Of course, this project is still very simple and basic, even fails to reflect the essence of MFC, but it is also a learner, record precipitation also provides a reference for future students.

If you have any opinions, discussions or questions, you are welcome to raise them in the comments area.

Complete project

Of course, even if you have written so much in detail, it is necessary to actually run for a better understanding, so here is a complete project folder for interested students to run debugging

Note that the database is inaccessible on a non-native host (local data source configuration) and needs to be configured to work properly

Tags: C++ odbc MFC

Posted on Mon, 06 Dec 2021 12:22:50 -0500 by kidd1270