public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
builder.Append('X', Conn.BufferSize);
var expected = builder.ToString();
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
cmd.Parameters.Add(new NpgsqlParameter("p", expected));
cmd.ExecuteNonQuery();
const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
var actual = reader[0];
Assert.That(actual, Is.EqualTo(expected));
if (IsSequential(behavior))
Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
Assert.That(reader[0], Is.EqualTo(expected));
Assert.That(reader.GetString(1), Is.EqualTo("foo"));
Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
Assert.That(reader.GetValue(3), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
}
public void ExceptionFieldsArePopulated()
{
const string dropTable = @"DROP TABLE IF EXISTS public.uniqueviolation";
const string createTable = @"CREATE TABLE public.uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))";
const string insertStatement = @"INSERT INTO public.uniqueviolation (id) VALUES(1)";
// In this case we'll test a simple unique violation, we're not too interested in testing more
// cases than this as the same code is executed in all error situations.
try
{
var command = new NpgsqlCommand(dropTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(insertStatement, Conn);
command.ExecuteNonQuery();
//Now cause the unique violation...
command.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Assert.AreEqual("", ex.ColumnName); // Should not be populated for unique violations.
Assert.AreEqual("uniqueviolation", ex.TableName);
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("uniqueviolation_pkey", ex.ConstraintName);
Assert.AreEqual("", ex.DataTypeName); // Should not be populated for unique violations.
}
}
public void TestSubquery()
{
const string sql = @"SELECT testid FROM preparetest WHERE :p1 IN (SELECT varchar_notnull FROM preparetest)";
var cmd = new NpgsqlCommand(sql, TheConnection);
var p1 = new NpgsqlParameter(":p1", DbType.String);
p1.Value = "blahblah";
cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery(); // Succeeds
cmd.Prepare(); // Fails
cmd.ExecuteNonQuery();
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="intitString"></param>
public pgsql_API(string intitString)
{
// connectionstring=
string[] parameters = intitString.Replace("\r\n","\n").Split('\n');
foreach(string param in parameters){
if(param.ToLower().IndexOf("connectionstring=") > -1){
m_ConStr = param.Substring(17);
}
}
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr);
string database = b.InitialCatalog;
b.InitialCatalog = "";
using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){
con.Open();
// See if database exists
try{
con.ChangeDatabase(database);
}
catch{
// Database don't exist, try to create it
try{
con.Close();
con.ConnectionString = b.ToString().ToLower().Replace("data source","server");
con.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "create database \"" + database + "\"";
cmd.ExecuteNonQuery();
con.ChangeDatabase(database);
// Create tables
cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
// Create procedures
cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
}
catch{
throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually.");
}
}
}
}
public void Alterar(Model_Vo_Agenda pAgenda)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;";
cmd.Parameters.AddWithValue("@id", pAgenda.Id);
cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false));
cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente);
cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
if ((isolation != IsolationLevel.ReadCommitted) &&
(isolation != IsolationLevel.Serializable))
throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation");
_conn = conn;
_isolation = isolation;
StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL ");
if (isolation == IsolationLevel.ReadCommitted)
commandText.Append("READ COMMITTED");
else
commandText.Append("SERIALIZABLE");
commandText.Append("; BEGIN");
NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
command.ExecuteNonQuery();
_conn.Connector.Transaction = this;
}
//När användaren kilckar på "OK" sker följande:
private void OK_button_Click(object sender, EventArgs e)
{
//Deklarerar variabel klass
var klass = "";
//Om "A" är iklickat av användaren får variabel klass värdet A.
if (A_radioButton.Checked == true)
{
klass = "A";
}
//Om "B" är iklickat av användaren får variabel klass värdet B.
else if (B_radioButton.Checked == true)
{
klass = "B";
}
//Om "C" är iklickat av användaren får variabel klass värdet C.
else if (C_radioButton.Checked == true)
{
klass = "C";
}
//Skapar strängen anmaldeltagare.
//Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari.
string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')";
//Skapar ett nytt Npgsql-kommando, command1.
NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn);
//Utför kommando, command1.
command1.ExecuteNonQuery();
//När allt ovan är utfört visas en meddelanderuta.
MessageBox.Show("Spelare är nu anmäld till tävling!");
//Sedan stängs hela detta form, AnmälDeltagare.
this.Close();
}
public static void CleanTables(NpgsqlConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
var script = GetStringResource(
typeof (PostgreSqlTestObjectsInitializer).Assembly,
"Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));
//connection.Execute(script);
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
using (var command = new NpgsqlCommand(script, connection, transaction))
{
command.CommandTimeout = 120;
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException ex)
{
throw;
}
}
}
public static bool ModificarUsuario(string nombre, string apellido, string direccion, string nombreusuario, string email)
{
try
{
NpgsqlCommand cmd = new NpgsqlCommand("update usuario set nombre = @nombre, apellido = @apellido, direccion = @direccion, email = @email where nombreusuario = @nombreusuario", Conexion.conexion);
cmd.Parameters.Add("nombre", nombre);
cmd.Parameters.Add("apellido", apellido);
cmd.Parameters.Add("direccion", direccion);
cmd.Parameters.Add("nombreusuario", nombreusuario);
cmd.Parameters.Add("email", email);
Conexion.abrirConexion();
if (cmd.ExecuteNonQuery() != -1)
{
Conexion.cerrarConexion();
return true;
}
else
{
Conexion.cerrarConexion();
return false;
}
}
catch (Exception ex)
{
throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde");
}
}
public void Alterar(Model_Vo_Produto pProduto)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;";
cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao);
cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade);
cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque);
cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda);
cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao);
cmd.Parameters.AddWithValue("@id", pProduto.Id);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
private void btnChange_Click(object sender, EventArgs e)
{
try
{
Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" +
Properties.Settings.Default.Port +
";User Id=" + Properties.Settings.Default.UserId + ";Password=" +
Properties.Settings.Default.Password + ";Database=" + Properties.Settings.Default.Database +
";";
Postgres.Open();
var commandUpdateDescr =
new NpgsqlCommand(
"UPDATE \"GPL\".\"INFORMATION\" SET \"DESCRIPTION\" = '" + txtDescription.Text + "' WHERE \"ID\" = " + _descrId + ";",
Postgres);
commandUpdateDescr.ExecuteNonQuery();
MessageBox.Show(@"Изменения применены", @"Изменение инструкции", MessageBoxButtons.OK,
MessageBoxIcon.Information);
Postgres.Close();
Close();
}
catch (Exception ex)
{
throw new Exception(@"Ошибка: ", ex);
}
}
public static bool CleanupOrphans()
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_pages ");
sqlCommand.Append("SET parentid = -1, parentguid = '00000000-0000-0000-0000-000000000000' ");
sqlCommand.Append("WHERE parentid <> -1 AND parentid NOT IN (SELECT pageid FROM mp_pages ) ");
sqlCommand.Append("");
int rowsAffected = 0;
// using scopes the connection and will close it /destroy it when it goes out of scope
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString.GetWriteConnectionString()))
{
conn.Open();
using (NpgsqlCommand command = new NpgsqlCommand(sqlCommand.ToString(), conn))
{
//command.Parameters.Add(new NpgsqlParameter("pageguid", DbType.StringFixedLength, 36));
command.Prepare();
//command.Parameters[0].Value = pageGuid.ToString();
rowsAffected = command.ExecuteNonQuery();
}
}
return (rowsAffected > 0);
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
_conn = conn;
_isolation = isolation;
StringBuilder commandText = new StringBuilder("BEGIN; SET TRANSACTION ISOLATION LEVEL ");
if ( (isolation == IsolationLevel.RepeatableRead) ||
(isolation == IsolationLevel.Serializable)
)
commandText.Append("SERIALIZABLE");
else
{
// Set isolation level default to read committed.
_isolation = IsolationLevel.ReadCommitted;
commandText.Append("READ COMMITTED");
}
commandText.Append(";");
NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
command.ExecuteNonQuery();
_conn.Connector.Transaction = this;
}
private void insertIntoBase()
{
try
{
short total = Convert.ToInt16( this.textBox1.Text.Trim());
int s_sum = Convert.ToInt32(this.textBox2.Text.Trim());
short d_sum = Convert.ToInt16(this.textBox3.Text.Trim());
short measure = Convert.ToInt16(this.textBox4.Text.Trim());
int _s = Convert.ToInt32(this.textBox5.Text.Trim());
short _d = Convert.ToInt16(this.textBox6.Text.Trim());
string _comm = this.richTextBox1.Text.Trim();
NpgsqlCommand _command = new NpgsqlCommand("Insert into tumor_size (total, d_sum, s_sum, s, d, measurable, pat_id, rasp_data, got_comment, brain, kidney, skin, bones, bone_marrow, lung, lymphatic, adrenal, os, hepar, lien, other, doc_id) values "
+ "( '" + total + "','" + d_sum + "','" + s_sum + "', '" + _s + "','" + _d + "','" + measure + "','" + this.searchPatientBox1.pIdN + "','" + this.dateTimePicker1.Value + "', :descr ,'" + this.checkedListBox1.GetItemChecked(0)
+ "','" + this.checkedListBox1.GetItemChecked(1) + "','" + this.checkedListBox1.GetItemChecked(2) + "','" + this.checkedListBox1.GetItemChecked(3) + "','" + this.checkedListBox1.GetItemChecked(4) + "','" + this.checkedListBox1.GetItemChecked(5)
+ "','" + this.checkedListBox1.GetItemChecked(6) + "','" + this.checkedListBox1.GetItemChecked(7) + "','" + this.checkedListBox1.GetItemChecked(8) + "','" + this.checkedListBox1.GetItemChecked(9) + "','" + this.checkedListBox1.GetItemChecked(10)
+ "','" + this.checkedListBox1.GetItemChecked(11) + "' , '" + DBExchange.Inst.dbUsrId + "') ;", DBExchange.Inst.connectDb);
using (_command)
{
_command.Parameters.Add(new NpgsqlParameter("descr", NpgsqlDbType.Text));
_command.Parameters[0].Value = _comm;
}
_command.ExecuteNonQuery();
clearForm();
}
catch (Exception exception)
{
Warnings.WarnLog log = new Warnings.WarnLog(); log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString());
}
}
void mainit_paroli()
{
switch (db.liet_grupa) {
case "Mācībspēks":
mainas_kom = new NpgsqlCommand("UPDATE macibspeki SET parole = @parole WHERE id_macsp = @lietotajs;", db.sav);
break;
case "Vadība":
mainas_kom = new NpgsqlCommand("UPDATE vadiba SET parole = @parole WHERE id_vadiba = @lietotajs;", db.sav);
break;
case "Students":
mainas_kom = new NpgsqlCommand("UPDATE studenti SET parole = @parole WHERE id_matr = @lietotajs;", db.sav);
break;
}
mainas_kom.Parameters.AddWithValue("@lietotajs", db.lietotajs);
mainas_kom.Parameters.AddWithValue("@parole", db.SHA1Parole(db.lietotajs, tb_jauna.Text));
db.sav.Open();
int ieraksti = mainas_kom.ExecuteNonQuery();
db.sav.Close();
if (ieraksti != -1) {
MessageBox.Show("Lietotāja parole veiksmīgi nomainīta!", "Parole nomainīta", MessageBoxButtons.OK, MessageBoxIcon.Information);
DialogResult = DialogResult.OK;
Close();
} else {
MessageBox.Show("Paroles maiņas procesā radās neparedzēta kļūda!\nMēģiniet atkārtot paroles maiņu vēlāk.", "Kļūda paroles maiņas procesā", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
//När användaren kilckar på "Radera markerad" sker följande
private void RaderaMarkerad_button_Click(object sender, EventArgs e)
{
//Använder strängen id för att se vilket golfid det är som användaren har markerat och valt att radera.
string id = markeratid();
//Skapar strängen tabort1.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
String tabort1 = "delete from deltari where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command11.
NpgsqlCommand command11 = new NpgsqlCommand(tabort1, Huvudfönster.conn);
//Utför kommando, command12.
command11.ExecuteNonQuery();
//Skapar strängen tabort1.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen person.
String tabort2 = "delete from person where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command12.
NpgsqlCommand command12 = new NpgsqlCommand(tabort2, Huvudfönster.conn);
//Utför kommando, command12.
command12.ExecuteNonQuery();
//Skapar strängen tabort2.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
String tabort3 = "delete from medlem where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command122.
NpgsqlCommand command122 = new NpgsqlCommand(tabort3, Huvudfönster.conn);
//Utför kommando, command122.
command122.ExecuteNonQuery();
//När allt ovan är utfört visas en meddelanderuta.
MessageBox.Show("Vald spelare är nu borttagen ur databasen!");
}
public static Facture getAjoutFacture(Facture a)
{
NpgsqlConnection con = Connexion.Connection();
try
{
string insert = "insert into yvs_com_doc_ventes"
+ "(num_piece, type_doc, statut, client, supp, actif, "
+ "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
+ "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ", false, true, "
+ "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")"; ;
if ((a.Categorie != null) ? a.Categorie.Id > 0 : false)
{
insert = "insert into yvs_com_doc_ventes"
+ "(num_piece, type_doc, statut, client, categorie_comptable, supp, actif, "
+ "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
+ "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ", " + a.Categorie.Id + ", false, true, "
+ "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")";
}
NpgsqlCommand cmd = new NpgsqlCommand(insert, con);
cmd.ExecuteNonQuery();
a.Id = getCurrent();
return a;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
public void DeriveParametersVarious()
{
// This function returns record because of the two Out (InOut & Out) parameters
ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ""func""(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS
'
BEGIN
param2 = ''sometext'';
param3 = param1 + param3;
END;
' LANGUAGE 'plpgsql';");
var cmd = new NpgsqlCommand("func", Conn);
cmd.CommandType = CommandType.StoredProcedure;
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
cmd.Parameters[0].Value = 5;
cmd.Parameters[2].Value = 4;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
}
private void create_pass()
{
if (titleB.Text.Length != 0)
{
if (loginB.Text.Length != 0)
{
if (pswdB.Text.Length != 0)
{
if (linkB.Text.Length != 0)
{
mf1.connect_db();
string sql_insert = "INSERT INTO infos (title, login, password,";
if (noteB.Text.Length != 0)
{
sql_insert += " note,";
}
sql_insert += " link ) VALUES (";
sql_insert += "'" + titleB.Text + "', ";
sql_insert += "'" + loginB.Text + "', ";
sql_insert += "'" + pswdB.Text + "', ";
if (noteB.Text.Length != 0)
{
sql_insert += "'" + noteB.Text + "', ";
}
sql_insert += "'" + linkB.Text + "'); ";
NpgsqlCommand command = new NpgsqlCommand(sql_insert, mf1.connect_db());
try
{
command.ExecuteNonQuery();
mf1.connect_db().Close();
}
catch
{
MessageBox.Show("Что-то пошло нетак! ");
return;
}
MessageBox.Show("Новое устройство добавленно!");
}
else
{
MessageBox.Show("Поле IP - пусто!");
}
}
else
{
MessageBox.Show("Поле пароля - пусто!");
}
}
else
{
MessageBox.Show("Поле Логни - пусто!");
}
}
else
{
MessageBox.Show("Поле Названия - пусто!");
}
}
public void Alterar(Model_Vo_Sala pSala)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update sala set nome = @nome, capacidade = @capacidade, tipo = @tipo, idproduto = @idproduto where id = @id;";
cmd.Parameters.AddWithValue("@id", pSala.Id);
cmd.Parameters.AddWithValue("@nome", pSala.Nome);
cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString().ToString());
cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public static bool ExecuteNonQuery(string catalog, NpgsqlCommand command)
{
try
{
if (command != null)
{
if (ValidateCommand(command))
{
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
return true;
}
}
}
return false;
}
catch (NpgsqlException ex)
{
if (ex.Code.StartsWith("P"))
{
string errorMessage = GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
throw;
}
}
public void CreateIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
indexName = "countries_geom_gist";
else return;
}
string createIndexCommandText =
@"DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '" + indexName + @"'
AND n.nspname = 'public'
) THEN
CREATE INDEX " + indexName + @"
ON random_points_" + (int)dataSize + @"
USING " + index + @" (geom);
END IF;
END$$;";
using (var command = new NpgsqlCommand(createIndexCommandText, _conn))
command.ExecuteNonQuery();
}
private static void Run()
{
var connStr = ConfigurationManager.AppSettings["ConnectionString"];
using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
{
conn.Open();
updateConn.Open();
// Define a query returning a single row result set
using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
{
using (NpgsqlDataReader dr = command.ExecuteReader())
{
// Output rows
while (dr.Read())
{
CreateThumbnail((string)dr[1], (long)dr[2]);
using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
{
update.ExecuteNonQuery();
}
}
}
}
}
}
public void ColumnNameExceptionFieldIsPopulated()
{
const string dropTable = @"DROP TABLE IF EXISTS public.notnullviolation";
const string createTable = @"CREATE TABLE public.notnullviolation (id INT NOT NULL)";
const string insertStatement = @"INSERT INTO public.notnullviolation (id) VALUES(NULL)";
// Since the 5 error fields were added as of PostgreSQL 9.3, we'll skip testing for versions previous to that.
if (Conn.PostgreSqlVersion < new Version("9.3"))
Assert.Ignore("Postgres version is {0} (< 9.3))", Conn.PostgreSqlVersion);
try
{
var command = new NpgsqlCommand(dropTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(insertStatement, Conn);
//Cause the NOT NULL violation
command.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("notnullviolation", ex.TableName);
Assert.AreEqual("id", ex.ColumnName);
}
}
public void Append(string name, byte[] data, long expectedVersion)
{
using (var conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx);
const string txt =
@"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data)
VALUES(:customerId, :name, :version, :data)";
using (var cmd = new NpgsqlCommand(txt, conn, tx))
{
cmd.Parameters.AddWithValue(":name", name);
cmd.Parameters.AddWithValue(":version", version+1);
cmd.Parameters.AddWithValue(":data", data);
cmd.Parameters.AddWithValue(":customerId", customerId);
cmd.ExecuteNonQuery();
}
tx.Commit();
}
}
}
public static void ProcessLinks(XmlReader reader, NpgsqlConnection connection)
{
var page_title = reader.GetAttribute("title");
List<string> titles;
XmlReader inner;
inner = reader.ReadSubtree();
titles = new List<string>();
while (inner.ReadToFollowing("link")) titles.Add(inner.ReadElementContentAsString());
if (titles.Count() == 0) return;
// Now we have a list of titles construct a subquery to get the ID's
// I could use this query, which looks nicer, but EXPLAIN says it costs a little bit more.
// SELECT id FROM pages WHERE title IN ('Apple','ElementalAllotropes')
// AND redirect IS NULL UNION SELECT p2.id FROM pages AS p1
// JOIN pages AS p2 ON p1.title IN ('Apple','ElementalAllotropes') AND p2.title = p1.redirect AND p2.redirect IS NULL;
var select_command_text = string.Format(PostgresSchema.LINK_ID_QUERY,
string.Join(",", (from val in Enumerable.Range(0, titles.Count)
select string.Format(":title_{0}", val))));
// Update our links to be the array of ID's using the subquery above
var command = new NpgsqlCommand(string.Format("UPDATE pages SET links = ARRAY({0}) WHERE title = :title", select_command_text), connection);
foreach (var val in Enumerable.Range(0, titles.Count)) command.Parameters.AddWithValue("title_"+val, titles[val]);
command.Parameters.Add(new NpgsqlParameter("title", page_title));
command.ExecuteNonQuery();
}
public void Create(IEnumerable<string> columnDefinitions)
{
var sb = new StringBuilder();
if(!string.IsNullOrEmpty(_schemaName))
sb.AppendFormat("CREATE SCHEMA \"{0}\";",_schemaName);
sb.Append("CREATE TABLE ");
sb.Append(NameWithSchema);
sb.Append(" (");
foreach (string definition in columnDefinitions)
{
sb.Append(definition);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
var s = sb.ToString();
using (var command = new NpgsqlCommand(s, Connection, Transaction))
command.ExecuteNonQuery();
}
public int Salvarcliente(string nome, int codempresa)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
int id;
using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans))
{
id = int.Parse(cmd.ExecuteScalar().ToString());
}
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
return id;
}
}
catch (Exception)
{
trans.Rollback();
return 0;
}
}
}
}
static void ClearCatalogue(string connString)
{
try
{
NpgsqlConnection conn = new NpgsqlConnection(connString);
conn.Open();
Console.Clear();
string clearTable = "DELETE FROM phone_book";
NpgsqlCommand cmd = new NpgsqlCommand(clearTable, conn);
cmd.ExecuteNonQuery();
Console.WriteLine(">>> Catalogue cleared");
Console.ReadKey();
Console.Clear();
conn.Close();
}
catch (Exception msg)
{
Console.WriteLine(msg.ToString());
throw;
}
}
public void SalvarGrupo(GrupclienteDto entidade)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO grupcliente (grcl_nome, grcl_ativo,empr_codigo) values ('" + entidade.grcl_nome + "'," + entidade.grcl_ativo + ","+entidade.empr_codigo+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
}
}
}
}
