Apr 11 2017

Switch to read JSON vs raw HTML

Category: C#Administrator @ 18:00

I've maintained a Windows Phone application since the early days of Windows Phone 7 and it has served well for over six years.  If you read my earlier posts you'll see that the application was based upon a targets' website raw html pages.  The basis of the application was to pull the data from out  (scrape) of the resulting web pages and give a better experience for phone users.  I did this since the website was a complete disaster when trying to use it from the desktop and a failure when trying it on any smaller form factor like a phone.

Well, as expected, the website has finally re-engineered the set of pages that were used to scrape data from used in the phone application.  So taking a look at the re-engineered data I see that the website has taken a path to a more modern, reactive framework.  Lucky for me it looks like they have gone the route of delivering the data in one JSON payload.

So for me, I'm going to redo my code to pull out the JSON data and blow it into my observable collection. 

I need to setup a new Windows 10 64-bit box and all the other bits to get back on track like Fiddler/VS2012/Source Repository and the Phone SDK.

I'll detail what things I find when re-doing to read the JSON data rather than the raw HTML pages.

Should be fun.

Tags:

Jun 28 2014

Comparing String Values for Similarities

Category: C# | SQL ServerAdministrator @ 12:41

Many moons ago I embarked on a proof-of-concept project to see if I could use SQL Server to perform a matching process.

It was successful but I still had a lingering suspicion that the underlying algorithm (Levenshtein) used to determine the sameness between human full names was less than optimal.  So what ensued was a period where I would look around the web, once in a while, to see if I missed something. 

I can't tell you how many algorithms I researched, converted to C# and tested.  Don't ask me why, but anyone who starts with Levenshtein will most likely never find this algorithm since so many others like Levenshtein vie for attention.

I finally found what I was looking for purely by accident:

Strike-A-Match: http://www.catalysoft.com/articles/StrikeAMatch.html

and kudos to the paste bin for the C# version: http://pastebin.com/EfcmR3Xx#

So now if you ever need to compare human full names whether they be in any order like:

  • last name, first name compared to first name, last name

Strike-A-Match will do the trick. Take a look at this comparison of a human name: "Jimi Hendrix" to "Hendrix Jimi"

Using Strike-A-Match will compute that these two are exactly equivalent.

Enough said.

I embedded this into A SQLCLR function and it works like a charm.

Levenshtein and all your brethren really don't get the job done when all you really want to do is compare for similarity.

The web has the brightest ideas but try to look in all the dark corners.

Tags: , ,

Sep 30 2012

SSIS Package Execution with C# -- SQL Server

Category: C# | SQL ServerAdministrator @ 08:16

There are times when it seems like IT management decisions are arbitrary and capricious.  This is one of them!

As with most shops, we have SQL Agent running the SSIS production packages but when we migrate to newer servers we are now instructed to no longer use the SQL Agent for execution on the SQL Server. 

OK -- so what's the substitute? 

I'm told to use Bat files which are executed through some other agent tool or use xp_cmdshell.

I feel like I'm going backwards in time and not moving forward with how an operational environment should be architected.  But thankfully some clever individuals have already paved the path to a more beautiful world.

And that world is C# execution of the packages.  You may be asking what the heck I'm talking about but SSIS packages can run anywhere.  I know this sounds unconventional but you can execute packages as long as you have the dependent dlls and the appropriate .NET framework in that environment.  Take a read through this following blog entry and you'll quickly see how to get SSIS packages running from any Windows server and not just a Windows server running SQL server:

Running Packages from C#

The beauty of doing it this way is that you now can have any Windows server running a service which executes the packages and base that execution upon a database configuration. 

Benefits:

  • Configuration files:
    • Running the SSIS package through C# allows you to pass in "User Variables" to the packages.  So just read what you want from a SQL configuration table and pass it to the package.
    • That's right, no longer do you have to maintain config files in folders but move that maintenance to a configuration table in SQL server.
            pkgLocation = Path.Combine(pkgLocation, pkgName.Replace("\"", ""));
            DtsLogging mylogger = new DtsLogging();
            mylogger.Initialize(pkgName);
            Application app = new Application();

            //Package pkg = app.LoadPackage(pkgLocation, eventListener);
            Package pkg = app.LoadPackage(pkgLocation,null);

            pkg.Variables["User::DmatchDataSource"].Value = pkgDmatchDataSource;
            pkg.Variables["User::DmatchUserId"].Value = pkgDmatchUserId;
            pkg.Variables["User::DmatchPassword"].Value = pkgDmatchPassword;
  • Error handling:
    • Make a consistent approach to your applications for error logging.  An errors collection is exposed from the C# package execution so that you can keep all your application logging in one place.
    • No more looking at an application log for one thing and SQL Agent history for another event.

In my case I wanted to capture the rows being sent across the wire (OnPipelineRowsSent) to SQL Server so now that can be captured with the Logging enabled:

            pkg.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;
            pkg.LoggingOptions.EventFilter = new string[] { "OnPipelineRowsSent" };

            DTSEventColumnFilter ecf = new DTSEventColumnFilter();
            ecf.MessageText = true;
            pkg.LoggingOptions.SetColumnFilter("OnPipelineRowsSent", ecf);
            pkg.LoggingMode = DTSLoggingMode.Enabled;
   
            DTSExecResult pkgResults = pkg.Execute(null,null,null,mylogger,null);

Here is the DTSLogging class:

internal class DtsLogging : IDTSLogging

  {
      public bool Enabled
      { get { return true; } 

      }

      ulong rowsprocessed = 0;
      Stopwatch stpWatch = new Stopwatch();
      string pkgName = "";

 

      public void Initialize(string Pkgname)

      {
          pkgName = Pkgname;
          stpWatch.Reset();
          stpWatch.Start();
      }

 

      public void Log(string eventName, string computerName, string operatorName, string sourceName, string sourceGuid, string executionGuid, string messageText, DateTime startTime, DateTime endTime, int dataCode, ref byte[] dataBytes)
      {
          switch (eventName)
          {
              case "OnPipelineRowsSent":
                  {
                      if (messageText == null)
                      {
                          break;
                      }
 
                      if (messageText.StartsWith("Rows were provided to a data flow component as input."))
                      {
                          string rowsText = messageText.Substring(messageText.LastIndexOf(' '));
                          ulong rowsSent = ulong.Parse(rowsText);

                          if (messageText.Contains(" OLE DB Source Output "))
                          {
                              LogRowProcessedInfo(rowsSent);
                          }
                      }
                  }
                  break;
          }
      }


      public bool[] GetFilterStatus(ref string[] eventNames)

      {
          //bool[] boolret = {};
          return new bool[] { };
      }


      void LogRowProcessedInfo(ulong rowsSent)
      {
          rowsprocessed += rowsSent;
          // Include further implementation for logging to db and text file.
          if (stpWatch.Elapsed.Minutes >= Convert.ToInt32( Config.Instance().EventMessageTimeInterval))
          {
              stpWatch.Reset();
              eventLogSimple.WriteEntry("Pkg: " + pkgName + ", PipelineRowsSent: " + rowsprocessed.ToString());
              stpWatch.Start();

          }         
      }

  }

In my case I made a Windows service to run the packages.  This way the C# code looks at a database to schedule when to execute a particular SSIS package.  The dtsx files are kept locally on the application server and the C# code loads them and runs them locally.  This ends up using the resources of the application server and there's no resource impact (my DBA loves this fact) felt on the SQL server.

 

Sometimes from miserable circumstances comes inspiration.

Tags: , , ,