Exemple #1
0
        public void ExecuteReader(Action <NpgsqlDataReader> readerHander, CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms)
        {
            DateTime      dt        = DateTime.Now;
            NpgsqlCommand cmd       = new NpgsqlCommand();
            string        logtxt    = "";
            DateTime      logtxt_dt = DateTime.Now;
            var           pc        = PrepareCommand(cmd, cmdType, cmdText, cmdParms, ref logtxt);

            logtxt += $"PrepareCommand: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
            Exception ex = Lib.Trys(delegate() {
                logtxt_dt = DateTime.Now;
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                logtxt += $"Open: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                try {
                    logtxt_dt           = DateTime.Now;
                    NpgsqlDataReader dr = cmd.ExecuteReader();
                    logtxt += $"ExecuteReader: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                    while (true)
                    {
                        logtxt_dt   = DateTime.Now;
                        bool isread = dr.Read();
                        logtxt     += $"	dr.Read: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                        if (isread == false)
                        {
                            break;
                        }

                        if (readerHander != null)
                        {
                            logtxt_dt       = DateTime.Now;
                            object[] values = new object[dr.FieldCount];
                            dr.GetValues(values);
                            logtxt   += $"	dr.GetValues: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                            logtxt_dt = DateTime.Now;
                            readerHander(dr);
                            logtxt += $"	readerHander: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms ({string.Join(",", values)})\r\n";
                        }
                    }
                    logtxt_dt = DateTime.Now;
                    dr.Dispose();
                    logtxt += $"ExecuteReader_dispose: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                } catch {
                    throw;
                }
            }, 1);

            logtxt_dt = DateTime.Now;
            if (pc.Tran == null)
            {
                this.Pool.ReleaseConnection(pc.Conn);
            }
            logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
            LoggerException(cmd, ex, dt, logtxt);
        }
        private Hashtable GetColumns()
        {
            StringBuilder sb = new StringBuilder();

            // the column index is used to find data.
            // any changes to the order of the columns needs to be reflected in struct Columns
            sb.Append("SELECT a.attname AS column_name, a.attnotnull AS column_notnull, a.attrelid AS table_id, a.attnum AS column_num, d.adsrc as column_default");
            sb.Append(" FROM pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attnum > 0 AND (");
            bool first = true;

            for (int i = 0; i < _currentResultset.RowDescription.NumFields; ++i)
            {
                if (_currentResultset.RowDescription[i].table_oid != 0)
                {
                    if (!first)
                    {
                        sb.Append(" OR ");
                    }
                    sb.AppendFormat("(a.attrelid={0} AND a.attnum={1})", _currentResultset.RowDescription[i].table_oid, _currentResultset.RowDescription[i].column_attribute_number);
                    first = false;
                }
            }
            sb.Append(')');

            // if the loop ended without setting first to false, then there will be no results from the query
            if (first)
            {
                return(null);
            }

            using (NpgsqlConnection connection = _connection.Clone())
                using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        Hashtable columnLookup = new Hashtable();
                        int       columnCount  = reader.FieldCount;
                        while (reader.Read())
                        {
                            object[] values = new object[columnCount];
                            reader.GetValues(values);
                            columnLookup[reader[Columns.table_id].ToString() + "," + reader[Columns.column_num].ToString()] = values;
                        }
                        return(columnLookup);
                    }
        }
        private Hashtable GetTablesFromOids(ArrayList oids)
        {
            if (oids.Count == 0)
            {
                return(null);
            }

            StringBuilder sb = new StringBuilder();

            // the column index is used to find data.
            // any changes to the order of the columns needs to be reflected in struct Tables
            sb.Append("SELECT current_database() AS table_catalog, nc.nspname AS table_schema, c.relname AS table_name, c.oid as table_id");
            sb.Append(" FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND (c.relkind = 'r' OR c.relkind = 'v') AND c.oid IN (");
            bool first = true;

            foreach (int oid in oids)
            {
                if (!first)
                {
                    sb.Append(',');
                }
                sb.Append(oid);
                first = false;
            }
            sb.Append(')');

            using (NpgsqlConnection connection = _connection.Clone())
                using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), connection))
                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        Hashtable oidLookup   = new Hashtable();
                        int       columnCount = reader.FieldCount;
                        while (reader.Read())
                        {
                            object[] values = new object[columnCount];
                            reader.GetValues(values);
                            oidLookup[Convert.ToInt32(reader[Tables.table_id])] = values;
                        }
                        return(oidLookup);
                    }
        }
Exemple #4
0
        /// <summary>
        /// 若使用【读写分离】,查询【从库】条件cmdText.StartsWith("SELECT "),否则查询【主库】
        /// </summary>
        /// <param name="readerHander"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        public void ExecuteReader(Action <NpgsqlDataReader> readerHander, CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms)
        {
            DateTime      dt        = DateTime.Now;
            NpgsqlCommand cmd       = new NpgsqlCommand();
            string        logtxt    = "";
            DateTime      logtxt_dt = DateTime.Now;
            var           pool      = this.MasterPool;
            bool          isSlave   = false;

            //读写分离规则
            if (this.SlavePools.Any() && cmdText.StartsWith("SELECT ", StringComparison.CurrentCultureIgnoreCase))
            {
                var availables = slaveUnavailables == 0 ?
                                 //查从库
                                 this.SlavePools : (
                    //查主库
                    slaveUnavailables == this.SlavePools.Count ? new List <NpgsqlConnectionPool>() :
                    //查从库可用
                    this.SlavePools.Where(sp => sp.IsAvailable).ToList());
                if (availables.Any())
                {
                    isSlave = true;
                    pool    = availables.Count == 1 ? availables[0] : availables[slaveRandom.Next(availables.Count)];
                }
            }

            Object <NpgsqlConnection> conn = null;
            var pc = PrepareCommand(cmdType, cmdText, cmdParms, ref logtxt);

            if (IsTracePerformance)
            {
                logtxt += $"PrepareCommand: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
            }
            Exception ex = null;

            try {
                if (IsTracePerformance)
                {
                    logtxt_dt = DateTime.Now;
                }
                if (isSlave)
                {
                    //从库查询切换,恢复
                    bool isSlaveFail = false;
                    try {
                        if (pc.cmd.Connection == null)
                        {
                            pc.cmd.Connection = (conn = pool.Get()).Value;
                        }
                        //if (slaveRandom.Next(100) % 2 == 0) throw new Exception("测试从库抛出异常");
                    } catch {
                        isSlaveFail = true;
                    }
                    if (isSlaveFail)
                    {
                        if (conn != null)
                        {
                            if (IsTracePerformance)
                            {
                                logtxt_dt = DateTime.Now;
                            }
                            pool.Return(conn, ex);
                            if (IsTracePerformance)
                            {
                                logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
                            }
                        }
                        LoggerException(pool, cmd, new Exception($"连接失败,准备切换其他可用服务器"), dt, logtxt, false);
                        pc.cmd.Parameters.Clear();
                        ExecuteReader(readerHander, cmdType, cmdText, cmdParms);
                        return;
                    }
                }
                else
                {
                    //主库查询
                    if (pc.cmd.Connection == null)
                    {
                        pc.cmd.Connection = (conn = pool.Get()).Value;
                    }
                }
                if (IsTracePerformance)
                {
                    logtxt   += $"Open: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                    logtxt_dt = DateTime.Now;
                }
                using (NpgsqlDataReader dr = pc.cmd.ExecuteReader()) {
                    if (IsTracePerformance)
                    {
                        logtxt += $"ExecuteReader: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                    }
                    while (true)
                    {
                        if (IsTracePerformance)
                        {
                            logtxt_dt = DateTime.Now;
                        }
                        bool isread = dr.Read();
                        if (IsTracePerformance)
                        {
                            logtxt += $"	dr.Read: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                        }
                        if (isread == false)
                        {
                            break;
                        }

                        if (readerHander != null)
                        {
                            object[] values = null;
                            if (IsTracePerformance)
                            {
                                logtxt_dt = DateTime.Now;
                                values    = new object[dr.FieldCount];
                                dr.GetValues(values);
                                logtxt   += $"	dr.GetValues: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                                logtxt_dt = DateTime.Now;
                            }
                            readerHander(dr);
                            if (IsTracePerformance)
                            {
                                logtxt += $"	readerHander: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms ({string.Join(",", values)})\r\n";
                            }
                        }
                    }
                    if (IsTracePerformance)
                    {
                        logtxt_dt = DateTime.Now;
                    }
                    dr.Close();
                }
                if (IsTracePerformance)
                {
                    logtxt += $"ExecuteReader_dispose: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n";
                }
            } catch (Exception ex2) {
                ex = ex2;
            }

            if (conn != null)
            {
                if (IsTracePerformance)
                {
                    logtxt_dt = DateTime.Now;
                }
                pool.Return(conn, ex);
                if (IsTracePerformance)
                {
                    logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
                }
            }
            LoggerException(pool, pc.cmd, ex, dt, logtxt);
            pc.cmd.Parameters.Clear();
        }