public static void AccessMaster(string Action, string TableName, Hashtable hash)
{
using(NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING))
{
string query = string.Format(@"SELECT * FROM ""{0}""", TableName);
using(NpgsqlCommand cmd = new NpgsqlCommand(@query, con))
{
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = cmd;
NpgsqlCommand actionCommand = null;
if(Action == "Entry"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetInsertCommand();
} else if(Action == "Modify"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetUpdateCommand();
} else if(Action == "Remove"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetDeleteCommand();
}
try {
foreach(NpgsqlParameter param in actionCommand.Parameters){
if(hash[param.SourceColumn] != null)
param.Value = hash[param.SourceColumn];
}
actionCommand.ExecuteNonQuery();
} catch (Exception ex) {
MessageBox.Show(string.Format("{0}", ex.Message));
MessageBox.Show(string.Format("{0}", ex.StackTrace.ToString()));
}
}
}
}
public List<Product> getList()
{
using (NpgsqlConnection connection = getConnection())
{
using (NpgsqlCommand cmd = new NpgsqlCommand(SELECT_CMD, connection))
{
connection.Open();
List<Product> result = new List<Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product temp = new Product();
temp.id = reader.GetGuid(0);
temp.description = reader.GetString(1);
temp.productGroupID = reader.GetGuid(2);
temp.Unit = Units.FromName(reader.GetString(3));
if (!reader.IsDBNull(4))
{
temp.weight = (Decimal)reader.GetFloat(4);
}
temp.money = reader.GetDecimal(5);
temp.quantity = reader.GetInt32(6);
result.Add(temp);
}
}
return result;
}
}
}
public static List<GrilleRabais> getListGrilleRistourne(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<GrilleRabais> l = new List<GrilleRabais>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
GrilleRabais a = new GrilleRabais();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Nature = lect["nature_montant"].ToString();
a.Minimal = (Double)((lect["montant_minimal"] != null) ? ((!lect["montant_minimal"].ToString().Trim().Equals("")) ? lect["montant_minimal"] : 0) : 0);
a.Maximal = (Double)((lect["montant_maximal"] != null) ? ((!lect["montant_maximal"].ToString().Trim().Equals("")) ? lect["montant_maximal"] : 0) : 0);
a.Montant = (Double)((lect["montant_ristourne"] != null) ? ((!lect["montant_ristourne"].ToString().Trim().Equals("")) ? lect["montant_ristourne"] : 0) : 0);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
builder.Append('X', Conn.BufferSize);
var expected = builder.ToString();
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
cmd.Parameters.Add(new NpgsqlParameter("p", expected));
cmd.ExecuteNonQuery();
const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
var actual = reader[0];
Assert.That(actual, Is.EqualTo(expected));
if (IsSequential(behavior))
Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
Assert.That(reader[0], Is.EqualTo(expected));
Assert.That(reader.GetString(1), Is.EqualTo("foo"));
Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
Assert.That(reader.GetValue(3), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
}
public static DataTable GetMenuTable(int parentMenuId, short level, int userId, int officeId, string culture)
{
if (userId.Equals(0))
{
return null;
}
if (officeId.Equals(0))
{
return null;
}
string sql = "SELECT * FROM policy.get_menu(@UserId, @OfficeId, @Culture) WHERE parent_menu_id is null ORDER BY menu_id;";
if (parentMenuId > 0)
{
sql = "SELECT * FROM policy.get_menu(@UserId, @OfficeId, @Culture) WHERE parent_menu_id=@ParentMenuId AND level=@Level ORDER BY menu_id;";
}
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@Culture", culture);
if (parentMenuId > 0)
{
command.Parameters.AddWithValue("@ParentMenuId", parentMenuId);
command.Parameters.AddWithValue("@Level", level);
}
return DbOperations.GetDataTable(command);
}
}
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 = "";
}
}
/// <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));
}
}
}
public static TestPerson BuildPersonFromDataBaseData(string idPerson, string MIS)
{
using (NpgsqlConnection connection = Global.GetSqlConnection())
{
string findPatient = "SELECT * FROM public.person WHERE id_person = '" + idPerson + "'";
NpgsqlCommand person = new NpgsqlCommand(findPatient, connection);
using (NpgsqlDataReader personFromDataBase = person.ExecuteReader())
{
Person p = new Person();
while (personFromDataBase.Read())
{
//что делать с DateSpecified и Мисами?
if (personFromDataBase["birthday"]!= DBNull.Value)
p.BirthDate = Convert.ToDateTime(personFromDataBase["birthday"]);
if (MIS == "")
p.IdPatientMis = null;
if ((personFromDataBase["family_name"] != DBNull.Value) || (personFromDataBase["given_name"] != DBNull.Value) || (personFromDataBase["middle_name"] != DBNull.Value))
{
p.HumanName = new HumanName();
if (personFromDataBase["family_name"] != DBNull.Value)
p.HumanName.FamilyName = Convert.ToString(personFromDataBase["family_name"]);
if (personFromDataBase["given_name"] != DBNull.Value)
p.HumanName.GivenName = Convert.ToString(personFromDataBase["given_name"]);
if (personFromDataBase["middle_name"] != DBNull.Value)
p.HumanName.MiddleName = Convert.ToString(personFromDataBase["middle_name"]);
}
TestPerson pers = new TestPerson(p);
if (personFromDataBase["id_sex"] != DBNull.Value)
pers.sex = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_sex"]));
return pers;
}
}
}
return null;
}
public Registration Get(Guid id)
{
var registrationEvents = new List <IRegistrationEvent>();
using (var sqlConnection = new NpgsqlConnection(ConnectionString))
{
sqlConnection.Open();
var command = "SELECT * FROM schooled.Registration WHERE id = @id ORDER BY timestamp";
using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection))
{
sqlCommand.Parameters.AddWithValue("id", NpgsqlDbType.Uuid, id);
using (var reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var eventType = Convert.ToString(reader["event_type"]);
var theEvent =
(IRegistrationEvent)JsonConvert.DeserializeObject(
Convert.ToString(reader["event"]),
RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key);
registrationEvents.Add(theEvent);
}
}
}
}
return(registrationEvents.Any() ? new Registration(id, registrationEvents) : null);
}
public static long PostTransaction(string catalog, long transactionMasterId, DateTime valueDate, int officeId, int userId, long loginId, int storeId, string partyCode, int priceTypeId, string referenceNumber, string statementReference, Collection<StockDetail> details, Collection<Attachment> attachments)
{
string detail = StockMasterDetailHelper.CreateStockMasterDetailParameter(details);
string attachment = AttachmentHelper.CreateAttachmentModelParameter(attachments);
string sql = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM transactions.post_purchase_return(@TransactionMasterId::bigint, @OfficeId::integer, @UserId::integer, @LoginId::bigint, @ValueDate::date, @StoreId::integer, @PartyCode::national character varying(12), @PriceTypeId::integer, @ReferenceNumber::national character varying(24), @StatementReference::text, ARRAY[{0}], ARRAY[{1}]);", detail, attachment);
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
command.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@LoginId", loginId);
command.Parameters.AddWithValue("@ValueDate", valueDate);
command.Parameters.AddWithValue("@StoreId", storeId);
command.Parameters.AddWithValue("@PartyCode", partyCode);
command.Parameters.AddWithValue("@PriceTypeId", priceTypeId);
command.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
command.Parameters.AddWithValue("@StatementReference", statementReference);
command.Parameters.AddRange(StockMasterDetailHelper.AddStockMasterDetailParameter(details).ToArray());
command.Parameters.AddRange(AttachmentHelper.AddAttachmentParameter(attachments).ToArray());
long tranId = Conversion.TryCastLong(DbOperation.GetScalarValue(catalog, command));
return tranId;
}
}
public static List<PlanCommission> getListPlanCommission(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<PlanCommission> l = new List<PlanCommission>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
PlanCommission a = new PlanCommission();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Nature = lect["nature_commission"].ToString();
a.Reference = lect["reference"].ToString();
a.Commissions = BLL.CommissionBll.Liste("select * from yvs_com_commission where plan = " + a.Id);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
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 List <Models.TestLevels.TestStructures> GetTopStructures()
{
List <Models.TestLevels.TestStructures> list = new List <Models.TestLevels.TestStructures>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = '00000000-0000-0000-0000-000000000000' order by name;", con);
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestLevels.TestStructures item = new Models.TestLevels.TestStructures();
item.id = new Guid(Convert.ToString(DR["row_idnumber"]));
item.Name = Convert.ToString(DR["name"]);
item.ShortName = Convert.ToString(DR["srtname"]);
item.Parent_IDNumber = new Guid(Convert.ToString(DR["parent_idnumber"]));
list.Add(item);
}
return(list);
}
public List <Models.Application> GetApplications()
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = @NoParent order by name;", con);
cmd.Parameters.Add("@NoParent", NpgsqlTypes.NpgsqlDbType.Uuid).Value = new Guid();
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
List <Models.Application> LI = new List <Models.Application>();
foreach (DataRow I in ds.Tables[0].Rows)
{
Models.Application application = new Models.Application();
application.id = new Guid(Convert.ToString(I["row_idnumber"]));
application.Name = Convert.ToString(I["name"]);
application.ShortName = Convert.ToString(I["srtname"]);
LI.Add(application);
}
return(LI);
}
//Build changelog responsefile
public override void MakeChangeLog(int startChangeId, int count, string dbConnectInfo, string wfsUrl, string changeLogFileName, int datasetId)
{
Logger.Info("MakeChangeLog START");
try
{
if (OptimizedChangeLog.Count == 0)
{
//Connect to postgres database
Npgsql.NpgsqlConnection conn = null;
conn = new NpgsqlConnection(dbConnectInfo);
conn.Open();
//Get max changelogid
Int64 endChangeId = GetMaxChangeLogId(conn, datasetId);
//Prepare query against the changelog table in postgres
Npgsql.NpgsqlCommand command = null;
PrepareChangeLogQuery(conn, ref command, startChangeId, endChangeId, datasetId);
//Execute query against the changelog table and remove unnecessary transactions.
FillOptimizedChangeLog(ref command, ref OptimizedChangeLog, startChangeId);
conn.Close();
}
//Get features from WFS and add transactions to changelogfile
BuildChangeLogFile(count, wfsUrl, startChangeId, changeLogFileName, datasetId);
}
catch (System.Exception exp)
{
Logger.Error(exp, "MakeChangeLog function failed:");
throw new System.Exception("MakeChangeLog function failed", exp);
}
Logger.Info("MakeChangeLog END");
}
public List <Models.Automation.Test_Run_Notes> GetRunNote(Guid ID)
{
List <Models.Automation.Test_Run_Notes> list = new List <Models.Automation.Test_Run_Notes>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from automation.runnotes where row_idnum = @ID;", con);
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.Automation.Test_Run_Notes item = new Models.Automation.Test_Run_Notes
{
idnum = new Guid(Convert.ToString(DR["row_idnum"])),
run_idnum = new Guid(Convert.ToString(DR["run_idnum"])),
testset_idnum = new Guid(Convert.ToString(DR["testset_idnum"])),
test_idnum = new Guid(Convert.ToString(DR["test_idnum"])),
id = Convert.ToInt32(DR["id"]),
notes = Convert.ToString(DR["notes"]),
method_name = Convert.ToString(DR["method_name"]),
Status = Convert.ToInt32(DR["status"])
};
list.Add(item);
}
return(list);
}
public Guid Maintain_RunNotes(Models.Automation.Test_Run_Notes item, string username)
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"SELECT automation.maintain_runnote(@IdNum, @run_IdNum, @testset_idnum,@test_IdNum, @ID,@method_name, @notes, @username, @status);", con);
cmd.Parameters.Add("@IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.idnum;
cmd.Parameters.Add("@run_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.run_idnum;
cmd.Parameters.Add("@testset_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.testset_idnum;
cmd.Parameters.Add("@test_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.test_idnum;
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.id;
cmd.Parameters.Add("@method_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.method_name ?? "";
cmd.Parameters.Add("@notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? "";
cmd.Parameters.Add("@username", NpgsqlTypes.NpgsqlDbType.Text).Value = username;
cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.Status;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_runnote"]));
return(IDNUM);
}
//Adição na tabela associativa, recebendo o ID do filme e do diretor respectivamente.
public void Add(int id_filme, int id_diretor)
{
try
{
bd.OpenConnection();
String query = "INSERT INTO tab_filme_diretor VALUES (:cod_filme, :cod_diretor)";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = id_filme;
sql.Parameters[1].Value = id_diretor;
int linhasAfetadas = sql.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
}
finally
{
bd.CloseConnection();
}
}
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 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();
}
}
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_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 List<OrganizationType> QueryAllOnOrganizationType()
{
try
{
this.OpenConn();
string SQL = "SELECT * FROM Organization_Type";
NpgsqlCommand command = new NpgsqlCommand(SQL, conn);
NpgsqlDataReader dr = command.ExecuteReader();
List<OrganizationType> orgTypes = new List<OrganizationType>();
while (dr.Read())
{
OrganizationType type = new OrganizationType();
type.Name = dr["name"].ToString();
type.Description = dr["description"].ToString();
orgTypes.Add(type);
}
this.CloseConn();
return orgTypes;
}
catch (Exception ne)
{
Console.WriteLine("error on query table connecting to server, Error details {0}", ne.ToString());
return null;
}
}
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_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 bool Update()
{
string sQuery = "UPDATE tbm_carrepair SET " +
"carid=@cid,dlt=@dlt,opedit=@opedit,pcedit=@pcedit,luedit=now()" +
"WHERE repairid = @rid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
if (m_repairid != null)
{
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid;
}
else
{
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (m_carid != null)
{
cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = carid;
}
else
{
cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (opedit != null)
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit;
}
else
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (pcedit != null)
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit;
}
else
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (luedit != null && luedit != DateTime.MinValue)
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit;
}
else
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value;
}
cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt;
cmd.CommandText = sQuery;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
// 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 string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
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 static bool SendPasswordRecoveryMail(EmailInfo eInfo)
{
bool success = false;
DataTable dt = new DataTable();
IDatabaseFactory dbFactory = new DatabaseFactory();
IDatabase dbObject = dbFactory.CreateDatabaseInstance(ACTIVE_DATABASE);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SQLClass.GET_FORGETUSERPASSWORD);
command.Parameters.Add("email", eInfo.Email.ToLower());
dt = dbObject.GetDataTable(command);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
eInfo.Email = dr["email"].ToString();
eInfo.Password = dr["webshop_pwd"].ToString();
eInfo.CompanyName = dr["companytype"].ToString();
}
string adminMail = Hey.Common.Utils.Functions.GetValueFromWebConfig("hey-admin-mail");
try
{
success = SendPasswordRecoveryMailToUser(eInfo, adminMail);
}
catch (Exception ex)
{
throw new Exception("The email is unknown to the server.");
}
}
else
{
success = false;
}
return(success);
}
public void ParametersGetName()
{
var command = new NpgsqlCommand();
// Add parameters.
command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean));
command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32));
command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime));
command.Parameters.Add(new NpgsqlParameter("Parameter4", DbType.DateTime));
var idbPrmtr = command.Parameters["Parameter1"];
Assert.IsNotNull(idbPrmtr);
command.Parameters[0].Value = 1;
// Get by indexers.
Assert.AreEqual(":Parameter1", command.Parameters[":Parameter1"].ParameterName);
Assert.AreEqual(":Parameter2", command.Parameters[":Parameter2"].ParameterName);
Assert.AreEqual(":Parameter3", command.Parameters[":Parameter3"].ParameterName);
//Assert.AreEqual(":Parameter4", command.Parameters["Parameter4"].ParameterName); //Should this work?
Assert.AreEqual(":Parameter1", command.Parameters[0].ParameterName);
Assert.AreEqual(":Parameter2", command.Parameters[1].ParameterName);
Assert.AreEqual(":Parameter3", command.Parameters[2].ParameterName);
Assert.AreEqual("Parameter4", command.Parameters[3].ParameterName);
}
/// <param name="catalog">The name of the database on which queries are being executed to.</param>
public static IEnumerable<DisplayField> GetDisplayFields(string catalog)
{
List<DisplayField> displayFields = new List<DisplayField>();
const string sql = "SELECT employee_type_id AS key, employee_type_code || ' (' || employee_type_name || ')' as value FROM hrm.employee_types;";
using (NpgsqlCommand command = new NpgsqlCommand(sql))
{
using (DataTable table = DbOperation.GetDataTable(catalog, command))
{
if (table?.Rows == null || table.Rows.Count == 0)
{
return displayFields;
}
foreach (DataRow row in table.Rows)
{
if (row != null)
{
DisplayField displayField = new DisplayField
{
Key = row["key"].ToString(),
Value = row["value"].ToString()
};
displayFields.Add(displayField);
}
}
}
}
return displayFields;
}
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 static bool ExecuteNonQuery(string catalog, NpgsqlCommand command)
{
try
{
if (command != null)
{
if (ValidateCommand(command))
{
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
return true;
}
}
}
return false;
}
catch (NpgsqlException ex)
{
if (ex.Code.StartsWith("P"))
{
string errorMessage = GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
throw;
}
}
public static List <MaterialGroupFilter> GetNumberOfFilterCombos(string code)
{
List <MaterialGroupFilter> ListOfFilters = new List <MaterialGroupFilter>();
MaterialGroupFilter FilterObject;
DataTable dt = new DataTable();
IDatabaseFactory dbFactory = new DatabaseFactory();
IDatabase dbObject = dbFactory.CreateDatabaseInstance(ACTIVE_DATABASE);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SQLClass.GET_NUMBEROFFIILTERCOMBO);
command.Parameters.Add("group_code", code.ToUpper());
dt = dbObject.GetDataTable(command);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
FilterObject = new MaterialGroupFilter();
FilterObject.FilterId = int.Parse(dr["filter_id"].ToString());
FilterObject.FilterLabel = dr["description"].ToString();
FilterObject.FieldName = dr["field_name"].ToString();
FilterObject.TableName = dr["table_name"].ToString();
FilterObject.ListPosition = int.Parse(dr["list_position"].ToString());
ListOfFilters.Add(FilterObject);
}
}
return(ListOfFilters);
}
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 static TestReferralSurvey BuildAdditionalFromDataBaseData(string idReferral)
{
using (NpgsqlConnection connection = Global.GetSqlConnection())
{
string findPatient = "SELECT id_survey_organ, survey_comment, id_survey_type FROM public.referral WHERE id_referral = '" + idReferral + "' ORDER BY id_referral DESC LIMIT 1";
NpgsqlCommand person = new NpgsqlCommand(findPatient, connection);
using (NpgsqlDataReader personFromDataBase = person.ExecuteReader())
{
Survey p = new Survey();
while (personFromDataBase.Read())
{
//что делать с DateSpecified и Мисами?
if (personFromDataBase["survey_comment"] != DBNull.Value)
p.Comment = Convert.ToString(personFromDataBase["survey_comment"]);
TestReferralSurvey pers = new TestReferralSurvey(p);
pers.additional = TestAdditional.BuildAdditionalFromDataBaseData(idReferral);
if (personFromDataBase["id_survey_organ"] != DBNull.Value)
pers.surveyOrgan = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_organ"]));
if (personFromDataBase["id_survey_type"] != DBNull.Value)
pers.surveyType = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_type"]));
return pers;
}
}
}
return null;
}
//Insert no banco recebendo como parametro um Filme.
public void Add(Filme f)
{
try
{
bd.OpenConnection();
String query = "INSERT INTO tab_filme VALUES (:cod_filme, :nome_filme, :data_filme, :cod_categoria)";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Parameters.Add(new NpgsqlParameter("nome_filme", NpgsqlTypes.NpgsqlDbType.Varchar));
sql.Parameters.Add(new NpgsqlParameter("data_filme", NpgsqlTypes.NpgsqlDbType.Date));
sql.Parameters.Add(new NpgsqlParameter("cod_categoria", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = f.Cod_filme;
sql.Parameters[1].Value = f.Nome_filme;
sql.Parameters[2].Value = f.Data;
sql.Parameters[3].Value = f.Categoria.Cod_categoria;
int linhasAfetadas = sql.ExecuteNonQuery();
if (Convert.ToBoolean(linhasAfetadas))
{
Console.WriteLine("Filme adicionado com sucesso!");
}
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
}
finally
{
bd.CloseConnection();
}
}
//När användaren kilckar på "OK" sker följande:
private void OK_button_Click(object sender, EventArgs e)
{
//Deklarerar variabel klass
var klass = "";
//Om "A" är iklickat av användaren får variabel klass värdet A.
if (A_radioButton.Checked == true)
{
klass = "A";
}
//Om "B" är iklickat av användaren får variabel klass värdet B.
else if (B_radioButton.Checked == true)
{
klass = "B";
}
//Om "C" är iklickat av användaren får variabel klass värdet C.
else if (C_radioButton.Checked == true)
{
klass = "C";
}
//Skapar strängen anmaldeltagare.
//Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari.
string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')";
//Skapar ett nytt Npgsql-kommando, command1.
NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn);
//Utför kommando, command1.
command1.ExecuteNonQuery();
//När allt ovan är utfört visas en meddelanderuta.
MessageBox.Show("Spelare är nu anmäld till tävling!");
//Sedan stängs hela detta form, AnmälDeltagare.
this.Close();
}
public IEnumerable <EventStoreItem> GetAll()
{
var entities = new List <EventStoreItem>();
using (var sqlConnection = new NpgsqlConnection(ConnectionString))
{
sqlConnection.Open();
var command = "SELECT db_id, id, event_type, event, timestamp FROM schooled.Registration";
using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection))
{
using (var reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
var eventType = Convert.ToString(reader["event_type"]);
var entity =
new EventStoreItem
{
Id = Guid.Parse(Convert.ToString(reader["id"])),
EventType = eventType,
TimeStamp = DateTimeOffset.Parse(Convert.ToString(reader["timestamp"])),
Event =
(IRegistrationEvent)JsonConvert.DeserializeObject(
Convert.ToString(reader["event"]),
RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key)
};
entities.Add(entity);
}
}
}
}
return(entities);
}
private PostgreSQLDBManager()
{
_connection = new NpgsqlConnection(Configuration.Settings("PostgreSQLDBConnectionString",
"User ID=postgres;Password=Passw0rd;Host=localhost;Port=5432;Database=postgres;Pooling=true;"));
try
{
_connection.Open();
}
catch (Exception exception)
{
Log.Error(ExceptionHelper.FormatStackTrace("Connect to PostgreSQL failed.",exception));
throw new RuntimeBinderException("Connect to Database Failed",exception);
}
var cmd = new NpgsqlCommand("select count(*) from content", _connection);
var count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
return;
var rootId = Configuration.Settings("Root", "42c5eb51-0e1c-4de1-976d-733bde24220a");
CreateSubItem(rootId, "Folder", "Data");
CreateSubItem(rootId, "Folder", "UI");
CreateSubItem(rootId, "Folder", "Translation");
CreateSubItem(rootId, "Folder", "Project");
CreateSubItem(rootId, "Folder", "Result");
CreateSubItem("", "Project", "Root", rootId);
}
public 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();
}
}
private static void Run()
{
var connStr = ConfigurationManager.AppSettings["ConnectionString"];
using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
{
conn.Open();
updateConn.Open();
// Define a query returning a single row result set
using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
{
using (NpgsqlDataReader dr = command.ExecuteReader())
{
// Output rows
while (dr.Read())
{
CreateThumbnail((string)dr[1], (long)dr[2]);
using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
{
update.ExecuteNonQuery();
}
}
}
}
}
}
public void 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 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 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);
}
private void PrepareChangeLogQuery(Npgsql.NpgsqlConnection conn, ref Npgsql.NpgsqlCommand command, int startChangeId, Int64 endChangeId, int datasetId)
{
Logger.Info("PrepareChangeLogQuery START");
try
{
//20121021-Leg: Correction "endringsid >= :startChangeId"
//20121031-Leg: rad is now lokalId
string sqlSelectGmlIds = "SELECT tabell || '.' || lokalid, type, endringsid FROM " + PDbSchema + ".endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid";
//string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid";
// string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid > :startChangeId AND endringsid <= :endChangeId ";
command = new NpgsqlCommand(sqlSelectGmlIds, conn);
command.Parameters.Add(new NpgsqlParameter("startChangeId", NpgsqlDbType.Integer));
command.Parameters.Add(new NpgsqlParameter("endChangeId", NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = startChangeId;
command.Parameters[1].Value = endChangeId;
}
catch (System.Exception exp)
{
Logger.ErrorException("PrepareChangeLogQuery function failed:", exp);
throw new System.Exception("PrepareChangeLogQuery function failed", exp);
}
Logger.Info("PrepareChangeLogQuery END");
}
/// <summary>
/// Get Message by Container Number
/// </summary>
/// <param name="containerNumber"></param>
/// <returns></returns>
public string GetMessageByContainerNumber(string containerNumber)
{
if (string.IsNullOrEmpty(containerNumber))
{
return(string.Empty);
}
string message = string.Empty;
try
{
using (NpgsqlConnection npgsqlConnection = AppConfig.GetConnection())
{
if (npgsqlConnection.State == ConnectionState.Closed)
{
npgsqlConnection.Open();
}
string query = string.Format("SELECT message FROM {0} WHERE cont = @ContainerNumber and disableduntil <= now()", DEFAULT_TABLE);
using (NpgsqlCommand npgsqlCommand = new Npgsql.NpgsqlCommand(query, npgsqlConnection))
{
npgsqlCommand.Parameters.AddWithValue("@ContainerNumber", containerNumber);
object result = npgsqlCommand.ExecuteScalar();
if (result != null)
{
message = result.ToString();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(message);
}
public void Append(string name, byte[] data, long expectedVersion)
{
using (var conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx);
const string txt =
@"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data)
VALUES(:customerId, :name, :version, :data)";
using (var cmd = new NpgsqlCommand(txt, conn, tx))
{
cmd.Parameters.AddWithValue(":name", name);
cmd.Parameters.AddWithValue(":version", version+1);
cmd.Parameters.AddWithValue(":data", data);
cmd.Parameters.AddWithValue(":customerId", customerId);
cmd.ExecuteNonQuery();
}
tx.Commit();
}
}
}
public string AccountCreation(string json)
{
string status = String.Empty;
try
{
using (NpgsqlConnection con = new NpgsqlConnection(_connectionString))
{
con.Open();
var command = new Npgsql.NpgsqlCommand("_bt_account_create", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "input";
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
parameter.Value = json;
command.Parameters.Add(parameter);
int output = command.ExecuteNonQuery();
status = "ok";
}
}
catch (Exception ex)
{
status = ex.Message;
}
return(status);
}
public void Roundtrip()
{
const string expected = "Something";
var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn);
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Text);
var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Varchar);
var p3 = new NpgsqlParameter("p3", DbType.String);
var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected };
Assert.That(p2.DbType, Is.EqualTo(DbType.String));
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
Assert.That(p3.DbType, Is.EqualTo(DbType.String));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
p1.Value = p2.Value = p3.Value = expected;
var reader = cmd.ExecuteReader();
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(string)));
Assert.That(reader.GetString(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(i), Is.EqualTo(expected));
Assert.That(reader.GetValue(i), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(i), Is.EqualTo(expected.ToCharArray()));
}
reader.Close();
cmd.Dispose();
}
//Insert no banco recebendo como parametro um Diretor.
public void Add(Diretor d)
{
try
{
bd.OpenConnection();
String query = "INSERT INTO tab_diretor VALUES (:cod_diretor, :nome_diretor)";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Parameters.Add(new NpgsqlParameter("nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar));
sql.Prepare();
sql.Parameters[0].Value = d.Cod_diretor;
sql.Parameters[1].Value = d.Nome_diretor;
int linhasAfetadas = sql.ExecuteNonQuery();
if (Convert.ToBoolean(linhasAfetadas))
{
Console.WriteLine("Diretor adicionado com sucesso!");
}
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
}
finally
{
bd.CloseConnection();
}
}
public void Create(IEnumerable<string> columnDefinitions)
{
var sb = new StringBuilder();
if(!string.IsNullOrEmpty(_schemaName))
sb.AppendFormat("CREATE SCHEMA \"{0}\";",_schemaName);
sb.Append("CREATE TABLE ");
sb.Append(NameWithSchema);
sb.Append(" (");
foreach (string definition in columnDefinitions)
{
sb.Append(definition);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
var s = sb.ToString();
using (var command = new NpgsqlCommand(s, Connection, Transaction))
command.ExecuteNonQuery();
}
//Mostra todos os registro do banco diretamente, sem fazer cast para objeto.
public void ShowAll()
{
try
{
bd.OpenConnection();
String query = "SELECT * FROM tab_diretor";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Prepare();
NpgsqlDataReader dr = sql.ExecuteReader();
while (dr.Read())
{
//Listar todos os campos automatizado
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write("{0} ", dr[i].ToString());
}
Console.Write("\n");
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
if ((isolation != IsolationLevel.ReadCommitted) &&
(isolation != IsolationLevel.Serializable))
throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation");
_conn = conn;
_isolation = isolation;
StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL ");
if (isolation == IsolationLevel.ReadCommitted)
commandText.Append("READ COMMITTED");
else
commandText.Append("SERIALIZABLE");
commandText.Append("; BEGIN");
NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
command.ExecuteNonQuery();
_conn.Connector.Transaction = this;
}
//Atualização passando como paramentro o ID e o novo atributo do Diretor.
public void Update(int id, string new_nome_diretor)
{
try
{
bd.OpenConnection();
String query = "UPDATE tab_diretor SET nome_diretor = :new_nome_diretor WHERE cod_diretor = :id";
Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
sql.Parameters.Add(new NpgsqlParameter("new_nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar));
sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters[0].Value = new_nome_diretor;
sql.Parameters[1].Value = id;
int linhasAfetadas = sql.ExecuteNonQuery();
if (Convert.ToBoolean(linhasAfetadas))
{
Console.WriteLine("Diretor atualizado com sucesso!");
}
else
{
Console.WriteLine("Não existe esse diretor!");
}
}
catch (NpgsqlException e)
{
Console.WriteLine(e);
}
finally
{
bd.CloseConnection();
}
}
private void UpdateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("GolfID", typeof(string));
dt.Columns.Add("Förnamn", typeof(string));
dt.Columns.Add("Efternamn", typeof(string));
String sql = "SELECT golf_id, förnamn, efternamn FROM medlem;";
NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn);
NpgsqlDataReader ndr = command.ExecuteReader();
while (ndr.Read())
{
DataRow row = dt.NewRow();
row["GolfID"] = ndr["golf_id"];
row["Förnamn"] = ndr["förnamn"];
row["Efternamn"] = ndr["efternamn"];
dt.Rows.Add(row);
}
ndr.Close();
DataView dv = new DataView(dt);
//TODO Fix this filter, only golfId working
dv.RowFilter = "GolfID LIKE '" + golfId_textBox.Text + "*' AND Förnamn LIKE '" + firstName_textBox.Text + "*' AND Efternamn LIKE '" + lastName_textBox.Text + "*'";
//Set the component data
dataGridView.DataSource = dv;
//Set column header text
dataGridView.Columns[0].HeaderText = "Golf-ID";
dataGridView.Columns[1].HeaderText = "Förnamn";
dataGridView.Columns[2].HeaderText = "Efternamn";
}
public static List<Remise> getListRemise(String query)
{
NpgsqlConnection con = Connexion.Connection();
try
{
List<Remise> l = new List<Remise>();
NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
NpgsqlDataReader lect = Lcmd.ExecuteReader();
if (lect.HasRows)
{
while (lect.Read())
{
Remise a = new Remise();
a.Id = Convert.ToInt64(lect["id"].ToString());
a.Reference = lect["reference"].ToString();
a.BaseRemise = lect["base_remise"].ToString();
a.Permanent = Convert.ToBoolean((lect["permanent"] != null) ? (!lect["permanent"].ToString().Trim().Equals("") ? lect["permanent"].ToString().Trim() : "false") : "false");
a.Grilles = BLL.GrilleRemiseBll.Liste("SELECT * FROM yvs_com_grille_remise WHERE remise =" + a.Id);
a.Update = true;
l.Add(a);
}
lect.Close();
}
return l;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
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 void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.SelectedValue.ToString() == "Quartiers")
{
grd.ClearSelection();
cmd = new n.NpgsqlCommand("select code,nom,densite from quartier", cnx);
ada = new n.NpgsqlDataAdapter(cmd);
dt = new DataTable();
ada.Fill(dt);
grd.DataSource = dt;
}
else
{
if (comboBox1.SelectedValue.ToString() == "Antennes")
{
grd.ClearSelection();
cmd = new n.NpgsqlCommand("select num,nom,puissance from antenne", cnx);
ada = new n.NpgsqlDataAdapter(cmd);
dt = new DataTable();
ada.Fill(dt);
grd.DataSource = dt;
}
else
{
dt = new DataTable();
grd.DataSource = dt;
}
}
}