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);
}
public void testDataReader(NpgsqlDataReader reader)
{
if (reader != null)
{
if (reader.HasRows)
{
while (reader.Read())
{// oid geomText
//string oid = reader["oid"].ToString();
//string geomText = reader["geomText"].ToString();
for (int i = 0; i < reader.FieldCount; i++)
{
string value = reader[i].ToString();
}
}
reader.Close();
reader.Dispose();
}
}
}
internal NpgsqlDataReader GetReader(CommandBehavior cb)
{
CheckConnectionState();
// Block the notification thread before writing anything to the wire.
using (_connector.BlockNotificationThread())
{
State = CommandState.InProgress;
NpgsqlDataReader reader;
_connector.SetBackendCommandTimeout(CommandTimeout);
if (_prepared == PrepareStatus.NeedsPrepare)
{
PrepareInternal();
}
if (_prepared == PrepareStatus.NotPrepared)
{
var commandText = GetCommandText();
// Write the Query message to the wire.
_connector.SendQuery(commandText);
// Tell to mediator what command is being sent.
if (_prepared == PrepareStatus.NotPrepared)
{
_connector.Mediator.SetSqlSent(commandText, NpgsqlMediator.SQLSentType.Simple);
}
else
{
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
}
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread());
// For un-prepared statements, the first response is always a row description.
// For prepared statements, we may be recycling a row description from a previous Execute.
// TODO: This is the source of the inconsistency described in #357
reader.NextResult();
reader.UpdateOutputParameters();
if (
CommandType == CommandType.StoredProcedure
&& reader.FieldCount == 1
&& reader.GetDataTypeName(0) == "refcursor"
)
{
// When a function returns a sole column of refcursor, transparently
// FETCH ALL from every such cursor and return those results.
var sw = new StringWriter();
while (reader.Read())
{
sw.WriteLine(String.Format("FETCH ALL FROM \"{0}\";", reader.GetString(0)));
}
reader.Dispose();
var queryText = sw.ToString();
if (queryText == "")
{
queryText = ";";
}
// Passthrough the commandtimeout to the inner command, so user can also control its timeout.
// TODO: Check if there is a better way to handle that.
_connector.SendQuery(queryText);
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread());
// For un-prepared statements, the first response is always a row description.
// For prepared statements, we may be recycling a row description from a previous Execute.
// TODO: This is the source of the inconsistency described in #357
reader.NextResultInternal();
reader.UpdateOutputParameters();
}
}
else
{
// Bind the parameters, execute and sync
for (var i = 0; i < _parameters.Count; i++)
_parameters[i].Bind(_connector.NativeToBackendTypeConverterOptions);
_connector.SendBind(AnonymousPortal, _planName, _parameters, _resultFormatCodes);
_connector.SendExecute();
_connector.SendSync();
// Tell to mediator what command is being sent.
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
// Construct the return reader, possibly with a saved row description from Prepare().
reader = new NpgsqlDataReader(this, cb, _connector.BlockNotificationThread(), true, _currentRowDescription);
if (_currentRowDescription == null) {
reader.NextResultInternal();
}
reader.UpdateOutputParameters();
}
return reader;
}
}
public DataSet get_data_mmyy(string str, string tu, string den)
{
DataSet tmp = null;
DateTime dt1 = StringToDate(tu);
DateTime dt2 = StringToDate(den);
int y1 = dt1.Year, m1 = dt1.Month;
int y2 = dt2.Year, m2 = dt2.Month;
int itu, iden;
string mmyy = "";
bool be = true;
Npgsql.NpgsqlConnection connct = new NpgsqlConnection(ConStr);
connct.Open();
for (int i = y1; i <= y2; i++)
{
itu = (i == y1) ? m1 : 1;
iden = (i == y2) ? m2 : 12;
for (int j = itu; j <= iden; j++)
{
mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
if (bMmyy(mmyy))
{
sql = str.Replace("xxx", "medibv" + mmyy);
sql = str.Replace("medibvmmyy", "medibv" + mmyy);
using (Npgsql.NpgsqlCommand cmm = new NpgsqlCommand(sql, connct))
{
// cmm.Connection.Open();
Npgsql.NpgsqlDataReader drd = null;
try
{
drd = cmm.ExecuteReader();
if (tmp == null)
{
tmp = new DataSet();
}
if (tmp.Tables.Count == 0 && drd.FieldCount > 0)
{
tmp.Tables.Add("Table");
}
if (tmp.Tables.Count > 0)
{
for (int ia = 0; ia < drd.FieldCount; ia++)
{
if (!tmp.Tables[0].Columns.Contains(drd.GetName(ia)))
{
tmp.Tables[0].Columns.Add(drd.GetName(ia), drd.GetFieldType(ia));
}
}
while (drd.Read())
{
DataRow ndtr = tmp.Tables[0].NewRow();
for (int ie = 0; ie < drd.FieldCount; ie++)
{
ndtr[drd.GetName(ie)] = drd[ie];
}
tmp.Tables[0].Rows.Add(ndtr);
}
}
}
catch
{
}
finally
{
if (drd != null)
{
drd.Close();
drd.Dispose();
}
}
}
}
}
}
connct.Close();
return(tmp);
}
internal NpgsqlDataReader GetReader(CommandBehavior cb)
{
CheckConnectionState();
// Block the notification thread before writing anything to the wire.
using (_connector.BlockNotificationThread())
{
IEnumerable<IServerResponseObject> responseEnum;
NpgsqlDataReader reader;
_connector.SetBackendCommandTimeout(CommandTimeout);
if (_prepared == PrepareStatus.NeedsPrepare)
{
PrepareInternal();
}
if (_prepared == PrepareStatus.NotPrepared)
{
byte[] commandText = GetCommandText();
var query = new NpgsqlQuery(commandText);
// Write the Query message to the wire.
_connector.Query(query);
// Tell to mediator what command is being sent.
if (_prepared == PrepareStatus.NotPrepared)
{
_connector.Mediator.SetSqlSent(commandText, NpgsqlMediator.SQLSentType.Simple);
}
else
{
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
}
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader.
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread()
);
if (
CommandType == CommandType.StoredProcedure
&& reader.FieldCount == 1
&& reader.GetDataTypeName(0) == "refcursor"
)
{
// When a function returns a sole column of refcursor, transparently
// FETCH ALL from every such cursor and return those results.
var sw = new StringWriter();
while (reader.Read())
{
sw.WriteLine("FETCH ALL FROM \"{0}\";", reader.GetString(0));
}
reader.Dispose();
var queryText = sw.ToString();
if (queryText == "")
{
queryText = ";";
}
// Passthrough the commandtimeout to the inner command, so user can also control its timeout.
// TODO: Check if there is a better way to handle that.
query = new NpgsqlQuery(queryText);
// Write the Query message to the wire.
_connector.Query(query);
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader.
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread()
);
}
}
else
{
// Update the Bind object with current parameter data as needed.
BindParameters();
// Write the Bind, Execute, and Sync message to the wire.
_connector.Bind(_bind);
_connector.Execute(_execute);
_connector.Sync();
// Tell to mediator what command is being sent.
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Execute);
// Flush and wait for responses.
responseEnum = _connector.ProcessBackendResponsesEnum();
// Construct the return reader, possibly with a saved row description from Prepare().
reader = new NpgsqlDataReader(
responseEnum,
cb,
this,
_connector.BlockNotificationThread(),
true,
_currentRowDescription
);
}
return reader;
}
}
