/// <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); } }