C#工具类SqlServerHelper，基于System.Data.SqlClient封装

  1 using system;
  2 using system.collections.generic;
  3 using system.data;
  4 using system.linq;
  5 using system.text;
  6 using system.threading.tasks;
  7 using system.data.sqlclient;
  8 
  9 namespace fly.util.database
 10 {
 11     /// <summary>
 12     /// sqlserver数据库操作类
 13     /// </summary>
 14     public 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>  
 24         public static int executenonquery(string connectionstring, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
 25         {
 26             sqlcommand cmd = new sqlcommand();
 27             using (sqlconnection conn = new sqlconnection(connectionstring))
 28             {
 29                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
 30                 int val = cmd.executenonquery();
 31                 cmd.parameters.clear();
 32                 return 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>  
 44         public 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);
 48             int val = cmd.executenonquery();
 49             cmd.parameters.clear();
 50             return 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>  
 61         public static int executenonquery(sqlconnection connection, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
 62         {
 63             if (connection == null)
 64                 throw new argumentnullexception("当前数据库连接不存在");
 65             sqlcommand cmd = new sqlcommand();
 66             preparecommand(cmd, connection, null, cmdtype, cmdtext, cmdparms);
 67             int val = cmd.executenonquery();
 68             cmd.parameters.clear();
 69             return 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>  
 80         public static sqldatareader executereader(string connectionstring, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
 81         {
 82             sqlcommand cmd = new sqlcommand();
 83             sqlconnection conn = new sqlconnection(connectionstring);
 84             try
 85             {
 86                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
 87                 sqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
 88                 cmd.parameters.clear();
 89                 return reader;
 90             }
 91             catch
 92             {
 93                 cmd.dispose();
 94                 conn.close();
 95                 throw;
 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>  
107         public 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;
112             try
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             }
121             catch
122             {
123                 throw;
124             }
125             finally
126             {
127                 cmd.dispose();
128                 conn.close();
129                 conn.dispose();
130             }
131 
132             return ds;
133         }
134 
135         /// <summary>  
136         /// 执行数据库查询操作,返回datatable类型的结果集  
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>  
143         public 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 
149             try
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             }
158             catch
159             {
160                 throw;
161             }
162             finally
163             {
164                 cmd.dispose();
165                 conn.close();
166                 conn.dispose();
167             }
168 
169             return 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>  
180         public static object executescalar(string connectionstring, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
181         {
182             sqlcommand cmd = new sqlcommand();
183             sqlconnection conn = new sqlconnection(connectionstring);
184             object result = null;
185             try
186             {
187                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
188                 result = cmd.executescalar();
189                 cmd.parameters.clear();
190             }
191             catch
192             {
193                 throw;
194             }
195             finally
196             {
197                 cmd.dispose();
198                 conn.close();
199                 conn.dispose();
200             }
201 
202             return 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>  
213         public static object executescalar(sqltransaction trans, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
214         {
215             if (trans == null)
216                 throw new argumentnullexception("当前数据库事务不存在");
217             sqlconnection conn = trans.connection;
218             if (conn == null)
219                 throw new argumentexception("当前事务所在的数据库连接不存在");
220 
221             sqlcommand cmd = new sqlcommand();
222             object result = null;
223 
224             try
225             {
226                 preparecommand(cmd, conn, trans, cmdtype, cmdtext, cmdparms);
227                 result = cmd.executescalar();
228                 cmd.parameters.clear();
229             }
230             catch
231             {
232                 throw;
233             }
234             finally
235             {
236                 trans.dispose();
237                 cmd.dispose();
238                 conn.close();
239                 conn.dispose();
240             }
241 
242             return 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>  
253         public static object executescalar(sqlconnection conn, commandtype cmdtype, string cmdtext, params sqlparameter[] cmdparms)
254         {
255             if (conn == null) throw new argumentexception("当前数据库连接不存在");
256             sqlcommand cmd = new sqlcommand();
257             object result = null;
258 
259             try
260             {
261                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
262                 result = cmd.executescalar();
263                 cmd.parameters.clear();
264             }
265             catch
266             {
267                 throw;
268             }
269             finally
270             {
271                 cmd.dispose();
272                 conn.close();
273                 conn.dispose();
274             }
275 
276             return 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>
286         public 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);
292             return 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>  
305         private static void preparecommand(sqlcommand cmd, sqlconnection conn, sqltransaction trans, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
306         {
307             if (conn.state != connectionstate.open)
308                 conn.open();
309 
310             cmd.connection = conn;
311             cmd.commandtext = cmdtext;
312 
313             if (trans != null)
314                 cmd.transaction = trans;
315 
316             cmd.commandtype = cmdtype;
317 
318             if (cmdparms != null)
319             {
320                 foreach (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>
332         private static sqlcommand buildsqlcommand(sqlconnection connection, string storedprocname, idataparameter[] parameters)
333         {
334             sqlcommand command = new sqlcommand(storedprocname, connection);
335             command.commandtype = commandtype.storedprocedure;
336             foreach (sqlparameter parameter in parameters)
337             {
338                 command.parameters.add(parameter);
339             }
340             return command;
341         }
342     }
343 }