using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Renci.SshNet;
using Npgsql;

namespace acc_pgsql
{
    public static class PGSqlHelper
    {
        static string _ipaddress = "192.168.0.108";
        static string _username = "mcarman";
        static string _pwd = "@ng31F@rm0823262";
        static string _localip = "127.0.0.1";
        static uint _port = (uint)5432;
        static string _connString = "Server={0};Database=accessdb_v2;Port={1};User Id={2};Password={3};";
        //allway wrap call in try catch
        public static System.Data.DataSet RunPGSql( List<string> sql)
        {
            uint port = (uint)_port;
            
            using (SshClient client = new SshClient(_ipaddress, _username, _pwd))
            {
                System.Data.DataSet ds = new System.Data.DataSet();
                client.Connect();
                ForwardedPortLocal tunnel = new ForwardedPortLocal(_localip, _localip, _port);
                client.AddForwardedPort(tunnel);
                if (client.IsConnected)
                {
                    tunnel.Start();
                    string connStr = string.Format(_connString, tunnel.BoundHost, tunnel.BoundPort, _username, _pwd);
                    try
                    {
                        using (NpgsqlConnection conn = new NpgsqlConnection(connStr))
                        {
                            
                            conn.Open();
                            NpgsqlCommand cmd = conn.CreateCommand();
                            cmd.CommandType = System.Data.CommandType.Text;
                            foreach (string s in sql)
                            {
                                System.Data.DataTable tbl = new System.Data.DataTable();
                                cmd.CommandText = s;
                                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
                                adapter.Fill(tbl);
                                ds.Tables.Add(tbl);
                            }
                            return ds;
                        }
                    }
                    catch (Exception ex)
                    {
                        FileHelper.Log(ex.Message, true);
                        throw ex;
                    }

                }
                else
                {
                    throw new Exception("Unable to establish Connection to remote database.");
                }
            }
        }
    }
}