C# NpgsqlConnection.Execute方法代码示例

        private static void SetupPg()
        {
            using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "postgres")))
            {
                connection.Open();
                // drop  database 
                connection.Execute("DROP DATABASE IF EXISTS  testdb;");
                connection.Execute("CREATE DATABASE testdb  WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;");
            }
            System.Threading.Thread.Sleep(1000);

            using (var connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "testdb")))
            {
                connection.Open();
                connection.Execute(@" create table Users (Id SERIAL PRIMARY KEY, Name varchar not null, Age int not null, ScheduledDayOff int null, CreatedDate date not null default CURRENT_DATE) ");
                connection.Execute(@" create table Car (CarId SERIAL PRIMARY KEY, Id int null, Make varchar not null, Model varchar not null, Color varchar not null, AgeInYears int not null) ");
                connection.Execute(@" create table BigCar (CarId BIGSERIAL PRIMARY KEY, Make varchar not null, Model varchar not null) ");
                connection.Execute(@" alter sequence bigcar_carid_seq RESTART WITH 2147483650");
                connection.Execute(@" create table City (Name varchar not null, Population int not null) ");
                connection.Execute(@" CREATE SCHEMA Log; ");
                connection.Execute(@" create table Log.CarLog (Id SERIAL PRIMARY KEY, LogNotes varchar NOT NULL) ");
                connection.Execute(@" CREATE TABLE GUIDTest(Id uuid PRIMARY KEY,name varchar NOT NULL)");
                connection.Execute(@" create table StrangeColumnNames (ItemId Serial PRIMARY KEY, word varchar not null, colstringstrangeword varchar) ");
                connection.Execute(@" create table UserWithoutAutoIdentity (Id int PRIMARY KEY, Name varchar not null, Age int not null) ");

            }

        }

      private static void CreateDatabase()
      {
         var cxBuilder = new NpgsqlConnectionStringBuilder(ConnectionString);
         var database = cxBuilder.Database;
         cxBuilder.Database = null;

         var db = new NpgsqlConnection(cxBuilder.ToString());
         db.Execute($"DROP DATABASE IF EXISTS \"{database}\"");
         db.Execute($"CREATE DATABASE \"{database}\"");
      }

		public void Dispose()
		{
			using (var connection = new NpgsqlConnection(PostgresFixture.ConnectionString))
			{
				connection.Open();

				connection.Execute($"drop table if exists {TableBuilder.EventsName(DefaultStream.StreamName)};");
				connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(DefaultStream.StreamName)};");

				connection.Execute($"drop table if exists {TableBuilder.EventsName(SnapshotStream.StreamName)};");
				connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(SnapshotStream.StreamName)};");
			}
		}

		private void Cleanup()
		{
			using (var connection = new NpgsqlConnection(ConnectionString))
			{
				connection.Open();

				foreach (var stream in _streams)
				{
					Catch(() => connection.Execute($"drop table if exists {TableBuilder.EventsName(stream)};"));
					Catch(() => connection.Execute($"drop table if exists {TableBuilder.SnapshotsName(stream)};"));
				}
			}
		}

        public static void Init()
        {
            StoreEvents = Wireup.Init()
                //.UsingInMemoryPersistence()
                .UsingSqlPersistence("EventStore") // Connection string is in app.config
                .WithDialect(new PostgreSqlDialect())
                .EnlistInAmbientTransaction() // two-phase commit
                .InitializeStorageEngine()
                //.TrackPerformanceInstance("example")
                .UsingJsonSerialization()
                //.Compress()
                //.EncryptWith(EncryptionKey)
                //.HookIntoPipelineUsing(new[] { new AuthorizationPipelineHook() })
                //.UsingSynchronousDispatchScheduler()
                //.DispatchTo(new DelegateMessageDispatcher(DispatchCommit))
                .Build();


            var connectionString = ConfigurationManager.ConnectionStrings["EventStore"].ConnectionString;
            var connection = new NpgsqlConnection(connectionString);
            connection.Open();

            var sql = @"
CREATE TABLE IF NOT EXISTS ""LigaView"" 
(
   ""Id"" uuid NOT NULL,
   ""Name"" varchar NOT NULL
);";
            connection.Execute(sql);

            connection.Close();
        }

		private static void RecreateSchemaAndInstallObjects()
		{
			using (var connection = new NpgsqlConnection(
				ConnectionUtils.GetMasterConnectionString()))
			{
				bool databaseExists = connection.Query<bool?>(
					@"select true :: boolean from pg_database where datname = @databaseName;",
					new
					{
						databaseName = ConnectionUtils.GetDatabaseName()
					}
					).SingleOrDefault() ?? false;

				if (!databaseExists)
				{
					connection.Execute([email protected]"CREATE DATABASE ""{ConnectionUtils.GetDatabaseName()}""");
				}
			}

			using (var connection = new NpgsqlConnection(ConnectionUtils.GetConnectionString()))
			{
				if (connection.State == ConnectionState.Closed)
				{
					connection.Open();
				}

				PostgreSqlObjectsInstaller.Install(connection);
				PostgreSqlTestObjectsInitializer.CleanTables(connection);
			}
		}

 public static int Delete(IEnumerable<int> malAnimeIds, NpgsqlConnection conn, NpgsqlTransaction transaction)
 {
     string idList = string.Join(", ", malAnimeIds.Select(id => id.ToString(CultureInfo.InvariantCulture)));
     string deleteSql = string.Format("DELETE FROM mal_anime_synonym WHERE mal_anime_id IN ({0})", idList);
     int numRowsDeleted = conn.Execute(deleteSql, transaction: transaction);
     return numRowsDeleted;
 }

        private void Run()
        {
            using (var conn = new NpgsqlConnection(ApplicationConfig.CONNECTION_STRING))
            {
                var orders = conn.Query<Order>("select * from Orders where IsMatchSearched <> true or IsMatchSearched is null").ToList();
                Console.WriteLine("finding matches for " + orders.Count + " orders");
                foreach(var order in orders)
                {
                    var minAmount = 0.9 * order.Amount;
                    var maxAmount = 1.1 * order.Amount;
                    var minPrice = 0.9 * order.Price;
                    var maxPrice = 1.1 * order.Price;
                    var type = order.OrderType == "Purchase" ? "Sell" : "Purchase";
                    var matchedOrders = conn.Query<Order>("select * from Orders where IsMatchSearched = true and Price < @MaxPrice and Price > @MinPrice and Amount < @MaxAmount and Amount > @MinAmount and OrderType = @Type and CommodityType = @CommodityType and fkUserId <> @UserId",
                        new {
                            MinAmount = minAmount,
                            MaxAmount = maxAmount,
                            MinPrice = minPrice,
                            MaxPrice = maxPrice,
                            CommodityType = order.CommodityType,
                            Type = type,
                            UserId = order.fkUserId
                        }).ToList();
                    Console.WriteLine("order " + order.Id + " has "+matchedOrders.Count+" matches");

                    foreach(var matchedOrder in matchedOrders)
                    {
                        String message = "Kami menemukan order yang cocok. {0} {1} {2} sebesar {3} kg dengan harga Rp. {4}";
                        conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = order.Id, SecondId = matchedOrder.Id });
                        conn.Execute("insert into orders_matches(first_id, second_id) values (@FirstId, @SecondId)", new { FirstId = matchedOrder.Id, SecondId = order.Id });
                        var verb = order.OrderType == "Purchase" ? "menjual" : "membeli";
                        var commodity = conn.Query<String>("select Name from CommodityType where Id = @Id", new { Id = order.CommodityType }).FirstOrDefault();
                        var user = conn.Query<User>("select Name, PhoneNumber from Users where Id = @Id", new { Id = matchedOrder.fkUserId }).FirstOrDefault();
                        message = String.Format(message, user.Name, verb, commodity, matchedOrder.Amount, matchedOrder.Price);
                        conn.Execute("insert into Outboxes(PhoneNumber, Message) values (@PhoneNumber, @Message)",
                            new
                            {
                                PhoneNumber = user.PhoneNumber,
                                Message = message
                            }
                        );
                    }
                    conn.Execute("update Orders set IsMatchSearched = true where Id = @Id", new { Id = order.Id });
                }
            };
        }

        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'", ConnectionUtils.GetSchemaName());
            
            connection.Execute(script);
        }

		public void Execute(string stream)
		{
			var sql = Sql
				.Replace("{events-table}", TableBuilder.EventsName(stream))
				.Replace("{snapshots-table}", TableBuilder.SnapshotsName(stream));

			using (var connection = new NpgsqlConnection(_connectionString))
			{
				connection.Open();
				connection.Execute(sql);
			}
		}

		public void Execute(string stream)
		{
			var sql = Sql
				.Replace("{events-table}", stream + "_events")
				.Replace("{snapshots-table}", stream + "_snapshots");

			using (var connection = new NpgsqlConnection(_connectionString))
			{
				connection.Open();
				connection.Execute(sql);
			}
		}

        public static void Install(NpgsqlConnection connection, string schemaName = "hangfire")
        {
            if (connection == null) throw new ArgumentNullException("connection");

            Log.Info("Start installing Hangfire SQL objects...");


            int version = 3; //We started at version 3
            bool scriptFound = true;

            do
            {
                try
                {

                    var script = GetStringResource(
                        typeof (PostgreSqlObjectsInstaller).Assembly,
                        string.Format("Hangfire.PostgreSql.Install.v{0}.sql",
                            version.ToString(CultureInfo.InvariantCulture)));
                    if (schemaName != "hangfire")
                    {
                        script = script.Replace("'hangfire'", string.Format("'{0}'", schemaName))
                            .Replace(@"""hangfire""", string.Format(@"""{0}""", schemaName));
                    }

                    try
                    {
                        connection.Execute(script, commandTimeout: 120);
                    }
                    catch (NpgsqlException ex)
                    {
                        if((ex.BaseMessage ?? "") != "version-already-applied")
                        {
                            throw;
                        }
                    }
                }
                catch
                {
                    scriptFound = false;
                }

                version++;
            } while (scriptFound);

            Log.Info("Hangfire SQL objects installed.");
        }

        public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction)
        {
            string sql = @"INSERT INTO mal_anime
            (mal_anime_id, title, mal_anime_type_id, num_episodes, mal_anime_status_id, start_year, start_month, start_day,
            end_year, end_month, end_day, image_url, last_updated)
            VALUES
            (:MalAnimeId, :Title, :MalAnimeTypeId, :NumEpisodes, :MalAnimeStatusId, :StartYear, :StartMonth, :StartDay,
            :EndYear, :EndMonth, :EndDay, :ImageUrl, :LastUpdated)";

            conn.Execute(sql,
                new
                {
                    MalAnimeId = mal_anime_id,
                    Title = title,
                    MalAnimeTypeId = mal_anime_type_id,
                    NumEpisodes = num_episodes,
                    MalAnimeStatusId = mal_anime_status_id,
                    StartYear = start_year,
                    StartMonth = start_month,
                    StartDay = start_day,
                    EndYear = end_year,
                    EndMonth = end_month,
                    EndDay = end_day,
                    ImageUrl = image_url,
                    LastUpdated = last_updated
                },

                transaction);
        }

        public static void DeleteOldestUsers(long numUsers, NpgsqlConnection conn, NpgsqlTransaction transaction)
        {
            if (numUsers == 0)
            {
                return;
            }
            if (numUsers < 0)
            {
                throw new ArgumentOutOfRangeException("numUsers", numUsers, string.Format("Cannot delete {0} oldest users", numUsers));
            }

            string deleteSql = @"DELETE FROM mal_user WHERE mal_user_id IN
            (SELECT mal_user_id FROM mal_user
            ORDER BY time_added
            LIMIT :NumToDelete)";

            conn.Execute(deleteSql, new { NumToDelete = numUsers }, transaction);
        }

 public void Insert(NpgsqlConnection conn, NpgsqlTransaction transaction)
 {
     string sql = "INSERT INTO mal_user (mal_user_id, mal_name, time_added) VALUES (:MalUserId, :MalName, :TimeAdded)";
     conn.Execute(sql, new { MalUserId = mal_user_id, MalName = mal_name, TimeAdded = time_added }, transaction);
 }