C# NpgsqlCommand.ExecuteScalar方法代码示例

 protected void Button1_Click(object sender, EventArgs e)
 {
     string checks;
     string checkw;
     string id_sali = DropDownList1.SelectedItem.ToString();
     string id_wyp = DropDownList2.SelectedItem.ToString();
     NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
     conn.Open();
     NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
     NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
     checks = (String)check1.ExecuteScalar();
     checkw = (String)check2.ExecuteScalar();
     if (checks == id_sali && checkw == id_wyp)
     {
         Label2.Text = "";
         Label3.Text = "Wyposażenie jest już przypisane do sali!";
     }
     else if (checkw != id_wyp)
     {
         NpgsqlCommand add = new NpgsqlCommand("insert into wyp_sali values ('" + id_wyp + "', '" + id_sali + "')", conn);
         add.ExecuteScalar();
         conn.Close();
         Label2.Text = "Dodano wyposażenie do sali!";
         Label3.Text = "";
     }
 }

 public 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 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);
                }
            }
        }

        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();
            }
        }

 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;
     }
 }

 public void Incluir(Model_Vo_Sala pSala)
 {
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         //command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandText = "insert into sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);";
         cmd.Parameters.AddWithValue("@nome", pSala.Nome);
         cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
         cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString());
         cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
         cn.Open();
         pSala.Id = Convert.ToInt32(cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }

 public void 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 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();
            }
        }

        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;
            }
        }

 //создание формы
 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);
     }
 }

        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 protected] AND [email protected]");
            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 = "";
     }
 }

        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();
        }