Posts

SQL Server vs .NET DateTime Leap Year Arithmetic

I recently refactored some code that was performing date arithmetic in SQL Server. The code was attempting to retrieve the "same day next year" by adding 365 days to the date provided. I was refactoring this logic out of SQL Server and in to .NET, and I was concerned that .NET's DateTime arithmetic wouldn't match SQL Server. I was pleasantly surprised that the results were the same. However, I should call out that perhaps in your situation adding 365 days to a given date is not appropriate for getting the "same day next year" value. I also compared this logic when preforming a "Add years + 1" and the results were also the same. (However, to be clear, adding 365 days differed from adding 1 year, but both SQL and .NET preformed the same given the method used.)

source code: https://gist.github.com/aaronhoffman/3d997390dab7f69e6597

results table:

MethodValueStartDate.NET ResultSQL ResultAddDays3652/27/20112/27/20122/27/2012AddYears12/27/20112/27/20122/…

Generate SQL Statements to Group By Each Column of Table Separately

When working with data with the intent to visualize, there are times when I'll want to group by every column in a table, separately, one at a time, to determine the possible values in that column.

I do this often enough I created a simple SQL Query to build these group by statements for me:


declare @table_name varchar(200) = 'dbo.mytablename'
select
'select ' + c.name + ', count(1) cnt from ' + @table_name + ' group by ' + c.name + ' order by 2 '
from sys.columns c
where c.object_id = object_id(@table_name)

gist: https://gist.github.com/aaronhoffman/d49d3705fd716b0fe6cb

Hope this helps,
Aaron





ASP.NET MVC 5 Identity, Users, Roles, Accounts, SQL to Generate Tables

In ASP.NET MVC 5, user management changed significantly from ASP.NET MVC 4.

This is the start of a series of blog posts on the topic (long overdue).

This post is on the SQL that your website executes to initially create the identity (user, role) tables.

The SQL the ApplicationUserManager and ApplicationSigninManager generate and execute is below:



CREATE TABLE [dbo].[AspNetRoles] (
    [Id] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY ([Id])
)
go
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] [nvarchar](128) NOT NULL,
    [RoleId] [nvarchar](128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId])
)
go
CREATE TABLE [dbo].[AspNetUsers] (
    [Id] [nvarchar](128) NOT NULL,
    [Email] [nvarchar](256),
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max),
    [SecurityStamp] [nvarchar](max),
    [PhoneNumber] [nvarchar](max),
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled]…

Install and Setup Windows 10 for a Developer

Image
I have been writing code on the Microsoft Stack for around 10 years and have set up quite a few developer machines over the years. Starting with Windows XP, Visual Studio 2003, and SQL Server 2000, and on every version of Windows, VS, .NET, and SQL Server since. If there is a quirk with installing dev tools, I've likely run into it. I recently upgraded my primary dev machine to Windows 10, Visual Studio Enterprise 2015, and SQL Server 2014, and decided to capture the steps in this blog so I could point other people to it.

Notice: Obviously, these are my preferences, and may not work for your situation. I am writing this post after running this setup for about a week* (edit: about 6 months now and still good). I will update this post as I continue to work with Windows 10.

Objective From scratch, set up a developer machine with the following:

1. Windows 10 Pro
2. SQL Server 2014 Developer Edition
3. Visual Studio 2015 Enterprise Edition
4. Other dev tools (git, Sublime Text, Notepad+…

Import-Module The specified module OneGet was not loaded because no valid module file was found in any module directory

Windows 10, PowerShell, OneGet, PackageManagement, Chocolatey


You may have tried the command:

Import-Module -Name OneGet


And received this message:

Import-Module : The specified module 'OneGet' was not loaded because no valid module file was found in any module directory.


But that's okay, because OneGet was renamed to PackageManagement. So just run this instead:

Import-Module -Name PackageManagement


And really, you probably don't even need to run that command at all.


Sources:
http://www.fixedbyvonnie.com/2014/11/5-minute-setup-using-oneget-chocolatey-windows-10/
http://blogs.technet.com/b/packagemanagement/archive/2015/05/05/10-things-about-oneget-that-are-completely-different-than-you-think.aspx