ADO.NET_08_OracleHelper
using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace Dao.DBUtility
{
public abstract class OracleHelper
{
public static readonly string ConnectionStringLocalTransaction =
ConfigurationManager.ConnectionStrings["OraConnString1"].ConnectionString;
public static readonly string ConnectionStringInventoryDistributedTransaction =
ConfigurationManager.ConnectionStrings["OraConnString2"].ConnectionString;
public static readonly string ConnectionStringOrderDistributedTransaction =
ConfigurationManager.ConnectionStrings["OraConnString3"].ConnectionString;
public static readonly string ConnectionStringProfile =
ConfigurationManager.ConnectionStrings["OraProfileConnString"].ConnectionString;
public static readonly string ConnectionStringMembership =
ConfigurationManager.ConnectionStrings["OraMembershipConnString"].ConnectionString;
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static int ExecuteNonQuery(string connectionString,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection connection = new OracleConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static int ExecuteNonQuery(OracleTransaction trans,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static int ExecuteNonQuery(OracleConnection connection,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public static OracleDataReader ExecuteReader(string connectionString,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public static object ExecuteScalar(string connectionString,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection conn = new OracleConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
public static object ExecuteScalar(OracleTransaction transaction,
CommandType commandType,
string commandText,
params OracleParameter[] commandParameters)
{
if(transaction == null)
throw new ArgumentNullException("事务");
if(transaction != null && transaction.Connection == null)
throw new ArgumentException("事务以被回滚或提交，请提供一个打开的事务。", "事务");
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
public static object ExecuteScalar(OracleConnection connectionString,
CommandType cmdType,
string cmdText,
params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
public static OracleParameter[] GetCachedParameters(string cacheKey)
{
OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
private static void PrepareCommand(OracleCommand cmd,
OracleConnection conn,
OracleTransaction trans,
CommandType cmdType,
string cmdText,
OracleParameter[] commandParameters)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (trans != null)
cmd.Transaction = trans;
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
public static string OraBit(bool value)
{
if(value)
return "Y";
else
return "N";
}
public static bool OraBool(string value)
{
if(value.Equals("Y"))
return true;
else
return false;
}
}
}
说明
(1) 这个抽象类对于初学者非常有学习价值；
(2) OracleHelper是Pet Shop中的一个抽象类，这个类是基本的，封装了数据库的操作。Pet Shop相当于一个框架，在.NET和JAVA中都可以使用；
(3) 之所以这样说，是因为，在Pet Shop中返回的数据集是IList，而不是DataSet或是DataTable；
(4) 在NHibernate中，查询返回的数据通常都是IList，但实际中有些数据控件在数据绑定上对IList的支持并不是很好，可以将IList转换成DataSet或DataTable。
(5) 该抽象类重载了三个ExecuteNonQuery()成员函数，用于删除修改编辑等操作，区别只在第一个参数。
(6) 这三个重载函数的区别是，第一个参数是连接字符串的一个，不带事务处理，不需要创建OracleConnection对象；第二个参数是事务，需要用OracleConnection创建事务和Oraclecommand对象；第三个参数是OracleConnection对象，不带事务；
(7) 该抽象类还有一个ExecuteReader()和三个重载的ExecuteScalar()成员函数，用于查询；
(8) 该抽象类中，没有返回DataTable和DataSet的方法，不知道为什么；
(9) 该抽象类中的PrepareCommand()成员函数，主要是将对个参数OracleParameter，添加到Oraclecommand.Parameters中。
(10) 该抽象类中的CacheParameters()和GetCachedParameters()成员函数是在缓存中，保存和获取参数OracleParameter；
(11) 该抽象类中的OraBit()和OraBool()成员函数是true和false在Y和N之间转换；
(12) 该抽象类中的五个静态只读变量，要在Web.Config文件的<connectionStrings></connectionStrings>中添加；
(13) 最后，封装操作的一个目的是，OracleCommand的操作比较麻烦。你自己可以到网上下载Pet Shop，它是免费的。
