C# Relational Database

• Download source files - 11.8 Kb
• Download demo project - 36.1 Kb

Introduction

ASP.NET has its "Drill Down" technique for displaying a Relational Database. I would like to introduce a new idea for displaying a Relational Database with a Parent/Child Multiple Document Interface. This has several advantages over "Drill Down", in that there can be several child MDI documents if necessary, and these documents, can contain any amount of information, as the document is resizable to a maximum size of full screen. We are not limited to expanding a single collapsed line of text as we are in "Drill Down" technique.

This is the Parent MDI

This is the Child MDI

ListBox enables the double click function to split the string.

We are going to add another function with this method. We are going to edit our records, and do this on the same page as the parent document. All we have to do is double click on our record, and this will split the string into the textboxes at the top of our page. Because I use a ListBox to view the records, I can use the "Double Click" event to accomplish that task. Here, in the textboxes, we can edit the fields as necessary, then press F1, and our changes are recorded.

In most real applications we would probably write this as an External Data Application. In this example I have "hard coded" all the data as is usually done in textbook examples for simplicity.

Sample code

Splitting the String

Here is a brief sample of the code that allows us to double click on a record, and split the string into textboxes, so that we can make our editing changes. I make extensive use of substrings, and Trim. The Trim method is necessary as to remove whitespace at the end of the field. When making editing changes this whitespace builds up, and then corrupts the remaining fields if not removed. I then call the reusable function ChangeText. This user defined function passes the Substrings by Value. In other programs I have used String.Split, or Regex.Split as each situation calls for.

Years ago we separated fields with a comma, and then with Regex.Split it was real easy to split fields at those commas. If you use this class to separate fields at whitespace, and there are several blank spaces in between fields, then Regex.Split will split at each occurrence of whitespace. This class can be more difficult to control. I would design your data to suit the class you plan on using.

Relational Database

I have created an external .ocx file, and written the ListBox.SelectedIndex to that file. This is how I pass that information to my Child MDI document. When the child document opens, it reads that external file, and selects the correct index from the string array. This index is passed to a string variable, and then parsed back to an integer variable, we used to call this "boxing" in the world of unmanaged code. That was when the "West" was still wild and untamed. We had to call the "Marshal" to do this. Hi Hi... This is the index that corresponds to the record we have selected in the parent document.

Parent MDI

private void Form1_Load(object sender, System.EventArgs e)
{
        // instantiate dialog box for creating records

        relDat = new frmRelationalDatabase(new MyDelegate(ShowSplash));
            
        // Declare Arrays

        string[] FName = {"Dave", "Lilly", "Bob", 
                          "Sandra", "Dick", "Ted",
                 "Kathy", "Angela", "Nick", 
                          "Juan"};
        string[] LName = {"Brighton", "Tomlin", "TheFather", 
                          "Murphy",  "Holiday", "Heathrow",
                          "Auburn", "Thomas", "TheGreek", 
                          "Valdez"};
        string[] DOB = {"05/23/1956", "02/03/1957", "01/01/1967", 
                        "02/02/1967", "08/09/1967", "04/05/1976", 
                        "06/06/1956", "07/02/1945", "05/05/1923", 
                        "07/01/1948"};
        string[] SocSec = {"123-23-3456", "234-23-2345", "345-23-1234", 
                           "456-23-2345", "567-34-5678", "567-23-4567", 
                           "234-34-2345", "456-12-7897", "456-23-6543", 
                           "654-43-6543"};
            
    // Format String

    string strFormatString = "";
    strFormatString = "{0, -10}{1, -12}{2, -12}{3, -12}";

    // Loop through arrays and load formatted string into listbox

    for( int i = 0; i < FName.Length; i++ )
    {
    // Instantiate new Stringbuilder class

        // When we instantiate instance of new class here the class is 

    // recreated and therefore the memory is erased. This is easier 

    // than using StringBuilder.Remove

    StringBuilder sb = new StringBuilder();
    sb.AppendFormat(strFormatString, FName[i], LName[i], 
            DOB[i], SocSec[i]);
    lstSplitString.Items.Add( sb.ToString() );
    }
}

private void lstSplitString_DoubleClick(object sender, 
            System.EventArgs e)
{
    string strSplit = lstSplitString.SelectedItem.ToString();
    intIndex = lstSplitString.SelectedIndex;
            
    strSplit1 = strSplit.Substring(0, 9);
    strSplit2 = strSplit.Substring(10, 11);
    strSplit3 = strSplit.Substring(22, 11);
    strSplit4 = strSplit.Substring(34, 12);

        txtFName.Text = strSplit1.Trim();
    txtLName.Text = strSplit2.Trim();
    txtDOB.Text = strSplit3.Trim();
    txtSocSec.Text = strSplit4.Trim();
}

private void txtFName_KeyDown(object sender, 
            System.Windows.Forms.KeyEventArgs e)
{
    if( e.KeyCode == Keys.F1)
    {
        ChangeText( strSplit1, strSplit2, strSplit3, strSplit4 );
    }
}
public void ChangeText(string strSplit1, string strSplit2, 
                       string strSplit3, string strSplit4 )
         
{
    // Instantiate new StringBuilder. 

        // We'll do this locally

    // so the Stringbuilder is cleared 

        // each time.

    StringBuilder sd = new StringBuilder();
            
    // Format String

    string strFormat = "";
    strFormat = "{0, -10}{1, -12}{2, -12}{3, -12}";

    // Pass values from textBoxes to 

        // string variables

    strSplit1 = txtFName.Text;
    strSplit2 = txtLName.Text;
    strSplit3 = txtDOB.Text;
    strSplit4 = txtSocSec.Text;

    // Rebuild StringBuilder Class, and replace old listBox item 

    // with new information

        sd.AppendFormat(strFormat,
                        strSplit1.Trim(),
                        strSplit2.Trim(), 
               strSplit3.Trim(), 
                        strSplit4.Trim());
       lstSplitString.Items.RemoveAt(intIndex);
       lstSplitString.Items.Insert(intIndex, 
       sd.ToString());
            
       // Clear textBoxes

       txtFName.Clear();
       txtLName.Clear();
       txtDOB.Clear();
       txtSocSec.Clear();
            
       // Deselects listbox item

       lstSplitString.Refresh();
}

private void lstSplitString_KeyDown(object sender, 
         System.Windows.Forms.KeyEventArgs e)
{
    if( e.KeyCode == Keys.Return )
    {
         if( File.Exists( myFileName ) )
         {
          // don't do nothing

         }
         else
         {
                  // Create File

          try
          {
             // Create File

             File.CreateText( myFileName );
             MessageBox.Show("New support file has been\r\n" +
                     "created, so restart program.",
                     "Important", MessageBoxButtons.OK,
                     MessageBoxIcon.Exclamation);
             Application.Exit();
          }
          catch( IOException )
          {
             Application.Exit();
          }
        }
      }
}
private void lstSplitString_KeyUp(object sender, System.Windows.Forms.
                                           KeyEventArgs e)            
{
    try
    {
         // Pass listBox Selected Index to variable

         intIndex = lstSplitString.SelectedIndex;
                
         // Instantiate StreamWriter

         StreamWriter streamWriter = new StreamWriter( myFileName );

         // Writes to external file

         streamWriter.Write( intIndex.ToString() );
            
         // Closes StreamWriter

         streamWriter.Close();
    }
    catch( Exception g )
    {
       MessageBox.Show( g.ToString(), "Important",
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    finally
    {
       // Show Relational database Form

       relDat.Show();
    }
}
protected void ShowSplash()
{
    Show();
}

Child MDI

private void frmRelationalDatabase_Activated(object sender, 
System.EventArgs e)
{
    string[] Address = {"4506 N. Hampton Rd \r\nFort Worth, TX" + 
                        "\r\n67895 \r\n\r\n",
                        "1809 S. Long Ave. \r\nDobson, MO" +
                        "\r\n76946 \r\n\r\n",
                        "23 NW 64th St. \r\nSeattle, WA" +
                        "\r\n98073 \r\n\r\n",
                        "1208 Midwife Ln. \r\nLos Angeles, CA" +
                        "\r\n45034 \r\n\r\n",
                        "1906 Angina Way \r\nTemecula, CA" +
                        "\r\n23786 \r\n\r\n",
                        "56 Humboldt St. \r\nOracle, FL" +
                        "\r\n97845 \r\n\r\n",
                        "3406 W. Harris Ln. \r\nMongrove, OH" +
                        "\r\n56483 \r\n\r\n",
                        "567 E. Lala Lane \r\nBurks, ME" +
                        "\r\n67895 \r\n\r\n",
                        "3459 S. ShineEmOn Circle \r\nPhoenix," +
                        "AZ\r\n86578 \r\n\r\n",
                        "1 Way \r\nWhite House, DC" +
                        "\r\n11011 \r\n\r\n"};
                    
    // Instantiate SreamReader

    StreamReader stream = new StreamReader( myFileName );

    // Read From external file

    strIndex = stream.ReadToEnd();
                
    //Close StreamWriter

    stream.Close();
                
    txtAddress.Text = Address[ Int32.Parse( strIndex )];

    strIndex = "";
    txtAddress.Refresh();
}

Why don't we use Properties to Get and Set our data?

There are several ways of passing data to a Windows Form. This would require a global variable for Visual Basic properties, or C# acceptors and mutators. Global variables are only available in C++. In fact, until C++ .NET 2003, Windows Forms were not even offered in this platform. Public scope is not enough for Windows Forms to be able to see variables in other Windows Forms.

People have used delegates, or even controls to pass data to Windows Forms. It is a subject that is not talked about much in textbooks. Textbook authors like to use Console applications to show their sample code.

Are there any foreseeable problems using this architecture?

The ListBox control must be able to contain (show) all of our data. This is how we utilize the "Double Click" event to split the record string. I have tested the ListBox control to 1,000 records. Depending on the size of your record and the max number of bytes the control will handle, the maximum number of records will vary. What if we want to push our record size?

We might only view the last 1,000 records, and archive the rest of the records to step through one at a time. We could use child MDI documents to view overflow records. I have used text searches, so that we view only the portion of the database we want to view.

This is the challenge of programming. We are drawn to this study, as it allows us to be creative. This particular architectural style is intended for use by small database users, as it does not allow for indexing, and the number of records we can show is finite. I did a test. I write a do loop, that writes 1,000 copies of a test record to the control, to see just how many records it will hold. I write 1,000 records, then 10,000 records, and then 100,000 records to the ListBox control. If it stands up to this test, then I know what the maximum capacity is. I think the maximum size for a textbox is about 64,000 bytes. This maximum size is not listed for the ListBox, but is probably somewhat less than the maximum size for a textbox. Of course, for large databases, there is always SQL Server 2000.

Summary

This is an easy and versatile way to do Relational Database as well as being able to edit records in a user friendly environment. I believe it is more versatile than the "Drill Down" techniques that are popular today. Developers who are interested in building distributable programs use C# or Visual Basic languages. The target computer only needs to install dotnetfx.exe Redistributable, and then install the .msi (Microsoft Installer) file to be able to run this distributable custom relational database. Operating Systems must be of Windows 98 SE or later.