Data_migration/migrate_data/acc_pgsql/PGSqlGenerator.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace acc_pgsql
{
    public static class PGSqlGenerator
    {
        static string _tblDescription = @"select column_name, data_type, character_maximum_length, column_default, is_nullable from INFORMATION_SCHEMA.COLUMNS where table_name = '{0}' order by ordinal_position;";

        public struct PGColumn
        {
            public string name { get; set; }
            public bool singlequotesneeded { get; set; }//This is for the value when selecting
        }
        public static void GenerateSeedData(string tblName)
        {
            StringBuilder sb = new StringBuilder();
            string sql = string.Format(_tblDescription, tblName);
            List<string> list = new List<string>();
            list.Add(sql);
            list.Add(string.Format("select * from \"{0}\"", tblName));
            System.Data.DataSet ds = PGSqlHelper.RunPGSql(list);
            string insert = "Insert Into \"" + tblName + "\"(";
            List<PGColumn> fields = new List<PGColumn>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                PGColumn clmn = new PGColumn();
                clmn.name = row.ItemArray[0].ToString();
                switch (row.ItemArray[1].ToString())
                { 
                    case "bigint":
                        clmn.singlequotesneeded = false;
                        break;
                    case "timestamp with time zone":
                        clmn.singlequotesneeded = true;
                        break;
                    case "text":
                        clmn.singlequotesneeded = true;
                        break;
                    case "boolean":
                        clmn.singlequotesneeded = false;
                        break;
                    default:
                        clmn.singlequotesneeded = true;
                        break;
                }
                fields.Add(clmn);
            }
            string topline = string.Format("Insert Into \"{0}\"(", tblName);
            string bottomline = "values(";
            for(int i = 0; i < fields.Count; i++)
            {

                if (i == 0)
                {
                    topline = topline + "\"" + fields[i].name + "\"";
                    if (fields[i].singlequotesneeded)
                        bottomline = bottomline + string.Format("'{0}'", "|"+i+"|");
                    else bottomline = bottomline + string.Format("{0}", "|" + i + "|");
                }
                else
                {
                    topline = topline + ", \"" + fields[i].name + "\"";
                    if (fields[i].singlequotesneeded)
                        bottomline = bottomline + string.Format(", '{0}'", "|" + i + "|");
                    else bottomline = bottomline + string.Format(", {0}", "|" + i + "|");
                }
                
            }
            topline = topline + ")";
            bottomline = bottomline + ");";
            string statment = string.Format("{0} {1}", topline, bottomline);
            List<string> insertstatements = new List<string>();
            foreach (DataRow row2 in ds.Tables[1].Rows)
            { 
                
                for(int c = 0; c < ds.Tables[1].Columns.Count; c++)
                {
                    string rep = string.Format("|{0}|", c);
                    statment = statment.Replace(rep, row2.ItemArray[c].ToString());
                }
                insertstatements.Add(statment);
                sb.AppendLine(statment);
            }
            Console.WriteLine(sb.ToString());
        }
    }
}