1. Purpose of this paper

MFC operates Sql Server 2012 through ADO. The main operations include: connection and disconnection of database, query and insertion of data.

2. Operation process

2.1 Configuration header file

Add the following code to the stdafx.h file:

#import "C://Program Files//Common Files//System//ado//msado15.dll" no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")

2.2 Database Connection

(a) Define a database operation class: class AdoSql;

b) Define member variables:

	_ConnectionPtr  m_pConnection;		//Connection object
	_RecordsetPtr  m_pRecordSet;		//Recordset object

c) Define the connection database function:

bool AdoSql::InitialConn()
{
	::CoInitialize(NULL);
	HRESULT hr = NULL;

	try
	{
		hr = m_pConnection.CreateInstance(_uuidof(Connection));//Create connection object instances;	
		
		if (!SUCCEEDED(hr))
		{
			OutToLog(_T("CreateInstance failed"));
			return false;
		}
        //uid represents the login name of the login database, pwd is the login password, and server represents the ip and port number of the stomach database. Database is the database name
		_bstr_t strConnect = "Driver={sql server};server=127.0.0.1,1433;uid=sa;pwd=12345678;database=lab_changsha;";
		hr = m_pConnection->Open(strConnect, "", "", adModeUnknown);
		if (!SUCCEEDED(hr)) {
			AfxMessageBox(_T("Failure to connect database"));
			return false;
		}
	}
	catch (_com_error e)
	{
		CString strError;
		strError.Format(_T("Failure to connect database\n %s:%s %d"), e.ErrorMessage(), (LPCTSTR)e.Description());
		AfxMessageBox(strError);
		return false;
	}
	return true;
}

2.3 Reading Table Data in Database

typedef struct CommunicationInfo
{
	CString ip;
	INT		port;
}COMMUNICATION;

//url is sql select traversal statement
bool AdoSql::getInfoCom(CString url, COMMUNICATION& communication_info)
{
	try {
		m_pRecordSet.CreateInstance(_uuidof(Recordset));		//Create a Recordset
		_variant_t vAffected;
//		CString url1 = _T("select equip_ip, equip_port from dbo._0_5_temperature_impact_box");
		m_pRecordSet->Open((_variant_t)url, m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
		_variant_t varian_ip, variant_port;
		while (!m_pRecordSet->adoEOF)
		{
			communication_info.ip = m_pRecordSet->GetCollect("equip_ip");
			communication_info.port = (INT)m_pRecordSet->GetCollect("equip_port");
			m_pRecordSet->MoveNext();
		}
		m_pRecordSet->Close();
	}
	catch (_com_error e)
	{
		CString strError;
		strError.Format(_T("Failed to read database data\n %s:%s %d"), e.ErrorMessage(), (LPCTSTR)e.Description());
		OutToLog(strError);
        m_pRecordSet->Close();
		return false;
	}
	return true;
}

2.4 Insert Data

/*
//The string format for inserting data is similar to: CString strSql = T ("INSERT family. dbo. Info VALUES (6,  Xiaofan ,  1997-01-13 ,  Zongyang \,  cloth \)");
char[]Escape with, and the string is represented by''
*/
bool AdoSql::writeDatabase(CString url)
{
	try 
	{	
		m_pConnection->Execute(_bstr_t(url), NULL, adCmdText);		
	}
	catch (_com_error e)
	{
		CString strError;
		strError.Format(_T("Failed to modify database data\n %s:%s %d"), e.ErrorMessage(), (LPCTSTR)e.Description());
		OutToLog(strError);
		return false;
	}
	return true;
}

2.5 Disconnect the database connection

void AdoSql::ExitConn()
{
	m_pConnection->Close();
	return; 
}