C#⼯具类SqlServerHelper，基于System.Data.SqlClient封装源码：
1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Linq;
5using System.Text;
6using System.Threading.Tasks;
7using System.Data.SqlClient;
8
9namespace Fly.Util.DataBase
10 {
11///<summary>
12/// SqlServer数据库操作类
13///</summary>
14public static class SqlServerHelper
15 {
16///<summary>
17///执⾏数据库⾮查询操作，返回受影响的⾏数
18///</summary>
19///<param name="connectionString">数据库连接字符串</param>
20///<param name="cmdType">命令的类型</param>
21///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
22///<param name="cmdParms">命令参数集合</param>
23///<returns>当前操作影响的数据⾏数</returns>
24public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
25 {
26 SqlCommand cmd = new SqlCommand();
27using (SqlConnection conn = new SqlConnection(connectionString))
28 {
29 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
30int val = cmd.ExecuteNonQuery();
31 cmd.Parameters.Clear();
32return val;
33 }
34 }
35
36///<summary>
37///执⾏数据库事务⾮查询操作，返回受影响的⾏数
38///</summary>
39///<param name="transaction">数据库事务对象</param>
40///<param name="cmdType">Command类型</param>
41///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
42///<param name="cmdParms">命令参数集合</param>
43///<returns>当前事务操作影响的数据⾏数</returns>
44public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
45 {
46 SqlCommand cmd = new SqlCommand();
47 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
48int val = cmd.ExecuteNonQuery();
49 cmd.Parameters.Clear();
50return val;
51 }
52
53///<summary>
54///执⾏数据库⾮查询操作，返回受影响的⾏数
55///</summary>
56///<param name="connection">SqlServer数据库连接对象</param>
57///<param name="cmdType">Command类型</param>
58///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
59///<param name="cmdParms">命令参数集合</param>
60///<returns>当前操作影响的数据⾏数</returns>
61public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
62 {
63if (connection == null)
64throw new ArgumentNullException("当前数据库连接不存在");
65 SqlCommand cmd = new SqlCommand();
66 PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
67int val = cmd.ExecuteNonQuery();
68 cmd.Parameters.Clear();
69return val;
70 }
71
72///<summary>
73///执⾏数据库查询操作，返回SqlDataReader类型的内存结果集
74///</summary>
75///<param name="connectionString">数据库连接字符串</param>
76///<param name="cmdType">命令的类型</param>
77///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
78///<param name="cmdParms">命令参数集合</param>
79///<returns>当前查询操作返回的SqlDataReader类型的内存结果集</returns>
80public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
81 {
82 SqlCommand cmd = new SqlCommand();
83 SqlConnection conn = new SqlConnection(connectionString);
84try
85 {
86 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
87 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
88 cmd.Parameters.Clear();
89return reader;
90 }
91catch
92 {
93 cmd.Dispose();
94 conn.Close();
95throw;
96 }
97 }
98
99///<summary>
100///执⾏数据库查询操作，返回DataSet类型的结果集
101///</summary>
102///<param name="connectionString">数据库连接字符串</param>
103///<param name="cmdType">命令的类型</param>
104///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
105///<param name="cmdParms">命令参数集合</param>
106///<returns>当前查询操作返回的DataSet类型的结果集</returns>
107public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 108 {
109 SqlCommand cmd = new SqlCommand();
110 SqlConnection conn = new SqlConnection(connectionString);
111 DataSet ds = null;
112try
113 {
114 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
115 SqlDataAdapter adapter = new SqlDataAdapter();
116 adapter.SelectCommand = cmd;
117 ds = new DataSet();
118 adapter.Fill(ds);
119 cmd.Parameters.Clear();
120 }
121catch
122 {
123throw;
124 }
125finally
126 {
127 cmd.Dispose();
128 conn.Close();
129 conn.Dispose();
130 }
131
132return ds;
133 }
134
135///<summary>
136///执⾏数据库查询操作，返回DataTable类型的结果集
sqltransaction什么意思137///</summary>
138///<param name="connectionString">数据库连接字符串</param>
139///<param name="cmdType">命令的类型</param>
140///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
141///<param name="cmdParms">命令参数集合</param>
142///<returns>当前查询操作返回的DataTable类型的结果集</returns>
143public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 144 {
145 SqlCommand cmd = new SqlCommand();
146 SqlConnection conn = new SqlConnection(connectionString);
147 DataTable dt = null;
148
149try
150 {
151 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
152 SqlDataAdapter adapter = new SqlDataAdapter();
153 adapter.SelectCommand = cmd;
154 dt = new DataTable();
155 adapter.Fill(dt);
156 cmd.Parameters.Clear();
157 }
158catch
159 {
160throw;
161 }
162finally
163 {
164 cmd.Dispose();
165 conn.Close();
166 conn.Dispose();
167 }
168
169return dt;
170 }
171
172///<summary>
173///执⾏数据库查询操作，返回结果集中位于第⼀⾏第⼀列的Object类型的值
174///</summary>
175///<param name="connectionString">数据库连接字符串</param>
176///<param name="cmdType">命令的类型</param>
177///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
178///<param name="cmdParms">命令参数集合</param>
179///<returns>当前查询操作返回的结果集中位于第⼀⾏第⼀列的Object类型的值</returns>
180public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 181 {
182 SqlCommand cmd = new SqlCommand();
183 SqlConnection conn = new SqlConnection(connectionString);
184object result = null;
185try
186 {
187 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
188 result = cmd.ExecuteScalar();
189 cmd.Parameters.Clear();
190 }
191catch
192 {
193throw;
194 }
195finally
196 {
197 cmd.Dispose();
198 conn.Close();
199 conn.Dispose();
200 }
201
202return result;
203 }
204
205///<summary>
206///执⾏数据库事务查询操作，返回结果集中位于第⼀⾏第⼀列的Object类型的值
207///</summary>
208///<param name="trans">⼀个已存在的数据库事务对象</param>
209///<param name="commandType">命令类型</param>
210///<param name="commandText">SqlServer存储过程名称或PL/SQL命令</param>
211///<param name="cmdParms">命令参数集合</param>
212///<returns>当前事务查询操作返回的结果集中位于第⼀⾏第⼀列的Object类型的值</returns>
213public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 214 {
215if (trans == null)
216throw new ArgumentNullException("当前数据库事务不存在");
217 SqlConnection conn = trans.Connection;
218if (conn == null)
219throw new ArgumentException("当前事务所在的数据库连接不存在");
220
221 SqlCommand cmd = new SqlCommand();
222object result = null;
223
224try
225 {
226 PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
227 result = cmd.ExecuteScalar();
228 cmd.Parameters.Clear();
229 }
230catch
231 {
232throw;
233 }
234finally
235 {
236 trans.Dispose();
237 cmd.Dispose();
238 conn.Close();
239 conn.Dispose();
240 }
241
242return result;
243 }
244
245///<summary>
246///执⾏数据库查询操作，返回结果集中位于第⼀⾏第⼀列的Object类型的值
247///</summary>
248///<param name="conn">数据库连接对象</param>
249///<param name="cmdType">Command类型</param>
250///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
251///<param name="cmdParms">命令参数集合</param>
252///<returns>当前查询操作返回的结果集中位于第⼀⾏第⼀列的Object类型的值</returns>
253public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 254 {
255if (conn == null) throw new ArgumentException("当前数据库连接不存在");
256 SqlCommand cmd = new SqlCommand();
257object result = null;
258
259try
260 {
261 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
262 result = cmd.ExecuteScalar();
263 cmd.Parameters.Clear();
264 }
265catch
266 {
267throw;
268 }
269finally
270 {
271 cmd.Dispose();
272 conn.Close();
273 conn.Dispose();
274 }
275
276return result;
277 }
278
279///<summary>
280///执⾏存储过程
281///</summary>
282///<param name="connection">SqlServer数据库连接对象</param>
283///<param name="storedProcName">存储过程名</param>
284///<param name="parameters">存储过程参数</param>
285///<returns>SqlDataReader对象</returns>
286public static SqlDataReader RunStoredProcedure(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
287 {
288 SqlDataReader returnReader = null;
289 connection.Open();
290 SqlCommand command = BuildSqlCommand(connection, storedProcName, parameters);
291 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
292return returnReader;
293 }
294
295
296///<summary>
297///执⾏数据库命令前的准备⼯作
298///</summary>
299///<param name="cmd">Command对象</param>
300///<param name="conn">数据库连接对象</param>
301///<param name="trans">事务对象</param>
302///<param name="cmdType">Command类型</param>
303///<param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
304///<param name="cmdParms">命令参数集合</param>
305private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 306 {
307if (conn.State != ConnectionState.Open)
308 conn.Open();
309
310 cmd.Connection = conn;
311 cmd.CommandText = cmdText;
312
313if (trans != null)
314 cmd.Transaction = trans;
315
316 cmd.CommandType = cmdType;
317
318if (cmdParms != null)
319 {
320foreach (SqlParameter parm in cmdParms)
321 cmd.Parameters.Add(parm);
322 }
323 }
324
325///<summary>
326///构建SqlCommand对象
327///</summary>
328///<param name="connection">数据库连接</param>
329///<param name="storedProcName">存储过程名</param>
330///<param name="parameters">存储过程参数</param>
331///<returns>SqlCommand</returns>
332private static SqlCommand BuildSqlCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
333 {
334 SqlCommand command = new SqlCommand(storedProcName, connection);
335 command.CommandType = CommandType.StoredProcedure;
336foreach (SqlParameter parameter in parameters)
337 {
338 command.Parameters.Add(parameter);
339 }
340return command;
341 }
342 }
343 }
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论