C++中SQLite多行数据一次性写入

这里以“学生成绩表”为例，创建表SQL语句如下：

void CStudentSqlite::InitDB()
{
	try
	{
		if (!tableExists(_T("学生成绩表")))
		{
			execDML(TEXT("CREATE TABLE 学生成绩表 ( \
					ID           CHAR( 50 )      PRIMARY KEY,\
					Name		 CHAR( 50 ),\
					Grade		 integer\
					);"));
		}
	}
	catch (CppSQLite3Exception &e)
	{
		TRACE("CStudentSqlite::InitDB %s", e.errorMessage());
	}
}

其中写入一行的代码如下

// 写入一行
bool CStudentSqlite::AddRecordToTableGrade(const STUDENT_GRADE_INFO& pStuGradeInfo)
{
	try
	{
		CString strSQL = _T("");
		strSQL.Format(_T("INSERT INTO 学生成绩表 VALUES('%s', '%s', %d);"), pStuGradeInfo.ID, pStuGradeInfo.Name, pStuGradeInfo.Grade);
		execDML(strSQL);
		return true;
	}
	catch (CppSQLite3Exception &e)
	{
		TRACE("CStudentSqlite::AddRecordToTableGrade %s", e.errorMessage());
	}
	return false;
}

// 学生成绩表信息
typedef struct STUDENT_GRADE_INFO
{
	CString ID;
	CString Name;
	int Grade;
}STUDENT_GRADE_INFO;
typedef std::vector<STUDENT_GRADE_INFO> StudentGradeInfoVec;

多行一次性写入：

// 写入多行
bool CStudentSqlite::AddRecordsToTableGrade(const StudentGradeInfoVec& vStudentGradeInfo)
{
	try
	{
		if (vStudentGradeInfo.size() > 0)
		{
			if (vStudentGradeInfo.size() == 1)
			{
				return AddRecordToTableGrade(vStudentGradeInfo[0]);
			}
			else
			{
				CString strSQL = _T("");
				strSQL.Format(_T("INSERT INTO 学生成绩表(ID,Name,Grade) SELECT '%s' AS 'ID', '%s' AS 'Name', '%d' AS 'Grade' "), vStudentGradeInfo[0].ID, vStudentGradeInfo[0].Name, vStudentGradeInfo[0].Grade);
				CString sTemp = _T("");
				for (unsigned int i = 1; i < vStudentGradeInfo.size(); ++i)
				{
					sTemp.Format(_T("union select '%s','%s','%d' "), vStudentGradeInfo[i].ID, vStudentGradeInfo[i].Name, vStudentGradeInfo[i].Grade);
					strSQL += sTemp;
				}
				execDML(strSQL);
				return true;
			}
		}
	}
	catch (CppSQLite3Exception &e)
	{
		TRACE("CStudentSqlite::AddRecordToTableGrade %s", e.errorMessage());
	}
	return false;
}

调用：

StudentGradeInfoVec vecGradeInfo;
for (int i = 0; i < iCount; ++i)
{
	STUDENT_GRADE_INFO StuGradeInfo;
	StuGradeInfo.ID.Format(_T("%07d"), i + 1);
	StuGradeInfo.Name.Format(_T("学生%d"), i + 1);
	StuGradeInfo.Grade = rand() % 100;
	vecGradeInfo.push_back(StuGradeInfo);
}
BbStudent.AddRecordsToTableGrade(vecGradeInfo);