Entity Framework and SQL Server's Time datatype

For a current WPF project, I needed to store a time value on SQL Server 2008, using its handy time datatype. For data retrieval, I was using Entity Framework 6 (EF), and my models had been built with database-first; hence the entity models in Visual Studio were automatically generated by EF.

Interestingly, EF used the .NET TimeSpan type to map to the SQL Server time datatype. My view models (for MVVM pattern) had to match the TimeSpan type as well. This all seemed strange, until I realized there's no .NET Time type. Regardless of the strangeness, EF successfully converted values between TimeSpan and SQL Server time, as documented in this article.

Block Windows 10 forced updates

This looks promising in preventing the forced updates: Block Windows 10 forced updates without breaking your machine, part 1

SQL Server: build dynamic WHERE and ORDER BY clauses

For a recent project, I needed to build a dynamic SELECT statement without using lots of IF statements or resort to dynamic SQL statements, with the EXEC statement. Well, some research came to the rescue.

In my use case, the user chooses a search column from a dropdown box and then provides a search value. So the WHERE clause needs to be dynamic. This article provided the solution: using a combination of OR and AND statements to account for null values:

select * from Employee
 where 
   (lastName = @LastName AND @LastName IS NOT NULL)
  OR (employeeID = @employeeID AND @employeeID IS NOT NULL)
  OR (securityID = @securityID AND @securityID IS NOT NULL)
 order by
  case when @LastName is not null then CONVERT(char(50), lastName) + CONVERT(char(50), firstName) 
   when @employeeID is not null then employeeID 
   when @securityID is not null then securityID
  end

As for the ORDER BY clause, this article solved it. A CASE statement. Note also the multi-value option for the lastName column: I'd like to sort on lastName and then firstName. This is not allowed in an ORDER BY CASE statement; however, thanks to the 4 Guys from Rolla, the trick is to use string concatenation to solve this. Happy coding!

Find a column name across all tables on SQL Server

I'm doing a "black box" project, where I need to discover how a vendor app's database works. To that end, I've found this T-SQL useful in searching across the 140 tables for a column name, thanks to the tip from Pinal Dave's site,

USE myDatabase
GO

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnNameToFind%' 
ORDER BY schema_name, table_name;

Rename all empty SQL Server tables

I'm working on a large database on the developer server and need a way to sift through and analyze the data. This is for a project where we're going to discard these tables and build a new system; in the meantime, I need to work with the copy of the old data. To that end, I created this T-SQL script to append all empty tables' names with '__EMPTY'.

declare @new_name varchar(max), @query varchar(max)

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

;WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      (index_id = 0 OR index_id = 1)
      and (LEFT(OBJECT_NAME(OBJECT_ID), 3) <> 'sys')

   GROUP BY 
      OBJECT_ID
)

SELECT * into #temp
FROM TableRows
WHERE [RowCount] = 0
order by TableName

DECLARE db_cursor CURSOR FOR
 SELECT tablename
 FROM #temp

OPEN
db_cursor
declare @name varchar(max)
FETCH
NEXT FROM db_cursor INTO @name

WHILE (@@FETCH_STATUS = 0)

BEGIN
set @new_name = @name + '__EMPTY'
set @query = 'exec sp_rename ''' + @name + ''',''' + @new_name + ''''
exec(@query)

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
deallocate db_cursor

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

I used the technique described in this Stack Overflow post to find the empty tables.

SamsaraJS: Famo.us fork juices mobile Web UIs

The library provides native-like app experiences and takes on the mobile Web's performance issues: SamsaraJS: Famo.us fork juices mobile Web UIs

References greyed out in VBA window

I needed to add some references in an Access 2013 code module. Going to Tools, I found the References item greyed out. Research led to this article with a registry key to enable References:

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security]
"AccessVBOM"=dword:00000001

Note that I am an admin on this machine, running Office 2013 (hence the "15.0") and need VBA in Access. You'll need to modify the above to your needs.

Angular 1.5 nested views with ASP.NET MVC

This tutorial from Miguel Castro demonstrates a way to create single-page application (SPA) silos in ASP.NET MVC using its server-side routing, and merging that with Angular ng-view directive and routing to simulate nested views.

Great podcast with the Google Angular team

The Google Angular team sat down for the "Adventures in Angular" podcast, answering a ton of questions about the upcoming Version 2.0: https://devchat.tv/adventures-in-angular/078-aia-ng-beta-with-brad-green-mi-ko-hevery-and-igor-minar. An interesting tidbit... release date for RC is around March/April time-frame this year.

Disable Chrome sign-in screen

Chrome has a logon screen that can become annoying. Instructions here provided the answer. To permanently disable it on Windows,

1. Close out of Chrome. 
2. Go to C:\Users\user-name\AppData\Local\Google\Chrome\User Data\Default\
3. Open the Preferences file with Notepad (or other text editor).
4. Search for "sync_promo"
5. Initially, it will look something like "sync_promo": { "startup_count": 6}
6. Edit it to this:
   "sync_promo":{"startup_count":6,"user_skipped":true,"view_count":4}
   
7. Save the file.
8. Exit the text editor.
9. Open Chrome. Viola! No more annoying logon screen.

Stationary corner triangle DIV using CSS

Ever wanted a triangle DIV that hovers over the page and has a drop shadow? Here's one I built using nothing but CSS 3 and HTML5:
To modify the triangle, edit the border-width and border-color on the div#corner-triangle. Read more details on my Stack Overflow answer. You can also use the nifty CSS Triangle Generator.

Learning Angular quickly

If you need to get up-to-speed on AngularJS quickly, nothing beats the collection compiled by the always great Aaron Marisi: Learning Angular Quickly. I'm just getting into the podcasts he lists, and recommend his resources.

Great beginning Angular tutorial

If you're starting with AngularJS on Visual Studio, look no further than Aaron Marisi's Angular First blog. He explains the basics and some more advanced subjects clearly and simply. Plus he follows best practices and provides the code for all his examples. Lots to learn here!

Run a program on Windows startup

I had a shortcut to a batch file that needed to run on the startup of Windows 10 x64 Enterprise startup. All the various methods described (adding to the Startup folder, adding to regular Run key in registry) didn't work. Finally, this post on Stack Overflow solved the issue. This should work well on other versions of Windows too.

Because this is a 64-bit Windows, the change has to be made in Wow6432Node:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Run

There, you can add a new string value, give it a name, and then double-click to edit it. Place the full path of the program in double quotes. If it's something requiring administrator privileges, on startup, Windows will display the UAC dialog.