using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace FileManagerApp
{
    public class SqlHelper
    {
        #region 执行查询语句
        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SelectString">查询语句</param>
        /// <param name="ConnString">Oracle数据库连接串</param>
        /// <returns>返回查询结果</returns>
        public DataTable DoSelect(string SelectString, string ConnString)
        {
            DataTable dt = new DataTable();
            OracleConnection conn = new OracleConnection(ConnString);
            try
            {
                conn.Open();
                OracleDataAdapter oda = new OracleDataAdapter(SelectString, conn);
                oda.Fill(dt);
                if (dt.Rows.Count == 0)
                {
                    dt = null;
                }
            }
            catch (Exception ex)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        #endregion

        #region 执行查询语句
        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SelectString">查询语句</param>
        /// <param name="ConnString">Oracle数据库连接串</param>
        /// <returns>返回查询结果</returns>
        public DataTable DoSelect1(string SelectString, string ConnString)
        {
            DataTable dt = new DataTable();
            OracleConnection conn = new OracleConnection(ConnString);
            try
            {
                conn.Open();
                OracleDataAdapter oda = new OracleDataAdapter(SelectString, conn);
                oda.Fill(dt);
                if (dt.Rows.Count == 0)
                {
                    //dt = null;
                }
            }
            catch
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        #endregion

        #region 执行查询语句
        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SelectString">查询语句</param>
        /// <param name="ConnString">Oracle数据库连接串</param>
        /// <returns>返回查询结果</returns>
        public string DoSelect2(string SelectString, string ConnString)
        {
            string result = string.Empty;
            DataTable dt = new DataTable();
            OracleConnection conn = null;
            try
            {
                conn = new OracleConnection(ConnString);
                conn.Open();
            }
            catch (Exception ex)
            {
                result = "open:" + ex.Message;
            }
            if (result == string.Empty)
            {
                try
                {
                    OracleDataAdapter oda = new OracleDataAdapter(SelectString, conn);
                    oda.Fill(dt);
                }
                catch (Exception ex)
                {
                    result = "select:" + ex.Message;
                }
            }
            if (result == string.Empty)
            {
                if (dt != null)
                {
                    result = dt.Rows.Count.ToString();
                }
                else
                {
                    if (SelectString == null)
                    {
                        result = "SelectString is null";
                    }
                    else if (ConnString == null)
                    {
                        result = "ConnString is null";
                    }
                }
            }

            conn.Close();
            conn.Dispose();
            return result;
        }
        #endregion

        #region 执行操作语句
        /// <summary>
        /// 执行操作语句
        /// </summary>
        /// <param name="CommandString">操作语句</param>
        /// <param name="ConnString">Oracle数据库连接串</param>
        /// <returns>返回执行成功条数</returns>
        public int DoCommand(string CommandString, string ConnString)
        {
            int Result = 0;
            OracleConnection conn = new OracleConnection(ConnString);
            try
            {
                conn.Open();
                OracleCommand comm = new OracleCommand(CommandString, conn);
                Result = comm.ExecuteNonQuery();
            }
            catch
            {
                Result = -1;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return Result;
        }
        #endregion

        #region 执行过程
        /// <summary>
        /// 执行过程
        /// </summary>
        /// <param name="ProcedureName">过程名（有参数的要带参数传入）</param>
        /// <param name="ConnString">Oracle数据库连接串</param>
        /// <returns>返回执行是否成功</returns>
        public string DoProcedure(string ProcedureName, string ConnString)
        {
            string Result = "执行成功";
            OracleConnection conn = new OracleConnection(ConnString);
            try
            {
                conn.Open();
                OracleCommand comm = new OracleCommand(ProcedureName, conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.ExecuteNonQuery();
                Result = "执行成功";
            }
            catch (Exception ex)
            {
                Result = "执行失败：" + ex.Message;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return Result;
        }
        #endregion


        public bool InsertClob2Oracle(string CommandTXT, string clob_name, string contant)
        {
            OracleConnection conn = new OracleConnection(System.Web.Configuration.WebConfigurationManager.AppSettings["connstring"]);
            bool flag = false;
            try
            {
                conn.Open();

                OracleCommand cmd = new OracleCommand(CommandTXT, conn);

                cmd.BindByName = true;

                cmd.Parameters.Add(new OracleParameter(clob_name, contant));


                cmd.ExecuteReader();

                flag = true;

            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
            }

            return flag;
        }
    }
}