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 }