1 using System;
  2 using System.Data;
  3 using System.Collections.Generic;
  4 using System.Configuration;
  5 using System.Data.OracleClient;
  6 using System.Text;
  7 using System.IO;
  8 
  9 /// <summary>  
 10 /// Oracle数据库操作类  
 11 /// </summary>  
 12 public static class OracleHelper
 13 {
 14     /// <summary>  
 15     /// 执行数据库非查询操作,返回受影响的行数  
 16     /// </summary>  
 17     /// <param name="connectionString">数据库连接字符串</param>
 18     /// <param name="cmdType">命令的类型</param>
 19     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
 20     /// <param name="cmdParms">命令参数集合</param>  
 21     /// <returns>当前查询操作影响的数据行数</returns>  
 22     public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
 23     {
 24         OracleCommand cmd = new OracleCommand();
 25         using (OracleConnection conn = new OracleConnection(connectionString))
 26         {
 27             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 28             int val = cmd.ExecuteNonQuery();
 29             cmd.Parameters.Clear();
 30             return val;
 31         }
 32     }
 33 
 34     /// <summary>  
 35     /// 执行数据库事务非查询操作,返回受影响的行数  
 36     /// </summary>  
 37     /// <param name="transaction">数据库事务对象</param>  
 38     /// <param name="cmdType">Command类型</param>  
 39     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
 40     /// <param name="cmdParms">命令参数集合</param>  
 41     /// <returns>当前事务查询操作影响的数据行数</returns>  
 42     public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
 43     {
 44         OracleCommand cmd = new OracleCommand();
 45         PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
 46         int val = cmd.ExecuteNonQuery();
 47         cmd.Parameters.Clear();
 48         return val;
 49     }
 50 
 51     /// <summary>  
 52     /// 执行数据库非查询操作,返回受影响的行数  
 53     /// </summary>  
 54     /// <param name="connection">Oracle数据库连接对象</param>  
 55     /// <param name="cmdType">Command类型</param>  
 56     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
 57     /// <param name="cmdParms">命令参数集合</param>  
 58     /// <returns>当前查询操作影响的数据行数</returns>  
 59     public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
 60     {
 61         if (connection == null) 
 62             throw new ArgumentNullException("当前数据库连接不存在");
 63         OracleCommand cmd = new OracleCommand();
 64         PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
 65         int val = cmd.ExecuteNonQuery();
 66         cmd.Parameters.Clear();
 67         return val;
 68     }
 69 
 70     /// <summary>  
 71     /// 执行数据库查询操作,返回OracleDataReader类型的内存结果集  
 72     /// </summary>  
 73     /// <param name="connectionString">数据库连接字符串</param>
 74     /// <param name="cmdType">命令的类型</param>
 75     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
 76     /// <param name="cmdParms">命令参数集合</param>  
 77     /// <returns>当前查询操作返回的OracleDataReader类型的内存结果集</returns>  
 78     public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
 79     {
 80         OracleCommand cmd = new OracleCommand();
 81         OracleConnection conn = new OracleConnection(connectionString);
 82         try
 83         {
 84             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
 85             OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 86             cmd.Parameters.Clear();
 87             return reader;
 88         }
 89         catch
 90         {
 91             cmd.Dispose();
 92             conn.Close();
 93             throw;
 94         }
 95     }
 96 
 97     /// <summary>  
 98     /// 执行数据库查询操作,返回DataSet类型的结果集  
 99     /// </summary>  
100     /// <param name="connectionString">数据库连接字符串</param>
101     /// <param name="cmdType">命令的类型</param>
102     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
103     /// <param name="cmdParms">命令参数集合</param>  
104     /// <returns>当前查询操作返回的DataSet类型的结果集</returns>  
105     public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
106     {
107         OracleCommand cmd = new OracleCommand();
108         OracleConnection conn = new OracleConnection(connectionString);
109         DataSet ds = null;
110         try
111         {
112             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
113             OracleDataAdapter adapter = new OracleDataAdapter();
114             adapter.SelectCommand = cmd;
115             ds = new DataSet();
116             adapter.Fill(ds);
117             cmd.Parameters.Clear();
118         }
119         catch
120         {
121             throw;
122         }
123         finally
124         {
125             cmd.Dispose();
126             conn.Close();
127             conn.Dispose();
128         }
129 
130         return ds;
131     }
132 
133     /// <summary>  
134     /// 执行数据库查询操作,返回DataTable类型的结果集  
135     /// </summary>  
136     /// <param name="connectionString">数据库连接字符串</param>
137     /// <param name="cmdType">命令的类型</param>
138     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
139     /// <param name="cmdParms">命令参数集合</param>  
140     /// <returns>当前查询操作返回的DataTable类型的结果集</returns>  
141     public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
142     {
143         OracleCommand cmd = new OracleCommand();
144         OracleConnection conn = new OracleConnection(connectionString);
145         DataTable dt = null;
146 
147         try
148         {
149             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
150             OracleDataAdapter adapter = new OracleDataAdapter();
151             adapter.SelectCommand = cmd;
152             dt = new DataTable();
153             adapter.Fill(dt);
154             cmd.Parameters.Clear();
155         }
156         catch
157         {
158             throw;
159         }
160         finally
161         {
162             cmd.Dispose();
163             conn.Close();
164             conn.Dispose();
165         }
166 
167         return dt;
168     }
169 
170     /// <summary>  
171     /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值  
172     /// </summary>  
173     /// <param name="connectionString">数据库连接字符串</param>
174     /// <param name="cmdType">命令的类型</param>
175     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
176     /// <param name="cmdParms">命令参数集合</param>  
177     /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>  
178     public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
179     {
180         OracleCommand cmd = new OracleCommand();
181         OracleConnection conn = new OracleConnection(connectionString);
182         object result = null;
183         try
184         {
185             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
186             result = cmd.ExecuteScalar();
187             cmd.Parameters.Clear();
188         }
189         catch
190         {
191             throw;
192         }
193         finally
194         {
195             cmd.Dispose();
196             conn.Close();
197             conn.Dispose();
198         }
199 
200         return result;
201     }
202 
203     ///    <summary>  
204     ///    执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值  
205     ///    </summary>  
206     ///    <param name="trans">一个已存在的数据库事务对象</param>  
207     ///    <param name="commandType">命令类型</param>  
208     ///    <param name="commandText">Oracle存储过程名称或PL/SQL命令</param>  
209     ///    <param name="cmdParms">命令参数集合</param>  
210     ///    <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>  
211     public static object ExecuteScalar(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
212     {
213         if (trans == null) 
214             throw new ArgumentNullException("当前数据库事务不存在");
215         OracleConnection conn = trans.Connection;
216         if (conn == null) 
217             throw new ArgumentException("当前事务所在的数据库连接不存在");
218 
219         OracleCommand cmd = new OracleCommand();
220         object result = null;
221 
222         try
223         {
224             PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
225             result = cmd.ExecuteScalar();
226             cmd.Parameters.Clear();
227         }
228         catch
229         {
230             throw;
231         }
232         finally
233         {
234             trans.Dispose();
235             cmd.Dispose();
236             conn.Close();
237             conn.Dispose();
238         }
239 
240         return result;
241     }
242 
243     /// <summary>  
244     /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值  
245     /// </summary>  
246     /// <param name="conn">数据库连接对象</param>  
247     /// <param name="cmdType">Command类型</param>  
248     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
249     /// <param name="cmdParms">命令参数集合</param>  
250     /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>  
251     public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
252     {
253         if (conn == null) throw new ArgumentException("当前数据库连接不存在");
254         OracleCommand cmd = new OracleCommand();
255         object result = null;
256 
257         try
258         {
259             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
260             result = cmd.ExecuteScalar();
261             cmd.Parameters.Clear();
262         }
263         catch
264         {
265             throw;
266         }
267         finally
268         {
269             cmd.Dispose();
270             conn.Close();
271             conn.Dispose();
272         }
273 
274         return result;
275     }
276 
277     /// <summary>  
278     /// 执行数据库命令前的准备工作  
279     /// </summary>  
280     /// <param name="cmd">Command对象</param>  
281     /// <param name="conn">数据库连接对象</param>  
282     /// <param name="trans">事务对象</param>  
283     /// <param name="cmdType">Command类型</param>  
284     /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
285     /// <param name="cmdParms">命令参数集合</param>  
286     private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
287     {
288         if (conn.State != ConnectionState.Open) 
289             conn.Open();
290 
291         cmd.Connection = conn;
292         cmd.CommandText = cmdText;
293        
294         if (trans != null)
295             cmd.Transaction = trans;
296 
297         cmd.CommandType = cmdType;
298 
299         if (cmdParms != null)
300         {
301             foreach (OracleParameter parm in cmdParms)
302                 cmd.Parameters.Add(parm);
303         }
304     }
305 
306     /// <summary>  
307     /// 将.NET日期时间类型转化为Oracle兼容的日期时间格式字符串  
308     /// </summary>  
309     /// <param name="date">.NET日期时间类型对象</param>  
310     /// <returns>Oracle兼容的日期时间格式字符串(如该字符串:TO_DATE('2007-12-1','YYYY-MM-DD'))</returns>  
311     public static string GetOracleDateFormat(DateTime date)
312     {
313         return "TO_DATE('" + date.ToString("yyyy-M-dd") + "','YYYY-MM-DD')";
314     }
315 
316     /// <summary>  
317     /// 将.NET日期时间类型转化为Oracle兼容的日期格式字符串  
318     /// </summary>  
319     /// <param name="date">.NET日期时间类型对象</param>  
320     /// <param name="format">Oracle日期时间类型格式化限定符</param>  
321     /// <returns>Oracle兼容的日期时间格式字符串(如该字符串:TO_DATE('2007-12-1','YYYY-MM-DD'))</returns>  
322     public static string GetOracleDateFormat(DateTime date, string format)
323     {
324         if (format == null || format.Trim() == "") format = "YYYY-MM-DD";
325         return "TO_DATE('" + date.ToString("yyyy-M-dd") + "','" + format + "')";
326     }
327 
328     /// <summary>  
329     /// 将指定的关键字处理为模糊查询时的合法参数值  
330     /// </summary>  
331     /// <param name="source">待处理的查询关键字</param>  
332     /// <returns>过滤后的查询关键字</returns>  
333     public static string HandleLikeKey(string source)
334     {
335         if (source == null || source.Trim() == "") return null;
336 
337         source = source.Replace("[", "[]]");
338         source = source.Replace("_", "[_]");
339         source = source.Replace("%", "[%]");
340 
341         return ("%" + source + "%");
342     }
343 
344 }