导入Excel时报出System.InvalidOperationException 中第一次偶然出现的“System.Data.dll”类型的错误
0
悬赏园豆：20
[已关闭问题]
关闭于 2014-06-12 11:17
在做数据导入的时候，出现了下面的错误：
在 System.InvalidOperationException 中第一次偶然出现的“System.Data.dll”类型的错误，不知道什么原因，忘各位大神告知。代码如下：
Model类
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace HRMSystem.Model 8 { 9 public class Employee 10 { 11 public Guid Id 12 { 13 get; 14 set; 15 } 16 17 /// <summary> 18 /// 工号 19 /// </summary> 20 public string Number 21 { 22 get; 23 set; 24 } 25 26 public string Name 27 { 28 get; 29 set; 30 } 31 32 /// <summary> 33 /// 性别Id 34 /// </summary> 35 public Guid GenderId 36 { 37 get; 38 set; 39 } 40 41 //因为图片如果一次性读取到Model中会非常耗内存，因此需要的时候再去单独读取，不在Model中建字段 42 43 /// <summary> 44 /// 出生日期 45 /// </summary> 46 public DateTime BirthDay 47 { 48 get; 49 set; 50 } 51 52 /// <summary> 53 /// 入职日期 54 /// </summary> 55 public DateTime InDate 56 { 57 get; 58 set; 59 } 60 61 /// <summary> 62 /// 婚姻状态Id 63 /// </summary> 64 public Guid MarriageId 65 { 66 get; 67 set; 68 } 69 70 /// <summary> 71 /// 政治面貌Id 72 /// </summary> 73 public Guid PartyStatusId 74 { 75 get; 76 set; 77 } 78 79 /// <summary> 80 /// 民族 81 /// </summary> 82 public string Nationality 83 { 84 get; 85 set; 86 } 87 88 /// <summary> 89 /// 籍贯 90 /// </summary> 91 public string NativeAddr 92 { 93 get; 94 set; 95 } 96 97 /// <summary> 98 /// 教育程度Id 99 /// </summary> 100 public Guid EducationId 101 { 102 get; 103 set; 104 } 105 106 /// <summary> 107 /// 专业 108 /// </summary> 109 public string Major 110 { 111 get; 112 set; 113 } 114 115 /// <summary> 116 /// 毕业院校 117 /// </summary> 118 public string School 119 { 120 get; 121 set; 122 } 123 124 /// <summary> 125 /// 地址 126 /// </summary> 127 public string Address 128 { 129 get; 130 set; 131 } 132 133 /// <summary> 134 /// 基本工资 135 /// </summary> 136 public int BaseSalary 137 { 138 get; 139 set; 140 } 141 142 /// <summary> 143 /// Email 144 /// </summary> 145 public string Email 146 { 147 get; 148 set; 149 } 150 151 /// <summary> 152 /// 有效身份证号 153 /// </summary> 154 public string IdNum 155 { 156 get; 157 set; 158 } 159 160 /// <summary> 161 /// 联系电话 162 /// </summary> 163 public string TelNum 164 { 165 get; 166 set; 167 } 168 169 /// <summary> 170 /// 紧急联系人信息 171 /// </summary> 172 public string EmergencyContact 173 { 174 get; 175 set; 176 } 177 178 /// <summary> 179 /// 部门Id 180 /// </summary> 181 public Guid DepartmentId 182 { 183 get; 184 set; 185 } 186 187 /// <summary> 188 /// 职位 189 /// </summary> 190 public string Position 191 { 192 get; 193 set; 194 } 195 196 /// <summary> 197 /// 合同起始时间 198 /// </summary> 199 public DateTime ContractStartDay 200 { 201 get; 202 set; 203 } 204 205 /// <summary> 206 /// 合同到期时间 207 /// </summary> 208 public DateTime ContractEndDay 209 { 210 get; 211 set; 212 } 213 214 /// <summary> 215 /// 简历 216 /// </summary> 217 public string Resume 218 { 219 get; 220 set; 221 } 222 223 /// <summary> 224 /// 备注 225 /// </summary> 226 public string Remarks 227 { 228 get; 229 set; 230 } 231 232 public byte[] Photo 233 { 234 get; 235 set; 236 } 237 } 238 }
点击导入按钮触发事件的代码：
1 /// <summary> 2 /// 导入Excel表数据至数据库的相应字段 3 /// </summary> 4 private void btnExcelToImport_Click(object sender, RoutedEventArgs e) 5 { 6 OpenFileDialog ofd = new OpenFileDialog(); 7 ofd.Filter = "Excel文件|*.xls|Excel文件|*.xlsx"; 8 if (ofd.ShowDialog() != true) 9 { 10 return; 11 } 12 string filePath = ofd.FileName; 13 DataTable table = ReadExcelToDataTable.ReadExcel(filePath); 14 for (var i = 0; i < table.Rows.Count; i++) 15 { 16 for (var j = 0; j < table.Columns.Count; j++) 17 { 18 Console.Write(table.Rows[i][j] + "/t"); 19 Console.Write(table.Columns[j].ColumnName); 20 } 21 Console.WriteLine(""); 22 } 23 string destinationTableName = "T_Employee"; 24 Console.WriteLine(CommonHelper.MsSqlBulkCopyData(table, CommonHelper.getConnStr(), destinationTableName)); 25 }
读取Excel的类：
1 /// <summary> 2 /// 导入Excel表数据至数据库的相应字段 3 /// </summary> 4 private void btnExcelToImport_Click(object sender, RoutedEventArgs e) 5 { 6 OpenFileDialog ofd = new OpenFileDialog(); 7 ofd.Filter = "Excel文件|*.xls|Excel文件|*.xlsx"; 8 if (ofd.ShowDialog() != true) 9 { 10 return; 11 } 12 string filePath = ofd.FileName; 13 DataTable table = ReadExcelToDataTable.ReadExcel(filePath); 14 for (var i = 0; i < table.Rows.Count; i++) 15 { 16 for (var j = 0; j < table.Columns.Count; j++) 17 { 18 Console.Write(table.Rows[i][j] + "/t"); 19 Console.Write(table.Columns[j].ColumnName); 20 } 21 Console.WriteLine(""); 22 } 23 string destinationTableName = "T_Employee"; 24 Console.WriteLine(CommonHelper.MsSqlBulkCopyData(table, CommonHelper.getConnStr(), destinationTableName)); 25 }
期间调用了SqlBuckCopy类进行批量导入：
1 /// <summary> 2 /// 利用Net SqlBulkCopy 批量导入数据库 3 /// </summary> 4 /// <param name="dataTable">源内存数据表</param> 5 public static bool MsSqlBulkCopyData(DataTable dt, string connectionString, string destinationTableName) 6 { 7 try 8 { 9 using (SqlConnection conn = new SqlConnection(connectionString)) 10 { 11 conn.Open(); 12 SqlTransaction trans = conn.BeginTransaction(); 13 14 SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans); 15 // 设置源表名称 16 sqlbulkCopy.DestinationTableName = destinationTableName; 17 //分几次拷贝 18 //sqlbulkCopy.BatchSize = 10; 19 // 设置超时限制 20 sqlbulkCopy.BulkCopyTimeout = 1000; 21 foreach (DataColumn dtColumn in dt.Columns) 22 { 23 sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName); 24 } 25 try 26 { 27 // 写入 28 sqlbulkCopy.WriteToServer(dt); 29 // 提交事务 30 trans.Commit(); 31 return true; 32 } 33 catch 34 { 35 trans.Rollback(); 36 sqlbulkCopy.Close(); 37 return false; 38 } 39 finally 40 { 41 sqlbulkCopy.Close(); 42 conn.Close(); 43 } 44 } 45 } 46 catch (Exception e) 47 { 48 new OperationLogDAL().Insert(CommonHelper.getOperatorId(), "-----------利用Net SqlBulkCopyData 批量导入数据库-----------\r\n" + e.Message + "\r\n"); 49 return false; 50 } 51 }
所有回答(3)
0
你能把异常的详细信息铁出来吗？
0
这个要调试 哪里出错 要详细点 注意的是SqlBuckCopy要复制表结构 要和数据库的表结构一致
这个是一致的
0
我记得好像是把debug里的这个异常去掉？
