public static void getWords()
{
#region Npgsql
NpgsqlConnection conn;
conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting);
try
{
conn.Open();
}
catch (NpgsqlException)
{
MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}
catch (System.IO.IOException)
{
MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
}
#endregion
string sql = "SELECT no, words1, words2, words3, operator, word_order FROM words ORDER BY word_order";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(localDB, "words");
if (localDB.Tables["words"].Rows.Count == 0)
{ MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
conn.Close();
}
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 TableInfo GetTableInfo(string ConnectionString, string schema, string table_name)
{
var ret = new TableInfo();
var sql = @"SELECT column_name, column_default,is_nullable,data_type,character_maximum_length
FROM information_schema.columns
WHERE table_schema = :schema
AND table_name = :table";
var cnn = new Npgsql.NpgsqlConnection(ConnectionString);
var cmd = new Npgsql.NpgsqlCommand("select * from " + Globals.Compiler.GetQName(schema, table_name), cnn);
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName="schema",
// Value=schema
//});
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName = "table",
// Value = table_name
//});
DataTable tbl = new DataTable();
//var adp = new Npgsql.NpgsqlDataAdapter(cmd);
try
{
cnn.Open();
tbl = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly).GetSchemaTable();
//adp.Fill(tbl);
}
catch (Exception ex)
{
cnn.Close();
throw (ex);
}
finally
{
cnn.Close();
}
ret.Columns = tbl.Rows.Cast <DataRow>().Select(p => new ColumInfo()
{
Name = p["ColumnName"].ToString(),
IsUnique = (p["IsUnique"] == DBNull.Value) ? false : (bool)p["IsUnique"],
IsAutoIncrement = (p["IsAutoIncrement"] == DBNull.Value) ? false : (bool)p["IsAutoIncrement"],
IsKey = (p["IsKey"] == DBNull.Value) ? false : (bool)p["IsKey"],
AllowDBNull = (p["AllowDBNull"] == DBNull.Value) ? false : (bool)p["AllowDBNull"],
IsReadOnly = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsExpression = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsIdentity = (p["IsIdentity"] == DBNull.Value) ? false : (bool)p["IsIdentity"],
DataType = p["DataType"],
ColumnSize = (int)p["ColumnSize"]
//DefaultValue = p["column_default"].ToString(),
//IsAuto = p["column_default"].ToString().Split("(")[0] == "nextval",
//AutoConstraint = ((p["column_default"].ToString().Split("(")[0] == "nextval") ? p["column_default"].ToString().Split("(")[1].Split("::")[0] : "")
}).ToList();
return(ret);
}
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 string canHandIn(string testID)
{
string result = "";
NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, start_time, end_time FROM completed_test where id= @testID ", conn);
cmd.Parameters.AddWithValue("testID", int.Parse(testID));
//cmd.Parameters.Add("testID", testID);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
result = dr["start_time"].ToString();
if (dr["end_time"] != null)
{
}
}
else
{
dr.Close();
conn.Close();
return "FINNS INGET TEST";
}
dr.Close();
conn.Close();
TimeSpan diffTime = DateTime.Parse(DateTime.Now.ToString()) - DateTime.Parse(result);
if (diffTime.TotalMinutes > 29)
{
return "TIDEN DROG ÖVER";
}
return "OK";
}
//создание формы
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);
}
}
protected void CreateUser_Click(object sender, EventArgs e)
{
//ArrayList for emails
ArrayList maillist = new ArrayList();
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
"Password=root;Database=project56;");
//Open connection
conn.Open();
// Define query
NpgsqlCommand cmd = new NpgsqlCommand("SELECT email FROM users", conn);
// Execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
//Get rows and place in ArrayList
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
maillist.Add(dr[i]);
}
}
// Close connection
conn.Close();
//Check if email in TextBox equals one of the existing accounts
for (int i = 0; i < maillist.Count; i++)
{
if (maillist[i].Equals(Email.Text))
{
EmailUnique = false;
MessageBox.Show(Page, "Dit emailadres is al geregistreerd!");
break;
}
}
if (EmailUnique)
{
//Insert Email and Password from TextBoxes
conn.Open();
NpgsqlCommand cmd1 = new NpgsqlCommand("INSERT INTO users(email, password, rank) VALUES (:email, :pw, 0)", conn);
cmd1.Parameters.Add(new NpgsqlParameter("email", Email.Text));
cmd1.Parameters.Add(new NpgsqlParameter("pw", Password.Text));
cmd1.ExecuteNonQuery();
conn.Close();
MessageBox.Show(Page, "Uw account is geregistreerd!");
}
}
public void readPtData(string patientID)
{
#region Npgsql
NpgsqlConnection conn;
try
{
conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting);
}
catch (ArgumentException)
{
MessageBox.Show(Properties.Resources.WrongConnectingString, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{ conn.Open(); }
catch (NpgsqlException)
{
MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
return;
}
catch (System.IO.IOException)
{
MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
return;
}
#endregion
string sql = "SELECT * FROM patient WHERE pt_id='" + patientID + "'";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count == 0)
{
conn.Close();
lbPtNameDB.Text = "No data";
return;
}
else
{
DataRow row = dt.Rows[0];
lbPtNameDB.Text = row["pt_name"].ToString();
conn.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 Alterar(Model_Vo_LivroCaixa pLivroCaixa)
{
// 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 livrocaixa set datahora = @datahora, descricao = @descricao, idcontasareceber = @idcontasareceber, tipodemovimento = @tipodemovimento, valor = @valor where id = @id;";
cmd.Parameters.AddWithValue("@id", pLivroCaixa.Id);
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();
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 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 DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
{
DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from kindofanimals where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.infoURL = reader["infoURL"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static DataTable getExecuteSQL(string sql, bool isTransaction = false)
{
DataSet ds = new DataSet();
if (!isTransaction)
{
conn.ConnectionString = CadenaConexion;
conn.Open();
}
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
//Limpiar el dataset
ds.Reset();
// llenar con la definicion de la consulta
da.Fill(ds);
if (!isTransaction)
{
conn.Close();
}
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
else
{
return(null);
}
}
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 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();
}
}
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 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();
}
}
//for testing
public void DeletePurchaserDataRecursive(long id)
{
Npgsql.NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "DELETE FROM dienstverhaeltnis WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command = new NpgsqlCommand(null, con);
command.CommandText = "DELETE FROM auftraggeberdaten WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command.CommandText = "DELETE FROM auftraggeber WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command.CommandText = "DELETE FROM person WHERE personid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
con.Close();
}
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 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 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!";
}
}
public static bool CreateConnection(ConnectionParams connectionParams, out NpgsqlConnection createdConnection)
{
bool result = false;
string connstring = "";
createdConnection = null;
try
{
connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
connectionParams.Server, connectionParams.Port, connectionParams.Username, connectionParams.Password, connectionParams.Database);
createdConnection = new NpgsqlConnection(connstring);
if(connectionParams.OpenImmidiately)
createdConnection.Open();
_allConnections.Add(createdConnection);
}
catch (Exception exc)
{
if (createdConnection != null && createdConnection.State == System.Data.ConnectionState.Open)
createdConnection.Close();
createdConnection = null;
}
return result;
}
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 ();
}
public static DBclasses.AsksCategory getAsksCategoriesById(int id)
{
DBclasses.AsksCategory result = new DBclasses.AsksCategory();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from askscategories where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.description = reader["description"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
// having a connecting to DB function with the sqlstring as input
private List<Team> ReadFromDB(string sqlstring)
{
List<Team> teams = new List<Team>();
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=reader;Password=hej123;Database=guessthegame;");
try
{
//connecting to the database
conn.Open();
// defining the sql npgsql command
NpgsqlCommand command = new NpgsqlCommand(sqlstring, conn);
NpgsqlDataReader dr = command.ExecuteReader();
Team tempTeam;
//reading all the users.
while (dr.Read())
{
tempTeam = new Team(dr.GetInt32(0), dr.GetString(1));
teams.Add(tempTeam);
}
}
catch (Exception msg)
{
//do something if exception
System.Diagnostics.Debug.WriteLine(msg.ToString());
}
finally
{
conn.Close();
}
return teams;
}
protected void Page_Load(object sender, EventArgs e)
{
string rola;
FormsIdentity id =
(FormsIdentity)HttpContext.Current.User.Identity;
FormsAuthenticationTicket bilet = id.Ticket;
Label1.Text = "Zalogowany jako: " + User.Identity.Name;
// Get the stored user-data, in this case, our roles
rola = bilet.UserData;
if (rola != "admins") Response.Redirect("index.aspx");
DataSet ds = new DataSet();
DataTable dt = new DataTable();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
// quite complex sql statement
string sql = "SELECT * FROM sala";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
// i always reset DataSet before i do
// something with it.... i don't know why :-)
ds.Reset();
// filling DataSet with result from NpgsqlDataAdapter
da.Fill(ds);
// since it C# DataSet can handle multiple tables, we will select first
dt = ds.Tables[0];
// connect grid to DataTable
GridView1.DataSource = dt;
GridView1.DataBind();
// since we only showing the result we don't need connection anymore
conn.Close();
}
public static List<Forestallning> HamtaForestallningLista()
{
List<Forestallning> forestallningslista = new List<Forestallning>();
NpgsqlConnection conn = new NpgsqlConnection("Server=webblabb.miun.se;Port=5432;Database=pgmvaru_g4;User Id=pgmvaru_g4;Password=trapets;ssl=true");
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("Select * from forestallning ORDER BY namn, datum, starttid ASC", conn);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Forestallning forestallning = new Forestallning();
forestallning.id = Convert.ToInt32(dr["id"]);
forestallning.namn = (string)dr["namn"];
forestallning.generellinfo = (string)dr["generell_info"];
forestallning.open = (bool)dr["open"];
forestallning.datum = Convert.ToDateTime(dr["datum"]);
forestallning.starttid = Convert.ToDateTime(dr["starttid"]);
forestallning.sluttid = Convert.ToDateTime(dr["sluttid"]);
forestallning.vuxenpris = Convert.ToInt32(dr["vuxenpris"]);
forestallning.ungdomspris = Convert.ToInt32(dr["ungdomspris"]);
forestallning.barnpris = Convert.ToInt32(dr["barnpris"]);
forestallning.forsaljningsslut = Convert.ToDateTime(dr["forsaljningslut"]);
forestallningslista.Add(forestallning);
}
conn.Close();
return forestallningslista;
}
public string getLevel(string userID)
{
string retLevel = "";
string getUsers;
try
{
NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
getUsers = "SELECT * FROM users INNER JOIN team ON users.id = team.user_id WHERE users.id = @uId";
// string sql = "SELECT * FROM users RIGHT JOIN team on users.id = team.user_id WHERE user.id='" + userID + "'";
NpgsqlCommand cmd = new NpgsqlCommand(getUsers, conn);
cmd.Parameters.AddWithValue("uId", Convert.ToInt32(userID));
conn.Open();
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
retLevel = "provledare";
}
else
{
retLevel = "deltagare";
}
conn.Close();
}
catch (NpgsqlException ex)
{
Debug.WriteLine(ex.Message);
}
return retLevel;
}
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();
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" +
"User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true";
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = "SELECT c.name as name1, a.movie, a.id id, b.all, trunc(100*a.movie/b.all,6) procent from genre c, (select count(*) as all from movie_genre) b, (select count(movie_id) as movie, genre_id as id from movie_genre group by genre_id) a where c.id = a.id";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(ds, "movie_genre");
dt = ds.Tables[0];
foreach (DataRow row in ds.Tables[0].Rows)
{
PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
{
Category = row["name1"].ToString(),
Data = Convert.ToDecimal(row["procent"])
});
}
conn.Close();
}
catch (Exception msg)
{
}
}
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!";
}
}
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();
}
}
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();
}
}
public void FecharContasAReceber(Model_Vo_ContasAReceber pContasAReceber)
{
// 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 contasareceber set recebido = @recebido where idreservaorigem = @idreservaorigem;";
cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
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();
}
}
/// <summary>
/// Inherited Abstract method that now overriden in this class to enable authentication of a user.
/// </summary>
public override void execute_action()
{
/// Instantiate a connection object to our PostGreSQL server
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ProudSourceDB"].ConnectionString);
Npgsql.NpgsqlDataAdapter da = new NpgsqlDataAdapter(this.query, conn);
/// prepare the data that will be compared against
da.SelectCommand.Parameters.AddWithValue("@UserName", this.Username);
da.SelectCommand.Parameters.AddWithValue("Password", this.PasswordHash);
System.Data.DataSet ds = new System.Data.DataSet();
try
{
conn.Open();
da.Fill(ds);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(String.Format("Error querying data base, user authentication. Error Data \r\n{0},\r\n{1}\r\n{2}", e.Message, e.InnerException, e.Data));
UserAuthenticated = false;
}
finally
{
conn.Close();
}
/// Check for success or failure
if (ds.Tables[0].Rows.Count == 1)
{
this.UserAuthenticated = true;
}
else
{
this.UserAuthenticated = true;
}
}
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 Boolean Excluir(int id)
{
//conexao
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 = "delete from produto where id = " + Convert.ToString(id);
cn.Open();
int resultado = cmd.ExecuteNonQuery();
if (resultado != 1)
{
throw new Exception("Não foi possível excluir a produto " + Convert.ToString(id));
}
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
return(true);
}
/// <summary>
/// 将数据读取到 DataSet 中.
/// </summary>
public void ReadDataToDataSet()
{
Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");
// 建立数据库连接.
NpgsqlConnection conn = new NpgsqlConnection(connString);
// 创建一个适配器
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(SQL, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
// 关闭数据库连接.
conn.Close();
// 处理DataSet中的每一行数据.
foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
{
// 将检索出来的数据，输出到屏幕上.
Console.WriteLine("NO:{0} ; Date:{1} ; Money:{2} ",
testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
);
}
}
public static List<Akt> HamtaAktLista(int valdforestallningsid)
{
List<Akt> aktlista = new List<Akt>();
NpgsqlConnection conn = new NpgsqlConnection("Server=webblabb.miun.se;Port=5432;Database=pgmvaru_g4;User Id=pgmvaru_g4;Password=trapets;ssl=true");
conn.Open();
string sql1 = @"SELECT * FROM akter , forestallning WHERE akter.forestallningsid = forestallning.id
and akter.forestallningsid = :nyValdforestallningsid ORDER BY akter.starttid ASC";
NpgsqlCommand command = new NpgsqlCommand(sql1, conn);
command.Parameters.Add(new NpgsqlParameter("nyValdforestallningsid", DbType.Int32));
command.Parameters[0].Value = valdforestallningsid;
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
Akt akten = new Akt();
akten.namn = (string)dr["aktnamn"];
akten.Aktinfo = (string)dr["aktinfo"];
akten.Starttid = Convert.ToDateTime(dr["starttid"]);
akten.Sluttid = Convert.ToDateTime(dr["sluttid"]);
akten.vuxen = Convert.ToInt32(dr["vuxenpris"]);
akten.ungdom = Convert.ToInt32(dr["ungdomspris"]);
akten.barn = Convert.ToInt32(dr["barnpris"]);
akten.id = Convert.ToInt32(dr["id"]);
aktlista.Add(akten);
}
conn.Close();
return aktlista;
}
protected void Page_Load(object sender, EventArgs e)
{
if (SiteMaster.Rank == "2" && SiteMaster.LoggedIn)
{
// Specify connection options and open an connection
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
"Password=root;Database=project56;");
conn.Open();
// Define query
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM users", conn);
// Execute query
NpgsqlDataReader dr = cmd.ExecuteReader();
//Get rows and place in ArrayList
while (dr.Read())
{
maillist.Add(dr[0]);
ranklist.Add(dr[2]);
}
// Close connection
conn.Close();
for(int i=0;i<maillist.Count;i++)
{
maildrop.Items.Add(maillist[i].ToString()); //Add all emails to dropdown
}
}
}
public Main()
{
InitializeComponent();
string str = "Uid=postgres; Password=123; server=localhost; port=5432; Database=classmanagement;";
NpgsqlConnection conn = new NpgsqlConnection(str);
conn.Open();
String select = "SELECT name FROM subject";
NpgsqlCommand select_command = new NpgsqlCommand(select, conn);
NpgsqlDataReader reader = select_command.ExecuteReader();
while (reader.Read())
{
comboBox1.Items.Add(reader[0]);
comboBox3.Items.Add(reader[0]);
comboBox4.Items.Add(reader[0]);
comboBox6.Items.Add(reader[0]);
comboBox7.Items.Add(reader[0]);
}
dataGridView2.Refresh();
dataGridView2.ColumnCount = 2;
dataGridView2.Columns[0].Name = "Name";
dataGridView2.Columns[1].Name = "Surname";
reader.Close();
conn.Close();
}
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 string getXml(string testID)
{
string result = "";
try
{
NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, xml_answer as qXml FROM completed_test where id= @testID", conn);
cmd.Parameters.AddWithValue("testID", int.Parse(testID));
//cmd.Parameters.Add("testID", testID);
NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
result = dr["qXml"].ToString();
}
dr.Close();
conn.Close();
return result.TrimStart();
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
return "";
}
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 Connection_Test(ENTITE.Serveur bean)
{
if (bean.Control_())
{
NpgsqlConnection con = new NpgsqlConnection();
try
{
string constr = "PORT=" + bean.getPort + ";TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE= 2.0.14.3;DATABASE=" + bean.getDatabase + ";HOST=" + bean.getAdresse + ";PASSWORD="******";USER ID=" + bean.getUser + "";
con = new NpgsqlConnection(constr);
con.Open();
return true;
}
catch (NpgsqlException ex)
{
Messages.Exception(ex);
return false;
}
finally
{
con.Close();
con.Dispose();
}
}
return false;
}
public bool AutenticarUsuario(string usuario, string senha)
{
string sql = "select usernumber from usertable where username = @usuario and userpassword = @senha";
List<Dominio.Submissao> submissoes = new List<Dominio.Submissao>();
NpgsqlConnection conexao = new NpgsqlConnection("Server=187.45.196.224;Database=bubblesort9;User ID=bubblesort9;Password=BSboca;");
try
{
conexao.Open();
NpgsqlCommand comando = new NpgsqlCommand(sql, conexao);
comando.Parameters.Add("usuario", NpgsqlTypes.NpgsqlDbType.Varchar).Value = usuario;
comando.Parameters.Add("senha", NpgsqlTypes.NpgsqlDbType.Varchar).Value = GetMD5Hash(senha);
var reader = comando.ExecuteReader();
bool resposta;
if (reader.HasRows)
{
resposta = true;
}
else
resposta = false;
reader.Close();
return resposta;
}
finally
{
conexao.Close();
}
}
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();
}
}
public void close()
{
if (con != null)
{
con.Close();
}
con = null;
}
public static List <DBclasses.Pet> getAllPets()
{
List <DBclasses.Pet> result = new List <DBclasses.Pet>();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from pets order by id";
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
DBclasses.Pet row = new DBclasses.Pet();
row.id = (long)reader["id"];
row.kindOfAnimals = (int)reader["kindOfAnimals"];
row.addedByUser = (long)reader["addedByUser"];
row.addedDateTime = reader["addedDateTime"].ToString();
row.lastUpdatedByUser = (long)reader["lastUpdatedByUser"];
row.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
row.name = reader["name"].ToString();
row.photos = reader["photos"] as string[];
row.code = reader["code"].ToString();
row.sterilised = (bool)reader["sterilised"];
row.pasported = (bool)reader["pasported"];
row.isLosted = (bool)reader["isLosted"];
row.birthDate = reader["birthDate"].ToString();
row.roughlyBirth = (bool)reader["roughlyBirth"];
row.roughlyDay = (bool)reader["roughlyDay"];
row.roughlyMonth = (bool)reader["roughlyMonth"];
row.organizationID = (int)reader["organizationID"];
row.address = reader["address"].ToString();
row.isDisabled = (bool)reader["isDisabled"];
row.disableDescription = reader["disableDescription"].ToString();
row.price = (long)reader["price"];
row.callTimeFrom = reader["_callTimeFrom"].ToString();
row.callTimeTo = reader["_callTimeTo"].ToString();
row.region = (int)reader["region"];
row.phones = reader["phones"] as string[];
row.vaccinated = (bool)reader["vaccinated"];
row.sex = (bool)reader["sex"];
result.Add(row);
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static IEnumerable <FindBooksTakenOut> FindBooksTakenOut(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var BooksTakenOutFound = connection.Query <FindBooksTakenOut>("SELECT * FROM \"Books Taken Out\"").AsList();
connection.Close();
return(BooksTakenOutFound);
}
public static IEnumerable <FindUsers> FindAllUsers(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var AllUsersFound = connection.Query <FindUsers>("SELECT * FROM \"Users\"").AsList();
connection.Close();
return(AllUsersFound);
}
public static int FindUserIDByEmail(string userEmail, string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var User = connection.QueryFirstOrDefault <FindUsers>($"SELECT * FROM \"Users\" WHERE \"Email\" = \'{userEmail}\'");
connection.Close();
return(User.UserID);
}
public static IEnumerable <Books> FindAllBooks(string connectionString)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
var FoundBooks = connection.Query <Books>("SELECT * FROM \"Books\"").AsList();
connection.Close();
return(FoundBooks);
}
private void button1_Click(object sender, EventArgs e)
{
alabel.Text = "";
c.Text = "";
tc.Text = "";
supc.Text = "";
supnc.Text = "";
pc.Text = "";
pnc.Text = "";
c.Text = Text;
alabel.Text = "";
Nantenne.Text = "";
puissance.Text = "";
s.Text = "";
densite.Text = "";
nbrZone.Text = "";
NC.Text = "";
NCC.Text = "";
cnx.Close();
cnx.Open();
n.NpgsqlCommand caa = new n.NpgsqlCommand(" Create or replace view zonec as select ST_INTERSECTION(ST_UNION(st_buffer(antenne.emp,puissance)),ST_UNION(quartier.zone)) as valeur from antenne,quartier;", cnx);
caa.ExecuteNonQuery();
VectorLayer nc = new VectorLayer("")
{
DataSource = new PostGIS(src, "zonec", "valeur", null)
};
nc.Style.Fill = Brushes.Black;
nc.Style.Outline = Pens.White;
nc.Style.EnableOutline = true;
map1.Map.Layers.Clear();
map1.Map.Layers.Add(quart);
map1.Map.Layers.Add(ant);
map1.Map.Layers.Add(nc);
map1.Map.ZoomToExtents();
map1.Refresh();
cnx.Close();
c.Text = "";
}
public static DBclasses.Organization getOrganizationById(int id)
{
DBclasses.Organization result = new DBclasses.Organization();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from organizations where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.isDeleted = (bool)reader["isDeleted"];
result.name = reader["name"].ToString();
result.contactPerson = reader["contactPerson"].ToString();
result.emails = reader["emails"] as string[];
result.phones = reader["phones"] as string[];
/* TODO */
result.callTimeFrom = reader["callTimeFrom"].ToString();;
result.callTimeTo = reader["callTimeTo"].ToString();;
result.addresses = reader["addresses"] as string[];
result.photo = reader["photo"].ToString();
result.aboutOrg = reader["aboutOrg"].ToString();
result.needHelp = reader["needHelp"].ToString();
result.donation = reader["donation"].ToString();
result.otherOrg = reader["otherOrg"].ToString();
result.linkToVK = reader["linkToVK"].ToString();
result.linkToFB = reader["linkToFB"].ToString();
result.linkToYT = reader["linkToYT"].ToString();
result.linkToTG = reader["linkToTG"].ToString();
result.linkToIG = reader["linkToIG"].ToString();
result.region = (int)reader["region"];
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
result.name = ex.StackTrace + ex.Message;
return(result);
}
}
return(result);
}
public void buffer_select(int num)
{
cnx.Close();
cnx.Open();
np.NpgsqlCommand cm = new np.NpgsqlCommand("create or replace view bf_t as Select num,st_buffer(emp,puissance) as bf from antenne where num=" + num + "", cnx);
cm.ExecuteNonQuery();
VectorLayer vc = new VectorLayer("buffer")
{
DataSource = new PostGIS(srx, "bf_t", "bf", "num")
};
VectorStyle styleSelected = new VectorStyle
{
Fill = Brushes.Transparent,
Outline = Pens.Blue,
EnableOutline = true
};
vc.Style = styleSelected;
mapImage1.Map.Layers.Add(vc);
cnx.Close();
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.IsOpen)
{
dbc.Close();
}
if (dbc != null)
{
dbc.Dispose();
dbc = null;
}
//throw new NotImplementedException();
}
override internal void LoadAll()
{
try
{
string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
"n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
"idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
"JOIN pg_class cls ON cls.oid=indexrelid " +
"JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
"JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
"JOIN pg_am a ON a.oid = cls.relam " +
"LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
"LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
"WHERE con.conname IS NULL ORDER BY cls.relname;";
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString);
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn);
cn.Open();
cn.ChangeDatabase(this.Table.Tables.Database.Name);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
cn.Close();
PopulateArrayNoHookup(metaData);
for (int i = 0; i < this.Count; i++)
{
Index index = this[i] as Index;
if (null != index)
{
string s = index._row["columns"] as string;
string[] colIndexes = s.Split(' ');
foreach (string colIndex in colIndexes)
{
if (colIndex != "0")
{
int id = Convert.ToInt32(colIndex);
Column column = this.Table.Columns[id - 1] as Column;
index.AddColumn(column.Name);
}
}
}
}
}
catch {}
}
public Boolean Excluir(int idMovimentacaoEstoque, int idAgenda)
{
//conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if ((idMovimentacaoEstoque > 0))
{
cmd.CommandText = "delete from movimentacaoestoque where id = " + Convert.ToString(idMovimentacaoEstoque);
}
else
{
cmd.CommandText = "delete from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda);
}
cn.Open();
int resultado = cmd.ExecuteNonQuery();
if (resultado != 1)
{
if ((idAgenda > 0))
{
throw new Exception("Não foi possível excluir a MovimentacaoEstoque da Agenda: " + Convert.ToString(idAgenda));
}
else
{
throw new Exception("Não foi possível excluir a MovimentacaoEstoque " + Convert.ToString(idMovimentacaoEstoque));
}
}
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
return(true);
}
