C# Npgsql.NpgsqlConnection類代碼示例

 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 CleanTables(NpgsqlConnection connection)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            var script = GetStringResource(
                typeof (PostgreSqlTestObjectsInitializer).Assembly,
                "Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));

			//connection.Execute(script);

			using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
			using (var command = new NpgsqlCommand(script, connection, transaction))
			{
				command.CommandTimeout = 120;
				try
				{
					command.ExecuteNonQuery();
					transaction.Commit();
				}
				catch (NpgsqlException ex)
				{
					throw;
				}
			}
		}

        static void ClearCatalogue(string connString)
        {
            try
            {
                NpgsqlConnection conn = new NpgsqlConnection(connString);
                conn.Open();
                Console.Clear();

                string clearTable = "DELETE FROM phone_book";

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

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

                Console.ReadKey();
                Console.Clear();
                conn.Close();
            }
            catch (Exception msg)
            {
                Console.WriteLine(msg.ToString());
                throw;
            }
        }

 void Initialize()
 {
     string connect = "Server=127.0.0.1;Port=5432;User Id=s;Database=practice;";
     var connection = new NpgsqlConnection(connect);
     if(connection.State == ConnectionState.Closed) { connection.Open(); }
     users = new Table("users", connection);
 }

        // GET api/values
        public IEnumerable<string> Get()
        {
            // return new string[] { "value1", "value2" };
               var result = new List<string>();
            using (var conn = new NpgsqlConnection())
            {
                conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty";
                conn.Open();

                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText =
                        "SELECT name,amenity, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'pub\' LIMIT 10;";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(reader.GetString(2));
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Initialises the estatedata class.
        /// </summary>
        /// <param name="connectionString">connectionString.</param>
        public void Initialise(string connectionString)
        {
            if (!string.IsNullOrEmpty(connectionString))
            {
                m_connectionString = connectionString;
                _Database = new PGSQLManager(connectionString);
            }

            //Migration settings
            using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
            {
                conn.Open();
                Migration m = new Migration(conn, GetType().Assembly, "EstateStore");
                m.Update();
            }

            //Interesting way to get parameters! Maybe implement that also with other types
            Type t = typeof(EstateSettings);
            _Fields = t.GetFields(BindingFlags.NonPublic |
                                  BindingFlags.Instance |
                                  BindingFlags.DeclaredOnly);

            foreach (FieldInfo f in _Fields)
            {
                if (f.Name.Substring(0, 2) == "m_")
                    _FieldMap[f.Name.Substring(2)] = f;
            }
        }

 public static void AccessMaster(string Action, string TableName, Hashtable hash)
 {
     using(NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING))
     {
       string query = string.Format(@"SELECT * FROM ""{0}""", TableName);
       using(NpgsqlCommand cmd = new NpgsqlCommand(@query, con))
       {
     NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
     adapter.SelectCommand = cmd;
     NpgsqlCommand actionCommand = null;
     if(Action == "Entry"){
       actionCommand = (new NpgsqlCommandBuilder(adapter)).GetInsertCommand();
     } else if(Action == "Modify"){
       actionCommand = (new NpgsqlCommandBuilder(adapter)).GetUpdateCommand();
     } else if(Action == "Remove"){
       actionCommand = (new NpgsqlCommandBuilder(adapter)).GetDeleteCommand();
     }
     try {
       foreach(NpgsqlParameter param in actionCommand.Parameters){
         if(hash[param.SourceColumn] != null)
           param.Value = hash[param.SourceColumn];
       }
       actionCommand.ExecuteNonQuery();
     } catch (Exception ex) {
       MessageBox.Show(string.Format("{0}", ex.Message));
       MessageBox.Show(string.Format("{0}", ex.StackTrace.ToString()));
     }
       }
     }
 }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string check;
            string id_wyp = TextBox1.Text;
            string nazwa = TextBox2.Text;
            string opis = TextBox3.Text;

            NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
            conn.Open();
            NpgsqlCommand check1 = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
            check = (String)check1.ExecuteScalar();
            if (check != id_wyp)
            {
                NpgsqlCommand add = new NpgsqlCommand("insert into wyposazenie values ('" + id_wyp + "', '" + nazwa + "', '" + opis + "')", conn);
                add.ExecuteScalar();
                conn.Close();
                TextBox1.Text = "";
                TextBox2.Text = "";
                TextBox3.Text = "";
                Label2.Text = "Dodano wyposażenie!";
                Label3.Text = "";
            }
            else if (check == id_wyp)
            {
                Label2.Text = "";
                Label3.Text = "Wyposażenie już istnieje!";
            }
        }

 public void SetUp()
 {
     Connection = new NpgsqlConnection(IntegrationTestOptions.Postgres.ConnectionString);
     Processor = new PostgresProcessor(Connection, new PostgresGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new PostgresDbFactory());
     Quoter = new PostgresQuoter();
     Connection.Open();
 }

 public PostgresProcessor(NpgsqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
     : base(generator, announcer, options)
 {
     Connection = connection;
     connection.Open();
     Transaction = connection.BeginTransaction();
 }

 public pgWorker(int pos, string dsn, 
     ProgressChangedEventHandler onProgress,
     RunWorkerCompletedEventHandler onComplete)
 {
     this.thNo = pos;
     this.pg_conn = new NpgsqlConnection(dsn);
     try
     {
         pg_conn.Open();
         pg_error = "";
     }
     catch (Exception ex)
     {
         pg_conn = null;
         pg_error = ex.Message;
     }
     if (pg_conn != null)
     {
         this.bw = new BackgroundWorker();
         this.bw.WorkerReportsProgress = true;
         this.bw.WorkerSupportsCancellation = true;
         this.bw.DoWork += this.bwDoWork;
         this.bw.ProgressChanged += onProgress;
         this.bw.RunWorkerCompleted += onComplete;
     }
 }

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

        // Zu Quell- und Zieldatenbank verbinden
        private void connectFischFaunaButton_Click(object sender, EventArgs e)
        {
            DatabaseConnection sourceCon = (DatabaseConnection)sourceDatabaseConnetions.SelectedValue;
            DatabaseConnection targetCon = (DatabaseConnection)targetDatabaseConnetions.SelectedValue;

            NpgsqlConnectionStringBuilder pgConStrBuilder = new NpgsqlConnectionStringBuilder();
            pgConStrBuilder.Host = sourceCon.ServerAddress;
            pgConStrBuilder.UserName = sourceCon.UserName;
            pgConStrBuilder.Password = sourceCon.Password;
            pgConStrBuilder.Database = sourceCon.Database;

            MySqlConnectionStringBuilder mySqlConStrBuilder = new MySqlConnectionStringBuilder();
            mySqlConStrBuilder.Server = targetCon.ServerAddress;
            mySqlConStrBuilder.UserID = targetCon.UserName;
            mySqlConStrBuilder.Password = targetCon.Password;
            mySqlConStrBuilder.Database = targetCon.Database;
            mySqlConStrBuilder.AllowZeroDateTime = true;

            _sourceCon = new NpgsqlConnection(pgConStrBuilder.ToString());
            _targetCon = new MySqlConnection(mySqlConStrBuilder.ToString());

            _mainLogic = new MainLogic(_sourceCon, _targetCon);
            _mainLogic.CheckForImportedFieldInMySql();

            FillImportsCombobox();
            FillImportUsersCombobox();
            FillRecordQualityCombobox();
            FillSourceTypeCombobox();
            FillCountryCombobox();

            PreSelectTestData();

            groupBox2.Enabled = true;
        }

 public IEnumerable<Routine> GetRoutines()
 {
     var list = new List<Routine>();
     using (var sqlConnection = new NpgsqlConnection(connectionString))
     {
         using (var cmd = new NpgsqlCommand(@"
     SELECT routine_name
     FROM INFORMATION_SCHEMA.routines
     WHERE
     routine_schema <> 'pg_catalog'
     and routine_schema <>'information_schema'
     ", sqlConnection))
         {
             sqlConnection.Open();
             cmd.CommandType = CommandType.Text;
             using (var reader = cmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     list.Add(new Routine(reader.GetString(0)));
                 }
             }
         }
     }
     return list;
 }

        public Sequence(NpgsqlConnection conn,RichTextBox t)
        {
            InitializeComponent();
            this.conn = conn;
            this.t = t;
            comboBox1.Items.Add("CACHE");
            comboBox1.Items.Add("NO CACHE");
            PC = new Postgres_Connection();

            richTextBox1.StyleResetDefault();
            richTextBox1.Styles[Style.Default].Font = "Consolas";
            richTextBox1.Styles[Style.Default].Size = 10;
            richTextBox1.StyleClearAll();
            richTextBox1.Styles[Style.Cpp.Default].ForeColor = Color.Silver;
            richTextBox1.Styles[Style.Cpp.Comment].ForeColor = Color.FromArgb(0, 128, 0); // Green
            richTextBox1.Styles[Style.Cpp.CommentLine].ForeColor = Color.FromArgb(0, 128, 0); // Green
            richTextBox1.Styles[Style.Cpp.CommentLineDoc].ForeColor = Color.FromArgb(128, 128, 128); // Gray
            richTextBox1.Styles[Style.Cpp.Number].ForeColor = Color.Red;
            richTextBox1.Styles[Style.Cpp.Word].ForeColor = Color.Blue;
            richTextBox1.Styles[Style.Cpp.Word2].ForeColor = Color.Fuchsia;
            richTextBox1.Styles[Style.Cpp.String].ForeColor = Color.FromArgb(163, 21, 21); // Red
            richTextBox1.Styles[Style.Cpp.Character].ForeColor = Color.FromArgb(163, 21, 21); // Red
            richTextBox1.Styles[Style.Cpp.Verbatim].ForeColor = Color.FromArgb(163, 21, 21); // Red
            richTextBox1.Styles[Style.Cpp.Operator].ForeColor = Color.Silver;
            richTextBox1.Styles[Style.Cpp.Preprocessor].ForeColor = Color.Purple;
            richTextBox1.Lexer = Lexer.Cpp;
            richTextBox1.Margins[0].Width = 16;

            //richTextBox1.SetKeywords(0, "abort absolute access action add admin after aggregate all also alter always analyse analyze and any array as asc assertion assignment asymmetric at attribute authorization backward before begin between bigint binary bit boolean both by cache called cascade cascaded case cast catalog chain char character characteristics check checkpoint class close cluster coalesce collate collation column comment comments commit committed concurrently configuration connection constraint constraints content continue conversion copy cost create cross csv current current_catalog current_date current_role current_schema current_time current_timestamp current_user cursor cycle data database day deallocate dec decimal declare default defaults deferrable deferred definer delete delimiter delimiters desc dictionary disable discard distinct do document domain double drop each else enable encoding encrypted end enum escape event except exclude excluding exclusive execute exists explain extension external extract false family fetch filter first float following for force foreign forward freeze from full function functions global grant granted greatest group handler having header hold hour identity if ilike immediate immutable implicit in including increment index indexes inherit inherits initially inline inner inout input insensitive insert instead int integer intersect interval into invoker is isnull isolation join key label language large last lateral lc_collate lc_ctype leading leakproof least left level like limit listen load local localtime localtimestamp location lock mapping match materialized maxvalue minute minvalue mode month move name names national natural nchar next no none not nothing notify notnull nowait null nullif nulls numeric object of off offset oids on only operator option options or order ordinality out outer over overlaps overlay owned owner parser partial partition passing password placing plans position preceding precision prepare prepared preserve primary prior privileges procedural procedure program quote range read real reassign recheck recursive ref references refresh reindex relative release rename repeatable replace replica reset restart restrict returning returns revoke right role rollback row rows rule savepoint schema scroll search second security select sequence sequences serializable server session session_user set setof share show similar simple smallint snapshot some stable standalone start statement statistics stdin stdout storage strict strip substring symmetric sysid system table tables tablespace temp template temporary text then time timestamp to trailing transaction treat trigger trim true truncate trusted type types unbounded uncommitted unencrypted union unique unknown unlisten unlogged until user using vacuum valid validate validator value values varchar variadic varying verbose version view views volatile when where whitespace window with within without work wrapper write xml xmlattributes xmlconcat xmlelement xmlexists xmlforest xmlparse xmlpi xmlroot xmlserialize year yes zone ABORT ABSOLUTE ACCESS ACTION ADD ADMIN AFTER AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION BACKWARD BEFORE BEGIN BETWEEN BIGINT BINARY BIT BOOLEAN BOTH BY CACHE CALLED CASCADE CASCADED CASE CAST CATALOG CHAIN CHAR CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS CONTENT CONTINUE CONVERSION COPY COST CREATE CROSS CSV CURRENT CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATA DATABASE DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE DELIMITER DELIMITERS DESC DICTIONARY DISABLE DISCARD DISTINCT DO DOCUMENT DOMAIN DOUBLE DROP EACH ELSE ENABLE ENCODING ENCRYPTED END ENUM ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT FALSE FAMILY FETCH FILTER FIRST FLOAT FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS GLOBAL GRANT GRANTED GREATEST GROUP HANDLER HAVING HEADER HOLD HOUR IDENTITY IF ILIKE IMMEDIATE IMMUTABLE IMPLICIT IN INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE INNER INOUT INPUT INSENSITIVE INSERT INSTEAD INT INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN KEY LABEL LANGUAGE LARGE LAST LATERAL LC_COLLATE LC_CTYPE LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK MAPPING MATCH MATERIALIZED MAXVALUE MINUTE MINVALUE MODE MONTH MOVE NAME NAMES NATIONAL NATURAL NCHAR NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL NULLIF NULLS NUMERIC OBJECT OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR ORDER ORDINALITY OUT OUTER OVER OVERLAPS OVERLAY OWNED OWNER PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION PRECEDING PRECISION PREPARE PREPARED PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM QUOTE RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX RELATIVE RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE SAVEPOINT SCHEMA SCROLL SEARCH SECOND SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SNAPSHOT SOME STABLE STANDALONE START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT STRIP SUBSTRING SYMMETRIC SYSID SYSTEM TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE TRUNCATE TRUSTED TYPE TYPES UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED UNTIL USER USING VACUUM VALID VALIDATE VALIDATOR VALUE VALUES VARCHAR VARIADIC VARYING VERBOSE VERSION VIEW VIEWS VOLATILE WHEN WHERE WHITESPACE WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE XML XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLPARSE XMLPI XMLROOT XMLSERIALIZE YEAR YES ZONE");
            //richTextBox1.SetKeywords(1, "update UPDATE");
        }