Wednesday 30 December 2009

Restore a database using TSQL

To restore a database use the following SQL:

USE [tempdb]
ALTER DATABASE [DatabaseName]

SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [DatabaseName]
FROM DISK = 'C:\BackupName.BAK'
WITH MOVE 'MDF_logical_name' TO 'C:\MDFName.mdf',
MOVE 'LDF_logical_name' TO 'C:\LDFName.ldf'
GO

ALTER DATABASE [DatabaseName] SET MULTI_USER
GO

Note that this puts the database into single user mode first (i.e. it kicks off other users). Don’t forget the USE [tempdb] statement or you may find the database still in use and the restore may fail. If you’re doing a straight restore you can get the logical filenames etc. from the backup file:

RESTORE FILELISTONLY FROM DISK = 'C:\BackupName.BAK'
GO

Wednesday 16 December 2009

Keyboard shortcuts for Remote Desktop sessions

  • CTRL+ALT+END: Open the Microsoft Windows NT Security dialog box (CTRL+ALT+DEL)
  • ALT+PAGE UP: Switch between programs from left to right (CTRL+PAGE UP)
  • ALT+PAGE DOWN: Switch between programs from right to left (CTRL+PAGE DOWN)
  • ALT+INSERT: Cycle through the programs in most recently used order (ALT+TAB)
  • ALT+HOME: Display the Start menu (CTRL+ESC)
  • CTRL+ALT+BREAK: Switch the client computer between a window and a full screen
  • ALT+DELETE: Display the Windows menu
  • CTRL+ALT+Minus sign (-): Place a snapshot of the entire client window area on the Terminal server clipboard (ALT+PRT SC)
  • CTRL+ALT+Plus sign (+): Place a snapshot of the active window in the client on the Terminal server clipboard (PRT SC)

User-defined Functions in SQL Server

User-defined functions (UDFs) encapsulate logic for use in other queries. Views are limited to a single SELECT statement but user-defined functions can have multiple SELECT statements.

There are basically 3 categories of UDF:

  • Scalar-valued function
    • Returns a single, scalar value.
    • Can be used as column name in queries.
    • Can contain an unlimited number of statements as long as only one value is returned.
  • Inline function
    • Returns a variable of data type table.
    • Can only contain a single SELECT statement.
    • The structure of the returned value is generated from the columns that compose the SELECT statement.
    • A table variable need not be explicitly declared and defined.
  • Table-valued function
    • Can contain any number of statements that populate the table variable to be returned.
    • Useful when you need to return a set of rows.
    • A table variable must be explicitly declared and defined.
    • An advantages over a view is that the function can contain multiple SQL statements whereas a view is composed of only one statement.

Differences between Stored Procedures and UDFs:

  • UDF can be used in SQL statements whereas SPs can’t.
  • UDFs that return tables can be treated as another rowset and can be used in JOINs.
  • Inline UDF's can be thought of as views that take parameters and can be used in JOINs etc.

Monday 14 December 2009

Model-View-ViewModel (MVVM) pattern

MVVM is gaining considerable traction in the WPF and Silverlight communities. The MVVM pattern can be said to be a specialisation of Fowler’s Presentation Model pattern.

The essence of a Presentation Model is of a fully self-contained class that represents all the data and behavior of the UI window, but without any of the controls used to render that UI on the screen. A view then simply projects the state of the presentation model onto the glass.

There is also some similarity with the Model-View-Presenter (MVP) pattern, with the ViewModel being roughly analogous to the Presenter.  However, unlike MVP the ViewModel doesn’t need a reference to the View – the view uses data binding to be made aware of changes. The ViewModel and not the View performs all modifications made to the model data.

Some features of MVVM:

  • View classes are unaware of Model classes
  • ViewModel and Model classes are unaware View classes (the ViewModel mustn’t assume types of rendering in the View  - e.g. that a button exists)
  • Model classes are unaware of ViewModel and View classes
  • ViewModel classes are aware of Model classes
  • View classes are aware of ViewModel classes

To summarise:

Aware of View Aware of Model Aware of ViewModel
View N/A No Yes
Model No N/A No
ViewModel No Yes N/A

Data binding facilitates loose coupling between the View and the ViewModel. It also supports a standardised input validation model.

The data is typically implemented as properties on the ViewModel. The View consumes that data via data binding. Application logic is typically implemented as methods on the ViewModel that the View can invoke through commanding.

A possible weakness with MVVM in Silverlight applications is the lack of support for Commands.

The following are the differences between Silverlight and WPF regarding commanding:

  • Routed commands are not available in Silverlight. However, the ICommand interface is available in Silverlight, allowing developers to create their own custom commands. The Composite Application Library provides the DelegateCommand and CompositeCommand classes to simplify command implementation. For more information, see the Commands technical concept.
  • In WPF, controls can be hooked up to commands through their Command property. By doing this, developers can declaratively associate controls and commands. This also means a control can interact with a command so that it can invoke the command and have its enabled status automatically updated. Silverlight controls do not currently support the Command property. To help work around this issue, the Composite Application Library provides an attached property-based extensibility mechanism that allows commands and controls to be declaratively associated with each other. For more information, see "Using Commands in Silverlight Commands" in the Commands technical concept.
  • There is no input gesture or input binding support in Silverlight.

http://msdn.microsoft.com/en-us/library/dd458872.aspx

ViewModel classes are easy to test, taking key logic out of the dreaded code behind files.

I need to check out a few things:

See also http://andysonlinenotes.blogspot.com/2010/06/stuff-i-should-be-interested-in.html

Saturday 5 December 2009

?length=5 at the end of an ActionLink

I tried to add an action link to a view which would link to an action on a different controller but the links did not generate correctly and ended with ?length=5.

The following ActionLink was used on a view used by a job controller (JobController):

<%= Html.ActionLink("Edit", "Edit", "Query", new{ id = item.Id }) %>

This should have generated a link to the Edit action on the Query controller. However the links generated as follows:

http://<server here>/Job/Edit?Length=5

In other words they were still generated as if they were on the Job controller (I was using the standard {controller}/{action}/{id} type route).

The fix is to add null at the end of the call to ActionRoute:

<%= Html.ActionLink("Edit", "Edit", "Query", new{ id = item.Id }, null) %>

There are a stack of overrides of the ActionLink method and this forced ASP.Net MVC to use the right one.