c# Oracle PL/SQL

一、plsql链接

二、执行sql语句

       #region 执行SQL语句,返回DataTable;只用来执行查询结果比较少的情况
        public static DataTable ExecuteDataTable(string sql)
        {
            string connStr = connectionString;
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    conn.Close();
                    return datatable;
                }
            }
        }
        public static DataTable ExecuteDataTable(string sql, params OracleParameter[] parameters)
        {
            string connStr = connectionString;
            using (OracleConnection conn = new OracleConnection(connStr))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    conn.Close();
                    return datatable;
                }
            }
        }
        #endregion

三、判断表是否存在

public static readonly string connectionString = ConfigurationSettings.AppSettings["BI_Oracle"];

#region 判断数据库表是否存在，通过指定专用的连接字符串，执行一个不需要返回值的SqlCommand命令。
  /// <summary>
  /// 判断数据库表是否存在，返回页头，通过指定专用的连接字符串，执行一个不需要返回值的SqlCommand命令。
  /// </summary>
  /// <param name="tablename">bhtsoft表</param>
  /// <returns></returns>
public static bool CheckExistsTable(string tablename)
{

    String tableNameStr = "select count(*) from ALL_TABLES where TABLE_NAME = upper('" + tablename + "')";
    Console.WriteLine("sql = " + tableNameStr);

    //using (SqlConnection con = new SqlConnection(ConnectionString))
    using (OracleConnection con = new OracleConnection(connectionString))
    {
        if (con.State == System.Data.ConnectionState.Closed)
            con.Open();
        OracleCommand cmd = new OracleCommand(tableNameStr, con);
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        if (result == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
} 
#endregion

四、执行insert之类的语句，返回受影响的行

        #region 执行SQL语句,返回受影响行数
        public static int ExecuteNonQuery(string strSql)
        {
            int result = 0;
            using (OracleConnection con = new OracleConnection(connectionString))
            {
                if (con.State == System.Data.ConnectionState.Closed)
                    con.Open();
                using (OracleCommand cmd = new OracleCommand(strSql, con))
                {
                    Console.WriteLine("strSql = " + strSql);
                    result = cmd.ExecuteNonQuery();

                }

                con.Close();
            }
            return result;
        }
        #endregion