protected void Button1_Click(object sender, EventArgs e)
{
string checks;
string checkw;
string id_sali = DropDownList1.SelectedItem.ToString();
string id_wyp = DropDownList2.SelectedItem.ToString();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
checks = (String)check1.ExecuteScalar();
checkw = (String)check2.ExecuteScalar();
if (checks == id_sali && checkw == id_wyp)
{
Label2.Text = "";
Label3.Text = "Wyposażenie jest już przypisane do sali!";
}
else if (checkw != id_wyp)
{
NpgsqlCommand add = new NpgsqlCommand("insert into wyp_sali values ('" + id_wyp + "', '" + id_sali + "')", conn);
add.ExecuteScalar();
conn.Close();
Label2.Text = "Dodano wyposażenie do sali!";
Label3.Text = "";
}
}
public static void CreateTables(NpgsqlConnection conn)
{
using (var cmdRename1 = new NpgsqlCommand("select table_name from information_schema.tables where table_name = 'rn_results'", conn))
{
var tblExists = false;
using (var rdr1 = cmdRename1.ExecuteReader()) { tblExists = rdr1.Read(); }
cmdRename1.Dispose();
if (tblExists)
{
var curCount = 0;
using (var cmdRename2 = new NpgsqlCommand("select count(*) from rn_results", conn))
{
curCount = Convert.ToInt32(cmdRename2.ExecuteScalar());
}
if (curCount == 0)
{
using (var cmdRename3 = new NpgsqlCommand("drop table rn_results", conn)) { cmdRename3.ExecuteNonQuery(); }
}
else
{
using (var cmdRename2 = new NpgsqlCommand("alter table rn_results rename to rn_results_old" + (curDBVersion - 1).ToString(), conn)) { cmdRename2.ExecuteNonQuery(); }
}
}
}
using (var cmd1 = new NpgsqlCommand("drop table rn_version;", conn)) { cmd1.ExecuteNonQuery(); }
using (var cmd2 = new NpgsqlCommand("create table rn_version (ver integer);", conn)) { cmd2.ExecuteNonQuery(); }
using (var cmd3 = new NpgsqlCommand("insert into rn_version (ver) values (" + curDBVersion.ToString() + ")", conn)) { cmd3.ExecuteScalar(); }
using (var cmd4 = new NpgsqlCommand("create table rn_results (email varchar(100), name varchar(100), phone_no varchar(100), daum_id varchar(100), work_place varchar(200), client_ip varchar(50), sign_image1 text, sign_image2 text, insert_dt timestamp, sign_radio1 integer, sign_radio2 integer, PRIMARY KEY(email));", conn))
{
cmd4.ExecuteNonQuery();
}
using (var cmd5 = new NpgsqlCommand("create index rn_results_idx_insert_dt on rn_results (insert_dt)", conn)) { cmd5.ExecuteNonQuery(); }
}
public Boolean addTeam(string team)
{
string sql = "SELECT COUNT(*) FROM team WHERE name = :team";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
command.Parameters.Add(new NpgsqlParameter("team", NpgsqlDbType.Text));
command.Parameters[0].Value = team;
long numFound = (long) command.ExecuteScalar();
if (numFound > 0)
{
sql = "UPDATE team SET num_matches = num_matches + 1 WHERE name = :team";
command = new NpgsqlCommand(sql, conn);
command.Parameters.Add(new NpgsqlParameter("team", team));
command.ExecuteNonQuery();
} else
{
sql = "INSERT INTO team (name, num_matches) VALUES (:team, 1)";
command = new NpgsqlCommand(sql, conn);
command.Parameters.Add(new NpgsqlParameter("team", team));
command.ExecuteNonQuery();
}
return true;
}
public static void Initialize()
{
var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
var connectionBuilder = new NpgsqlConnectionStringBuilder(connectionString);
//connect to postgres database to create a new database
var databaseName = connectionBuilder.Database;
connectionBuilder.Database = "postgres";
connectionString = connectionBuilder.ToString();
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
bool dbExists;
using (var cmd = new NpgsqlCommand())
{
cmd.CommandText = string.Format(@"SELECT TRUE FROM pg_database WHERE datname='{0}'", databaseName);
cmd.Connection = conn;
var result = cmd.ExecuteScalar();
dbExists = result != null && Convert.ToBoolean(result);
}
if (dbExists)
{
DoClean(conn);
}
else
{
DoCreate(conn, databaseName);
}
}
}
public void Incluir(Model_Vo_ContasAReceber pContasAReceber)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into contasareceber(datahoracriacao,descricao,idcliente,idproduto,idreservaorigem,origem,recebido,valorareceber) values (@datahoracriacao,@descricao,@idcliente,@idproduto,@idreservaorigem,@origem,@recebido,@valorareceber);";
cmd.Parameters.AddWithValue("@datahoracriacao", Dados.Model_Dao_Dados.ConverterDataToStr(pContasAReceber.DataHoraCriacao, false));
cmd.Parameters.AddWithValue("@descricao", pContasAReceber.Descricao);
cmd.Parameters.AddWithValue("@idcliente", pContasAReceber.IdCliente);
cmd.Parameters.AddWithValue("@idproduto", pContasAReceber.IdProduto);
cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
cmd.Parameters.AddWithValue("@origem", pContasAReceber.Origem.ToString());
cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
cmd.Parameters.AddWithValue("@valorareceber", pContasAReceber.ValorAReceber);
cn.Open();
pContasAReceber.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public int IntScalar(NpgsqlCommand cmd)
{
try
{
int dataset = 0;
using (cmd)
{
cmd.Connection = GlobalVariables.Connection;
dataset = Convert.ToInt32(cmd.ExecuteScalar());
}
return dataset;
}
catch (NpgsqlException e)
{
sMessage = e.ToString();
MessageBox.Show(sMessage.ToString());
return 0;
}
catch (Exception e)
{
sMessage = e.ToString();
MessageBox.Show(sMessage.ToString());
return 0;
}
}
public void Incluir(Model_Vo_Sala pSala)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);";
cmd.Parameters.AddWithValue("@nome", pSala.Nome);
cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString());
cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
cn.Open();
pSala.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public void Incluir(Model_Vo_Produto pProduto)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into produto(descricao,unidade,estoque,valordevenda,observacao) values (@descricao,@unidade,@estoque,@valordevenda,@observacao);";
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);
cn.Open();
pProduto.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public static object Function(string connectionString, string functionName, params NpgsqlParameter[] parameters)
{
var npgsqlConnection = OpenConnection(connectionString);
try
{
var command = new NpgsqlCommand(functionName, npgsqlConnection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddRange(parameters);
var result = command.ExecuteScalar();
return result;
}
catch (Exception ex)
{
throw new FhirbaseException(
$"Call {functionName} FHIRbase function failed. Reason {ex.Message}",
ex);
}
finally
{
npgsqlConnection.Close();
}
}
public static string Get(string strcmd)
{
if (connectionString == null || connectionString.Length <= 0)
{
Initialize();
}
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
string ret = null;
NpgsqlCommand cmd = new NpgsqlCommand(strcmd, conn);
conn.Open();
try
{
ret = cmd.ExecuteScalar().ToString();
}
catch(Exception e)
{
}
finally
{
conn.Close();
}
return ret;
}
}
//создание формы
public Form_Countries(string connStr)
{
InitializeComponent();
this.connStr = connStr;
this.conn = new NpgsqlConnection(connStr);
//инициализация грида
this.table = new DataTable();
try
{
Class_Helper.SetColumns(table, dataGridView_Countries, columns);
}
catch (Exception ex)
{
throw new Exception("Can't init datagrid: " + ex.Message);
}
//получить ид России (сервер Б)
try
{
string query = "SELECT id from sb.countries";
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
conn.Open();
label_ServerB.Text = ((int)cmd.ExecuteScalar()).ToString();
conn.Close();
}
catch (Exception ex)
{
conn.Close();
throw new Exception("Can't connect to B: " + ex.Message);
}
}
private bool LoginUser(Credentials credentials)
{
string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand command = new NpgsqlCommand(@"SELECT COUNT(*) FROM clients WHERE [email protected] AND [email protected]");
command.Parameters.Add("@email", NpgsqlDbType.Varchar, 100).Value = credentials.Email;
command.Parameters.Add("@password", NpgsqlDbType.Varchar, 100).Value = credentials.Password;
command.Connection = conn;
try
{
conn.Open();
int result = Convert.ToInt32(command.ExecuteScalar());
if (result > 0)
return true;
else
return false;
}
catch (NpgsqlException e)
{
return false;
}
finally
{
conn.Close();
}
}
internal static void PostgreSqlTest()
{
const string connectionString = @"Server=127.0.0.1;Port=5432;Database=test;User Id=postgres;Password=123456;";
try
{
using (NpgsqlConnection npgsql = new NpgsqlConnection(connectionString))
{
npgsql.Open();
//执行查询命令
NpgsqlCommand command = new NpgsqlCommand("select *from student", npgsql);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"SNO:{reader[0]} SNAME:{reader[1]} AGE:{reader[2]}");
}
reader.Close();
//执行ExecuteScalar方法
command = new NpgsqlCommand("select count(*) from student", npgsql);
int count = Convert.ToInt32(command.ExecuteScalar());
Console.WriteLine($"总共有{count}条记录");
}
}
catch (NpgsqlException)
{
throw;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string checks;
string checkw;
string id_sali = TextBox1.Text;
string id_wyp = TextBox2.Text;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
checks = (String)check1.ExecuteScalar();
checkw = (String)check2.ExecuteScalar();
if (checkw != id_wyp)
{
Label2.Text = "";
Label3.Text = "Wyposażenie nie istnieje w tej sali";
}
else if (checkw == id_wyp && checks == id_sali)
{
NpgsqlCommand del = new NpgsqlCommand("delete from wyp_sali where id_wyp ='" + id_wyp + "' and id_sali = '" + id_sali + "'", conn);
del.ExecuteScalar();
conn.Close();
Label2.Text = "Usunięto wyposażenie z sali!";
Label3.Text = "";
}
}
public static void HandleRedirects(XmlReader reader, NpgsqlConnection connection)
{
var redirect_to = reader.GetAttribute("redirect");
if (string.IsNullOrEmpty(redirect_to)) return;
var title = reader.GetAttribute("title");
var fetch_row_command = new NpgsqlCommand(@"with redirect_page as (SELECT id,title,redirect FROM pages WHERE title = :title)
select * from redirect_page union all
select page.id,page.title,page.redirect from pages page
join redirect_page on page.title = redirect_page.redirect", connection);
fetch_row_command.Parameters.AddWithValue("title", title);
var query_reader = fetch_row_command.ExecuteReader();
if (!query_reader.HasRows) return;
query_reader.Read();
int redirect_id = query_reader.GetInt32(0);
query_reader.Read();
int real_page_id = query_reader.GetInt32(0);
query_reader.Close();
var update = string.Format(@"UPDATE pages SET links = (links - ARRAY[{0}]) || ARRAY[{1}] WHERE links @> ARRAY[{0}];",
redirect_id, redirect_id);
var command = new NpgsqlCommand(update, connection);
command.ExecuteScalar();
}
public void Incluir(Model_Vo_Agenda pAgenda)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into agenda(datahorareserva,idcliente,idsala) values (@datahorareserva,@idcliente,@idsala);";
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();
pAgenda.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string id_wyp = TextBox1.Text;
string check;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check_user = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
check = (String)check_user.ExecuteScalar();
if (check == id_wyp)
{
NpgsqlCommand delete = new NpgsqlCommand("delete from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
delete.ExecuteScalar();
TextBox1.Text = "";
TextBox2.Text = "";
Label3.Text = "Wyposażenie zostało usunięta!";
Label2.Text = "";
}
else if (check != id_wyp)
{
Label2.Text = "Brak wyposażenia w bazie!";
Label3.Text = "";
}
}
catch
{
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string check;
string typ_sali = TextBox1.Text;
string opis = TextBox2.Text;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select typ_sali from typ_sali where typ_sali = '" + typ_sali + "'", conn);
check = (String)check1.ExecuteScalar();
if (check != typ_sali)
{
NpgsqlCommand add = new NpgsqlCommand("insert into typ_sali values ('" + typ_sali + "', '" + opis + "')", conn);
add.ExecuteScalar();
conn.Close();
TextBox1.Text = "";
TextBox2.Text = "";
Label2.Text = "Dodano typ!";
Label3.Text = "";
}
else if (check == typ_sali)
{
Label2.Text = "";
Label3.Text = "Typ już istnieje!";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string check;
string id_sali = TextBox1.Text;
int pojemnosc = System.Int32.Parse(TextBox2.Text);
string typ = DropDownList1.SelectedItem.ToString();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from sala where id_sali = '" + id_sali + "'", conn);
check = (String)check1.ExecuteScalar();
if (check != id_sali)
{
NpgsqlCommand add = new NpgsqlCommand("insert into sala values ('" + id_sali + "', '" + typ + "', '" + pojemnosc + "')", conn);
add.ExecuteScalar();
conn.Close();
TextBox1.Text = "";
TextBox2.Text = "";
Label2.Text = "Dodano salę!";
Label3.Text = "";
}
else if (check == id_sali)
{
Label2.Text = "";
Label3.Text = "Sala już istnieje!";
}
}
public void Incluir(Model_Vo_LivroCaixa pLivroCaixa)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into livrocaixa(datahora,descricao,idcontasareceber,tipodemovimento,valor) values (@datahora,@descricao,@idcontasareceber,@tipodemovimento,@valor);";
cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr((pLivroCaixa.DataHora), false));
cmd.Parameters.AddWithValue("@descricao", pLivroCaixa.Descricao);
cmd.Parameters.AddWithValue("@idcontasareceber", pLivroCaixa.IdContasAReceber);
cmd.Parameters.AddWithValue("@tipodemovimento", pLivroCaixa.TipoDeMovimento.ToString());
cmd.Parameters.AddWithValue("@valor", pLivroCaixa.Valor);
cn.Open();
pLivroCaixa.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public static object GetScalar(NpgsqlCommand command, NpgsqlConnection connection)
{
using (connection)
{
return command.ExecuteScalar();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string user_id = TextBox1.Text;
string check;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check_user = new NpgsqlCommand("select id_prac from pracownicy where id_prac = '" + user_id + "'", conn);
check = (String)check_user.ExecuteScalar();
if (check == user_id)
{
NpgsqlCommand delete = new NpgsqlCommand("delete from pracownicy where id_prac = '" + user_id + "'", conn);
delete.ExecuteScalar();
TextBox1.Text = "";
TextBox2.Text = "";
Label3.Text = "Użytkownik został usunięty!";
Label2.Text = "";
}
else if (check != user_id)
{
Label2.Text = "Brak użytkownika w bazie!";
Label3.Text = "";
}
}
catch
{
}
}
public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "insert into movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);";
cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false));
cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante);
cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto);
cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem);
cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade);
cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario);
cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal);
cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString());
cn.Open();
pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string check;
string id_wyp = TextBox1.Text;
string nazwa = TextBox2.Text;
string opis = TextBox3.Text;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
check = (String)check1.ExecuteScalar();
if (check != id_wyp)
{
NpgsqlCommand add = new NpgsqlCommand("insert into wyposazenie values ('" + id_wyp + "', '" + nazwa + "', '" + opis + "')", conn);
add.ExecuteScalar();
conn.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
Label2.Text = "Dodano wyposażenie!";
Label3.Text = "";
}
else if (check == id_wyp)
{
Label2.Text = "";
Label3.Text = "Wyposażenie już istnieje!";
}
}
private PostgreSQLDBManager()
{
_connection = new NpgsqlConnection(Configuration.Settings("PostgreSQLDBConnectionString",
"User ID=postgres;Password=Passw0rd;Host=localhost;Port=5432;Database=postgres;Pooling=true;"));
try
{
_connection.Open();
}
catch (Exception exception)
{
Log.Error(ExceptionHelper.FormatStackTrace("Connect to PostgreSQL failed.",exception));
throw new RuntimeBinderException("Connect to Database Failed",exception);
}
var cmd = new NpgsqlCommand("select count(*) from content", _connection);
var count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
return;
var rootId = Configuration.Settings("Root", "42c5eb51-0e1c-4de1-976d-733bde24220a");
CreateSubItem(rootId, "Folder", "Data");
CreateSubItem(rootId, "Folder", "UI");
CreateSubItem(rootId, "Folder", "Translation");
CreateSubItem(rootId, "Folder", "Project");
CreateSubItem(rootId, "Folder", "Result");
CreateSubItem("", "Project", "Root", rootId);
}
public static void Main(String[] args)
{
NpgsqlConnection conn = null;
try
{
conn = new NpgsqlConnection(NpgsqlTests.getConnectionString());
conn.Open();
Console.WriteLine("Connection completed");
NpgsqlCommand command = new NpgsqlCommand();
command.CommandText = "select count(*) from tablea";
command.Connection = conn;
Object result = command.ExecuteScalar();
Console.WriteLine(result.ToString());
}
catch(NpgsqlException e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (conn != null)
conn.Close();
}
}
public void ChangeDatabase()
{
Conn.ChangeDatabase("template1");
var command = new NpgsqlCommand("select current_database()", Conn);
var result = (String)command.ExecuteScalar();
Assert.AreEqual("template1", result);
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
Int32 numer = System.Int32.Parse(TextBox1.Text);
Int32 check;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check_user = new NpgsqlCommand("select nr_rezerwacji from rezerwacja where nr_rezerwacji = '" + numer + "'", conn);
check = (Int32)check_user.ExecuteScalar();
if (check == numer)
{
NpgsqlCommand delete = new NpgsqlCommand("delete from rezerwacja where nr_rezerwacji = '" + numer + "'", conn);
delete.ExecuteScalar();
TextBox1.Text = "";
TextBox2.Text = "";
Label3.Text = "Rezerwacja została anulowana!";
Label2.Text = "";
}
else if (check != numer)
{
Label2.Text = "Brak rezerwacji w bazie!";
Label3.Text = "";
}
}
catch { }
}
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;
}
}
}
}
public void DistributedTransactionRollback()
{
int field_serial1;
int field_serial2;
var connectionString = ConnectionString + ";enlist=true";
using (var scope = new TransactionScope())
{
//UseStringParameterWithNoNpgsqlDbType
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
var command = new NpgsqlCommand("insert into data (field_text) values (:p0)", connection);
command.Parameters.Add(new NpgsqlParameter("p0", "test"));
Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Text);
Assert.AreEqual(command.Parameters[0].DbType, DbType.String);
object result = command.ExecuteNonQuery();
Assert.AreEqual(1, result);
field_serial1 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
var command2 = new NpgsqlCommand("select field_text from data where field_serial = (select max(field_serial) from data)", connection);
result = command2.ExecuteScalar();
Assert.AreEqual("test", result);
}
//UseIntegerParameterWithNoNpgsqlDbType
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
var command = new NpgsqlCommand("insert into data(field_int4) values (:p0)", connection);
command.Parameters.Add(new NpgsqlParameter("p0", 5));
Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Integer);
Assert.AreEqual(command.Parameters[0].DbType, DbType.Int32);
Object result = command.ExecuteNonQuery();
Assert.AreEqual(1, result);
field_serial2 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
var command2 = new NpgsqlCommand( "select field_int4 from data where field_serial = (select max(field_serial) from data)", connection);
result = command2.ExecuteScalar();
Assert.AreEqual(5, result);
// using new connection here... make sure we can't see previous results even though
// it is the same distributed transaction
var command3 = new NpgsqlCommand("select field_text from data where field_serial = :p0", connection);
command3.Parameters.Add(new NpgsqlParameter("p0", field_serial1));
result = command3.ExecuteScalar();
// won't see value of "test" since that's
// another connection
Assert.AreEqual(null, result);
}
// not commiting here.
}
// This is an attempt to wait for the distributed transaction to rollback
// not guaranteed to work, but should be good enough for testing purposes.
System.Threading.Thread.Sleep(500);
AssertNoPreparedTransactions();
// ensure they no longer exist since we rolled back
AssertRowNotExist("field_text", field_serial1);
AssertRowNotExist("field_int4", field_serial2);
}
