Friday, October 13, 2017

generic execution of stored procedures in c# accessing sql server

for generic execution of stored procedures I found some helpful links to generate code:

... but in fact I want to pass in generic data without much of validation before hand, because I want to keep it RAD (rapid application development) and test it with integration tests.

Nevertheless with we can validate that:
  create procedure t1(@x int) as
    select 1 as resultValue

can be executed using (e.g.):
  exec dbo.t1 3;

parameters can be queried using
   'Parameter_name' = name, 
   'Type'   = type_name(user_type_id),
   'Nullable' = is_nullable,
   'DirectionOut' = is_output,
   'Length'   = max_length, 
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier'
              then precision 
              else OdbcPrec(system_type_id, max_length, precision) end, 
   'Scale'   = OdbcScale(system_type_id, scale), 
   'Param_order'  = parameter_id, 
   'Collation'   = convert(sysname,
                   case when system_type_id in (35, 99, 167, 175, 231, 239) 
                   then ServerProperty('collation') end)  ,
   system_type_id, user_type_id
  from sys.parameters
  where object_id = object_id('dbo.t1')
  order by param_order

(first) result record set meta data can be queried using
   SELECT * FROM sys.dm_exec_describe_first_result_set ('exec dbo.t1 3', NULL, 0) ;  

So execute a stored procedure and retrieve a datatable can be achieved using the code from:

    public DataTable RunSP_ReturnDT(string procedureName, List<SqlParameter> parameters, string connectionString)
        DataTable dtData = new DataTable();
        using (SqlConnection sqlConn = new SqlConnection(connectionString))
            using (SqlCommand sqlCommand = new SqlCommand(procedureName, sqlConn))
                sqlCommand.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
        return dtData;

this link shows an easy way to map datatables and datarows to objects

(things dapper is doing for us in general).

So this opens up a lot of opportunities for strongly typed argument objects (or an on-the-fly generated instance from a json-string) and output handling with a list of strongly typed instances mapped by datarows. There only needs to be a mapping between class and procedure name AND a mapping between argument fields and parameter names.

swagger integration into webapi project

In the (let's say) "early days" of .net's webapi the controllers and its operations could be listed (with the option to try the operation) using the nuget which is currently out of maintenance (I believe) because the last update was in february 2015 which is 2,5 years ago.

During some research I found a perfect alternative which seems to be the more or less official successor: .

There is a perfect tutorial from redgate related to swashbuckle at:

It works in 5 minutes and allows to generate REST API Clients which means perfect fit between client and server.

Things I changed (after installing the nuget):

  • c.DocumentTitle
  • c.IgnoreObsoleteActions
  • c.IgnoreObsoleteProperties
  • c.IncludeXmlComments

    with the function from the redgate blog-entry (add xml documentation in properties)

    protected static string GetXmlCommentsPath()
                return System.String.Format(@"{0}\bin\webDemo.XML",

I don't needed to adapt the global.asax file from the root folder (and which does not work in a sub-folder which is very logical afterwards, but took me an 1 hour of research to find the bug).

In the global.asax (application_start) I still have:

  • simple-injector init (see:
  • GlobalConfiguration.Configure((config) =>


Wednesday, October 11, 2017

sql server - datetime to number

needed to transform minutes and seconds into a decimal number... it was not that easy as I thought originally... here the snippets:

I needed to find out the current hours, seconds and minutes



(works for every part of the current timestamp...)

afterwards I needed to convert it to decimal and assemble the parts into one number using convert.

something like:
select convert(double(6, 2), @hour * 100 + @min) + convert(double(6, 2), @sec);

Now to cut off seconds we just need to convert the number to int again (as in the good old days of programming).

set @hourAndMin = convert(int, @hourAndMinAndSeconds)

Friday, July 14, 2017

SQL Server - sp_search_code


the following stored procedure from the stone-age of sql server 7.0 is very helpful when searching for the usage of objects inside database code (up till now).
"Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved."

It queries the syscomments table so I would simplify the query to something like

   select * from syscomments c 
   where c.text like '%<TEXTTOCHECK>%' 

... but of course the check for encryption and object properties for filtering makes absolutely sense when you are allowed to deploy such a maintenance stored procedure. 

Wednesday, May 31, 2017

starting with polymer 2.0 in visual studio

I was looking for an alternative for angular 2/4 since I realized that typescript development is not so funny as it looks like... even not in visual studio... as an angular 1 developer I liked the features of angular but was kind of overwhelmed from the overhead I needed to setup the same thing in angular 2/4. So after some days of research I found polymer which fits perfectly.

With VS2017 we have full bower support which is great so just start with the download of the bower-package polymer (menu: project/bower-package management). The dependencies are installed automatically which makes it easy to startup my project. Additionally I added the bower_components folder into my project and started with an html page (more exactly with an page, but that does not really matter for this article).

On the same level as bower_components I added a folder elements where I wanted to place my polymer-elements. My "hello world"-example in here is a polymer-element called say-hello.html which says hello to a person who's name is added as a parameter.

The code of this html file is quite simple:

<link rel="import" href="../bower_components/polymer/polymer-element.html">

<dom-module id="say-hello">
            :host {
                color: forestgreen;
        <div>Hello {{guy}}!</div>
        class SayHello extends Polymer.Element {
            constructor() { super(); }
            static get is() { return 'say-hello'; }
            static get properties() {
                return {
                    guy: { type: String, value: '<default_name>' }
        customElements.define(, SayHello);
Without going into any further details say-hello.html can now print "Hello John!" in green to John if John is set as the parameter value of name in a call like <say-hello guy="John"></say-hello>

Very helpful resource for e.g.: the class definitions in javascript (ES6) is which I used a lot.


Friday, March 24, 2017

Fluent interface (API) in C#


I was wondering why "fluent API" or "fluent interface" brings so many developer into trouble. I do like that kind of style, but I am not sure I would sacrifice my whole development style for this nice calling structure.

As a C# developer I found a way to go for me. I wrote an extension method on object-level like this:
    static class Fluent
        public static T Do<T>(this T item, Action<T> method)
            return item;

now i can work fluent like this:
    class Program
        static void Main(string[] args)
                .Do(x => x.WriteLine("test1"))
                .Do(x => x.WriteLine("test2"))
                .Do(x => x.WriteLine("test3"))
                .Do(x => x.WriteLine("test4"))
                .Do(x => x.WriteLine("test5"))
                .Do(x => x.WriteLine("test6"));


It is probably not that expressive as it is in a real fluent interface, but it works for every single object in every single case...

Nevertheless see Martin Fowler's original post on FluentInterfaces:


Saturday, March 18, 2017

Lightning fast (minimal) setup for a JSON Rest API using WebAPI

While development time it is often necessary to mock services or create services for test. These don't need to consider security or performance issues, but have to return valid responses.


  • Create ASP.NET-WebApplication (currently I used regular .NET framework application and not CORE)
    • consider to use Azure or not (I believe that for test-services no Azure usage will be the default)
  • Empty Template
  • Add NuGets
    • Microsoft.AspNet.WebApi and its dependencies
    • (Newtonsoft.Json for JSON Operations)
    • (System.Data.SQLite for in Memory or file Database implementing Linq and standard interfaces usable for any DB-Communication library)
    • (Dapper for DB-Communication)
  • Add a global.asax file
    • Add the following code to Application_Start
GlobalConfiguration.Configure((config) =>

                        name: "DefaultApi",
                        routeTemplate: "api/{controller}/{id}",
                        defaults: new { id = RouteParameter.Optional }
  • Add a new Item (WebApi-Controller) ... I called it in my example MyController
  • I removed the code generated and implemented the following example for demonstration
        public IHttpActionResult GetData()
            return Ok(new List<string> { "data1", "data2", "data3" });
  • you can test this method (after starting in debugger) using the web browser
    see: http://localhost:63268/api/My/
    (Attention: the port is randomly generated by visual studio: check in your Properties, which port visual studio assigned you for the debugging sessions, or see which page is started when debugging session starts).
This small tutorial showed an easy way of creating a REST Api using WebApi. Consider using for further investigation into REST (especially which Http-Methods should be used for which operation) because using the wrong http-method can be really confusing.

If you added the nugets in the parenthesis you would be able to use better data stores than "List". By using:          
            data = new SQLiteConnection("Data Source=:memory:");
... you can create an in-memory datastore with an initialized connection object inheriting IDbConnection which is the standard interface for SqlConnection, OdbcConnection and all the others like oracle and stuff...

Using dapper makes a simple Get-Request to a really simple one-liner. See:

        [HttpGet, Route("api/InMemory/Dapper")]
        public IHttpActionResult TestDapper()
            return Ok(data.Query("select * from data"));

Consider that this storage is non-persistent, but could be perfectly used for testing purposes.

kr, Daniel

Tuesday, February 7, 2017

check for interfaces in c#

today an interesting question about interfaces came up. Does reflection's GetInterfaces() return all interfaces of all hierarchical levels?

    public interface ILevel1{}
    public interface ILevel2 : ILevel1, IAdditionalInterface2{}
    public interface ILevel3 : ILevel2{}
    public interface IAdditionalInterface {}
    public interface IAdditionalInterface2 { }
    public class MyClass : ILevel3, IAdditionalInterface {}

The answer is yes: it does!

MyClass implements any interface which can be tested using the 
  • "is"-operator (INSTANCE is INTERFACE_TO_CHECK) or 
  • INSTANCE.GetType().GetInterfaces().Contains(typeof( INTERFACE_TO_CHECK ))
both methods return the same.


Wednesday, January 25, 2017

FakeItEasy essentials

Today I had a closer look on FakeItEasy. A mockup (or faking) framework to create objects which can be configured freely from outside to be able to unit test classes working with these objects. Additionally it can create dummies (unneeded objects created to satisfy an interface).

FakeItEasy can be installed using nuget without any dependencies. Internally it uses the castle project (which makes me believe that FakeItEasy is more or less a super-powerfull dynamic proxy).

  • A.Fake<class or interface>(); (also CollectionOfFake)
    • tons of creation options can be added in overloaded Fake functions
      • e.g: WithArgumentsForConstructor, Implements,...
    • most interesting (for me) is .CallsBaseMethods so any object can be wrapped and be used with fakeiteasy magic
  • A.CallTo(...); // Properties: A.CallToSet
    • Arguments:
      • exact: "1", "xyz",..
      • by type: A<string>._
    • Conditions: WithReturnType, Where, To
    • ReturnValues: Throws, Returns, ReturnsNextFromSequence, ReturnsLayzily, ThrowsAsync, AssignOutAndRefParameters, AssignOutAndRefParametersLazily
    • Behaviors: DoesNothing, CallsBaseMethod, Invoke
    • Matchers: MustHaveHappened (Repeated), That.Matches(...)
  • Raise.Wtih


  • can not be used with static or sealed classes 
  • methods that are not virtual or abstract can not be overriden

It took me about 3 hours to read the docs and to test my sample, but I haven't found any show stoppers... Going to use it in my tests and look forward to write more about it...

Saturday, January 21, 2017

Check for usb-sticks in windows


I wanted to check in my app whether usb sticks are connected or not (automatic recognition)...

Some research later I found the following article:

works quite straight forward ...

things to mention:
- for different use-cases it might be enough to check Win32_LogicalDisk where drivetype=2

- in wpf you might use something like:

        protected override void OnSourceInitialized(EventArgs e)
            HwndSource source = PresentationSource.FromVisual(this) as HwndSource;

- when you disconnect a stick and call the wmi to show you data Win32_DiskDrive / Win32_LogicalDisk you get exception ... sleep and retry works after about 5 seconds.

- you can check the result in command line using "wmic logicaldisk get"


Wednesday, January 18, 2017

C# code generation for databases


today I found SQLMetal.exe...

It is part of the visual studio installation and can create c# and vb code for different kind of db artefacts... I am curious how this works in comparison to Entity Framework (= T4).


Tuesday, January 3, 2017

vim as a command line util

For quick find, edit, search and/or replace actions vi | vim | gvim is a perfect tool. A research question for me was: is it also a tool for automation? I wanted to search and replace strings with vi-syntax through a batch file (yeah... using windows makes the thing even more special) which can be scheduled or called on demand.

... and yes... it works. see: ... the trick is to start in "Ex-Mode" ... (see: documentation), but unfortunately it did not worked for me... I was not able to make it work in my script... no idea why... but:

then I found the -c option which is simple and works perfectly... -c executes a command like substitutions or other stuff and can be used in a chain of "-c"s.

my test-environment:
echo. >> data.txt
del data.txt
echo data data data > data.txt

type data.txt
gvim -c %%s/data/0101/g -c wq data.txt
echo _____________________________________________________
type data.txt

it outputs data before the substitution and 0101 after it... perfect!