KN_CAMP_SOFTWARE_TEST/Code/ClassLib/RoadFlow/RoadFlow.Platform/DBConnection.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

using MySql.Data.MySqlClient;

namespace RoadFlow.Platform
{
    public class DBConnection
    {
        private RoadFlow.Data.Interface.IDBConnection dataDBConnection;
        public DBConnection()
        {
            this.dataDBConnection = Data.Factory.Factory.GetDBConnection();
        }

        /// <summary>
        /// 连接类型
        /// </summary>
        public enum Types
        {
            SqlServer,
            Oracle
        }

        /// <summary>
        /// 新增
        /// </summary>
        public int Add(RoadFlow.Data.Model.DBConnection model)
        {
            int i = dataDBConnection.Add(model);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 更新
        /// </summary>
        public int Update(RoadFlow.Data.Model.DBConnection model)
        {
            int i = dataDBConnection.Update(model);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<RoadFlow.Data.Model.DBConnection> GetAll(bool fromCache = false)
        {
            if (!fromCache)
            {
                return dataDBConnection.GetAll();
            }
            else
            {
                string key = RoadFlow.Utility.Keys.CacheKeys.DBConnnections.ToString();
                object obj = RoadFlow.Cache.IO.Opation.Get(key);
                if (obj != null && obj is List<RoadFlow.Data.Model.DBConnection>)
                {
                    return obj as List<RoadFlow.Data.Model.DBConnection>;
                }
                else
                {
                    var list = dataDBConnection.GetAll();
                    RoadFlow.Cache.IO.Opation.Set(key, list);
                    return list;
                }
            }
        }
        /// <summary>
        /// 查询单条记录
        /// </summary>
        public RoadFlow.Data.Model.DBConnection Get(Guid id)
        {
            return dataDBConnection.Get(id);
        }
        /// <summary>
        /// 删除
        /// </summary>
        public int Delete(Guid id)
        {
            int i = dataDBConnection.Delete(id);
            ClearCache();
            return i;
        }
        /// <summary>
        /// 查询记录条数
        /// </summary>
        public long GetCount()
        {
            return dataDBConnection.GetCount();
        }
        /// <summary>
        /// 连接类型
        /// </summary>
        public enum ConnTypes
        {
            SqlServer,
            Oracle
        }
        /// <summary>
        /// 得到所有数据连接类型的下拉选择
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetAllTypeOptions(string value = "")
        {
            StringBuilder options = new StringBuilder();
            var array = Enum.GetValues(typeof(ConnTypes));
            foreach (var arr in array)
            {
                options.AppendFormat("<option value=\"{0}\" {1}>{0}</option>", arr, arr.ToString() == value ? "selected=\"selected\"" : "");
            }
            return options.ToString();
        }
        /// <summary>
        /// 得到所有数据连接的下拉选择
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetAllOptions(string value = "")
        {
            var conns = GetAll(true);
            StringBuilder options = new StringBuilder();
            foreach (var conn in conns.OrderBy(p => p.Name))
            {
                options.AppendFormat("<option value=\"{0}\" {1}>{2}</option>", conn.ID,
                    string.Compare(conn.ID.ToString(), value, true) == 0 ? "selected=\"selected\"" : "", conn.Name);
            }
            return options.ToString();
        }
        /// <summary>
        /// 清除缓存
        /// </summary>
        public void ClearCache()
        {
            string key = RoadFlow.Utility.Keys.CacheKeys.DBConnnections.ToString();
            RoadFlow.Cache.IO.Opation.Remove(key);
        }

        /// <summary>
        /// 根据连接ID得到所有表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<string> GetTables(Guid id)
        {
            var allConns = GetAll(true);
            var conn = allConns.Find(p => p.ID == id);
            if (conn == null) return new List<string>();
            List<string> tables = new List<string>();

            tables = getTables_MySql(conn);
            return tables;
        }

        /// <summary>
        /// 得到所有字段
        /// </summary>
        /// <param name="id">连接ID</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public Dictionary<string, string> GetFields(Guid id, string table)
        {
            if (table.IsNullOrEmpty()) return new Dictionary<string, string>();
            var allConns = GetAll(true);
            var conn = allConns.Find(p => p.ID == id);
            if (conn == null) return new Dictionary<string, string>();

            Dictionary<string, string> fields = getFields_MySql(conn, table);

            return fields;
        }

        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="link_table_field"></param>
        /// <returns></returns>
        public string GetFieldValue(string link_table_field, Dictionary<string, string> pkFieldValue)
        {
            if (link_table_field.IsNullOrEmpty()) return "";
            string[] array = link_table_field.Split('.');
            if (array.Length != 3) return "";
            string link = array[0];
            string table = array[1];
            string field = array[2];
            var allConns = GetAll(true);
            Guid linkid;
            if (!link.IsGuid(out linkid)) return "";
            var conn = allConns.Find(p => p.ID == linkid);
            if (conn == null) return "";
            List<string> fields = new List<string>();

            string value = getFieldValue_MySql(conn, table, field, pkFieldValue);

            return value;
        }

        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="link_table_field"></param>
        /// <returns></returns>
        public string GetFieldValue(string link_table_field, string pkField, string pkFieldValue)
        {
            if (link_table_field.IsNullOrEmpty())
            {
                return "";
            }
            string[] array = link_table_field.Split('.');
            if (array.Length != 3)
            {
                return "";
            }
            string link = array[0];
            string table = array[1];
            string field = array[2];
            var allConns = GetAll(true);
            Guid linkid;
            if (!link.IsGuid(out linkid))
            {
                return "";
            }
            var conn = allConns.Find(p => p.ID == linkid);
            if (conn == null)
            {
                return "";
            }
            string value = string.Empty;

            value = getFieldValue_MySql(conn, table, field, pkField, pkFieldValue);

            return value;
        }

        /// <summary>
        /// 测试一个连接
        /// </summary>
        /// <param name="connID"></param>
        /// <returns></returns>
        public string Test(Guid connID)
        {
            var link = Get(connID);
            if (link == null) return "未找到连接!";

            return test_MySql(link);

        }


        /// <summary>
        /// 测试一个连接(oracle)
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private string test_MySql(RoadFlow.Data.Model.DBConnection conn)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                    return "连接成功!";
                }
                catch (Exception err)
                {
                    return err.Message;
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                        MySqlConnection.ClearPool(sqlConn);//加入此行可释放连接
                        sqlConn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 得到一个连接所有表(oracle)
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private List<string> getTables_MySql(RoadFlow.Data.Model.DBConnection conn)
        {
            using (MySqlConnection oraConn = new MySqlConnection(conn.ConnectionString))
            {
                try
                {
                    oraConn.Open();

                    List<string> tables = new List<string>();
                    string sql = "select table_name TNAME,(case table_type WHEN 'BASE TABLE' then 'TABLE' when 'VIEW' then 'VIEW' else '' end ) TABTYPE ,null CLUSTERID from information_schema.tables where table_schema='smartcamp'  ";
                    using (MySqlCommand sqlCmd = new MySqlCommand(sql, oraConn))
                    {
                        MySqlDataReader dr = sqlCmd.ExecuteReader();
                        while (dr.Read())
                        {
                            tables.Add(dr.GetString(0));
                        }
                        dr.Close();
                        return tables;
                    }
                }
                catch (SqlException err)
                {
                    Log.Add(err);
                    return new List<string>();
                }
                finally
                {
                    if (oraConn.State == ConnectionState.Open)
                    {
                        oraConn.Close();
                        MySqlConnection.ClearPool(oraConn);//加入此行可释放连接
                        oraConn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }


        /// <summary>
        /// 得到一个连接一个表所有字段 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private Dictionary<string, string> getFields_MySql(RoadFlow.Data.Model.DBConnection conn, string table)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                    Dictionary<string, string> fields = new Dictionary<string, string>();
                    string sql = string.Format("select COLUMN_NAME,column_comment COMMENTS from information_schema.columns where table_schema='smartcamp' and table_name='{0}'", table);
                    using (MySqlCommand sqlCmd = new MySqlCommand(sql, sqlConn))
                    {
                        MySqlDataReader dr = sqlCmd.ExecuteReader();
                        while (dr.Read())
                        {
                            fields.Add(dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1));
                        }
                        dr.Close();
                        return fields;
                    }
                }
                catch (Exception err)
                {
                    Log.Add(err);
                    return new Dictionary<string, string>();
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                        MySqlConnection.ClearPool(sqlConn);//加入此行可释放连接
                        sqlConn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }


        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="conn">连接ID</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段名</param>
        /// <param name="pkFieldValue">主键和值字典</param>
        /// <returns></returns>
        private string getFieldValue_MySql(RoadFlow.Data.Model.DBConnection conn, string table, string field, Dictionary<string, string> pkFieldValue)
        {
            using (MySqlConnection sqlConn = new MySqlConnection(conn.ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                    List<string> fields = new List<string>();
                    StringBuilder sql = new StringBuilder();
                    sql.AppendFormat("select {0} from {1} where 1=1", field, table);
                    foreach (var pk in pkFieldValue)
                    {
                        sql.AppendFormat(" and {0}='{1}'", pk.Key, pk.Value);
                    }

                    using (MySqlCommand sqlCmd = new MySqlCommand(sql.ToString(), sqlConn))
                    {
                        MySqlDataReader dr = sqlCmd.ExecuteReader();
                        string value = string.Empty;
                        if (dr.HasRows)
                        {
                            dr.Read();
                            value = dr.GetString(0);
                        }
                        dr.Close();
                        return value;
                    }
                }
                catch (Exception err)
                {
                    Log.Add(err);
                    return "";
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                        MySqlConnection.ClearPool(sqlConn);//加入此行可释放连接
                        sqlConn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }


        /// <summary>
        /// 得到一个连接一个表一个字段的值
        /// </summary>
        /// <param name="linkID">连接ID</param>
        /// <param name="table">表</param>
        /// <param name="field">字段</param>
        /// <param name="pkField">主键字段</param>
        /// <param name="pkFieldValue">主键值</param>
        /// <returns></returns>
        private string getFieldValue_MySql(RoadFlow.Data.Model.DBConnection conn, string table, string field, string pkField, string pkFieldValue)
        {
            string v = "";
            using (MySqlConnection sqlConn = new MySqlConnection(conn.ConnectionString))
            {
                try
                {
                    sqlConn.Open();
                    string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", field, table, pkField, pkFieldValue);
                    using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, sqlConn))
                    {
                        try
                        {
                            DataTable dt = new DataTable();
                            dap.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                v = dt.Rows[0][0].ToString();
                            }
                        }
                        catch (Exception err)
                        {
                            Log.Add(err);
                        }
                        return v;
                    }
                }
                catch (Exception err)
                {
                    Log.Add(err);
                    return "";
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        sqlConn.Close();
                        MySqlConnection.ClearPool(sqlConn);//加入此行可释放连接
                        sqlConn.Dispose();
                    }
                    GC.Collect();
                }
            }
        }

        /// <summary>
        /// 根据连接实体得到连接
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public System.Data.IDbConnection GetConnection(RoadFlow.Data.Model.DBConnection dbconn)
        {
            if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
            {
                return null;
            }
            IDbConnection conn = null;

            conn = new MySqlConnection(dbconn.ConnectionString);

            return conn;

        }

        /// <summary>
        /// 根据连接实体得到数据适配器
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public System.Data.IDbDataAdapter GetDataAdapter(IDbConnection conn, string connType, string cmdText, IDataParameter[] parArray)
        {
            IDbDataAdapter dataAdapter = null;

            MySqlCommand cmd1 = new MySqlCommand(cmdText, (MySqlConnection)conn);
            if (parArray != null && parArray.Length > 0)
            {
                cmd1.Parameters.AddRange(parArray);
            }
            dataAdapter = new MySqlDataAdapter(cmd1);

            return dataAdapter;
        }

        /// <summary>
        /// 测试一个sql是否合法
        /// </summary>
        /// <param name="dbconn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool TestSql(RoadFlow.Data.Model.DBConnection dbconn, string sql)
        {
            if (dbconn == null)
            {
                return false;
            }

            using (MySqlConnection conn = new MySqlConnection(dbconn.ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql.ReplaceSelectSql(), conn))
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                            return true;
                        }
                        catch
                        {
                            return false;
                        }
                    }
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }

        }

        /// <summary>
        /// 根据连接实体得到数据表
        /// </summary>
        /// <param name="dbconn"></param>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string dbconn, string table, string field, string fieldValue)
        {
            if (dbconn.IsNullOrEmpty() || table.IsNullOrEmpty() || field.IsNullOrEmpty() || fieldValue.IsNullOrEmpty())
            {
                return new DataTable();
            }

            var conn = Get(dbconn.ToGuid());
            if (conn == null)
            {
                return new DataTable();
            }

            string sql = "SELECT * FROM " + table + " WHERE " + field + " = @" + field;
            IDataParameter[] parameterArray = new MySqlParameter[] { new MySqlParameter("@" + field, fieldValue) };
            return GetDataTable(conn, sql, parameterArray);
        }

        /// <summary>
        /// 根据连接实体得到数据表
        /// </summary>
        /// <param name="linkID"></param>
        /// <returns></returns>
        public DataTable GetDataTable(RoadFlow.Data.Model.DBConnection dbconn, string sql, IDataParameter[] parameterArray = null)
        {
            if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
            {
                return null;
            }
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(dbconn.ConnectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        if (parameterArray != null && parameterArray.Length > 0)
                        {
                            cmd.Parameters.AddRange((MySqlParameter[])parameterArray);
                        }
                        using (MySqlDataAdapter dap = new MySqlDataAdapter(cmd))
                        {
                            dap.Fill(dt);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Platform.Log.Add(ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        MySqlConnection.ClearPool(conn);//加入此行可释放连接
                        conn.Dispose();
                    }
                    GC.Collect();
                }
            }

            return dt;
        }

        /// <summary>
        /// 得到一个表的结构
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public System.Data.DataTable GetTableSchema(System.Data.IDbConnection conn, string tableName, string dbType)
        {
            DataTable dt = new DataTable();

            string sql1 = string.Format(@"SELECT COLUMN_NAME as f_name,DATA_TYPE as t_name 
                ,CHARACTER_MAXIMUM_LENGTH AS length
                ,CASE IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 END AS is_null
                ,COLUMN_DEFAULT AS cdefault
                ,0 as isidentity 
                FROM information_schema.columns WHERE table_schema='smartcamp' and table_name='{0}' ", tableName);
            MySqlDataAdapter dap1 = new MySqlDataAdapter(sql1, (MySqlConnection)conn);
            dap1.Fill(dt);

            return dt;
        }

        /// <summary>
        /// 更新一个连接一个表一个字段的值
        /// </summary>
        /// <param name="connID"></param>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <param name="value"></param>
        public void UpdateFieldValue(Guid connID, string table, string field, string value, string where)
        {
            var conn = Get(connID);
            if (conn == null)
            {
                return;
            }

            using (var dbconn = GetConnection(conn))
            {
                try
                {
                    dbconn.Open();
                }
                catch (Exception ex)
                {
                    Platform.Log.Add(ex);
                }
                string sql = string.Format("UPDATE {0} SET {1}=@value WHERE {2}", table, field, where);
                MySqlParameter par = new MySqlParameter("@value", value);
                using (MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)dbconn))
                {
                    cmd.Parameters.Add(par);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Platform.Log.Add(ex);
                    }
                }
            }

        }

        /// <summary>
        /// 删除一个连接表的数据
        /// </summary>
        /// <param name="connID"></param>
        /// <param name="table"></param>
        /// <param name="pkFiled"></param>
        /// <param name="pkValue"></param>
        public int DeleteData(Guid connID, string table, string pkFiled, string pkValue)
        {
            int count = 0;
            var conn = Get(connID);
            if (conn == null)
            {
                return count;
            }

            using (var dbconn = GetConnection(conn))
            {
                try
                {
                    dbconn.Open();
                }
                catch (Exception ex)
                {
                    Platform.Log.Add(ex);
                }
                string sql = string.Format("DELETE FROM {0} WHERE {1}=@{1}", table, pkFiled);
                MySqlParameter par = new MySqlParameter("@" + pkFiled, pkValue);
                using (MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)dbconn))
                {
                    cmd.Parameters.Add(par);
                    try
                    {
                        count = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Platform.Log.Add(ex);
                    }
                }
            }

            return count;
        }
    }
}