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