public void Bug1011241_DiscardAll()
{
var connection = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
connection.Open();
if (connection.PostgreSqlVersion < new Version(8, 3, 0)
|| new NpgsqlConnectionStringBuilder(ConnectionString).Protocol == ProtocolVersion.Version2)
{
connection.Close();
return;
}
using (var command = connection.CreateCommand())
{
command.CommandText = "SHOW SEARCH_PATH";
Assert.AreEqual("public", command.ExecuteScalar());
command.CommandText = "SET SEARCH_PATH = \"$user\"";
command.ExecuteNonQuery();
command.CommandText = "SHOW SEARCH_PATH";
Assert.AreEqual("\"$user\"", command.ExecuteScalar());
}
connection.Close();
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SHOW SEARCH_PATH";
Assert.AreEqual("public", command.ExecuteScalar());
}
connection.Close();
}
public static User CreateUser(User user)
{
NpgsqlConnection conn = new NpgsqlConnection (conn_string);
NpgsqlCommand comm = conn.CreateCommand ();
// Add data to the User table
string sql = "INSERT INTO users (name, email) VALUES (@name, @email)";
comm.CommandText = sql;
comm.Parameters.Add ("@name", user.Name);
comm.Parameters.Add ("@email", user.Email);
conn.Open ();
comm.ExecuteNonQuery ();
comm.Dispose ();
User new_user = GetUser (user.Name);
// Add data to the OpenID table
comm = conn.CreateCommand ();
sql = "INSERT INTO openids (id, userid) VALUES (@id, @userid)";
comm.CommandText = sql;
comm.Parameters.Add ("@id", user.OpenID);
comm.Parameters.Add ("@userid", new_user.ID);
comm.ExecuteNonQuery ();
comm.Dispose ();
conn.Close ();
return new_user;
}
public IDocumentStore Initialize(Action<MartenRegistry> register = null)
{
var builder = new NpgsqlConnectionStringBuilder(_targetConnectionString);
var targetDatabaseName = builder.Database;
using (var connection = new NpgsqlConnection(_masterConnectionString))
{
connection.Open();
var existsCommand = connection.CreateCommand();
existsCommand.CommandText = "select (count(*) > 0)::boolean as exists from pg_database where datname=:0";
existsCommand.Parameters.Add(new NpgsqlParameter("0", targetDatabaseName));
var exists = (bool)existsCommand.ExecuteScalar();
if (!exists)
{
var createCommand = connection.CreateCommand();
createCommand.CommandText = string.Format("CREATE DATABASE \"{0}\"", targetDatabaseName);
createCommand.ExecuteNonQuery();
}
}
var store = DocumentStore.For(cfg =>
{
cfg.Connection(_targetConnectionString);
cfg.AutoCreateSchemaObjects = true;
cfg.Schema.For<Commit>()
.Searchable(x => x.StreamId)
.Searchable(x => x.StreamVersion);
if (register != null)
{
register(cfg.Schema);
}
});
return store;
}
/// <summary>
/// Executes the non query.
/// </summary>
/// <param name="query">The query.</param>
/// <returns>Returns the count of the affected rows.</returns>
public int ExecuteNonQuery(IQuery query)
{
var command = _connection.CreateCommand();
command.CommandText = query.Command;
command.Transaction = _currentTransaction;
command.Parameters.AddRange(query.Parameters.Select(x => new NpgsqlParameter(x.Name, x.Value)).ToArray());
command.Prepare();
return(command.ExecuteNonQuery());
}
protected override void CreateTestStore()
{
using (var conn = new NpgsqlConnection(this.ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
var version = PostGisVersion();
if (version.StartsWith("1."))
{
cmd.CommandText = "DELETE FROM \"geometry_columns\" WHERE \"f_table_name\" = 'nts_io_postgis_2d'; ";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "DROP TABLE IF EXISTS \"nts_io_postgis_2d\";";
cmd.ExecuteNonQuery();
cmd.CommandText =
"CREATE TABLE \"nts_io_postgis_2d\" (id int primary key, wkt text);"
+ "SELECT AddGeometryColumn('nts_io_postgis_2d', 'the_geom', " + 4326 + ",'GEOMETRY', 2);"
;
cmd.ExecuteNonQuery();
}
}
RandomGeometryHelper.Ordinates = Ordinates.XY;
}
public void Test()
{
using (NpgsqlConnection cn = new NpgsqlConnection(ConnectionString))
{
cn.Open();
NpgsqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT \"srid\", \"srtext\" FROM \"public\".\"spatial_ref_sys\" ORDER BY \"srid\";";
int counted = 0;
int failed = 0;
int tested = 0;
using (NpgsqlDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (r != null)
{
while (r.Read())
{
counted++;
string srtext = r.GetString(1);
if (!string.IsNullOrEmpty(srtext))
{
tested++;
if (!TestParse(r.GetInt32(0), srtext)) failed++;
}
}
}
}
Console.WriteLine("\n\nTotal number of Tests {0}, failed {1}", tested, failed);
Assert.IsTrue(failed == 0);
}
}
public static void Main(string[] args)
{
NpgsqlConnection connection = new NpgsqlConnection
("Server= localhost; Database= postgres; User Id=dbprueba; Password = 12345");
connection.Open();
//Comando para insertar datos en las columnas
//IDbCommand command2 = connection.CreateCommand();
//command2.CommandText ="insert into prueba (id, nombre) values (2, 'otro')";
//command2.ExecuteNonQuery();
// fin comando
//comando para mostrar datos de tabla "prueba"
IDbCommand command = connection.CreateCommand();
command.CommandText ="select * from prueba";
IDataReader datareader = command.ExecuteReader();
Console.WriteLine(datareader.GetName(0));
Console.WriteLine(datareader.GetName(1));
while(datareader.Read() ){
Console.WriteLine(datareader["id"]);
Console.WriteLine(datareader["nombre"]);
}
//fin comando
datareader.Close();
connection.Close();
}
private void fillComboBox()
{
CellRenderer cellRenderer = new CellRendererText();
comboBox.PackStart(cellRenderer, false); //expand=false
comboBox.AddAttribute (cellRenderer, "text", 1);
ListStore listStore = new ListStore(typeof(string), typeof(string));
comboBox.Model = listStore;
string connectionString = "Server=localhost;Database=PruebaBD;User Id=ximo;Password=admin";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open ();
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = "select id, nombre from categoria";
IDataReader dataReader = dbCommand.ExecuteReader();
while (dataReader.Read ())
listStore.AppendValues (dataReader["id"].ToString (), dataReader["nombre"].ToString () );
dataReader.Close ();
dbConnection.Close ();
}
private static NpgsqlConnection OpenDbConnection(string connectionString)
{
var connection = new NpgsqlConnection(connectionString);
while (true)
{
try
{
connection.Open();
break;
}
catch (DbException)
{
Console.Error.WriteLine("Failed to connect to db - retrying");
}
}
var command = connection.CreateCommand();
command.CommandText = @"CREATE TABLE IF NOT EXISTS votes (
id VARCHAR(255) NOT NULL UNIQUE,
vote VARCHAR(255) NOT NULL
)";
command.ExecuteNonQuery();
return connection;
}
private async Task<string> Create()
{
string sql;
var assembly = typeof(TestDatabase).GetTypeInfo().Assembly;
using (var reader = new StreamReader(assembly.GetManifestResourceStream(_sqlFile)))
{
sql = await reader.ReadToEndAsync();
}
var dbName = await CreateDatabase();
var connectionString = $"Server=localhost;Port=5432;Database={_databaseName};User Id=postgres;Password=s1mpl3;";
using (var connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
foreach (
var statement in
Regex.Split(sql, @"^\s*GO\s*$", RegexOptions.Multiline)
.Where(s => !string.IsNullOrWhiteSpace(s)))
{
//_logger.LogDebug(sql);
command.CommandText = statement;
await command.ExecuteNonQueryAsync();
}
}
}
return _connectionString = connectionString;
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ;
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulo where id=4";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString ();
Console.WriteLine("Tabla con los cambios");
show (dataSet.Tables[0]);
dbDataAdapter.Update (dataSet);
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
NpgsqlConnection dbConnection = new NpgsqlConnection("Server=localhost;Database=prueba;User Id=dbprueba; Password=sistemas");
NpgsqlCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText ="select * from categoria";
NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter ();
new NpgsqlCommandBuilder(dbDataAdapter);
dbDataAdapter.SelectCommand=selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill(dataSet);
Console.WriteLine("Tables.Count={0}",dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow =dataSet.Tables[0].Rows[0];
dataRow["nombre"]=DateTime.Now.ToString ();
Console.WriteLine("Tabla con los cambios");
show (dataSet.Tables[0]);
dbDataAdapter.Update(dataSet.Tables[0]);
}
public void TestReturn()
{
var parameters = GetParameters("public", "test_return");
using (var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "public.test_return";
cmd.Parameters.Add(new NpgsqlParameter
{
Direction = ParameterDirection.Input,
Value = 2
});
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "__return",
Direction = ParameterDirection.ReturnValue
});
using (var rdr = cmd.ExecuteReader())
{
rdr.Read();
}
}
}
}
internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, NpgsqlParameter[] pars)
{
int result = 0;
using (NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING))
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
//Log.Error(e);
throw;
}
}
}
return (result > 0);
}
private static NpgsqlConnection OpenDbConnection(string connectionString)
{
NpgsqlConnection connection;
while (true)
{
try
{
connection = new NpgsqlConnection(connectionString);
connection.Open();
break;
}
catch (SocketException)
{
Console.Error.WriteLine("Waiting for db");
Thread.Sleep(1000);
}
catch (DbException)
{
Console.Error.WriteLine("Waiting for db");
Thread.Sleep(1000);
}
}
Console.Error.WriteLine("Connected to db");
var command = connection.CreateCommand();
command.CommandText = @"CREATE TABLE IF NOT EXISTS votes (
id VARCHAR(255) NOT NULL UNIQUE,
vote VARCHAR(255) NOT NULL
)";
command.ExecuteNonQuery();
return connection;
}
private string GetForeignKeyReferenceTableName(string selectedTableName, string columnName)
{
var conn = new Npgsql.NpgsqlConnection(connectionStr);
conn.Open();
using (conn)
{
NpgsqlCommand tableCommand = conn.CreateCommand();
tableCommand.CommandText = String.Format(
@"
select pk.table_name
from information_schema.referential_constraints c
inner join information_schema.table_constraints fk on c.constraint_name = fk.constraint_name
inner join information_schema.table_constraints pk on c.unique_constraint_name = pk.constraint_name
inner join information_schema.key_column_usage cu on c.constraint_name = cu.constraint_name
inner join (
select i1.table_name, i2.column_name
from information_schema.table_constraints i1
inner join information_schema.key_column_usage i2 on i1.constraint_name = i2.constraint_name
where i1.constraint_type = 'PRIMARY KEY'
) pt on pt.table_name = pk.table_name
where fk.table_name = '{0}' and cu.column_name = '{1}'",
selectedTableName, columnName);
object referencedTableName = tableCommand.ExecuteScalar();
return((string)referencedTableName);
}
}
public static List<Role> GetRolesForUser(int userId)
{
NpgsqlConnection conn = new NpgsqlConnection (conn_string);
NpgsqlCommand comm = conn.CreateCommand ();
string sql = "SELECT * FROM roles, userroles WHERE userroles.roleid = roles.id and userroles.userid = @userid";
comm.CommandText = sql;
comm.Parameters.Add ("@userid", userId);
conn.Open ();
NpgsqlDataReader reader = comm.ExecuteReader ();
List<Role> roles = new List<Role> ();
while (reader.Read ())
roles.Add (new Role (reader));
reader.Close ();
comm.Dispose ();
conn.Close ();
return roles;
}
public static int AddTodo(string todo)
{
//string sql = "insert into todos(task) values (:todo)";
//using (var conn = new NpgsqlConnection(connStr))
//{
// NpgsqlCommand cmd = conn.CreateCommand();
// cmd.CommandText = sql;
// cmd.Parameters.AddWithValue("todo", todo);
// conn.Open();
// cmd.ExecuteNonQuery();
// conn.Close();
//}
string sql = "insert into todos(task) values (:todo) RETURNING id;";
int a = 0;
using (var conn = new NpgsqlConnection(connStr))
{
NpgsqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("todo", todo);
conn.Open();
a = (int)cmd.ExecuteScalar();
conn.Close();
}
return a;
}
public static void Main()
{
// TODO: change to your real values
string connString = "Server=127.0.0.1; Port=9999; Database=LOCALDB; User Id=sa; Password="******"Established connection to PG2LucidDB");
// get server version:
NpgsqlCommand comm = conn.CreateCommand();
comm.CommandText = "drop schema PG2LUCIDDBTEST cascade";
comm.ExecuteNonQuery();
Console.WriteLine("PG2LUCIDDBTEST schema has been successfully deleted");
}
catch(Exception ex)
{
Console.WriteLine("Exception occured: {0}", ex);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost; Database=dbprueba; Id=javi; password=sistemas";
NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
NpgsqlCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulo";
NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");
foreach (DataTable dataTable in dataSet.Tables){
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine("Tabla con los cambios:");
show (dataSet.Tables[0]);
// dbDataAdapter.RowUpdated +- delegate(object dbDadapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
// Console.WriteLine("RowUpdating command.CommandText= {0}", EventArgs.Command.CommantText);
//
// foreach (IDataParameter dataParameter in EventArgs.Command.Parameters)
// Console.WriteLine ("{0}={1}", dataParameter.ParameterName, dataParameter.Value);
};
dbDataAdapter.Update(dataSet.Tables[0]);
}
//Modelo de funcionamiento
//1º Referencias (System data y Npgsql)
//2º Diseñador
//3º picar codigo
private void fillComboBox()
{
CellRendererText cellRenderer = new CellRendererText();
comboBox.PackStart(cellRenderer,false); //expand = false
comboBox.AddAttribute(cellRenderer,"text", 1);
ListStore liststore = new ListStore(typeof(string), typeof(string));
comboBox.Model=liststore;
//Atacamos a la base de datos para conectarnos
string connectionString="Server=localhost; Database=dbprueba; User Id=dbprueba; password=1234";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText="select id, nombre from categoria";
IDataReader dataReader = dbCommand.ExecuteReader();
while(dataReader.Read()){
liststore.AppendValues(dataReader("id").ToString(), dataReader("nombre").ToString());
}
dataReader.Close ();
dbConnection.Close();
}
public void TestDataRowToPOCO()
{
using var dbConnection =
new Npgsql.NpgsqlConnection("Host=localhost;Port=5432;Username=yim;Database=yim;Password=;");
dbConnection.Open();
var command = dbConnection.CreateCommand();
command.CommandText = "select p.* from public.\"Person\" as p";
var sqlAdapter = new NpgsqlDataAdapter(command);
var dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
dbConnection.Close();
var person = dataTable.ToEntities <Person>();
person.ToList().ForEach(p =>
{
_testOutputHelper.WriteLine(p.Name);
_testOutputHelper.WriteLine(p.Adult.ToString());
_testOutputHelper.WriteLine(p.Age.ToString());
});
}
public void Destroy()
{
var connection = new NpgsqlConnection(_connectionString);
try
{
connection.Open();
foreach (var tablename in StoreInfo.Tables)
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = String.Format(@"drop table {0};", tablename);
command.ExecuteNonQuery();
}
}
}
catch (NpgsqlException exception)
{
throw new Exception(String.Format("Could not drop table {0}; see the inner exception for more information.", _storeInfo.Name), exception);
}
finally
{
connection.Dispose();
}
}
public void Empty()
{
var connection = new NpgsqlConnection(ConnectionString);
try
{
connection.Open();
foreach (var tablename in StoreInfo.Tables)
{
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = $@"delete from {tablename};";
command.ExecuteNonQuery();
}
}
}
catch (NpgsqlException exception)
{
throw new Exception($"Could not delete all from table {StoreInfo.Name}; see the inner exception for more information.", exception);
}
finally
{
connection.Dispose();
}
}
protected void OnPropertiesActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost; Database=pruebaBD; Id=ximo; password=admin";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * BaseDatos1.from articulo where id=1";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");
foreach (DataTable dataTable in dataSet.Tables){
Show(dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["Nombre"] = DateTime.Now.ToString();
Console.WriteLine("Tabla con los cambios: ");
Show (dataSet.Tables[0]);
dbDataAdapter.Update (dataSet);
}
}
private void fillComboBox()
{
CellRenderer cellRenderer = new CellRendererText();
comboBox.PackStart(cellRenderer, false); //expand:false
comboBox.AddAttribute(cellRenderer, "text", 0);
CellRenderer cellRenderer2 = new CellRendererText();
comboBox.PackStart(cellRenderer2, false); //expand:false
comboBox.AddAttribute(cellRenderer2, "text", 1);
ListStore listStore = new ListStore(typeof(string),typeof(string));
string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba; Password=sistemas;";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = "SELECT id, nombre FROM categoria";
IDataReader dataReader = dbCommand.ExecuteReader();
while(dataReader.Read()) {
listStore.AppendValues(dataReader["id"].ToString(),dataReader["nombre"].ToString());
}
comboBox.Model = listStore;
dataReader.Close();
dbConnection.Close();
}
private static void InitConnection(NpgsqlConnection npgsqlConnection)
{
var cmd = npgsqlConnection.CreateCommand();
cmd.CommandText = "SET plv8.start_proc = 'plv8_init'";
cmd.ExecuteNonQuery();
}
public void SalvarDados()
{
try {
if (CodigoContato != 0) {
//Consulta o usuário para ter certeza da alteração de dados
var Msg = new MessageDialog(this, DialogFlags.Modal, MessageType.Info, ButtonsType.YesNo,
"Deseja realmente alterar este contato ?");
if((ResponseType)Msg.Run()==ResponseType.Yes)
{
Msg.Destroy();
}
//SALVAR DADOS
}
else if (CodigoContato == 0)
{
//INSERIR NOVO CONTATO
string connectionString =
"Server=localhost;" +
"Database=BoletoVX;" +
"User ID=postgres;" +
"Password=Pa$$w0rdMark1;";
IDbConnection dbcon; // cria conexão
dbcon = new NpgsqlConnection(connectionString); // cria relação entre a conexão*/
dbcon.Open(); // abre a conexão
IDbCommand dbcmd = dbcon.CreateCommand(); // instancia objeto para intruções (query)
string sql =@"INSERT INTO contato(" +
"razao, fantasia, tipo, documento, dataaniverssario," +
"dataadmissao, observacao, statusatual, email, logradouro, numero," +
" bairro, cidade, uf, cep, telefone1, telefone2, conta," +
"agencia, operaccao, banco)" +
" VALUES ('"+Razaotxt.Text+"','"+Fantasiatxt.Text+"' , ?, ?, 'pplplp', ?," +
" ?, ?, ?, ?, ?, ?," +
"?, ?, ?, ?, ?, ?, ?, ?," +
"?, ?, ?);";
// comando sql
dbcmd.CommandText = sql;
// clean up
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
limparCampo();
}
} catch (Exception error) {
//Caso ocorra um erro este será apresentado para facilitar o debug e identificação de erros
var erromsg = new MessageDialog(this, DialogFlags.Modal, MessageType.Info, ButtonsType.Ok,
"Erro" + error);
if((ResponseType)erromsg.Run()==ResponseType.Ok)
{
erromsg.Destroy();
}
}
}
protected void OnBotonBuscarClicked(object sender, System.EventArgs e)
{
// while (treeView.GetColumn(0) != null) {
// treeView.RemoveColumn(treeView.GetColumn(0));
// }
TreeViewColumn[] treeViewColumns = treeView.Columns;
foreach (TreeViewColumn treeViewColumn in treeViewColumns) {
treeView.RemoveColumn(treeViewColumn);
}
NpgsqlConnection connection = new NpgsqlConnection
("Server= localhost; Database= dbprueba; User Id=dbprueba; Password = 12345");
connection.Open();
string sentencia = campoSentencia.Text;
IDbCommand command = connection.CreateCommand();
command.CommandText = sentencia;
IDataReader datareader = command.ExecuteReader();
//treeView.AppendColumn (datareader.GetName(0), new CellRendererText (), "text", 0);
//treeView.AppendColumn (datareader.GetName(1), new CellRendererText (), "text", 1);
//manera de nombrar las columnas
for (int i = 0; i < datareader.FieldCount; i++){
treeView.AppendColumn(datareader.GetName(i), new CellRendererText (), "text", i);
}
//
//manera de crear los tipos
Type[] types = TypeExtensions.GetTypes (typeof(string), datareader.FieldCount);
//sin crear un metodo aparte
//Type[] types = new Type[ datareader.FieldCount ];
//for (int index = 0; index < datareader.FieldCount; index++){
//types[index] = typeof(string);
//}
ListStore listStore = new ListStore(types);
//
treeView.Model = listStore;
while(datareader.Read() ) {
string[] values = new string [datareader.FieldCount];
for (int index = 0; index < datareader.FieldCount; index++) {
values[index] = datareader[index].ToString();
}
listStore.AppendValues (values);
}
datareader.Close();
connection.Close();
}
public void Orcl()
{
const string connStr = @"server=192.168.79.134;database=redmine;uid=redmine;pwd=123";
var connection = new NpgsqlConnection(connStr);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "select count(0) from users";
var ret = cmd.ExecuteScalar();
connection.Close();
Console.WriteLine(ret);
}
protected override void ExecuteInternalTest(PerformanceResult result)
{
using (var c = new NpgsqlConnection(Connection)) {
c.Open();
var cmd = c.CreateCommand();
cmd.CommandText = Query;
using (var r = cmd.ExecuteReader()) {
while (r.Read()) {}
}
}
}
static void PurgeInputQueue(string queueName)
{
using (var connection = new NpgsqlConnection(PostgresqlConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = $@"DELETE FROM ""{TableName}"" WHERE ""recipient"" = @recipient;";
command.Parameters.Add("recipient", NpgsqlDbType.Text, 200).Value = queueName;
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Execute NonQuery
/// </summary>
/// <param name="conn"></param>
/// <param name="commandFactory"></param>
public static void ExecuteNonQuery(this NpgsqlConnection conn, Action <NpgsqlCommand> commandFactory)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(commandFactory);
command.ExecuteNonQuery();
}
/// <summary>
/// Execute DataTable
/// </summary>
/// <param name="conn"></param>
/// <param name="commandFactory"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(this NpgsqlConnection conn, Action <NpgsqlCommand> commandFactory)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(commandFactory);
return(command.ExecuteFirstDataTable());
}
/// <summary>
/// Execute scalar as...
/// </summary>
/// <param name="conn"></param>
/// <param name="commandFactory"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static T ExecuteScalarAs <T>(this NpgsqlConnection conn, Action <NpgsqlCommand> commandFactory)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(commandFactory);
return((T)command.ExecuteScalar());
}
/// <summary>
/// Execute Reader
/// </summary>
/// <param name="conn"></param>
/// <param name="commandFactory"></param>
/// <returns></returns>
public static NpgsqlDataReader ExecuteReader(this NpgsqlConnection conn, Action <NpgsqlCommand> commandFactory)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(commandFactory);
return(command.ExecuteReader());
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <param name="commandType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText, NpgsqlParameter[] parameters, CommandType commandType, NpgsqlTransaction transaction)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(cmdText, commandType, transaction, parameters);
return(command.ExecuteScalar());
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="commandFactory"></param>
/// <returns></returns>
public static Task <object> ExecuteScalarAsync(this NpgsqlConnection conn, Action <NpgsqlCommand> commandFactory)
{
conn.CheckNull(nameof(conn));
using var command = conn.CreateCommand(commandFactory);
return(command.ExecuteScalarAsync());
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba;Password=sistemas;";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulos";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbConnection.Open();
DataSet dataSet = new DataSet();
dbDataAdapter.Fill(dataSet);
Console.WriteLine("Table.Count={0}\n", dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine ("\nTabla con los cambios:");
show (dataSet.Tables[0]);
IDbCommand comando = commandBuilder.GetUpdateCommand (dataSet.Tables[0].Rows[0]);
//COSAS DE HOY DIA 18
comando.ExecuteNonQuery ();
}
