/// <summary>
/// Import data from .xml file to SQL
/// </summary>
/// <param fullFileName=".xml">fullFileName</param>
public List <ExamClass> ImportFromXmlFileToSQL(string xmlFileName)
{
try
{
XmlFileService xmlFileService = new XmlFileService();
List <ExamClass> modelList = xmlFileService.ImportData(xmlFileName);
using (var connect = new NpgsqlConnection(connectSql))
{
connect.Open();
// 创建DataTable
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable(tableName);
// 设置table各列的字段
DataColumn dc = dataTable.Columns.Add("Id", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dataTable.Columns.Add("ClassNo", Type.GetType("System.String"));
dataTable.Columns.Add("InstituteStudents", Type.GetType("System.String"));
dataTable.Columns.Add("NumberStudents", Type.GetType("System.Int32"));
dataTable.Columns.Add("InstituteProctors", Type.GetType("System.String"));
dataTable.Columns.Add("NumberProctors", Type.GetType("System.Int32"));
// 将列表数据添加到 dataTable 中
foreach (var model in modelList)
{
DataRow row = dataTable.NewRow();
row["Id"] = model.Id;
row["ClassNo"] = model.ClassNo;
row["InstituteStudents"] = model.InstituteStudents;
row["NumberStudents"] = model.NumberStudents;
row["InstituteProctors"] = model.InstituteProctors;
row["NumberProctors"] = model.NumberProctors;
dataTable.Rows.Add(row);
}
dataSet.Tables.Add(dataTable);
/* 查看dataset中的数据 */
//for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
//{
// string one1 = dataSet.Tables[0].Rows[i].ItemArray[0].ToString();
// string one2 = dataSet.Tables[0].Rows[i].ItemArray[1].ToString();
// string one3 = dataSet.Tables[0].Rows[i].ItemArray[2].ToString();
// string one4 = dataSet.Tables[0].Rows[i].ItemArray[3].ToString();
// string one5 = dataSet.Tables[0].Rows[i].ItemArray[4].ToString();
// string one6 = dataSet.Tables[0].Rows[i].ItemArray[5].ToString();
// Console.WriteLine("w:{0},x:{1},y:{2},z:{3},z:{4},z:{5}", one1, one2, one3, one4, one5, one6);
//}
/* 清空table, 否则update操作会出错 */
string deleteSql = string.Format("DELETE FROM {0};", tableName);
using (NpgsqlCommand cmd = new NpgsqlCommand(deleteSql, connect))
{
cmd.ExecuteNonQuery();//执行查询命令
}
/* 上传数据到数据库 */
string selectSql = string.Format("SELECT * FROM {0};", tableName);
using (NpgsqlDataAdapter nadapter = new NpgsqlDataAdapter(selectSql, connect))
{
NpgsqlCommandBuilder commendBuilder = new NpgsqlCommandBuilder(nadapter);
int count = nadapter.Update(dataSet, tableName);
Console.WriteLine("Succeed tot import {0} items from {1} to SQL:{2}.{3}", count, xmlFileName, sqlName, tableName);
return(modelList);
}
}
}
catch
{
Console.WriteLine("Error of importing data from {0} to SQL {1}", xmlFileName, sqlName);
return(null);
}
}
