Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Databases » Downloads
 
Add your own
alternative version

PostgreSQL & PostGis Operations

, 10 Jun 2009
In this article, I would like to show you how to (backup, restore) DB & converting between PostGIS & Esri shape file
postgreSqlThaboot.zip
postgreSqlThaboot
postgreSqlThaboot
bin
Debug
de
Npgsql.resources.dll
es
Npgsql.resources.dll
fi
Npgsql.resources.dll
fr
Npgsql.resources.dll
ja
Npgsql.resources.dll
Mono.Security.dll
Npgsql.dll
postgreSqlThaboot.exe
postgreSqlThaboot.vshost.exe
postgreSqlThaboot.vshost.exe.manifest
zh-CN
Npgsql.resources.dll
Properties
Settings.settings
postgreSqlThaboot.suo
postgreSqlThaboot_Image.zip
postgreSqlThaboot_Project.zip
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Mono.Security.dll
Npgsql.dll
postgreSqlThaboot.exe
postgreSqlThaboot.vshost.exe
postgreSqlThaboot.vshost.exe.manifest
Npgsql.resources.dll
Settings.settings
postgreSqlThaboot.suo
postgreSqlThaboot_Source.zip
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Mono.Security.dll
Npgsql.dll
postgreSqlThaboot.exe
postgreSqlThaboot.vshost.exe
postgreSqlThaboot.vshost.exe.manifest
Npgsql.resources.dll
Settings.settings
postgreSqlThaboot.suo
postgreSqlThaboot_Source_code.zip
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Npgsql.resources.dll
Mono.Security.dll
Npgsql.dll
pgsql2shp.exe
pg_dump.exe
pg_restore.exe
postgreSqlThaboot.exe
postgreSqlThaboot.vshost.exe
postgreSqlThaboot.vshost.exe.manifest
shp2pgsql.exe
Npgsql.resources.dll
Settings.settings
postgreSqlThaboot.suo
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);
            }
        }

        
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Thabet Tech
Web Developer HP Enterprise Services , Egypt
Egypt Egypt
My name is Mohammed Thabet Zaky
I have graduated from faculty of Computer & Information system at Helwan Universty since 2007
I worked as GIS developer and .Net developer since Feb-2009 until now
Follow on   Twitter

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Thabet Tech
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid