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); } }
/// <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(); }