C# NpgsqlConnection.Close方法代码示例

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

        public void Bug1011241_DiscardAll()
        {
            var connection = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
            connection.Open();

            if (connection.PostgreSqlVersion < new Version(8, 3, 0)
                || new NpgsqlConnectionStringBuilder(ConnectionString).Protocol == ProtocolVersion.Version2)
            {
                connection.Close();
                return;
            }

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("public", command.ExecuteScalar());

                command.CommandText = "SET SEARCH_PATH = \"$user\"";
                command.ExecuteNonQuery();
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("\"$user\"", command.ExecuteScalar());
            }
            connection.Close();

            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SHOW SEARCH_PATH";
                Assert.AreEqual("public", command.ExecuteScalar());
            }
            connection.Close();
        }

        public string canHandIn(string testID)
        {
            string result = "";
            NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
            conn.Open();
            NpgsqlCommand cmd = new NpgsqlCommand("SELECT  id, start_time, end_time FROM completed_test  where id= @testID ", conn);
            cmd.Parameters.AddWithValue("testID", int.Parse(testID));
            //cmd.Parameters.Add("testID", testID);
            NpgsqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = dr["start_time"].ToString();
                if (dr["end_time"] != null)
                {

                }
            }
            else
            {
                dr.Close();
                conn.Close();
                return "FINNS INGET TEST";
            }
            dr.Close();
            conn.Close();
            TimeSpan diffTime = DateTime.Parse(DateTime.Now.ToString()) - DateTime.Parse(result);
            if (diffTime.TotalMinutes > 29)
            {
                return "TIDEN DROG ÖVER";
            }
            return "OK";
        }

        public static void getWords()
        {
            #region Npgsql
            NpgsqlConnection conn;
            conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
                Settings.DBconnectID + ";Password=" + Settings.DBconnectPw + ";Database=endoDB;" + Settings.sslSetting);

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

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

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            da.Fill(localDB, "words");
            if (localDB.Tables["words"].Rows.Count == 0)
            { MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            conn.Close();
        }

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

        protected void CreateUser_Click(object sender, EventArgs e)
        {
            //ArrayList for emails
            ArrayList maillist = new ArrayList();

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

            //Open connection
            conn.Open();

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

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

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

            // Close connection
            conn.Close();

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

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

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

                cmd1.ExecuteNonQuery();

                conn.Close();

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

            }
        }

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

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

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

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count == 0)
            {
                conn.Close();
                lbPtNameDB.Text = "No data";
                return;
            }
            else
            {
                DataRow row = dt.Rows[0];
                lbPtNameDB.Text = row["pt_name"].ToString();
                conn.Close();
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (SiteMaster.Rank == "2" && SiteMaster.LoggedIn)
            {
                // Specify connection options and open an connection
                NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
                                        "Password=root;Database=project56;");
                conn.Open();

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

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

                //Get rows and place in ArrayList
                while (dr.Read())
                {
                    maillist.Add(dr[0]);
                    ranklist.Add(dr[2]);
                }

                // Close connection
                conn.Close();

                for(int i=0;i<maillist.Count;i++)
                {
                    maildrop.Items.Add(maillist[i].ToString()); //Add all emails to dropdown
                }
            }
        }

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

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

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

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            // quite complex sql statement
            string sql = "SELECT * FROM sala";
            // data adapter making request from our connection
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            // i always reset DataSet before i do
            // something with it.... i don't know why :-)
            ds.Reset();
            // filling DataSet with result from NpgsqlDataAdapter
            da.Fill(ds);
            // since it C# DataSet can handle multiple tables, we will select first
            dt = ds.Tables[0];
            // connect grid to DataTable
            GridView1.DataSource = dt;
            GridView1.DataBind();
            // since we only showing the result we don't need connection anymore
            conn.Close();
        }

        public static bool CreateConnection(ConnectionParams connectionParams, out NpgsqlConnection createdConnection)
        {
            bool result = false;
            string connstring = "";
            createdConnection = null;

            try
            {
                connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                               connectionParams.Server, connectionParams.Port, connectionParams.Username, connectionParams.Password, connectionParams.Database);

                createdConnection = new NpgsqlConnection(connstring);

                if(connectionParams.OpenImmidiately)
                    createdConnection.Open();

                _allConnections.Add(createdConnection);
            }
            catch (Exception exc)
            {
                if (createdConnection != null && createdConnection.State == System.Data.ConnectionState.Open)
                    createdConnection.Close();

                createdConnection = null;
            }

            return result;
        }

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

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

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

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

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

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

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

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

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

            // 处理DataSet中的每一行数据.
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                // 将检索出来的数据，输出到屏幕上.
                Console.WriteLine("NO:{0} ;  Date:{1} ; Money:{2}   ",
                    testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
                    );
            }
        }

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