MDI Case Study Purchasing - XII - Working With Data

• Download souce - 1.7 MB

Introduction

Now that we have a base for our application, let's work on getting some sample data that we can work with. There are several approaches that you can use for this, and usually you will pick one based on the availability of the data you want to work with. You could, for example, link up directly with a data source, so that your data is always live and accurate. For our example, we will be loading data from a series of CSV files. This is based on the assumption that these files would be output by our imaginary business server. Feel free to adapt your application to any type of data implementation that best suits your needs. I will try and loosely cover a few of these here, to get you started down the right track.

Global Collections

First of all, regardless of where or what our data source is, we've built our platform on the basis of object sources, so our end goal with any data approach is to get our data rows converted into objects in our collections. But first, we need collections to store these objects in as we merge them from our data. There are certainly different approaches to how or where to store these collections, but typically I house them in the Program class. This allows the data to be reachable regardless of what form or subclass you are in, which eliminates the need for passing the collections into constructors or methods. It also ensures that no matter where you access the collections from, you always know that you are hitting the same exact collection instance. So for this example we will maintain 2 collections, one for our Vendors, and one for our Inventory Items. In the Program class, lets add

public static VendorCollection Vendors { get; set; }
public static ItemCollection InventoryItems { get; set; }

Dont forget to add a using statement for the MDICaseStudyPurchasing.ObjectBase if you're following along with our same structure

Next, within the Main method, let's go ahead and initialize our collections

static void Main()
{
   Vendors = new VendorCollection();
   InventoryItems = new ItemCollection();

Now anywhere within our project, we can simply call Program.Vendors or Program.InventoryItems to access our data. Next, we want a way to add objects into our collection, while ensuring that we do not duplicate based on certain criteria, in the case of our Inventory Items, we don't want duplicate ProductNumber, and in the case of our Vendors, we don't want duplicate VendorID. To achieve this, let's add a method to our collection classes, Merge(Item item) for ItemCollection, and Merge(Vendor vendor) for VendorCollection. What Merge will do is either add a new object if the key property is new, or it will set the already existing object's properties to the argument object's property values.

public void Merge(Item item)
{
    Item mergeItem = this[item.ProductNumber];
    if (mergeItem == null) Add(item);
    else
    {
        mergeItem.ProductNumber = item.ProductNumber;
        mergeItem.Cost = item.Cost;
        mergeItem.Description = item.Description;
        Add(mergeItem);
    }
}

public void Merge(Vendor vendor)
{
    Vendor mergeVendor = this[vendor.VendorID];
    if (mergeVendor == null) Add(vendor);
    else
    {
        mergeVendor.VendorID = vendor.VendorID;
        mergeVendor.Address.AddressLine1 = vendor.Address.AddressLine1;
        mergeVendor.Address.AddressLine2 = vendor.Address.AddressLine2;
        mergeVendor.Address.Attention = vendor.Address.Attention;
        mergeVendor.Address.City = vendor.Address.City;
        mergeVendor.Address.CompanyName = vendor.Address.CompanyName;
        mergeVendor.Address.FaxNumber = vendor.Address.FaxNumber;
        mergeVendor.Address.PhoneNumber = vendor.Address.PhoneNumber;
        mergeVendor.Address.State = vendor.Address.State;
        mergeVendor.Address.ZipCode = vendor.Address.ZipCode;
        Add(mergeVendor);
    }
}

Row To Object

Now we need a mechanism for converting each data row we bring in to an object. For this, we will create a couple of overloaded methods, MergeData(DataRow data) and MergeData(String[] data). The former will take a DataRow object from a data source, the later will take a String[] from a CSV file. The content of these methods will, of course, be dependant on the schema of the data you're importing.

First, for our Item objects

public void MergeData(DataRow data)
{
    ProductNumber = data.ItemArray[0].ToString();
    Description = data.ItemArray[1].ToString();
    double cost = 0.0;
    Double.TryParse(data.ItemArray[2].ToString(), out cost);
    Cost = cost;
}

public void MergeData(String[] data)
{
    ProductNumber = data[0];
    Description = data[1];
    double cost = 0.0;
    Double.TryParse(data[2], out cost);
    Cost = cost;
}

And for our Vendor objects

public void MergeData(DataRow data)
{
    VendorID = data.ItemArray[0].ToString();
    Address.Attention = data.ItemArray[1].ToString();
    Address.CompanyName = data.ItemArray[2].ToString();
    Address.AddressLine1 = data.ItemArray[3].ToString();
    Address.AddressLine2 = data.ItemArray[4].ToString();
    Address.City = data.ItemArray[5].ToString();
    Address.State = data.ItemArray[6].ToString();
    Address.ZipCode = data.ItemArray[7].ToString();
    Address.PhoneNumber = data.ItemArray[8].ToString();
    Address.FaxNumber = data.ItemArray[9].ToString();
}

public void MergeData(String[] data)
{
    VendorID = data[0];
    Address.Attention = data[1];
    Address.CompanyName = data[2];
    Address.AddressLine1 = data[3];
    Address.AddressLine2 = data[4];
    Address.City = data[5];
    Address.State = data[6];
    Address.ZipCode = data[7];
    Address.PhoneNumber = data[8];
    Address.FaxNumber = data[9];
}

Loading Our Data

So now you just have to load your data. First let's look at how you might do this from a data source standpoint. Let's assume we are pulling data from a SQL server, we'll use the OLEDB library. In our example, we've created a splash screen, so let's use this form class to handle our loading. Go to the SplashForm class, and first let's add using statement for our OLEDB database library, System.Data.OleDB. Next let's add an instance of OleDbConnection and OleDbDataAdapter

private OleDbConnection;
private OleDbDataAdapter;

And let's initialize these in our form constructor

public SplashForm()
{
    InitializeComponent();

    // Database Setup
    String connectionString = "";
    dataConnection = new OleDbConnection(connectionString);
    String selectCommand = "";
    dataAdapter = new OleDbDataAdapter(selectCommand, dataConnection);

I won't "go hard in the paint" over setting up your connection string and select command string, there are already some good resources on this

Microsoft Developer Network - OleDbConnectionString
W3 Schools SQL Tutorial  

So now, assuming you have your connection and select strings correct, let's see how to grab the data, and get it merged into our collections. First, let's inform the user that we are loading our data by adding a bit of text and a progress bar to our SplashForm UI. Add a label and name it loadingLabel. Set it's BackColor property to Magenta and place it in the top left corner of the form. Set it's ForeColor property to Window, and it's Font to size 12 and style Bold Oblique, and set it's Visible to False. Next add a ProgressBar and name it loadingProgress. Set it's Style to Marque. This will cause it to just bounce back and forth rather than try to count up, which is sufficient behavior for this example. It just lets the user know that it's still thinking. Set the ProgressBar MarqueAnimationSpeed to 0 and Visible to False. Place it beside the label, and size it extends to the right side of the form. Because of our TransparencyKey in the form, the label and progress bar will appear to float on the desktop, which is a nice effect.

To test the behavior, let's set the label and progress bar to show on load, and hide with then timer ticks. Add the following code just before calling _hideTimer.Start() in the SplashForm constructor

loadingLabel.Visible = true;
loadingProgress.Visible = true;
loadingProgress.MarqueeAnimationSpeed = 100;

Now, in the _hideTimer_Tick event handler, let's add some code to stop and hide the progress bar and label

loadingLabel.Visible = false;
loadingProgress.Visible = false;
loadingProgress.MarqueeAnimationSpeed = 0;

Run a quick test and you'll see the label and progress bar bouncing around. Now let's replace the timer with some loading code, we'll use a BackgroundWorker to achieve this. Declare a private instance of BackgroundWorker in SplashForm, then in the SplashForm constructor, let's initialize and set up our BackgroundWorker, and after setting our loading label and progress bar to visible, we call BackgroundWorker.RunWorkerAsync()

private BackgroundWorker loader;

public SpashForm()
{
   ....
   loader = new BackgroundWorker();
   loader.DoWork += loader_DoWork;
   loader.RunWorkerCompleted += loader_RunWorkerCompleted;
   ....
   _mainForm = new MainForm();
   loader.RunWorkerAsync();
}

A few things about BackgroundWorker. For one, it runs Async so it's going out onto it's own thread to perform it's work. This means if you are needing to wait for it to complete to do something, be sure you take that action in the RunWorkerCompleted method, rather than trying to call it after starting the BackgroundWorker, because if you try that, the next action will try run immediately after starting the worker, and whatever actions the worker has to do will not have been done yet. It's good practice for RunWorkerAsync() to be the last line of code in the method it's in, so that you're not tryin to run code too early. Because of this, we want to initialize our MainForm instance BEFORE starting the worker. Also, BackgroundWorker allows you to pass around an object to keep track of anything that may be modified during the worker's lifetime. You can call the overloaded version of RunWorkerAsync(object argument), and inside the DoWork handler, the DoWorkEventArgs.Argument gives you access to this passed object. If you use this, you need to set the DoWorkEventArgs.Result object before completing the DoWork handler. Then inside the RunWorkerCompleted handler, you can access RunWorkerCompletedEventArgs.Result object. This can be handy in some occassions.

So let's knock the RunWorkerCompleted handler out of the way, it's basically the same code set as the _hideTimer_Tick, the only difference being, we no longer need to stop the _hideTimer

private void loader_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    loadingLabel.Visible = false;
    loadingProgress.Visible = false;
    loadingProgress.MarqueeAnimationSpeed = 0;

    this.Hide();
    _mainForm.Show();
}

Now for the DoWork handler, fist, here's what it should pretty much look like if you're going to be using data sources

private void loader_DoWork(object sender, DoWorkEventArgs e)
{
    // Data Source loading
    // Repalce vendorSelectCommand "" with your actual SQL statement for selecting the appropriate data from your data source
    String vendorSelectCommand = "";
    dataAdapter.SelectCommand.CommandText = vendorSelectCommand;
    DataSet vendorDataSet = new DataSet();
    dataAdapter.Fill(vendorDataSet);
    foreach (DataRow row in vendorDataSet.Tables[0].Rows)
    {
        Vendor newVendor = new Vendor();
        newVendor.MergeData(row);
        Program.Vendors.Merge(newVendor);
    }
    // Replace itemSelectCommand "" with your actual SQL statement for selecting the appropriate data for your data source
    String itemSelectCommand = "";
    dataAdapter.SelectCommand.CommandText = itemSelectCommand;
    DataSet itemDataSet = new DataSet();
    dataAdapter.Fill(itemDataSet);
    foreach (DataRow row in itemDataSet.Tables[0].Rows)
    {
        Item newItem = new Item();
        newItem.MergeData(row);
        Program.InventoryItems.Merge(newItem);
    }
}

If you're using CSV files, here is what you'll need

private void loader_DoWork(object sender, DoWorkEventArgs e)
{
   // CSV Loading
   // Vendors
   try
   {
       using (FileStream stream = File.Open("vendors.csv", FileMode.Open))
       {
           StreamReader rdr = new StreamReader(stream);
           // Split file text into it's seperate lines, which you may need to research what your files line endings are,
           // in our case, this is a linux file, which typically has \r\n line endings
           String[] fileLines = rdr.ReadToEnd().Split(new String[] { "\r\n" }, StringSplitOptions.None);
           int count = 0;
           foreach (String line in fileLines)
           {
               // If this is the first line, it's column headings, so we skip
               if (count == 0)
               {
                   count++;
                   continue;
               }
               // Trim startind and ending " from string, and split into cells, which should all be seperated by ","
               // Some CSV files dont encapsulate with " unless the cell contents include a ,
               // If your file is like this you may need to put a bit of extra logic into parsing the file
               String[] lineTokens = line.Trim('\"').Split(new String[] { "\",\"" }, StringSplitOptions.None);
               Vendor newVendor = new Vendor();
               // This is a rock bottom example, and assumes prestine data integrity. In a real world example you
               // will most likely want to take extra precautions to ensure the data is consistent, i.e. make sure the 
               // String[] is the right length so you don't get Index out of bounds exceptions
               newVendor.MergeData(lineTokens);
               Program.Vendors.Merge(newVendor);
           }
       }
       using (FileStream stream = File.Open("items.csv", FileMode.Open))
       {
           StreamReader rdr = new StreamReader(stream);
           // Split file text into it's seperate lines, which you may need to research what your files line endings are,
           // in our case, this is a linux file, which typically has \r\n line endings
           String[] fileLines = rdr.ReadToEnd().Split(new String[] { "\r\n" }, StringSplitOptions.None);
           int count = 0;
           foreach (String line in fileLines)
           {
               // If this is the first line, it's column headings, so we skip
               if (count == 0)
               {
                   count++;
                   continue;
               }
               // Trim startind and ending " from string, and split into cells, which should all be seperated by ","
               // Some CSV files dont encapsulate with " unless the cell contents include a ,
               // If your file is like this you may need to put a bit of extra logic into parsing the file
               String[] lineTokens = line.Trim('\"').Split(new String[] { "\",\"" }, StringSplitOptions.None);
               Item newItem = new Item();
               // This is a rock bottom example, and assumes prestine data integrity. In a real world example you
               // will most likely want to take extra precautions to ensure the data is consistent, i.e. make sure the 
               // String[] is the right length so you don't get Index out of bounds exceptions
               newItem.MergeData(lineTokens);
               Program.InventoryItems.Merge(newItem);
           }
       }
   }
   catch { }
}

Just take note that you may need to alter what line endings you expect when splitting the file, and you may need to alter your logic for parsing based on how your CSV format encapsulates fields that don't have a comma in the content. This example assumes prestine data, but in a real world setting you probably want to put a few checks in for data integrity, to avoid unhandled exceptions.

So now we've covered some basic ways of getting data into your program. In the next installment we'll cover using the DockingSuite to create dockable tool panels, and a tabbed document interface, similar to the layout you see in Visual Studio. If you use the included data files for example, you'll notice that the Splash Form doesn't stick around long, milliseconds in fact. Thats due to the data being processed so fast, and I only have a few sample records there. When you have much large data stores, it will take longer to load and will give a better view of the Splash Form.

Points of Interest

• Data Source loading
• CSV File loading
• BackgroundWorker

History

Keep a running update of any changes or improvements you've made here.