.net/c#/oracle操作类实例代码
using system;
using system.data;
using system.collections.generic;
using system.configuration;
using system.data.oracleclient;
using system.text;
using system.io;
/// <summary>
/// oracle数据库操作类
/// </summary>
internal static class oraclehelper
{
//数据库连接字符串
private readonly static string connstr = configurationmanager.connectionstrings["connectionstrings"].connectionstring;
/// <summary>
/// 执行数据库查询操作,返回受影响的行数
/// </summary>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作影响的数据行数</returns>
internal static int executenonquery(string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = new oracleconnection(connstr);
int result = 0;
try
{
preparecommand(command, connection, null,commandtype.text, cmdtext, commandparameters);
result = command.executenonquery();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库事务查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdtype">command类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前事务查询操作影响的数据行数</returns>
internal static int executenonquery(oracletransaction transaction, commandtype cmdtype, string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = transaction.connection;
int result = 0;
try
{
preparecommand(command, connection, transaction, cmdtype, cmdtext, commandparameters);
result = command.executenonquery();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
transaction.dispose();
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库查询操作,返回受影响的行数
/// </summary>
/// <param name="connection">oracle数据库连接对象</param>
/// <param name="cmdtype">command类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作影响的数据行数</returns>
internal static int executenonquery(oracleconnection connection, commandtype cmdtype, string cmdtext, params oracleparameter[] commandparameters)
{
if (connection == null) throw new argumentnullexception("当前数据库连接不存在");
oraclecommand command = new oraclecommand();
int result = 0;
try
{
preparecommand(command, connection, null, cmdtype, cmdtext, commandparameters);
result = command.executenonquery();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库查询操作,返回oracledatareader类型的内存结果集
/// </summary>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作返回的oracledatareader类型的内存结果集</returns>
internal static oracledatareader executereader(string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = new oracleconnection(connstr);
oracledatareader reader = null;
try
{
preparecommand(command, connection, null,commandtype.text, cmdtext, commandparameters);
reader = command.executereader(commandbehavior.closeconnection);
command.parameters.clear();
return reader;
}
catch
{
command.dispose();
connection.close();
throw;
}
}
/// <summary>
/// 执行数据库查询操作,返回dataset类型的结果集
/// </summary>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作返回的dataset类型的结果集</returns>
internal static dataset executedataset(string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = new oracleconnection(connstr);
dataset dataset = null;
try
{
preparecommand(command, connection, null,commandtype.text, cmdtext, commandparameters);
oracledataadapter adapter = new oracledataadapter();
adapter.selectcommand = command;
dataset = new dataset();
adapter.fill(dataset);
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return dataset;
}
/// <summary>
/// 执行数据库查询操作,返回datatable类型的结果集
/// </summary>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作返回的datatable类型的结果集</returns>
internal static datatable executedatatable(string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = new oracleconnection(connstr);
datatable table = null;
try
{
preparecommand(command, connection, null,commandtype.text, cmdtext, commandparameters);
oracledataadapter adapter = new oracledataadapter();
adapter.selectcommand = command;
table = new datatable();
adapter.fill(table);
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return table;
}
/// <summary>
/// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值
/// </summary>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>
internal static object executescalar(string cmdtext, params oracleparameter[] commandparameters)
{
oraclecommand command = new oraclecommand();
oracleconnection connection = new oracleconnection(connstr);
object result = null;
try
{
preparecommand(command, connection, null,commandtype.text, cmdtext, commandparameters);
result = command.executescalar();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库事务查询操作,返回结果集中位于第一行第一列的object类型的值
/// </summary>
/// <param name="transaction">一个已存在的数据库事务对象</param>
/// <param name="commandtype">命令类型</param>
/// <param name="commandtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前事务查询操作返回的结果集中位于第一行第一列的object类型的值</returns>
internal static object executescalar(oracletransaction transaction, commandtype commandtype, string commandtext, params oracleparameter[] commandparameters)
{
if (transaction == null) throw new argumentnullexception("当前数据库事务不存在");
oracleconnection connection = transaction.connection;
if (connection == null) throw new argumentexception("当前事务所在的数据库连接不存在");
oraclecommand command = new oraclecommand();
object result = null;
try
{
preparecommand(command, connection, transaction, commandtype, commandtext, commandparameters);
result = command.executescalar();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
transaction.dispose();
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="cmdtype">command类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
/// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>
internal static object executescalar(oracleconnection connection, commandtype cmdtype, string cmdtext, params oracleparameter[] commandparameters)
{
if (connection == null) throw new argumentexception("当前数据库连接不存在");
oraclecommand command = new oraclecommand();
object result = null;
try
{
preparecommand(command, connection, null, cmdtype, cmdtext, commandparameters);
result = command.executescalar();
command.parameters.clear();
}
catch
{
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
return result;
}
/// <summary>
/// 执行数据库命令前的准备工作
/// </summary>
/// <param name="command">command对象</param>
/// <param name="connection">数据库连接对象</param>
/// <param name="trans">事务对象</param>
/// <param name="cmdtype">command类型</param>
/// <param name="cmdtext">oracle存储过程名称或pl/sql命令</param>
/// <param name="commandparameters">命令参数集合</param>
private static void preparecommand(oraclecommand command, oracleconnection connection, oracletransaction trans, commandtype cmdtype, string cmdtext, oracleparameter[] commandparameters)
{
if (connection.state != connectionstate.open) connection.open();
command.connection = connection;
command.commandtext = cmdtext;
command.commandtype = cmdtype;
if (trans != null) command.transaction = trans;
if (commandparameters != null)
{
foreach (oracleparameter parm in commandparameters)
command.parameters.add(parm);
}
}
/// <summary>
/// 将.net日期时间类型转化为oracle兼容的日期时间格式字符串
/// </summary>
/// <param name="date">.net日期时间类型对象</param>
/// <returns>oracle兼容的日期时间格式字符串（如该字符串：to_date('2007-12-1','yyyy-mm-dd')）</returns>
internal static string getoracledateformat(datetime date)
{
return "to_date('" + date.tostring("yyyy-m-dd") + "','yyyy-mm-dd')";
}
/// <summary>
/// 将.net日期时间类型转化为oracle兼容的日期格式字符串
/// </summary>
/// <param name="date">.net日期时间类型对象</param>
/// <param name="format">oracle日期时间类型格式化限定符</param>
/// <returns>oracle兼容的日期时间格式字符串（如该字符串：to_date('2007-12-1','yyyy-mm-dd')）</returns>
internal static string getoracledateformat(datetime date, string format)
{
if (format == null || format.trim() == "") format = "yyyy-mm-dd";
return "to_date('" + date.tostring("yyyy-m-dd") + "','" + format + "')";
}
/// <summary>
/// 将指定的关键字处理为模糊查询时的合法参数值
/// </summary>
/// <param name="source">待处理的查询关键字</param>
/// <returns>过滤后的查询关键字</returns>
internal static string handlelikekey(string source)
{
if (source == null || source.trim() == "") return null;
source = source.replace("[", "[]]");
source = source.replace("_", "[_]");
source = source.replace("%", "[%]");
return ("%" + source + "%");
}
/// <summary>
/// 将文本内容写入到数据库的clob字段中（不可用：报连接被关闭的异常）
/// </summary>
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="table">数据库表名称</param>
/// <param name="where">指定的where条件语句</param>
/// <param name="clobfield">clob字段的名称</param>
/// <param name="content">要写入的文本内容</param>
internal static void writeclob(string table, string where, string clobfield, string content)
{
if (string.isnullorempty(connstr) || string.isnullorempty(table) || string.isnullorempty(clobfield)) return;
using (oracleconnection connection = new oracleconnection(connstr))
{
oraclecommand command = null;
try
{
connection.open();
command = connection.createcommand();
command.commandtext = "select " + clobfield + " from " + table + " where " + where + " for update";
oracledatareader reader = command.executereader();
if (reader != null && reader.hasrows)
{
reader.read();
command.transaction = command.connection.begintransaction();
oraclelob lob = reader.getoraclelob(0);
byte[] buffer = encoding.unicode.getbytes(content);
if (lob != oraclelob.null) lob.erase();
lob.write(buffer, 0, ((buffer.length % 2 == 0) ? buffer.length : (buffer.length - 1)));
command.transaction.commit();
reader.close();
}
}
catch
{
command.transaction.rollback();
throw;
}
finally
{
command.dispose();
connection.close();
connection.dispose();
}
}
}
/// <summary>
/// 从数据库中读取clob字段的内容并进行输出
/// </summary>
/// <param name="connectionstring">数据库连接字符串</param>
/// <param name="table">数据库表名称</param>
/// <param name="where">指定的where条件语句</param>
/// <param name="clobfield">clob字段的名称</param>
/// <param name="output">保存内容输出的字符串变量</param>
internal static void readclob(string connectionstring, string table, string where, string clobfield, ref string output)
{
if (string.isnullorempty(connectionstring) || string.isnullorempty(table) || string.isnullorempty(clobfield)) return;
using (oracleconnection connection = new oracleconnection(connectionstring))
{
oraclecommand command = null;
streamreader stream = null;
try
{
connection.open();
command = connection.createcommand();
command.commandtext = "select " + clobfield + " from " + table + " where " + where;
oracledatareader reader = command.executereader();
if (reader != null && reader.hasrows)
{
reader.read();
command.transaction = command.connection.begintransaction();
oraclelob lob = reader.getoraclelob(0);
if (lob != oraclelob.null)
{
stream = new streamreader(lob, encoding.unicode);
output = stream.readtoend().trim();
command.transaction.commit();
reader.close();
}
}
}
catch
{
command.transaction.rollback();
throw;
}
finally
{
stream.close();
command.dispose();
connection.close();
connection.dispose();
}
}
}
}
