StartledCat

StartledCat Media Library

09-May-2018 An update on progress...

Sometime between now and the last entry, I have created a WebAPI server in the main solution and a new UI WebAPI consumer solution.
...and got the main list of records on screen via WebAPI calls to a remote box hosting the ML on IIS.
Today I have successfully added the remaining WebAPI interfaces for the LUSID operations on the Media table.
TODO - implement for the remaining tables.

11-Apr-2018

1. ADO switch applied to all DAL function = DONE!
2. OOD finally sorted out - all elements in the correct places = DONE!
Now for number 3...

10-Apr-2018 TODO List:

  1. Sort out Use ADO setting for all DAL class methods;
  2. Sort out OOD for all classes to eliminate the default MVC template (table classes are in the DAL, NOT Models folder);
  3. Web API - Get, Put, Post & Delete
Reality Check - this now means the Media Library will have 3 interfaces:
  • MVC
  • Win Forms
  • Web API

23-Mar-2018 Win Forms - following on from my review of options to update grids, the 'other' option is to only process any changes once the user has finished with the grid.

This has led me to build a generic ListComparer to simplify the process.
Tested and working 24-Mar-2018

19-Mar-2018 Win Forms - Bad Coding Smell (14/3). The use of a timer to delete a cancelled add-row...

Recap: Using DataViewGrid unbound. The user adds a new row on the grid. On leaving, the code checks for changes and prompts the user to save: Yes/No/Cancel.
- Yes saves the new record;
- Cancel keeps editing the new record;
- No should removed the new row from the grid as not in the database, but throws an error 'Operation cannot be performed in this event handler'
The event handler is RowValidating.

This link suggests the user of a Timer.
...and uses this code:
	                else if (result == DialogResult.No)
                        {
                            RowToDelete = e.RowIndex;
                            ViewModel = null;
                            timerDVG.Start();
                        }
...used in conjunction with this:

        private void timerDVG_Tick(object sender, EventArgs e)
        {
            if(RowToDelete > -1)
            {
                dataGridView_Artist.Rows.RemoveAt(RowToDelete);
                RowToDelete = -1;
                timerDVG.Stop();
            }
        }

However, having seem a code snippet detailing how to intercept a combo-box selection change, it dawned on me that another approach would be more correct and understandable:
	                else if (result == DialogResult.No)
                        {
                            RowToDelete = e.RowIndex;
                            ViewModel = null;
                            this.BeginInvoke(new MethodInvoker(EndEdit_DeleteRow));
                        }
...used in conjunction with this:

        void EndEdit_DeleteRow()
        {
            if (RowToDelete > -1)
            {
                dataGridView_Media.Rows.RemoveAt(RowToDelete);
                RowToDelete = -1;
            }
        }
I think these two do exactly the same thing i.e. run some asynchronous code that performs the required action, but my implementation does not require the use of a timer.

16-Mar-2018 Win Forms - ditched the MDI. Media and Artist screens tested working for on-screen grid editing. But is it really worth it?

The basic purpose for a data view grid (dvg) is to display a view of the data.
For this point, a user/client should be able to do some basic operations i.e. Add, Update & Delete.
To achieve this, there are only three options:
  1. Have a section to allow data entry and have buttons to Add/Edit/Delete
  2. Have buttons to open screens to perform Add/Edit/Delete (same as the web page version)
  3. Allow in-line editing on the dvg

The first two are easy to understand and implement, and the code required can follow an easy to understand pattern. However, some clients\users want in-line editing.
On the web, this is easy to achieve with one or more scripts that edit the DOM (HTML Document Object Model).
For Windows Applications this is not so simple, since the forms are event driven. This leads to a less-than-pleasant coding pattern, a pain to debug, a pain to code and a pain to maintain.
Conclusion - best to avoid at all costs and convince the client that in-line editing on grids is going to cost them a lot more.

14-Mar-2018 ...well, no change in 18 years - still the same rubbish grid it always was!!

After a bit of event order investigation, sorted out the Insert, Update and Delete operations.
One point of concern - for a new record (Insert), if the user cancels the new row is left in place and cannot be removed in the event handler.
The only working solution I have found so far is to use a timer - but, is this a bad coding smell?

13-Mar-2018 MDI and Artist screens added using unbound DataViewGrid (DVG). Populated with data. Now for the in-line editing...

12-Mar-2018 Starting on adding a Windows Forms (that's a Windows desktop application!) user interface on top of the stack.

01-Mar-2018 Added ADO alongside EF in database access layer

There is logic in this madness. The original plan was to test/evaluate EF vs ADO. In this app I can now switch between using EF or ADO and hence do some timing tests...but it was more a case of sorting out the structure to be able to switch rather than timing test.
There are a lot of opinions on the web regarding the pros and cons of either, but the bottom line comes down to EF gives the coder everything, ADO gives you nothing.
Well that does not sound right = correct!
  • ADO allows full control over what you want to do, but you need to implement it;
  • EF gives you everything and you ought/need to switch off or override it default implementation.
It's kind of the the same as Oracle vs SQL Server access rights, and similar to having databound or unbound data elements on grids, or using MS Access without restriction on data updates and deletes.

21-Feb-2018 - Restructuring

Last night I planned out a generic separations of concerns model for just about any system that kind of looks like this (to be done!!)

In terms of this media library, the main change was to separate the Views from the Table classes and move the EF DBContext.
So far, three issues:
  1. Moving the DBContext from namespace MediaLibrary.Models to namespace MediaLibrary.Tables created a new empty database;
  2. Spent a lot of time trying to work out why one of the views was not returning data to the controller - there was a missing { get; set; } on a new class field I added;
  3. Spent even longer on another one - the only 'fix' was to rename the model elements, and that worked - can't see a conflict so must be a bug or feature!!!

20-Feb-2018 (8am) - All Working :-)

It turns out that if the data is supplied using the EF Linq method, then the model returned to the delete action has something in it that prevents the delete.

The code to populate the model for the delete view was this:

media = db.MediaLibItems.Include("MediaFormat").Include("MediaType").Include("MediaArtist").Include("MediaLabel").Single(m => m.MediaId == mediaid);

This did what was expected i.e. displayed what I wanted in the view, but supplies all of the child table data. The upshot of this, is EF can't handle the delete.

So, I replaced it with this:

            mediaToDelete.media = db.MediaLibItems.Single(m => m.MediaId == mediaid);

            // Now get the child table text values from the ids

            int? formatid = mediaToDelete.media.FormatId;
            int? labelid = mediaToDelete.media.LabelId;
            int? typeid = mediaToDelete.media.MediaTypeId;
            int? artistid = mediaToDelete.media.ArtistId;

            mediaToDelete.FormatName = formatid == null ? "" : db.MediaLibFormat.SingleOrDefault(m => m.FormatId == formatid).FormatName.ToString();
            mediaToDelete.LabelName = labelid == null ? "" : db.MediaLibLabel.SingleOrDefault(m => m.LabelId == labelid).LabelName.ToString();
            mediaToDelete.TypeName = typeid == null ? "" : db.MediaLibType.SingleOrDefault(m => m.MediaTypeId == typeid).TypeName.ToString();
            mediaToDelete.ArtistName = artistid == null ? "" : db.MediaLibArtist.SingleOrDefault(m => m.ArtistId == artistid).ArtistName.ToString();

Using the .Include() is a bit smoke and mirrors - it does get the required data, but mucks up EF delete, throws an error and no delete.

The belt and braces method works first time, all the time :-)

Moral of the Story = KISS. The old ways are the best!

19-Feb-2018 - All Gone Wrong!!!

Having used a console test app to test the ADO.NET SqlClient interface classes to stored procedures, it's now time to try them out under MVC.
A quick test and delete of the test records added using the current MVC interface threw up some unexpected errors, namely:
  • Under IIS: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details. )
  • A referential integrity constraint violation occurred: The property value(s) on one end of a relationship do not match the property value(s) of on the other end.

The first error required a quick recreate of the MSSQLLocalDB instance, redeploy of the website and mdf, and a reboot - all sorted!"

The second one was a tad trickier, and had assumed that the ADO tests had mucked up the data, so deleted all of the data and started from scratch.

A couple of hours later, with new clean data, still got the same error, so something else not quite right.

Having done a few internet searches, it seems there is something wrong with the MVC model using an EF delete, so need to check the code....but it's very late and time for bed.

21-Jan-2018 - Some Real Data.

Added core ref data for Label and Artist using Discogs export data.
  • Used MS Access to perform the required ETL.
  • Added OrderBy across all views
  • TODO - possibly pagenate ref data.
  • TODO - bulk add format data.
  • TODO - check correct page returned to if pagenation used i.e. if add/edit/delete return to correct page number.

19-Jan-2018 - More Maintenance Screens.

Added the missing reference data maintenance views for Artist and Label.

Then thought I ought to standardise the table id columns to Id rather than ID - boy what an ordeal!
It's the same as changing a field name this is plastered on just every view, so broke just about every page.
All fixed now :-)

Point to note, the Add-Migration only changed indexes NOT field names - I had to do this manually!!!!

10-Jan-2018 - Time to get it sorted!

To help, this is the the basic structure of the database:

Media Library Basic ERD

Having slept on it...back when using ADO.NET to supply a list of data to a web page, I used views to supply the nicely formatted, no nulls data that was required on-screen.

So, to recap:

Combo DDL Boxes: For the 'CRUD' operation pages (DEAL, LEAD, ADEL?):
  • Add - requires hit on db for each combo ddl box = no way around this
  • Edit - requires hit on db for each combo ddl box = no way around this
  • Delete - only needs to display the text (no combo ddl boxes required)
  • List - only needs to display the text (no combo ddl boxes required for this view in this system)

Yesterday's working solution was to pass a list of ref data for each table to the view, but this is only required for Add and Edit i.e. to populate the DDLs.

If you consider what happens in MS Access, for example, a form will have a data source for the bulk of the data to be displayed and additional data sources for each combo-box used on the form.

Back to my issue - ideally require a view for the List.

BUT - EF code first does not allow for a nice view to be scripted in SQL and called i.e. one that supplies the required data.
So...
  • Add - blank record for input - get all the ref data for the Combo DDLs
  • Edit - get the record and all the ref data for the Combo DDLs
  • Delete - get the record and ref text using a join
  • List - get a list of all records and ref text using a join

The Solution

Originally, for a full list of everything view list, I had this:


        public ActionResult Index()
        {
            MediaPartialList model = new MediaPartialList();

            using (var db = new MediaDBContext())
            {
                List media = db.MediaLibItems.ToList(); // this is all the data

                List media = db.MediaLibItems.Include("MediaFormat").Include("MediaType").Include("MediaArtist").Include("MediaLabel").ToList();

                model.MediaList = media;

                var formats = GetMediaFormats();
                var types = GetMediaTypes();
                var artists = GetMediaArtists();
                var labels = GetMediaLabels();

                model.FormatList = formats.ToList();
                model.TypeList = types.ToList();
                model.ArtistList = artists.ToList();
                model.LabelList = labels.ToList();
            }

            return View(model);
        }

After some investigation, what I needed was to specify and get the joined data, like so:


        public ActionResult Index()
        {
            MediaPartialList model = new MediaPartialList();

            using (var db = new MediaDBContext())
            {
                List media = db.MediaLibItems.Include("MediaFormat").Include("MediaType").Include("MediaArtist").Include("MediaLabel").ToList();

                model.MediaList = media;
            }

            return View(model);
        }

09-Jan-2018 - Added a couple of maintenance screens to Media Library

This went really well and I had two maintenance screens for reference data up and running within an hour.
BUT...then I realised I had Ids on display rather than the corresponding text names.

After much searching and testing plausible solutions, the one that worked fell in line with the data required to populate the Combos, so no extra hit on the database, but did not feel like the right solution."

My notes left to myself last night:
Reference Data Lists (record lists)
TODO - smacks of bad design = bad coding smell?
  1. Should be able to use EF directly to include the data defined by the join?
  2. If not...get the data from a view
  3. But...the data IS required for the drop-downs, so get it anyway
December 2017 - As a ramp-up on MVC and EF, I've embarked on building a full-blown dynamic web-site. This is using VS2017, SQL Server 2014, C# MVC Code First Entity Framework.

A few thing to remember and lesson learned:
  • The web is still only text based (with images) and only uses HTML, CSS & JavaScript.
  • Get your DB Context EF structures right or else!
  • EF will create the database if not found
  • Combo Drop-Down Lists are a pain, but do-able = get it right once then copy 'n' paste

© 2018 - StartledCat