Since the dashboard project reached a new milestone last month I’ve had time to start planning the next two projects. The first is a private Internet site for customers to place orders, check on order status, check on payment status, etc. The second, and much larger, is a total rewrite of the order entry, shipping, inventory, receivables, payables, and general ledger system from the mish-mash of legacy languages and data storage to a .NET / SQL Server based system.
The dashboard uses a combination of the patterns & practices Enterprise Library and naked ADO.NET. The client is deployed via ClickOnce and talks to the server via ASPX Web Services. It’s built using the 2.0 framework and that was about it for data access options (I’m ignoring things like NHibernate, Subsonic and LLBLGen for the moment.) Now with the new projects I’m reviewing the current crop of options, starting with Entity Framework (which RTM’d when SP1 for VS2008 was released) and LINQ to Entities. I built the database and then created the entity model:
I’m starting with some simple CRUD operations for the salesman, route, chain, and customer tables:
For the moment the data model closely matches the legacy data model; that’s why the orders table references the customer table and the chain table while the customer table also references the chain table. This will most likely change going forward as I refine the model.
Then I created an ugly-as-sin, break-every-rule form for the chain table:
Here’s the FormLoad:
1: void HandleFormLoad(object sender, EventArgs e)
2: {
3: InitializeForm();
4: LoadStatesDropdown();
5: TransitionToMode(FormMode.KeyMode);
6: txtChainCode.Focus();
7: }
InitializeForm() simply sets the MaxLength of each TextBox; it’s hard-coded now but I’d like it to come from the model’s meta-data.
LoadStatesDropdown() does exactly what it says; it uses a small piece of LINQ to populate an Infragistics combobox with the states:
1: private void LoadStatesDropdown()
2: {
3: var q = from s in ctx.states orderby s.abbrev select s;
4: cboChainState.DataSource = q;
5: cboChainState.ValueMember = "abbrev";
6: cboChainState.DisplayMember = "abbrev";
7: }
TransitionToMode() enables or disables controls and sets up or clears data binding depending on the form’s mode (KeyMode, EditMode, or AddMode.)
Here’s the data binding code:
1: private void SetDataBinding(bool setIt)
2: {
3: if (setIt)
4: {
5: txtChainCode.DataBindings.Add(new Binding("Text", currentChn, "chaincode", false, DataSourceUpdateMode.OnPropertyChanged));
6: txtChainName1.DataBindings.Add(new Binding("Text", currentChn, "chainname1", false, DataSourceUpdateMode.OnPropertyChanged));
7: txtChainName2.DataBindings.Add(new Binding("Text", currentChn, "chainname2", false, DataSourceUpdateMode.OnPropertyChanged));
8: txtChainAddr.DataBindings.Add(new Binding("Text", currentChn, "chainaddr", false, DataSourceUpdateMode.OnPropertyChanged));
9: txtChainCity.DataBindings.Add(new Binding("Text", currentChn, "chaincity", false, DataSourceUpdateMode.OnPropertyChanged));
10: cboChainState.DataBindings.Add(new Binding("Value", currentChn, "chainstate", false, DataSourceUpdateMode.OnPropertyChanged));
11: txtChainZip.DataBindings.Add(new Binding("Text", currentChn, "chainzip", false, DataSourceUpdateMode.OnPropertyChanged));
12: txtChainPhone.DataBindings.Add(new Binding("Text", currentChn, "chainphone", false, DataSourceUpdateMode.OnPropertyChanged));
13: }
14: else
15: {
16: txtChainCode.DataBindings.Clear();
17: txtChainName1.DataBindings.Clear();
18: txtChainName2.DataBindings.Clear();
19: txtChainAddr.DataBindings.Clear();
20: txtChainCity.DataBindings.Clear();
21: cboChainState.DataBindings.Clear();
22: txtChainZip.DataBindings.Clear();
23: txtChainPhone.DataBindings.Clear();
24: }
25: }
I spent a lot of time searching online for databinding examples. All the ones I found were in the context of binding a grid or a list. None of them illustrated binding other controls to entities. After I figured it out it was pretty simple (note that the chain table contains no foreign keys; more on that later.)
Here’s the code behind the Submit button:
1: void HandleSubmitClick(object sender, EventArgs e)
2: {
3: if (!String.IsNullOrEmpty(txtChainCode.Text))
4: {
5: string findChain = txtChainCode.Text.Trim().PadLeft(3, '0');
6:
7: currentChn = ctx.chains.Where(c => c.chaincode.Equals(findChain, StringComparison.OrdinalIgnoreCase)).FirstOrDefault<chain>();
8:
9: if (currentChn == null)
10: {
11: if (MessageBox.Show(this, String.Format("Chain '{0}' was not found; would you like to add it?", findChain), "Chains",
12: MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
13: {
14: currentChn = new chain();
15: currentChn.chaincode = findChain;
16: TransitionToMode(FormMode.AddMode);
17: txtChainName1.Focus();
18: }
19: else
20: {
21: txtChainCode.Text = String.Empty;
22: txtChainCode.Focus();
23: }
24:
25: }
26: else
27: {
28: TransitionToMode(FormMode.EditMode);
29: }
30: }
31: }
In the legacy system, things like a chain code are numeric only, right-justified, zero-filled. In the original system they were the primary key; I plan on changing that to use a Guid as the primary key and use the human-readable code as a surrogate key.
It took my a little while to figure out how to handle foreign key constraint violations and I’m still not sure it’s the right way to do it:
1: void HandleDeleteClick(object sender, EventArgs e)
2: {
3: // only edit mode
4: if (DialogResult.Yes == MessageBox.Show(this, "Are you sure you want to delete this chain?", "Delete Chain",
5: MessageBoxButtons.YesNo, MessageBoxIcon.Question))
6: {
7: try
8: {
9: ctx.DeleteObject(currentChn);
10: ctx.SaveChanges();
11: currentChn = null;
12: TransitionToMode(FormMode.KeyMode);
13: txtChainCode.Focus();
14: }
15: catch (UpdateException ex)
16: {
17: // referential integrity exception
18: ctx.Refresh(RefreshMode.StoreWins, currentChn);
19: MessageBox.Show(this, "Unable to delete chain; the chain is currently referenced by another part of the system.",
20: "Delete Chain", MessageBoxButtons.OK, MessageBoxIcon.Information);
21: }
22: }
23: }
Again, I couldn’t find any mention of how to handle foreign key constraint violations. There are plenty of example on how to handle optimistic concurrency collisions; that’s where RefreshMode.StoreWins comes in and it seems to work correctly here as well. Without the ctx.Refresh() line, the failed DeleteObject() stays in the object context and the framework will try to execute it each time you call SaveChanges().
One thing I’m still trying to figure out is how to databind related objects. In the diagram above there are foreign keys from the customer table to the route table, salesman table, and chain table. In other words, a customer is in a route, has a salesman, and is in a chain. Here’s a portion of the code in the customer form’s SetDataBinding() method:
1: Binding b;
2: txtAccount.DataBindings.Add(new Binding("Text", currentCust, "account", false, DataSourceUpdateMode.OnPropertyChanged));
3: umeChain.DataBindings.Add(new Binding("Value", currentCust.chain, "chaincode", false, DataSourceUpdateMode.OnValidation));
4:
5: b = new Binding("Value", currentCust, "wkscredit", true, DataSourceUpdateMode.OnPropertyChanged, 0);
6: b.Format += new ConvertEventHandler(HandleFormatWeeksCredit);
7: b.Parse += new ConvertEventHandler(HandleParseWeeksCredit);
8: spnWeeksCredit.DataBindings.Add(b);
9: umeSalesman.DataBindings.Add(new Binding("Value", currentCust.salesmen, "smancode", false, DataSourceUpdateMode.OnPropertyChanged));
umeChain is a masked edit control that displays the customer’s chain code.
If the user changes the value for the chain control I want to retrieve the matching chain entity, attach it to the customer entity, and update the display. If the user entered an invalid chain, I want to whine at them and change it back to what it was. Pretty basic stuff that’s been done since the year one.
Here’s the code attached to the chain control’s Validating event:
1: void umeChain_Validating(object sender, CancelEventArgs e)
2: {
3: // Did it change?
4:
5: if (!String.IsNullOrEmpty(umeChain.Text))
6: {
7: string tempChn = umeChain.Text.Trim().PadLeft(3,'0');
8: if (tempChn.Equals(currentCust.chain.chaincode)) // if the chain didn't change, don't do the query.
9: return;
10:
11: // Have to return a chains entity here so we can attach it to the customer.
12: chain chn = ctx.chains.Where(c => c.chaincode.Equals(tempChn)).FirstOrDefault<chain>();
13: if (chn == null)
14: {
15: MessageBox.Show(this, "Invalid chain", "Customers", MessageBoxButtons.OK, MessageBoxIcon.Warning);
16: e.Cancel = true;
17: umeChain.Value = currentCust.chain.chaincode;
18: }
19: else
20: {
21: if (!currentCust.chain.Equals(chn))
22: {
23: currentCust.chain = chn;
24: umeChain.Value = currentCust.chain.chaincode;
25: }
26: }
27: }
28: }
This is where I’m currently having trouble. If I change the chain to one that actually exists (for example, 079 instead of 078) it throws an exception on SaveChanges():
A duplicate key error. Opening a quick watch on the context object shows a ModifiedEntityCount of 1 and an AddedEntityCount of 1. Nothing should have been added; this example is editing an existing entity. Drilling down into the ObjectStateManager shows one item in the _addedRelationshipStore (the EntityKey for the chain we changed to,) one item in the _deletedRelationshipStore (the EntityKey for the chain we changed from) and one item in the _modifiedEntityStore (the EntityKey for the chain we changed from not the EntityKey for the customer we changed.) If this was a bug I can’t believe it would have gone undiscovered so obviously I’m doing something wrong.
More to come…
