Click here to Skip to main content
Click here to Skip to main content
Go to top

DBCreate In Postgres

, 22 Dec 2008
Rate this:
Please Sign up or sign in to vote.
An article on database create using PostgreSQL
DBCreate

Introduction

When installing a database application many of the customers want to see the SQL code that is executed against the database. For a large part they need to see this for security reasons and sometime Sarbanes-Oxley regulations. I wanted to see if we could get to a place where we can have the .SQL along with a program to execute the code against the database. In the past, we just spawned a shell and it showed the sys admin the results. We would also check the output file for words such as error. Although this worked, it is not the most elegant way of doing things.

Using the Code

The program has one main screen and two panels that are enabled based on input. The first panel gathers the information needed to execute a series of SQL against. The buttons are for the back and next sequences but also the button name gets changed for the last step to start. The second screen displays a textbox that will get updates after each SQL statement is read from the input file. The trick is to put each control that you want into its respective panel. This way when you enable and make visible the panel you need it done for the panel and not each control within the panel.

        /// 
        /// initialize the variables and screens. 
        /// 
        public DBIns()
        {
            InitializeComponent();
            panel2.Enabled = false;
            panel2.Visible = false;
            btnBack.Enabled = false;
        }

When the buttons are pressed, the code must check its current state and change any button names that need changing along with making the panels visible and enabled. Since I actually plan on using this code I have also added the errorprovider to check for valid input before we proceed to the second panel.

        /// 
        /// click the back button. determine what to do. 
        /// 
        private void btnBack_Click(object sender, EventArgs e)
        {
            if (panel1.Enabled != true)
            {
                panel2.Enabled = false;
                panel2.Visible = false;
                panel1.Enabled = true;
                panel1.Visible = true;
                btnNext.Text = "Next ->";   // change the button to read next. 
                btnBack.Enabled = false;
            }
        }
        /// 
        /// click the next button. determine what to do. 
        /// 
        private void btnNext_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            // if panel 1 is enabled and a next button was pressed we
            // want to display panel2 and allow the user to press the start button. 
            if (panel1.Enabled == true)
            {
                // Check to see if the data was entered before we proceeed. 
                if (txtServer.Text.Length == 0)
                {
                    errorProvider1.SetError(txtServer,
                        "Please enter a valid server name");
                    return;
                }
                // Check to see if the data was entered before we proceeed. 
                if (txtDBName.Text.Length == 0)
                {
                    errorProvider1.SetError(txtDBName,
                        "Please enter a valid database name");
                    return;
                }
                // Check to see if the data was entered before we proceeed. 
                if (txtUser.Text.Length == 0)
                {
                    errorProvider1.SetError(txtUser, "Please enter a valid User name");
                    return;
                }
                // Check to see if the data was entered before we proceeed. 
                if (txtPassword.Text.Length == 0)
                {
                    errorProvider1.SetError(txtPassword,
                        "Please enter a valid password");
                    return;
                }
                panel1.Enabled = false;
                panel1.Visible = false;
                panel2.Enabled = true;
                panel2.Visible = true;
            
                
                btnNext.Text = "Start";
                btnBack.Enabled = true;
                panel2.Visible = true;
                textBox1.TabIndex = 1;
                textBox1.Focus();
                btnBack.TabIndex = 2;
                btnNext.TabIndex = 3;
            }
            else
            {
                NpgsqlRtns cl1;
                bool    bCreateDB;
                string strDBName;
                string strServer;
                string strUser;
                string strPassword;

                btnBack.Enabled = false;
                btnNext.Enabled = false;
                cl1 = new NpgsqlRtns();
                bCreateDB = chkBoxCreateDB.Checked;
                strDBName = txtDBName.Text;
                strServer = txtServer.Text;
                strUser = txtUser.Text;
                strPassword = txtPassword.Text;
                Subscribe(cl1);
                cl1.StartConv(bCreateDB, strDBName, strServer, strUser, strPassword);
            }
        }

The startconv procedure handles creating the database if selected and parsing the file for SQL commands.

        public int StartConv(bool bCreateDB, string strDBName, string strServer,
            string strUser, string strPassword)
        {
            int ians = 0;

            ians = doConv(bCreateDB, strDBName, strServer, strUser, strPassword);
            return (ians);
        }
        /// 
        /// This routine does the actual database conversion process. 
        /// 
        private int doConv(bool bCreateDB, string strDBName, string strServer,
            string strUser, string strPassword)
        {
            int     ians = 0;
            string  strlastCommand;
            string  strCommand;
            FileRtns    frtn;
            NpgsqlConnection conn;
            //
            // first use the SA account to create a datbase if requested.  
            //
            strCommand = string.Format(
                "Server={0};Port=5432;User Id={1};Password={2};Database=postgres;",
                strServer, strUser, strPassword);
            conn = new NpgsqlConnection(strCommand);
            conn.Open();
            // if we were requested toc reate the datbase then do so 
            if (bCreateDB)
            {
                strCommand = string.Format(
                  "CREATE DATABASE \"{0}\" WITH OWNER = postgres ENCODING = 'WIN1252';",
                  strDBName);
                strlastCommand = strCommand;
                NpgsqlCommand command = new NpgsqlCommand(strCommand, conn);
                try
                {
                    command.ExecuteScalar();
                    strResult = "Create Database - Successful";
                }
                catch
                {
                    strResult = "Error ";
                }
                if (Tick != null)
                {
                    Tick(this, e);
                }
            }
            conn.Close();

            //
            // Now log into the requested database and issue the sql statements. 
            //

            // create a file routines class to read the sql statements form the file. 
            frtn = new FileRtns();
            frtn.FileOpen();
            strCommand = string.Format(
                "Server={1};Port=5432;User Id={2};Password={3};Database={0};",
                strDBName, strServer, strUser, strPassword);
            conn = new NpgsqlConnection(strCommand);
            conn.Open();
            // while there are statements in the file read them. 
            while (true)
            {
                strCommand = frtn.FileRead();
                if (strCommand.Length == 0)
                {
                    break;  // we are at the end of the file. 
                }
                strlastCommand = strCommand;
                NpgsqlCommand command = new NpgsqlCommand(strCommand, conn);
                // execute the sql statement. 
                try
                {
                    command.ExecuteScalar();
                    strResult = findCommand(strCommand) + " - Successful";
                }
                catch
                {
                    strResult = findCommand(strCommand) + " - Error";
                }
                if (Tick != null)
                {
                    Tick(this, e);
                }
            }
            conn.Close();
            frtn.FileClose();

            strResult = "Finished";
            if (Tick != null)
            {
                Tick(this, e);
            }

            return (ians);
        }

In this version, the file parser does a read until the first semicolon. I know this will only work for commands that are one line long (which are many) but it will fail for functions. For the next version, I will update this to include functions. To make that job easier I have encapsulated the class.

        /// 
        /// Read the frist line from the file.
        /// We will read until the first semicolon since that ends the sql statement. 
        /// 
        public string FileRead()
        {
            string  strResult = "";
            string  strBuffer = "";

            while ((sr.EndOfStream)!= true) {
                strBuffer = sr.ReadLine();
                strBuffer = strBuffer.Trim();
                if (strBuffer.EndsWith(";"))
                {
                    strResult += strBuffer;
                    break;
                }
                strResult += strBuffer;
            }
            return (strResult);
        }

After the command is executed, it is parsed for display and an event is sent. The main program catches the event and then displays the command in the textbox for the user.

Although there are other programs that focus on database creation, this is somewhat unique since the SQL file can be used standalone as well as part of this program. If you want to download postgreSQL please go here.

Other Considerations

The next version will include the fix for functions along with some other improvements for postgreSQL.

History

Dec 19 -- first version

License

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

Share

About the Author

Donsw
Web Developer
United States United States
I am a Director of Engineering, have an MBA and work in C# forms, Asp.Net and vb.net. I have been writing Windows program since windows 3.0. I am currently working in the Healthcare industry.
 
I enjoy reading, music (most types), cars, and cigars. I am involved in opensource projects at codeplex.
 
My linkedin link is
http://www.linkedin.com/in/donsweitzer
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralGreat Code and thanks Pinmemberhardsoft22-Dec-08 15:14 
GeneralRe: Great Code and thanks PinmemberDonsw23-Dec-08 2:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 22 Dec 2008
Article Copyright 2008 by Donsw
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid