KN_CAMP_SOFTWARE_TEST/Code/ClassLib/RoadFlow/RoadFlow.Data.ORACLE/DBHelper.cs

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace RoadFlow.Data.ORACLE
{
    /// <summary>
    /// SQLSERVER助手类
    /// </summary>
    public class DBHelper
    {
        private string connectionString;

        public DBHelper()
        {
            this.connectionString = RoadFlow.Utility.Config.PlatformConnectionStringORACLE;
        }
        public DBHelper(string connString)
        {
            this.connectionString = connString;
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return this.connectionString; }
        }

        /// <summary>
        /// 释放连接
        /// </summary>
        public void Dispose()
        {

        }

        ///// <summary>
        ///// 得到一个MySqlDataReader
        ///// </summary>
        ///// <param name="sql">sql语句</param>
        ///// <returns></returns>
        //public MySqlDataReader GetDataReader(string sql)
        //{
        //    MySqlConnection conn = new MySqlConnection(ConnectionString);
        //    conn.Open();
        //    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
        //    {
        //        cmd.Prepare();
        //        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //    }
        //}
        /// <summary>
        /// 得到一个MySqlDataReader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataReader(string sql)
        {
            var dt = GetDataTable(sql);
            return dt;
        }

        ///// <summary>
        ///// 得到一个MySqlDataReader
        ///// </summary>
        ///// <param name="sql">sql语句</param>
        ///// <returns></returns>
        //public MySqlDataReader GetDataReader(string sql, MySqlParameter[] parameter)
        //{
        //    MySqlConnection conn = new MySqlConnection(ConnectionString);
        //    conn.Open();
        //    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
        //    {
        //        if (parameter != null && parameter.Length > 0)
        //            cmd.Parameters.AddRange(parameter);
        //        MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //        //cmd.Parameters.Clear();
        //        return dr;
        //    }
        //}

        /// <summary>
        /// 得到一个MySqlDataReader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataReader(string sql, MySqlParameter[] parameter)
        {
            var dt = GetDataTable(sql, parameter);
            return dt;
        }


        /// <summary>
        /// 得到一个DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                DataTable dt = new DataTable();
                try
                {

                    connection.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        MySqlDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        dr.Close();
                        dr.Dispose();
                        return dt;
                    }
                }
                catch (Exception ex)
                {
                    return dt;
                }
            }
        }

        /// <summary>
        /// 得到一个DataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, MySqlParameter[] parameter)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                DataTable dt = new DataTable();
                try
                {
                    connection.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                    {
                        if (parameter != null && parameter.Length > 0)
                            cmd.Parameters.AddRange(parameter);
                        MySqlDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        dr.Close();
                        dr.Dispose();
                        cmd.Parameters.Clear();
                        return dt;
                    }

                }
                catch (Exception ex)
                {
                    return dt;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                        MySqlConnection.ClearPool(connection);//加入此行可释放连接
                        connection.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 得到数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, connection))
                    {
                        dap.Fill(ds);
                        return ds;
                    }

                }
                catch (Exception ex)
                {
                    return ds;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                        MySqlConnection.ClearPool(connection);//加入此行可释放连接
                        connection.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        //cmd.Prepare();
                        return cmd.ExecuteNonQuery();
                    }

                }
                catch (Exception ex)
                {
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行SQL(事务)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(List<string> sqlList)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        int i = 0;
                        cmd.Connection = conn;
                        foreach (string sql in sqlList)
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sql;
                            //cmd.Prepare();
                            i += cmd.ExecuteNonQuery();
                        }
                        return i;
                    }

                }
                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 执行带参数的SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute2(string sql, MySqlParameter[] parameter)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (parameter != null && parameter.Length > 0)
                            cmd.Parameters.AddRange(parameter);
                        int i = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        //cmd.Prepare();
                        return i;
                    }

                }
                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }
        /// <summary>
		/// 执行SQL语句，返回影响的记录数
		/// </summary>
		/// <param name="SQLString">SQL语句</param>
		/// <returns>影响的记录数</returns>
		public int Execute(string SQLString, params MySqlParameter[] parameter)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        try
                        {
                            conn.Open();
                            cmd.Connection = conn;
                            cmd.CommandText = SQLString;
                            // cmd.CommandType = CommandType.Text;//cmdType;
                            if (parameter != null && parameter.Length > 0)
                                cmd.Parameters.AddRange(parameter);
                            int rows = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            //cmd.Prepare();
                            return rows;
                        }
                        catch (Exception E)
                        {
                            throw new Exception(E.Message);
                        }
                    }

                }
                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }
        /// <summary>
        /// 执行SQL(事务)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Execute(List<string> sqlList, List<MySqlParameter[]> parameterList)
        {
            if (sqlList.Count > parameterList.Count)
            {
                throw new Exception("参数错误");
            }
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {

                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        int i = 0;
                        cmd.Connection = conn;
                        for (int j = 0; j < sqlList.Count; j++)
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sqlList[j];
                            if (parameterList[j] != null && parameterList[j].Length > 0)
                            {
                                cmd.Parameters.AddRange(parameterList[j]);
                            }
                            i += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            //cmd.Prepare();
                        }
                        return i;
                    }
                }
                catch (Exception ex)
                {
                    return 0;
                }

                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 得到一个字段的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        object obj = cmd.ExecuteScalar();
                        //cmd.Prepare();
                        return obj != null ? obj.ToString() : string.Empty;
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 得到一个字段的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sql, MySqlParameter[] parameter)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (parameter != null && parameter.Length > 0)
                            cmd.Parameters.AddRange(parameter);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        //cmd.Prepare();
                        return obj != null ? obj.ToString() : string.Empty;
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }
        /// <summary>
        /// 得到一个字段的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetFieldValue(string sql)
        {
            return ExecuteScalar(sql);
        }
        /// <summary>
        /// 得到一个字段的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public string GetFieldValue(string sql, MySqlParameter[] parameter)
        {
            return ExecuteScalar(sql, parameter);
        }

        /// <summary>
        /// 获取一个sql的字段名称
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public string GetFields(string sql, MySqlParameter[] param)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    System.Text.StringBuilder names = new System.Text.StringBuilder(500);
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            names.Append("[" + dr.GetName(i) + "]" + (i < dr.FieldCount - 1 ? "," : string.Empty));
                        }
                        cmd.Parameters.Clear();
                        dr.Close();
                        dr.Dispose();
                        //cmd.Prepare();
                        return names.ToString();
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 获取一个sql的字段名称
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tableName">表名 </param>
        /// <returns></returns>
        public string GetFields(string sql, MySqlParameter[] param, out string tableName)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                try
                {
                    conn.Open();
                    System.Text.StringBuilder names = new System.Text.StringBuilder(500);
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (param != null && param.Length > 0)
                            cmd.Parameters.AddRange(param);
                        MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                        tableName = dr.GetSchemaTable().TableName;
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            names.Append("[" + dr.GetName(i) + "]" + (i < dr.FieldCount - 1 ? "," : string.Empty));
                        }
                        cmd.Parameters.Clear();
                        dr.Close();
                        dr.Dispose();
                        //cmd.Prepare();
                        return names.ToString();
                    }

                }
                catch (Exception ex)
                {
                    tableName = "";
                    return "";
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 得到分页sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetPaerSql(string sql, int size, int number, out long count, MySqlParameter[] param = null)
        {
            string count1 = GetFieldValue(string.Format("select count(*) from ({0}) t ", sql), param);
            long i;
            count = count1.IsLong(out i) ? i : 0;
            number = number < 1 ? 1 : number;
            StringBuilder sql1 = new StringBuilder();
            sql1.Append(sql);
            sql1.AppendFormat(" LIMIT {0} , {1}", (number - 1) * size, size);

            return sql1.ToString();
        }

        /// <summary>
        /// 得到分页sql
        /// </summary>
        /// <param name="table">表名称</param>
        /// <param name="fileds">查询列</param>
        /// <param name="where">查询条件</param>
        /// <param name="order">排序字段</param>
        /// <param name="size">每页大小</param>
        /// <param name="number">当前页</param>
        /// <param name="count">查询结果总页数</param>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public string GetPaerSql(string table, string fileds, string where, string order, int size, int number, out long count, MySqlParameter[] param = null)
        {
            string where1 = string.Empty;
            if (where.IsNullOrEmpty())
            {
                where1 = "";
            }
            else
            {
                where1 = where.Trim();
                if (where1.StartsWith("and", StringComparison.CurrentCultureIgnoreCase))
                {
                    where1 = where1.Substring(3);
                }
            }
            string where2 = where1.IsNullOrEmpty() ? "" : " where " + where1;
            string sql = string.Format("select bm.{0} from {2} bm {3}", fileds, order, table, where2);


            string count1 = GetFieldValue(string.Format("select count(*) from {0} {1}", table, where2), param);
            long i;
            count = count1.IsLong(out i) ? i : 0;

            StringBuilder sql1 = new StringBuilder();
            sql1.Append(sql);
            sql1.AppendFormat(" LIMIT  {0} , {1}", ((number < 1 ? 1 : number) - 1) * size, size);

            return sql1.ToString();
        }

    }
}