Oracle数据库连接帮助类

帮助类

/// <summary>
    /// OracleDBHelper 的摘要说明
    /// </summary>
    public class OracleDBHelper
    {
        //Oracle 数据库连接
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["OracleDataSource"].ConnectionString;


        private static DataTable GetDataTable(OracleConnection conn, string sql, int timeOut = 40)
        {
            var cmd = new OracleCommand(sql, conn) { CommandType = CommandType.Text, CommandTimeout = timeOut };
            var da = new OracleDataAdapter(cmd);
            var dt = new DataTable("dataTable");
            da.Fill(dt);
            return dt;
        }

        private static void ExecuteNonQuery(OracleConnection conn, CommandType commandType, string sql, OracleParameter[] paras, int timeOut = 40)
        {
            var cmd = new OracleCommand(sql, conn) { CommandType = commandType, CommandTimeout = timeOut };
            cmd.Parameters.AddRange(paras);
            cmd.ExecuteNonQuery();
        }

        private static void ExecuteNonQuery(OracleConnection conn, OracleTransaction tran, string sql)
        {
            var cmd = new OracleCommand(sql, conn) { CommandType = CommandType.Text, Transaction = tran };
            cmd.ExecuteNonQuery();
        }
        private static void ExecuteNonQuery(OracleConnection conn, CommandType commandType, string sql, int timeOut = 40)
        {
            var cmd = new OracleCommand(sql, conn) { CommandType = CommandType.Text, CommandTimeout = timeOut };
            cmd.ExecuteNonQuery();
        }
        private static OracleDataReader ExecuteDataReader(OracleConnection conn, string sql, int timeOut = 40)
        {
            var cmd = new OracleCommand(sql, conn) { CommandType = CommandType.Text, CommandTimeout = timeOut };
            return cmd.ExecuteReader();
        }
    }

使用

 private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["OracleDataSource"].ConnectionString;

• 查询

using (var conn = new OracleConnection(ConnectionString))
{
    conn.Open();
    try
    {
        var sql = "";
        DataTable dt = GetDataTable(conn, sql);
    }
    catch (Exception ex)
    {
        
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
            conn.Close();
    }
}

• 更新

using (var conn = new OracleConnection(ConnectionString))
{
    conn.Open();
    //using (OracleTransaction trans = conn.BeginTransaction())//事务
    {
        try
        {
            foreach (var l in list)
            {
                var sql = "";
                //ExecuteNonQuery(conn, trans, sql);
                ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            //trans.Commit();
            return 1;
        }
        catch (Exception ex)
        {
            //trans.Rollback();
            return 0;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
}

• 执行Procedure

using (var conn = new OracleConnection(ConnectionString))
{
    conn.Open();
    var parms = new[]
    {
        new OracleParameter("a", OracleType.Number, 10),
        new OracleParameter("b", OracleType.VarChar),
        new OracleParameter("c", OracleType.DateTime),
        new OracleParameter("d", OracleType.Number)
    };

    parms[0].Value = recordId;
    parms[1].Value = deviceId;
    parms[2].Value = startTime;
    parms[4].Direction = ParameterDirection.Output;//输出

    ExecuteNonQuery(conn, CommandType.StoredProcedure, "Procedure_Test", parms);
    if (parms[4].Value != DBNull.Value)
    {
        return Convert.ToDouble(parms[4].Value);
    }
    return 0;
}

连接配置

<connectionStrings>
    <!--数据库配置-->
    <!--<add name="SqlDataSource" connectionString="Data Source=10.2.137.28;Database=AAATEST;User ID=sa;Password=tdsoft;Pooling=true;connection Timeout=600;Max Pool Size = 512;" providerName="System.Data.SqlClient" />-->
    <!--<add name="OracleDataSource" connectionString="Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 119.39.124.93)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)));User ID=root;Password=TEST" providerName="System.Data.OracleClient" />-->
    <add name="OracleDataSource" connectionString="Data Source=tonglin;User ID=root;Password=123456;Unicode=True" providerName="System.Data.OracleClient" />
  </connectionStrings>