Selfelected

Convert dotnet DateTime to Sqlserver SqlDateTime

The SqlDateTime has a narrower span than the Dotnet ditto; dotnet starts from year 0 but sqlserver from 1753.  This means that uninitialised DateTime fields in dotnet will give runtime execution errors when trying to persist them in a sqlserver database.

I write together a small and simple method for making sure the DateTime interval is inside the valid SqlDateTime interval.

public static class DateTimeExtensions
    {
        /// <summary>This method returns the datetime fitting into min/max of Sqlserver
        /// as per http://codebetter.com/petervanooijen/2008/08/21/valid-date-time-values-in-sql-server-sqldatetime-vs-datetime/
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DateTime ToValidSqlserverDatetime(this DateTime dt)
        {
            //  Must cast System.Data.SqlTypes.SqlDateTime.MinValue: http://codebetter.com/petervanooijen/2008/08/21/valid-date-time-values-in-sql-server-sqldatetime-vs-datetime/
            if (dt < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
            {
                return (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else if (dt > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue)
            {
                return (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;
            }
            else
            {
                return dt;
            }
        }
    }

The code is easier to read at pastebin.

Honour those who should.

Scope_Identity doesn’t return an Int in sqlserver but a Decimal

This is an old item.  Scope_Identity in Sqlserver doesn’t return an Int but a Decimal.  So in Dapper the call is

                var id = conn.Query<Decimal>(...

Honour those who should and here.

VB.Net and lambda

Using the terse lambda syntax used in C# also in VB.Net is also (almost) possible.

Here is a short example:

var nameList =
 myFacade.GetData().
 where( x => x.ID = 42 ).
 select( x => x.Name ).
 ToList();

which in VBNet becomes:

Dim nameList =
 myFacade.GetData().
 Where( Function(x) x.ID = 42 ).
 Select( Function(x) x.Name ).
 ToList()

Note that the periods have to be at the end of the line, otherwise the multi line capability of Vbnet doesn’t work.  This can be remedied by the old trailing underscore of VB.

If you need to set the type of x it is also doable:

Dim nameList =
 myFacade.GetData().
 Where( Function(x As MyClass) x.ID = 42 ).
 Select( Function(x As MyClass) x.Name ).
 ToList()

Unit tests, TDD, BDD

Maybe we shouldn’t think of unit tests, TDD or BDD but more of go no go tests. Whatever you automatically test it has a Go/NoGo, Yes/No, Success/Fail result.

C#: string.ToUpper() and char.ToUpper()

In C# there is a

"mystring".ToUpper()

but nothing in the char class.  Instead use

char.ToUpper('x')

… or write an extension method.

Singleton in dotnet

Singleton is often considered the simplest pattern but I digress. Except for the simplest cases writing a singleton requires thorough knowledge about the language at hand; one really has to know the locks and ifs and constructors to make a zyzygy.

Below is linked a good article. I don’t consider it as wordy since it really explains the subject in depth in a simple way.

There are 6 examples where 4 can be used. At the bottom of the article is a short explanation if you don’t want to grok it.

– http://csharpindepth.com/Articles/General/Singleton.aspx

Aspnetmvc 4 and standard dotnet membership and roles

I recently added the aspnet way of handling membership and roles to an experimental site.

One should not write one’s own usr/pwd handling since there are too many places to go wrong.

The MS tutorials all take for granted we want our users in a sqlexpress database situated as app_data in the web project.  For most real solutions we want to have the users in a database of our own pick.  Pointing aspnet to this one is not hard but has some caveats.

Update your database either through aspnet_regsql.exe found in the dotnet framework folder.  This application creates a bunch of stored procedures in the database you chose.  If you don’t want everything – say you only want roles and membership but not personalization or schema you have to run aspnet_regsql.exe with parameters.  You can also run some scripts to do the same, the same scripts that aspnet_regsql.exe uses.

Then you have to point your site (web.config) to use the database.  There is already a default membership provider in machine.config and we want to override it.

Create your ususal connection string.

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString=...;MultipleActiveResultSets=True" />
  </connectionStrings>
...

Then to point at it scroll down to (aspnetmvc template has already created it) membership and update.  Check the bold for clues.
“Clear” is used for overriding the machine.config value.  And since you override it you have to provide a new; that is where “defaultProvider” comes in so set it to the same value as “name”.  Finally  “connectionStringName” must be equal to your connectionstring’s name as above.

    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <clear />
        <add connectionStringName="DefaultConnection"              ...
             name="DefaultMembershipProvider"               ...
         />
      </providers>
     </membership>

Then you probably want Roles to also be handled in your own database so do the same for “roleManager”.

All this can be found on the web but I spent an hour on it so I thought I’d jot it down for future googling.

Sources:
http://computerscribe.com/blogs/BlogPost?id=11
http://www.asp.net/security/tutorials/creating-the-membership-schema-in-sql-server-cs
http://forums.asp.net/t/978442.aspx/1 

Version managing a Solution in Visual studio

There is no problem versioning a project  – just edit the AssemblyInfo.cs file.  But how does one update all assemblies in a solution without having to update this file for every project?

Create a AssemblyInfo.shared.cs file in any project and fill it with the versioning parts.  Then link this file from all the other projects through the dropdown by the add button in the add existing file dialogue. One cannot create a file directly in the Properties folder so add it in the root of the project and then move it to the Properties folder through drag and drop.

As of VS2013 one can copy-paste the linked file and it is pasted as linked. This makes it very fast to insert a linked file in several projects in the same solution.

Semi hidden Add as link.

VB.Net requires a trick

The trick to do it is VB.Net (as of Visual studio 2012) is to update the vbproj file to something like

Above Include… is where the file is linked from and Link… is where it shows up in the target project.
Presumably the easiest way to do it is to use the shared file in the root of a project and then link it through editing the project. There is no way to directly link a file into My Project folder but one has to first link it to some other place in the target project and them move it through this hack.

(sorry for the > and < but it’s late and I really want to get the stuff into production, not update a blog)

Dynamic data

I found a trick at Stack overflow that uses T4 to enter dynamic data.

Constant reference

It is also possible to reference a constant.

<Assembly: AssemblyVersion(Common.Constants.Version)>
<Assembly: AssemblyFileVersion(Common.Constants.Version)>

and in a common file:

public const string Version = “7.1.3.*”;

I believe the value has to be a constant and not a readonly. This might (I have not bothered to check) make a difference when the common DLL is compiled but not the referencing assembly as constants are copied to the referencing assembly. A remedy might be to rebuild all.

Team city

I also cannot get it to work together with Team city.  Moving the version to a file with a different name makes Teamcity update AssemblyInfo.cs and then we get 2 references to the same [assembly: AssemblyVersion(“1.0.0.0”)].  Maybe this on Stackoverflow would save my day but I haven’t had the time to delve into it.
I have another solution idea and that is to set AssemblyInfo.cs as shared and then create a unique AssemblyInfo.project.cs for each project.  Have not had time for this either.

Honor those who should. Or as of later here.

Refactor code from presentation to business layer

Way too often the presentation layer contains business logic.  When I recognise this and need to refactor my working order is like this:

myButton_OnClick(... {
    ...do some business logic with control values 
    ...and object variables as input and object
}

I only want very little stuff in the very click event handler.  The less the click event knows about other controls, or the form, the easier it is to change or even remove it.

myButton_OnClick(... {
   DoThis()
}

That was an uninteresting refactoring. Not something worth blogging about. So I make it static, recompile and get rid of the references to object variables.

myButton_OnClick(... {
   DoThis(txtUsername.Text, txtSalary.Text, ddlCompany.Value) 
}

private static void DoThis(string  username, int salary, Company company ){
   ... do business stuff
}

Now I just move the method to the business layer and dig out the remaining bugs (the Company enum was maybe declared in the presentation layer).

myButton_OnClick(... {
   DoThis(txtUsername.Text, txtSalary.Text, ddlCompany.Value) 
}

private static void DoThis(string  username, int salary, Company company ){
   string username = txtUsername.Text
   int salary = txtSalary.Text
   Company company = ddlCompany.Value
   var bl = new BusinessLayer.User()
   bl.DoThis(username, salary, company) 
}

There is nothing magic with this working order.  It is just a way to refactor step by step to avoid to much non-compiling code at once.

Show connected users and disconnect them in Sqlserver through Tsql

The title says it all.

For example when one restores a database one has to disconnect all users.  Here is a script for that.

USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
 datediff(mi, @start, current_timestamp) < 5)
    begin
     DECLARE spids CURSOR FOR
      SELECT convert(varchar, spid) FROM sysprocesses
       WHERE dbid = db_id(@dbname)
     OPEN spids
     while(1=1)
     BEGIN
      FETCH spids INTO @spid
      IF @@fetch_status < 0 BREAK
      exec('kill ' + @spid)
     END
     DEALLOCATE spids
END
GO

I recently forgot to close the connection and looped through records so the connection pool was exhausted and people couldn’t log on to the system.  Typically something one finds out in a live system where many use your web at once.  At least that was the case for me.  Below is the code I ran over and over again while looping to make sure my updated code didn’t use too many connections in the pool.

USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')

Honor those who should.

There is also a longer version of the latter here.