/// <inheritdoc />
public override Job Get(string qid, string jobId)
{
NpgsqlDataReader reader = null;
try {
// Attempt to get job
Log.Debug("Retrieving job", qid, jobId);
string commandText =
$"BEGIN TRANSACTION;" +
$" SELECT job_id, qid, data, prev_job_ids FROM {TableName}\n" +
$" WHERE job_id = '{jobId}' AND qid = '{qid}';" +
$"END TRANSACTION;";
NpgsqlCommand cmd = new NpgsqlCommand(commandText, this.connection);
reader = cmd.ExecuteReader();
// Access results
if (!reader.HasRows)
{
throw new Exception($"Did not find any results for {jobId} on {qid}");
}
reader.Read();
return(PostgresJob.JobFromReader(reader));
} catch (Exception e) {
throw new Exception($"PostgreSQL: Failed to get Job: {qid},{jobId}", e);
} finally {
reader?.Close();
}
}
public object[] RequestLine(string function, int length, params object[] args)
{
var command = BuildCommand(function, args);
NpgsqlDataReader reader = null;
var resp = new object[length];
try
{
reader = command.ExecuteReader();
if (!reader.HasRows)
{
if (reader != null)
{
reader.Close();
}
if (command != null)
{
command.Dispose();
}
return(null);
}
if (!reader.Read())
{
if (reader != null)
{
reader.Close();
}
if (command != null)
{
command.Dispose();
}
return(null);
}
try
{
for (int i = 0; i < length; i++)
{
resp[i] = reader[i];
}
}
catch (Exception e)
{
return(null);
}
}
catch (Exception)
{
return(null);
}
reader?.Close();
command?.Dispose();
return(resp);
}
public List <object[]> RequestTable(string function, int length, params object[] args)
{
var command = BuildCommand(function, args);
var resp = new List <object[]>();
NpgsqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
if (!reader.HasRows)
{
if (reader != null)
{
reader.Close();
}
if (command != null)
{
command.Dispose();
}
return(null);
}
while (reader.Read())
{
var tmp = new object[length];
try
{
for (int i = 0; i < length; i++)
{
tmp[i] = reader[i];
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
resp.Add(tmp);
}
}
catch (Exception)
{ }
reader?.Close();
command?.Dispose();
return(resp);
}
/// <summary>
/// Opens a view for reading.
/// </summary>
private BinaryReader GetViewReader(string path)
{
string sql = $"SELECT contents FROM {GetTableName(DataCategory.View)} WHERE path = @path LIMIT 1";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("path", NormalizePath(path));
NpgsqlDataReader reader = null;
bool postponeClose = false;
void CloseAction()
{
reader?.Close();
conn.Close();
Monitor.Exit(conn);
}
try
{
Monitor.Enter(conn);
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (reader.Read())
{
if (reader.IsDBNull(0))
{
return(new BinaryReader(new MemoryStream(0)));
}
else
{
postponeClose = true;
return(new ViewReader(reader.GetStream(0), CloseAction));
}
}
}
finally
{
if (!postponeClose)
{
CloseAction();
}
}
throw new FileNotFoundException(string.Format(CommonPhrases.NamedFileNotFound, path));
}
public static Image GetMainImage(NpgsqlConnection connection, long?CarId)
{
Image image = null;
Byte[] pic = null;
if (CarId.HasValue)
{
NpgsqlCommand command = new NpgsqlCommand($"Select picture from {TABLE} where car_id = :cid and main = true", connection);
command.Parameters.AddWithValue("cid", CarId.Value);
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
pic = reader.IsDBNull(0) ? null : (byte[])reader.GetValue(0);
MemoryStream memoryStream = new MemoryStream(pic);
image = Image.FromStream(memoryStream);
memoryStream.Close();
}
reader.Close();
}
return(image);
}
/// <summary>
/// 测试 调用 函数.
/// </summary>
private void CallFunc(NpgsqlConnection conn)
{
// 创建一个 Command.
NpgsqlCommand testCommand = conn.CreateCommand();
// 定义需要执行的SQL语句.
testCommand.CommandText = "SELECT HelloWorld() ";
// 执行SQL命令，结果存储到Reader中.
NpgsqlDataReader testReader = testCommand.ExecuteReader();
// 处理检索出来的每一条数据.
while (testReader.Read())
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("调用函数:{0}; 返回:{1}",
testCommand.CommandText, testReader[0]
);
}
// 关闭Reader.
testReader.Close();
}
private void getSinirsToTextBox()
{
try {
string connString = ConfigurationManager.ConnectionStrings["MyKey"].ConnectionString;
NpgsqlConnection connection = new NpgsqlConnection(connString);
connection.Open();
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM sinir", connection);
NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
txtOgrenciKitap.Text = reader[0].ToString();
txtOgretimKitap.Text = reader[1].ToString();
txtMemurKitap.Text = reader[2].ToString();
txtOgrenciSure.Text = reader[3].ToString();
txtOgretimSure.Text = reader[4].ToString();
txtMemurSure.Text = reader[5].ToString();
reader.Close();
connection.Close();
} catch (Exception ex) {
MessageBox.Show(ex.Message.ToString());
}
}
//BUSCAR A UNO
public Usuario buscarUsuario(int cod)
{
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
string sql = "SELECT \"COD\", \"Nombre\", \"Contrasena\" FROM \"Usuario\"" +
"WHERE \"COD\" = " + cod + "";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
Usuario data = new Usuario();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
data.username = dr[1].ToString();
data.contrasena = dr[2].ToString();
}
dr.Close();
conn.Close();
return(data);
}
public List <Badge> FindByUserID(Guid user_id)
{
List <Badge> list = new List <Badge>();
Badge b;
string query = @"SELECT id, name, description, created_at FROM Badge WHERE user_id = @user_id";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("user_id", user_id);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Guid id = reader.GetGuid(0);
string name = reader.GetString(1);
string desc = reader.GetString(2);
DateTime date = reader.GetDateTime(3);
b = new Badge(id, name, desc, user_id, date);
list.Add(b);
}
reader.Close();
}
return(list);
}
private void materialRaisedButton2_Click_1(object sender, EventArgs e)
{
if (t_m_nombre.Text == "")
{
MessageBox.Show("faltan datos...!!");
}
else
{
m.d_id = id;
m.d_nombre = t_m_nombre.Text;
listar = m.update();
while (listar.Read())
{
MessageBox.Show("Marca " + listar[0]);
listar_marca();
t_m_nombre.Text = "";
b_actualizar.Enabled = false;
b_regis.Enabled = true;
listar.Close();
l_proceso.Text = "ACTUALIZADO";
}
}
}
//this method will charge the information on the table
public void MostrarDatossincombo()
{
bd.Conexion();
Conexion.ConexionBD.conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM aeropuerto", Conexion.ConexionBD.conexion);
NpgsqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
dataGridView2.Rows.Add(reader.GetInt32(0), reader.GetString(1), null, reader.GetString(2));
}
}
finally
{
reader.Close();
cmd.Dispose();
Conexion.ConexionBD.conexion.Close();
}
dataGridView2.Refresh();
dataGridView2.ClearSelection();
}
/// <summary>
/// Gets the geoposition of a given node in the postgresql database. To be used with way GetNodesPostgreSQL() method.
/// </summary>
/// <param name="nodeId">The id of the node</param>
/// <param name="connectionString">The connection string to the Postgresql database. Please refer to <a href="http://www.openstreetmap.org/help">OSM Help</a> for more information.</param>
/// <returns>Returns the position of the node.</returns>
public GeoPosition GetPositionPostgreSQL(String nodeId, String connectionString)
{
GeoPosition temp = null;
NpgsqlConnection con = new NpgsqlConnection(connectionString);
con.Open();
NpgsqlCommand com = new NpgsqlCommand("", con);
com.CommandText = "select id,Latitude,Longitude from node where id='" + nodeId + "'";
NpgsqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
temp = new GeoPosition((int)reader["Latitude"] / scale, (int)reader["Longitude"] / scale);
}
if (!reader.IsClosed)
{
reader.Close();
}
con.Close();
return(temp);
}
private void bntLogin_Click(object sender, EventArgs e)
{
try
{
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432; User Id=postgres;Password=123456; Database=postgres;");
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("Select * from usuario where login= '******' and senha = '" + textBoxSenha.Text + "'", conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
FrmMenu fr2 = new FrmMenu();
fr2.Show();
this.Hide();
}
dr.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <Barrier> readAllBarriers()
{
NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM barriers", connection);
NpgsqlDataReader reader = command.ExecuteReader();
List <Barrier> barriers = new List <Barrier>();
// read the routes themselves
while (reader.Read())
{
String guid = (reader[0] == DBNull.Value) ? null : (String)reader[0];
double x = (double)reader[1];
double y = (double)reader[2];
double angle = (double)reader[3];
Barrier barrier = new Barrier(guid, x, y, angle);
barriers.Add(barrier);
}
reader.Close();
return(barriers);
}
/// <summary>
/// Faz a busca por um determinado usuário quando for buscado por meio de texto
/// </summary>
/// <param name="text_search">É o texto que será usado para pesquisar o usuário</param>
/// <returns>Os dados do usuário encontrado</returns>
public User SearchUser(string text_search)
{
User user = new User();
cmd = new NpgsqlCommand(searchUser, conn);
cmd.Parameters.AddWithValue("@use_name", text_search);
cmd.Parameters.AddWithValue("@use_email", text_search);
reader = cmd.ExecuteReader();
if (reader.Read())
{
user.use_id = (int)reader["use_id"];
user.use_name = reader["use_name"].ToString();
user.use_code = reader["use_code"].ToString();
user.use_email = reader["use_email"].ToString();
user.use_image = reader["use_image"].ToString();
user.use_active = (bool)reader["use_active"];
user.use_excluded = (bool)reader["use_excluded"];
}
reader.Close();
return(user);
}
static public void Dynamic_Search2(NpgsqlConnection con)
{
int team_id;
Console.WriteLine("Input id");
team_id = Convert.ToInt32(Console.ReadLine());
var sql = $"select team.id, team.name, score.game_id, score.score1, score.score2 from team " +
$"inner join game on team.id = game.team1_id" +
$" inner join score on game.id = score.game_id where team.id = {team_id}";
var cmd = new NpgsqlCommand(sql, con);
Execute(cmd);
NpgsqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine($"{rdr.GetName(0),-4}\t {rdr.GetName(1),-4}\t \t{rdr.GetName(2),10}\t {rdr.GetName(3),10} \t {rdr.GetName(4),10}");
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} \t {rdr.GetString(1),-3}\t\t {rdr.GetInt32(2),10} \t\t{rdr.GetInt32(3),10} \t\t {rdr.GetInt32(4),10} ");
}
rdr.Close();
}
public static String[] GetWayIdsInStockholm()
{
NpgsqlConnection con = new NpgsqlConnection(conPostGreGIS);
con.Open();
NpgsqlCommand com = new NpgsqlCommand("", con);
com.CommandText = "select way_Id from boundedlist " + "group by way_Id";
com.CommandText = com.CommandText.Replace(",", ".");
NpgsqlDataReader reader = com.ExecuteReader();
List <String> temp = new List <string>();
while (reader.Read())
{
temp.Add(reader[0].ToString());
}
if (!reader.IsClosed)
{
reader.Close();
}
con.Close();
return(temp.ToArray());
}
private Boolean LoginUser(String username, String password)
{
String query = "select id, login, password, \"isPasswordLimited\", " +
"(select case when exists(select * from blocked_users where blocked_users.id = users.id) " +
"THEN CAST(1 AS BOOLEAN) ELSE CAST(0 AS BOOLEAN) END) as isBlocked from users where login='******'";
NpgsqlCommand getCommand = new NpgsqlCommand(query);
getCommand.Connection = dbConfig.getConnection();
NpgsqlDataReader result = getCommand.ExecuteReader();
bool isNotEmpty = result.Read() && passwordSymbolsAreCorrect(result.GetString(2), password);
if (isNotEmpty)
{
User.id = result.GetInt32(0);
User.login = result.GetString(1);
User.password = result.GetString(2);
User.isSpecialPasswordEnabled = result.GetBoolean(3);
User.isBlocked = result.GetBoolean(4);
}
result.Close();
getCommand.Dispose();
return(isNotEmpty);
}
private static bool Check_enough_cards_store()
{
var con = new NpgsqlConnection(DBManagment.cs);
string sql = "Select count(*) from store;";
var cmd = new NpgsqlCommand(sql, con);
con.Open();
NpgsqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
int count_cards = rdr.GetInt32(0);
rdr.Close();
con.Close();
if (count_cards >= 4)
{
return(true);
}
else
{
return(false);
}
}
void EstablishConnection() //Kopplar upp oss till rätt databas
{
string sqlCommand = "Server=pgserver.mah.se; Port=5432; User Id = ah9353; Password = khz0g68k; Database = mfrockfestival2";
conn = new NpgsqlConnection(sqlCommand);
conn.Open();
cmd = new NpgsqlCommand("SELECT version(); ", conn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Console.WriteLine("connection established");
Console.WriteLine("{0}", dr[0]);
Console.WriteLine();
//dr[int ordinal] dr[string name]
}
else
{
Console.WriteLine("Data does not exist");
}
dr.Close(); //Stänger av kommandot så att man kan skriva in nya kommandom till databasen
}
//Metodos
public CitaDTO ObtenerCita(DateTime horario)
{
CitaDTO cita = new CitaDTO();
conn.Open();
comm.Connection = conn;
comm.CommandText = "select * from cita where horario = '" + horario + "';";
comm.ExecuteNonQuery();
LeerFilas = comm.ExecuteReader();
if (LeerFilas.HasRows)
{
while (LeerFilas.Read())
{
cita.Numero_Cita = LeerFilas.GetInt32(0);
cita.Nss = LeerFilas.GetInt32(1);
cita.Cedula = LeerFilas.GetString(2);
cita.Horario = LeerFilas.GetDateTime(3);
}
}
LeerFilas.Close();
conn.Close();
return(cita);
}
public static string getAllUsers()
{
NpgsqlConnection connection = DBConnectionService.getFreeConnection();
NpgsqlCommand command = new NpgsqlCommand(getAllUsersCommand, connection);
NpgsqlDataReader reader = command.ExecuteReader();
string result;
if (reader.HasRows)
{
reader.Read();
result = reader.GetString(0);
}
else
{
result = "[]";
}
reader.Close();
DBConnectionService.returnConnection(connection);
return(result);
}
public Badge FindByName(string name)
{
Badge b;
string query = @"SELECT id,description FROM badges WHERE name = @name";
using (var cmd = new NpgsqlCommand(query, _connection, _transaction))
{
cmd.Parameters.AddWithValue("name", name);
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Guid id = reader.GetGuid(0);
string description = reader.GetString(1);
b = new Badge(id, name, description);
}
else
{
return(null);
}
reader.Close();
}
return(b);
}
private void CarregaComboBoxGen()
{
ClassControl.conectar();
string sql = "SELECT COUNT(*) FROM genero";
NpgsqlCommand cmd = new NpgsqlCommand(sql, ClassControl.cn);
sql = "SELECT nome_gen FROM genero";
cmd.CommandText = sql;
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
cmbGeneros.Items.Add("---");
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
cmbGeneros.Items.Add(dr.GetString(i));
}
}
}
dr.Close();
}
private void altValue(NpgsqlCommand command)
{
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=" + login + ";Password="******";Database=clinic;");
command.Connection = conn;
NpgsqlDataReader reader = null;
try
{
conn.Open();
reader = command.ExecuteReader();
reader.Close();
}
catch (Exception m)
{
MessageBox.Show(m.Message);
}
finally
{
conn.Close();
Close();
}
}
//this method will charge the informacion on the datagridview
public void mostrarInfo(DataGridView data)
{
bd.Conexion();
ConexionBD.conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM hotel", ConexionBD.conexion);
NpgsqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
data.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetString(4));
}
}
finally
{
reader.Close();
cmd.Dispose();
ConexionBD.conexion.Close();
}
data.Refresh();
data.ClearSelection();
}
public void MostrarDatosTabla(DataGridView data)
{
connection = conexion1.Conexion();
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM aeropuertos order by nombre", connection);
NpgsqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
data.Rows.Add(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3));
}
}
finally
{
reader.Close();
cmd.Dispose();
connection.Close();
}
data.Refresh();
data.ClearSelection();
}
public List <DbEntityRelation> Read()
{
using (DbConnection con = DbContext.Current.CreateConnection())
{
NpgsqlCommand command = con.CreateCommand("SELECT json FROM entity_relations;");
using (NpgsqlDataReader reader = command.ExecuteReader())
{
JsonSerializerSettings settings = new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.Auto
};
List <DbEntityRelation> relations = new List <DbEntityRelation>();
while (reader.Read())
{
DbEntityRelation relation = JsonConvert.DeserializeObject <DbEntityRelation>(reader[0].ToString(), settings);
relations.Add(relation);
}
reader.Close();
return(relations);
}
}
}
public List <Telefono> listaTelefonos(int id_cliente, string schema, NpgsqlConnection conexion)
{
List <Telefono> listaTelefonos = new List <Telefono>();
NpgsqlCommand cmd = null;
NpgsqlDataReader dr = null;
try
{
cmd = new NpgsqlCommand("logueo.spgettelefonosclientes", conexion);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("parm_idcliente", id_cliente);
cmd.Parameters.AddWithValue("parm_schema", schema);
//tran = conexion.BeginTransaction();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Telefono telefono = new Telefono();
telefono.id = Convert.ToInt32(dr["ID"].ToString());
telefono.id_cliente = Convert.ToInt32(dr["IDCLIENTE"].ToString());
telefono.telefono = dr["TELEFONO"].ToString();
telefono.descripcion = dr["DESCRIPCION"].ToString();
telefono.fecha = Convert.ToDateTime(dr["FECHA"].ToString());
telefono.es_activo = Convert.ToBoolean(dr["ESACTIVO"].ToString());
listaTelefonos.Add(telefono);
}
dr.Close();
}
catch (Exception e)
{
listaTelefonos = null;
throw (e);
}
finally
{
}
return(listaTelefonos);
}
//REPORTE 7 DE LOS REQUERIMIENTOS
public List <Empleado> obtenerReporte7R()
{
List <Empleado> data = null;
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
string sql = "Select e.\"CI\", e.\"Nombre\"||' '||e.\"Apellido\", to_char(a.\"Fecha\", 'DD-MM-YYYY') " +
"from \"Empleado\" e, \"Asistencia\" a " +
"where a.\"CIEmpleado\"=e.\"CI\" " +
"order by e.\"Nombre\", e.\"Apellido\", a.\"Fecha\"";
try
{
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
data = new List <Empleado>();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.Add(new Empleado()
{
CI = Int32.Parse(dr[0].ToString()),
Nombre = dr[1].ToString(),
horario = dr[2].ToString(),
});
}
dr.Close();
}
catch (Exception e)
{
conn.Close();
}
conn.Close();
return(data);
}
