ExecuteScalar() public method

Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
public ExecuteScalar ( ) : Object
return Object
 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 = "";
     }
 }
        private bool LoginUser(Credentials credentials)
        {
            string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);

            NpgsqlCommand command = new NpgsqlCommand(@"SELECT COUNT(*) FROM clients WHERE email=@email AND password=@password");
            command.Parameters.Add("@email", NpgsqlDbType.Varchar, 100).Value = credentials.Email;
            command.Parameters.Add("@password", NpgsqlDbType.Varchar, 100).Value = credentials.Password;
            command.Connection = conn;

            try
            {
                conn.Open();

                int result = Convert.ToInt32(command.ExecuteScalar());

                if (result > 0)
                    return true;
                else
                    return false;
            }
            catch (NpgsqlException e)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
 internal static void PostgreSqlTest()
 {
     const string connectionString = @"Server=127.0.0.1;Port=5432;Database=test;User Id=postgres;Password=123456;";
     try
     {
         using (NpgsqlConnection npgsql = new NpgsqlConnection(connectionString))
         {
             npgsql.Open();
             //执行查询命令
             NpgsqlCommand command = new NpgsqlCommand("select *from student", npgsql);
             NpgsqlDataReader reader = command.ExecuteReader();
             while (reader.Read())
             {
                 Console.WriteLine($"SNO:{reader[0]} SNAME:{reader[1]} AGE:{reader[2]}");
             }
             reader.Close();
             //执行ExecuteScalar方法
             command = new NpgsqlCommand("select count(*) from student", npgsql);
             int count = Convert.ToInt32(command.ExecuteScalar());
             Console.WriteLine($"总共有{count}条记录");
         }
     }
     catch (NpgsqlException)
     {
         throw;
     }
 }
 protected void Button1_Click(object sender, EventArgs e)
 {
     string checks;
     string checkw;
     string id_sali = TextBox1.Text;
     string id_wyp = TextBox2.Text;
     NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
     conn.Open();
     NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
     NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
     checks = (String)check1.ExecuteScalar();
     checkw = (String)check2.ExecuteScalar();
     if (checkw != id_wyp)
     {
         Label2.Text = "";
         Label3.Text = "Wyposażenie nie istnieje w tej sali";
     }
     else if (checkw == id_wyp && checks == id_sali)
     {
         NpgsqlCommand del = new NpgsqlCommand("delete from wyp_sali where id_wyp ='" + id_wyp + "' and id_sali = '" + id_sali + "'", conn);
         del.ExecuteScalar();
         conn.Close();
         Label2.Text = "Usunięto wyposażenie z sali!";
         Label3.Text = "";
     }
 }
Beispiel #5
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);
     }
 }
        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();
            }
        }
Beispiel #7
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();
            }
        }
 public static object GetScalar(NpgsqlCommand command, NpgsqlConnection connection)
 {
     using (connection)
     {
         return command.ExecuteScalar();
     }
 }
        protected void Button1_Click(object sender, EventArgs e)
        {
            string check;
            string id_sali = TextBox1.Text;
            int pojemnosc = System.Int32.Parse(TextBox2.Text);
            string typ = DropDownList1.SelectedItem.ToString();

            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from sala where id_sali = '" + id_sali + "'", conn);
            check = (String)check1.ExecuteScalar();
            if (check != id_sali)
            {
                NpgsqlCommand add = new NpgsqlCommand("insert into sala values ('" + id_sali + "', '" + typ + "', '" + pojemnosc + "')", conn);
                add.ExecuteScalar();
                conn.Close();
                TextBox1.Text = "";
                TextBox2.Text = "";
                Label2.Text = "Dodano salę!";
                Label3.Text = "";
            }
            else if (check == id_sali)
            {
                Label2.Text = "";
                Label3.Text = "Sala już istnieje!";
            }
        }
 public int IntScalar(NpgsqlCommand cmd)
 {
     try
     {
         int dataset = 0;
         using (cmd)
         {
             cmd.Connection = GlobalVariables.Connection;
             dataset = Convert.ToInt32(cmd.ExecuteScalar());
         }
         return dataset;
     }
     catch (NpgsqlException e)
     {
         sMessage = e.ToString();
         MessageBox.Show(sMessage.ToString());
         return 0;
     }
     catch (Exception e)
     {
         sMessage = e.ToString();
         MessageBox.Show(sMessage.ToString());
         return 0;
     }
 }
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                string id_wyp = TextBox1.Text;
                string check;
                NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
                conn.Open();
                NpgsqlCommand check_user = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
                check = (String)check_user.ExecuteScalar();
                if (check == id_wyp)
                {
                    NpgsqlCommand delete = new NpgsqlCommand("delete from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
                    delete.ExecuteScalar();
                    TextBox1.Text = "";
                    TextBox2.Text = "";
                    Label3.Text = "Wyposażenie zostało usunięta!";
                    Label2.Text = "";
                }
                else if (check != id_wyp)
                {
                    Label2.Text = "Brak wyposażenia w bazie!";
                    Label3.Text = "";
                }

            }
            catch
            {
            }
        }
Beispiel #12
0
        public static object Function(string connectionString, string functionName, params NpgsqlParameter[] parameters)
        {
            var npgsqlConnection = OpenConnection(connectionString);

            try
            {
                var command = new NpgsqlCommand(functionName, npgsqlConnection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddRange(parameters);

                var result = command.ExecuteScalar();

                return result;
            }
            catch (Exception ex)
            {
                throw new FhirbaseException(
                    $"Call {functionName} FHIRbase function failed. Reason {ex.Message}",
                    ex);
            }
            finally
            {
                npgsqlConnection.Close();
            }
        }
Beispiel #13
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();
            }
        }
        public void Incluir(Model_Vo_ContasAReceber pContasAReceber)
        {
            SqlConnection cn = new SqlConnection();
            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = "insert into contasareceber(datahoracriacao,descricao,idcliente,idproduto,idreservaorigem,origem,recebido,valorareceber) values (@datahoracriacao,@descricao,@idcliente,@idproduto,@idreservaorigem,@origem,@recebido,@valorareceber);";
                cmd.Parameters.AddWithValue("@datahoracriacao", Dados.Model_Dao_Dados.ConverterDataToStr(pContasAReceber.DataHoraCriacao, false));
                cmd.Parameters.AddWithValue("@descricao", pContasAReceber.Descricao);
                cmd.Parameters.AddWithValue("@idcliente", pContasAReceber.IdCliente);
                cmd.Parameters.AddWithValue("@idproduto", pContasAReceber.IdProduto);
                cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
                cmd.Parameters.AddWithValue("@origem", pContasAReceber.Origem.ToString());
                cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
                cmd.Parameters.AddWithValue("@valorareceber", pContasAReceber.ValorAReceber);

                cn.Open();
                pContasAReceber.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
Beispiel #15
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();
            }
        }
 protected void Button1_Click(object sender, EventArgs e)
 {
     try
     {
         Int32 numer = System.Int32.Parse(TextBox1.Text);
         Int32 check;
         NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
         conn.Open();
         NpgsqlCommand check_user = new NpgsqlCommand("select nr_rezerwacji from rezerwacja where nr_rezerwacji = '" + numer + "'", conn);
         check = (Int32)check_user.ExecuteScalar();
         if (check == numer)
         {
             NpgsqlCommand delete = new NpgsqlCommand("delete from rezerwacja where nr_rezerwacji = '" + numer + "'", conn);
             delete.ExecuteScalar();
             TextBox1.Text = "";
             TextBox2.Text = "";
             Label3.Text = "Rezerwacja została anulowana!";
             Label2.Text = "";
         }
         else if (check != numer)
         {
             Label2.Text = "Brak rezerwacji w bazie!";
             Label3.Text = "";
         }
     }
     catch { }
 }
Beispiel #17
0
 public static void CreateTables(NpgsqlConnection conn)
 {
     using (var cmdRename1 = new NpgsqlCommand("select table_name from information_schema.tables where table_name = 'rn_results'", conn))
     {
         var tblExists = false;
         using (var rdr1 = cmdRename1.ExecuteReader()) { tblExists = rdr1.Read(); }
         cmdRename1.Dispose();
         if (tblExists)
         {
             var curCount = 0;
             using (var cmdRename2 = new NpgsqlCommand("select count(*) from rn_results", conn))
             {
                 curCount = Convert.ToInt32(cmdRename2.ExecuteScalar());
             }
             if (curCount == 0)
             {
                 using (var cmdRename3 = new NpgsqlCommand("drop table rn_results", conn)) { cmdRename3.ExecuteNonQuery(); }
             }
             else
             {
                 using (var cmdRename2 = new NpgsqlCommand("alter table rn_results rename to rn_results_old" + (curDBVersion - 1).ToString(), conn)) { cmdRename2.ExecuteNonQuery(); }
             }
         }
     }
     using (var cmd1 = new NpgsqlCommand("drop table rn_version;", conn)) { cmd1.ExecuteNonQuery(); }
     using (var cmd2 = new NpgsqlCommand("create table rn_version (ver integer);", conn)) { cmd2.ExecuteNonQuery(); }
     using (var cmd3 = new NpgsqlCommand("insert into rn_version (ver) values (" + curDBVersion.ToString() + ")", conn)) { cmd3.ExecuteScalar(); }
     using (var cmd4 = new NpgsqlCommand("create table rn_results (email varchar(100), name varchar(100), phone_no varchar(100), daum_id varchar(100), work_place varchar(200), client_ip varchar(50), sign_image1 text, sign_image2 text, insert_dt timestamp, sign_radio1 integer, sign_radio2 integer, PRIMARY KEY(email));", conn))
     {
         cmd4.ExecuteNonQuery();
     }
     using (var cmd5 = new NpgsqlCommand("create index rn_results_idx_insert_dt on rn_results (insert_dt)", conn)) { cmd5.ExecuteNonQuery(); }
 }
        public Boolean addTeam(string team)
        {
            string sql = "SELECT COUNT(*) FROM team WHERE name = :team";
            NpgsqlCommand command = new NpgsqlCommand(sql, conn);
            command.Parameters.Add(new NpgsqlParameter("team", NpgsqlDbType.Text));
            command.Parameters[0].Value = team;

            long numFound = (long) command.ExecuteScalar();

            if (numFound > 0)
            {
                sql = "UPDATE team SET num_matches = num_matches + 1 WHERE name = :team";
                command = new NpgsqlCommand(sql, conn);
                command.Parameters.Add(new NpgsqlParameter("team", team));

                command.ExecuteNonQuery();
            } else
            {
                sql = "INSERT INTO team (name, num_matches) VALUES (:team, 1)";
                command = new NpgsqlCommand(sql, conn);
                command.Parameters.Add(new NpgsqlParameter("team", team));

                command.ExecuteNonQuery();
            }

            return true;
        }
 public void Incluir(Model_Vo_LivroCaixa pLivroCaixa)
 {
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         //command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandText = "insert into livrocaixa(datahora,descricao,idcontasareceber,tipodemovimento,valor) values (@datahora,@descricao,@idcontasareceber,@tipodemovimento,@valor);";
         cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr((pLivroCaixa.DataHora), false));
         cmd.Parameters.AddWithValue("@descricao", pLivroCaixa.Descricao);
         cmd.Parameters.AddWithValue("@idcontasareceber", pLivroCaixa.IdContasAReceber);
         cmd.Parameters.AddWithValue("@tipodemovimento", pLivroCaixa.TipoDeMovimento.ToString());
         cmd.Parameters.AddWithValue("@valor", pLivroCaixa.Valor);
         cn.Open();
         pLivroCaixa.Id = Convert.ToInt32(cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }
 public 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();
     }
 }
Beispiel #21
0
        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();
     }
 }
 public int Salvarcliente(string nome, int codempresa)
 {
     using (var conn = new NpgsqlConnection(Connectionstring))
     {
         conn.Open();
         using (var trans  = conn.BeginTransaction())
         {
             try
             {
                 int id;
                 using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans))
                 {
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                     
                 }
                 using (
                     var cmd =
                         new NpgsqlCommand(
                             "INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn,
                             trans))
                 {
                     cmd.ExecuteNonQuery();
                     trans.Commit();
                     return id;
                 }
             }
             catch (Exception)
             {
                 trans.Rollback();
                 return 0;
             }
         }
     }
 }
Beispiel #24
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();
        }
    }
Beispiel #25
0
        /// <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);
        }
Beispiel #26
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();
     }
 }
Beispiel #27
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();
            }
        }
Beispiel #28
0
        public static string Get(string strcmd)
        {
            if (connectionString == null || connectionString.Length <= 0)
            {
                Initialize();
            }
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {

                string ret = null;
                NpgsqlCommand cmd = new NpgsqlCommand(strcmd, conn);
                conn.Open();
                try
                {
                    ret = cmd.ExecuteScalar().ToString();
                }
                catch(Exception e)
                {

                }
                finally
                {
                    conn.Close();
                }
                return ret;
            }
        }
        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!";
            }
        }
Beispiel #30
0
        public static void Initialize()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
            var connectionBuilder = new NpgsqlConnectionStringBuilder(connectionString);

            //connect to postgres database to create a new database
            var databaseName = connectionBuilder.Database;
            connectionBuilder.Database = "postgres";
            connectionString = connectionBuilder.ToString();

            using (var conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();

                bool dbExists;
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.CommandText = string.Format(@"SELECT TRUE FROM pg_database WHERE datname='{0}'", databaseName);
                    cmd.Connection = conn;

                    var result = cmd.ExecuteScalar();
                    dbExists = result != null && Convert.ToBoolean(result);
                }

                if (dbExists)
                {
                    DoClean(conn);
                }
                else
                {
                    DoCreate(conn, databaseName);
                }
            }
        }
        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!";
            }
        }
        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 static void HandleRedirects(XmlReader reader, NpgsqlConnection connection)
        {
            var redirect_to = reader.GetAttribute("redirect");
            if (string.IsNullOrEmpty(redirect_to)) return;
            var title = reader.GetAttribute("title");

            var fetch_row_command = new NpgsqlCommand(@"with redirect_page as (SELECT id,title,redirect FROM pages WHERE title = :title)
             select * from redirect_page union all
             select page.id,page.title,page.redirect from pages page
             join redirect_page on page.title = redirect_page.redirect", connection);
            fetch_row_command.Parameters.AddWithValue("title", title);
            var query_reader = fetch_row_command.ExecuteReader();
            if (!query_reader.HasRows) return;
            query_reader.Read();
            int redirect_id = query_reader.GetInt32(0);
            query_reader.Read();
            int real_page_id = query_reader.GetInt32(0);
            query_reader.Close();

            var update = string.Format(@"UPDATE pages SET links = (links - ARRAY[{0}])  || ARRAY[{1}] WHERE links @> ARRAY[{0}];",
                redirect_id, redirect_id);

            var command = new NpgsqlCommand(update, connection);
            command.ExecuteScalar();
        }
 public void Incluir(Model_Vo_Agenda pAgenda)
 {
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         //command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandText = "insert into agenda(datahorareserva,idcliente,idsala) values (@datahorareserva,@idcliente,@idsala);";
         cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false));
         cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente);
         cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala);
         cn.Open();
         pAgenda.Id = Convert.ToInt32(cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }
Beispiel #35
0
 public void ChangeDatabase()
 {
     Conn.ChangeDatabase("template1");
     var command = new NpgsqlCommand("select current_database()", Conn);
     var result = (String)command.ExecuteScalar();
     Assert.AreEqual("template1", result);
 }
        public void DistributedTransactionRollback()
        {
            int field_serial1;
            int field_serial2;
            var connectionString = ConnectionString + ";enlist=true";
            using (var scope = new TransactionScope())
            {
                //UseStringParameterWithNoNpgsqlDbType
                using (var connection = new NpgsqlConnection(connectionString))
                {
                    connection.Open();
                    var command = new NpgsqlCommand("insert into data (field_text) values (:p0)", connection);
                    command.Parameters.Add(new NpgsqlParameter("p0", "test"));
                    Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Text);
                    Assert.AreEqual(command.Parameters[0].DbType, DbType.String);
                    object result = command.ExecuteNonQuery();
                    Assert.AreEqual(1, result);

                    field_serial1 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
                    var command2 = new NpgsqlCommand("select field_text from data where field_serial = (select max(field_serial) from data)", connection);
                    result = command2.ExecuteScalar();
                    Assert.AreEqual("test", result);
                }
                //UseIntegerParameterWithNoNpgsqlDbType
                using (var connection = new NpgsqlConnection(connectionString))
                {
                    connection.Open();
                    var command = new NpgsqlCommand("insert into data(field_int4) values (:p0)", connection);
                    command.Parameters.Add(new NpgsqlParameter("p0", 5));
                    Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Integer);
                    Assert.AreEqual(command.Parameters[0].DbType, DbType.Int32);
                    Object result = command.ExecuteNonQuery();
                    Assert.AreEqual(1, result);

                    field_serial2 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar();
                    var command2 = new NpgsqlCommand( "select field_int4 from data where field_serial = (select max(field_serial) from data)", connection);
                    result = command2.ExecuteScalar();
                    Assert.AreEqual(5, result);

                    // using new connection here... make sure we can't see previous results even though
                    // it is the same distributed transaction
                    var command3 = new NpgsqlCommand("select field_text from data where field_serial = :p0", connection);
                    command3.Parameters.Add(new NpgsqlParameter("p0", field_serial1));
                    result = command3.ExecuteScalar();

                    // won't see value of "test" since that's
                    // another connection
                    Assert.AreEqual(null, result);
                }
                // not commiting here.
            }
            // This is an attempt to wait for the distributed transaction to rollback
            // not guaranteed to work, but should be good enough for testing purposes.
            System.Threading.Thread.Sleep(500);
            AssertNoPreparedTransactions();
            // ensure they no longer exist since we rolled back
            AssertRowNotExist("field_text", field_serial1);
            AssertRowNotExist("field_int4", field_serial2);
        }
Beispiel #37
0
        public static object GetScalarValue(Npgsql.NpgsqlCommand command)
        {
            if (command != null)
            {
                using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
                {
                    command.Connection = connection;
                    connection.Open();
                    return(command.ExecuteScalar());
                }
            }

            return(null);
        }
Beispiel #38
0
        /// <summary>
        /// 查询一个字段
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            object obj = null;

            try
            {
                using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, conn))
                {
                    obj = command.ExecuteScalar();
                }
            }
            catch {
                Console.WriteLine("查询数据失败");
            }
            return(obj);
        }
Beispiel #39
0
    internal void InsertAttachment(long newsId, string key, byte[] value)
    {
        var strInsertAttachmentCommand = @"INSERT INTO news.news_attachment(
            news_attachment_id, attachment_description, attachment_file, 
            attachment_file_path, type, userid, bst_suggestion_register_id, 
            news_id, proposed_news_approval_id, attachment_type_id)
    VALUES (nextval('log_seq'), null, '@value', 
            '" + key + @"', null, null, null, 
            '" + newsId + @"', null, null);";
        var cmd = new Npgsql.NpgsqlCommand(strInsertAttachmentCommand, conn);

        cmd.Parameters.AddWithValue("value", value);

        cmd.Parameters.Add(new NpgsqlParameter()
        {
        });
        cmd.ExecuteScalar();
    }
Beispiel #40
0
    private long InsertNews(RasadNews rasadNews, long forignSourceId)
    {
        var strInsertNewsCommand = @"INSERT INTO news.news(
            news_id, bst_suggestion_register, consideration, realtime, news_content, 
            declarationdate_time, news_lead, news_level_type, news_number, 
            occurrence_time, register_date, register_news_type, title, approvedorganizationalposition, 
            bst_accuracy_degree_id, bst_confidential_id, bst_refer_level_id, 
            forignsource_forign_source_id, newspic_news_pic_id, organizationalposition_id, 
            news_sources_str, news_types_str, bst_news_type_id)
    VALUES (nextval('log_seq'), null, null, false, '" + rasadNews.Content + @"', 
            '" + rasadNews.PubTime + @"', '" + rasadNews.Description + @"', null, '" + GetStationNumber() + "-" + DateTime.Now.ToString("yyyyMMddHHss") + @"', 
            '" + rasadNews.PubTime + @"', '" + rasadNews.PubTime + @"', 10, '" + rasadNews.Title + @"', null, 
            950, 218, 1, 
            '" + forignSourceId + @"', null, null, 
            null, null, null);";
        var cmd = new Npgsql.NpgsqlCommand(strInsertNewsCommand, conn);

        return((long)cmd.ExecuteScalar());
    }
Beispiel #41
0
    private long InsertForignSource(RasadNews rasadNews, string folder, long line)
    {
        var strInsertForignCommand = @"INSERT INTO news.forign_source(
            forign_source_id, author, category_type, content, description, 
            fetch_time, folder, id, insert_on, line, mesbahdocid, page_url, 
            pub_time, source_id, title, news_id)
    VALUES (nextval('log_seq'), @autor, @CategoryType, @Content, @Description, 
            '" + new DateTime(long.Parse(rasadNews.FetchTime)) + "', '" + folder + "', " + rasadNews.Id + @", '" + DateTime.Now + "', " + line + ", " + rasadNews.mesbahdocid + ", '" + rasadNews.PageUrl + @"', 
            '" + new DateTime(long.Parse(rasadNews.PubTime)) + @"', '" + rasadNews.SourceId + "', @Title, null) RETURNING forign_source_id;";

        var cmd = new Npgsql.NpgsqlCommand(strInsertForignCommand, conn);

        cmd.Parameters.AddWithValue("autor", rasadNews.Author);
        cmd.Parameters.AddWithValue("CategoryType", rasadNews.CategoryType);
        cmd.Parameters.AddWithValue("Content", (rasadNews.Content.Length > 10000) ? rasadNews.Content.Substring(0, 9999) : rasadNews.Content);
        cmd.Parameters.AddWithValue("Description", rasadNews.Description);
        cmd.Parameters.AddWithValue("Title", rasadNews.Title);

        var a = cmd.ExecuteScalar();

        return((long)a);
    }
Beispiel #42
0
        /// <summary>
        /// This method checks if the connector is still ok.
        /// We try to send a simple query text, select 1 as ConnectionTest;
        /// </summary>
        internal Boolean IsValid()
        {
            try
            {
                // Here we use a fake NpgsqlCommand, just to send the test query string.

                // Get random test value.
                Byte[] testBytes = new Byte[2];
                rng.GetNonZeroBytes(testBytes);
                String testValue = String.Format("Npgsql{0}{1}", testBytes[0], testBytes[1]);

                //Query(new NpgsqlCommand("select 1 as ConnectionTest", this));
                string compareValue = string.Empty;
                string sql          = "select '" + testValue + "'";
                // restore initial connection parameters resetted by "Discard ALL"
                if (SupportsDiscard)
                {
                    sql = this.initQueries + sql;
                }
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, this))
                {
                    compareValue = (string)cmd.ExecuteScalar();
                }

                if (compareValue != testValue)
                {
                    return(false);
                }

                this.RequireReadyForQuery = true;
            }
            catch
            {
                return(false);
            }

            return(true);
        }
Beispiel #43
0
 public bool ExecuteNonQuery(out int Id)
 {
     Id = 0;
     try
     {
         if (!string.IsNullOrEmpty(Script))
         {
             if (Conectar())
             {
                 using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(Script, Connection))
                 {
                     Parametros.ForEach(x => cmd.Parameters.AddWithValue(x.Parameter.ToString(), x.Valor));
                     Id = (Int32)cmd.ExecuteScalar();
                     return(true);
                 }
             }
             else
             {
                 return(false);
             }
         }
         msg = "Comando inválido.";
         return(false);
     }
     catch (NpgsqlException ex)
     {
         msg = ex.Message;
         return(false);
     }
     finally
     {
         if (Transacao == false)
         {
             Desconectar();
         }
     }
 }
Beispiel #44
0
        public void Incluir(Model_Vo_ContasAReceber pContasAReceber)
        {
            SqlConnection cn = new SqlConnection();

            try
            {
                cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
                //command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = cn;
                cmd.CommandText = "insert into contasareceber(datahoracriacao,descricao,idcliente,idproduto,idreservaorigem,origem,recebido,valorareceber) values (@datahoracriacao,@descricao,@idcliente,@idproduto,@idreservaorigem,@origem,@recebido,@valorareceber);";
                cmd.Parameters.AddWithValue("@datahoracriacao", Dados.Model_Dao_Dados.ConverterDataToStr(pContasAReceber.DataHoraCriacao, false));
                cmd.Parameters.AddWithValue("@descricao", pContasAReceber.Descricao);
                cmd.Parameters.AddWithValue("@idcliente", pContasAReceber.IdCliente);
                cmd.Parameters.AddWithValue("@idproduto", pContasAReceber.IdProduto);
                cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
                cmd.Parameters.AddWithValue("@origem", pContasAReceber.Origem.ToString());
                cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
                cmd.Parameters.AddWithValue("@valorareceber", pContasAReceber.ValorAReceber);

                cn.Open();
                pContasAReceber.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                throw new Exception("Servidor SQL Erro:" + ex.Number);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }
Beispiel #45
0
        private static void DoDeriveParameters(NpgsqlCommand command)
        {
            // See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html
            command.Parameters.Clear();
            // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
            // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
            var    serverVersion = command.Connection.Connector.ServerVersion;
            string query;
            string procedureName;
            string schemaName = null;
            var    fullName   = command.CommandText.Split('.');

            if (fullName.Length > 1 && fullName[0].Length > 0)
            {
                // proargsmodes is supported for Postgresql 8.1 and above
                query = serverVersion >= new Version(8, 1, 0)
                    ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"
                    : "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
                schemaName    = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
                procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();

                // The pg_temp pseudo-schema is special - it's an alias to a real schema name (e.g. pg_temp_2).
                // We get the real name with pg_my_temp_schema().
                if (schemaName == "pg_temp")
                {
                    using (var c = new NpgsqlCommand("SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()", command.Connection))
                    {
                        schemaName = (string)c.ExecuteScalar();
                    }
                }
            }
            else
            {
                // proargsmodes is supported for Postgresql 8.1 and above
                query = serverVersion >= new Version(8, 1, 0)
                    ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"
                    : "select proargnames, proargtypes from pg_proc where proname = :proname";
                procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
            }

            using (var c = new NpgsqlCommand(query, command.Connection))
            {
                c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
                c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
                if (fullName.Length > 1 && !string.IsNullOrEmpty(schemaName))
                {
                    var prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
                    prm.Value = schemaName.Replace("\"", "").Trim();
                }

                string[] names = null;
                uint[]   types = null;
                char[]   modes = null;

                using (var rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                        {
                            names = rdr.GetValue(0) as string[];
                        }
                        if (serverVersion >= new Version("8.1.0"))
                        {
                            if (!rdr.IsDBNull(2))
                            {
                                types = rdr.GetValue(2) as uint[];
                            }
                            if (!rdr.IsDBNull(3))
                            {
                                modes = rdr.GetValue(3) as char[];
                            }
                        }
                        if (types == null)
                        {
                            if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0)
                            {
                                return;  // Parameterless function
                            }
                            types = rdr.GetFieldValue <uint[]>(1);
                        }
                    }
                    else
                    {
                        throw new InvalidOperationException(string.Format("{0} does not exist in pg_proc", command.CommandText));
                    }
                }

                command.Parameters.Clear();
                for (var i = 0; i < types.Length; i++)
                {
                    var param = new NpgsqlParameter();

                    // TODO: Fix enums, composite types
                    var npgsqlDbType = c.Connection.Connector.TypeHandlerRegistry[types[i]].NpgsqlDbType;
                    if (npgsqlDbType == NpgsqlDbType.Unknown)
                    {
                        throw new InvalidOperationException(string.Format("Invalid parameter type: {0}", types[i]));
                    }
                    param.NpgsqlDbType = npgsqlDbType;

                    if (names != null && i < names.Length)
                    {
                        param.ParameterName = ":" + names[i];
                    }
                    else
                    {
                        param.ParameterName = "parameter" + (i + 1);
                    }

                    if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported)
                    {
                        param.Direction = ParameterDirection.Input;
                    }
                    else
                    {
                        switch (modes[i])
                        {
                        case 'i':
                            param.Direction = ParameterDirection.Input;
                            break;

                        case 'o':
                            param.Direction = ParameterDirection.Output;
                            break;

                        case 'b':
                            param.Direction = ParameterDirection.InputOutput;
                            break;

                        case 'v':
                            throw new NotImplementedException("Cannot derive function parameter of type VARIADIC");

                        case 't':
                            throw new NotImplementedException("Cannot derive function parameter of type TABLE");

                        default:
                            throw new ArgumentOutOfRangeException("proargmode", modes[i],
                                                                  "Unknown code in proargmodes while deriving: " + modes[i]);
                        }
                    }

                    command.Parameters.Add(param);
                }
            }
        }
Beispiel #46
0
        /*/// <value>Counts the numbers of Connections that share
         * /// this Connector. Used in Release() to decide wether this
         * /// connector is to be moved to the PooledConnectors list.</value>
         * // internal int mShareCount;*/

        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ServerVersion = null;
            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.

            _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown)
                                          ? ProtocolVersion.Version3
                                          : settings.Protocol;

            // Reset state to initialize new connector in pool.
            CurrentState = NpgsqlClosedState.Instance;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this);
            try
            {
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }
            catch (NpgsqlException ne)
            {
                // Check for protocol not supported.  If we have been told what protocol to use,
                // we will not try this step.
                if (settings.Protocol != ProtocolVersion.Unknown)
                {
                    throw;
                }
                // If we attempted protocol version 3, it may be possible to drop back to version 2.
                if (BackendProtocolVersion != ProtocolVersion.Version3)
                {
                    throw;
                }
                NpgsqlError Error0 = (NpgsqlError)ne.Errors[0];

                // If NpgsqlError..ctor() encounters a version 2 error,
                // it will set its own protocol version to version 2.  That way, we can tell
                // easily if the error was a FATAL: protocol error.
                if (Error0.BackendProtocolVersion != ProtocolVersion.Version2)
                {
                    throw;
                }
                // Try using the 2.0 protocol.
                _mediator.ResetResponses();
                BackendProtocolVersion = ProtocolVersion.Version2;
                CurrentState           = NpgsqlClosedState.Instance;

                // Get a raw connection, possibly SSL...
                CurrentState.Open(this);
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }

            // Change the state of connection to open and ready.
            _connection_state = ConnectionState.Open;
            CurrentState      = NpgsqlReadyState.Instance;

            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersion == null)
            {
                NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this);
                ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar()));
            }

            // Adjust client encoding.

            NpgsqlParameterStatus clientEncodingParam = null;

            if (
                !ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) ||
                (!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase))
                )
            {
                new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind();
            }

            if (!string.IsNullOrEmpty(settings.SearchPath))
            {
                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                 * p.Value = settings.SearchPath;
                 * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                 * commandSearchPath.Parameters.Add(p);
                 * commandSearchPath.ExecuteNonQuery();*/

                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                 * p.Value = settings.SearchPath;
                 * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                 * commandSearchPath.Parameters.Add(p);
                 * commandSearchPath.ExecuteNonQuery();*/

                // TODO: Add proper message when finding a semicolon in search_path.
                // This semicolon could lead to a sql injection security hole as someone could write in connection string:
                // searchpath=public;delete from table; and it would be executed.

                if (settings.SearchPath.Contains(";"))
                {
                    throw new InvalidOperationException();
                }

                // This is using string concatenation because set search_path doesn't allow type casting. ::text
                NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH=" + settings.SearchPath, this);
                commandSearchPath.ExecuteBlind();
            }



            /*
             * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made
             * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it.
             * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here.
             * This only works on postgresql servers where the ssl renegotiation settings is supported of course.
             * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information.
             */



            try

            {
                NpgsqlCommand commandSslrenegotiation = new NpgsqlCommand("SET ssl_renegotiation_limit=0", this);

                commandSslrenegotiation.ExecuteBlind();
            }

            catch {}



            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            ProcessServerVersion();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }
Beispiel #47
0
        private Boolean CheckFunctionNeedsColumnDefinitionList()
        {
            // If and only if a function returns "record" and has no OUT ("o" in proargmodes), INOUT ("b"), or TABLE
            // ("t") return arguments to characterize the result columns, we must provide a column definition list.
            // See http://pgfoundry.org/forum/forum.php?thread_id=1075&forum_id=519
            // We would use our Output and InputOutput parameters to construct that column definition list.  If we have
            // no such parameters, skip the check: we could only construct "AS ()", which yields a syntax error.

            // Updated after 0.99.3 to support the optional existence of a name qualifying schema and allow for case insensitivity
            // when the schema or procedure name do not contain a quote.
            // The hard-coded schema name 'public' was replaced with code that uses schema as a qualifier, only if it is provided.

            String returnRecordQuery;

            StringBuilder parameterTypes = new StringBuilder("");

            // Process parameters

            Boolean seenDef = false;

            foreach (NpgsqlParameter p in Parameters)
            {
                if ((p.Direction == ParameterDirection.Input) || (p.Direction == ParameterDirection.InputOutput))
                {
                    parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID.ToString() + " ");
                }

                if ((p.Direction == ParameterDirection.Output) || (p.Direction == ParameterDirection.InputOutput))
                {
                    seenDef = true;
                }
            }

            if (!seenDef)
            {
                return(false);
            }

            // Process schema name.

            String schemaName    = String.Empty;
            String procedureName = String.Empty;

            String[] fullName = CommandText.Split('.');

            String predicate = "prorettype = ( select oid from pg_type where typname = 'record' ) "
                               + "and proargtypes=:proargtypes and proname=:proname "
                               // proargmodes && array['o','b','t']::"char"[] performs just as well, but it requires PostgreSQL 8.2.
                               + "and ('o' = any (proargmodes) OR 'b' = any (proargmodes) OR 't' = any (proargmodes)) is not true";

            if (fullName.Length == 2)
            {
                returnRecordQuery =
                    "select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where " + predicate + " and n.nspname=:nspname";

                schemaName    = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
                procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
            }
            else
            {
                // Instead of defaulting don't use the nspname, as an alternative, query pg_proc and pg_namespace to try and determine the nspname.
                //schemaName = "public"; // This was removed after build 0.99.3 because the assumption that a function is in public is often incorrect.
                returnRecordQuery =
                    "select count(*) > 0 from pg_proc p where " + predicate;

                procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower();
            }

            bool ret;

            using (NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection))
            {
                c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Oidvector));
                c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Name));

                c.Parameters[0].Value = parameterTypes.ToString();
                c.Parameters[1].Value = procedureName;

                if (schemaName != null && schemaName.Length > 0)
                {
                    c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Name));
                    c.Parameters[2].Value = schemaName;
                }

                ret = (Boolean)c.ExecuteScalar();
            }

            return(ret);
        }
Beispiel #48
0
        /*/// <value>Counts the numbers of Connections that share
         * /// this Connector. Used in Release() to decide wether this
         * /// connector is to be moved to the PooledConnectors list.</value>
         * // internal int mShareCount;*/

        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ServerVersion = null;
            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.

            _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown)
                                          ? ProtocolVersion.Version3
                                          : settings.Protocol;

            // Reset state to initialize new connector in pool.
            CurrentState = NpgsqlClosedState.Instance;

            // Keep track of time remaining; Even though there may be multiple timeout-able calls,
            // this allows us to still respect the caller's timeout expectation.
            int      connectTimeRemaining = this.ConnectionTimeout * 1000;
            DateTime attemptStart         = DateTime.Now;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this, connectTimeRemaining);
            try
            {
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }
            catch (NpgsqlException ne)
            {
                connectTimeRemaining -= Convert.ToInt32((DateTime.Now - attemptStart).TotalMilliseconds);

                // Check for protocol not supported.  If we have been told what protocol to use,
                // we will not try this step.
                if (settings.Protocol != ProtocolVersion.Unknown)
                {
                    throw;
                }
                // If we attempted protocol version 3, it may be possible to drop back to version 2.
                if (BackendProtocolVersion != ProtocolVersion.Version3)
                {
                    throw;
                }
                NpgsqlError Error0 = (NpgsqlError)ne.Errors[0];

                // If NpgsqlError..ctor() encounters a version 2 error,
                // it will set its own protocol version to version 2.  That way, we can tell
                // easily if the error was a FATAL: protocol error.
                if (Error0.BackendProtocolVersion != ProtocolVersion.Version2)
                {
                    throw;
                }
                // Try using the 2.0 protocol.
                _mediator.ResetResponses();
                BackendProtocolVersion = ProtocolVersion.Version2;
                CurrentState           = NpgsqlClosedState.Instance;

                // Get a raw connection, possibly SSL...
                CurrentState.Open(this, connectTimeRemaining);
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }

            // Change the state of connection to open and ready.
            _connection_state = ConnectionState.Open;
            CurrentState      = NpgsqlReadyState.Instance;

            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersion == null)
            {
                //NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this);
                //ServerVersion = new Version(PGUtil.ExtractServerVersion((string) command.ExecuteScalar()));
                using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this))
                {
                    ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar()));
                }
            }

            // Adjust client encoding.

            NpgsqlParameterStatus clientEncodingParam = null;

            if (
                !ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) ||
                (!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase))
                )
            {
                new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind();
            }

            if (!string.IsNullOrEmpty(settings.SearchPath))
            {
                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                 * p.Value = settings.SearchPath;
                 * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                 * commandSearchPath.Parameters.Add(p);
                 * commandSearchPath.ExecuteNonQuery();*/

                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                 * p.Value = settings.SearchPath;
                 * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                 * commandSearchPath.Parameters.Add(p);
                 * commandSearchPath.ExecuteNonQuery();*/

                // TODO: Add proper message when finding a semicolon in search_path.
                // This semicolon could lead to a sql injection security hole as someone could write in connection string:
                // searchpath=public;delete from table; and it would be executed.

                if (settings.SearchPath.Contains(";"))
                {
                    throw new InvalidOperationException();
                }

                // This is using string concatenation because set search_path doesn't allow type casting. ::text
                NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH=" + settings.SearchPath, this);
                commandSearchPath.ExecuteBlind();
            }

            if (!string.IsNullOrEmpty(settings.ApplicationName))
            {
                if (!SupportsApplicationName)
                {
                    //TODO
                    //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported"));
                    throw new InvalidOperationException("ApplicationName not supported.");
                }

                if (settings.ApplicationName.Contains(";"))
                {
                    throw new InvalidOperationException();
                }

                NpgsqlCommand commandApplicationName = new NpgsqlCommand("SET APPLICATION_NAME='" + settings.ApplicationName + "'", this);
                commandApplicationName.ExecuteBlind();
            }

            /*
             * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made
             * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it.
             * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here.
             * This only works on postgresql servers where the ssl renegotiation settings is supported of course.
             * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information.
             */

            try
            {
                NpgsqlCommand commandSslrenegotiation = new NpgsqlCommand("SET ssl_renegotiation_limit=0", this);
                commandSslrenegotiation.ExecuteBlind();
            }
            catch {}



            /*
             * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3.
             * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least.
             * Check bug report #1010992 for more information.
             */


            try
            {
                NpgsqlCommand commandSingleDoublePrecision = new NpgsqlCommand("SET extra_float_digits=2;SET extra_float_digits=3;", this);
                commandSingleDoublePrecision.ExecuteBlind();
            }
            catch {}


            /*
             * Set lc_monetary format to 'C' ir order to get a culture agnostic representation of money.
             * I noticed that on Windows, even when the lc_monetary is English_United States.UTF-8, negative
             * money is formatted as ($value) with parentheses to indicate negative value.
             * By going with a culture agnostic format, we get a consistent behavior.
             */

            try
            {
                NpgsqlCommand commandMonetaryFormatC = new NpgsqlCommand("SET lc_monetary='C';", this);
                commandMonetaryFormatC.ExecuteBlind();
            }
            catch
            {
            }


            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            ProcessServerVersion();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }
        /// <value>Counts the numbers of Connections that share
        /// this Connector. Used in Release() to decide wether this
        /// connector is to be moved to the PooledConnectors list.</value>
        // internal int mShareCount;

        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ProtocolVersion PV;

            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.
            if (ConnectionString.Contains(ConnectionStringKeys.Protocol))
            {
                PV = ConnectionString.ToProtocolVersion(ConnectionStringKeys.Protocol);
            }
            else
            {
                PV = ProtocolVersion.Unknown;
            }

            _backendProtocolVersion = (PV == ProtocolVersion.Unknown) ? ProtocolVersion.Version3 : PV;

            // Reset state to initialize new connector in pool.
            Encoding     = Encoding.Default;
            CurrentState = NpgsqlClosedState.Instance;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this);
            // Establish protocol communication and handle authentication...
            CurrentState.Startup(this);

            // Check for protocol not supported.  If we have been told what protocol to use,
            // we will not try this step.
            if (_mediator.Errors.Count > 0 && PV == ProtocolVersion.Unknown)
            {
                // If we attempted protocol version 3, it may be possible to drop back to version 2.
                if (BackendProtocolVersion == ProtocolVersion.Version3)
                {
                    NpgsqlError Error0 = (NpgsqlError)_mediator.Errors[0];

                    // If NpgsqlError.ReadFromStream_Ver_3() encounters a version 2 error,
                    // it will set its own protocol version to version 2.  That way, we can tell
                    // easily if the error was a FATAL: protocol error.
                    if (Error0.BackendProtocolVersion == ProtocolVersion.Version2)
                    {
                        // Try using the 2.0 protocol.
                        _mediator.ResetResponses();
                        BackendProtocolVersion = ProtocolVersion.Version2;
                        CurrentState           = NpgsqlClosedState.Instance;

                        // Get a raw connection, possibly SSL...
                        CurrentState.Open(this);
                        // Establish protocol communication and handle authentication...
                        CurrentState.Startup(this);
                    }
                }
            }

            // Check for errors and do the Right Thing.
            // FIXME - CheckErrors needs to be moved to Connector
            CheckErrors();

            _backend_keydata = _mediator.BackendKeyData;

            // Change the state of connection to open and ready.
            _connection_state = ConnectionState.Open;
            CurrentState      = NpgsqlReadyState.Instance;

            String ServerVersionString = String.Empty;

            // First try to determine backend server version using the newest method.
            if (((NpgsqlParameterStatus)_mediator.Parameters["__npgsql_server_version"]) != null)
            {
                ServerVersionString = ((NpgsqlParameterStatus)_mediator.Parameters["__npgsql_server_version"]).ParameterValue;
            }


            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersionString.Length == 0)
            {
                NpgsqlCommand command = new NpgsqlCommand("select version();set DATESTYLE TO ISO;", this);
                ServerVersionString = PGUtil.ExtractServerVersion((String)command.ExecuteScalar());
            }

            // Cook version string so we can use it for enabling/disabling things based on
            // backend version.
            ServerVersion = PGUtil.ParseServerVersion(ServerVersionString);

            // Adjust client encoding.

            //NpgsqlCommand commandEncoding1 = new NpgsqlCommand("show client_encoding", _connector);
            //String clientEncoding1 = (String)commandEncoding1.ExecuteScalar();

            if (ConnectionString.ToString(ConnectionStringKeys.Encoding, ConnectionStringDefaults.Encoding).ToUpper() == "UNICODE")
            {
                Encoding = Encoding.UTF8;
                NpgsqlCommand commandEncoding = new NpgsqlCommand("SET CLIENT_ENCODING TO UNICODE", this);
                commandEncoding.ExecuteNonQuery();
            }

            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            ProcessServerVersion();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }
Beispiel #50
0
        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ServerVersion = null;
            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.

            _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown)
                                                                                  ? ProtocolVersion.Version3
                                                                                  : settings.Protocol;

            // Reset state to initialize new connector in pool.
            CurrentState = NpgsqlClosedState.Instance;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this);
            try
            {
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }
            catch (NpgsqlException ne)
            {
                // Check for protocol not supported.  If we have been told what protocol to use,
                // we will not try this step.
                if (settings.Protocol != ProtocolVersion.Unknown)
                {
                    throw;
                }
                // If we attempted protocol version 3, it may be possible to drop back to version 2.
                if (BackendProtocolVersion != ProtocolVersion.Version3)
                {
                    throw;
                }
                NpgsqlError Error0 = (NpgsqlError)ne.Errors[0];

                // If NpgsqlError..ctor() encounters a version 2 error,
                // it will set its own protocol version to version 2.  That way, we can tell
                // easily if the error was a FATAL: protocol error.
                if (Error0.BackendProtocolVersion != ProtocolVersion.Version2)
                {
                    throw;
                }
                // Try using the 2.0 protocol.
                _mediator.ResetResponses();
                BackendProtocolVersion = ProtocolVersion.Version2;
                CurrentState           = NpgsqlClosedState.Instance;

                // Get a raw connection, possibly SSL...
                CurrentState.Open(this);
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }

            // Change the state of connection to open and ready.
            State        = ConnectionState.Open;
            CurrentState = NpgsqlReadyState.Instance;

            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersion == null)
            {
                using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this))
                {
                    ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar()));
                }
            }

            StringBuilder sbInit = new StringBuilder();

            // Adjust client encoding.
            NpgsqlParameterStatus clientEncodingParam = null;

            if (!ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) ||
                !string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase))
            {
                sbInit.AppendLine("SET CLIENT_ENCODING TO UTF8;");
            }

            if (!string.IsNullOrEmpty(settings.SearchPath))
            {
                // TODO: Add proper message when finding a semicolon in search_path.
                // This semicolon could lead to a sql injection security hole as someone could write in connection string:
                // searchpath=public;delete from table; and it would be executed.

                if (settings.SearchPath.Contains(";"))
                {
                    throw new InvalidOperationException();
                }

                sbInit.AppendLine("SET SEARCH_PATH=" + settings.SearchPath + ";");
            }

            if (!string.IsNullOrEmpty(settings.ApplicationName))
            {
                if (!SupportsApplicationName)
                {
                    //TODO
                    //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported"));
                    throw new InvalidOperationException("ApplicationName not supported.");
                }

                if (settings.ApplicationName.Contains(";"))
                {
                    throw new InvalidOperationException();
                }

                sbInit.AppendLine("SET APPLICATION_NAME='" + settings.ApplicationName.Replace('\'', '-') + "';");
            }

            /*
             * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made
             * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it.
             * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here.
             * This only works on postgresql servers where the ssl renegotiation settings is supported of course.
             * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information.
             */
            sbInit.AppendLine("SET ssl_renegotiation_limit=0;");

            /*
             * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3.
             * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least.
             * Check bug report #1010992 for more information.
             */
            sbInit.AppendLine("SET extra_float_digits=3;");
            try
            {
                new NpgsqlCommand(sbInit.ToString(), this).ExecuteBlind();
            }
            catch
            {
                foreach (var line in sbInit.ToString().Split(Environment.NewLine.ToCharArray()))
                {
                    try
                    {
                        if (line.Length > 0)
                        {
                            new NpgsqlCommand(line, this).ExecuteBlind();
                        }
                    }
                    catch { }
                }
            }

            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            ProcessServerVersion();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }
Beispiel #51
0
        /*/// <value>Counts the numbers of Connections that share
         * /// this Connector. Used in Release() to decide wether this
         * /// connector is to be moved to the PooledConnectors list.</value>
         * // internal int mShareCount;*/

        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ServerVersion = null;
            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.

            // Reset state to initialize new connector in pool.
            CurrentState = NpgsqlClosedState.Instance;

            // Keep track of time remaining; Even though there may be multiple timeout-able calls,
            // this allows us to still respect the caller's timeout expectation.
            int      connectTimeRemaining = this.ConnectionTimeout * 1000;
            DateTime attemptStart         = DateTime.Now;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this, connectTimeRemaining);
            try
            {
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this, settings);
            }
            catch (NpgsqlException)
            {
                if (_stream != null)
                {
                    try
                    {
                        _stream.Dispose();
                    }
                    catch
                    {
                    }
                }

                throw;
            }

            // Change the state of connection to open and ready.
            _connection_state = ConnectionState.Open;
            CurrentState      = NpgsqlReadyState.Instance;

            // After attachment, the stream will close the connector (this) when the stream gets disposed.
            _baseStream.AttachConnector(this);

            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersion == null)
            {
                //NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this);
                //ServerVersion = new Version(PGUtil.ExtractServerVersion((string) command.ExecuteScalar()));
                using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this))
                {
                    ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar()));
                }
            }

            ProcessServerVersion();

            StringWriter sbInitQueries = new StringWriter();

            // Some connection parameters for protocol 3 had been sent in the startup packet.
            // The rest will be setted here.
            if (SupportsExtraFloatDigits3)
            {
                sbInitQueries.WriteLine("SET extra_float_digits=3;");
            }

            if (SupportsSslRenegotiationLimit)
            {
                sbInitQueries.WriteLine("SET ssl_renegotiation_limit=0;");
            }

            initQueries = sbInitQueries.ToString();

            NpgsqlCommand.ExecuteBlind(this, initQueries, 60);

            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            NativeToBackendTypeConverterOptions.OidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }