T-SQL – Where Is a Table Used?

Another “put it in my blog so I don’t lose it” T-SQL nugget.

Nicer than just a raw text-based search, a where-used search for a named table.

select distinct [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
  where (o.name = 'Cow') 
  order by sp.Name

Handy T-SQL Merge

Inserts records into target that aren’t in source, deletes records that are no longer in source.

MERGE CowTarget AS t
USING CowSource AS s
ON (t.TargetCowId = s.CowId)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (TargetCowId, TargetName, TargetMotto)
	VALUES (CowId, Name, Motto)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

My Git Cheat Sheet

I prefer to use git from the command line, rather than using a GUI-based tool.  Here are my favorite or most-used git commands.

Tools

  • Command line git – posh-git
  • Comes with Git for Windows – https://git-scm.com/download/win
  • After install, you have shortcut to “Git Shell”
  • NOTE: Tab for auto-completion in Git Shell

 

Setting up Kdiff3 as diff/merge tool

  • Download/install Kdiff3
  • Add to PATH: c:\Program Files\Kdiff3 (or equiv)
  • git config –global merge.tool kdiff3
  • git config –global diff.tool kdiff3

 

Cloning – get local of repo

 

List branches (local and remote)

  • git branch -a

 

Create new local branch

  • git branch 170502
  • git checkout -b 170502 master    // create new branch off specified branch (master)

 

Switch to new branch

  • git checkout 170502

 

Switch back to master (or another branch)

  • git checkout master

 

Push new branch (simple git push will suggest this)

  • git push –set-upstream origin 170502

 

Revert local changes (single file or all files)

  • git checkout HEAD <filename>
  • git checkout HEAD .

 

Doing diffs of local changes

  • git difftool <filename>              // diff local file to remote (same branch, if not staged)
  • git difftool master <branchname> — <filename>   // diff file across branches, remote copies
  • git difftool master — <filename>   // diff local file to different branch (remote)

 

Sequence for commit/push changes

  • git add .    — add file to list of things to be committed
  • git commit -m “some message” .    — commit to local repo
  • git push     — push to remote

To unstage a file (reverse of git add)

  • git reset <filename>

To merge changes back to master, from branch

  • On github.com site, find branch, do New Pull Request
    • This just shows all diffs, allows comments, but doesn’t yet create the pull request
  • Add title and comments
  • Review commits and changes
  • Create Pull Request
  • Verify no conflicts
  • (Can leave Pull Request sitting out there at this point)
  • Merge Pull Request
  • Can delete branch after merge (Trash icon in branch list)

 

Update local, after merge

  • git checkout master
    • Back to branch that you merged into
  • git pull
    • Update from remote (you get the just-merged changes)
  • git remote update origin –prune
    • Update local list of remote branches
  • git branch -d 170502
    • Delete local branch that you just merged

 

Switching to new branch that already exists on remote

  • git remote update origin –prune
    • Update local list of remotes
  • git checkout newbranch
    • Automatically set to track remote

 

Abandon a branch

  • git checkout master
    • Get off the branch
  • git branch -d mybranch
    • Delete local
  • Delete branch on github web site

 

Merging

  • Assume you’re working on a feature branch and want to periodically merge from master
  • git checkout master
  • git pull
  • git checkout mybranch
  • git merge master

 

If you have conflict

  • git mergetool filewithconflict
  • Resolve in mergetool (e.g. KDiff3) and save
  • Note that merged file is now included in list of files to be committed and that .orig file exists as untracked file
  • Remove untracked orig files
    • Option 1
      • git clean -n
      • git clean -f
    • Option 2 – just delete files

 

See also: Git Cheat Sheat by Tower

 

 

 

WPF Commands – A Pattern that Works

Using commands in WPF can be frustrating. Having so many different ways to do commanding in WPF leads to some confusion. There are multiple ways to wire up commands that are equally correct, though some patterns are cleaner than others.

Below is a pattern that works.

Create static property to expose command

Making the command a property rather than a variable makes the syntax in XAML a bit cleaner.  Using RoutedUICommand allows setting the text for the command. This is useful if you want to use the command in multiple places.

Here’s code for creating the command, e.g. in a MainWindow class:

private static RoutedUICommand _pressMeCommand = new RoutedUICommand("Press Me", "PressMe", typeof(MainWindow));
public static RoutedUICommand PressMeCommand
{
    get { return _pressMeCommand; }
}

Add CanExecute and Executed handlers

Add code in code-behind (or ViewModel) for handlers. Below, we have private handlers in MainWindow. In this example, we have a boolean indicating whether the command can be executed. You could also put the logic directly in CanExecute, if it’s not used elsewhere.

private void PressMe_CanExecute(object sender, CanExecuteRoutedEventArgs e)
{
    e.CanExecute = CowboyCanTalk;
}

private void PressMe_Executed(object sender, ExecutedRoutedEventArgs e)
{
    MessageBox.Show("Howdy howdy I'm a cowboy");
}

Set up command binding

You can do this from code or XAML.  Doing it in XAML is perhaps a bit cleaner. Below is an example of binding the above command to its handlers from XAML (for MainWindow). Note that we can just use command name instead of {x:Static syntax because we made the command a property.

<Window.CommandBindings>
    <CommandBinding Command="local:MainWindow.PressMeCommand"
                    CanExecute="PressMe_CanExecute"
                    Executed="PressMe_Executed"/>
</Window.CommandBindings>

 

Wire up a button

Below, we wire a button up to the command.  You could just set Content to text directly. This would be fine to do and is simpler than what we’re doing below. But putting the text into the RoutedUICommand is helpful if you use the command in more than one place (since you specify text in just one place—in the command). And menu items automatically pick up the command text. (NOTE: If you used this pattern for Content regularly, you could just put it in a style to hide the complexity).

<Button Command="local:MainWindow.PressMeCommand"
        Content="{Binding RelativeSource={RelativeSource Self}, Path=Command.Text}" />

Wire up a menu item

Below, we wire up a menu item in a context menu to the same command. Note that here we do have to use the x:Static syntax. But also note that we don’t need to specify text for the menu item—it comes from the RoutedUICommand.

<Window.ContextMenu>
    <ContextMenu>
        <MenuItem Command="{x:Static local:MainWindow.PressMeCommand}" />
    </ContextMenu>
</Window.ContextMenu>

Bottom line

  • We used RoutedUICommand so that we could attach text to the command and avoid specifying text for both button and menu item
  • Binding in XAML is a (tiny) bit less code than doing it in code-behind

Other thoughts

  • You could also use a DelegateCommand pattern, passing in lambdas for both CanExecute and Executed. Use of a DelegateCommand is common in MVVM architectures.

 

 

Object Disposal and BackgroundWorker Object in .NET

Here are a few notes about best practices related to:

  • IDisposable and an object’s Dispose() method
  • The using statement
  • Disposal of BackgroundWorker objects

(NOTE: BackgroundWorker object is no longer the preferred mechanism for doing work on a background thread in C#, given that the language supports task-basked asynchrony with the async/await constructs. However, many legacy applications still make use of the BackgroundWorker class).

Q: What’s the goal of the using statement and IDisposable (Dispose) interface?

A: (short) To tell an object when it can clean up unmanaged resources that it might be hanging onto

A: (longer)

  • .NET code can make use of managed resources (e.g. instantiate another .NET object) or unmanaged resources (e.g. open a file to read from it)
  • Managed resources are released by the garbage collector (GC) automatically
    • Note that this is non-deterministic, i.e. you can’t predict when an object will be GC’d
  • To release an unmanaged resource, code typically follows this pattern:
    • Release resource in finalizer  (~ syntax).  Finalizer called during GC, so unmanaged resource is then released when object is being GC’d
    • Optionally, can support IDisposable (Dispose method)
      • Client calls Dispose before object is GC’d to released unmanaged resource earlier than normal GC
      • Allows for deterministic destruction
      • using statement automates calling of Dispose on an object
    • Classes implementing Dispose will still get GC’d normally at a later time
      • If Dispose was called first, code typically tells GC not to call its finalizer, since it’s already done stuff done by the finalizer (GC.SuppressFinalization)
      • If client failed to call Dispose, finalizer runs normally, so unmanaged resources then get cleaned up before GC
    • Objects with finalizers take a little bit longer to be GC’d
    • Here’s how IDispose is typically implemented – http://csharp.2000things.com/2011/10/11/430-a-dispose-pattern-example/

 

Q: When should I use the using statement?

A: Typically, you should use the using statement to invoke Dispose on any object that implements IDisposable

 

Q: What happens if I don’t call Dispose or use the using statement?

A: (short) Unmanaged resources are (typically) released a bit later than they otherwise would be

A: (longer)

  • If you don’t call Dispose on an object that implements IDisposable, it typically hangs onto unmanaged resources until it is GC’d and then releases them
  • Depending on the type of resource, the first object may block access to the resource until it’s released
  • Failing to use using (or call Dispose) typically doesn’t lead to a memory leak. Rather, it just means that resources are released a bit later

 

Q: Should I use a using statement for a BackgroundWorker object?

A: (short) Yes, since BackgroundWorker has Dispose method (although calling Dispose doesn’t actually do anything)

A: (longer)

  • It’s okay to use using on BackgroundWorker, since it does implement IDisposable
  • BackgroundWorker, however, doesn’t actually do anything when Dispose is called.  Its parent class, Component, detaches from its ISite container, but this is only relevant in Windows Forms.
  • Calling Dispose does suppress finalization, which means that the BackgroundWorker will be GC’d a little bit sooner.  This is reason enough to use using on the BackgroundWorker.
  • The using statement for a BackgroundWorker does nothing with the BackgroundWorker’s event handlers (i.e. it doesn’t detach any event handlers)

 

Q: Should I detach event handlers in the handler for RunWorkerCompleted?

A: (short) No, you (typically) don’t need to explicitly detach event handlers for a BackgroundWorker

A: (longer)

  • In .NET, if two objects reference each other, but no other “root” object references either of them, they do both get garbage collected
  • If we have a WPF form that has a class-level reference to a BackgroundWorker
    • Assume that we instantiate the BackgroundWorker when user does something on the form and attach handlers (methods in form) to that instance
    • Form now has ref to BackgroundWorker (class-level ref) and BW has ref to form (via the handlers)
    • When form closes, if main application no longer has a reference to the form, both the form and the BackgroundWorker will be properly garbage collected even though they reference each other
  • You do need to detach handlers if you have a BackgroundWorker that is meant to live longer than the object that owns the handlers
    • g. If we had an application-level BackgroundWorker and forms that attached handlers to its DoWork or RunWorkerCompleted events.  If the BW was meant to live after the form closes, you’d want to have the form detach its handlers when it closed.

 

 

String Properties in ADO.NET Entity Data Model

(NOTE: This is Model First development in Entity Framework–start with a data model in the Entity Framework Designer and then generate database schema from the model).

When you add a scalar property of type string to an entity data model, you can fill in the values of several other properties within Visual Studio.

  • Fixed Length – (None), True, False
  • Max Length – (None), value, Max
  • Unicode – (None), True, False

stringprop

What does a value of (None) mean for these properties (when generating SQL Server Database from model)?

  • Fixed Length – (None) equivalent to False
  • Max Length – (None) equivalent to Max
  • Unicode – (None) equivalent to True

Here’s how choices for these properties map to SQL Server data types:

  • Fixed=True, Max=20 – nchar(20)
  • Fixed=(None), Max=20 – nvarchar(20)
  • Fixed=False, Max=20 – nvarchar(20)
  • Fixed=(None), Max=(None) – nvarchar(max)
  • Fixed=True, Max=(None) – nchar(4000)
  • Fixed=False, Max=(None) – nvarchar(max)
  • Fixed=(None), Max=Max – nvarchar(max)
  • Fixed=True, Max=Max – nchar(4000)
  • Fixed=False, Max=Max – nvarchar(max)
  • Fixed=(None), Max=20, Unicode=True – nvarchar(20)
  • Fixed=(None), Max=20, Unicode=False – varchar(20)

This means that if you just pick String as the type and set nothing else, you’ll get nvarchar(max) as the SQL Server type.

NOTE: The value of 4,000 is being used because the maximum length of a fixed length string in SQL Server is 4,000 characters (8,000 bytes when encoded as UTF-16).

 

Count Total Rows while Paging and Avoid Two Queries

This is well covered in many other places, but I’m posting on my own blog so that I can more easily find this trick.

You can do paging in SQL Server 2012 and later using the OFFSET-FETCH syntax. This is useful for returning a single page’s worth of records from a larger (sorted) set of records. To count the total number of records, however, you’d normally do a second query. The T-SQL below shows how to do a total count as part of the query that returns the page in question. The count is repeated for every record, but it avoids having to do two queries.

select FirstName, LastName, COUNT(*) over () as TotalCount
  from Person
  where LastName like 'mc%'
  order by LastName, FirstName
  offset 500 rows
  fetch next 100 rows only

Here’s what the output looks like, returning records 501-600 out of 1,968 records having a last name that starts with “Mc”.

161109-1