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 #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 }
浙公网安备 33010602011771号