PostgreSQL & PostGis Operations

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Npgsql;

namespace postgreSqlThaboot
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void executeCommand(string commandType,string commandSentence )
        {
            try
            {
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
                info.FileName = "C:\\Program Files\\PostgreSQL\\8.3\\bin\\" + commandType + ".exe ";
                info.Arguments = commandSentence;
                info.CreateNoWindow = true;
                info.UseShellExecute = false;
                System.Diagnostics.Process proc = new System.Diagnostics.Process();
                proc.StartInfo = info;
                proc.Start();
                proc.WaitForExit();

                if (commandType == "pg_dump")
                    toolStripStatusLabel1.Text = "Backup successfuly created";
                else if (commandType == "pg_restore")
                    toolStripStatusLabel1.Text = "Restore successfuly executed";
                else if(commandType=="shp2pgsql")
                    toolStripStatusLabel1.Text = "Your selected shape file successfuly transfered to PostGIS";
                else if (commandType == "pgsql2shp")
                    toolStripStatusLabel1.Text = "Your selected layer from PostGIS successfuly converted to shape file";

            }
            catch (Exception ex)
            {
                toolStripStatusLabel1.Text = ex.ToString();
            }
        }

        private void btnCreateBackup_Click(object sender, EventArgs e)
        {
            if (txtDB.Text != null)
            {
                FolderBrowserDialog dlgFolder = new FolderBrowserDialog();


                string tempPath = "";

                if (dlgFolder.ShowDialog() == DialogResult.OK)
                {
                    toolStripStatusLabel1.Text = "Creating backup now .....";
                    tempPath = dlgFolder.SelectedPath + "\\";
                    string cmd = "-i -h localhost -p 5432 -U postgres -F c -b -v -f " + tempPath + txtDB.Text + ".backup " + txtDB.Text;

                    executeCommand("pg_dump", cmd);
                }
            }
            else
            {
                MessageBox.Show("Please enter DB name which You want to make a backup");
            }


        }

        private void btnRestoreDB_Click(object sender, EventArgs e)
        {
            if (txtDB.Text != null)
            {
                OpenFileDialog myOpenFileDialog = new OpenFileDialog();
                myOpenFileDialog.Filter = "Backup files (*.backup)|*.backup";

                if (myOpenFileDialog.ShowDialog() == DialogResult.OK)
                {
                    toolStripStatusLabel1.Text = "Restoring backup now .....";
                    string path = myOpenFileDialog.FileName;

                    string cmd = "-i -h localhost -p 5432 -U postgres -d " + txtDB.Text + " -v " + path;

                    executeCommand("pg_restore", cmd);


                }
            }
            else
            {
                MessageBox.Show("Please enter DB name which You want to restore it");
            }

            

        }

        private void btnShp2Pgsql_Click(object sender, EventArgs e)
        {
            if (txtDB.Text != null)
            {
                OpenFileDialog myOpenFileDialog = new OpenFileDialog();
                myOpenFileDialog.Filter = "Esri Shape files (*.shp)|*.shp";

                if (myOpenFileDialog.ShowDialog() == DialogResult.OK)
                {
                    toolStripStatusLabel1.Text = "Transfering shape to PostGis .....";
                    string path = myOpenFileDialog.FileName;
                    string[] fileName = myOpenFileDialog.SafeFileName.Split('.');
                    string cmd = "–I –D "+path+" "+fileName[0]+" | psql "+txtDB.Text+" "+ userName;
                    //string cmd = "–I –D " + path + " " + fileName[0] + "|psql " + txtShp2PglDB.Text + " postgres";
                    executeCommand("shp2pgsql", cmd);
                }
            }
            else
            {
                MessageBox.Show("Please enter DB name which You want to load shapefile to it");
            }

        }

        private void btnPgsql2Shp_Click(object sender, EventArgs e)
        {
            
            FolderBrowserDialog myFolderBrowserDialog = new FolderBrowserDialog();
            string layerName = lstAvailbeLayers.SelectedItem.ToString();
            if (myFolderBrowserDialog.ShowDialog() == DialogResult.OK)
            {
                toolStripStatusLabel1.Text = "Transfering PostGis layer to shape   .....";
                string path = myFolderBrowserDialog.SelectedPath;
               
                string Cmd = " -u "+userName +"-P "+ password+ " "+txtDB.Text +" "+ layerName + " -f " + path+"\\"+ layerName + ".shp";
                //string strCmd =  " -h localhost -u " + username +" -P 123"+ " postgis " + "public." + fName+"-f " + tempPath + fName + ".shp" ;
                executeCommand("pgsql2shp", Cmd);
            }

        }

        private void btnLoadLayers_Click(object sender, EventArgs e)
        {
            if (txtDB.Text != null)
            {
                Npgsql.NpgsqlConnection npgConnection = null;


                npgConnection = new NpgsqlConnection("Server=localhost;UID="+userName+";PWD="+password+";Database=" + txtDB.Text + ";Port=5432;");
                try
                {
                    npgConnection.Open();

                    string strSql = "select f_table_name from geometry_columns";
                    IDbDataAdapter daPgsql = new Npgsql.NpgsqlDataAdapter(strSql, npgConnection);
                    DataSet dsPg = new DataSet();
                    try
                    {
                        daPgsql.Fill(dsPg);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    DataTable dtPg = dsPg.Tables[0];
                    lstAvailbeLayers.Items.Clear();
                    for (int i = 0; i < dtPg.Rows.Count; i++)
                    {
                        lstAvailbeLayers.Items.Add(dtPg.Rows[i][0]);
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                //mysqlConnection = npgConnection;

            }
            else
            {
                MessageBox.Show("Enter DB name which contain layers ");
            }
        }
        string userName;
        string password;
        string port;
        string hostName;
        string dbName;

        private void txtLogin_Click(object sender, EventArgs e)
        {
            userName = txtUserName.Text;
            password = txtPass.Text;
            port=txtPort.Text;
            hostName = txtHost.Text;
            dbName = txtDB.Text;
            Npgsql.NpgsqlConnection npgConnection = null;


            npgConnection = new NpgsqlConnection("Server="+hostName+";UID=" + userName + ";PWD=" + password + ";Database=" + txtDB.Text + ";Port=5432;");
            try
            {
                npgConnection.Open();
                MessageBox.Show("Connecting success");
                
                groupBox1.Enabled = true;
                groupBox3.Enabled = true;
                groupBox4.Enabled = true;
                groupBox5.Enabled = true;

                string strSql = "select f_table_name from geometry_columns";
                IDbDataAdapter daPgsql = new Npgsql.NpgsqlDataAdapter(strSql, npgConnection);
                DataSet dsPg = new DataSet();
                try
                {
                    daPgsql.Fill(dsPg);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                DataTable dtPg = dsPg.Tables[0];
                lstAvailbeLayers.Items.Clear();
                for (int i = 0; i < dtPg.Rows.Count; i++)
                {
                    lstAvailbeLayers.Items.Add(dtPg.Rows[i][0]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        
    }
}