/// <summary>
/// Converts a NpgsqlDataReader to a DataSet
/// <param name='reader'>
/// NpgsqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(NpgsqlDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
/// <summary>
/// Executes a SQL statement against the connection and returns the number of rows affected.
/// </summary>
/// <returns>The number of rows affected if known; -1 otherwise.</returns>
public override Int32 ExecuteNonQuery()
{
//We treat this as a simple wrapper for calling ExecuteReader() and then
//update the records affected count at every call to NextResult();
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");
int?ret = null;
using (NpgsqlDataReader rdr = GetReader(CommandBehavior.SequentialAccess))
{
do
{
int thisRecord = rdr.RecordsAffected;
if (thisRecord != -1)
{
ret = (ret ?? 0) + thisRecord;
}
lastInsertedOID = rdr.LastInsertedOID ?? lastInsertedOID;
}while (rdr.NextResult());
}
return(ret ?? -1);
}
NpgsqlDataReader Execute(CommandBehavior behavior = CommandBehavior.Default)
{
Validate();
if (!IsPrepared)
ProcessRawQuery();
LogCommand();
State = CommandState.InProgress;
try
{
_connector = Connection.Connector;
// If a cancellation is in progress, wait for it to "complete" before proceeding (#615)
lock (_connector.CancelLock) { }
// Send protocol messages for the command
// Unless this is a prepared SchemaOnly command, in which case we already have the RowDescriptions
// from the Prepare phase (no need to send anything).
if (!IsPrepared || (behavior & CommandBehavior.SchemaOnly) == 0)
{
_connector.UserTimeout = CommandTimeout * 1000;
_sendState = SendState.Start;
_writeStatementIndex = 0;
if (IsPrepared)
Send(PopulateExecutePrepared);
else if ((behavior & CommandBehavior.SchemaOnly) == 0)
Send(PopulateExecuteNonPrepared);
else
Send(PopulateExecuteSchemaOnly);
}
var reader = new NpgsqlDataReader(this, behavior, _statements);
reader.NextResult();
_connector.CurrentReader = reader;
return reader;
}
catch
{
State = CommandState.Idle;
throw;
}
}
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;
}
}
async ValueTask <NpgsqlDataReader> Execute(CommandBehavior behavior, bool async, CancellationToken cancellationToken)
{
ValidateParameters();
var connector = Connection.Connector;
Debug.Assert(connector != null);
if (_isParsed)
{
if (_connectorPreparedOn != null && _connectorPreparedOn != Connection.Connector)
{
// The command was prepared, but since then the connector has changed. Detach all prepared statements.
foreach (var s in _statements)
{
s.PreparedStatement = null;
}
_connectorPreparedOn = null;
}
}
else
{
ProcessRawQuery();
}
State = CommandState.InProgress;
try
{
Log.ExecuteCommand(connector.Id, this);
Task sendTask;
// If a cancellation is in progress, wait for it to "complete" before proceeding (#615)
lock (connector.CancelLock) { }
connector.UserTimeout = CommandTimeout * 1000;
if ((behavior & CommandBehavior.SchemaOnly) == 0)
{
if (connector.Settings.MaxAutoPrepare > 0)
{
foreach (var statement in _statements)
{
// If this statement isn't prepared, see if it gets implicitly prepared.
// Note that this may return null (not enough usages for automatic preparation).
if (!statement.IsPrepared)
{
statement.PreparedStatement =
connector.PreparedStatementManager.TryGetAutoPrepared(statement);
}
if (statement.PreparedStatement != null)
{
statement.PreparedStatement.LastUsed = DateTime.UtcNow;
}
}
_connectorPreparedOn = connector;
}
// We do not wait for the entire send to complete before proceeding to reading -
// the sending continues in parallel with the user's reading. Waiting for the
// entire send to complete would trigger a deadlock for multistatement commands,
// where PostgreSQL sends large results for the first statement, while we're sending large
// parameter data for the second. See #641.
// Instead, all sends for non-first statements and for non-first buffers are performed
// asynchronously (even if the user requested sync), in a special synchronization context
// to prevents a dependency on the thread pool (which would also trigger deadlocks).
// The WriteBuffer notifies this command when the first buffer flush occurs, so that the
// send functions can switch to the special async mode when needed.
sendTask = SendExecute(async, cancellationToken);
}
else
{
sendTask = SendExecuteSchemaOnly(async, cancellationToken);
}
// The following is a hack. It raises an exception if one was thrown in the first phases
// of the send (i.e. in parts of the send that executed synchronously). Exceptions may
// still happen later and aren't properly handled. See #1323.
if (sendTask.IsFaulted)
{
sendTask.GetAwaiter().GetResult();
}
var reader = new NpgsqlDataReader(this, behavior, _statements, sendTask);
if (async)
{
await reader.NextResultAsync(cancellationToken);
}
else
{
reader.NextResult();
}
connector.CurrentReader = reader;
return(reader);
}
catch
{
State = CommandState.Idle;
throw;
}
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2login(string username_in, string password_in, ref int customerid_out, ref int rows_returned,
ref string[] title_out, ref string[] actor_out, ref string[] related_title_out, ref double rt)
{
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
Login.Parameters["username_in"].Value = username_in;
Login.Parameters["password_in"].Value = password_in;
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
NpgsqlTransaction t ;
try
{
t = objConn.BeginTransaction();
Rdr = Login.ExecuteReader();
Rdr.Read();
customerid_out = Rdr.GetInt32(0);
}
catch (NpgsqlException e)
{
Console.WriteLine("Thread {0}: Error in Login: {1}", Thread.CurrentThread.Name, e.Message);
return (false);
}
int i_row = 0;
if ((customerid_out > 0) && Rdr.NextResult())
{
while (Rdr.Read())
{
title_out[i_row] = Rdr.GetString(0);
actor_out[i_row] = Rdr.GetString(1);
related_title_out[i_row] = Rdr.GetString(2);
++i_row;
}
}
Rdr.Close();
t.Commit();
rows_returned = i_row;
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
return(true);
}
