The Problem

Recently I needed to count how many particular HL7 segments were in a given column. Rather, I wanted to find those with 2+. I needed a solution to find how many occurrences of a substring were in a target column to be searched.

I was about to post a question on StackOverflow on this, but found the answer myself bit a bit of help from a few SQL related questions that I found while searching (How to count instances of a character in a SQL column). Funny how SO’s ‘search’ functionality doesn’t show the questions related, but when you're composing the question’s title, it does a MUCH better job.

Here’s the question I would have posted:

         Consider a varchar column or varchar variable holding a value like this:

              ABC123|foo|bar|ABC123|987|ABC123|123|DEF|456|ABC|

          The goal is to count the number of times ABC123| appears in that given string/column.

          How would you write the algorithm in T-SQL for SQL 2005 and/or 2008?

I would suggest that mostly you’d want to take this sort of task to a higher level language, rather than do it at the database level. Sometimes you have no choice, and a customer is asking for this kind of information in a short timeframe.

The Solution

The solution here is fairly easy.

  • define the substring you want to find.
  • find the length of the original string
  • REPLACE that string with a blank
  • subtract the length of the replaced string from the length of the original
  • the remainder is the number of characters that were removed
  • the number of substrings found is found by dividing by the length of the substring
DECLARE  @Subj         VARCHAR(6000),
         @Find         VARCHAR(10),
         @NumInstances INT

SELECT @Find = 'OBX|' --this is the string we want to count the occurrences of         
       ,@Subj = 'MSH|^~\&|LAB|TEST|TEST||20100302132036||ORU^R01|987654321|T|2.3 PID|1||Z000000167||TESTLAST^TESTFIRST^TESTMIDDLE||19100101|M|||^^^^|||||||ABC123 PID|1||0000000135|9028520613|KGH^ECPAT^^^^||19561203|F|^^^^^||99 TESTING STREET^^TEST^CA^90210||555-333-4444|||M|NV|ABC123|ZYX987| PV1|1|P|EM^^^.|||||||||||||||ER||HP|||||||||||||||||||.||PRE| ORC|SC|0203:U00006R300.0000|||CM||||201003021319|TEST^TESTER^DR^HELLO^^^||TESTING^TEST^DR^WORLD^^^| OBR|1|0203:U00006R300.0000|0203:U00006R|Some Test^L300.0000^00024940^24356-8^Some Test Name^|||201003021319||| ||||201003021319|| TEST^TESTER^DR^HELLO^^^||||||201003021320||LAB|F||| TEST^TESTER^DR^HELLO^^^| OBX|1|ST|L300.0200^Clarity^000z4940^32167-9^Clarity|1|Clear||Clear|N|||F|||201003021320| OBX|2|ST|L300.0700^Colour^000z4940^5778-6^Colour|1|Yellow||Yellow|N|||F|||201003021320| OBX|3|NM|L300.1300^XGravity^000z4940^5811-5^XGravity|1|1.006||1-10|N|||F|||201003021320| OBX|4|NM|L300.1400^pH^000z4940^5803-2^pH|1|5.0||5.0-9.0|N|||F|||201003021320| OBX|5|ST|L300.1500^Protein^000z4940^5804-0^Protein|1|Negative|g/L|N|N|||F|||201003021320| '

SELECT @NumInstances = (LEN(@Subj) - LEN(REPLACE(@Subj,@Find,''))) / LEN(@Find)

SELECT @NumInstances AS NumInstancesFound

 

It’d take a tiny bit of modification to convert the logic to perform the REPLACE/LEN statement on a column, rather than a table.

SELECT  myColumn
FROM   myTable
WHERE  (LEN(myColumn) - LEN(REPLACE(myColumn,@mySubstring,''))) / LEN(@mySubstring) > 1 --where we want to find 2 or more occurrences

Any better ways?

Having solved the problem in a fairly string-manipulative way, I wondered if there were any other ways to achieve this in the database layer. The other possibilities:

  • Write a CLR stored procedure. Here you could use your .NET Framework features to EASILY bust this out with a simple one-liner: String.Split() and Array.Length(). Better? Perhaps for maintainability or the abstraction of the logic. It’s a bit heavy for my tastes.
  • Set-based. Given that’s what SQL is actually for, you could perform some gymnastics on the column/variable, but the amount of code would be much more than the REPLACE/LEN solution above.

Posted in: code blowout , sql , datalayer  Tags:
Actions: E-mail | Permalink | Comments (0) |
Phil posted on December 11, 2009 03:06

Here’s a class that’ll make your life easier when you want to deal with saving information in cookies on your user’s browser. Everyone needs a wrapper class for all those external data-stores – session, cookies, file system, web.config and app.config, registry, log files, etc. Here’s a class usable in ASP.NET Web Forms and ASP.NET MVC.

Wrapper Class

Here’s a static class that you can simply include in your web project, and refer to its static properties to get to your cookies. Any and all simple datatypes can be used, and heck, even serialized versions of your POCO objects can be saved/retrieved here. Image if you wanted to save those shopping cart items, a collection of user prefs, or whatever, you could simply override the .toString() method in your custom class.

Just Make Properties

The key pattern here is that you purposefully create new properties for each piece of data that you want to save/retrieve. This solves the problem of:

  • having to remember strings all over your project.
  • ensuring no duplicates exist – imagine if multiple developers created a defect by using the same string indexer for their cookie, and ended up stomping each other’s value?
  • typos in cookie names.

Instead, the data access cookie-retrieval is done through named properties. This solution solves all those potential problems. Here’s a peek at one of these properties.

public static string UserFullName
{
get { return GetCookieVal(CookieItem.UserFullName); }
set { UpdateCookieVal(CookieItem.UserFullName, value, 365); }
}

Enums Help

With the aforementioned ‘remembering strings’ problem, the pattern that this class uses relies internally on an enum to handle the naming of the value in the cookie. The enum will boil down to an integer, but really we don’t care what the key’s is actually stored as in the cookie. We really only care to access/read/save the values constantly and easily from our calling code.

Download

Download the cookie class, or copy/paste from below. You can see that I pre-loaded it with some amusing properties for your entertainment!

Be sure to change the ApplicationName const at the top.

Special thanks to Special-K!

using System;
using System.Web;

namespace MyNamespace
{
public class Cookies
{
private const string ApplicationName = "MyCoolApplication";

private enum CookieItem
{
UserGuid,
UserFullName,
UserLoginExpiry,
UserHadForBreakfast,
UserTimezone
}
/**************
All cookie values are accessible by public static methods.
No typos/duplicates are possible from calling code!
**************/

public static string UserFullName
{
get { return GetCookieVal(CookieItem.UserFullName); }
set { UpdateCookieVal(CookieItem.UserFullName, value, 365); }
}

public static Guid UserGuid
{
get { return new Guid(GetCookieVal(CookieItem.UserGuid)); }
set { UpdateCookieVal(CookieItem.UserGuid, value.ToString(), 365); }
}

public static DateTime UserLoginExpiry
{
get { return DateTime.Parse(GetCookieVal(CookieItem.UserLoginExpiry)); }
set { UpdateCookieVal(CookieItem.UserLoginExpiry, value.ToString(), 365); }
}

public static string UserHadForBreakfast
{
get { return GetCookieVal(CookieItem.UserHadForBreakfast); }
set { UpdateCookieVal(CookieItem.UserHadForBreakfast, value, 1); }
}

private static string GetCookieVal(CookieItem item)
{
HttpCookie cookie = GetAppCookie(false); //get the existing cookie
return (cookie != null && (cookie.Values[item.ToString()] != null)) //value or empty if doesn't exist
? cookie.Values[item.ToString()]
: string.Empty;
}

private static void UpdateCookieVal(CookieItem item, string val, int expireDays)
{
//get the existing cookie (or new if not exists)
HttpCookie cookie = GetAppCookie(true);

//modify its contents & meta.
cookie.Expires = DateTime.Now.AddDays(expireDays);
cookie.Values[item.ToString()] = val;

//add back to the http response to send back to the browser
HttpContext.Current.Response.Cookies.Add(cookie);
}

private static HttpCookie GetAppCookie(bool createIfDoesntExist)
{
//get the cookie or a new one if indicated
return HttpContext.Current.Request.Cookies[ApplicationName] ?? ((createIfDoesntExist) ? new HttpCookie(ApplicationName) : null);
}

}
}


Posted in: c# , asp.net , best practices , code blowout , development  Tags:
Actions: E-mail | Permalink | Comments (0) |

On two recent projects, I've had the need to write out the properties of multiple custom entities. The example here will be around the venerable Customer class. Let's pretend that a requirement would be to send an email each time a customer makes an order to a support rep in your company. Yes, we'll be logging the order to the database, but the value-add here is that the recipient of the email will receive a link to the order, plus all the details of the customer and order included in the email.

So we need to take an object, iterate through its properties and their values, and put into a string for the body of an email.

The first thought might be: "just loop through each property and write it to the email body". That's fine, but as soon as you add a property in the future, you need to remember to change the emailing code. That's not helpful for the developer needing to maintain this application. Let's look at the Reflection namespace in the .NET framework.

Reflection in .NET

Reflection lets you programmatically find out information about types in your assemblies at runtime. Using classes in the System.Reflection namespace, you can learn details of an class's methods & properties. In the topic at hand, we're interested in the names, values, and datatypes of properties. You could possibly use reflection to get info at runtime about a method's parameters.

Override Customer.ToString()

The Customer class needs its own ToString() method overridden. Here's the class:

 
using System.Reflection; 
using System.Text;
 
public class Customer{
    
    public string Name{ get; set; } 
    public string Email { get; set; }
    public string Address { get; set; } 
    public string City { get; set; } 
    public string State { get; set; } 
    public DateTime JoinDate { get; set; }
        
    public override string ToString() {
    
        StringBuilder personString = new StringBuilder(); 
    
        foreach (PropertyInfo pi in this.GetType().GetProperties()) {
            //get the name of the property and its value. 
            personString.AppendLine(string.Format("{0}: {1}", pi.Name, pi.GetValue(this,null))); 
        } 
 
        return personString.ToString(); 
    }
} 

 

Use It!

 
Customer cust = new Customer{
    Name = "Mike",
    Email = "some@dot.com",
    HomeAddress = "1 Some Street",
    State = "ZZ"};
 
string customerOuput = cust.ToString(); //now put this in your email body!
 
Console.WriteLine(customerOutput);
 
/* Console will show:
Name: Mike
Email: some@dot.com
HomeAddress: 1 Some Street
City: 
State: ZZ
JoinDate: 
*/

 

Consider This…

There are a few things to consider with this implementation:

  • collections aren't handled well.
  • complex datatypes aren't either.
  • consider customizing your implementation to include special formatting for DateTimes.
  • what happens when you want the output to have a well-formatted output for a property like HomeAddress. We'd probably want it to show as "Home Address".
  • consider handling null values better than writing "null".

Wrap Up

I know this will save developers time in two ways:

  1. Not having to iterate manually through X properties to build your string for email. That'll scale depending on the number of properties and your adeptness at Ctrl-C, Ctrl-V.
  2. When you add a new simple property, you will NOT have to adjust anything for it to show in the .toString() method.

How are you using System.Reflection?

Posted in: c# , development , code blowout , .net framework  Tags:
Actions: E-mail | Permalink | Comments (0) |
Phil posted on June 17, 2009 09:29

A new project has me writing up the same old Country/State/Province reference tables. My feeling is that these static (fairly static) entities should be normalized and referenced by foreign key. I had asked a StackOverflow question on whether other developers had this prebuilt set of country/state/province create scripts in their toolbelt.

This is a code garage sale! Never again worry about creating and loading country and province/state data.

Create The Schema

Create your Country and State tables with this CREATE SQL script. As always, name the State table whatever you like (ProvState, tblState, whathaveyou).  Some folks don't like table names to be the same as reserved keywords.

country_state_erd

Populating

Here is a collection of insert scripts to get the data populated quickly for you.

Have Fun

Some developers go further down the normalization path by creating a City table, but I usually pass on that. As always, that decision is largely dependant on the problem domain or task at hand.

Yippee for me – that’ll be fun trying to reconcile data when someone enters their city location incorrectly as “St. Paul” / “Tuscon” / “Pittsburg” instead of Saint Paul / Tucson/ Pittsburgh.

If you would like to contribute an insert script or two for a country that you would like to see here, just contact me with your script!


Posted in: code blowout , sql  Tags:
Actions: E-mail | Permalink | Comments (0) |
Phil posted on June 4, 2009 17:28

The Goal: Have your HL7 v3 converted by Mirth to a specified HL7 v2 message.

The Prerequisites

  • Using Mirth 1.8+. This may work with previous versions, but this solution hasn't been tested on anything but 1.8.
  • You know what your HL7 XML looks like. You have a sample message available.
  • You know what you want the HL7 v2 to look like.
  • You may be expecting repeating segments to be converted. That's OK, it will be covered here.

Let's Do It

1. Login to your Mirth Administrator, create a new channel.

  • Give your channel a name
  • Ensure the incoming datatype is set to HL7 v3
  • All the other defaults are OK
  • Save
1

2. Switch to the Source tab.

  • Ensure your Listener port is something unique.
  • All the other defaults are OK
1b

3. Switch to the Destinations tab.

  • Give the first Destination a good name
  • Change the connector type to anything
  • Save
3

4. Switch to the "Edit Transformer" menu option

  • Click "Add New Step"
  • Change the Type to "JavaScript". This walkthrough will take the JavaScript route as more code/mapping can be fit into one window. The other mappers may fit your style better!
  • Give it an appropriate name, hit Enter
4


4b

5. On the top right pane, click the Message Templates tab.

  • This is where your XML HL7 v3 template will go. If you don't have a template, you can make one up for your experimental/development purposes!
  • Find your XML HL7 v3 template, and paste it here.
55b

6. On the bottom right pane, find the Outbound Message Template. It will likely default to HL7 v3.0.

  • Ensure the data type dropdown shows HL7 v2.x.
  • Find your HL7 v2 template, and paste it here. Keep any default values that you have, but go through and prefix them with something unique to remind yourself to ensure that value is mapped from the HL7 v3 message. That'll help identify any mapping that has been missed.
66b

7. Click Message Trees

  • You'll see a tree representation of both your messages.
7
  • 8.     Now it's time to match up the elements that you want to go from the v3 to the v2. Draggy-droppy time! Repeat this for EACH data field that you want moved from the v3 to the v2.
  • Drill down into the Outbound Message Template. Find the v2 element that you want filled. (e.g. Patient Last Name at PID 5.1)
  • Pick the Green dot icon. Drag it over and drop onto to the JavaScript window.
  • You'll now have something like this in the JavaScript code window:
            tmp['PID']['PID.5']['PID.5.1']
  • Type the equals sign at the end of this line. We're going to assign this value to something after the next step. Now you'll have this:

tmp['PID']['PID.5']['PID.5.1'] =

  • Go back to the Inbound Message Template Tree. Drill down into where the Patient Last Name is at.
  • Drag and drop that Green dot icon over to the JavaScript window. Drop it off after the equals sign.
  • You should now have something like this code in your JavaScript window:
        tmp['PID']['PID.5']['PID.5.1']=msg['controlActProcess']['subject']['target']['identifiedPerson']['name']['family'].toString()   
  • Congrats, you've just mapped one property from v3 to v2.
  • Repeat the above step as necessary for all the fields that you need transformed from v3 to v2.
  • You code any steps by hand in JavaScript now that you have the basic syntax down. I'd suggest creating new Transformer steps for each v2 segment. It will help you find/fix problems that you find for a particular segment if/when they appear. It follows the idea of modularity.
88b
9. Use this sample channel for the full solution.  

10. Deploy your channel and use the Send Message command.

  • Copy/paste a V3 message with all the values filled in.
  • Click Send.
  • In the "Encoded Message" tab of the Destination Connector, you'll see your output HL7 v2.
 

Posted in: code blowout , mirth  Tags:
Actions: E-mail | Permalink | Comments (0) |
Phil posted on April 26, 2009 01:23

Taking the advice of Scott Hanselman today. It's a code garage sale. Everything of worth (you be the judge!) must be opened up and freed for WAY below cost.

SQL Server ADO Abstractor/Wrapper

never write a line of ADO.NET setup code again!

The first offering is a .NET class that you can reference in any project that needs to call SQL Server stored procedures. It takes care of spinning up the ADO.NET objects and closing them (mostly) when appropriate.

Use it to:

  • ExecNonQuery
  • ExecScalar
  • ExecDataReader
  • ExecDataSet

All you've got to do is supply it with:

  • a connection string (as managed by your own datalayer and/or app.config. Hardcode a string into it, go ahead, see if anyone cares),
  • a stored procedure name
  • any parameter names and values that you need.

Another great feature here is that this class implements the IDisposable interface. This means you can use it with the Using statement/construct, and it will go out of scope when execution steps out of the Using statement/construct. This saves you a line or two of code.

Here's some sample code showing how to use it:

C#

   1:      // run a non-query stored procedure
   2:     try{                
   3:          using (SqlAdoAbstractor db = new SQLDataHelper(someConnectionString))
   4:          {
   5:              db.AddParam("@MyNumericIdentifier", 1);
   6:              db.AddParam("@MyString", "Hello World");
   7:          
   8:              db.ExecNonQuery("MySproc");
   9:          }
  10:      }
  11:      catch (Exception ex) { throw; }
  12:              
  13:      // get a datareader
  14:     try{                
  15:          using (SqlAdoAbstractor db = new SQLDataHelper(someConnectionString))
  16:          {
  17:              db.AddParam("@SomeNumericIdentifier", 1);    
  18:          
  19:              SqlDataReader dr = db.ExecReader("ListAllEmployeesByRegionID");
  20:              
  21:              //iterate through your datareader as necessary
  22:              while (dr.Read()){
  23:                  //dr["EmpID"].ToString();
  24:              }
  25:              dr.Close(); //you must close the open reader 
  26:          }
  27:      }
  28:      catch (Exception ex) { throw; }
  29:      

VB

   1:  Using help As SqlAdoAbstractor = New SqlAdoAbstractor(someConnectionString)
   2:          help.AddParam("@MyNumericIdentifier", 1)
   3:          help.ExecNonQuery("MySproc")
   4:   
   5:      End Using
   6:   
   7:   
   8:      Using help As SqlAdoAbstractor = New SqlAdoAbstractor(someConnectionString)
   9:          help.AddParam("@MyNumericIdentifier", 1)
  10:          Dim dr As SqlDataReader = help.ExecReader("MySproc")
  11:   
  12:          While dr.Read
  13:              'dr("EmpID")
  14:          End While
  15:          dr.Close 'you must close the datareader yourself
  16:      End Using

Posted in: ado.net , c# , code blowout , datalayer , vb  Tags:
Actions: E-mail | Permalink | Comments (3) |