monitor sql server table to track changes in .net c# projects

Today I found a class in the .net framework which makes it possible to get events about data changes of a sql server table (idea of a trigger, but outside the db in a higher layer) what is perfect for caches: SqlDependency. Internally it uses the service broker of the sql server what sounds quite reliable in comparison to watch dogs. (About 2 years ago I made such a watch dog framework,... my experience: don't do that...)

kr,
Daniel

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

http://stackoverflow.com/questions/5288434/how-to-monitor-sql-server-table-changes-by-using-c

https://github.com/dyatchenko/ServiceBrokerListener

REST APIs

found 2 very nice projects which are used to develop REST APIs. They are designed high level and can be used for code generation and documentation.

• http://swagger.io/
• http://raml.org/

with the ability to move swagger to raml using the project swagger to raml ( http://raml.org/projects/projects#q:swagger2raml ).

Important for me: both work with Java and .NET ... Swagger even has Client Generation for Angular (1 and 2).

Currently not available: .NET WebApi which would be great.

kr,

Daniel

Powershell functions

I needed a simple .NET application with less than 10 lines of code... after writing the code in less than 2 minutes I thought about the maintenance, deployment, version control and so on...

Finally I decided to change the c# code into powershell and was surprised how easy it was to call .NET functions and how hard it was to work with functions... (this is a different example but with better param-handling and with about the same complexity).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

function CountOfDaysSince { param( [Parameter(Mandatory=$True ,Position=0)] [DateTime] $date, [Parameter(Mandatory=$False )] [bool ] $logging = $false); [int] $delta = ([System.DateTime]::Now.Subtract($date)).TotalDays; if($logging) { "Date is : " + $date.ToShortDateString() | Write-host ; "Diff in Days to now: " + $delta | Write-host ; } return $delta } $x=CountOfDaysSince (New-Object System.DateTime(2012,08,04)) -logging $false [int] $count = $x[-1] Write-Host $count

For me functions are quite complex because I am not used to the syntax or the way how powershell functions are used. Even the naming is not very powershell-ish (Probably Count-DaysSince?). Writing return $delta returns $delta, but in fact the return type is object[]. I read on stackoverflow that return $a is equivalent to "$a; return;" what opens up the possibility to easily return 2 return values like "$a; $b; return". This is quite similar to stored procedures in SQL Server where a stored procedure can return multiple record sets. Nice, but not very intuitive. Thats the reason why line 18 gets one array item. With index -1 you will receive the last element of the array, which is also nice, but... :-)

kr,
Daniel

WPF and Castle Windsor

Before using Castle Windsor I used to implement a provider keeping static references to view-models and reference these in the View using Xaml Databinding... something like DataContext="{x:static ViewModelProvider.MainViewModel}".

Now after getting to know Castle Windsor and its ability to handle references it makes totally sense to solve that differently (or to be concrete: to solve that better). For development time we still need to hard code a design-time-view-model cause else we loose all the features of the IDE... Nevertheless Windsor should take over the control about the wiring of View and ViewModel. Main reason therefore is the power (features like Interceptors) and the flexibility (DynamicParameters, Load Config from XML, work with interfaces) coming up with.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

public class ActivatorWPFVM<T> : DefaultComponentActivator { public ActivatorWPFVM(ComponentModel model, IKernelInternal kernel, ComponentInstanceDelegate onCreation, ComponentInstanceDelegate onDestruction) : base(model, kernel, onCreation, onDestruction) { } protected override object CreateInstance(CreationContext context, ConstructorCandidate constructor, object[] arguments) { var component = base.CreateInstance(context, constructor, arguments); if(component is Window) { ((Window)component).DataContext = DIProvider.Container.Resolve<T>(); } return component; } }

So by calling...

container.Register(
   Component
      .For<MainWindow>() 
      .Activator<ActivatorWPFVM<MainViewModel> >() 
      .LifestyleTransient());

... we can create a Window reference with an already wired up connection to its ViewModel (in config section where it should be).

kr,

Daniel

sqlcmd nowadays

Hi,

it is quite uncommon for MS-SQL Server-DBAs to work in command line (or much more uncommon in comparison to oracle or pg-sql DBAs). Nevertheless there is a tool from the stone-age called sqlcmd which has very nice features and which is actively developed by Microsoft up to now. See here: http://www.sqlserverspecialists.com/2012/10/sqlcmd-commands-sql-server-tool.html and https://msdn.microsoft.com/en-us/library/ms162773.aspx .

Main thing is:
- connect to different db
- call dos commands (mixing up OS and DB)
- set and use variables
- load sql files for execution
- redirect output to filestreams (file, stdout, stderr)
- change output format to XML

... even if this sounds quite limited it is still helpful for many purposes like e.g.: scheduled tasks. Especially the OS / DB combination is often very useful if you need to wait for the execution of A to start db-job B.

In SQL Server Management Studio the so called sqlcmd-mode to be used inside the query window is implemented which allows a limited feature set in comparison to the command line features seen above. See: https://msdn.microsoft.com/en-us/library/ms174187.aspx

Using SQL Server Agent you need to use an Operating-System Job-Step to work with sqlcmd (like with any other OS application). See an article about differences between TSQL-JobSteps and CmdExec using sqlcmd here: http://www.travisgan.com/2014/04/sql-server-agent-job-and-sqlcmd.html

So finally: with sqlcmd scripting can be improved by using variables, call different scripts and call the OS. In the original command line version it has some possibilities (like opening a dedicated admin connection) which are nice, but another very helpful thing is that it is kept small and performant.

Kr,
Daniel

simple secure data safe

Hi,

today I added a new github repo which contains a script toggling a folder to be

1) a password secured file or
2) a data folder to work in

https://github.com/starkeeny/SSDS

I will go on working on this, but I think it is a nice start for securing my data.

Btw: there is a nice hint from codeproject related to the topic of securing data http://www.codeproject.com/Articles/1151836/How-to-hide-your-files-in-windows-using-unmounted , but I think to remove the drive letter from a drive is not my favourite solution. Nevertheless as a user I wouldn't have expected to find data on an "un-named" drive... so this is a nice and still simple trick...


Kr,
Daniel

SQL Server - Providing Demo Data (temporary)

Today I found a new solution to provide temporary data! Additionally to creating a table (not really temporary), creating a temp table (create table #xy) and declare a table variable (declare @xy table(...)) there is also the possibility to put values directly into a select statement:

select * from

(

  values 

    (1,2,3),

    (2,3,4),

    (3,4,5)

) as myValues (nr1, nr2, nr3);

this looks quite easy and can be very useful in a with block, because you can use this adhoc-values like a table and if it is necessary you can move them very easily into a table.

with config as (

  select * from ( values ('dataFolder', '/home/...') as config (key, val)

)

select 

  dataFolder.Value + data.FileName, data.Content

from 

  data, config dataFolder

where 

   dataFolder.Key = 'dataFolder'

... but this is probably not the best show case... I think demonstration / training about SQL would be a better use-case (e.g.: showing how join statements work or stuff like this... would be the first training explaining "with" before "join" :-) ... nevertheless I was fascinated and love this feature...).

kr,

Daniel