Sql: Convert Number to Digit

I worked on a project that needs to convert numbers to digits and display to the users. For example display $100.00 as "one Hundred Dollar and Zero Cents"
I couldn't find any system defined sproc or func in Sql Server to handle this request. So I set out to build a simple func to return USD in digits.

[This was one of my earliest Transact Sql sprocs that I wrote in Sql Server 5.0, I just converted the sproc logic to funcs in Sql 2000 here. There are CLR functions out there to do this job (for Sql 2005) however the Sql server instance was 2000, so I was not able to use Sql CLR function for this.]

Here are the main logic
1. Convert 0 to 9 to digits(1 to 'One', 2 to 'Two'...)

2. Convert 10 to 19 to digits (10 to 'Ten', 11 to 'Eleven'...)
3. Convert 10*2 to 10*9 to digits (20 to 'Twenty', 30 to 'Thirty'...)

4. Convert 100*x by appending step 1 result with 'Hundred' (100 to 'One'+' Hundreds'..)

5. Convert thousands, millions etc by appending appropriate labels. This is based on how the numbers are grouped to obtain thousands, millions etc in the requested currency

6. Take 2 decimal points and apply step 1 or 2 and append the result

7. Wrap all these funcs into an entry point function, loop through the numbers and append the results to a string.


You can easily alter the logic to add more currencies or port this to other RDBMS that supports funcs.
You can change the funcs with sprocs if your rdbms doesn't support funcs

That's all there to it!

download

-Paul

c#: nmap in c#

nmap is a very useful network discovery tool. In a recent project I needed to enumerate all windows computers in a subnet. After searching through a couple of sites, I couldn't find anything that suited my need. So I decided to build a library to use in my project.

[If all your computers are in a domain, you can very easily enumerate all computers by DirectoryServices class. However when you have domains and workgroups in your network, this may be the only way to get the machine list]

Here is how I tackled the problem.
1. Scan the entire subnet and enumerate active devices.


2. Resolve ip address to host names by our trusted System.Net.Dns namespace


3. telnet to 3389 on these enumerated devices. This will give all windows machines in the network. (check msdn for other standard windows port in case you want to use a different port)

4. Use multi-thread in the enumeration process to speed up the whole thing.I am using a utility class to hold the data for multi threading


5. Populate a struct with the name and ip value and return as a List<>
If you are going to use this a production environment, monitor the thread usage and adjust it either pulling from a config file or from registry/database.

P.S. I am using a wrong coding convention for method name here because I am extending a library that was already standardized in this coding convention

download

That's it!

-Paul

java: Pass parameters by name to Sql sproc

If you are using sproc to access data and your programming language is java, you might be interested in this small utility to pass parameters by name to any sproc or function in Sql Server (v7.0, 2000 and 2005 are tested)

I was developing a java application and all our business logics were in sprocs. Data access and manipulation were done by sprocs and functions. We were using Microsoft JDBC driver and everything was fine until we realized the clunkiness of passing parameters by position restriction.

In java to call a sproc, you need to use CallableStatement stmt = connection.prepareCall("{call sp_name(?,?,..)}")
There is no way to pass the name of the parameter to the sproc. I borrowed a technique from aspnet's DeriveParameter implementation to fix this issue. DeriveParameters calls sp_sproc_columns and then dynamically build the parameters with proper data type and data size

1. Define a SqlParam object with Name, Value, Type and IsOutput properties.


2. Create a method to get the sproc's parameters by calling sp_sproc_columns system sproc. Get the resultset, loop through the resultset and then build an arraylist of SqlParam object. (you can persist this in an application scoped object to improve performance)


3. In the calling function, you build the SqlParam arraylist with the same parameter name as the proc and the parameter values.


4. Loop through the derived param list and build the callable statement like {call sproc (?,?..)}.
Loop through the derived param list then match the parameter name to the calling param list and then build the actual parameter collection. This ensure that the order in which the parameters are added to the parameter collection matches the sproc parameter position.


5.Loop through the derived parameters and build the output parameters' value into your calling param list (so that you can access this from the calling method)


6. Build parameter name and value to debug/error log in the catch section

To Call a Function
1. You need to exclude "@RETURN_VALUE" and "@TABLE_RETURN_VALUE" in the buildParameter function
2. If the function is a table valued function, then use "select * from (?,?)" statement.
3. If the function is a scalar valued function, the use "select from function(?,?)" statement

download code

That's it!

Paul