Spreadsheets (Excel) compared to Database

Triangle of Solution

Based on number of times used:

  1. 1-5 use a piece of paper
  2. 5-20 use a spreadsheet
  3. 20+ use a database

 Spreadsheets Disadvantages

  1. Data should be stored once and only once. Using spreadsheets often creates multiple out of date files
  2. VLookup is far inferior to SQL links
  3. Some problems are very difficult to solve in comparison with similar solution in database transact sql
  4. Limited or no programmatic power dependent on developer
  5. Out of systems – suggests refactor not found
  6. Out of security control
  7. Unable to unit test
  8. Unstructured data
  9. A little knowledge is a dangerous thing
  10. Spreadsheets exported from a database – just adds another step – NOT combine, reduce, eliminate

 Spreadsheet Advantages

  1. More people can prepare them
  2. Users can demonstrate requirements by attempting to build what they want . Form of tracer bullet
  3. Possibly more print control
    1. offset by users knowledge of how to use Context Menu in grids
    2. why are they printing anyway – why not two screens – are they avoiding IT – find it easier to work with paper

Spreadsheets Interesting

  1. For users showing advanced spreadsheet skills. Could promote move to SQL Express of other. Or MS Access using ODBC. Or Reporting.
  2. Could direct them to request views for plugging in

Real-life Examples

  1. Model in Database exported to Spreadsheet
    • Created a model.
    • User downloaded to spreadsheet and then edits some data.
    • Model is blind to changes.
    • Also Model then changed in database and spreadsheet is out of date.
    • Also spreadsheet is not used for a few days so data in it becomes a little out of date.
  2. User wants to export to excel – to print out a stock driven demand. This is a step that was removed before in order to save time.

1.Do you have a consistent .NET Solution Structure?

See http://rules.ssw.com.au/SoftwareDevelopment/RulesToBetterDotNETProjects/Pages/default.aspx

While I like many projects and it solves some problems, it also causes others for instance if changing the connection string at run-time this must be done in all projects.

Database Naming Convention


For table identity column generally use ID not <TableName>ID unless it is clear what an identity column should be e.g. WO for Work Order
My preference is ID not <TableName>ID because:

  1. Less verbose. If table name itself is long then using <TableName>ID will be wordy and is likely to extend the number of lines in any Transact SQL
  2. ID works
  3. In code you know that the ID column should exist

SOLID Principles



My current design has /architecture is putting DAL in same layer/project/assembly as domain model, by which I mean classes

Parent and Child new row on windows forms details

Problem is that

then when saving:
will destroy child row.

My workaround is:
Store a variable stating is NewRow and/or child row data IsMissing
Then on SaveData use:
If NewRow OrElse IsChildRowMissing Then
End If
If NewRow OrElse IsChildRowMissing Then
  Using taP as New dsTableAdapters.ParentTableAdapter
  End Using
  DisplayStoredData – which includes re-adding child row
End If
Continue with normal save

This link describes problem but is not much help to solution:

Microsoft Visual Studio 2010 Architecture Model Upgrade Tool(Free)

Visual Studio 2010 Beta 2 Architecture diagrams. (Not dgml). Need to be upgraded to use in Release Candidate.
Tool to do this is here:-

Team City – Continuous Integration and Automated Testing

If Team City returns "Failure" > "Build Errors"
error MSB4019: The imported project "C:\Microsoft.VisualBasic.targets" was not found.
Then Team City is not using Microsoft Framework 3.5
Then go to Project Settings > Runner >
If using Visual Studio 2008 for a project .proj file then use
Build Version 3.5
Tools Version 3.5

Sln2008 only for solution files

Move to Server
Download – install – choose port
Access from another computer by using server address : port no.

Using Microsoft SQL Server
In order to use Windows Authentication need to copy dll file. In my case was
…Microsoft SQL Server JDBC Driver\sqljdbc_2.0\enu\auth\x86\sqljdbc_auth.dll
into Windows\System32 directory
file database.properties was

Refactor: Anonymous Type to Class


Dim RCO As Object = New With {.ID = 1, .Name = "ABC", .Value = 0.123}

Then use Refactor to name the anonymous type and create a class based on it.


FXCop Analyze shows dialog “Locate Assembly Reference”

If when using FXCop Analyze you get a dialog box entitled "locate assembly reference"
  1. Go to the project references.
  2. Find the related reference and note it’s location in the file system.
  3. Return to FXCop and browse to this location and put in dialog box

Also Crystal Decisions.Shared lives in C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Shared\10.5.3700.0__692fb ….
Unable to navigate to this or find a copy of the file. So for GAC use following

  1. Open FxCop 1.36.
  2. Click menu: “Project | Options…”
  3. Click the “Spelling & Analysis” tab in the “Project Options” dialog.
  4. Select the “Search Global Assembly Cache for missing references” option (by default it is unselected).


Also see that the Config file "FxCopCmd.exe.config" may be edited. See



Names all those tricks that you have been doing for a while and a lot of those that you may not currently do.
Explains motivation – method and examples.
Very good.