c datatable导入mysql_c# 把List<T>转成DataTable对象，批量导入Sqlserver库

1 ///

2 ///Sqlbulkcopies the specified SMS.批量插入到数据库3 ///

4 /// list类型数据.

5 /// 数据库连接字符串.

6 private void SqlbulkcopyPipeLines(List data, SqlConnection sqlconn, string prjId, stringmodid)7 {8 #region 待处理数据初始化处理

9 List pList = new List();//创建属性的集合

10 DataTable dtLoad = newDataTable();11 //把所有的public属性加入到集合 并添加DataTable的列12 //Array.ForEach(typeof(CJunc).GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });//获得反射的入口(typeof())//要对 array 的每个元素执行的 System.Action。

13

14

15 dtLoad.Columns.Add("ProjectID", typeof(int));16 dtLoad.Columns.Add("ModelID", typeof(int));17 dtLoad.Columns.Add("ID", typeof(string));18 dtLoad.Columns.Add("Node1", typeof(string));19 dtLoad.Columns.Add("Node2", typeof(string));20 dtLoad.Columns.Add("Length", typeof(decimal));21 dtLoad.Columns.Add("Diameter", typeof(decimal));22 dtLoad.Columns.Add("Roughness", typeof(decimal));23 dtLoad.Columns.Add("MinorLoss", typeof(string));24 dtLoad.Columns.Add("Status", typeof(string));25 dtLoad.Columns.Add("Comment", typeof(string));26

27

28

29 foreach (var item indata)30 {31 DataRow row = dtLoad.NewRow(); //创建一个DataRow实例32 //pList.ForEach(p => row[p.Name] = p.GetValue(item, null));//给row 赋值33 //[ProjectID] ,[ModelID] ,[ID] ,[Node1] ,[Node2] ,[Length] ,[Diameter] ,[Roughness],[MinorLoss],[Status] ,[Comment]34 //insert_pipesData(db_do, prjId, modid, PipeLines[i].ID, PipeLines[i].Node1, PipeLines[i].Node2, PipeLines[i].Data[CPipe.PIPE_LEN_INDEX], PipeLines[i].Data[CPipe.PIPE_DIAM_INDEX], PipeLines[i].Data[CPipe.PIPE_ROUGH_INDEX], PipeLines[i].Data[CPipe.PIPE_MLOSS_INDEX], PipeLines[i].Data[CPipe.PIPE_STATUS_INDEX], PipeLines[i].Data[CGlobalConst.COMMENT_INDEX]);

35

36 row["ProjectID"] =prjId;37 row["ModelID"] =modid;38 row["ID"] =item.ID;39 row["Node1"] =item.Node1;40 row["Node2"] =item.Node2;41

42 /*

43 if (item.Data[CPipe.PIPE_LEN_INDEX].Trim().Length == 0) { row["Length"] = 0; Console.WriteLine("Length为空:" + item.Data[CPipe.PIPE_LEN_INDEX]); }44 if (IsNumeric(item.Data[CPipe.PIPE_LEN_INDEX])) { row["Length"] = item.Data[CPipe.PIPE_LEN_INDEX]; }45 else { row["Length"] = 0; Console.WriteLine("Length非数字:" + item.Data[CPipe.PIPE_LEN_INDEX]); }46

47

48 if (item.Data[CPipe.PIPE_DIAM_INDEX].Trim().Length == 0) { row["Diameter"] = 0; Console.WriteLine("Diameter为空:" + item.Data[CPipe.PIPE_DIAM_INDEX]); }49 if (IsNumeric(item.Data[CPipe.PIPE_DIAM_INDEX])) { row["Diameter"] = item.Data[CPipe.PIPE_DIAM_INDEX]; }50 else { row["Diameter"] = 0; Console.WriteLine("Diameter非数字:" + item.Data[CPipe.PIPE_DIAM_INDEX]); }51

52 if (item.Data[CPipe.PIPE_ROUGH_INDEX].Trim().Length == 0) { row["Roughness"] = 0; Console.WriteLine("Roughness为空:" + item.Data[CPipe.PIPE_ROUGH_INDEX]); }53 if (IsNumeric(item.Data[CPipe.PIPE_ROUGH_INDEX])) { row["Roughness"] = item.Data[CPipe.PIPE_ROUGH_INDEX]; }54 else { row["Roughness"] = 0; Console.WriteLine("Roughness非数字:" + item.Data[CPipe.PIPE_ROUGH_INDEX]); }55 */

56

57 row["Length"] =item.Data[CPipe.PIPE_LEN_INDEX];58 row["Diameter"] =item.Data[CPipe.PIPE_DIAM_INDEX];59 row["Roughness"] =item.Data[CPipe.PIPE_ROUGH_INDEX];60 row["MinorLoss"] =item.Data[CPipe.PIPE_MLOSS_INDEX];61 row["Status"] =item.Data[CPipe.PIPE_STATUS_INDEX];62 row["Comment"] =item.Data[CGlobalConst.COMMENT_INDEX];63

64 dtLoad.Rows.Add(row); //加入到DataTable

65 }66 #endregion

67 #region 批量插入数据库 SqlBulkCopy声明及参数设置

68 try

69 {70 //SqlBulkCopy xx = new SqlBulkCopy(sqlconn,71 //SqlBulkCopy bulk = new SqlBulkCopy(sqlconn.ToString(), SqlBulkCopyOptions.UseInternalTransaction)72 //{ DestinationTableName = "ENG_FailSendSMS" /*设置数据库目标表名称*/, BatchSize = dt.Rows.Count /*每一批次中的行数*/ };73 //SqlBulkCopy xxx =new SqlBulkCopy(sqlconn,

74 SqlBulkCopy bulk = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.UseInternalTransaction, null) { DestinationTableName = "T_PIPES" /*设置数据库目标表名称*/, BatchSize = dtLoad.Rows.Count /*每一批次中的行数*/};75

76

77 bulk.ColumnMappings.Add("ProjectID", "ProjectID"); //设置数据源中的列和目标表中的列之间的映射关系

78 bulk.ColumnMappings.Add("ModelID", "ModelID");//ColumnMappings.Add("源数据表列名称", "目标表数据列名称");

79 bulk.ColumnMappings.Add("ID", "ID");80 bulk.ColumnMappings.Add("Node1", "Node1");81 bulk.ColumnMappings.Add("Node2", "Node2");82 bulk.ColumnMappings.Add("Length", "Length");83 bulk.ColumnMappings.Add("Diameter", "Diameter");84 bulk.ColumnMappings.Add("Roughness", "Roughness");85 bulk.ColumnMappings.Add("MinorLoss", "MinorLoss");86 bulk.ColumnMappings.Add("Status", "Status");87 bulk.ColumnMappings.Add("Comment", "Comment");88

89 //insert_pipesData(db_do, prjId, modid, PipeLines[i].ID, PipeLines[i].Node1, PipeLines[i].Node2, PipeLines[i].Data[CPipe.PIPE_LEN_INDEX], PipeLines[i].Data[CPipe.PIPE_DIAM_INDEX], PipeLines[i].Data[CPipe.PIPE_ROUGH_INDEX], PipeLines[i].Data[CPipe.PIPE_MLOSS_INDEX], PipeLines[i].Data[CPipe.PIPE_STATUS_INDEX], PipeLines[i].Data[CGlobalConst.COMMENT_INDEX]);90

91 //void insert_pipesData(WaterNetObjectDB db_do, string prjId, string modid, string id, string node1, string node2, string len, string diam, string rough, string mloss, string status, string comment)92 //{93 //string sql;94 //sql = "insert into T_PIPES values(" + prjId + "," + modid + ",'" + id + "','" + node1 + "','" + node2 + "'," + len + "," + diam + "," + rough + ",'" + mloss + "','" + status + "','" + comment + "') ";95 //db_do.nonQuerySql(sql);96 //}97

98 //[ProjectID] ,[ModelID] ,[ID] ,[Node1] ,[Node2] ,[Length] ,[Diameter] ,[Roughness],[MinorLoss],[Status] ,[Comment]

99 #endregion

100 bulk.WriteToServer(dtLoad);101 if (bulk != null)102 {103 bulk.Close();104 }105 }106 catch(Exception e)107 {108 Console.WriteLine(e.Message.ToString());109 }110 }