resequenced_pgsql/PGHelper.cs

using Npgsql;
using Renci.SshNet;
using System.Data;

namespace resequence_pgsql
{
    public class PGHelper : IDisposable
    {
        string _ipaddress = "192.168.0.108";
        string _username = "mcarman";
        string _pwd = "@ng31F@rm0823262";
        string _localip = "127.0.0.1";
        uint _port = (uint)5432;
        string _connString = "Server={0};Database={1};Port={2};User Id={3};Password={4};";
        string _db = "cm";
        SshClient _client;
        ForwardedPortLocal _tunnel;

        public PGHelper()
        {
            _client = new SshClient(_ipaddress, _username, _pwd);
            _client.Connect();
            _tunnel = new ForwardedPortLocal(_localip, _localip, _port);
            _client.AddForwardedPort(_tunnel);
            _tunnel.Start();
            _connString = string.Format(_connString, _tunnel.BoundHost, _db, _tunnel.BoundPort, _username, _pwd);
        }

        public PGHelper(string ipaddress, string username, string pwd, string localip, int port, string db)
        {
            _ipaddress = ipaddress;
            _username = username;
            _pwd = pwd;
            _localip = localip;
            _port = (uint)port;
            _db = db;
            _client = new SshClient(_ipaddress, _username, _pwd);
            _client.Connect();
            _tunnel = new ForwardedPortLocal(_localip, _localip, _port);
            _client.AddForwardedPort(_tunnel);
            _tunnel.Start();
            _connString = string.Format(_connString, _tunnel.BoundHost, _db, _tunnel.BoundPort, _username, _pwd);

        }
        public void ResequenceTables()
        {
            if (_client.IsConnected)
            {
                try
                {
                    using (NpgsqlConnection conn = new NpgsqlConnection(_connString))
                    {
                        conn.Open();
                        NpgsqlCommand cmd = conn.CreateCommand();
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandText = "SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;";
                        DataTable tbl = new DataTable();
                        NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
                        adapter.Fill(tbl);
                        foreach (DataRow row in tbl.Rows)
                        {
                            cmd.CommandText = string.Format("SELECT last_value + 1 from \"{0}\"", row[0]);
                            int seqVal = Convert.ToInt32(cmd.ExecuteScalar());
                            cmd.CommandText = string.Format("ALTER SEQUENCE public.\"{0}\" RESTART WITH {1};", row[0], seqVal);
                            cmd.ExecuteScalar();
                        }
                    }
                }
                catch { throw; }
            }
            else { throw new Exception("There was a problem with your ssh connection."); }
        }


        private bool isDisposed = false;
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected bool Disposed { get; private set; }
        protected virtual void Dispose(bool disposing)
        {
            if (!isDisposed)
            {
                if (disposing)
                {
                    if (_client != null) _client.Dispose();
                }
            }
            isDisposed = true;
        }
    }
}