Tuesday, October 31, 2017

mstest with test cases

I am still wondering why there is not better support in mstest for test-cases as it is in nunit like described in http://nunit.org/docs/2.5/testCase.html ... nevertheless I wrote a code snippet which makes life bit easier... I assemble the data to test into the test-name which is not that bad as it might sound, because in some cases e.g.: testing mathematical functions you want to see the input data which is used directly and so you would write it into the name anyway... so we can use the name of the test and reflect over it like:

         public static int[] GetIntArrayFromName()
        {
            StackTrace t = new StackTrace(skipFrames: 1);
            var frames = t.GetFrames();
            string name = frames.First().GetMethod().Name;
            return name.Split('_').Skip(1).Select(x => int.Parse(x)).ToArray();
        }

so if you call this function inside a test-method which is called something like Test_1_2_3 you will get an array like new[]{1,2,3} which might fit quite well.

            [TestMethod] public void Add_1() => AddMethod(GetIntArrayFromName());
            [TestMethod] public void Add_1_2() => AddMethod(GetIntArrayFromName());
            [TestMethod] public void Add_5_4_6_3_1() => AddMethod(GetIntArrayFromName());
            [TestMethod] public void Add_7_5_1_3_4() => AddMethod(GetIntArrayFromName());
           
the rest is copy / pasting which is easy...

just to mention it, there do is some kind of support using the data source attribute in mstest... see: https://msdn.microsoft.com/en-us/library/microsoft.visualstudio.testtools.unittesting.datasourceattribute.aspx and https://stackoverflow.com/questions/21608462/how-to-run-unit-test-with-multiple-datasource

Saturday, October 21, 2017

swagger integration into webapi project (Part 2 - .net core)

While trying to setup a test web-api solution in .net core I was wondering whether the swagger integration even works for .net core with the swashbuckle nuget and yes... it does work!

I used swashbuckle.aspnetcore (with .swagger / .swaggergen / .swaggerui)

the only things I had to add in startup.cs were:

ConfigureService:

  • addmvc
  • addmvccore
  • addapiexplorer
  • addswaggergen
    • swaggerdoc
    • includexmlcomments
Configure:
  • usemvc
  • useswagger
  • useswaggerUI
    • SwaggerEndpoint
done.

Every created controller will from now on be listed in swagger UI.

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:

https://stackoverflow.com/questions/20115881/how-to-get-stored-procedure-parameters-details
https://raresql.com/2014/01/18/sql-server-how-to-retrieve-the-metadata-of-a-stored-procedure/
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql

... 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 sqlfiddle.com we can validate that:
  create procedure t1(@x int) as
    select 1 as resultValue
  go

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

parameters can be queried using
select 
   '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:
https://stackoverflow.com/questions/25121021/generic-execution-of-stored-procedure-in-csharp

    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)
                {
                    sqlCommand.Parameters.AddRange(parameters.ToArray());
                }
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(dtData);
                }
            }
        }
        return dtData;
    }

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

https://www.exceptionnotfound.net/mapping-datatables-and-datarows-to-objects-in-csharp-and-net-using-reflection/

(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 https://www.nuget.org/packages/Microsoft.AspNet.WebApi.HelpPage/ 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: https://www.nuget.org/packages/Swashbuckle .

There is a perfect tutorial from redgate related to swashbuckle at: https://www.red-gate.com/simple-talk/dotnet/net-development/visual-studio-2017-swagger-building-documenting-web-apis/

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",
                    System.AppDomain.CurrentDomain.BaseDirectory);
    }

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: http://simpleinjector.readthedocs.io/en/latest/webapiintegration.html
  • GlobalConfiguration.Configure((config) =>
    {
      ((HttpConfiguration)config).MapHttpAttributeRoutes();
    });



kr,
Daniel

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

DATEPART(SECOND, CURRENT_TIMESTAMP)
DATEPART(MINUTE, CURRENT_TIMESTAMP)

DATEPART(HOUR,   CURRENT_TIMESTAMP)

(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)