.net 链接及操作 Oracle

1.项目添加引用【.Net】选项卡，添加：System.Data.OracleClient

2.测试链接

            OracleConnection oc = new OracleConnection("Data Source=服务器地址:端口号/数据库名;User Id=登陆名;Password=密码;Integrated Security=no;");
            oc.Open();
            MessageBox.Show(oc.State.ToString());

3.

using System;
using System.Data;
using System.Data.OracleClient;

/// <summary>
/// OracleDbHelper 的摘要说明。
/// </summary>
public static class OracleDbHelper
{
    /// <summary>
    /// 获取连接字符串
    /// </summary>
    //public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
    public static string connectionString = "Data Source=服务器地址:端口号/数据库名;User Id=登陆名;Password=密码;Integrated Security=no;";
    /// <summary>
    /// 关闭数据库
    /// </summary>
    /// <param name="conn">连接</param>
    public static void CloseConnection(OracleConnection conn)
    {
        if (null != conn && conn.State != ConnectionState.Closed)
        {
            conn.Close();
        }
    }
    /// <summary>
    /// 关闭读取器
    /// </summary>
    /// <param name="sr">读取器</param>
    public static void CloseReader(OracleDataReader sr)
    {
        if (null != sr && sr.IsClosed != true)
        {
            sr.Close();
        }
    }



    /// <summary>
    /// 执行增删改的sql语句
    /// </summary>
    /// <param name="commandType">执行的类型</param>
    /// <param name="commandText">执行的语句</param>
    /// <param name="prms">所需的参数</param>
    /// <returns>影响的行数</returns>
    public static int ExecuteNonQuery(CommandType commandType, string commandText, params OracleParameter[] prms)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = new OracleConnection(connectionString);
        cmd.CommandTimeout = 0;
        cmd.CommandText = commandText;
        cmd.CommandType = commandType;
        if (null != prms && prms.Length > 0)
        {
            foreach (OracleParameter sp in prms)
            {
                cmd.Parameters.Add(sp);
            }
        }
        try
        {
            cmd.Connection.Open();
            return cmd.ExecuteNonQuery();
        }
        catch (Exception err)
        {
            throw new Exception(err.Message);
        }
        finally
        {
            CloseConnection(cmd.Connection);
        }
    }


    /// <summary>
    /// 执行查询的Sql语句
    /// </summary>
    /// <param name="commandType">执行的类型</param>
    /// <param name="commandText">执行的语句</param>
    /// <param name="prms">所需的参数</param>
    /// <returns>DataSet</returns>
    public static DataSet ExecuteDataSet(string commandText)
    {
        OracleConnection con = new OracleConnection(connectionString);
        OracleDataAdapter da = new OracleDataAdapter(commandText, con);

        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch (Exception err)
        {
            throw new Exception(err.Message);
        }
        finally
        {
            CloseConnection(da.SelectCommand.Connection);
        }
    }
    /// <summary>
    /// 执行查询的Sql语句
    /// </summary>
    /// <param name="commandType">执行的类型</param>
    /// <param name="commandText">执行的语句</param>
    /// <param name="prms">所需的参数</param>
    /// <returns>读取器</returns>
    public static OracleDataReader ExecuteDataReader(CommandType commandType, string commandText, params OracleParameter[] prms)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = new OracleConnection(connectionString);
        cmd.CommandText = commandText;
        cmd.CommandType = commandType;
        if (null != prms && prms.Length > 0)
        {
            foreach (OracleParameter sp in prms)
            {
                cmd.Parameters.Add(sp);
            }
        }
        try
        {
            cmd.Connection.Open();
            OracleDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sr;
        }
        catch (Exception err)
        {
            throw new Exception(err.Message);
        }
    }
    /// <summary>
    /// 返回单行单列的Sql语句
    /// </summary>
    /// <param name="commandType">执行的类型</param>
    /// <param name="commandText">执行的语句</param>
    /// <param name="prms">所需的参数</param>
    /// <returns>影响的行数</returns>
    public static string ExecuteScalar(CommandType commandType, string commandText, params OracleParameter[] prms)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = new OracleConnection(connectionString);
        cmd.CommandText = commandText;
        cmd.CommandType = commandType;
        if (null != prms && prms.Length > 0)
        {
            foreach (OracleParameter sp in prms)
            {
                cmd.Parameters.Add(sp);
            }
        }
        try
        {
            cmd.Connection.Open();
            string id = cmd.ExecuteScalar().ToString();
            return id;
        }
        catch (Exception err)
        {
            throw new Exception(err.Message);
        }
        finally
        {
            CloseConnection(cmd.Connection);
        }
    }


    /// <summary>
    /// 返回cmd的参数
    /// </summary>
    /// <returns></returns>
    public static OracleParameter GetCmdParameter(string name, SqlDbType dt, object value)
    {
        OracleParameter sp = new OracleParameter(name, dt);
        sp.Value = value;
        return sp;
    }

    public static int ExecuteNonQuery(string sql, DataTable myDataTable)
    {
        OracleConnection con = new OracleConnection(connectionString);
        OracleDataAdapter da = new OracleDataAdapter(sql, con);
        int i = 0;
        try
        {
            i = da.Update(myDataTable);

        }
        catch (Exception err)
        {
            ;
        }
        finally
        {
            CloseConnection(da.SelectCommand.Connection);
        }
        return i;
    }
}