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 static DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
{
DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from kindofanimals where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.infoURL = reader["infoURL"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static DBclasses.AsksCategory getAsksCategoriesById(int id)
{
DBclasses.AsksCategory result = new DBclasses.AsksCategory();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from askscategories where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.description = reader["description"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
/// <summary>
/// Verifica si en la base de datos existe un registro con el dpi enviado
/// </summary>
/// <param name="dpi">DPI de la persona que se desea buscar en la base de datos</param>
/// <returns>Valor booleano que india si el registro existe o no</returns>
public bool ConsultarRegistro(String dpi)
{
dataReader = null;
String query = String.Format("SELECT * FROM contacto WHERE dpi ='{0}'", dpi);
//Abre la conexion
if (this.Open())
{
// Crea la sentencia de ejecucion del select
cmd = new NpgsqlCommand(query, conexion);
// Ejecuta la sentencia y la guarda en el dataReader
dataReader = cmd.ExecuteReader();
// Si los resultados no son mayores a 0, no encontro ningun registro con ese dpi
// retorna falso, de lo contrario, retorna verdadero
if (!dataReader.HasRows)
{
// Cierra la conexión
this.Close();
return false;
}
}
// Cierra la conexión
this.Close();
return true;
}
public Summoner(NpgsqlDataReader dataReader)
{
Reader reader = new Reader(dataReader);
Id = reader.Integer();
Region = reader.String().ToRegionType();
AccountId = reader.Integer();
SummonerId = reader.Integer();
SummonerName = reader.String();
InternalName = reader.String();
SummonerLevel = reader.Integer();
ProfileIcon = reader.Integer();
UpdateAutomatically = reader.Boolean();
TimeCreated = reader.Time();
TimeUpdated = reader.Time();
reader.SanityCheck(Fields);
Ratings = new List<SummonerRating>();
RatingDictionary = new Dictionary<MapType, Dictionary<GameModeType, SummonerRating>>();
RankedStatistics = new List<SummonerRankedStatistics>();
}
public string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
private void SetCountryLanguage(CountryLanguage language, NpgsqlDataReader dr)
{
language.CountryCode = dr[0].ToString();
language.Name = dr[4].ToString();
language.IsOfficial = (bool)dr[5];
language.PercentageUse = double.Parse(dr[6].ToString());
}
// List All Candidates
public IEnumerable <CandidateInfo> GetAllCandidates()
{
List <CandidateInfo> canList = new List <CandidateInfo>();
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * from public.us_election_states order by name";
cmd.CommandType = CommandType.Text;
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CandidateInfo can = new CandidateInfo();
can.ID = Convert.ToInt32(dr["ID"].ToString());
can.State = dr["name"].ToString();
can.Republicans = Convert.ToInt32(dr["percent_rep"].ToString());
can.Democrats = Convert.ToInt32(dr["percent_dem"].ToString());
canList.Add(can);
}
conn.Close();
}
return(canList);
}
internal User(NpgsqlDataReader reader)
{
ID = (int)reader["id"];
Name = (string)reader["name"];
Email = (string)reader["email"];
Gravatar = (string)reader["gravatar"];
SvnAccount = (string)reader["svn"];
}
private void SetCountry(CountryViewModel country, NpgsqlDataReader countryDR, List<CountryLanguage> languages)
{
country.Code = countryDR[0].ToString();
country.Continent = countryDR[1].ToString();
country.Country = countryDR[2].ToString();
country.Population = int.Parse(countryDR[3].ToString());
country.Languages = languages;
}
/// <summary>
/// Preenche um User com os seus dados
/// </summary>
public override void fillUser( User user )
{
NpgsqlConnection conn = new NpgsqlConnection(OrionGlobals.getConnectionString("connectionStringPG"));
NpgsqlCommand cmd = new NpgsqlCommand("OrionsBelt_UsersGetUser", conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.Add("@user_mail",user.Mail);
try {
conn.Open();
reader = cmd.ExecuteReader();
if( reader.HasRows ) {
reader.Read();
user.Nick = (string) getField("user_nick");
user.UserId = (int) getField("user_id");
if ( getField("user_ruler_id") != DBNull.Value )
user.RulerId = (int) getField("user_ruler_id");
user.EloRanking = (int) getField("user_rank");
user.RegistDate = (DateTime) getField("user_registDate");
user.LastLogin = (DateTime) getField("user_lastLogin");
user.Skin = (int) getField("user_skin");
user.Lang = (string) getField("user_lang");
user.ImagesDir = (string) getField("user_imagesDir");
user.Website = (string) getField("user_website");
user.Avatar = (string) getField("user_avatar");
user.Msn = (string) getField("user_msn");
user.Icq = (string) getField("user_icq");
user.Jabber = (string) getField("user_jabber");
user.Aim = (string) getField("user_aim");
user.Yahoo = (string) getField("user_yahoo");
user.AllianceId = (int) reader["user_alliance_id"];
user.AllianceRank = AllianceInfo.ToAllianceRank( reader["user_alliance_rank"].ToString() );
user.Wins = (int) reader["user_wins"];
user.Losses = (int) reader["user_losses"];
}else {
ExceptionLog.log("User não encontrado", "Mail: " + user.Mail==null?"null":user.Mail );
}
reader = null;
} catch( NpgsqlException e ) {
throw new AlnitakException("Erro ao executar o procedimento 'OrionsBelt_UsersGetUser' @ NpgsqlServerUserutility::fillUser ",e);
} finally {
conn.Close();
}
}
public static List <DBclasses.Pet> getAllPets()
{
List <DBclasses.Pet> result = new List <DBclasses.Pet>();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from pets order by id";
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
DBclasses.Pet row = new DBclasses.Pet();
row.id = (long)reader["id"];
row.kindOfAnimals = (int)reader["kindOfAnimals"];
row.addedByUser = (long)reader["addedByUser"];
row.addedDateTime = reader["addedDateTime"].ToString();
row.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
row.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
row.name = reader["name"].ToString();
row.photos = reader["photos"] as string[];
row.code = reader["code"].ToString();
row.sterilised = (bool)reader["sterilised"];
row.pasported = (bool)reader["pasported"];
row.isLosted = (bool)reader["isLosted"];
row.birthDate = reader["birthDate"].ToString();
row.roughlyBirth = (bool)reader["roughlyBirth"];
row.roughlyDay = (bool)reader["roughlyDay"];
row.roughlyMonth = (bool)reader["roughlyMonth"];
row.organizationID = (int)reader["organizationID"];
row.address = reader["address"].ToString();
row.isDisabled = (bool)reader["isDisabled"];
row.disableDescription = reader["disableDescription"].ToString();
row.price = (long)reader["price"];
row.callTimeFrom = reader["_callTimeFrom"].ToString();
row.callTimeTo = reader["_callTimeTo"].ToString();
row.region = (int)reader["region"];
row.phones = reader["phones"] as string[];
row.vaccinated = (bool)reader["vaccinated"];
row.sex = (bool)reader["sex"];
result.Add(row);
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static Decimal?GetDecimalOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetDecimal(ordinal));
}
}
public static DateTime?GetDateTimeOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetDateTime(ordinal));
}
}
public static Boolean?GetBooleanOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetBoolean(ordinal));
}
}
private string GetSafeString(NpgsqlDataReader reader, int colindex)
{
if(reader.IsDBNull(colindex))
{
return string.Empty;
}
else
{
return reader.GetString(colindex);
}
}
public static Int64?GetInt64OrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetInt64(ordinal));
}
}
protected Incident(NpgsqlDataReader reader, Area area)
{
string tableName = GetTableName(area, false);
_id = Convert.ToInt32(reader[tableName + "_" + Columns.Id]);
_location = new PostGIS.Point(Convert.ToDouble(reader[Columns.X(area)]), Convert.ToDouble(reader[Columns.Y(area)]), Convert.ToInt32(reader[Columns.SRID(area)]));
_simulated = Convert.ToBoolean(reader[tableName + "_" + Columns.Simulated]);
_time = Convert.ToDateTime(reader[tableName + "_" + Columns.Time]);
_type = Convert.ToString(reader[tableName + "_" + Columns.Type]);
_nativeId = Convert.ToString(reader[tableName + "_" + Columns.NativeId]);
}
}//O(1)
public bool isSupplierGotItems(int id) //check is supplier got items, return true if does
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Products\" WHERE \"Supplier\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id != 0);
}//O(1)
}//O(N)
public bool supplierExists(int id) //checks if supplier exists and return true if doesn't exists
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Suppliers\" WHERE \"ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id == 0);
}//O(1)
}//O(1)
public bool isProductExists(int id) //check if product exists, if doesn't returns true
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Products\" WHERE \"Product number\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id == 0);
}//O(1)
}//O(1)
public bool isCustomerOrdered(int id) //checks if customer has an open order, if does reuturn true
{
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Orderd\" WHERE \"Customer ID\" = @ID", this.con)) {
execute.Parameters.AddWithValue("ID", id);
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
return(id != 0);
}//O(1)
public static DBclasses.Organization getOrganizationById(int id)
{
DBclasses.Organization result = new DBclasses.Organization();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from organizations where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.name = reader["name"].ToString();
result.contactPerson = reader["contactPerson"].ToString();
result.emails = reader["emails"] as string[];
result.phones = reader["phones"] as string[];
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.addresses = reader["addresses"] as string[];
result.photo = reader["photo"].ToString();
result.aboutOrg = reader["aboutOrg"].ToString();
result.needHelp = reader["needHelp"].ToString();
result.donation = reader["donation"].ToString();
result.otherOrg = reader["otherOrg"].ToString();
result.linkToVK = reader["linkToVK"].ToString();
result.linkToFB = reader["linkToFB"].ToString();
result.linkToYT = reader["linkToYT"].ToString();
result.linkToTG = reader["linkToTG"].ToString();
result.linkToIG = reader["linkToIG"].ToString();
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
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"); }
}
}//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 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)
}//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)
}//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)
// Initialize data field. It should be initialized once,
// or it would be slow as I don't know what.
//
public Table All()
{
var query = new NpgsqlCommand(command, connection);
switch(type) {
case QueryType.Select:
data = query.ExecuteReader();
break;
case QueryType.Update:
case QueryType.Insert:
query.ExecuteNonQuery();
break;
}
return this;
}
public SummonerRating(NpgsqlDataReader dataReader)
{
Reader reader = new Reader(dataReader);
Map = reader.String().ToMapType();
GameMode = reader.String().ToGameModeType();
Wins = reader.Integer();
Losses = reader.Integer();
Leaves = reader.Integer();
CurrentRating = reader.MaybeInteger();
TopRating = reader.MaybeInteger();
reader.SanityCheck(Fields);
}
}//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)
}//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)
protected override Credit_Score ReaderToObject(Npgsql.NpgsqlDataReader dr)
{
try
{
Credit_Score thisCS = new Credit_Score();
thisCS.iDcreditscore = Convert.ToInt32(dr[0]);
thisCS.score = Convert.ToInt32(dr[1]);
thisCS.grade = Convert.ToString(dr[2]);
thisCS.comment = Convert.ToString(dr[3]);
return(thisCS);
}
catch
{
throw new NotImplementedException();
}
}
public static Npgsql.NpgsqlDataReader GetDataReader(Npgsql.NpgsqlCommand command)
{
if (command != null)
{
Npgsql.NpgsqlDataReader reader = default(Npgsql.NpgsqlDataReader);
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
{
command.Connection = connection;
command.Connection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
}
return(null);
}
public static Location create(Npgsql.NpgsqlDataReader reader)
{
Location l = new Location();
l.id = int.Parse(reader[9].ToString());
l.tenantId = int.Parse(reader[1].ToString());
l.userId = int.Parse(reader[0].ToString());
l.lat = double.Parse(reader[2].ToString());
l.lon = double.Parse(reader[3].ToString());
l.alt = double.Parse(reader[4].ToString());
l.bearing = double.Parse(reader[5].ToString());
l.speed = double.Parse(reader[6].ToString());
l.accuracy = double.Parse(reader[7].ToString());
l.ts = reader[8].ToString();
return(l);
}
private CommittedEvent ReadEventFromDbReader(NpgsqlDataReader reader)
{
StoredEvent<string> rawEvent = ReadEvent(reader);
var document = _translator.TranslateToCommon(rawEvent);
_converter.Upgrade(document);
var payload = _formatter.Deserialize(document.Data, document.EventName);
// TODO: Legacy stuff... we do not have a dummy id with the current schema.
var dummyCommitId = Guid.Empty;
var evnt = new CommittedEvent(dummyCommitId, document.EventIdentifier, document.EventSourceId, document.EventSequence, document.EventTimeStamp, payload, document.EventVersion);
// TODO: Legacy stuff... should move.
if (evnt is ISourcedEvent) { ((ISourcedEvent)evnt).InitializeFrom(rawEvent); }
return evnt;
}
}//O(1)
//orders:
public void addOrder(int product, int customer, int amount)
{
int count = 0; //auto order pk
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("SELECT COUNT(*) FROM \"Orderd\"", this.con))
using (Npgsql.NpgsqlDataReader reader = execute.ExecuteReader())
while (reader.Read()) //count query result always 1 row, so it's one iteration
{
count = reader.GetInt32(0) + 1;
}
using (Npgsql.NpgsqlCommand execute = new NpgsqlCommand("INSERT INTO \"Orderd\"(\"Order ID\", \"Product Number\", \"Customer ID\", \"Quantity\") VALUES (@PK, @ProID, @CusID, @quantity)", this.con)) {
execute.Parameters.AddWithValue("PK", count);
execute.Parameters.AddWithValue("ProID", product);
execute.Parameters.AddWithValue("CusID", customer);
execute.Parameters.AddWithValue("quantity", amount);
execute.ExecuteNonQuery();
}
}//O(1)
public AggregatedChampionStatistics(NpgsqlDataReader dataReader)
{
Reader reader = new Reader(dataReader);
ChampionId = reader.Integer();
Wins = reader.Integer();
Losses = reader.Integer();
Kills = reader.Integer();
Deaths = reader.Integer();
Assists = reader.Integer();
Gold = reader.Integer();
MinionKills = reader.Integer();
reader.SanityCheck(Fields);
}
public List <T> Query(string query)
{
List <T> resultset = new List <T>();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, Connection.Get()))
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dynamic entry = new ExpandoObject();
for (int i = 0; i < reader.FieldCount; i++)
{
Type type = reader.GetFieldType(i);
entry[reader.GetName(i)] = Convert.ChangeType(reader.GetFieldValue <Object>(i), type);
}
resultset.Add(entry);
}
return(resultset);
}
}
public Game(NpgsqlDataReader dataReader)
{
Reader reader = new Reader(dataReader);
Id = reader.Integer();
GameId = reader.Integer();
Map = reader.String().ToMapType();
GameMode = reader.String().ToGameModeType();
GameTime = reader.Time();
Team1Won = reader.Boolean();
Team1Id = reader.Integer();
Team2Id = reader.Integer();
reader.SanityCheck(Fields);
}
public static DBclasses.User getUserById(long id)
{
DBclasses.User result = new DBclasses.User();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from users where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.rank = (int)reader["rank"];
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.email = reader["email"].ToString();
result.phone1 = reader["phone1"].ToString();
result.phone2 = reader["phone2"].ToString();
result.getAsksInfo = reader["getAsksInfo"] as int[];
result.photo = reader["photo"].ToString();
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
/// <summary>
/// Arma la lista de contactos con la información obtenida de la DB de PostgreSQL
/// </summary>
/// <param name="data">DataReader obtenido de la consulta select</param>
/// <returns>Lista de los contactos con su información respectiva</returns>
public List<Contacto> ArmarListaContactosPgSQL(NpgsqlDataReader data)
{
List<Contacto> listaContactos = new List<Contacto>();
// Lee la información y la almacena en cada campo del modelo de vista
while (data.Read())
{
Contacto contacto = new Contacto();
contacto.dpi = (String)data["dpi"] ?? String.Empty;
contacto.nombre = (String)data["nombre"] ?? String.Empty;
contacto.apellido = (String)data["apellido"] ?? String.Empty;
contacto.direccion = (String)data["direccion"] ?? String.Empty;
contacto.telefonoCasa = (String)data["telefono_casa"] ?? String.Empty;
contacto.telefonoMovil = (String)data["telefono_movil"] ?? String.Empty;
contacto.nombreContacto = (String)data["nombre_contacto"] ?? String.Empty;
contacto.numeroContacto = (String)data["numero_telefono_contacto"] ?? String.Empty;
listaContactos.Add(contacto);
}
return listaContactos;
}
public static DBclasses.ArchivePet getArchivePetById(int id)
{
DBclasses.ArchivePet result = new DBclasses.ArchivePet();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from archivepets where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.kindOfAnimals = (int)reader["kindOfAnimals"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.organizationID = (int)reader["organizationID"];
result.name = reader["name"].ToString();
result.photos = reader["photos"] as string[];
result.homeFound = (bool)reader["homeFound"];
result.newOwnerName = reader["newOwnerName"].ToString();
result.newOwnerPhone = reader["newOwnerPhone"].ToString();
result.lastContactWithNewOwner = reader["lastContactWithNewOwner"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public SummonerRankedStatistics(NpgsqlDataReader dataReader)
{
Reader reader = new Reader(dataReader);
ChampionId = reader.Integer();
Wins = reader.Integer();
Losses = reader.Integer();
Kills = reader.Integer();
Deaths = reader.Integer();
Assists = reader.Integer();
MinionKills = reader.Integer();
Gold = reader.Integer();
TurretsDestroyed = reader.Integer();
DamageDealt = reader.Integer();
PhysicalDamageDealt = reader.Integer();
MagicalDamageDealt = reader.Integer();
DamageTaken = reader.Integer();
DoubleKills = reader.Integer();
TripleKills = reader.Integer();
QuadraKills = reader.Integer();
PentaKills = reader.Integer();
TimeSpentDead = reader.Integer();
MaximumKills = reader.Integer();
MaximumDeaths = reader.Integer();
reader.SanityCheck(Fields);
}
public static DBclasses.Ask getAskById(int id)
{
DBclasses.Ask result = new DBclasses.Ask();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from asks where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.addedByUser = (long)reader["addedByUser"];
result.addedDateTime = reader["addedDateTime"].ToString();
result.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
result.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
result.categories = reader["categories"] as int[];
result.name = reader["name"].ToString();
result.desciption = reader["desciption"].ToString();
result.organizationID = (int)reader["organizationID"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
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);
}
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;
}
/// <summary>
/// Establece las propiedades de la flight instance dado un NpgsqlDataReader
/// (miturriaga)
/// </summary>
/// <param name="dr">El NpgsqlDataReader que contiene los datos de la flight instance</param>
public void SetDesde(NpgsqlDataReader dr)
{
this.ID = Convert.ToInt64(dr["flightInstanceID"]);
this.StateID = Convert.ToInt64(dr["state"]);
this.Cost = Convert.ToInt32(dr["cost"]);
this.FlightNumberID = Convert.ToInt64(dr["flightNumber"]);
if (dr["realDeparture"] != DBNull.Value)
{
this.RealDeparture = Convert.ToDateTime(dr["realDeparture"]);
}
else
{
this.RealDeparture = DateTime.MinValue;
}
if (dr["realArrival"] != DBNull.Value)
{
this.RealArrival = Convert.ToDateTime(dr["realArrival"]);
}
else
{
this.RealArrival = DateTime.MinValue;
}
if (dr["estimatedDeparture"] != DBNull.Value)
{
this.EstimatedDeparture = Convert.ToDateTime(dr["estimatedDeparture"]);
}
else
{
this.EstimatedDeparture = DateTime.MinValue;
}
if (dr["estimatedArrival"] != DBNull.Value)
{
this.EstimatedArrival = Convert.ToDateTime(dr["estimatedArrival"]);
}
else
{
this.EstimatedArrival = DateTime.MinValue;
}
this.AircraftID = Convert.ToInt64(dr["aircraftID"]);
}
internal NpgsqlDataReader Execute(CommandBehavior behavior = CommandBehavior.Default)
{
LogCommand();
State = CommandState.InProgress;
try
{
_queryIndex = 0;
_connector.SendAllMessages();
// We consume response messages, positioning ourselves before the response of the first
// Execute.
if (IsPrepared)
{
if ((behavior & CommandBehavior.SchemaOnly) == 0)
{
// No binding in SchemaOnly mode
var msg = _connector.ReadSingleMessage(DataRowLoadingMode.NonSequential);
Contract.Assert(msg is BindCompleteMessage);
}
}
else
{
IBackendMessage msg;
do
{
msg = _connector.ReadSingleMessage();
} while (!ProcessMessageForUnprepared(msg, behavior));
}
var reader = new NpgsqlDataReader(this, behavior, _queries);
reader.Init();
_connector.CurrentReader = reader;
return reader;
}
catch
{
State = CommandState.Idle;
throw;
}
}
/// <summary>
///
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public object npgsqldatareader2arraylist(NpgsqlDataReader dr)
{
ArrayList a = new ArrayList();
if (dr.HasRows || dr != null)
{
while (dr.Read())
{
for (int n = 0; n < dr.FieldCount; n++)
{
var x = dr.GetValue(n).ToString();
a.Add(x);
}
}
}
return a;
}
private Object RetrieveValueWithDateTime(NpgsqlDataReader dr)
{
Value v;
string[] date_str = Convert.ToString(dr["date"]).Split(new char[] { ' ' });
string[] time_str = Convert.ToString(dr["time"]).Split(new char[] { ' ' });
v.dt = Convert.ToDateTime(date_str[0] + " " + time_str[1]);
v.value = Convert.ToSingle(dr["value"]);
v.status = Convert.ToBoolean(dr["status"]);
v.id_taken_params = Convert.ToUInt32(dr["id_taken_params"]);
return (Object)v;
}
public GameTeamPlayer(NpgsqlDataReader dataReader)
: base(dataReader)
{
}
private void Construct(NpgsqlDataReader reader, string table)
{
Construct(Convert.ToInt32(reader[table + "_" + Columns.Id]),
Convert.ToString(reader[table + "_" + Columns.IncidentType]),
new PostGIS.Point(Convert.ToDouble(reader[Columns.X(table)]), Convert.ToDouble(reader[Columns.Y(table)]), Convert.ToInt32(reader[Columns.SRID(table)])),
Convert.ToDateTime(reader[table + "_" + Columns.Time]));
}
internal Point(NpgsqlDataReader reader, string table)
{
Construct(reader, table);
}
/// <summary>
/// Reads SQL Table.
/// Be sure to call sqlclose() after done reading!
/// </summary>
/// <param name="SqlQuery">Reads data from SQL DB, SqlQuery = string SqlQuery = "SELECT * FROM `table` WHERE ID = "+ "'"+charID+"'";</param>
public void SqlRead(string SqlQuery)
{
#region Mysql
//MysqlRead: Create a SqlQuery to send to this wrapper for Reading the DB
if (ismysql)
{
try
{
mcom = new MySqlCommand(SqlQuery);
mcc = new MySqlConnection(ConnectionString_MySQL);
mcom.Connection = mcc;
mcom.CommandTimeout = 10000;
mcc.Open();
myreader = mcom.ExecuteReader();
}
catch (MySqlException me)
{
if (ConfigReadWrite.Instance.CurrentConfig.SqlLog == true)
{
MySqlLogger(me, SqlQuery);
}
else
{
throw me;
}
}
}
#endregion
#region MSSQL
else if (ismssql)
{
try
{
sqlcc = new SqlConnection(ConnectionString_MSSQL);
sqlcom = new SqlCommand(SqlQuery);
sqlcom.Connection = sqlcc;
sqlcom.CommandTimeout = 10000;
if (sqlcc.State == 0)
{
sqlcc.Open();
}
sqlreader = sqlcom.ExecuteReader();
}
catch (SqlException se)
{
if (ConfigReadWrite.Instance.CurrentConfig.SqlLog == true)
{
MsSqlLogger(se, SqlQuery);
}
else
{
throw se;
}
}
}
#endregion
#region Postgresql
else if (isnpgsql)
{
try
{
npgcc = new NpgsqlConnection(ConnectionString_PostGreSQL);
npgcom = new NpgsqlCommand(SqlQuery);
npgcom.Connection = npgcc;
npgcom.CommandTimeout = 10000;
if (npgcc.State == 0)
{
npgcc.Open();
}
npgreader = npgcom.ExecuteReader();
}
catch (NpgsqlException ne)
{
if (ConfigReadWrite.Instance.CurrentConfig.SqlLog == true)
{
PostgressqlLogger(ne, SqlQuery);
}
else
{
throw ne;
}
}
}
#endregion
}
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;
}
internal NpgsqlDataReader Execute(CommandBehavior behavior = CommandBehavior.Default)
{
State = CommandState.InProgress;
try
{
_queryIndex = 0;
_connector.SendAllMessages();
if (!IsPrepared)
{
IBackendMessage msg;
do
{
msg = _connector.ReadSingleMessage();
} while (!ProcessMessageForUnprepared(msg, behavior));
}
var reader = new NpgsqlDataReader(this, behavior, _queries);
reader.Init();
_connector.CurrentReader = reader;
return reader;
}
catch
{
State = CommandState.Idle;
throw;
}
}
public NpgsqlDataReaderEx(MappingSchema schema, NpgsqlDataReader reader)
{
_reader = reader;
_schema = schema;
}
// Get Candidate by ID
public CandidateInfo GetCandidateById(int?canId)
{
CandidateInfo can = new CandidateInfo();
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * from public.us_election_states where id=@ID";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new NpgsqlParameter("ID", canId));
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
can.ID = Convert.ToInt32(dr["ID"].ToString());
can.State = dr["name"].ToString();
can.Republicans = Convert.ToInt32(dr["percent_rep"].ToString());
can.Democrats = Convert.ToInt32(dr["percent_dem"].ToString());
}
conn.Close();
}
return(can);
}
