Пример #1
0
        public static void BulkInsert(List <Message> messages)
        {
            var clock = new Stopwatch();

            var messagesColumns = "\"Type\"";

            string detailsColumns =
                "\"TeamName\", \"TeamNameInternational\", \"ExternalId\", \"InternationalReference\", " +
                "\"TeamNickname\", \"TeamCode\", \"TeamCodeLong\", \"TeamCodeInternational\", " +
                "\"TeamCodeLongInternational\", \"TeamNicknameInternational\", \"CountryCode\", \"CountryCodeIOC\", " +
                "\"Country\", \"Website\", \"IsHomeCompetitor\"";

            string coachesColumns = "\"FamilyName\", \"FirstName\", \"InternationalFamilyName\", " +
                                    "\"InternationalFirstName\", \"ScoreboardName\", \"TVName\", \"NickName\", " +
                                    "\"ExternalId\", \"NationalityCode\", \"NationalityCodeIOC\", \"Nationality\"";

            string teamsColumns = "\"TeamNumber\", \"DetailId\", \"CoachId\", \"AssistCoachId1\", " +
                                  "\"AssistCoachId2\", \"MessageId\"";

            string playersColumns = "\"Pno\", \"FamilyName\", \"FirstName\", \"InternationalFamilyName\", " +
                                    "\"InternationalFirstName\", \"ScoreboardName\", \"TVName\", \"NickName\", " +
                                    "\"Website\", \"DateOfBirth\", \"Height\", \"ExternalId\"," +
                                    "\"InternationalReference\", \"ShirtNumber\", \"PlayingPosition\", \"Starter\"," +
                                    "\"Captain\", \"Active\", \"NationalityCode\", \"NationalityCodeIOC\", \"Nationality\", \"TeamId\"";

            using var connection = new NpgsqlConnection(connectionString);
            connection.Open();

            using (var command = new NpgsqlCommand($"SET search_path TO public", connection))
                command.ExecuteNonQuery();

            clock.Start();

            //Details import
            using (var importer = connection
                                  .BeginBinaryImport($"COPY \"public\".\"Details\" ({detailsColumns}) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var detail in ListFilter.FilterDetails(messages))
                {
                    importer.StartRow();
                    importer.Write(detail.TeamName, NpgsqlDbType.Text);
                    importer.Write(detail.TeamCodeInternational, NpgsqlDbType.Text);
                    importer.Write(detail.ExternalId, NpgsqlDbType.Text);
                    importer.Write(detail.InternationalReference, NpgsqlDbType.Text);
                    importer.Write(detail.TeamNickname, NpgsqlDbType.Text);
                    importer.Write(detail.TeamCode, NpgsqlDbType.Text);
                    importer.Write(detail.TeamCodeLong, NpgsqlDbType.Text);
                    importer.Write(detail.TeamCodeInternational, NpgsqlDbType.Text);
                    importer.Write(detail.TeamCodeLongInternational, NpgsqlDbType.Text);
                    importer.Write(detail.TeamNicknameInternational, NpgsqlDbType.Text);
                    importer.Write(detail.CountryCode, NpgsqlDbType.Text);
                    importer.Write(detail.CountryCodeIOC, NpgsqlDbType.Text);
                    importer.Write(detail.Country, NpgsqlDbType.Text);
                    importer.Write(detail.Website, NpgsqlDbType.Text);
                    importer.Write((int)detail.IsHomeCompetitor, NpgsqlDbType.Smallint);
                }

                importer.Complete();
            }

            //Coaches import
            using (var importer = connection
                                  .BeginBinaryImport($"COPY \"public\".\"Coaches\" ({coachesColumns}) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var coach in ListFilter.FilterCoach(messages))
                {
                    importer.StartRow();
                    importer.Write(coach.FamilyName, NpgsqlDbType.Text);
                    importer.Write(coach.FirstName, NpgsqlDbType.Text);
                    importer.Write(coach.InternationalFamilyName, NpgsqlDbType.Text);
                    importer.Write(coach.InternationalFirstName, NpgsqlDbType.Text);
                    importer.Write(coach.ScoreboardName, NpgsqlDbType.Text);
                    importer.Write(coach.TVName, NpgsqlDbType.Text);
                    importer.Write(coach.NickName, NpgsqlDbType.Text);
                    importer.Write(coach.ExternalId, NpgsqlDbType.Text);
                    importer.Write(coach.NationalityCode, NpgsqlDbType.Text);
                    importer.Write(coach.NationalityCodeIOC, NpgsqlDbType.Text);
                    importer.Write(coach.Nationality, NpgsqlDbType.Text);
                }

                importer.Complete();
            }

            //Messages import
            using (var importer = connection
                                  .BeginBinaryImport($"COPY \"public\".\"Messages\" ({messagesColumns}) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var message in messages)
                {
                    importer.StartRow();
                    importer.Write(message.Type, NpgsqlDbType.Text);
                }

                importer.Complete();
            }

            //Teams import
            using (var importer = connection
                                  .BeginBinaryImport($"COPY \"public\".\"Teams\" ({teamsColumns}) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var team in ListFilter.FilterTeams(messages))
                {
                    importer.StartRow();
                    importer.Write(team.TeamNumber, NpgsqlDbType.Integer);
                    importer.Write(team.Detail.DetailId, NpgsqlDbType.Integer);
                    importer.Write(team.Coach.PersonId, NpgsqlDbType.Integer);
                    importer.Write(team.AssistCoach1.PersonId, NpgsqlDbType.Integer);
                    importer.Write(team.AssistCoach2.PersonId, NpgsqlDbType.Integer);
                    importer.Write(team.MessageId, NpgsqlDbType.Integer);
                }

                importer.Complete();
            }

            //Players import
            using (var importer = connection
                                  .BeginBinaryImport($"COPY \"public\".\"Players\" ({playersColumns}) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var player in ListFilter.FilterPlayers(messages))
                {
                    importer.StartRow();
                    importer.Write(player.Pno, NpgsqlDbType.Integer);
                    importer.Write(player.FamilyName, NpgsqlDbType.Text);
                    importer.Write(player.FirstName, NpgsqlDbType.Text);
                    importer.Write(player.InternationalFamilyName, NpgsqlDbType.Text);
                    importer.Write(player.InternationalFirstName, NpgsqlDbType.Text);
                    importer.Write(player.ScoreboardName, NpgsqlDbType.Text);
                    importer.Write(player.TVName, NpgsqlDbType.Text);
                    importer.Write(player.NickName, NpgsqlDbType.Text);
                    importer.Write(player.Website, NpgsqlDbType.Text);
                    importer.Write(player.DateOfBirth, NpgsqlDbType.Timestamp);
                    importer.Write(player.Height, NpgsqlDbType.Double);
                    importer.Write(player.ExternalId, NpgsqlDbType.Text);
                    importer.Write(player.InternationalReference, NpgsqlDbType.Text);
                    importer.Write(player.ShirtNumber, NpgsqlDbType.Text);
                    importer.Write(player.PlayingPosition, NpgsqlDbType.Text);
                    importer.Write((int)player.Starter, NpgsqlDbType.Smallint);
                    importer.Write((int)player.Captain, NpgsqlDbType.Smallint);
                    importer.Write((int)player.Active, NpgsqlDbType.Smallint);
                    importer.Write(player.NationalityCode, NpgsqlDbType.Text);
                    importer.Write(player.NationalityCodeIOC, NpgsqlDbType.Text);
                    importer.Write(player.Nationality, NpgsqlDbType.Text);
                    importer.Write(player.TeamId, NpgsqlDbType.Integer);
                }

                importer.Complete();
            }

            clock.Stop();
            connection.Close();

            Console.WriteLine($"Messages was successfully added to database. Elapsed time: {clock.ElapsedMilliseconds} ms.");
            logger.Info($"Messages was successfully added to database. Elapsed time: {clock.ElapsedMilliseconds} ms.");
        }