.NET/C#/Oracle数据库操作类实例代码

.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();  
            }  
        }  
    }  
}