Trials of the novice Programmer Part 3

And Then There’s SQLite

When I left off in part two I was about start explaining about how to create an SQLite Database only to look at the code and realize I had “fixed” it and “cleaned” it to the point where it would no longer actually create anything. (I’d been working with test databases created before the cleanup)

So, I took a break and fixed my fix…

Creating an SQLite database is actually quite easy, even though everything I research discombobulated it to the point of absurdity. Basically, you create a file and add a table.

Of course, it is really a good idea to think through what tables you want and what information goes in each table.

As SQLite has minimal data types my tables are not all that complex:

I have a function where I retrieve a list for the two tables currently defined.

private List<string> getTableSpec(string type)

        {

            List<string> tblSpec = new List<string>();

switch (type)

            {

case "Accounts":

                    tblSpec.Add("Name TEXT PRIMARY KEY UNIQUE");

// The Name of the Account. Recommend last four of Account # Leads

                    tblSpec.Add("Type TEXT NOT NULL"); // Type of Account F Checking or Savings;

                                // C Credit; V vendor places you buy from; D Donors for non-profits

                    tblSpec.Add("Ledger TEXT NOT NULL");

// Name for Ledger Table if used

                    tblSpec.Add("Budgeted TEXT DEFAULT N");

// Is this Account to be part of the budget

                    tblSpec.Add("Contact TEXT DEFAULT N");

// Does this Account have a Contact Entry

                    tblSpec.Add("LastStat TEXT");

// Last Date Reconciled YYYYMMDDHHMM00

//4 digit Year; Two Digit Month; Two digit Day; Two digit 24 Hour;

                                // Two Digit Miniutes; Two digit for future

                    tblSpec.Add("Balance TEXT");

break;

case "Ledger":

                    tblSpec.Add("DateTime TEXT NOT NULL");

// Note: DATE_TIME as Column name did not work.

                    tblSpec.Add("ChkNum TEXT NOT NULL");

// Note:Check as Column name did not work

                    tblSpec.Add("PayToFrom TEXT NOT NULL");

                    tblSpec.Add("Amount INTEGER DEFAULT 0");

                    tblSpec.Add("Debit INTEGER DEFAULT 0");

                    tblSpec.Add("Balance INTEGER DEFAULT 0");

                    tblSpec.Add("Cleared TEXT");

                    tblSpec.Add("Memo TEXT DEFAULT '0'");

break;

default:

break;

            }

return tblSpec;

        }

In this case the ‘Accounts’ table is the primary Table and ‘relates’ as a base to everything else.

At a later date Contact and Donor table will be considered.

NOTE: A SQLite database is only as secure as the folder it is kept in. Everything you put in it is subject to being compromised if not adequately protected.

The Donor Table, is still uncertain, the original idea is for this financial system to be used m=by small non-profits.

Ledger, Contact and Donor columns in the Accounts Table are Table Names, which presents its own surprises. J

Now that we have an idea as to what we want in the database, let’s create one.

When the app comes up it comes up in Hidden mode:

I am using the Title Bar to help decide what parameters to use in sizing each Tab.

It only updates when I actually Resize the window.

Here is the current Database tab selected.

We only really need two selections. Close is obvious. J

The Open or Create Database button click gets you this…

private void btnOpenDatabase_Click(object sender, EventArgs e)

        {

string databaseFileName = "";

string fileToMerge = fileOpen("SQLite Database Files|*.sqlite", "Select Database File",

                   myDirectory, false);

if (fileToMerge != "")

            {

bool newDatabase = false;

                databaseFileName = fileToMerge;

if (System.IO.File.Exists(fileToMerge) == false)

                {

//showMe.Comment = fileToMerge; Application.DoEvents();

                    DialogResult answr = MessageBox.Show("File: " + fileToMerge +

" Does not exit!\r\nCreate Database?", "Create File?",

                                          MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);

if (answr != DialogResult.OK) { return; }

// Did not work using default folder.

                    SQLiteConnection.CreateFile(fileToMerge);

                    databaseFileName = fileToMerge;

this.Text = programName + "Database: " + databaseFileName;

                    newDatabase = true;

                }

else

                {

                    databaseFileName = fileToMerge;

this.Text = programName + "Database: " + databaseFileName;

                }

string connectionString = @"Data Source=" + databaseFileName.Trim() + ";Version=3;";

//showMe.Comment = connectionString; Application.DoEvents();

                m_dbConnection = new SQLiteConnection(connectionString);

                m_dbConnection.Open();

this.Text += " Connected";

if (newDatabase == true)

                {

                    List<string> tableSpec = getTableSpec("Accounts");

                    sqlAddTable("Accounts", tableSpec);

                }

            }

        }

The fileopen function opens the OpenFileDialog.

private string fileOpen(string filter, string title, string folder, bool noAllowNew = false)

        {

            OpenFileDialog getSeqFile = new OpenFileDialog

            {

                CheckFileExists = false,

                InitialDirectory = folder,

                Title = title,

                Filter = filter

            };

if (getSeqFile.ShowDialog() != DialogResult.OK)

            {

return "";

            }

return getSeqFile.FileName;

        }

In order to return a user selected filename, one not already in existence, CheckFileExists must be false. I ruse a lot of code, hence the getSeqFile…

Basically, what happens is if the filename does not already exist, SQLiteConnection.CreateFile(fileToMerge); Creates an empty database structure.

Which then needs at least one table, which in this case, is the Accounts Table.

From this point on you really can’t do anything without being connected to the database. I find creating my SQLite commands as separate strings makes debugging them easier.

All you need is a real SQLite database file, it can be empty.

string connectionString = @"Data Source=" + databaseFileName.Trim() + ";Version=3;";

Create the connection

m_dbConnection = new SQLiteConnection(connectionString);

Then Open it m_dbConnection.Open();

Now we can add that obligatory table.

We getTableSpec("Accounts")

Then we

private string sqlAddTable(string tableName, List<string> tableSpec)

        {

string SQLitecmd = "create table if not exists " + tableName + "(";

for (int n = 0; n < tableSpec.Count; n++)

            {

if (n == 0)

                {

                    SQLitecmd += tableSpec[n].ToString();

                }

else

                {

                    SQLitecmd += ", " + tableSpec[n].ToString();

                }

            }

            SQLitecmd += ")";

return SQLitecmd;

        }

Which formats the tablespec list into an SQLite command string.

Then we

private void SQLiteCmdExec(string SQLitecmd)

        {

            SQLiteCommand command = new SQLiteCommand(SQLitecmd, m_dbConnection);

try

            {

                command.ExecuteNonQuery();

            }

catch (Exception ex)

            {

                showMe.Comment = "EXCEPTION: " + ex.Message;

                Application.DoEvents();

            }

        }

While all of this could be combined into one or two lines of code spreading it out helps debugging and is illustrative for your learning what I’ve learned by trial and many errors.

Note: When, not if, the command fails your list form will display it before the system barfs. But only if that list form is in an area not covered by Visual Studio when it returns from a non-trappable error.

OK That’s enough for this sitting…

Questions & Comments myrrhhouseof2[at]gmail.com

You will have to change the [at] to @

MUST Have a subject