1  /// <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             #endregion100                 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         }