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