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

/// <summary> 2 /// Sqlbulkcopies the specified SMS.批量插入到数据库 3 /// </summary> 4 /// <param name="data">list类型数据.</param> 5 /// <param name="sqlconn">数据库连接字符串.</param> 6 private void SqlbulkcopyPipeLines(List<CPipe> data, SqlConnection sqlconn, string prjId, string modid) 7 { 8 #region 待处理数据初始化处理 9 List<PropertyInfo> pList = new List<PropertyInfo>();//创建属性的集合 10 DataTable dtLoad = new DataTable(); 11 //把所有的public属性加入到集合 并添加DataTable的列 12 // Array.ForEach<PropertyInfo>(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 in data) 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 }

 

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

标签：映射关系   设置   param   try   ring   之间   div   eof   bsp