Close() public method

Releases the connection to the database. If the connection is pooled, it will be made available for re-use. If it is non-pooled, the actual connection will be shutdown.
public Close ( ) : void
return void
Esempio n. 1
2
        public static void getWords()
        {
            #region Npgsql
            NpgsqlConnection conn;
            conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
                Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting);

            try
            {
                conn.Open();
            }
            catch (NpgsqlException)
            {
                MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
            catch (System.IO.IOException)
            {
                MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
            #endregion

            string sql = "SELECT no, words1, words2, words3, operator, word_order FROM words ORDER BY word_order";

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            da.Fill(localDB, "words");
            if (localDB.Tables["words"].Rows.Count == 0)
            { MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            conn.Close();
        }
 protected void Button1_Click(object sender, EventArgs e)
 {
     string checks;
     string checkw;
     string id_sali = DropDownList1.SelectedItem.ToString();
     string id_wyp = DropDownList2.SelectedItem.ToString();
     NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
     conn.Open();
     NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
     NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
     checks = (String)check1.ExecuteScalar();
     checkw = (String)check2.ExecuteScalar();
     if (checks == id_sali && checkw == id_wyp)
     {
         Label2.Text = "";
         Label3.Text = "Wyposażenie jest już przypisane do sali!";
     }
     else if (checkw != id_wyp)
     {
         NpgsqlCommand add = new NpgsqlCommand("insert into wyp_sali values ('" + id_wyp + "', '" + id_sali + "')", conn);
         add.ExecuteScalar();
         conn.Close();
         Label2.Text = "Dodano wyposażenie do sali!";
         Label3.Text = "";
     }
 }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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();
        }
Esempio n. 5
0
        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";
        }
Esempio n. 6
0
 //создание формы
 public Form_Countries(string connStr)
 {
     InitializeComponent();
     this.connStr = connStr;
     this.conn = new NpgsqlConnection(connStr);
     //инициализация грида
     this.table = new DataTable();
     try
     {
         Class_Helper.SetColumns(table, dataGridView_Countries, columns);
     }
     catch (Exception ex)
     {
         throw new Exception("Can't init datagrid: " + ex.Message);
     }
     //получить ид России (сервер Б)
     try
     {
         string query = "SELECT id from sb.countries";
         NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
         conn.Open();
         label_ServerB.Text = ((int)cmd.ExecuteScalar()).ToString();
         conn.Close();
     }
     catch (Exception ex)
     {
         conn.Close();
         throw new Exception("Can't connect to B: " + ex.Message);
     }
 }
Esempio n. 7
0
        protected void CreateUser_Click(object sender, EventArgs e)
        {
            //ArrayList for emails
            ArrayList maillist = new ArrayList();

            // Specify connection options and open an connection
            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
                                    "Password=root;Database=project56;");

            //Open connection
            conn.Open();

            // Define query
            NpgsqlCommand cmd = new NpgsqlCommand("SELECT email FROM users", conn);

            // Execute query
            NpgsqlDataReader dr = cmd.ExecuteReader();

            //Get rows and place in ArrayList
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    maillist.Add(dr[i]);
                }
            }

            // Close connection
            conn.Close();

            //Check if email in TextBox equals one of the existing accounts
            for (int i = 0; i < maillist.Count; i++)
            {
                if (maillist[i].Equals(Email.Text))
                {
                    EmailUnique = false;
                    MessageBox.Show(Page, "Dit emailadres is al geregistreerd!");
                    break;
                }
            }

            if (EmailUnique)
            {
                //Insert Email and Password from TextBoxes
                conn.Open();

                NpgsqlCommand cmd1 = new NpgsqlCommand("INSERT INTO users(email, password, rank) VALUES (:email, :pw, 0)", conn);
                cmd1.Parameters.Add(new NpgsqlParameter("email", Email.Text));
                cmd1.Parameters.Add(new NpgsqlParameter("pw", Password.Text));

                cmd1.ExecuteNonQuery();

                conn.Close();

                MessageBox.Show(Page, "Uw account is geregistreerd!");

            }
        }
        public void readPtData(string patientID)
        {
            #region Npgsql
            NpgsqlConnection conn;
            try
            {
                conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
                    Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting);
            }
            catch (ArgumentException)
            {
                MessageBox.Show(Properties.Resources.WrongConnectingString, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            try
            { conn.Open(); }
            catch (NpgsqlException)
            {
                MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
                return;
            }
            catch (System.IO.IOException)
            {
                MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
                return;
            }
            #endregion

            string sql = "SELECT * FROM patient WHERE pt_id='" + patientID + "'";

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count == 0)
            {
                conn.Close();
                lbPtNameDB.Text = "No data";
                return;
            }
            else
            {
                DataRow row = dt.Rows[0];
                lbPtNameDB.Text = row["pt_name"].ToString();
                conn.Close();
            }
        }
Esempio n. 9
0
        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());
            });
        }
Esempio n. 10
0
        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();
            }
        }
Esempio n. 11
0
        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();
            }
        }
Esempio n. 12
0
        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);
        }
Esempio n. 13
0
        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);
            }
        }
Esempio n. 14
0
        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();
            }
        }
Esempio n. 15
0
 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();
     }
 }
Esempio n. 16
0
        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();
            }
        }
Esempio n. 17
0
        public void Incluir(Model_Vo_Sala pSala)
        {
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "insert into sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);";
                cmd.Parameters.AddWithValue("@nome", pSala.Nome);
                cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
                cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString());
                cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
                cn.Open();
                pSala.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
Esempio n. 18
0
        //for testing
        public void DeletePurchaserDataRecursive(long id)
        {
            Npgsql.NpgsqlConnection con = DB.DBConnector.GetConnection();

            NpgsqlCommand command = new NpgsqlCommand(null, con);

            command.CommandText = "DELETE FROM dienstverhaeltnis WHERE agid=@agid";

            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            command             = new NpgsqlCommand(null, con);
            command.CommandText = "DELETE FROM auftraggeberdaten WHERE agid=@agid";

            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            command.CommandText = "DELETE FROM auftraggeber WHERE agid=@agid";

            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            command.CommandText = "DELETE FROM person WHERE personid=@agid";

            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            con.Close();
        }
Esempio n. 19
0
        static void ClearCatalogue(string connString)
        {
            try
            {
                NpgsqlConnection conn = new NpgsqlConnection(connString);
                conn.Open();
                Console.Clear();

                string clearTable = "DELETE FROM phone_book";

                NpgsqlCommand cmd = new NpgsqlCommand(clearTable, conn);
                cmd.ExecuteNonQuery();

                Console.WriteLine(">>> Catalogue cleared");

                Console.ReadKey();
                Console.Clear();
                conn.Close();
            }
            catch (Exception msg)
            {
                Console.WriteLine(msg.ToString());
                throw;
            }
        }
        public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque)
        {
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "insert into movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);";
                cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false));
                cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante);
                cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto);
                cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem);
                cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade);
                cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario);
                cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal);
                cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString());
                cn.Open();
                pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            string check;
            string typ_sali = TextBox1.Text;
            string opis = TextBox2.Text;

            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            NpgsqlCommand check1 = new NpgsqlCommand("select typ_sali from typ_sali where typ_sali = '" + typ_sali + "'", conn);
            check = (String)check1.ExecuteScalar();
            if (check != typ_sali)
            {
                NpgsqlCommand add = new NpgsqlCommand("insert into typ_sali values ('" + typ_sali + "', '" + opis + "')", conn);
                add.ExecuteScalar();
                conn.Close();
                TextBox1.Text = "";
                TextBox2.Text = "";
                Label2.Text = "Dodano typ!";
                Label3.Text = "";
            }
            else if (check == typ_sali)
            {
                Label2.Text = "";
                Label3.Text = "Typ już istnieje!";
            }
        }
Esempio n. 22
0
        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;
        }
Esempio n. 23
0
    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 ();
    }
Esempio n. 24
0
        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);
        }
Esempio n. 25
0
        // having a connecting to DB function with the sqlstring as input
        private List<Team> ReadFromDB(string sqlstring)
        {
            List<Team> teams = new List<Team>();
            NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=reader;Password=hej123;Database=guessthegame;");
            try
            {
                //connecting to the database
                conn.Open();

                // defining the sql npgsql command
                NpgsqlCommand command = new NpgsqlCommand(sqlstring, conn);
                NpgsqlDataReader dr = command.ExecuteReader();

                Team tempTeam;

                //reading all the users.
                while (dr.Read())
                {
                    tempTeam = new Team(dr.GetInt32(0), dr.GetString(1));
                    teams.Add(tempTeam);
                }
            }
            catch (Exception msg)
            {
                //do something if exception
                System.Diagnostics.Debug.WriteLine(msg.ToString());
            }
            finally
            {
                conn.Close();
            }

            return teams;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            string rola;
            FormsIdentity id =
                (FormsIdentity)HttpContext.Current.User.Identity;
            FormsAuthenticationTicket bilet = id.Ticket;
            Label1.Text = "Zalogowany jako: " + User.Identity.Name;
            // Get the stored user-data, in this case, our roles
            rola = bilet.UserData;

            if (rola != "admins") Response.Redirect("index.aspx");

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            // quite complex sql statement
            string sql = "SELECT * FROM sala";
            // data adapter making request from our connection
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            // i always reset DataSet before i do
            // something with it.... i don't know why :-)
            ds.Reset();
            // filling DataSet with result from NpgsqlDataAdapter
            da.Fill(ds);
            // since it C# DataSet can handle multiple tables, we will select first
            dt = ds.Tables[0];
            // connect grid to DataTable
            GridView1.DataSource = dt;
            GridView1.DataBind();
            // since we only showing the result we don't need connection anymore
            conn.Close();
        }
Esempio n. 27
0
        public static List<Forestallning> HamtaForestallningLista()
        {
            List<Forestallning> forestallningslista = new List<Forestallning>();
            NpgsqlConnection conn = new NpgsqlConnection("Server=webblabb.miun.se;Port=5432;Database=pgmvaru_g4;User Id=pgmvaru_g4;Password=trapets;ssl=true");
            conn.Open();
            NpgsqlCommand command = new NpgsqlCommand("Select * from forestallning ORDER BY namn, datum, starttid ASC", conn);
            NpgsqlDataReader dr = command.ExecuteReader();

            while (dr.Read())
            {

                Forestallning forestallning = new Forestallning();

                forestallning.id = Convert.ToInt32(dr["id"]);
                forestallning.namn = (string)dr["namn"];
                forestallning.generellinfo = (string)dr["generell_info"];
                forestallning.open = (bool)dr["open"];
                forestallning.datum = Convert.ToDateTime(dr["datum"]);
                forestallning.starttid = Convert.ToDateTime(dr["starttid"]);
                forestallning.sluttid = Convert.ToDateTime(dr["sluttid"]);
                forestallning.vuxenpris = Convert.ToInt32(dr["vuxenpris"]);
                forestallning.ungdomspris = Convert.ToInt32(dr["ungdomspris"]);
                forestallning.barnpris = Convert.ToInt32(dr["barnpris"]);
                forestallning.forsaljningsslut = Convert.ToDateTime(dr["forsaljningslut"]);

                forestallningslista.Add(forestallning);
            }
            conn.Close();
            return forestallningslista;
        }
Esempio n. 28
0
        public string getLevel(string userID)
        {
            string retLevel = "";
            string getUsers;

            try
            {
                NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
                getUsers = "SELECT * FROM users INNER JOIN team ON users.id = team.user_id WHERE users.id = @uId";
               // string sql = "SELECT *  FROM users RIGHT JOIN team on users.id = team.user_id WHERE user.id='" + userID + "'";
                NpgsqlCommand cmd = new NpgsqlCommand(getUsers, conn);
                cmd.Parameters.AddWithValue("uId", Convert.ToInt32(userID));
                conn.Open();
                NpgsqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    retLevel = "provledare";
                }
                else
                {
                    retLevel = "deltagare";
                }
                conn.Close();
            }
            catch (NpgsqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
            return retLevel;
        }
Esempio n. 29
0
 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();
     }
 }
Esempio n. 30
0
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {

                string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" +
                    "User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true";

                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();

                string sql = "SELECT c.name as name1, a.movie, a.id id, b.all, trunc(100*a.movie/b.all,6) procent from genre c, (select count(*) as all from movie_genre) b, (select count(movie_id) as movie, genre_id as id from movie_genre group by genre_id) a where c.id = a.id";

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);

                da.Fill(ds, "movie_genre");

                dt = ds.Tables[0];

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
                    {
                        Category = row["name1"].ToString(),
                        Data = Convert.ToDecimal(row["procent"])
                    });
                }

                conn.Close();
            }
            catch (Exception msg)
            {

            }
        }
 public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque)
 {
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         //command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandText = "insert into movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);";
         cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false));
         cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante);
         cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto);
         cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem);
         cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade);
         cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario);
         cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal);
         cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString());
         cn.Open();
         pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }
        protected void Button1_Click(object sender, EventArgs e)
        {
            string check;
            string id_wyp = TextBox1.Text;
            string nazwa = TextBox2.Text;
            string opis = TextBox3.Text;

            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            NpgsqlCommand check1 = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
            check = (String)check1.ExecuteScalar();
            if (check != id_wyp)
            {
                NpgsqlCommand add = new NpgsqlCommand("insert into wyposazenie values ('" + id_wyp + "', '" + nazwa + "', '" + opis + "')", conn);
                add.ExecuteScalar();
                conn.Close();
                TextBox1.Text = "";
                TextBox2.Text = "";
                TextBox3.Text = "";
                Label2.Text = "Dodano wyposażenie!";
                Label3.Text = "";
            }
            else if (check == id_wyp)
            {
                Label2.Text = "";
                Label3.Text = "Wyposażenie już istnieje!";
            }
        }
Esempio n. 33
0
        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();
            }
        }
Esempio n. 34
0
        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();
            }
        }
Esempio n. 35
0
        public void FecharContasAReceber(Model_Vo_ContasAReceber pContasAReceber)
        {
            // conexao
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update contasareceber set recebido = @recebido  where idreservaorigem = @idreservaorigem;";
                cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
                cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
 /// <summary>
 /// Inherited Abstract method that now overriden in this class to enable authentication of a user.
 /// </summary>
 public override void execute_action()
 {
     /// Instantiate a connection object to our PostGreSQL server
     Npgsql.NpgsqlConnection  conn = new Npgsql.NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ProudSourceDB"].ConnectionString);
     Npgsql.NpgsqlDataAdapter da   = new NpgsqlDataAdapter(this.query, conn);
     /// prepare the data that will be compared against
     da.SelectCommand.Parameters.AddWithValue("@UserName", this.Username);
     da.SelectCommand.Parameters.AddWithValue("Password", this.PasswordHash);
     System.Data.DataSet ds = new System.Data.DataSet();
     try
     {
         conn.Open();
         da.Fill(ds);
     }
     catch (Exception e)
     {
         System.Diagnostics.Debug.WriteLine(String.Format("Error querying data base, user authentication. Error Data \r\n{0},\r\n{1}\r\n{2}", e.Message, e.InnerException, e.Data));
         UserAuthenticated = false;
     }
     finally
     {
         conn.Close();
     }
     /// Check for success or failure
     if (ds.Tables[0].Rows.Count == 1)
     {
         this.UserAuthenticated = true;
     }
     else
     {
         this.UserAuthenticated = true;
     }
 }
Esempio n. 37
0
  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();
        }
    }
Esempio n. 38
0
        public Boolean Excluir(int id)
        {
            //conexao
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "delete from produto where id = " + Convert.ToString(id);
                cn.Open();
                int resultado = cmd.ExecuteNonQuery();
                if (resultado != 1)
                {
                    throw new Exception("Não foi possível excluir a produto " + Convert.ToString(id));
                }
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }

            return(true);
        }
        /// <summary>
        /// 将数据读取到 DataSet 中.
        /// </summary>
        public void ReadDataToDataSet()
        {

            Console.WriteLine("使用DataAdapter,将数据填充到DataSet中,然后脱离数据库,直接对DataSet进行处理。");

            // 建立数据库连接.
            NpgsqlConnection conn = new NpgsqlConnection(connString);

            // 创建一个适配器
            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(SQL, conn);

            // 创建DataSet,用于存储数据.
            DataSet testDataSet = new DataSet();

            // 执行查询,并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");

            // 关闭数据库连接.
            conn.Close();

            // 处理DataSet中的每一行数据.
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                // 将检索出来的数据,输出到屏幕上.
                Console.WriteLine("NO:{0} ;  Date:{1} ; Money:{2}   ",
                    testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
                    );
            }
        }
Esempio n. 40
0
        public static List<Akt> HamtaAktLista(int valdforestallningsid)
        {
            List<Akt> aktlista = new List<Akt>();
            NpgsqlConnection conn = new NpgsqlConnection("Server=webblabb.miun.se;Port=5432;Database=pgmvaru_g4;User Id=pgmvaru_g4;Password=trapets;ssl=true");
            conn.Open();
            string sql1 = @"SELECT * FROM akter , forestallning WHERE akter.forestallningsid = forestallning.id
                                                 and akter.forestallningsid = :nyValdforestallningsid ORDER BY akter.starttid ASC";

            NpgsqlCommand command = new NpgsqlCommand(sql1, conn);

            command.Parameters.Add(new NpgsqlParameter("nyValdforestallningsid", DbType.Int32));
            command.Parameters[0].Value = valdforestallningsid;

            NpgsqlDataReader dr = command.ExecuteReader();

            while (dr.Read())
            {
                Akt akten = new Akt();

                akten.namn = (string)dr["aktnamn"];
                akten.Aktinfo = (string)dr["aktinfo"];
                akten.Starttid = Convert.ToDateTime(dr["starttid"]);
                akten.Sluttid = Convert.ToDateTime(dr["sluttid"]);
                akten.vuxen = Convert.ToInt32(dr["vuxenpris"]);
                akten.ungdom = Convert.ToInt32(dr["ungdomspris"]);
                akten.barn = Convert.ToInt32(dr["barnpris"]);
                akten.id = Convert.ToInt32(dr["id"]);

                aktlista.Add(akten);
            }
            conn.Close();
            return aktlista;
        }
Esempio n. 41
0
        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
                }
            }
        }
Esempio n. 42
0
        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();
        }
Esempio n. 43
0
 public void Incluir(Model_Vo_Sala pSala)
 {
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         //command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandText = "insert into sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);";
         cmd.Parameters.AddWithValue("@nome", pSala.Nome);
         cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
         cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString());
         cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
         cn.Open();
         pSala.Id = Convert.ToInt32(cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }
Esempio n. 44
0
 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 "";
 }
Esempio n. 45
0
 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();
     }
 }
Esempio n. 46
0
 public static bool Connection_Test(ENTITE.Serveur bean)
 {
     if (bean.Control_())
     {
         NpgsqlConnection con = new NpgsqlConnection();
         try
         {
             string constr = "PORT=" + bean.getPort + ";TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE= 2.0.14.3;DATABASE=" + bean.getDatabase + ";HOST=" + bean.getAdresse + ";PASSWORD="******";USER ID=" + bean.getUser + "";
             con = new NpgsqlConnection(constr);
             con.Open();
             return true;
         }
         catch (NpgsqlException ex)
         {
             Messages.Exception(ex);
             return false;
         }
         finally
         {
             con.Close();
             con.Dispose();
         }
     }
     return false;
 }
        public bool AutenticarUsuario(string usuario, string senha)
        {
            string sql = "select usernumber from usertable where username = @usuario and userpassword = @senha";
            List<Dominio.Submissao> submissoes = new List<Dominio.Submissao>();

            NpgsqlConnection conexao = new NpgsqlConnection("Server=187.45.196.224;Database=bubblesort9;User ID=bubblesort9;Password=BSboca;");

            try
            {
                conexao.Open();
                NpgsqlCommand comando = new NpgsqlCommand(sql, conexao);
                comando.Parameters.Add("usuario", NpgsqlTypes.NpgsqlDbType.Varchar).Value = usuario;
                comando.Parameters.Add("senha", NpgsqlTypes.NpgsqlDbType.Varchar).Value = GetMD5Hash(senha);
                var reader = comando.ExecuteReader();
                bool resposta;
                if (reader.HasRows)
                {
                    resposta = true;
                }
                else
                    resposta = false;
                reader.Close();
                return resposta;
            }
            finally
            {
                conexao.Close();
            }
        }
Esempio n. 48
0
        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();
            }
        }
Esempio n. 49
0
 public void close()
 {
     if (con != null)
     {
         con.Close();
     }
     con = null;
 }
Esempio n. 50
0
        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);
        }
Esempio n. 51
0
        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);
        }
Esempio n. 52
0
        public static IEnumerable <FindUsers> FindAllUsers(string connectionString)
        {
            NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);

            connection.Open();

            var AllUsersFound = connection.Query <FindUsers>("SELECT  *  FROM  \"Users\"").AsList();

            connection.Close();

            return(AllUsersFound);
        }
Esempio n. 53
0
        public static int FindUserIDByEmail(string userEmail, string connectionString)
        {
            NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);

            connection.Open();

            var User = connection.QueryFirstOrDefault <FindUsers>($"SELECT * FROM \"Users\" WHERE \"Email\" = \'{userEmail}\'");

            connection.Close();

            return(User.UserID);
        }
Esempio n. 54
0
        public static IEnumerable <Books> FindAllBooks(string connectionString)
        {
            NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);

            connection.Open();

            var FoundBooks = connection.Query <Books>("SELECT  *  FROM  \"Books\"").AsList();

            connection.Close();

            return(FoundBooks);
        }
Esempio n. 55
0
        private void button1_Click(object sender, EventArgs e)
        {
            alabel.Text    = "";
            c.Text         = "";
            tc.Text        = "";
            supc.Text      = "";
            supnc.Text     = "";
            pc.Text        = "";
            pnc.Text       = "";
            c.Text         = Text;
            alabel.Text    = "";
            Nantenne.Text  = "";
            puissance.Text = "";
            s.Text         = "";
            densite.Text   = "";
            nbrZone.Text   = "";
            NC.Text        = "";
            NCC.Text       = "";
            cnx.Close();
            cnx.Open();
            n.NpgsqlCommand caa = new n.NpgsqlCommand(" Create or replace view zonec as select ST_INTERSECTION(ST_UNION(st_buffer(antenne.emp,puissance)),ST_UNION(quartier.zone)) as valeur from antenne,quartier;", cnx);
            caa.ExecuteNonQuery();

            VectorLayer nc = new VectorLayer("")
            {
                DataSource = new PostGIS(src, "zonec", "valeur", null)
            };

            nc.Style.Fill          = Brushes.Black;
            nc.Style.Outline       = Pens.White;
            nc.Style.EnableOutline = true;
            map1.Map.Layers.Clear();
            map1.Map.Layers.Add(quart);
            map1.Map.Layers.Add(ant);
            map1.Map.Layers.Add(nc);
            map1.Map.ZoomToExtents();
            map1.Refresh();
            cnx.Close();
            c.Text = "";
        }
Esempio n. 56
0
        public static DBclasses.Organization getOrganizationById(int id)
        {
            DBclasses.Organization result = new DBclasses.Organization();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from organizations where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id            = id;
                    result.isDeleted     = (bool)reader["isDeleted"];
                    result.name          = reader["name"].ToString();
                    result.contactPerson = reader["contactPerson"].ToString();

                    result.emails = reader["emails"] as string[];
                    result.phones = reader["phones"] as string[];

                    /* TODO */
                    result.callTimeFrom = reader["callTimeFrom"].ToString();;
                    result.callTimeTo   = reader["callTimeTo"].ToString();;

                    result.addresses = reader["addresses"] as string[];
                    result.photo     = reader["photo"].ToString();

                    result.aboutOrg = reader["aboutOrg"].ToString();
                    result.needHelp = reader["needHelp"].ToString();
                    result.donation = reader["donation"].ToString();
                    result.otherOrg = reader["otherOrg"].ToString();
                    result.linkToVK = reader["linkToVK"].ToString();
                    result.linkToFB = reader["linkToFB"].ToString();
                    result.linkToYT = reader["linkToYT"].ToString();
                    result.linkToTG = reader["linkToTG"].ToString();
                    result.linkToIG = reader["linkToIG"].ToString();

                    result.region = (int)reader["region"];
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    result.name = ex.StackTrace + ex.Message;
                    return(result);
                }
            }

            return(result);
        }
Esempio n. 57
0
        public void buffer_select(int num)
        {
            cnx.Close();
            cnx.Open();
            np.NpgsqlCommand cm = new np.NpgsqlCommand("create or replace view bf_t as Select num,st_buffer(emp,puissance) as bf from antenne where num=" + num + "", cnx);
            cm.ExecuteNonQuery();
            VectorLayer vc = new VectorLayer("buffer")
            {
                DataSource = new PostGIS(srx, "bf_t", "bf", "num")
            };
            VectorStyle styleSelected = new VectorStyle
            {
                Fill = Brushes.Transparent,

                Outline       = Pens.Blue,
                EnableOutline = true
            };

            vc.Style = styleSelected;
            mapImage1.Map.Layers.Add(vc);
            cnx.Close();
        }
Esempio n. 58
0
 /// <summary>
 /// 关闭数据库
 /// </summary>
 public void Close()
 {
     if (this.IsOpen)
     {
         dbc.Close();
     }
     if (dbc != null)
     {
         dbc.Dispose();
         dbc = null;
     }
     //throw new NotImplementedException();
 }
Esempio n. 59
0
        override internal void LoadAll()
        {
            try
            {
                string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
                                "n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
                                "idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
                                "JOIN pg_class cls ON cls.oid=indexrelid " +
                                "JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
                                "JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
                                "JOIN pg_am a ON a.oid = cls.relam " +
                                "LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
                                "LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
                                "WHERE con.conname IS NULL ORDER BY cls.relname;";

                NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString);

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn);
                cn.Open();
                cn.ChangeDatabase(this.Table.Tables.Database.Name);
                DataTable metaData = new DataTable();

                adapter.Fill(metaData);
                cn.Close();

                PopulateArrayNoHookup(metaData);

                for (int i = 0; i < this.Count; i++)
                {
                    Index index = this[i] as Index;

                    if (null != index)
                    {
                        string   s          = index._row["columns"] as string;
                        string[] colIndexes = s.Split(' ');

                        foreach (string colIndex in colIndexes)
                        {
                            if (colIndex != "0")
                            {
                                int id = Convert.ToInt32(colIndex);

                                Column column = this.Table.Columns[id - 1] as Column;
                                index.AddColumn(column.Name);
                            }
                        }
                    }
                }
            }
            catch {}
        }
        public Boolean Excluir(int idMovimentacaoEstoque, int idAgenda)
        {
            //conexao
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;

                if ((idMovimentacaoEstoque > 0))
                {
                    cmd.CommandText = "delete from movimentacaoestoque where id = " + Convert.ToString(idMovimentacaoEstoque);
                }
                else
                {
                    cmd.CommandText = "delete from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda);
                }

                cn.Open();
                int resultado = cmd.ExecuteNonQuery();
                if (resultado != 1)
                {
                    if ((idAgenda > 0))
                    {
                        throw new Exception("Não foi possível excluir a MovimentacaoEstoque da Agenda: " + Convert.ToString(idAgenda));
                    }
                    else
                    {
                        throw new Exception("Não foi possível excluir a MovimentacaoEstoque " + Convert.ToString(idMovimentacaoEstoque));
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }

            return(true);
        }