(更新时间)2021年5月19日 仓库温控系统(Winform) 08 数据库操作相关帮助类SqlHelper

public class SqlHelper
{
    /// <summary>
    /// 连接字符串
    /// </summary>
    private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    /// <summary>
    /// 增、删、改的通用方法
    /// 执行Sql语句或存储过程，返回受影响的行数
    /// SQL注入 
    /// </summary>
    /// <param name="sql">sql语句或存储过程名</param>
    /// <param name="cmdType">执行的脚本类型 1:sql语句  2:存储过程</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sql, int cmdType, params SqlParameter[] parameters)
    {
        //select @@Identity 返回上一次插入记录时自动产生的ID
        int result = 0;//返回结果
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            //执行脚本的对象cmd
            SqlCommand cmd = BuilderCommand(conn, sql, cmdType, null, parameters);
            result = cmd.ExecuteNonQuery();//执行T-SQL并返回受影响行数
            cmd.Parameters.Clear();
        }
        //using原理：类似于try finally
        return result;
    }

    /// <summary>
    /// 执行sql查询，返回第一行第一列的值
    /// </summary>
    /// <param name="sql">sql语句或存储过程</param>
    /// <param name="cmdType">执行的脚本类型 1:sql语句  2:存储过程</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] parameters)
    {
        object result = null;//返回结果
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            //执行脚本的对象cmd
            SqlCommand cmd = BuilderCommand(conn, sql, cmdType, null, parameters);
            result = cmd.ExecuteScalar();//执行T-SQL并返回第一行第一列的值
            cmd.Parameters.Clear();
            if (result == null || result == DBNull.Value)
            {
                return null;
            }
            else
            {
                return result;
            }
        }
    }

    /// <summary>
    /// 执行sql查询,返回SqlDataReader对象
    /// </summary>
    /// <param name="sql">sql语句或存储过程</param>
    /// <param name="cmdType">执行的脚本类型 1:sql语句  2:存储过程</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] parameters)
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = BuilderCommand(conn, sql, cmdType, null, parameters);
        SqlDataReader reader;
        try
        {
            //conn.Open();连接必须是打开的
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//推荐
            return reader;
        }
        catch (Exception ex)
        {
            conn.Close();
            throw new Exception("创建reader对象发生异常", ex);
        }

    }

    /// <summary>
    /// 执行查询，查询结果填充到DataTable 只针对查询一个表的情况
    /// </summary>
    /// <param name="sql">sql语句或存储过程</param>
    /// <param name="cmdType">执行的脚本类型 1:sql语句  2:存储过程</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] parameters)
    {
        DataTable dt = null;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            SqlCommand cmd = BuilderCommand(conn, sql, cmdType, null, parameters);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cmd.Parameters.Clear();
        }
        return dt;
    }

    /// <summary>
    /// 执行查询，数据填充到DataSet（多个结果集）
    /// </summary>
    /// <param name="sql">sql语句或存储过程</param>
    /// <param name="cmdType">执行的脚本类型 1:sql语句  2:存储过程</param>
    /// <param name="parameters">参数列表</param>
    /// <returns></returns>
    public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] parameters)
    {
        DataSet ds = null;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            SqlCommand cmd = BuilderCommand(conn, sql, cmdType, null, parameters);
            //数据适配器
            //conn 自动打开  断开式连接
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            //自动关闭conn
        }
        return ds;
    }

    /// <summary>
    /// 事务 执行批量sql
    /// </summary>
    /// <param name="listSql"></param>
    /// <returns></returns>
    public static bool ExecuteTrans(List<string> listSql)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();//必要条件
            SqlTransaction trans = conn.BeginTransaction();//开启事务
            SqlCommand cmd = BuilderCommand(conn, "", 1, trans);
            try
            {
                int count = 0;
                for (int i = 0; i < listSql.Count; i++)
                {
                    if (listSql[i].Length > 0)
                    {
                        cmd.CommandText = listSql[i];
                        cmd.CommandType = CommandType.Text;
                        count += cmd.ExecuteNonQuery();//一般执行的是增删改命令
                    }
                }
                trans.Commit();//提交
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();//回滚
                throw new Exception("执行事务出现异常", ex);
            }
        }
    }

    /// <summary>
    /// 事务 批量执行 CommandInfo 包括sql,脚本类型，参数列表
    /// </summary>
    /// <param name="comList"></param>
    /// <returns></returns>
    public static bool ExecuteTrans(List<CommandInfo> comList)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand cmd = BuilderCommand(conn, "", 1, trans);
            try
            {
                int count = 0;
                for (int i = 0; i < comList.Count; i++)
                {
                    cmd.CommandText = comList[i].CommandText;

                    if (comList[i].IsProc)
                        cmd.CommandType = CommandType.StoredProcedure;
                    else
                        cmd.CommandType = CommandType.Text;

                    if (comList[i].Paras != null && comList[i].Paras.Length > 0)
                    {
                        cmd.Parameters.Clear();
                        foreach (var p in comList[i].Paras)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    count += cmd.ExecuteNonQuery();

                }
                trans.Commit();
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw new Exception("执行事务出现异常", ex);
            }
        }
    }

    //public static T ExecuteSql<T>(string sql, int cmdType, DbParameter[] paras, Func<IDbCommand, T> action)
    //{
    //    using (DbConnection conn = new SqlConnection(connStr))
    //    {
    //        conn.Open();
    //        IDbCommand cmd = conn.CreateCommand();
    //        cmd.CommandText = sql;
    //        if (cmdType==2)
    //            cmd.CommandType = CommandType.StoredProcedure;
    //        return action(cmd);
    //    }
    //}

    public static T ExecuteTrans<T>(Func<IDbCommand, T> action)
    {
        using (IDbConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            IDbTransaction trans = conn.BeginTransaction();
            IDbCommand cmd = conn.CreateCommand();
            cmd.Transaction = trans;
            return action(cmd);
        }
    }




    /// <summary>
    /// 构建SqlCommand
    /// </summary>
    /// <param name="conn">数据库连接对象</param>
    /// <param name="sql">SQL语句或存储过程</param>
    /// <param name="comType">命令字符串的类型</param>
    /// <param name="trans">事务</param>
    /// <param name="paras">参数数组</param>
    /// <returns></returns>
    private static SqlCommand BuilderCommand(SqlConnection conn, string sql, int cmdType, SqlTransaction trans, params SqlParameter[] paras)
    {
        if (conn == null) throw new ArgumentNullException("连接对象不能为空！");
        SqlCommand command = new SqlCommand(sql, conn);
        if (cmdType == 2)
            command.CommandType = CommandType.StoredProcedure;
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        if (trans != null)
            command.Transaction = trans;
        if (paras != null && paras.Length > 0)
        {
            command.Parameters.Clear();
            command.Parameters.AddRange(paras);
        }
        return command;
    }

    /// <summary>
    /// 添加参数集合
    /// </summary>
    /// <param name="cmd"></param>
    /// <param name="paras"></param>
    public static void AddParas(IDbCommand cmd, SqlParameter[] paras)
    {
        cmd.Parameters.Clear();
        if(paras!=null &&paras.Length >0)
        {
            foreach(SqlParameter p in paras)
            {
                cmd.Parameters.Add(p);
            }
        }
    }

}

  

 

  
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
  
119
  
120
  
121
  
122
  
123
  
124
  
125
  
126
  
127
  
128
  
129
  
130
  
131
  
132
  
133
  
134
  
135
  
136
  
137
  
138
  
139
  
140
  
141
  
142
  
143
  
144
  
145
  
146
  
147
  
148
  
149
  
150
  
151
  
152
  
153
  
154
  
155
  
156
  
157
  
158
  
159
  
160
  
161
  
162
  
163
  
164
  
165
  
166
  
167
  
168
  
169
  
170
  
171
  
172
  
173
  
174
  
175
  
176
  
177
  
178
  
179
  
180
  
181
  
182
  
183
  
184
  
185
  
186
  
187
  
188
  
189
  
190
  
191
  
192
  
193
  
194
  
195
  
196
  
197
  
198
  
199
  
200
  
201
  
202
  
203
  
204
  
205
  
206
  
207
  
208
  
209
  
210
  
211
  
212
  
213
  
214
  
215
  
216
  
217
  
218
  
219
  
220
  
221
  
222
  
223
  
224
  
225
  
226
  
227
  
228
  
229
  
230
  
231
  
232
  
233
  
234
  
235
  
236
  
237
  
238
  
239
  
240
  
241
  
242
  
243
  
244
  
245
  
246
  
247
  
248
  
249
  
250
  
251
  
252
  
253
  
254
  
255
  
256
  
257
  
258
  
259
  
260
  
261
  
262
  
263
  
264
  
265
  
266
  
267
  
268
  
269
  
270
  
271
  
272
  
273
  
274
  
275
  
276
  
277
  
278
  
279
  
280
  
281
  
282
 

文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。

原文链接：codeboy.blog.csdn.net/article/details/117047910