Retrieve/Update SQL Records based on Barcodes

• Download source and binaries - 227 KB

Introduction

Remember my article "How to embed Barcodes in your SSRS report" ? Well let's continue on the idea.

This tool gives you the power to use your printed barcodes to manually intervene in some automated processes if needed. i.e. fetch a specific record from a specific DB choosen based on your barcodes' contents and then update some of the scanned records or other data upon demand.

And yeah, maybe many of my dear audience here will dismiss the whole article as useless for it not meeting what they have been looking for. Quite sure! But there are for sure some people who will love it, for it is ready to go and use WITH the CORRECT configuration and a real life use case scenario presumed, having something ready to play, rather than doing this by themselves from scratch. So please don't downrate this article if it's not what you are looking for. The worth of the work for those who can use it is 5/5! ;-)

Background

Imagine you have printed a barcode on each and every letter/order/invoice that you have sent out. Now some of those return to you as undeliverable because the recipient has moved. Now your staff has to painstakingly open those envelopes and enter the system and mark the letter as undeliverable returned. Or on outbound postage you may have printed all the labels and have the letters ready to be sent, but you want to give them a final update when you hand them over to the postman.

So this tool will allow you to do just that in a blink. Just scan your items through the envelopes window or whereever you barcoded them. The tool will retrieve the corresponding records from your Databases and update them accordingly.

This is assuming a barcode format (you can change that bit) containing a string that indicates which of the known databases (configurable) to use, then query that database for the specific record (ID also from barcode) and show the record as configured in the Grid.

Then tick or untick the records to update and click submit.

So this tool can be reused for all sorts of "crimes" of retrieving and updating certain DB records just by using barcodes. You can have multiple configurations for various purposes and have then copies of the app lying around for each purpose, or go a step further and modify it to select from a configuration.  

Using the code

The app.config of this tool is the KEY to make it work!

You can add one or more SQL Connection Strings. Configure the Title of your Form, the Column name of the Checkbox Column in front of every record, the keys comma separated that come from your scanned barcode prior the record ID to determine whether to query Database/CustomerData from source DB or source DB1, the sql command that fetches the requested record, and the update commands that upon submit button are issued to update this or other records based on this record. In the below Sample a barcode of format Code39Extended with the contents *ORA1234* would make the app use connection string DB1, then select record with @ItemID=1234 and display it on the datagridview. Simple enough?

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <clear/>
    <add name="DB" connectionString="YourConnectionString" providerName="System.Data.SqlClient" />
    <add name="DB1" connectionString="YourOptional2DatabaseConnectionString" providerName="System.Data.SqlClient" />

  </connectionStrings>
  <appSettings>
    <add key ="Title" value ="Barcode Handler"/>
    <add key ="CheckColumnName" value ="Invalidate Address"/> <!-- the first column is a checkbox so you can check or uncheck to have that one updated or not //-->
    <add key ="DB" value ="ORD,ORF"/> <!-- Key name matching the first connection STring//-->
    <add key ="DB1" value="ORA,ORB"/> <!-- Optional Key name matching the second connection STring//-->
    <add key ="RetrieveCommand_ID_Column_No" value ="1"/>
    <!-- @ItemID is the ID that is returned and used from the second portion of the
    Barcode Your Barcode should contain a value from DB or DB1 plus the item ID enclosed by *
    i.e. *ORA1234* and 1234 would be the ItemID or OrderId if you like
    DB and DB1 just signify to query DB x or DB y depending which customer it is, if you splitted the data accross multiple DBs
   
    RetrieveCommand finds a record and puts it in the datagrid
    UpdateCommand could be multiple Updates semi colon separated using @ItemID to select the records to be updated with whatever status or SQL logic you can configure here
   //-->
    <add key="RetrieveCommand"  value="SELECT top 1 'ORA' as DB,'35295' ItemID,'2014-01-01 00:34:23' Orderdate , 'Sent' as Status, 'Peter' as [First name] ,'Pan' as [Last name] ,'Treehouse 1' as [Address],'Neverland' as [City],'Xmas Presents' as [Description] FROM orders where @ItemID=orderID"/>
    <add key ="UpdateCommand" value="UPDATE ORDER_STATUS SET STATUS = 'Returned Mail (Undeliverable)' ,statusdate = getdate() ,updated_by = 'BarcodeHandler' , updated_date = getdate() WHERE OrderID = @ItemID ; UPDATE m SET incorrect_address_flag = 'Y' FROM customer m inner joine orders ......... orderid= @ItemID ; "/>
  </appSettings>
</configuration>

See how I hard coded the RetrieveCommand and UpdateCommand to unusable stuff. Well you wouldn't be able to use what I used for it depends on your specific database schema, but the point is you can configure your SQL commands here that retrieve the individual record and update the individual record using the parameter @ItemID which could refer to your orderID or fulfillmentID or whatever it is that you use. The other fields are just to make some data visible to make the user be able to identify and verify that the record retrieved matches the item just scanned.

RetrieveCommand_ID_Column_No is 0 based index and should point to the column number from your select (retrieve command) that contains the actual ID that matches the second half of your barcode.

Points of Interest

I made the tool retrieve the gridview column names be retrieved dynamically from the select statement, so no worries about configuring that too.

private void SetColumns()

You can of course run this with just one database, no problem. Just delete the second connection string.

Make sure to retain </clear> in the connection strings to avoid unmarked "LocalSqlServer" Connection to be attempted to be opened.

ScannerTextBox Class makes easy to use barcode scanner to scan and automatically issue the record retrieval without having to click any extra buttons. I followed the idea of someone else long back in some forum (unfortunately don't remember who - if it's you, let me know and I'll mention you fairly here. ;-)) to use a timer to measure the entries and the following "\r" coming from the scanner. In my case the cheapest 10$ USB scanner from some online auction site sometime ago. It behaves just like a magic keyboard.

public class ScannerTextBox : TextBox
  {
      public event ChangedEventHandler BarCodeEntered;

      // Invoke the Changed event; called whenever list changes
      protected virtual void OnChanged(EventArgs e)
      {
      }

      public bool BarcodeOnly { get; set; }

      Timer timer;

      private void InitializeComponent()
      {
          this.SuspendLayout();
          this.ResumeLayout(false);
      }

      void timer_Tick(object sender, EventArgs e)
      {
          if (BarcodeOnly == true)
          {
              Text = "";
          }
          timer.Enabled = false;
      }

      protected override void OnKeyPress(KeyPressEventArgs e)
      {
          base.OnKeyPress(e);
          if (BarcodeOnly == true)
          {
              if (timer == null || !timer.Enabled )
                  Text = "";

              if (timer == null)
              {
                  timer = new Timer();
                  timer.Interval = 200;
                  timer.Tick += new EventHandler(timer_Tick);
                  timer.Enabled = false;
              }
              timer.Enabled = true;
          }

          if (e.KeyChar == '\r')
          {
              if (BarcodeOnly == true && timer != null)
              {
                  timer.Enabled = false;
                  if (BarCodeEntered != null)
                  {
                      if (this.Text.Length > 3 && !String.IsNullOrEmpty(this.Text))
                      {
                          BarCodeEnteredEventArgs e1 = new BarCodeEnteredEventArgs(this.Text);
                          BarCodeEntered(this, e1);
                      }
                  }
              }
          }
      }
  }

History

No History yet.