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)

Friday, July 14, 2017

SQL Server - sp_search_code

Hi,

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

http://vyaskn.tripod.com/code/search_stored_procedure_code.txt
"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 asp.net 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<link rel="import" href="../bower_components/polymer/polymer-element.html">

<dom-module id="say-hello">
    
    <template>
        <style>
            :host {
                color: forestgreen;
            }
        </style>
        <div>Hello {{guy}}!</div>
    </template>
    <script>
        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.is, SayHello);
    </script>
</dom-module>
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 http://es6-features.org/#ClassDefinition which I used a lot.

kr,
Daniel

Friday, March 24, 2017

Fluent interface (API) in C#

Hi,

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:
1
2
3
4
5
6
7
8
    static class Fluent
    {
        public static T Do<T>(this T item, Action<T> method)
        {
            method(item);
            return item;
        }
    }

now i can work fluent like this:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    class Program
    {
        static void Main(string[] args)
        {
            Console.Out
                .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"));
        }
    }

great.

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: https://www.martinfowler.com/bliki/FluentInterface.html

kr,
Daniel

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.

So... 

  • 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) =>
                {
                    ((HttpConfiguration)config).MapHttpAttributeRoutes();

                    config.Routes.MapHttpRoute(
                        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
        [HttpGet]
        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 http://www.restapitutorial.com/ 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:");
            data.Open();
            
... 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