Pharmacological office management using products barcode

• Download source - 260 KB

Introduction

This C# code show how to use a barcode reader to manage pharmaceutical sales. It could be adapted to any store or service based on barcode reading. To achieve our goal, I used a SQL Server database to store all the entries. Every time we need to get a product out of stock our script searches in the database to see if it exists and how many product have the same barcode in the table. Products are sorted in a ListView, and we have to select the appropriate product. This is strongly recommended in pharmaceutical sales to avoid mistakes that may be considered dangerous for the patient or buyer. I have kept in the attached source code project only some interesting Windows Forms, such as Form to register a product, Bloc Notes, Inventory, prescriptions form, etc. We need some functions to validate data. For this I,

1. Add a class named validation.cs

It contains all classes to control if TexBox(s) entries are valid. I added an EventControl everywhere I wanted use validation functions.

Must Be Numeric

Some entries must be numeric, same if we have to tape the barcode manually.

public void Number_Validating(object sender, KeyPressEventArgs e)
{
    char ch = e.KeyChar;
    TextBox tb = sender as TextBox;
    if (!Char.IsDigit(ch))
    {
        MessageBox.Show(tb.Tag + " La valeur doit être numérique");
        e.Handled= true;
    }
}

Should Not Be Empty

public void empty_Validating(object sender, EventArgs e)
{
    TextBox tb = sender as TextBox;
    if (tb.Text == "" )
    {
        MessageBox.Show(tb.Tag + " Non valide ");
        tb.Focus();
        tb.BackColor = System.Drawing.Color.Beige;
    }
}

Must Be Decimal

public void decimal_Validating(object sender, KeyPressEventArgs e)
{
    char ch = e.KeyChar;
    TextBox tb = sender as TextBox;
    if (ch == 46 && tb.Text.IndexOf('.') != -1)
    {
        e.Handled =true;
        return;
    }
    if (!Char.IsDigit(ch) && ch != 46 && ch != 8)
    {
        MessageBox.Show(tb.Tag + " La valeur doit être décimal");
        e.Handled = true;
    }
}

CR TEXBOX

public void ClearTextBoxes(Control control, String txtbx)
{
    // Fonction pour effacer tout les textboxes
    foreach (Control c in control.Controls)
    {
        if (c is TextBox)
        {
            if (c.Name != txtbx)
            {
    }

Clear TextBox

public void ClearTextBoxes(Control control, String txtbx)

Add a SQL database

manipBaseDonnees.cs has code to connect to a database, test if a product exists, if so get all stored data for this product. This class makes it easy to get all the information for a product every time we sell or buy a product. It prevents all mistakes if two products exist with the same barcode. My database is named “product.mdf” and contain tables such as “medicament.mdf” for meds, “vendeur.mdf” for professionals, “notes.mdf” for bloc notes etc. For recall, to connect to a database we use a connection string as below:

namespace officinelCommand cmd = new SqlCommand();
        SqlDataReader dr;
    }

Then:

private void FormMenu_Load(object sender, EventArgs e)
{
    cmd.Connection = cn; // sqlconn
}

This function gets stored data about a product to a list.

public List<string> getFromTable(Double codebar)
{
    List<string> donnees = new List<string>();
    cmd.Connection = cn; // sqlconn
    cn.Open();
    var selectString = "SELECT * FROM medicaments WHERE code_bare = @codebar";
    var myCommand = new SqlCommand(selectString, cn);
    myCommand.Parameters.AddWithValue("@codebar", codebar);
    rd = myCommand.ExecuteReader();
    if (rd.HasRows)
    while (rd.Read())
    {
        t0 = rd[0].ToString(); // valeur id
        t1 = rd[1].ToString(); // valeur nom
        ….
    }
    rd.Close();
    cn.Close();
    donnees.Add(t0);donnees.Add(t1);donnees.Add(t2);donnees.Add(t3);donnees.Add(t4);donnees.Ad
    d(t5);donnees.Add(t6);donnees.Add(t7);
    donnees.Add(t8); donnees.Add(t9); donnees.Add(t10); donnees.Add(t11); donnees.Add(t12);
    return donnees;
}

This function tests if a product exists:

public Boolean checkIfExist(Double codebar) {
    Boolean bol = false;
    cmd.Connection = cn; // sqlconn
    cn.Open();
    var selectString = "SELECT code_bare FROM medicaments WHERE code_bare = @codebar ";
    var myCommand = new SqlCommand(selectString, cn);
    myCommand.Parameters.AddWithValue("@codebar", codebar);
    object result =myCommand.ExecuteScalar();
    if (result != DBNull.Value && result != null)
    {
        bol = true;
    }
    else { bol = false; }
    cn.Close();
    return bol;
}

public List<int> countInTable(Double codebar)
{
    cmd.Connection = cn; // sqlconn
    cn.Open();
    List<int> id = new List<int>();
    String countString = "SELECT * FROM medicaments WHERE code_bare = @codebar
    ";
    var myCommand = new SqlCommand(countString, cn);
    myCommand.Parameters.AddWithValue("@codebar", codebar);
    using (rd = myCommand.ExecuteReader())
    {
        // loop over all rows returned by SqlDataReader
        while (rd.Read())
        {
            // grab the column no. 0 (corresponds to "Role" from your
            // SQL select statement) as a string, and store it into list of roles
            t0 = rd[0].ToString();
            id.Add(int.Parse(t0));
        }
    }
    rd.Close();
    cn.Close();
    return id;
}

To read a barcode we use a timer. If the textbox of the barcode reader changes the new value is transferred to another textbox (or listbox, listview, etc.) to be gathered. Once reading is finished the function public Boolean checkIfExist(Double codebar) tests if the product exists, if so another function public List<string> getFromTable(Double codebar) send us all the stored data to our prescription interface (in my project, I get name, price). A professional user can get all other data as description, posology, manufacturing date, etc.

private void textBox1_TextChanged(object sender, EventArgs e) {
    try
    {
        if (textBox1.Text.Trim().Length == 1)
        {
            tmrDelay.Enabled = true;
            tmrDelay.Start();
            tmrDelay.Tick += new EventHandler(tmrDelay_Tick);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

void tmrDelay_Tick(object sender, EventArgs e)
{
    try
    {
        tmrDelay.Stop();
        string strCurrentString = textBox1.Text.Trim().ToString();
        if (strCurrentString != "")
        {
            textBox2.Text = strCurrentString;
            textBox1.Text = "";
        }
        textBox1.Focus();
        if (manipBaseDonnees.checkIfExist(double.Parse(textBox2.Text)) == true)
        {
            int nrbOfData = 
              manipBaseDonnees.countInTable(double.Parse(textBox2.Text))[0];
            this.listView2.Items.Clear();
            laodProduct(double.Parse(textBox2.Text));
        }
        else { MessageBox.Show("Ce produit est nouveau"); }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}