Year: 2014

Web Deploy hijinks

I always used to do a file system x-deploy to copy a web app from local out to QA/PROD. But that involved manually changing the web.config and is fraught with manual steps (i.e. errors). So, if you’re getting errors on running deployments, try the following to access Web Deploy 2.0

  1. Remote desktop onto the target IIS webserver
  2. Go into IIS Manager
  3. Right click on the site, and select Deploy, “Configure Web Deploy Publishing”
  4. Click on Setup – > it will create a PublishSettings file locally on the desktop. Copy this to your local system and use this in Visual Studio (manage publish settings in Publish)

Web deployment notes: http://blogs.iis.net/msdeploy/archive/2011/04/05/announcing-web-deploy-2-0-refresh.aspx

From this article – find out if your settings are correct:

  • Click on the Web Site in IIS – select Features tab.
  • IIS Manager permissions icon.
  • IIS Manager Permissions, Action, Allow User – and add your user name.

 

 

Messing around with ListViews

I do like this simple walkthrough here on ListViews. Note it’s much easier to scaffold this stuff without sprocs – MSFT seems to be (esp with innovations in EF, Code-First and POCO) moving away from old-school procedural logic in our business layer.http://msdn.microsoft.com/en-us/library/bb515102(v=vs.100).aspx (there’s an accompanying article showing inserts/updates).

Notice how easy VS makes it for us to scaffold out ListViews as we would a Controller/View in MVC using EF. Basically it’s a three step process:

  1. Drag on a ListView, and view it in Design Mode. click on the SmartTag on the right, and select Choose a data source. Fill in a SELECT statement.
  2. Select Advanced and generate Insert, Update, and Delete statements.
  3. Go back to Configure Listview in the smart tag and select the Enable Editing, Inserting, and Deleting checkboxes, and pagination if you swing that way.
  4. Treat yourself to a Banquet Beer. (added this last one)

VS adds in a TON of coding here without you having to lift a finger, or slave away over a hot stove writing sprocs. And, you can replace this down the road with sprocs if you so desire and use it as a template. Super groovy…

So, that’s good for generating plain vanilla listviews like the below. What if, shudder, you want to extend it a little?

I’m embarrassed to say, it took me a few hours yesterday to figure out which events I should hook into – and ListView has a TON of them – to update/save rows on my listview. I’m attaching the code below – suffice to say, the _DataBound, _ItemInserting and _ItemUpdating are your friends here. See the following code…. the main page is here.

More details – another halfway decent post: http://www.codedigest.com/Articles/ASPNET/105_EditUpdateDelete_and_Insert_in_ListView_Control.aspx

jQuery Explorations

According to this posting and this one, it’s faster to use a CDN than host it internally. (Redundancy, geolocation, more servers/bandwidth, etc). (love the snarky “So I assume you’re shipping a deprecated browser as well” comment). The redoubtable Scott H did a great writeup on having a backup for a CDN so you can fall back to local… should I find that necessary. However, for my purposes, it’s enough to note that using this:

<link
rel=”stylesheet”
href=”http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css”&gt;


<script
src=”http://code.jquery.com/jquery-1.9.1.js”></script>


<script
src=”http://code.jquery.com/ui/1.10.3/jquery-ui.js”></script>

instead of local sources (href=”~/Content/jquery-ui.css”) will not slow down my code execution and in fact offers significant caching advantages.

Anyway, the long story short is, this little nugget of code is all I needed – in the end – to use the jquery datepicker.

<link
rel=”stylesheet”
href=”http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css”&gt;


<script
src=”http://code.jquery.com/jquery-1.9.1.js”></script>


<script
src=”http://code.jquery.com/ui/1.10.3/jquery-ui.js”></script>


<script>

$(document).ready(function () {

$(function () {

$(“#<%= DateStartText.ClientID %>“).datepicker();

$(“#<%= DateEndText.ClientID %>“).datepicker();

});

});


</script>

 

I’m messing around with Accordions and tabbed panels now. From a design standpoint it couldn’t be easier to drag on these controls and limit what users see. From a UI perspective, it’s not great practice to stuff hundreds of fields and master/detail views on a form and show/reveal in tabs… There’s a reason why MVC encourages us to think in terms of very simple View logic (Edit/Index/List/Delete/Insert) instead of old-school VB6-type parent/child views. (Yes you CAN do this in MVC with little trouble, but IMHO it’s easier to create and especially scaffold your views in atomic pieces.)

Postings to look at in reference: http://weblogs.asp.net/stevewellens/archive/2010/09/04/goodbye-ajax-toolkit-hello-jquery-ui.aspx… and from jquery – http://jqueryui.com/datepicker/

Learning from my mistakes on SQL…

Here’s some design mistakes I’ve made in the past… learn from my example and shun these anti-patterns!

  1. Normalization is here for a reason. Normalization defines a set of methods to break down tables to their constituent parts until each table represents one and only one “thing”, and its columns serve to fully describe only the one “thing” that the table represents. If you find yourself using string operators in WHERE clauses a lot (SUBSTRING, CHARINDEX, LIKE) to parse out a value in a column – you’ve missed the boat design-wise and your data will become less and less searchable as you move away from the SQL paradigm. (And don’t get me started on COLDESC1, cOLDESC2, etc)
  2. Natural keys are the thing, man. Think carefully before you use an identity or a GUID as your only key. You should reconsider your design if you can’t pick out which row you want from a table without knowledge of the surrogate key. Each of your tables in general should have a natural key that means something to the user and uniquely identifies each row in the table – i.e. go with PartNumber over an integer PartID GUID. In the extremely rare event that there’s no natural key (like with an audit table) – then and only then use a surrogate key.
  3. Protect your data, dammit. Your tables should have constraints on nullability, string length, FK assignment, etc.
  4. Naming standards are actually important. For example, think about X304E_DESCR. It’s important to be CONSISTENT above all and be descriptive – this is your first line of defense when it comes to documentation. Make your names clear, simple and descriptive – and don’t include metadata in the column names.
  5. Don’t use one table to hold all your domain values. Remember that SQL works in sets, according to normalization rules. If you’re doing joins like “AND customer.TableName = ‘Customer’ and customer.RelatedToColumn = ‘descr’, well jeez… that’s a performance killer and will quickly make your queries maddeningly hard to write and update. Remember, tables should represent one ‘thing’ – and one thing only.
  6. Avoid dynamic SQL unless there’s truly no other choice. The same reason above applies to trying to code generic T-SQL objects – such as accepting a table or set of column values as a parameter. It seems neat and tidy to do this initially as it can handle multiple tables with one sproc – but we don’t think of the performance implications of misusing sp_executeSQL in this way. Sometimes there’s no shortcut to writing CRUD statements against the entities directly – or using EF.

Looking through this article on the interwebs, I’m thinking a lot of my experience here isn’t unique. Tip of the hat to that blogger and the great books from Celko and the Guru series from Ken Henderson.

ETL from hell – getting past denial

A great article here on diagnosing problems with batch system performance: https://www.simple-talk.com/sql/performance/the-etl-from-hell—diagnosing-batch-system-performance-issues/

Basically it goes like this – when there’s problems with batch processes overlapping or timing out, companies typically try the following:

  1. Can we ‘fix’ this with more hardware? (this is expensive, and a temporary fix)
  2. Can we change the product – for example from SQL to Oracle? (this only work with a redesign. Usually the product isn’t the issue!)
  3. Turnkey solution (expensive and typically doesn’t address the root issue)
  4. Split systems into parts, each on isolated servers (hard to synchronize running jobs across your now-separate systems)

Instead of these knee-jerk reactions, there’s a better way – both strategic and tactical – to fix this:

  • Tactical
    • Gather statistics – how long is a process likely to take?
    • Get a baseline of long-running problem queries
  • Strategic
    • Determine processing time windows
    • Create a task list in Excel – and include internal/external dependencies
    • Monitor the system – and include management in monitoring reports
    • Define threads – tasks in sequence – and processing times
    • Implement batch system control that runs off your table of tasks/dependencies