xml2DB

1.在项目 -> 属性 -> C/C++->预处理器 -> 预处理器定中添加 _CRT_SECURE_NO_WARNINGS

------------------------

绘制界面

list control 添加变量 

----------------------------

pch.h

#import < msxml6.dll >

-----------------------

xml2DBDlg.cpp

#include <io.h>
#import"C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

//OnInitDialog()

	// TODO: 在此添加额外的初始化代码
	AfxOleInit();//初始化COM。

	// 初始化ListCtrl
	DWORD dwStyle = m_ListData.GetExtendedStyle();
	dwStyle |= LVS_EX_FULLROWSELECT;	// 使整行高亮
	dwStyle |= LVS_EX_GRIDLINES;		// 网格线
	m_ListData.SetExtendedStyle(dwStyle);
	// 列标题
	m_ListData.InsertColumn(0, _T("学号"), LVCFMT_LEFT, 120);
	m_ListData.InsertColumn(1, _T("姓名"), LVCFMT_LEFT, 180);
	m_ListData.InsertColumn(2, _T("分数"), LVCFMT_LEFT, 110);

//xml导入数据库
void Cxml2DBDlg::OnBnClickedButton1()
{
	// TODO: 在此添加控件通知处理程序代码
	::CoInitialize(NULL);//必须加初始化，否则异常
	MSXML2::IXMLDOMDocumentPtr pDoc;
	HRESULT hr = pDoc.CreateInstance(__uuidof(MSXML2::DOMDocument60));
	if (!SUCCEEDED(hr))
	{
		MessageBox(_T(" 无法创建DOMDocument 对象，请检查是否安装了MS XML Parser 运行库! "));
		return;
	}
	// 加载文件
	pDoc->put_async(VARIANT_FALSE);
	// 使用绝对路径
	char* strXmlPath = "D:\\考研\\复试\\专业面试\\XML导入到数据库\\test1.XML";
	VARIANT_BOOL bhr = pDoc->load((_variant_t)strXmlPath);
	// load 函数只能用variant_t 类型于是这里用char 去写路径，不然还是CString好用
	if (!bhr)
	{
		MessageBox(_T(" 无法打开XML 文件"));//测试过，基本上都能打开
		return;
	}
	// 根节点取得
	MSXML2::IXMLDOMElementPtr root = pDoc->documentElement;
	// 取得根节点的名字
	//使用nodeName 的字符串用这个strRootName.bstrVal;这里取得名字没用，只是为了演示
	_variant_t strRootName = root->nodeName;

	// 打开数据库
	_ConnectionPtr pConnection;
	try {
		pConnection.CreateInstance("ADODB.Connection");
		_bstr_t strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=grade;Data Source=LAPTOP-2E390ION";
		pConnection->Open(strConnect, "", "", adModeUnknown);
	}
	catch (_com_error& e)
	{
		MessageBox(e.Description(), _T("错误"));
	}

	//下面的这个nodelist 是精髓，得到所有结点的链表
	MSXML2::IXMLDOMNodeListPtr nodeList = root->GetchildNodes();
	int ilength = nodeList->Getlength();//这里的长度函数很好用
	for (int nodeCount = 0; nodeCount < ilength; nodeCount++)
	{
		MSXML2::IXMLDOMNodePtr nodePtr = nodeList->nextNode();
		_variant_t strNodeName = nodePtr->GetnodeName();
		_variant_t strNodeValue = nodePtr->GetfirstChild()->GetnodeValue();

		MSXML2::IXMLDOMNodeListPtr nodeList = nodePtr->GetchildNodes();
		//课程ID
		MSXML2::IXMLDOMNodePtr nodeChildPtr = nodeList->nextNode();
		_variant_t strNodeChildValue = nodeChildPtr->GetfirstChild()->GetnodeValue();
		CString strCourseID = COLE2T(strNodeChildValue.bstrVal);
		//学生ID
		nodeChildPtr = nodeList->nextNode();
		strNodeChildValue = nodeChildPtr->GetfirstChild()->GetnodeValue();
		CString strStudentID = COLE2T(strNodeChildValue.bstrVal);
		//分数
		nodeChildPtr = nodeList->nextNode();
		strNodeChildValue = nodeChildPtr->GetfirstChild()->GetnodeValue();
		CString strScore = COLE2T(strNodeChildValue.bstrVal);

		// 保存到数据库
		CString strSql;
		strSql.Format(_T("INSERT INTO Scores(cid,sid,score) VALUES('%s','%s',%s)"), strCourseID, strStudentID, strScore);
		try
		{
			// 执行Sql语句
			pConnection->Execute((_bstr_t)strSql, NULL, adCmdText);
		}
		catch (_com_error& e)
		{
			MessageBox(_T("导入成绩数据时数据库异常"), _T("提示"), MB_ICONINFORMATION);
			pConnection->Close();
			return;
		}
	}
	pConnection->Close();
	MessageBox(_T("导入成功"), _T("提示"), MB_ICONINFORMATION);
}

//查询
void Cxml2DBDlg::OnBnClickedButton2()
{
	// TODO: 在此添加控件通知处理程序代码

	m_ListData.DeleteAllItems();

	//获取输入的课程名
	CString strCourseName;
	GetDlgItem(IDC_EDIT1)->GetWindowText(strCourseName);
	if (strCourseName.IsEmpty())
	{
		MessageBox(_T("课程名不能为空"), _T("提示"), MB_ICONINFORMATION);
		return;
	}

	// 打开数据库
	_ConnectionPtr pConnection;
	_RecordsetPtr pRecordset;
	try {
		pConnection.CreateInstance("ADODB.Connection");
		_bstr_t strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=grade;Data Source=LAPTOP-2E390ION";
		pConnection->Open(strConnect, "", "", adModeUnknown);
	}
	catch (_com_error& e)
	{
		MessageBox(e.Description(), _T("错误"));
	}

	// 降序查询
	CString strSql;
	strSql = _T("SELECT students.sid as ID, students.sname as Name, scores.score as Score \\
		FROM scores, students, courses WHERE scores.sid = students.sid AND scores.cid = courses.cid AND \\
		courses.cname = '") + strCourseName + _T("' ORDER BY scores.score DESC");

		try
	{
		// 执行Sql
		pRecordset = pConnection->Execute((_bstr_t)strSql, NULL, adCmdText);
	}
	catch (_com_error& e)
	{
		pConnection->Close();
		MessageBox(_T("查询课程信息时数据库错误"), _T("提示"));
		return;
	}
	if (pRecordset->adoEOF == VARIANT_TRUE)
	{	// 没有数据
		pConnection->Close();
		MessageBox(_T("没有查询到数据"), _T("提示"));
		return;
	}

	CString strID;
	CString strName;
	CString strScore;
	int nItem = 0;
	// 获取数据
	try {
		pRecordset->MoveFirst();
		while (pRecordset->adoEOF == VARIANT_FALSE)
		{
			_variant_t value = "";

			// 学号
			value = pRecordset->GetCollect("ID");
			if (value.vt != VT_NULL)
			{
				strID = (char*)(_bstr_t)value;
			}
			else
			{
				strID = _T("");
			}
			// 姓名
			value = pRecordset->GetCollect("Name");
			if (value.vt != VT_NULL)
			{
				strName = (char*)(_bstr_t)value;
			}
			else
			{
				strName = _T("");
			}
			// 分数
			value = pRecordset->GetCollect("Score");
			if (value.vt != VT_NULL)
			{
				strScore = (char*)(_bstr_t)value;
			}
			else
			{
				strScore = _T("");
			}

			// 加入到数据列表
			m_ListData.InsertItem(nItem, strID);
			m_ListData.SetItemText(nItem, 1, strName);
			m_ListData.SetItemText(nItem, 2, strScore);

			// 下一个
			pRecordset->MoveNext();
			nItem++;
		}
	}
	catch (_com_error& e)
	{
		MessageBox(_T("读取成绩信息时数据库异常"), _T("提示"));
		pConnection->Close();
		return;
	}
	// 关闭数据库
	pConnection->Close();
}