OracleDatabase.cs

using System;
using System.Data;// DataTable
using System.Configuration;// ConfigurationManager
using Oracle.DataAccess.Client;// OracleConnection,OracleCommand,OracleDataReader,OracleDataAdapter
using System.Collections;// ArrayList

/// <summary>
/// OracleDatabase 的摘要说明
/// </summary>

/// <example>
/// Web.config 中配置如下:
/// <code>
/// <connectionStrings>
/// 	<add name="link_sample" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SID=xxxx)));User Id=xxxx;Password=xxxx;" providerName="System.Data.OracleClient" />
/// </connectionStrings>
/// </code>
/// </example>
public class OracleDatabase
{
    // 构造函数
    public OracleDatabase() { }

    // 连接状态检测
    public bool ConnectionStatus(string connStr)
    {
        bool result = false;
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
        try
        {
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select 1 from dual";// 连接测试语句
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Close();
            result = true;
        }
        catch
        {
            result = false;
        }
        finally
        {
            conn.Close();
        }
        return result;
    }

    // 执行SQL并获取结果,重载
    public string Query(string connStr, string sql)
    {
        string result = "";
        try
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            cmd.InitialLONGFetchSize = -1;// 设定为抓取不限长度的Long字段
            conn.Open();
            cmd.CommandText = sql;
            OracleDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = dr[0].ToString();
            }
            dr.Close();
            conn.Close();
        }
        catch
        {
        }
        return result;
    }

    // 执行SQL并获取结果,重载
    public string Query(string connStr, string sql, int num)
    {
        string result = "";
        try
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            cmd.InitialLONGFetchSize = -1;// 设定为抓取不限长度的Long字段
            conn.Open();
            cmd.CommandText = sql;
            OracleDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = dr[num].ToString();
            }
            dr.Close();
            conn.Close();
        }
        catch
        {
        }
        return result;
    }

    // 执行SQL并确认,重载
    public bool Perform(string connStr, string sql)
    {
        bool result = false;
        try
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = sql;
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Close();
            conn.Close();
            result = true;
        }
        catch
        {
        }
        return result;
    }

    // 执行SQL并确认,重载
    public bool Perform(string connStr, string sql, out string err)
    {
        bool result = false;
        err = null;
        try
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = sql;
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Close();
            conn.Close();
            result = true;
        }
        catch (Exception ex)
        {
            err = ex.Message;
        }
        return result;
    }

    // 执行SQL并获取数据集
    public DataSet GetDataSet(string connStr, string sql)
    {
        DataSet ds = new DataSet();
        try
        {
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(ds);
        }
        catch
        {
        }
        return ds;
    }

    // 执行SQL并获取数据表
    public DataTable GetDataTable(string connStr, string sql)
    {
        DataTable dt = new DataTable();
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
        try
        {
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            da.Fill(dt);
        }
        catch
        {
        }
        return dt;
    }

    // 执行SQL并获取数据列
    public ArrayList GetList(string connStr, string sql)
    {
        ArrayList list = new ArrayList();
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[connStr].ConnectionString);
        OracleCommand cmd = conn.CreateCommand();
        conn.Open();
        cmd.InitialLONGFetchSize = -1;// 设定为抓取不限长度的Long字段
        cmd.CommandText = sql;
        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            list.Add(dr[0].ToString());// 每次读取后数据前推,因此只需读首行即可
        }
        dr.Close();
        conn.Close();
        return list;
    }

}