/// <summary>
/// Se obtiene de la base de datos el password actual del usuario
/// </summary>
/// <param name="entidad">Entidad como instancia de usuario</param>
public Entidad ObtenerPassword(Entidad entidad)
{
try
{
usuario = (Usuario)entidad;
base.Conectar();
base.Comando = base.SqlConexion.CreateCommand(); //Crea el comando
base.Comando.CommandText = "ConsultarContrasena";
base.Comando.CommandType = CommandType.StoredProcedure;
parametro = new NpgsqlParameter();
parametro.NpgsqlDbType = NpgsqlDbType.Varchar; //Ingresa parametros de entrada
parametro.Value = usuario.NombreUsuario;
base.Comando.Parameters.Add(parametro);
leerDatos = base.Comando.ExecuteReader(); //Ejecuta el comando
if (leerDatos.Read())
{
usuario.Clave = leerDatos.GetString(0);
}
leerDatos.Close(); //Cierra el Data Reader
base.Desconectar(); //Culmina la sesion con la base de datos
log.Info("Se pudo consultar la contraseña del usuario");
return(usuario);
}
catch (NpgsqlException e)
{
throw new BaseDeDatosExcepcion(e);
}
catch (Exception e)
{
log.Error("No se pudo obtener la contraseña del usuario ");
throw e;
}
}
private void createPacient()
{
panelPacient.Visible = true;
Point point = new Point(12, 12);
panelPacient.Location = point;
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=" + login + ";Password="******";Database=clinic;");
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM public.\"" + table + "\" WHERE id = " + id, conn);
NpgsqlDataReader reader = null;
try
{
conn.Open();
reader = command.ExecuteReader();
int i = reader.VisibleFieldCount;
if (i > 0 && reader.HasRows)
{
while (reader.Read())
{
textBox2.Text = reader.GetString(1);
dateTimePicker1.Value = reader.GetDateTime(2);
numericUpDown1.Value = reader.GetInt32(3);
numericUpDown2.Value = reader.GetInt32(4);
}
}
else
{
MessageBox.Show("Ошибка!"); Close(); reader.Close(); conn.Close();
}
reader.Close();
}
catch (Exception m)
{
MessageBox.Show(m.Message);
}
finally
{
conn.Close();
}
}
public IHttpActionResult getStagesProject(int id)
{
List <Etapa> stages = new List <Etapa>();
using (NpgsqlConnection connection = DataBase.getConnection())
{
NpgsqlCommand command = new NpgsqlCommand("obteneretapasproyecto", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pidproyecto", NpgsqlDbType.Integer).Value = id;
try
{
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
int counter = 0;
while (reader.Read())
{
Etapa stage = new Etapa();
stage._id = reader.GetInt32(0);
stage._nombre = reader.GetString(1);
stage._fInicio = reader.GetDateTime(2);
stage._fFin = reader.GetDateTime(3);
stage._costo = reader.GetDecimal(4);
stages.Add(stage);
counter++;
}
if (counter > 0)
{
return(Json(stages));
}
else
{
return(Json(1));
}
}
catch (NpgsqlException ex) { return(Json(2)); }
finally { connection.Close(); }
}
}
private void ProductCD_ListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
dynamic PC = ProductCD_ListBox.SelectedItem as dynamic;
int id = PC.id;
string Name = PC.Name;
btn_edit.IsEnabled = true;
NpgsqlConnection connection = new NpgsqlConnection(connstring);
// connection.Close();
connection.Open();
//this.Dispatcher.Invoke(() => { LoginProcessingText.Text = "Process: Getting All Warehouse Present"; });
NpgsqlCommand cmd_ProductCAT_GetData = new NpgsqlCommand("select m_product_category_id,Name,searchkey,categoryColour,DigitalMenu,selfService from m_product_category where m_product_category_id=" + id + " and ad_org_id=" + _OrgId + " and Name='" + Name + "' ;", connection);//
NpgsqlDataReader _ProductCAT_GetData = cmd_ProductCAT_GetData.ExecuteReader();
_ProductCAT_GetData.Read();
txtProductCATID.Text = _ProductCAT_GetData.GetString(0);
txtName.Text = _ProductCAT_GetData.GetString(1);
txtSearchKey.Text = _ProductCAT_GetData.GetString(2);
txtCategory.Text = _ProductCAT_GetData.GetString(3);
if (_ProductCAT_GetData.GetString(4) != null)
{
if (_ProductCAT_GetData.GetString(4) == "Y")
{
CBL_DigitalMenu.IsChecked = true;
}
}
if (_ProductCAT_GetData.GetString(5) != null)
{
if (_ProductCAT_GetData.GetString(5) == "Y")
{
Self_Service.IsChecked = true;
}
}
connection.Close();
}
public Dictionary <int, string> GetMarkets()
{
lock (_lockObject)
{
Dictionary <int, string> marketsDictionary = new Dictionary <int, string>();
using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(ConnectionString))
{
npgsqlConnection.Open();
const string sqlCommandText = "select * from mlb.get_markets()";
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(sqlCommandText, npgsqlConnection))
{
npgsqlCommand.CommandType = CommandType.Text;
using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader())
{
if (!npgsqlDataReader.HasRows)
{
const string message = "GetMarkets() has no rows";
Logger.Info(message);
return(marketsDictionary);
}
while (npgsqlDataReader.Read())
{
int id = npgsqlDataReader.GetInt("market_id");
string name = npgsqlDataReader.GetString("name");
marketsDictionary[id] = name;
}
return(marketsDictionary);
}
}
}
}
}
public List <Player> GetAllPlayers()
{
List <Player> players = new List <Player>();
string query = @"SELECT * FROM ""player"" p
LEFT JOIN
(SELECT playerId, SUM(xpvalue) as xp FROM exp GROUP BY playerId) e ON p.playerId = e.playerId";
Player p;
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
p = new Player(reader.GetGuid(0), reader.GetString(1), new Exp(reader.GetInt32(2)));
players.Add(p);
}
}
}
return(players);
}
public List <Products> ListProducts()
{
NpgsqlConnection connection = Database.openConnection();
List <Products> listProducts;
Products product;
try
{
string sql = "SELECT id, nome, precosugerido FROM negocio.produtos WHERE ativado = true ORDER BY nome ASC";
NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
NpgsqlDataReader dataReader = cmd.ExecuteReader();
listProducts = new List <Products>();
while (dataReader.Read())
{
product = new Products
{
Id = dataReader.GetInt32(dataReader.GetOrdinal("id")),
Name = dataReader.GetString(dataReader.GetOrdinal("nome")),
Price = dataReader.GetDouble(dataReader.GetOrdinal("precosugerido"))
};
listProducts.Add(product);
}
cmd.Dispose();
return(listProducts);
}
catch (NpgsqlException npgEx)
{
throw new Exception("Erro ao listar os produtos do banco de dados! " + npgEx.Message);
}
catch (Exception e)
{
throw new Exception("Erro ao listar os produtos do banco de dados! " + e.Message);
}
finally
{
connection.Close();
}
}
public bool ogunSorgu()
{
try
{
conn.Open();
sql = @"select * from ogun_sorgu()";
cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ogun = rdr.GetString(0);
ogun_ucret = rdr.GetDouble(1);
}
conn.Close();
return(true);
}
catch (Exception)
{
conn.Close();
return(false);
}
}
//Add user - also saving RSA keys + Email Encryption
//Note: this will not work since the columns encryption, publicKey, privateKey where removed from users table (re-create them to work)
/*
* public void AddUser(string email, string name, string surname)
* {
* RSAHash rsaHash = new RSAHash();
* string pubKey = rsaHash.GetPublicKey(); //XML version
* string privKey = rsaHash.GetPrivateKey(); //XML version
* string emailEnc = rsaHash.Encrypt(email, pubKey, 4096);
*
* //To avoid sql injection we add the values using parameters such as @email.
* string sql = "INSERT INTO users (email, name, surname, lastloggedin, encryption, publicKey, privateKey) VALUES(@email, @name, @surname, @lastLoggedIn, @enc, @pubKey, @privKey)";
* NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
* cmd.Parameters.AddWithValue("@email", email);
* cmd.Parameters.AddWithValue("@name", name);
* cmd.Parameters.AddWithValue("@surname", surname);
* cmd.Parameters.AddWithValue("@lastLoggedIn", DateTime.UtcNow);
* cmd.Parameters.AddWithValue("@enc", emailEnc);
* cmd.Parameters.AddWithValue("@pubKey", pubKey);
* cmd.Parameters.AddWithValue("@privKey", privKey);
*
*
* //Connection is Opend and closed after Adding the new user
* MyConnection.Open();
* cmd.ExecuteNonQuery();
* MyConnection.Close();
* }
*/
#endregion
#region Methods used for Email RSA enc handling
//Returns Encryption of the email passed.
public string GetEmailEnc(string email)
{
string sql = "select encryption from users where email = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
string enc = null;
MyConnection.Open();
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
enc = reader.GetString(0);
}
}
MyConnection.Close();
return(enc);
}
public static string getConfig(string sConfigStr)
{
NpgsqlDataReader odr = null;
try
{
odr = GetDataReader("select value from config where name='" + sConfigStr + "'");
string sValue = odr.GetString(0);
return(sValue);
}
catch (Exception ex)
{
return("");
}
finally
{
if (!odr.IsClosed)
{
odr.Close();
}
}
}
public double GetTimeFindByMask(int num)
{
var startTime = DateTime.Now;
NpgsqlConnection conn = new NpgsqlConnection(ConnectStr);
conn.Open();
var com = String.Format("SELECT * FROM test" + num + " WHERE name LIKE 'new5%'");
NpgsqlCommand cmd = new NpgsqlCommand(com, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
var carmodels = new List <CarModel>();
while (dr.Read())
{
carmodels.Add(new CarModel {
id = dr.GetInt32(0), name = dr.GetString(1), cargotype_id = dr.GetInt32(2), payload = dr.GetFloat(3), price_buy = dr.GetDouble(4), price_sell = dr.GetDouble(5)
});
}
conn.Close();
double dtime = (DateTime.Now - startTime).TotalSeconds;
return(dtime);
}
static int getCount(string functionText, Dictionary <string, int> paramaters)
{
string commandText = "select " + functionText + "(" + appUserIdParam + ")";
using (var connection = new NpgsqlConnection(AppSettings.ConnectionString))
{
connection.Open();
var command = new NpgsqlCommand(commandText, connection);
command.CommandType = CommandType.Text;
foreach (KeyValuePair <string, int> paramater in paramaters)
{
command.Parameters.AddWithValue(paramater.Key, paramater.Value);
}
NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
return(int.Parse(reader.GetString(0)));
}
}
public static string Show_scoreboard(string i_username)
{
if (has_session(i_username) == true)
{
var con = new NpgsqlConnection(cs);
string result = "";
string sql = "Select * from scoreboard";
var cmd = new NpgsqlCommand(sql, con);
con.Open();
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result += "Username: "******" - Fights: " + rdr.GetInt32(4) + " - Wins: " + rdr.GetInt32(1) +
" - Defeats: " + rdr.GetInt32(2) + " - Draws: " + rdr.GetInt32(3) + " - Elo: " + rdr.GetInt32(5) + " - KD: " + rdr.GetInt32(6) + "\r\n";
}
return(result);
}
else
{
return("Error: User is not logged in / Invalid Token!");
}
}
public static Clan getClanDB(object valor, int type)
{
try
{
Clan clan = new Clan();
if (type == 1 && (int)valor <= 0 || type == 0 && string.IsNullOrEmpty(valor.ToString()))
{
return(clan);
}
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
string str = type == 0 ? "clan_name" : "clan_id";
NpgsqlCommand command = npgsqlConnection.CreateCommand();
npgsqlConnection.Open();
command.Parameters.AddWithValue("@valor", valor);
command.CommandText = "SELECT * FROM clan_data WHERE " + str + "=@valor";
command.CommandType = CommandType.Text;
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
while (npgsqlDataReader.Read())
{
clan._id = npgsqlDataReader.GetInt32(0);
clan._rank = npgsqlDataReader.GetInt32(1);
clan._name = npgsqlDataReader.GetString(2);
clan.owner_id = npgsqlDataReader.GetInt64(3);
clan._logo = (uint)npgsqlDataReader.GetInt64(4);
clan._name_color = npgsqlDataReader.GetInt32(5);
}
command.Dispose();
npgsqlDataReader.Close();
npgsqlConnection.Dispose();
npgsqlConnection.Close();
}
return(clan._id == 0 ? new Clan() : clan);
}
catch
{
return(new Clan());
}
}
public ActionResult PurchaseManagment()
{
List <PurchaseManagmentModels> Data = new List <PurchaseManagmentModels>();
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionString))
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT id, condition FROM public.currentpurchase", connection);
NpgsqlDataReader dataReader = command.ExecuteReader();
for (int i = 0; dataReader.Read(); i++)
{
Data.Add(new PurchaseManagmentModels {
Id = dataReader.GetInt32(0), Condition = dataReader.GetString(1)
});
}
connection.Close();
}
Data = Data.OrderBy(x => x.Id).ToList();
ViewBag.Data = Data;
return(View());
}
/// <summary>
/// Retorna la informacion completa de un usuario
/// mediante el nombre dado por parametro
/// </summary>
/// <param name="nombre"></param>
/// <returns> los datos completos cedula[0], nombre[1] y tipo de usuario[3] </returns>
public int ConsultarCedulaUsuario(String nombre)
{
int cedulaUsurio = 0;
Conexion();
conexion.Open();
NpgsqlCommand consulta = new NpgsqlCommand("SELECT * FROM usuarios WHERE nombre='" + nombre + "'", conexion);
NpgsqlDataReader lectorConsulta = consulta.ExecuteReader();
if (lectorConsulta.HasRows)
{
while (lectorConsulta.Read())
{
//cedula, nombre y tipo de usuario
//informacionUsuario = lectorConsulta.GetString(0) + ";" + lectorConsulta.GetString(1) + ";"+ lectorConsulta.GetString(3);
cedulaUsurio = int.Parse(lectorConsulta.GetString(0));
}
}
conexion.Close();
return(cedulaUsurio);
}
/// <summary>
/// Consulta los lugares turisticos por nombre, o similiares.
/// </summary>
/// <param name="busqueda">Palabra cuya similitud se busca en el nombre de la actividad que se esta buscando.</param>
/// <returns>Retorna una lista con las actividades que tengan coincidencia.</returns>
public List <Actividad> ConsultarActividades(string busqueda)
{
List <Actividad> list_actividades = new List <Actividad>();
try
{
con = new ConexionBase();
con.Conectar();
comm = new NpgsqlCommand("consultar_actividades", con.SqlConexion);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Varchar, busqueda);
pgread = comm.ExecuteReader();
//Se recorre los registros devueltos.
while (pgread.Read())
{
Actividad actividad = new Actividad();
actividad.Id = pgread.GetInt32(0);
actividad.Nombre = pgread.GetString(1);
//actividad.Foto = pgread.GetString(2);
list_actividades.Add(actividad);
}
con.Desconectar();
return(list_actividades);
}
catch (NpgsqlException e)
{
con.Desconectar();
throw e;
}
catch (Exception e)
{
con.Desconectar();
throw e;
}
}
public IHttpActionResult register(Etapa pEtapa)
{
using (NpgsqlConnection connection = DataBase.getConnection())
{
NpgsqlCommand command = new NpgsqlCommand("registraretapa", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pnombre", NpgsqlDbType.Text).Value = pEtapa._nombre;
command.Parameters.AddWithValue("@pdescripcion", NpgsqlDbType.Text).Value = pEtapa._descripcion;
try
{
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
return(Json(new Response(reader.GetString(0))));
}
catch (NpgsqlException ex) { return(Json(new Response(Constants.ERROR_DATABASE_CONNECTION))); }
finally { connection.Close(); }
}
}
public IHttpActionResult endStage(int id)
{
using (NpgsqlConnection connection = DataBase.getConnection())
{
NpgsqlCommand command = new NpgsqlCommand("marcaretapa", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pidrelacion", NpgsqlDbType.Integer).Value = id;
try
{
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
var result = reader.GetString(0);
return(Json(new Response(result)));
}
catch (NpgsqlException ex) { return(Json(new Response(Constants.ERROR_DATABASE_CONNECTION))); }
finally { connection.Close(); }
}
}
public User FindById(Guid id)
{
string query =
"SELECT username from \"user\" WHERE userId = @id";
string name = "";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("id", id);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
name = reader.GetString(0);
}
}
}
User u = new User(id, name, new Exp(GetExp(id)));
return(u);
}
public Boolean authenticateUser(string username, string password, out string errorMessage)
{
errorMessage = null;
string result = null;
using (var connection = new NpgsqlConnection(ConfigParser.ConnString))
{
try
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText = "SELECT password FROM playground.public.user WHERE email = (@p)";
command.Parameters.AddWithValue("p", username);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
result = reader.GetString(0);
}
if (result == password)
{
return(true);
}
errorMessage = "Not found or no match";
return(false);
}
catch (Exception e)
{
Console.WriteLine(e);
errorMessage = e.ToString();
throw;
}
}
}
// public const string Fields = "id, diarienummer, startdate, enddate, status, motpartstyp, SBKavtalsid, scan_url, orgnummer, enligt_avtal, internt_alias, kommentar";
public static List <Person> GetPersons(NpgsqlDataReader reader)
{
var lst = new List <Person>();
while (reader.Read())
{
lst.Add(new Person
{
id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Belagenhetsadress = reader.GetString(3),
Postnummer = reader.GetString(4),
Postort = reader.GetString(5),
Telefonnummer = reader.GetString(6),
epost = reader.GetString(7)
});
}
return(lst);
}
public IEnumerable <string> FetchSchUtils()
{
// Connect to a PostgreSQL database
NpgsqlConnection conn = new NpgsqlConnection(_configuration["ConnectionStrings:WBESArchiveConnection"]);
conn.Open();
// Define query
NpgsqlCommand cmd = new NpgsqlCommand("select name from public.isgs_gen_names order by name", conn);
// Execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
List <string> utilNames = new List <string>();
// Read all rows and output the first column in each row
while (dr.Read())
{
utilNames.Add(dr.GetString(0));
}
return(utilNames);
}
public void MostrarInformacionPaisModificar(ComboBox pais, TextBox nombre)
{
try
{
Conexion();
conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT nombre FROM paises WHERE identificador = '" + pais.SelectedItem.ToString() + "'", conexion);
NpgsqlDataReader leer = cmd.ExecuteReader();
if (leer.HasRows)
{
while (leer.Read())
{
nombre.Text = leer.GetString(0);
}
conexion.Close();
}
}
catch (Exception error)
{
Console.WriteLine(error);
}
}
//Método responsável por retornar um Diretor cujo ID seja igual o passado pelo parâmetro.
public Diretor getOne(int id)
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_diretor WHERE cod_diretor = :id";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = id;
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
Diretor d = new Diretor();
d.Cod_diretor = dr.GetInt32(0);
d.Nome_diretor = dr.GetString(1);
//É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
d.filmes = this.getFilmes(d.Cod_diretor);
return(d);
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
return(null);
}
public Usuario BuscarUsuarioEspecifico(int Id_U)
{
using (NpgsqlConnection con = conexion.GetConexion())
{
con.Open();
string sql = "SELECT id_usuario,correo,contrasennia,id_rol FROM users.usuario where id_usuario=@id_u";
using (var command = new NpgsqlCommand(sql, con))
{
command.Parameters.AddWithValue("@id_u", Id_U);
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
return(new Usuario(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3)));
}
}
}
con.Close();
}
return(null);
}
public List <Usuario> ListarUsuarios()
{
List <Usuario> usuarios = new List <Usuario>();
using (NpgsqlConnection con = conexion.GetConexion())
{
con.Open();
string sql = "SELECT id_usuario,correo,id_rol FROM users.usuario";
using (var command = new NpgsqlCommand(sql, con))
{
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
usuarios.Add(new Usuario(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2)));
}
}
}
}
return(usuarios);
}
private Tuple <string, int> TestForUnscrapedTag()
{
if (testForUnscrapedTagCommand == null)
{
testForUnscrapedTagCommand = connection.CreateCommand();
testForUnscrapedTagCommand.CommandText = "SELECT tag, id FROM tags WHERE scraped = FALSE AND tag != ''";
}
NpgsqlDataReader dataReader = testForUnscrapedTagCommand.ExecuteReader();
if (dataReader.Read())
{
string tag = dataReader.GetString(0);
int id = dataReader.GetInt32(1);
dataReader.Dispose();
return(new Tuple <string, int>(tag, id));
}
else
{
dataReader.Dispose();
return(null);
}
}
public string ShowLeaderBoard()
{
string sqlLeaderBoard = "SELECT username, wins, lose, score, ROUND(wins / lose::numeric, 3) AS winratio FROM \"user\" WHERE lose <> 0 ORDER BY score DESC, winratio DESC";
string leaderBoard = "";
int i = 1;
_conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(sqlLeaderBoard, _conn);
cmd.Prepare();
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
leaderBoard += $"\n{i++}. {reader.GetString(0)}: Wins:{reader.GetInt32(1)} Lose:{reader.GetInt32(2)} Score:{reader.GetInt32(3)} WinRatio:{reader.GetValue(4)}";
}
_conn.Close();
return(leaderBoard);
}
internal static List <City> GetCitiesByState(State state)
{
List <City> cities = new List <City>();
using (NpgsqlConnection connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
NpgsqlCommand cmd = connection.CreateCommand();
cmd.CommandText = $"Select distinct city from Business where st = '{state.Name}' order by city;";
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
City city = new City();
city.Name = reader.GetString(0);
cities.Add(city);
}
}
}
return(cities);
}
//
// GetUserFromReader
// A helper function that takes the current row from the NpgsqlDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(NpgsqlDataReader reader)
{
object providerUserKey = new Guid(reader.GetValue(0).ToString());
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string email = reader.IsDBNull(2) ? "" : reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? "" : reader.GetString(3);
string comment = reader.IsDBNull(4) ? "" : 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.Now : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.Now : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.Now : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.Now : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.Now : reader.GetDateTime(11);
MembershipUser u =
new MembershipUser(this.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut,
creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate);
return u;
}
NpgsqlDbColumn LoadColumnDefinition(NpgsqlDataReader reader)
{
var columnName = reader.GetString(reader.GetOrdinal("attname"));
var column = new NpgsqlDbColumn
{
AllowDBNull = !reader.GetBoolean(reader.GetOrdinal("attnotnull")),
BaseCatalogName = _connection.Database,
BaseColumnName = columnName,
BaseSchemaName = reader.GetString(reader.GetOrdinal("nspname")),
BaseServerName = _connection.Host,
BaseTableName = reader.GetString(reader.GetOrdinal("relname")),
ColumnName = columnName,
ColumnOrdinal = reader.GetInt32(reader.GetOrdinal("attnum")) - 1,
ColumnAttributeNumber = (short)(reader.GetInt16(reader.GetOrdinal("attnum")) - 1),
IsKey = reader.GetBoolean(reader.GetOrdinal("isprimarykey")),
IsReadOnly = !reader.GetBoolean(reader.GetOrdinal("is_updatable")),
IsUnique = reader.GetBoolean(reader.GetOrdinal("isunique")),
DataTypeName = reader.GetString(reader.GetOrdinal("typname")),
TableOID = reader.GetFieldValue<uint>(reader.GetOrdinal("attrelid")),
TypeOID = reader.GetFieldValue<uint>(reader.GetOrdinal("typoid"))
};
var defaultValueOrdinal = reader.GetOrdinal("default");
column.DefaultValue = reader.IsDBNull(defaultValueOrdinal) ? null : reader.GetString(defaultValueOrdinal);
column.IsAutoIncrement = column.DefaultValue != null && column.DefaultValue.StartsWith("nextval(");
ColumnPostConfig(column, reader.GetInt32(reader.GetOrdinal("atttypmod")));
return column;
}
