/// <summary>
/// Get all CBS neighbourhoods, specifically its name, boundary (in WKT), and center.
/// </summary>
public void Enhance(IEnumerable<LocationDescription> locationDescriptions)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var streets = new List<string>();
foreach (var locationDescription in locationDescriptions)
{
var query = string.Format(Query, locationDescription.RdBoundary);
using (var command = new NpgsqlCommand(query, conn))
{
try
{
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
streets.Add(dr["straat"].ToString());
}
}
}
catch (SystemException e)
{
Console.WriteLine(e.Message);
}
}
locationDescription.Features.Add("straten", string.Join(";", streets));
}
}
}
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 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();
}
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!");
}
}
/// <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.");
}
}
}
}
/// <summary>
/// Gets an open connection to the DB. Can be called any number of times.
/// </summary>
/// <param name="connectionString">
/// The connection String.
/// </param>
/// <returns>
/// The <see cref="NpgsqlConnection"/>.
/// </returns>
public static NpgsqlConnection OpenDBConnection(string connectionString)
{
// create the connection
var connection = new NpgsqlConnection(connectionString);
connection.Notification += new NotificationEventHandler(Connection_InfoMessage);
connection.Open();
if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
private void logButtonClicked(object sender, RoutedEventArgs e)
{
Label1.Visibility = Visibility.Hidden;
Label2.Visibility = Visibility.Hidden;
AdminPanelButton.Visibility = Visibility.Hidden;
connectionString = String.Format("Host=127.0.0.1;Username={0};Password={1};Database=name",
textBox1.Text, pasBox.Password);
pasBox.Password = "";
connection.ConnectionString = connectionString.ToString();
try
{
connection.Open();
MessageBox.Show("Успешно", "Соединение");
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "Ошибка соединения");
}
pasBox.Visibility = Visibility.Hidden;
textBox1.Visibility = Visibility.Hidden;
logButton.Visibility = Visibility.Hidden;
discButton.Visibility = Visibility.Visible;
execButton.Visibility = Visibility.Visible;
tablesComboBox.Visibility = Visibility.Visible;
procComboBox.Visibility = Visibility.Visible;
updateButton.Visibility = Visibility.Visible;
}
/// <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 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);
}
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);
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connectionString"></param>
public void Open(string connectionString)
{
//throw new NotImplementedException();
this.Close();
dbc = new NpgsqlConnection(connectionString);
dbc.Open();
}
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 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_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();
}
}
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();
}
}
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 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();
}
}
private static void RecreateSchemaAndInstallObjects()
{
using (var connection = new NpgsqlConnection(
ConnectionUtils.GetMasterConnectionString()))
{
bool databaseExists = connection.Query<bool?>(
@"select true :: boolean from pg_database where datname = @databaseName;",
new
{
databaseName = ConnectionUtils.GetDatabaseName()
}
).SingleOrDefault() ?? false;
if (!databaseExists)
{
connection.Execute($@"CREATE DATABASE ""{ConnectionUtils.GetDatabaseName()}""");
}
}
using (var connection = new NpgsqlConnection(ConnectionUtils.GetConnectionString()))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
PostgreSqlObjectsInstaller.Install(connection);
PostgreSqlTestObjectsInitializer.CleanTables(connection);
}
}
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;
}
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 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();
}
}
}
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;
}
}
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();
}
}
}
}
}
}
/// <summary>
/// Initialises the estatedata class.
/// </summary>
/// <param name="connectionString">connectionString.</param>
public void Initialise(string connectionString)
{
if (!string.IsNullOrEmpty(connectionString))
{
m_connectionString = connectionString;
_Database = new PGSQLManager(connectionString);
}
//Migration settings
using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
{
conn.Open();
Migration m = new Migration(conn, GetType().Assembly, "EstateStore");
m.Update();
}
//Interesting way to get parameters! Maybe implement that also with other types
Type t = typeof(EstateSettings);
_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in _Fields)
{
if (f.Name.Substring(0, 2) == "m_")
_FieldMap[f.Name.Substring(2)] = f;
}
}
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;
}
}
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);
}
// GET api/values
public IEnumerable<string> Get()
{
// return new string[] { "value1", "value2" };
var result = new List<string>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"SELECT name,amenity, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'pub\' LIMIT 10;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(reader.GetString(2));
}
}
}
}
return result;
}
public void SetUp()
{
Connection = new NpgsqlConnection(IntegrationTestOptions.Postgres.ConnectionString);
Processor = new PostgresProcessor(Connection, new PostgresGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new PostgresDbFactory());
Quoter = new PostgresQuoter();
Connection.Open();
}
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();
}
}
private static void TruncateAndCopy(string table, string[] columns, BlockingCollection <object[]> data, Action <string> log)
{
try
{
log("Connecting to database");
using (var c = new Npgsql.NpgsqlConnection("Host=/var/run/postgresql;Port=5433"))
{
c.Open();
using (var t = c.BeginTransaction())
{
log($"TRUNCATE {Q(table)}");
using (var cmd = new NpgsqlCommand($"TRUNCATE {Q(table)}", c))
cmd.ExecuteNonQuery();
log($"COPY {Q(table)}");
Copy(
c,
tablename: table,
colnames: columns,
data: data.GetConsumingEnumerable()
);
log("COMMIT");
t.Commit();
}
}
}
finally
{
log("Disposing data...");
data.CompleteAdding();
data.Dispose();
}
}
private string GetForeignKeyReferenceTableName(string selectedTableName, string columnName)
{
var conn = new Npgsql.NpgsqlConnection(connectionStr);
conn.Open();
using (conn)
{
NpgsqlCommand tableCommand = conn.CreateCommand();
tableCommand.CommandText = String.Format(
@"
select pk.table_name
from information_schema.referential_constraints c
inner join information_schema.table_constraints fk on c.constraint_name = fk.constraint_name
inner join information_schema.table_constraints pk on c.unique_constraint_name = pk.constraint_name
inner join information_schema.key_column_usage cu on c.constraint_name = cu.constraint_name
inner join (
select i1.table_name, i2.column_name
from information_schema.table_constraints i1
inner join information_schema.key_column_usage i2 on i1.constraint_name = i2.constraint_name
where i1.constraint_type = 'PRIMARY KEY'
) pt on pt.table_name = pk.table_name
where fk.table_name = '{0}' and cu.column_name = '{1}'",
selectedTableName, columnName);
object referencedTableName = tableCommand.ExecuteScalar();
return((string)referencedTableName);
}
}
void Initialize()
{
string connect = "Server=127.0.0.1;Port=5432;User Id=s;Database=practice;";
var connection = new NpgsqlConnection(connect);
if(connection.State == ConnectionState.Closed) { connection.Open(); }
users = new Table("users", connection);
}
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);
}
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 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 pgWorker(int pos, string dsn,
ProgressChangedEventHandler onProgress,
RunWorkerCompletedEventHandler onComplete)
{
this.thNo = pos;
this.pg_conn = new NpgsqlConnection(dsn);
try
{
pg_conn.Open();
pg_error = "";
}
catch (Exception ex)
{
pg_conn = null;
pg_error = ex.Message;
}
if (pg_conn != null)
{
this.bw = new BackgroundWorker();
this.bw.WorkerReportsProgress = true;
this.bw.WorkerSupportsCancellation = true;
this.bw.DoWork += this.bwDoWork;
this.bw.ProgressChanged += onProgress;
this.bw.RunWorkerCompleted += onComplete;
}
}
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 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 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 PostgresProcessor(NpgsqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
: base(generator, announcer, options)
{
Connection = connection;
connection.Open();
Transaction = connection.BeginTransaction();
}
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 "";
}
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 IDocumentStore Initialize(Action<MartenRegistry> register = null)
{
var builder = new NpgsqlConnectionStringBuilder(_targetConnectionString);
var targetDatabaseName = builder.Database;
using (var connection = new NpgsqlConnection(_masterConnectionString))
{
connection.Open();
var existsCommand = connection.CreateCommand();
existsCommand.CommandText = "select (count(*) > 0)::boolean as exists from pg_database where datname=:0";
existsCommand.Parameters.Add(new NpgsqlParameter("0", targetDatabaseName));
var exists = (bool)existsCommand.ExecuteScalar();
if (!exists)
{
var createCommand = connection.CreateCommand();
createCommand.CommandText = string.Format("CREATE DATABASE \"{0}\"", targetDatabaseName);
createCommand.ExecuteNonQuery();
}
}
var store = DocumentStore.For(cfg =>
{
cfg.Connection(_targetConnectionString);
cfg.AutoCreateSchemaObjects = true;
cfg.Schema.For<Commit>()
.Searchable(x => x.StreamId)
.Searchable(x => x.StreamVersion);
if (register != null)
{
register(cfg.Schema);
}
});
return store;
}
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 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();
}
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();
}
}
public IEnumerable<Routine> GetRoutines()
{
var list = new List<Routine>();
using (var sqlConnection = new NpgsqlConnection(connectionString))
{
using (var cmd = new NpgsqlCommand(@"
SELECT routine_name
FROM INFORMATION_SCHEMA.routines
WHERE
routine_schema <> 'pg_catalog'
and routine_schema <>'information_schema'
", sqlConnection))
{
sqlConnection.Open();
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Routine(reader.GetString(0)));
}
}
}
}
return list;
}
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 static NpgsqlConnection DBConnection(string serv_adress, string port, string user_id, string pass, string db_name)
//Соединение с базой данных
{
Npgsql.NpgsqlConnection connect = new Npgsql.NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", serv_adress, port, user_id, pass, db_name));
connect.Open();
return(connect);
}
public static Npgsql.NpgsqlConnection CreateConnection()
{
string strConnection = @"Server=192.168.145.12; Port=5432; User Id=pqm; Password=dbuser; Database=pqmdb; CommandTimeout=100; Timeout=100;";
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(strConnection);
conn.Open();
return(conn);
}
//
// static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
// {
// string cnstr = dbRoot.ConnectionString + "Database=" + database + ";";
// NpgsqlConnection cn = new Npgsql.NpgsqlConnection(cnstr);
// return cn;
// }
static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
{
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(database);
return(cn);
}
/// <summary>
/// Функция инициализации подключения к базе данных.
/// </summary>
/// <returns>Если успешно, то true</returns>
private bool ConnectionInitDataBase()
{
//Далее только если подключение не через сервер приложений:
this.DatabaseLoginDecrypt = Crypto.DecryptAES(DatabaseLogin);
this.DatabasePassDecrypt = Crypto.DecryptAES(DatabasePass);
try
{
if (serverType == ServerType.Postgre)
{
ConnectStringPostgre = "Server=" + ServerName + ";User Id=" + DatabaseLoginDecrypt + ";Password="******";Database=" + DatabaseName + ";";
Npgsql.NpgsqlConnection connection1 = null;
connection1 = new Npgsql.NpgsqlConnection(ConnectStringPostgre);
connection1.Open();
}
if (serverType == ServerType.MSSQL)
{
if (DatabaseLogin == "")
{
ConnectStringMSSQL = @"Data Source=" + ServerName + ";Initial Catalog=" + DatabaseName + ";Integrated Security=True";
}
else
{
ConnectStringMSSQL = "Server=" + ServerName + ";User Id=" + DatabaseLoginDecrypt + ";Password="******";Database=" + DatabaseName + ";"; //Connection Timeout=2;
}
System.Data.SqlClient.SqlConnection connection2;
connection2 = new System.Data.SqlClient.SqlConnection(ConnectStringMSSQL);
connection2.Open();
}
if (serverType == ServerType.SQLite)
{
if ((sys.ErrorCheck(!File.Exists(ServerName), "Не найден файл базы данных.")))
{
return(false);
}
ConnectStringSQLite = @"Data Source=" + ServerName + @";New=False;Version=3;";
System.Data.SQLite.SQLiteConnection connection;
connection = new System.Data.SQLite.SQLiteConnection(ConnectStringSQLite);
connection.Open();
//connection3.BindFunction(CloseToSQLiteFunction.GetAttribute(), new CloseToSQLiteFunction());
}
serverTypeRemote = serverType;
ConnectionActive = true;
ConnectionDirect = true;
return(true);
}
catch (Exception e)
{
sys.SM("Ошибка подключения: " + e.Message + " к серверу " + serverType.ToString());
ConnectionActive = false;
ConnectionDirect = false;
return(false);
}
}
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 int ExecuteNonQueryOfSql(string sql)
{
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
return(cmd.ExecuteNonQuery());
}
}
}
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 object ScalerOfSql(string sql)
{
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
return(cmd.ExecuteScalar());
}
}
}
private void Init()
{
string connectionParameters = "Server=localhost;Port=5432;Username=postgres;Password=123;Database=third;";
connection = new Npgsql.NpgsqlConnection(connectionParameters);
sentences = new Dictionary <int, string>();
insertQeuries = new string[0];
connection.Open();
PopulateStringValues();
index = Index.noIndex;
}
