首页 新闻 搜索 专区 学院

导入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 }
View Code

点击导入按钮触发事件的代码:

 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         }
View Code

读取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         }
View Code

期间调用了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         }
View Code
落幕的开场白的主页 落幕的开场白 | 初学一级 | 园豆:168
提问于:2014-06-10 15:53
< >
分享
所有回答(3)
0

你能把异常的详细信息铁出来吗?

Launcher | 园豆:45045 (高人七级) | 2014-06-10 16:36
0

这个要调试 哪里出错 要详细点 注意的是SqlBuckCopy要复制表结构 要和数据库的表结构一致

s_p | 园豆:290 (菜鸟二级) | 2014-06-10 20:55

这个是一致的

支持(0) 反对(0) 落幕的开场白 | 园豆:168 (初学一级) | 2014-06-11 09:07
0

我记得好像是把debug里的这个异常去掉?

William Zhao | 园豆:201 (菜鸟二级) | 2014-06-11 00:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册