1 /// <summary>
2 /// 数据库通用操作类
3 /// </summary>
4 public abstract class DbHelper
5 {
6 #region " 连接字符串 "
7 //连接字符串
8 //public static string ConnString = ConfigurationManager.ConnectionStrings["CommonSqlConnectionString"].ConnectionString;
9 public static string DefaultConnString = string.Empty;
10 public const int defaultCommandTimeout = 180;
11 public const int defaultdbconfig = 1;
12
13 #endregion
14
15 #region " GetSqlCommand "
16
17 /// <summary>
18 /// 获取初始化好的Command对象
19 /// </summary>
20 /// <param name="conn">Connection对象</param>
21 /// <param name="cmdText">命令文本</param>
22 /// <param name="cmdType">命令类型</param>
23 /// <param name="parameters">参数列表</param>
24 /// <returns>初始化好的Command对象</returns>
25 private static SqlCommand GetSqlCommand(SqlConnection conn, string cmdText, CommandType cmdType, SqlParameter[] parameters)
26 {
27 SqlCommand cmd = new SqlCommand(cmdText, conn);
28 cmd.CommandType = cmdType;
29 cmd.CommandTimeout = defaultCommandTimeout;
30 if (parameters != null)
31 {
32 cmd.Parameters.AddRange(parameters);
33 }
34
35 return cmd;
36 }
37
38 #endregion
39
40 #region " ExecSqlDataSet "
41
42 public static DataTable ExecSqlDataSet(string strSQL)
43 {
44 return ExecSqlDataSet(strSQL, null);
45 }
46
47 public static DataTable ExecSqlDataSet(string strSQL, SqlParameter[] parameters)
48 {
49 return ExecSqlDataSet(strSQL, parameters, DefaultConnString);
50 }
51
52 public static DataTable ExecSqlDataSet(string strSQL, SqlParameter[] parameters, string connStr)
53 {
54 if (string.IsNullOrWhiteSpace(connStr))
55 {
56 return new DataTable();
57 }
58 using (SqlConnection conn = new SqlConnection(connStr))
59 {
60 conn.Open();
61 SqlCommand cmd = GetSqlCommand(conn, strSQL, CommandType.Text, parameters);
62 SqlDataAdapter da = new SqlDataAdapter(cmd);
63 DataSet ds = new DataSet();
64 da.Fill(ds);
65 cmd.Dispose();
66 da.Dispose();
67 return ds.Tables[0];
68 }
69 }
70
71
72
73 #endregion
74
75 #region " ExecSqlNonQuerry "
76
77 /// <summary>
78 /// 执行非查询SQL语句
79 /// </summary>
80 /// <param name="strSQL">待执行SQL语句</param>
81 /// <returns>受影响的行数</returns>
82 public static int ExecSqlNonQuerry(string strSQL)
83 {
84 return ExecSqlNonQuerry(strSQL, null);
85 }
86
87 /// <summary>
88 /// 执行非查询的带参数的SQL语句
89 /// </summary>
90 /// <param name="strSQL">待执行SQL语句</param>
91 /// <returns>受影响的行数</returns>
92 public static int ExecSqlNonQuerry(string strSQL, SqlParameter[] parameters)
93 {
94 return ExecSqlNonQuerry(strSQL, parameters, DefaultConnString);
95 }
96
97
98
99 public static int ExecSqlNonQuerry(string strSQL, SqlParameter[] parameters, string connStr)
100 {
101 using (SqlConnection conn = new SqlConnection(connStr))
102 {
103 conn.Open();
104 SqlCommand cmd = GetSqlCommand(conn, strSQL, CommandType.Text, parameters);
105 cmd.CommandTimeout = 0;
106 int result = cmd.ExecuteNonQuery();
107 cmd.Dispose();
108 return result;
109 }
110 }
111
112 #endregion
113
114 #region " ExecSqlScalar "
115
116 /// <summary>
117 /// 执行统计查询
118 /// </summary>
119 /// <param name="strSQL">待执行SQL语句</param>
120 /// <returns>执行结果的第1行第1列的值</returns>
121 public static object ExecSqlScalar(string strSQL)
122 {
123 return ExecSqlScalar(strSQL, null);
124 }
125
126 /// <summary>
127 /// 执行带参数的统计查询
128 /// </summary>
129 /// <param name="strSQL">待执行SQL语句</param>
130 /// <param name="parameters">参数数组</param>
131 /// <returns>执行结果的第1行第1列的值</returns>
132 public static object ExecSqlScalar(string strSQL, SqlParameter[] parameters)
133 {
134 return ExecSqlScalar(strSQL, parameters, DefaultConnString);
135 }
136
137 /// <summary>
138 /// 执行带参数的统计查询
139 /// </summary>
140 /// <param name="strSQL">待执行SQL语句</param>
141 /// <param name="parameters">参数数组</param>
142 /// <returns>执行结果的第1行第1列的值</returns>
143 public static object ExecSqlScalar(string strSQL, SqlParameter[] parameters, string connStr)
144 {
145 using (SqlConnection conn = new SqlConnection(connStr))
146 {
147 conn.Open();
148 SqlCommand cmd = GetSqlCommand(conn, strSQL, CommandType.Text, parameters);
149 object result = cmd.ExecuteScalar();
150 cmd.Dispose();
151 return result;
152 }
153 }
154
155 #endregion
156
157 #region " ExecProcDataSet "
158
159 /// <summary>
160 /// 执行存储过程，返回执行结果
161 /// </summary>
162 /// <param name="procName">待执行存储过程</param>
163 /// <returns>查询结果</returns>
164 public static DataSet ExecProcDataSet(string procName)
165 {
166 return ExecProcDataSet(procName, null);
167 }
168 /// <summary>
169 /// 执行带参数的存储过程，返回执行结果
170 /// </summary>
171 /// <param name="procName">待执行存储过程</param>
172 /// <param name="parameters">参数数组</param>
173 /// <returns>查询结果</returns>
174 public static DataSet ExecProcDataSet(string procName, SqlParameter[] parameters)
175 {
176 return ExecProcDataSet(procName, parameters, DefaultConnString);
177 }
178
179 /// <summary>
180 /// 执行带参数的存储过程，返回执行结果
181 /// </summary>
182 /// <param name="procName">待执行存储过程</param>
183 /// <param name="parameters">参数数组</param>
184 /// <returns>查询结果</returns>
185 public static DataSet ExecProcDataSet(string procName, SqlParameter[] parameters, string connStr)
186 {
187 using (SqlConnection conn = new SqlConnection(connStr))
188 {
189 conn.Open();
190 SqlCommand cmd = GetSqlCommand(conn, procName, CommandType.StoredProcedure, parameters);
191 cmd.CommandTimeout = 0;
192 SqlDataAdapter da = new SqlDataAdapter(cmd);
193 DataSet ds = new DataSet();
194 da.Fill(ds);
195 cmd.Dispose();
196 return ds;
197 }
198 }
199
200
201 #endregion
202
203 #region " ExecProcDataTable "
204
205 /// <summary>
206 /// 执行存储过程，返回执行结果
207 /// </summary>
208 /// <param name="procName">待执行存储过程</param>
209 /// <returns>查询结果</returns>
210 public static DataTable ExecProcDataTable(string procName)
211 {
212 return ExecProcDataSet(procName).Tables[0];
213 }
214 /// <summary>
215 /// 执行带参数的存储过程，返回执行结果
216 /// </summary>
217 /// <param name="procName">待执行存储过程</param>
218 /// <param name="parameters">参数数组</param>
219 /// <returns>查询结果</returns>
220 public static DataTable ExecProcDataTable(string procName, SqlParameter[] parameters)
221 {
222 return ExecProcDataSet(procName, parameters).Tables[0];
223 }
224
225 public static DataTable ExecProcDataTable(string procName, SqlParameter[] parameters, string connStr)
226 {
227 return ExecProcDataSet(procName, parameters, connStr).Tables[0];
228 }
229
230
231 #endregion
232
233 #region " ExecProcNonQuerry "
234
235 /// <summary>
236 /// 执行非查询存储过程
237 /// </summary>
238 /// <param name="procName">待执行存储过程</param>
239 /// <returns>受影响的行数</returns>
240 public static int ExecProcNonQuerry(string procName)
241 {
242 return ExecProcNonQuerry(procName);
243 }
244
245 /// <summary>
246 /// 执行非查询的带参数的存储过程
247 /// </summary>
248 /// <param name="procName">待执行存储过程</param>
249 /// <returns>受影响的行数</returns>
250 public static int ExecProcNonQuerry(string procName, SqlParameter[] parameters)
251 {
252 return ExecProcNonQuerry(procName, parameters, DefaultConnString);
253 }
254
255
256 /// <summary>
257 /// 执行非查询的带参数的存储过程
258 /// </summary>
259 /// <param name="procName">待执行存储过程</param>
260 /// <returns>受影响的行数</returns>
261 public static int ExecProcNonQuerry(string procName, SqlParameter[] parameters, string connStr)
262 {
263 using (SqlConnection conn = new SqlConnection(connStr))
264 {
265 conn.Open();
266 SqlCommand cmd = GetSqlCommand(conn, procName, CommandType.StoredProcedure, parameters);
267 int result = cmd.ExecuteNonQuery();
268 cmd.Dispose();
269 return result;
270 }
271 }
272
273
274
275 #endregion
276
277 #region " ExecSqlDataReader "
278
279 /// <summary>
280 /// 执行SQL语句，返回执行结果
281 /// </summary>
282 /// <param name="strSQL">待执行SQL语句</param>
283 /// <returns>查询结果</returns>
284 public static SqlDataReader ExecSqlDataReader(string strSQL)
285 {
286 return ExecSqlDataReader(strSQL, null);
287 }
288
289 /// <summary>
290 /// 执行带参数的SQL语句，返回执行结果
291 /// </summary>
292 /// <param name="strSQL">待执行SQL语句</param>
293 /// <param name="parameters">参数数组</param>
294 /// <returns>查询结果</returns>
295 public static SqlDataReader ExecSqlDataReader(string strSQL, SqlParameter[] parameters)
296 {
297 return ExecSqlDataReader(strSQL, parameters, DefaultConnString);
298 }
299
300 /// <summary>
301 /// 执行带参数的SQL语句，返回执行结果
302 /// </summary>
303 /// <param name="strSQL">待执行SQL语句</param>
304 /// <param name="parameters">参数数组</param>
305 /// <returns>查询结果</returns>
306 public static SqlDataReader ExecSqlDataReader(string strSQL, SqlParameter[] parameters, string connStr)
307 {
308 using (SqlConnection conn = new SqlConnection(connStr))
309 {
310 conn.Open();
311 SqlCommand cmd = GetSqlCommand(conn, strSQL, CommandType.Text, parameters);
312 SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
313 cmd.Dispose();
314 return result;
315 }
316 }
317
318
319 #endregion
320
321 #region " ExecProcDataReader "
322
323 /// <summary>
324 /// 执行存储过程，返回执行结果
325 /// </summary>
326 /// <param name="procName">待执行存储过程</param>
327 /// <returns>查询结果</returns>
328 public static SqlDataReader ExecProcDataReader(string procName)
329 {
330 return ExecProcDataReader(procName, null);
331 }
332
333 /// <summary>
334 /// 执行带参数的存储过程，返回执行结果
335 /// </summary>
336 /// <param name="procName">待执行存储过程</param>
337 /// <param name="parameters">参数数组</param>
338 /// <returns>查询结果</returns>
339 public static SqlDataReader ExecProcDataReader(string procName, SqlParameter[] parameters)
340 {
341 return ExecProcDataReader(procName, parameters, DefaultConnString);
342 }
343
344 /// <summary>
345 /// 执行带参数的存储过程，返回执行结果
346 /// </summary>
347 /// <param name="procName">待执行存储过程</param>
348 /// <param name="parameters">参数数组</param>
349 /// <returns>查询结果</returns>
350 public static SqlDataReader ExecProcDataReader(string procName, SqlParameter[] parameters, string connStr)
351 {
352 using (SqlConnection conn = new SqlConnection(connStr))
353 {
354 conn.Open();
355
356 SqlCommand cmd = GetSqlCommand(conn, procName, CommandType.StoredProcedure, parameters);
357 SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
358 cmd.Dispose();
359 return result;
360 }
361 }
362
363
364
365 #endregion
366
367 #region " DtToSqlServer "
368
369 /// <summary>
370 /// 将DataTable批量导入SqlServer
371 /// </summary>
372 /// <param name="dtExcel">数据表</param>
373 /// <param name="tableName">目标数据表名</param>
374 /// <param name="dtColName">对应列的数据集</param>
375 public static void DtToSqlServer(DataTable dtExcel, string tableName, DataTable dtColName)
376 {
377 DtToSqlServer(dtExcel, tableName, dtColName, DefaultConnString);
378 }
379
380 /// <summary>
381 /// 将DataTable批量导入SqlServer
382 /// </summary>
383 /// <param name="dtExcel">数据表</param>
384 /// <param name="tableName">目标数据表名</param>
385 /// <param name="dtColName">对应列的数据集</param>
386 public static void DtToSqlServer(DataTable dtExcel, string tableName, DataTable dtColName, string connStr)
387 {
388 using (SqlConnection conn = new SqlConnection(connStr))
389 {
390 conn.Open();
391
392 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
393 {
394 try
395 {
396 bulkCopy.DestinationTableName = tableName;//要插入的表的表名
397 for (int i = 0; i < dtColName.Rows.Count; i++)
398 {
399 bulkCopy.ColumnMappings.Add(dtColName.Rows[i][0].ToString().Trim(), dtColName.Rows[i][1].ToString().Trim());
400
401 }
402
403 bulkCopy.WriteToServer(dtExcel);
404 }
405 catch (Exception ex)
406 {
407 Console.WriteLine(ex.Message);
408 }
409 }
410 }
411 }
412
413 #endregion
414
415 #region
416 /// <summary>
417 ///
418 /// </summary>
419 /// <param name="dbconfig">目标连接字符</param>
420 /// <param name="tablename">目标表</param>
421 /// <param name="dt">源数据</param>
422 public static string SqlBulkCopyByDatatable(string tablename, DataTable table, string connStr, SqlConnection m_clsSqlConn)
423 {
424 string dataBaseStr = "";
425 if (tablename.Contains("."))
426 {
427 dataBaseStr = tablename.Substring(0, tablename.LastIndexOf(".") + 1);
428 tablename = tablename.Substring(tablename.LastIndexOf(".") + 1);
429 }
430
431 try
432 {
433 string result = "";
434 SqlBulkCopy sqlBulkCopy = null;
435 if (m_clsSqlConn != null)
436 {
437 sqlBulkCopy = new SqlBulkCopy(m_clsSqlConn);
438 if (m_clsSqlConn.State == ConnectionState.Closed)
439 {
440 m_clsSqlConn.Open();
441 }
442 }
443 else
444 {
445 sqlBulkCopy = new SqlBulkCopy(connStr);
446 }
447
448
449
450 sqlBulkCopy.DestinationTableName = dataBaseStr + ((tablename.IndexOf("[") > -1 && tablename.IndexOf("]") > -1) ? tablename : "[" + tablename + "]");
451 sqlBulkCopy.BulkCopyTimeout = 500;
452 //sqlBulkCopy.BatchSize = 800;
453
454 for (int i = 0; i < table.Columns.Count; i++)
455 {
456 sqlBulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
457 }
458
459 if (table.Rows.Count > 0)
460 {
461 sqlBulkCopy.WriteToServer(table);
462 }
463 else
464 {
465 result = "表为空";
466 }
467
468 sqlBulkCopy.Close();
469 return result;
470 }
471 catch (Exception e)
472 {
473 return e.Message;
474 }
475 finally
476 {
477 try
478 {
479 if (m_clsSqlConn != null)
480 {
481
482 try
483 {
484 if (m_clsSqlConn.State == ConnectionState.Open)
485 {
486 m_clsSqlConn.Close();
487 }
488 }
489 catch (Exception)
490 {
491
492 }
493 }
494 }
495 catch (Exception)
496 {
497
498 }
499 }
500 }
501
502 public static string SqlBulkCopyByDatatable(string tablename, DataTable table, SqlConnection m_clsSqlConn)
503 {
504 return SqlBulkCopyByDatatable(tablename, table, string.Empty, m_clsSqlConn);
505
506 }
507 public static string SqlBulkCopyByDatatable(string tablename, DataTable table, string connStr)
508 {
509 return SqlBulkCopyByDatatable(tablename, table, connStr, null);
510 }
511
512 public static string SqlBulkCopyByDatatable(string tablename, DataTable table)
513 {
514 return SqlBulkCopyByDatatable(tablename, table, DefaultConnString, null);
515 }
516
517 public static string CreateTempTable(string tablename, DataTable table, string connStr)
518 {
519 return CreateTempTable(tablename, table, new SqlConnection(connStr));
520 }
521 public static string CreateTempTable(string tablename, DataTable table, SqlConnection connStr)
522 {
523 try
524 {
525
526 string sqlstr = "CREATE TABLE [" + tablename + "](";
527 for (int i = 0; i < table.Columns.Count; i++)
528 {
529 switch (table.Columns[i].DataType.FullName)
530 {
531 case "System.String":
532 {
533 sqlstr += "[" + table.Columns[i].ColumnName + "] [nvarchar](4000) NULL,";
534 }
535 break;
536 case "System.Int32":
537 {
538 sqlstr += "[" + table.Columns[i].ColumnName + "] [int] NULL,";
539 }
540 break;
541 case "System.Double":
542 {
543 sqlstr += "[" + table.Columns[i].ColumnName + "] [numeric](24,2) NULL,";
544 }
545 break;
546 case "System.DateTime":
547 {
548 sqlstr += "[" + table.Columns[i].ColumnName + "] [datetime] NULL,";
549 }
550 break;
551 default:
552 {
553 sqlstr += "[" + table.Columns[i].ColumnName + "] [nvarchar](4000) NULL,";
554 }
555 break;
556 }
557 }
558 sqlstr = sqlstr.Substring(0, sqlstr.Length - 1) + ")";
559
560 if (connStr.State != ConnectionState.Open)
561 {
562 connStr.Open();
563 }
564
565 SqlCommand cmd = GetSqlCommand(connStr, sqlstr, CommandType.Text, null);
566 int result = cmd.ExecuteNonQuery();
567 cmd.Dispose();
568 return "";
569 }
570 catch (Exception e)
571 {
572 return e.ToString();
573 }
574 }
575
576 #endregion
577
578
579 }
580
