Wednesday, April 30, 2014

variable count of parameters in a t-sql stored procedure (sql server)

Hi,

yesterday I faced the problem that I had to push a variable amount of parameters in a stored procedure. In C# we have a keyword called "params".

public void MyFunction(params int[] myIDsToPush)
{
}

and call it like this:  MyFunction(1, 2, 43);
        
I expected that the database supports such a concept too, especially sql server, but unfortunately that was not the case :-( . I needed a work around. 

It took some time to think about possible solutions (and yes of course some google research too) but finally I realized that I needed a memory to store the data and even if no array type was available or params option another memory was necessary. Then I thought about (object to string -) serialization and how serialization can help to pass the items to the stored procedure.

So I built the following algorithm which deserializes ids from a string to a table (idstring represents the parameter, idtable is the result table).



 -- render a comma separated list of IDs into a table
 -- this targets on identity columns
 -- input string
 declare @idstring nvarchar(max) =
  '1,2,3,4,5,6,7,8,9,10,11,12,9999,13,752268687,100,56,322223,5'

 -- output object
 declare @idtable Table(id int)
 -- object to work on
 declare @parsedID   int = 0

 -- iteration variable
 declare @i   int = 0
 -- sign to work on
 declare @c   nvarchar(10)
 -- walk over
 while(@i <= len(@idstring))
 begin
  -- query sign to work on
  set @c = substring(@idstring, @i, 1);

  if(@c = ',')
  begin
   insert into @idtable values (@parsedID)
   set @parsedID = 0;
  end else if(ISNUMERIC(@c) = 1) begin
   set @parsedID = @parsedID * 10 + (cast(@c as int));
  end;
  -- increment iteration variable to walk over next item
  set @i = @i + 1;
 end;
 if(@parsedID <> 0) begin
  insert into @idtable values (@parsedID)
 end;
 select * from @idtable



kind regards,
Daniel

EDIT: found more or less my solution in a technet article (2002) https://technet.microsoft.com/en-us/library/aa496058(v=sql.80).aspx

Tuesday, April 22, 2014

PowerShell - how to start

Hi,

today I am working with power shell and I had no clue :-( so I checked out how to get information about the commands themselves. I found the following commands:

  • Get-Help
    shows the help of the get-help command. Adding a command name as argument shows the help of the command in the current context: e.g.: get-help get-childitem.
  • Get-Command
    shows all commands provided. First argument provides a possibility to filter. e.g.: get-command *-service shows all commands with the end "-service".
  • Get-Member
    shows what items were pipelined out from the last statement e.g.: get-childitem | get-member ... you can see that .NET's System.IO.{File|Directory}Info objects were pushed into the pipeline using get-childitem.
  • Get-help about_*
    shows all entries which are help to power shell concepts.
The setup of a command is "verb"-"dash"-"singular noun" like in get-childitem, so it is easy to split commands with the dash e.g. if searching for commands for "*-servi*". (Power shell is not case-sensitive)
 
What is another cool feature is the -WhatIf and the -Confirm option which is a inherited property (meaning all items where it makes sense have that option). Every item must have implemented -WhatIf parameter if its execution effects the state of the machine 
e.g.: Remove-Item test.txt -WhatIf DOES NOT remove the file, but what it does is telling the user what would have been done, if -WhatIf wouldn't be set...

Remove-Item test.txt -WhatIf
What if: Performing the operation "Remove File" on target "PATHPATHPATH\test.text".

The confirm option (quite boring against whatif) asks if you are really really sure to do whatever you wanted to do. You can set both preferences by changing $WhatIfPreference or $ConfirmPreference.


... but for me as a starter I use the commands I know by heart e.g.: del instead of remove-item... this seems to be so natural for me and makes it much easier to use power shell as a newbe. I was interested what del does and I found out (using get-alias) that this is mapped to Remove-Item too. So dear power shell developers: aliases were a perfect idea :-). Even dir AND ls works. Power shell seems to be very useful! I am looking forward to learn some more.

kind regards,
Daniel

Creating a web page using JavaScript

Hi,

today I was working with javascript. I was trying to reuse my knowledge of .NET and OOP to build a webpage, but it didn't really worked:

  • Classes are functions (I think). 
  • IntelliSense in Visual Studio is bad. 
  • Using a loosely typed language makes it easy to fail.

... but never mind... let's see what I learned today. At first I realized that a function seems to be a good way to stick to OOP in javascript.

//-----------------------------------------------------------------------------
// class Credential
//-----------------------------------------------------------------------------
function Credential(userName, password) {
    // Username
    this.UserName = userName;
    // Password encrypted
    this.Password = password;
}

... as a C# developer my eyes were bleeding while I was staring at these lines of code (no data types and only these "functions"), but anyway, what we got is something like a class we can use with public members. Creating a "var x = new Credential('admin', 'password');" instantiates the class.

In the next step I created a Controller class for my webpage like this:

function PageController() {
 
}

and it simply seems to work as a class with no functionality. My next challenge was to add static members to this class, because I wanted to use this class as a static class only. I was surprised to add a variable outside of the function block, but with the following line it seem to work:

// stores username and password
PageController.CurrentCredentials = null;

and moreover I was even able to add non-static members from outside the function using PageController.prototype.XY... again something strange for the good old c# guy... So I added a function in the same way as I did with the member variable:

PageController.Main = function () {

}

This function was intended to build me the link between javascript and html (in the following my html-file):

<!DOCTYPE html>
<html>
    <head>
        <title>ITADOS</title>
        <link rel="stylesheet" type="text/css" href="main.css" />
<script type="text/javascript" src="PageController.js"></script>
    </head>
    <body onload="PageController.Main();">
    <div id="PageControllerPlaceHolder"></div>
    </body>
</html>

... as we can see here the control of the page will be handed over to the PageController in the onLoad method. I think it is the most proper way to call onLoad, because the whole page is already received and rendered when onLoad is called.

I extended the main method with some code to get the magic in there...

//-----------------------------------------------------------------------------
// static function Main
// This function is the entry point called by the html part.
//-----------------------------------------------------------------------------
PageController.Main = function () {

   var content = "";

   if(PageController.CurrentCredentials == null) {
      content =    "<h1>Login</h1>\n"+
                   "<div class='LoginBox'>\n" +
                   "<div>Username: <input type='text' id='username' /></div>\n" +
                   "<div>Password: <input type='password' id='password' /> <input type='button' value='Login' " +
                                         "onclick='PageController.Login();'/></div>\n" +
                   "</div>\n" ;       
    } else {
       content = "<img src='img.png'/>";
    }
    document.getElementById("PageControllerPlaceHolder").innerHTML = content;
}

what i did is:
  • check if we are already logged in
  • if so
    • show an image as success message
  • if not
    • show a login box and add all the code necessary to proceed
  • place the content to the html-div tag

Now for completness only I add this last function to the blog entry, but it is only a dummy test method:

//-----------------------------------------------------------------------------
// static function Login
// This function will be called when the credentials are submitted. It creates
// a user context and reinitializes the dashboard.
//-----------------------------------------------------------------------------
PageController.Login = function () {
    // TODO: make the credential check by calling a remote service...
    PageController.CurrentCredentials = new Credentials();
    PageController.Main();
}

... so what we still haven't seen is the css file of the page:

body {
background-color: orange;
}

h1 {
margin-top:50px;
text-align: center;
}

div.LoginBox {
position:absolute;
left:50%;
width:300px;
padding:3px;
margin-left:-150px;
background-color:rgba(255,255,255,0.3)
}

... now the whole code is posted in the blog entry and we can summarize:



First thing to mention is that object oriented programming is absolutly possible in javascript. I saw a lot of possiblities e.g.: inheritance (by using: SpecialCredentials.prototype = new Credentials(); ), static vars and functions, public variables (using: this.VarName=value;), private variables (using var VarName = value;) and the same with private and public functions; and constructors. What I haven't found is: interfaces, abstract classes and protected methods, but there most be a reason why they built typescript (typescriptlang.org quote: TypeScript offers classes, modules, and interfaces to help you build robust components.) and maybe http://coffeescript.org/ should be mentioned here too as an other example of javascript derivates.

It is easy to start with javascript (using a lot of google-support) and easy to see results. What is absolutly awesome is this php approach using innerHTML and placeholder instead of php's if this-that echo "some-stuff". Every php-programmer will easily be able to build such a webapplicaion as seen above using the way to work he is used to. The cool thing here is that you have the code executed locally and so it is fast as hell without any need of reloading a whole page. A disadvantage is that you have no browser history (in such an approach) what I missed very hard while debugging the login page.

What was absolutly surprising to me was the fact that my chrome web tools provided a perfect debugging-IDE where I can see javascript errors, can set breakpoints, see variables and their state and values... After some research I found out that the guy, who developed firebug, moved to google to build these chrome toolsets. So there was no way for this tool not become awesome.

What I want to point out at last is the proper separation between html, javascript and css. In my example the html file is an empty page which says: I want this way to look (using a css file) and this kind of business logic (using a javascript file) and in the second step it simply hands over the control calling a javascript function (like Main in my example). The css here is a list of style information which will/can be used. The javascript code contains the actual business logic and enables the page to be dynamic and useful. The problem here is that you can't build a service in pure javascript because you need server-side code to e.g.: get data. My next project in javascript will be to find out how to get data from a server in an easy way using (I think) websockets...

Kind Regards,
Daniel

Monday, April 14, 2014

ITADOS

Hi out there. I'm Daniel and this will be (or maybe is already) my blog. 

I work in a known austrian corporation as head of development in the service management department. As a service manager I keep an eye on the operating departments and support them with: it knowledge, administration, development and other stuff (or short it a d o s as I called my blog). 

Stuff I'm working with is:
- Visual Studio 2013
- .NET
- C#
- LINQ
- LINQ2SQL
- XML
- WPF
- ASP.NET
- HTML
- CSS
- T-SQL
- SQL Server administration
- Windows Server administration
- UML

I hope you will find some interesting news here and please feel free to leave some comments.

Cheers,
Daniel