SqlHelper.cs
4.02 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using Microsoft.Data.SqlClient;
using HHECS.Infrastructure.LogHelper;
using System.Reflection;
using NPOI.SS.Formula.Functions;
using System.Collections.Generic;
using HHECS.Infrastructure;
using HHECS.Dal;
using Hh.Mes.Service.WebService.Planned;
using HHECS.Infrastructure.CommonHelper;
using System.Transactions;
namespace HHECS.Application.Service.ImportBase
{
public sealed class SqlHelper
{
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">要执行的Sql语句</param>
/// <param name="param">Sql语句中的参数</param>
/// <returns>结果集表</returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] param)
{
DataTable table = new DataTable();
using (SqlConnection con = new SqlConnection(DALHelper.Constr))
{
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
// 不为空 就添加参数
if (param.Length != 0)
{
adapter.SelectCommand.Parameters.AddRange(param);
}
adapter.Fill(table);
}
return table;
}
/// <summary>
/// SqlBulkCopy批量插入数据
/// https://blog.csdn.net/xulong5000/article/details/103891296
/// </summary>
/// <param name="connectionStr">链接字符串</param>
/// <param name="dataTableName">表名</param>
/// <param name="sourceDataTable">数据源</param>
/// <param name="batchSize">一次事务插入的行数</param>
public static Tuple<bool, string> SqlBulkCopyByDataTable( Dictionary<string, DataTable> dicDtSource)
{
SqlConnection connection = null;
SqlBulkCopy sqlBulkCopy = null;
SqlTransaction tran = null;
return ExceptionsHelp.Instance.ExecuteT(() =>
{
connection = new SqlConnection(DALHelper.Constr);
connection.Open();
tran = connection.BeginTransaction();
sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, tran);
foreach (var item in dicDtSource)
{
if (item.Key.IndexOf(TableNameBase.tableSuffix, StringComparison.Ordinal) > 0)
{
if (item.Value.Rows.Count > 0)
{
sqlBulkCopy.DestinationTableName = item.Value.TableName;
sqlBulkCopy.WriteToServer(item.Value);
}
}
}
tran.Commit();
return new Tuple<bool, string>(true, SystemVariable.dataActionOk);
}, actionFinally: () =>
{
tran?.Dispose();
sqlBulkCopy?.Close();
connection?.Close();
}, actionCatCh: (ex) =>
{
tran.Rollback();
string msg = ex.ToString().Substring(0,200).StrJsonReplace();
Log4NetHelper.Instance.Error($"【SqlHelper-SqlBulkCopyByDataTable】" + ex.ToString());
return new Tuple<bool, string>(false, msg); ;
});
}
/// <summary>
/// 返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteNonquery(string sql, params SqlParameter[] param)
{
int i = -1;
using (SqlConnection con = new SqlConnection(DALHelper.Constr))
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
if (param != null) com.Parameters.AddRange(param);
i = com.ExecuteNonQuery();
}
return i;
}
}
}