DSMR Reader PostgreSQL2MySQL

PostgreSQL2MySQL.cs

        public static async void DownloadData()
        {
            await using NpgsqlConnection conn = new("Host=192.168.199.203;Username=dsmrreader;Password=dsmrreader;Database=dsmrreader");
            await conn.OpenAsync();

            File.Delete("result.sql");
            File.WriteAllText("result.sql", "SET FOREIGN_KEY_CHECKS=0;\r\n");

            List<String> tablesFound = new();
            using NpgsqlCommand cmd = new("select table_name from information_schema.tables where table_type = 'BASE TABLE' AND table_schema = 'public'", conn);
            {
                await using NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                    tablesFound.Add(reader.GetString(0));
            }
            
            foreach (String table in tablesFound)
            {
                using NpgsqlCommand cmdContent = new("select * from " + table + "", conn);
                await using NpgsqlDataReader readerContent = await cmdContent.ExecuteReaderAsync();
                List<(Type, String)> fields = new();
                List<List<String>> results = new();
                for (Int32 i = 0; i < readerContent.FieldCount; i++)
                    fields.Add((readerContent.GetFieldType(i), readerContent.GetName(i)));
                while (await readerContent.ReadAsync())
                {
                    List<String> resultItems = new();
                    for (Int32 i = 0; i < fields.Count; i++)
                    {
                        if (readerContent.IsDBNull(i))
                        {
                            resultItems.Add("NULL");
                            continue;
                        }

                        if (fields[i].Item1 == typeof(String))
                            resultItems.Add("'" + readerContent.GetString(i).EscapeMySQLString() + "'");
                        else if (fields[i].Item1 == typeof(Int16))
                            resultItems.Add(readerContent.GetInt16(i).ToString(CultureInfo.InvariantCulture));
                        else if (fields[i].Item1 == typeof(Int32))
                            resultItems.Add(readerContent.GetInt32(i).ToString(CultureInfo.InvariantCulture));
                        else if (fields[i].Item1 == typeof(Decimal))
                        {
                            String result = readerContent.GetDecimal(i).ToString(CultureInfo.InvariantCulture);
                            if (result.Contains("."))
                                result = result.TrimEnd('0').TrimEnd('.');
                            resultItems.Add(result);
                        }
                        else if (fields[i].Item1 == typeof(TimeSpan))
                            resultItems.Add("'" + readerContent.GetTimeSpan(i).ToMySqlTime() + "'");
                        else if (fields[i].Item1 == typeof(DateTime))
                        {
                            Type specificFieldType = readerContent.GetProviderSpecificFieldType(i);
                            if (specificFieldType == typeof(NpgsqlDate))
                            {
                                NpgsqlDate date = readerContent.GetDate(i);
                                resultItems.Add("'" + date.Year.ToString(CultureInfo.InvariantCulture) + "-" + date.Month.ToString(CultureInfo.InvariantCulture) + "-" + date.Day.ToString(CultureInfo.InvariantCulture) + "'");
                            }
                            else
                            {
                                DateTimeOffset dateTimeOffset = readerContent.GetFieldValue<DateTimeOffset>(i);
                                resultItems.Add("'" + dateTimeOffset.DateTime.AddHours(dateTimeOffset.Offset.Hours * -1).ToMySql() + "'");
                            }
                        }
                        else if (fields[i].Item1 == typeof(Boolean))
                            resultItems.Add(readerContent.GetBoolean(i) ? "1" : "0");
                        else
                            throw new Exception("Unknown type: " + fields[i].Item1.FullName);
                    }
                    results.Add(resultItems);
                }

                List<List<List<String>>> chunkedResults = results.ChunkToList(500);
                List<String> resultLines = new(chunkedResults.Count);
                foreach (List<List<String>> chunkedResult in chunkedResults)
                {
                    StringBuilder stringBuilder = new();
                    stringBuilder.Append("INSERT INTO `dsmrreader`.`").Append(table).Append("` (`").Append(String.Join("`,`", fields.Select(x => x.Item2))).Append("`)VALUES");
                    Boolean firstRun = true;
                    foreach (List<String> list in chunkedResult)
                    {
                        if (!firstRun)
                            stringBuilder.Append(",");
                        else
                            firstRun = false;

                        stringBuilder.Append("(");
                        Boolean firstItem = true;
                        foreach (String field in list)
                        {
                            if (!firstItem)
                                stringBuilder.Append(",");
                            else
                                firstItem = false;
                            stringBuilder.Append(field);
                        }
                        stringBuilder.Append(")");
                    }
                    stringBuilder.Append(";");
                    resultLines.Add(stringBuilder.ToString());
                }
                File.AppendAllLines("result.sql", resultLines, Encoding.UTF8);
            }
        }
        
                public static String ToMySql(this DateTime input, DateTime? minDate = null)
        {
            minDate ??= DateTime.MinValue;

            //2012-08-31 13:35:38
            return input.Equals(minDate) ? "0000-00-00 00:00:00" : input.ToString("yyyy-MM-dd HH:mm:ss");
        }
        public static List<List<T>> ChunkToList<T>(this List<T> source, Int32 chunksize)
        {
            return source.Select((x, i) => new { Index = i, Value = x }).GroupBy(x => x.Index / chunksize).Select(x => x.Select(v => v.Value).ToList()).ToList();
        }
        public static String EscapeMySQLString(this String input)
        {
            return input == null ? "" : MySqlHelper.EscapeString(input).Replace("\r", @"\r").Replace("\n", @"\n");
        }
        public static String ToMySqlTime(this TimeSpan input, TimeSpan? minTime = null)
        {
            minTime ??= TimeSpan.Zero;

            //13:35:38
            return input == minTime ? "00:00:00" : input.ToString(@"hh\:mm\:ss\.fffffff");
        }

Requires the Npgsql and MySql.Data nuget packafes