DBCreate In Postgres

• Download source - 263.55 KB

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