欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-22 14:24:27
源码: ......

源码:

  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 }