We’re using a program at work called Fishbowl Inventory for managing our sample inventory.  The program is a full blown ISRP (Inventory, Shipping, Receiving, and Packing) solution that does everything from sales order entry to warehouse logistics.  It’s been pretty rock-solid (tho I do regular full backups/restores and database re-indexes).  As of today, we are still using version 4.7 (very old) because the product road map changed after version 5.0 ( I’m not sure who drove their road map, but I feel the went the wrong way on a bunch of changes and every change was followed with huge bug lists).  In a nutshell, Fishbowl is a Java application that uses a Firebird database to store it’s data.  Upon the release of version 4.x, Fishbowl released a new “integration” feature that allowed developers to write applications that could communicate with Fishbowl via XML.  This was a big “improvement”, since previously I had written a few applications directly against the DB that generates labels (another missing feature) with a Dymo thermal printer and ran into lots of problems because Fishbowl does not like DB SCHEMA changes (even very simple benign changes gave me and support many issues).

Fast forward 4 years and here I am, I need to get some data out of Fishbowl but I find the ODBC drivers very unstable and I get errors > 50% of the time when I try to pull data out of Fishbowl.  I also tried their C# SDK and also received a bunch of random errors (probably because it’s for version 2011.7 vs. 4.7).  Since I needed to get some data out, I did a quick google search and found somebody made a PHP wrapper called  fishconnect based on the SDK for version 2010.4.  It says it required Fishbowl Inventory 2010.4+ to work, but after reviewing the code (don’t have PHP server, so I didn’t test anything), I’m convinced that it will probably work for all previous versions back to v4.7.  After a few minutes reading the code, I figured I could make myself a nice C# wrapper to use on our ASP.NET intranet site.

Here is a quick summary of what I used to put this together….

 

1. Have XSD file, now generate me some objects!

I grabbed the “fbimsg.xsd” from fishbowlconnect PHP project, which is also provided with the official Fishbowl Java SDK.

Microsoft has had the “XSD.EXE” for a long time, it will generate your objects based on a XSD file.  The command was moved into the Visual Studio Command Prompt with VS2010.  To use the XSD tool, go to your Microsoft Visual Studio 2010 folder and launch “Visual Studio Command Prompt (2010)”.  Once your at a command prompt, type the following.

xsd <location of XSD>.xsd /s

Presto, you got a nice DAL that is strongly typed!!!

 

2. Communications with Fishbowl Requires a Big Endian Stream

Why MS only supports Little Endian streams out of the box is a mystery to me, maybe a push to use the MS stack?  I don’t know, but regardless you have to solve this problem to talk with Fishbowl.  I did a quick and dirty sample to get it working, but it was a terrible “permanent” solution and required “Allow unsafe code” to be enabled.  I ran to StackOverflow to find somebody smarter with better code and ended up grabbing what I needed from Jon Skeet’s MiscUtil Library.  With this, I now had full support for BigEndian data streams.

 

3. Approving Fishbowl Integrated Applications

Fishbowl Inventory requires you to approve all 3rd party applications in the Fishbowl Server GUI, before the become active.  If you are running Fishbowl as a service you’ll have to stop it and then run it interactively. Once started, go to “Integrated Applications” and you should see Fishbowl Connect C# listed, select and check the green check mark to approve.

Fishbowl Inventroy Integration - Approve

Fishbowl Server Integrated Applications - Approval Screen

 

4. Putting it all together and a working example.

The final product consists of a single class called “FishbowlSession” that implements IDisposable.  This class handles all the communications with fishbowl. In addition to this class we have a DAL that was created from our XSD file and a Utilities library to provide a few helpers.  I wrote the main class by writing NUnit tests, if you want to test against your Fishbowl Server then change values in [Setup] test to reference valid data for your server (e.g. Server IP, Login, Password, Part Numbers).

If you find bugs please let me know, I only tested sending/receiving 4 message types so I’m not sure if all message types will work.

 

Example showing how to get the inventory for a part in Fishbowl.

        private static string GetFishbowlPartInventory(string part)
        {
            string inventoryResults = "Fishbowl Inventory Offline!";

            using (FishbowlSession fishbowlInventory = new FishbowlSession("192.168.168.168"))
            {
                // Connect to fishbowl using a valid login/password
                fishbowlInventory.Connect("app","app");

                // Make sure we are authenticated before we try to send/receive messages
                if (fishbowlInventory.IsAuthenticated)
                {
                    // Build PartTagQueryRqType Request
                    PartTagQueryRqType partTagQueryRqType = new PartTagQueryRqType { LocationGroup = "Product", PartNum = part };

                    // Submit Request and get Response
                    PartTagQueryRsType partTagQueryRsType = fishbowlInventory.IssueRequest<PartTagQueryRsType>(partTagQueryRqType);

                    // Unregister Part, show custom message
                    if (partTagQueryRsType.statusCode != "1000")
                    {
                        inventoryResults = Utilities.StatusCodeMessage(partTagQueryRsType.statusCode);
                    }

                    // Tag object is optional, if there is no tag element you can not get the quantity
                    if (partTagQueryRsType.Tag != null && partTagQueryRsType.Tag.Length > 0)
                    {
                        Tag tag = partTagQueryRsType.Tag[0];

                        inventoryResults = int.Parse(tag.Quantity) > 0
                                               ? tag.Quantity + " Available in Fishbowl Inventory."
                                               : "No Inventory Available in Fishbowl Inventory.";
                    }
                }
            }

            return inventoryResults;
        }

IMPORTANT NOTE: There is an important caveat to this solution… Communication with the server will consume one of your seats that you have available based on your license key. This means, if your key only allows for 5 concurrent users and your app tries to connect when all the seats are full… it will fail! I have put a lot of other “things” in place to prevent this in my setup; short session timeouts, terminal server user caps, etc… but it’s still possible…

Download Fishbowl Inventory C# Wrapper