static void Main(string[] args)
{
Console.WriteLine(ReadDBConnectionString());
var DBConnection = new NpgsqlConnection(ReadDBConnectionString());
DBConnection.Open();
var StringSQLCommand = "select version()";
var SQLCommand = new NpgsqlCommand(StringSQLCommand, DBConnection);
var PostgreSQLVersion = SQLCommand.ExecuteScalar().ToString();
Console.WriteLine($"PostgreSQL version: {PostgreSQLVersion}");
StringSQLCommand = @"SELECT * FROM public.""Item"";";
SQLCommand.CommandText = StringSQLCommand;
SQLCommand.Connection = DBConnection;
Npgsql.NpgsqlDataReader ItemList = SQLCommand.ExecuteReader();
while (ItemList.Read())
{
Console.WriteLine("{0} , {1} ", ItemList.GetString(0), ItemList.GetString(1));
}
Console.ReadKey();
DBConnection.Close();
string ReadDBConnectionString()
{
string appSettings = ConfigurationManager.ConnectionStrings["Database1"].ToString();
return(appSettings);
}
}
public void CarregarAluno(NpgsqlDataReader dr)
{
Model.Aluno objAluno = new Model.Aluno();
if (dr.Read())
{
objAluno.Nome = dr.IsDBNull(dr.GetOrdinal("nm_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("nm_usuario"));
objAluno.Email = dr.IsDBNull(dr.GetOrdinal("email_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("email_usuario"));
objAluno.Senha = dr.IsDBNull(dr.GetOrdinal("senha_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("senha_usuario"));
objAluno.Id = dr.IsDBNull(dr.GetOrdinal("id_usuario")) ? 0 : dr.GetInt32(dr.GetOrdinal("id_usuario"));
Model.Session.Session.Aluno = objAluno;
}
else { throw new ExceptionDAL("Não foi possível logar usuário"); }
}
private string GetSafeString(NpgsqlDataReader reader, int colindex)
{
if(reader.IsDBNull(colindex))
{
return string.Empty;
}
else
{
return reader.GetString(colindex);
}
}
}//O(1)
public void checkSuppliers(ref LinkedList <Supply> suppliers) //get all suppliers
{
while (suppliers.Count != 0)
{
suppliers.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Suppliers\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
suppliers.AddLast(new Supply(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
}
this.sort.sortSuppliers(ref suppliers);
}//O(N)
}//O(1)
public void checkCustomers(ref LinkedList <Customer> customers)
{
while (customers.Count != 0)
{
customers.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Customers\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
customers.AddLast(new Customer(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4)));
}
this.sort.sortCustomers(ref customers);
}//O(N)
}//O(1)
public void checkProducts(ref LinkedList <Product> products)
{
while (products.Count != 0)
{
products.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT \"Product number\", \"Name\", \"First name\" || ' ' || \"Last name\" as \"supplier\", \"ID\", \"Mail\" FROM \"Products\" JOIN \"Suppliers\" ON \"Products\".\"Supplier\" = \"Suppliers\".\"ID\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
products.AddLast(new Product(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetString(4)));
}
this.sort.sortProducts(ref products);
}//O(N)
public Curso carregarCursoXAluno(NpgsqlDataReader data_reader)
{
Curso curso = new Curso();
curso.Tl_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TL_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_CURSO"));
curso.Ds_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_CURSO"));
curso.Duracao_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DUR_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DUR_CURSO"));
curso.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return curso;
}
/// <summary>
/// A helper function that takes the current row from the NpgsqlDataReader
/// and hydrates a MembershipUser from the values. Called by the
/// MembershipUser.GetUser implementation.
/// </summary>
/// <param name="reader">NpgsqlDataReader object</param>
/// <returns>MembershipUser object</returns>
private MembershipUser GetUserFromReader(NpgsqlDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
return new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
}
private Resposta carregar(NpgsqlDataReader data_reader)
{
Resposta resposta = new Resposta();
resposta.id_resposta = data_reader.IsDBNull(data_reader.GetOrdinal("ID_RESPOSTA")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_RESPOSTA"));
resposta.Ds_resposta = data_reader.IsDBNull(data_reader.GetOrdinal("DS_RESPOSTA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_RESPOSTA"));
resposta.FL_correta = data_reader.IsDBNull(data_reader.GetOrdinal("FL_CORRETA")) ? false : data_reader.GetBoolean(data_reader.GetOrdinal("FL_CORRETA"));
resposta.Questao_obj.Id_questao = data_reader.IsDBNull(data_reader.GetOrdinal("ID_QUESTAO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_QUESTAO"));
resposta.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return resposta;
}
}//O(1)
public void checkOrders(ref LinkedList <Order> orders)
{
while (orders.Count != 0)
{
orders.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT DISTINCT \"Order ID\", \"Product Number\", \"Products\".\"Name\", \"Customer ID\", \"First name\" || ' ' || \"Last name\" as \"Customer name\" , \"Quantity\" FROM \"Orderd\" JOIN \"Products\" ON \"Orderd\".\"Product Number\" = \"Products\".\"Product number\" JOIN \"Customers\" ON \"Orderd\".\"Customer ID\" = \"Customers\".\"ID\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
orders.AddLast(new Order(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetInt32(3), reader.GetString(4), reader.GetInt32(5)));
}
this.sort.sortOrders(ref orders);
}//O(N)
public bool GetByPrimaryKey(string pKey)
{
string sQuery = "select * from tbm_carrepair WHERE repairid='" + pKey + "'";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
try
{
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("repairid")))
{
m_repairid = rdr.GetString(rdr.GetOrdinal("repairid"));
}
else
{
m_repairid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("carid")))
{
m_carid = rdr.GetString(rdr.GetOrdinal("carid"));
}
else
{
m_carid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opadd")))
{
m_opadd = rdr.GetString(rdr.GetOrdinal("opadd"));
}
else
{
m_opadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd")))
{
m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd"));
}
else
{
m_pcadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luadd")))
{
m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd"));
}
else
{
m_luadd = System.DateTime.MinValue;
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opedit")))
{
m_opedit = rdr.GetString(rdr.GetOrdinal("opedit"));
}
else
{
m_opedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit")))
{
m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit"));
}
else
{
m_pcedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luedit")))
{
m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit"));
}
else
{
m_luedit = System.DateTime.MinValue;
};
m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt"));
}
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
private QuoteServerEvent GetQuoteServerEventFromRecord(NpgsqlDataReader reader)
{
var quoteServerEvent = new QuoteServerEvent
{
StockSymbol = reader.GetString(9),
Price = reader.GetDecimal(10),
QuoteServerTime = reader.GetDateTime(14),
CryptoKey = reader.GetString(15)
};
FillBaseEventPropertiesFromRecord(quoteServerEvent, reader);
return quoteServerEvent;
}
private UserCommandEvent GetUserCommandEventFromRecord(NpgsqlDataReader reader)
{
var userCommandEvent = new UserCommandEvent
{
Command = (CommandType) reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10)
};
FillBaseEventPropertiesFromRecord(userCommandEvent, reader);
return userCommandEvent;
}
private void FillBaseEventPropertiesFromRecord(TransactionEvent te, NpgsqlDataReader reader)
{
te.LoggedAt = reader.GetDateTime(1);
te.OccuredAt = reader.GetDateTime(2);
te.TransactionId = reader.GetInt32(4);
te.UserId = (reader.IsDBNull(5)) ? null : reader.GetString(5);
te.Service = reader.GetString(6);
te.Server = reader.GetString(7);
}
/* Expected record:
* (0) id
* (1) logged_at
* (2) occured_at
* (3) type
* (4) transaction_id
* (5) user_id
* (6) service
* (7) server
* (8) command
* (9) stock
* (10) funds
* (11) filename
* (12) message
* (13) action
* (14) quote_server_time
* (15) cryptokey
*/
private TransactionEvent GetTransactionEventFromRecord(NpgsqlDataReader reader)
{
string eventType = reader.GetString(3);
switch (eventType)
{
case UserCommandEvent:
return GetUserCommandEventFromRecord(reader);
case QuoteServerEvent:
return GetQuoteServerEventFromRecord(reader);
case AccountTransactionEvent:
return GetAccountTransactionEventFromRecord(reader);
case SystemEvent:
return GetSystemEventFromRecord(reader);
case ErrorEvent:
return GetErrorEventFromRecord(reader);
case DebugEvent:
return GetDebugEventFromRecord(reader);
default:
throw new UnrecognizedTransactionEventException("Received an unrecognized event type from the database: " + eventType);
}
}
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;
}
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2browse(string browse_type_in, string browse_category_in, string browse_actor_in,
string browse_title_in, int batch_size_in, int customerid_out, ref int rows_returned,
ref int[] prod_id_out, ref string[] title_out, ref string[] actor_out, ref decimal[] price_out,
ref int[] special_out, ref int[] common_prod_id_out, ref double rt)
{
// Products table: PROD_ID INT, CATEGORY TINYINT, TITLE VARCHAR(50), ACTOR VARCHAR(50),
// PRICE DECIMAL(12,2), SPECIAL TINYINT, COMMON_PROD_ID INT
int i_row;
string data_in = null;
int[] category_out = new int[GlobalConstants.MAX_ROWS];
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
switch(browse_type_in)
{
case "category":
Browse_By_Category.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Category.Parameters["category_in"].Value = Convert.ToInt32(browse_category_in);
data_in = browse_category_in;
break;
case "actor":
Browse_By_Actor.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Actor.Parameters["actor_in"].Value = "\"" + browse_actor_in + "\"";
data_in = "\"" + browse_actor_in + "\"";
break;
case "title":
Browse_By_Title.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Title.Parameters["title_in"].Value = "\"" + browse_title_in + "\"";
data_in = "\"" + browse_title_in + "\"";
break;
}
// Console.WriteLine("Thread {0}: Calling Browse w/ browse_type= {1} batch_size_in= {2} data_in= {3}",
// Thread.CurrentThread.Name, browse_type_in, batch_size_in, data_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
try
{
switch(browse_type_in)
{
case "category":
Rdr = Browse_By_Category.ExecuteReader();
break;
case "actor":
Rdr = Browse_By_Actor.ExecuteReader();
break;
case "title":
Rdr = Browse_By_Title.ExecuteReader();
break;
}
i_row = 0;
while (Rdr.Read())
{
prod_id_out[i_row] = Rdr.GetInt32(0);
category_out[i_row] = Rdr.GetInt16(1);
title_out[i_row] = Rdr.GetString(2);
actor_out[i_row] = Rdr.GetString(3);
price_out[i_row] = Rdr.GetDecimal(4);
special_out[i_row] = Rdr.GetInt16(5);
common_prod_id_out[i_row] = Rdr.GetInt32(6);
++i_row;
}
Rdr.Close();
rows_returned = i_row;
}
catch (NpgsqlException e)
{
Console.WriteLine("Thread {0}: Error in Browse: {1}", Thread.CurrentThread.Name, e.Message);
return(false);
}
#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);
}
public Usuario carregar(NpgsqlDataReader data_reader)
{
Usuario consulta = new Usuario();
consulta.Id_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("ID_USUARIO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_USUARIO"));
int id_tipo_usuario = consulta.Id_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("ID_TIPO_USUARIO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_TIPO_USUARIO"));
if (id_tipo_usuario == (int)Tipo_usuario.ADMINISTRADOR)
{
consulta.Tipo = Tipo_usuario.ADMINISTRADOR;
}
else if (id_tipo_usuario == (int)Tipo_usuario.ALUNO)
{
consulta.Tipo = Tipo_usuario.ALUNO;
}
else
{
consulta.Tipo = Tipo_usuario.NONE;
}
consulta.Nm_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("NM_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("NM_USUARIO"));
consulta.Email_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("EMAIL_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("EMAIL_USUARIO"));
consulta.Senha_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("SENHA_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("SENHA_USUARIO"));
consulta.Link_imagem_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("LINK_IMAGEM_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("LINK_IMAGEM_USUARIO"));
consulta.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return consulta;
}
private DebugEvent GetDebugEventFromRecord(NpgsqlDataReader reader)
{
var debugEvent = new DebugEvent
{
Command = (CommandType)reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10),
FileName = (reader.IsDBNull(11)) ? null : reader.GetString(11),
DebugMessage = (reader.IsDBNull(12)) ? null : reader.GetString(12)
};
FillBaseEventPropertiesFromRecord(debugEvent, reader);
return debugEvent;
}
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;
}
}
private Condominio PreencherObjeto(NpgsqlDataReader dataReader)
{
int i = 0;
Condominio obj = new Condominio();
obj.Id = dataReader.GetInt32(i++);
obj.Id_endereco = dataReader.GetInt32(i++);
obj.Qtd_Apt = dataReader.GetInt32(i++);
obj.Valor_agua = dataReader.GetFloat(i++);
obj.Valor_luz = dataReader.GetFloat(i++);
obj.Valor_gas = dataReader.GetFloat(i++);
obj.Nome = dataReader.GetString(i++);
return obj;
}
Aula carregar(NpgsqlDataReader data_reader)
{
Aula aula = new Aula();
aula.Id_aula = data_reader.IsDBNull(data_reader.GetOrdinal("ID_AULA")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_AULA"));
aula.Tl_aula = data_reader.IsDBNull(data_reader.GetOrdinal("TL_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_AULA"));
aula.Ds_aula = data_reader.IsDBNull(data_reader.GetOrdinal("DS_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_AULA"));
aula.Curso_obj.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
aula.Link_aula = data_reader.IsDBNull(data_reader.GetOrdinal("LINK_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("LINK_AULA"));
aula.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return aula;
}
public static string GetStringOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetString(ordinal);
}//O(1)
public void customerOrder(ref LinkedList <CustomerOrder> allCustomerOrder, int id) //get all customer orders
{
while (allCustomerOrder.Count != 0)
{
allCustomerOrder.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT \"Order ID\", \"Product Number\", \"Name\", \"Quantity\" FROM \"Orderd\" JOIN \"Products\" ON \"Orderd\".\"Product Number\" = \"Products\".\"Product number\" WHERE \"Customer ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
allCustomerOrder.AddLast(new CustomerOrder(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), reader.GetInt32(3)));
}
}
this.sort.sortCustomerOrder(ref allCustomerOrder);
}//O(N)
private Endereco PreencherObjeto(NpgsqlDataReader dataReader)
{
int i = 0;
Endereco obj = new Endereco();
obj.Id = dataReader.GetInt32(i++);
obj.Cidade = dataReader.GetString(i++);
obj.Estado = dataReader.GetString(i++);
obj.Cep = dataReader.GetString(i++);
obj.Bairro = dataReader.GetString(i++);
obj.Numero = dataReader.GetString(i++);
obj.Logradouro = dataReader.GetString(i++);
obj.Complemento = dataReader.GetString(i++);
return obj;
}
}//O(1)
public void allSupplierItems(ref LinkedList <AllSupplierItems> allSupplierItems, int id) //shows all products from that seller
{
while (allSupplierItems.Count != 0)
{
allSupplierItems.RemoveFirst();
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT * FROM \"Products\" WHERE \"Supplier\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
allSupplierItems.AddLast(new AllSupplierItems(reader.GetInt32(0), reader.GetString(1)));
}
}
this.sort.sortSupplierItems(ref allSupplierItems);
}//O(N)
private Sindico PreencherObjeto(NpgsqlDataReader dataReader)
{
int i = 0;
Sindico obj = new Sindico();
obj.Id = dataReader.GetInt32(i++);
obj.Id_endereco = dataReader.GetInt32(i++);
obj.Id_condominio = dataReader.GetInt32(i++);
obj.Nome = dataReader.GetString(i++);
obj.Cpf = dataReader.GetString(i++);
obj.Rg = dataReader.GetString(i++);
return obj;
}
private Questao carregar(NpgsqlDataReader data_reader)
{
Questao questao = new Questao();
questao.Id_questao = data_reader.IsDBNull(data_reader.GetOrdinal("ID_QUESTAO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_QUESTAO"));
questao.Ds_questao = data_reader.IsDBNull(data_reader.GetOrdinal("DS_QUESTAO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_QUESTAO"));
questao.Curso_obj.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
questao.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return questao;
}
private MembershipUser GetUserFromReader(string providerName, NpgsqlDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = string.Empty;
if (reader.GetValue(3) != DBNull.Value)
passwordQuestion = reader.GetString(3);
string comment = string.Empty;
if (reader.GetValue(4) != DBNull.Value)
comment = reader.GetString(4);
bool isApproved = reader.GetBoolean(5);
bool isLockedOut = reader.GetBoolean(6);
DateTime creationDate = reader.GetDateTime(7);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(8) != DBNull.Value)
lastLoginDate = reader.GetDateTime(8);
DateTime lastActivityDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(11) != DBNull.Value)
lastLockedOutDate = reader.GetDateTime(11);
MembershipUser u = new MembershipUser(
providerName,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
///<summary>
///
/// This method is reponsible to derive the command parameter list with values obtained from function definition.
/// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown.
/// Parameters name will be parameter1, parameter2, ...
/// For while, only parameter name and NpgsqlDbType are obtained.
///</summary>
/// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
public static void DeriveParameters(NpgsqlCommand command)
{
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
// This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
query =
"select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
}
else
{
query = "select proargnames, proargtypes from pg_proc where proname = :proname";
procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
}
using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
{
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
{
NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
prm.Value = schemaName.Replace("\"", "").Trim();
}
String[] names = null;
String[] types = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (!rdr.IsDBNull(1))
{
types = rdr.GetString(1).Split();
}
}
}
if (types == null)
{
throw new InvalidOperationException(
String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
command.Parameters.Clear();
for (Int32 i = 0; i < types.Length; i++)
{
// skip parameter if type string is empty
// empty parameter lists can cause this
if (!string.IsNullOrEmpty(types[i]))
{
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo))
{
command.Parameters.Clear();
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
if (names != null && i < names.Length)
{
command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType));
}
else
{
command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType));
}
}
}
}
}
private static User GetUser(NpgsqlDataReader reader)
{
return new User
{
Id = reader.GetInt64(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
Photo = reader.GetByteArray(3),
Enabled = reader.GetBoolean(4),
CanChange = reader.GetBoolean(5),
Privilege = reader.GetNullableInt32(6)
};
}
public Curso carregar(NpgsqlDataReader data_reader)
{
Curso curso = new Curso();
curso.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
curso.Tl_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TL_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_CURSO"));
curso.Ds_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_CURSO"));
curso.Obj_curso = data_reader.IsDBNull(data_reader.GetOrdinal("OBJ_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("OBJ_CURSO"));
curso.Topicos_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TOPICOS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TOPICOS_CURSO"));
curso.Pre_req_curso = data_reader.IsDBNull(data_reader.GetOrdinal("PRE_REQ_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("PRE_REQ_CURSO"));
curso.Duracao_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DURACAO_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DURACAO_CURSO"));
curso.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return curso;
}
private Morador PreencherObjeto(NpgsqlDataReader dataReader)
{
int i = 0;
Morador obj = new Morador();
obj.Id = dataReader.GetInt32(i++);
obj.Id_condominio = dataReader.GetInt32(i++);
obj.Nome = dataReader.GetString(i++);
obj.Cpf = dataReader.GetString(i++);
obj.Rg = dataReader.GetString(i++);
obj.Numero_apt = dataReader.GetInt32(i++);
return obj;
}
private static void DoDeriveParameters(NpgsqlCommand command)
{
// See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html
command.Parameters.Clear();
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
// This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
var serverVersion = command.Connector.ServerVersion;
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
else
{
query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
}
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
// The pg_temp pseudo-schema is special - it's an alias to a real schema name (e.g. pg_temp_2).
// We get the real name with pg_my_temp_schema().
if (schemaName == "pg_temp")
{
using (var c = new NpgsqlCommand("SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()", command.Connection))
{
schemaName = (string)c.ExecuteScalar();
}
}
}
else
{
// proargsmodes is supported for Postgresql 8.1 and above
if (serverVersion >= new Version(8, 1, 0))
{
query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname";
}
else
{
query = "select proargnames, proargtypes from pg_proc where proname = :proname";
}
procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
}
using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
{
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
{
NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
prm.Value = schemaName.Replace("\"", "").Trim();
}
string[] names = null;
int[] types = null;
string[] modes = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (serverVersion >= new Version("8.1.0"))
{
if (!rdr.IsDBNull(2))
{
types = rdr.GetValue(2) as int[];
}
if (!rdr.IsDBNull(3))
{
modes = rdr.GetValue(3) as String[];
}
}
if (types == null)
{
if (rdr.IsDBNull(1) || rdr.GetString(1) == "")
{
return; // Parameterless function
}
types = rdr.GetString(1).Split().Select(int.Parse).ToArray();
}
}
else
{
throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
}
command.Parameters.Clear();
for (var i = 0; i < types.Length; i++)
{
var param = new NpgsqlParameter();
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo))
{
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
param.NpgsqlDbType = typeInfo.NpgsqlDbType;
if (names != null && i < names.Length)
{
param.ParameterName = ":" + names[i];
}
else
{
param.ParameterName = "parameter" + (i + 1);
}
if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported)
{
param.Direction = ParameterDirection.Input;
}
else
{
switch (modes[i])
{
case "i":
param.Direction = ParameterDirection.Input;
break;
case "o":
param.Direction = ParameterDirection.Output;
break;
case "b":
param.Direction = ParameterDirection.InputOutput;
break;
case "v":
throw new NotImplementedException("Cannot derive function parameter of type VARIADIC");
case "t":
throw new NotImplementedException("Cannot derive function parameter of type TABLE");
default:
throw new ArgumentOutOfRangeException("proargmode", modes[i],
"Unknown code in proargmodes while deriving: " + modes[i]);
}
}
command.Parameters.Add(param);
}
}
}
//
//-------------------------------------------------------------------------------------------------
//
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);
}
