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