6
Posted August 15, 2012 by George in C#
 
 

How to Make a Simple Inventory System in C#

Inventory-Management-Item-Control
Inventory-Management-Item-Control

One application that most companies look for is an Inventory System. This allows them to track their stocks and avoid looking for items manually from the stock room. Through this, and an equally efficient management, businesses and companies will not waste time and allow more time for sales instead, as well as know which products need to be replenished. The Inventory System I am going to teach you today have a cart function for every customer and allows the user to add, or remove the items from the cart. It also has a function to add, update, or delete the products in the inventory. As with any enterprise, the application also needs to have an account system.

So let us first create a database using the MS Access tool. Name the database inventory and create two tables named admin and products. The admin table have two columns namely: ID and Password. The products table have 5 columns: product_id, product_name, product_srp, product_cost, and product_stock. Now create a new Windows Forms application on Visual C# and connect the database. Remember that you have to modify the data source connection to Microsoft Access Database File (OLE DB) so that the application will be able to read the Access Database.

Form1 will be our log-in form so drag in two labels, one textbox, one maskedTextBox and name them USERNAME and PASSWORD respectively. Now drag in two buttons and name them LOG-IN and Exit. Double click Form1 and add these codes in their own specific position.


//this is placed below using System.Windows.Forms; so that we can use the //OleDb functions.

using System.Data.OleDb;

//These are placed inside this code’s brackets public partial class Form3 : Form and //are what you’ll be using to connect to the database

private OleDbConnection mycon;

private OleDbDataReader dr=null;

private OleDbCommand cmd=null;

//Lastly, you have to define the connection the application will use so put define //mycon inside public Form1()

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb");

So starting off with the easy one, go back to the Form1 designer and double click on the Exit button and put this code in:


//this line of code closes the whole application

this.Close();

Next double click on the LOG-IN button and paste these codes in


//This code snippet connects to the database and retrieves the //information that we need, namely the username ID and Password needed //to access the system.

     mycon.Open();

            string cmdstr = "SELECT Password FROM admin WHERE ID ='"+textBox1.Text+"' ";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] login = new string[3];

            while (dr.Read())

            {

                login[1] = (dr["Password"].ToString());

            }

            dr.Close();

            mycon.Close();

//This code presents a confirmation message if the password typed //inside the maskedTextBox matches with the one in the database and an //error message if not

            if (maskedTextBox1.Text == login[1])

            {

                MessageBox.Show("LOG-IN SUCCESFUL!", "Confirmation Message");

                Form control = new Form2();

                control.Show();

                this.Hide();

            }

            else

            {

                MessageBox.Show("LOG-IN FAILED!", "Confirmation Message");

            }

So as you can see, we will be selecting the password that is in line with the ID typed into textBox1. This removes the need to retrieve all the Ids and Passwords then selecting the matching strings from an array which is a lot more complicated than needed.

Now create a new windows form and that would be the control the user will be facing. Drag in three buttons and name them INVENTORY MANAGEMENT, TRANSACTION, and LOG OUT. This form will not have any database connection so we don’t need to define any OleDb properties here. Double click on the respective buttons and add in the codes.


//this would redirect us to the inventory management form where //we can add, edit, and delete items. But take note that the name //inventory_management here is just a variable name and not the direct ID //of the form we’ll be calling.

private void button1_Click(object sender, EventArgs e)

        {

            Form inventory_management=new Form4();

            inventory_management.Show();

            this.Hide();

        }

       // this would redirect us to the transaction form.

        private void button2_Click(object sender, EventArgs e)

        {

            Form transaction = new Form3();

            transaction.Show();

            this.Hide();

        }

       //this would redirect us back to the log-in form

        private void button3_Click(object sender, EventArgs e)

        {

            Form login = new Form1();

            login.Show();

            this.Hide();

        }

So you might have a few errors here and there since we haven’t defined Form3 and Form4 so it would be wise to comment those first and just uncomment them once the Forms are ready. So let’s go ahead and configure Form3.

Create a new Windows Form and drag in:

-two ListView (which will be the INVENTORY and CART), 5 buttons (ADD TO CART, REMOVE FROM CART, TOTAL, BUY, and EXIT),

-two textbox(where we will show the total price, and the number of items to be added on the cart), and lastly

-one Label (CART, put this close to the cart listView.)

As you can see, there are quite a lot of objects in the form. Make the layout easy to understand so that it will be as user-friendly as possible. If you want to, you can group them together depending on their functions.

Start by pasting these same codes in their respective places


//this is placed below using System.Windows.Forms; so that we can use the //OleDb functions.

using System.Data.OleDb;

//These are placed inside this code’s brackets public partial class Form3 : Form and //are what you’ll be using to connect to the database

private OleDbConnection mycon;

private OleDbDataReader dr=null;

private OleDbCommand cmd=null;

//Lastly, you have to define the connection the application will use so put define //mycon inside public Form3()

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb");

Now that you are making the transaction Form, you should have a limiter that will denote how many items are inside the cart. You need to make this a universal variable so that any object can use it as needed.


//add this just below private OleDbCommand cmd=null;

int cartitems = -1;

So you might be wondering, why is it denoted to -1? This is a limiter that we will use to limit the iterations needed to retrieve the items on the cart. Remember that the listView item indexes are denoted in an array, so whatever item you add into the listView, it will always start with the index 0. That is why we cannot start the limit at 0, since there is already an item inside that slot. If we start the limit from 0 we might receive an “Index Invalid” error since the limit and iterations will exceed the slots that have data inside them. So by starting the limiter at -1, we will be able to go through all the slots having the information needed and stop just at the right slot.

Now go back to the designer and double click on Form3 so that we’ll input codes into the Form Load event.


mycon.Open();

loaddbase();

mycon.Close();

You’ll get an error here since we haven’t defined loaddbase() yet. So let’s go ahead and define this function.


private void loaddbase()

        {

            listView1.Items.Clear();

            string cmdstr = "SELECT * FROM products";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] product_info = new string[7];

            while (dr.Read())

            {

                product_info[1] = (dr["product_id"].ToString());

                product_info[2] = (dr["product_name"].ToString());

                product_info[3] = (dr["product_srp"].ToString());

                product_info[4] = (dr["product_stock"].ToString());

                this.listView1.Items.Add(new ListViewItem(new string[] { product_info[1], product_info[2], product_info[3], product_info[4] }));

            }

            dr.Close();

        }

This function lets us SELECT all the information needed and put them into an array, string[] product_info = new string[7];, so that we will be able to add them into the listView (this.listView1.Items.Add(new ListViewItem(new string[] { product_info[1], product_info[2], product_info[3], product_info[4] }));

) and show the existing inventory. Take note that we didn’t retrieve the product_cost here because the transaction function will not use the cost of the items, unless of course you are going to add a cashiering system.

Why didn’t we just define the codes on the loaddbase() function on the Form Load event and have to create a separate function? Take note that the application will need to show the changes made on the database so that it will not have any inconsistencies. So we need to have a way to constantly reload the database and reflect the data on the inventory listView without having to recode everything from scratch. Defining it in a function will allow us to do this efficiently. But we didn’t include the mycon.Open() and mycon.Close() methods since there will already be an open connection at other functions and have to define this before and after the loaddbase() instance.

Now let us define the ADD TO CART button. Make sure that you have a textbobx next this button so the user will know where they should put the number of items to buy before clicking ADD to CART. Double Click on the ADD TO CART button and paste this code:


mycon.Open();

            string[] product_info = new string[7];

            string[] select=new string[2];

            int[] num=new int[5];

            foreach (ListViewItem item in listView1.SelectedItems)

            {

//SELECT THE SPECIFIC PRODUCT FROM DATABASE

                select[1]=item.Text;

                string cmdstr = "SELECT * FROM products WHERE product_id = '"+select[1]+"'";

                cmd = new OleDbCommand(cmdstr, mycon);

                dr = cmd.ExecuteReader();

                while (dr.Read())

                    {

                        product_info[1] = (dr["product_id"].ToString());

                        product_info[2] = (dr["product_name"].ToString());

                        product_info[3] = (dr["product_srp"].ToString());

                        product_info[4] = (dr["product_stock"].ToString());

                        this.listView2.Items.Add(new ListViewItem(new string[] { product_info[1], product_info[3], textBox2.Text })); //THIS ADDS THE PRODUCT INFO TO THE CART LISTVIEW

                        cartitems++;

                   //THIS UPDATES THE SPECIFIC PRODUCTS STOCKS

     num[1] = int.Parse(product_info[4]);

                        num[2] = int.Parse(textBox2.Text);

                        string cmdstr2 = "UPDATE products SET product_stock = '" + (num[1] - num[2]) + "' WHERE product_id = '" + product_info[1] + "'";

                        cmd = new OleDbCommand(cmdstr2, mycon);

                        cmd.ExecuteNonQuery();

                    }

            }

            loaddbase();

            mycon.Close();

            textBox2.Text = "0";

So as you can see I have grouped this into the specific functions they perform since clicking the ADD TO CART button will perform several functions simultaneously. It needs to select the specific product the user has clicked from the inventory listView from the database and load the information it has. This would then be loaded into the cart listView which would show the item code, the price per piece, and the number of items the customer wishes to be buy. Take note of the cartitems++; which adds the current number of items inside the cart and iterates every new kind of product is added to the cart. Now once the items are added, we have to show the remaining stocks or that item so we have to get the subtract the number of items to be bought from the current number of stocks available and update the database through this string, string cmdstr2 = “UPDATE products SET product_stock = ‘” + (num[1] – num[2]) + “‘ WHERE product_id = ‘” + product_info[1] + “‘”;. Take note also that we didn’t put the cmd command inside a data reader and just executed the command as is, cmd.ExecuteNonQuery();. This is because the UPDATE function is not a query that would retrieve data from the database. It is a modifying command that would let us update one or more columns in a specific row, WHERE product_id = ‘” + product_info[1] + “‘”.

After these functions are executed, reload the database to show the changes made, loaddbase();. And revert the textbox where the user would input the number of items to be bought to 0.

Now double click on the REMOVE FROM CART button and paste in the following code:


mycon.Open();

            string[] select=new string[3];

            int[] rem = new int[5];

            string[] product_info = new string[7];

            foreach (ListViewItem item in listView2.SelectedItems)

            {

//SELECT THE ITEM TO BE REMOVED FROM CART

                select[1] = item.Text;

                listView2.FullRowSelect = true;

                string cmdstr = "SELECT * FROM products WHERE product_id = '" + select[1] + "'";

                cmd = new OleDbCommand(cmdstr, mycon);

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    product_info[1] = (dr["product_id"].ToString());

                    product_info[2] = (dr["product_stock"].ToString());

                    product_info[3] = (dr["product_srp"].ToString());

item.Remove();//REMOVES THE ITEM FROM THE CART LISTVIEW

                    cartitems--;

                      //THIS RETURNS THE STOCKS ON CART TO THE INVENTORY

                    rem[1] = int.Parse(product_info[2]);

                    rem[2] = int.Parse(listView2.SelectedItems[0].SubItems[2].Text);

                    string cmdstr2 = "UPDATE products SET product_stock = '" + (rem[1] + rem[2]) + "' WHERE product_id = '" + product_info[1] + "'";

                    cmd = new OleDbCommand(cmdstr2, mycon);

                    cmd.ExecuteNonQuery();

                }

                loaddbase();

                mycon.Close();

            }

On the REMOVE FROM CART button, we will only be doing the opposite. We will remove the item from the cart listVIew, item.Remove();,and deduct the number of items on cart by the number of items removed, cartitems–;. Lastly, it will UPDATE the database by adding the items removed from cart to the database. Now reload the database and show the updated stocks.

Next, we have to show the total price to be paid t the cashier. So double click on the TOTAL button and paste in this code:


double total = 0;

            int i = 0;

            do

            {

                double product = Convert.ToDouble(listView2.Items[i].SubItems[1].Text) * Convert.ToDouble(listView2.Items[i].SubItems[2].Text);

                total = total + product;

                i++;

            } while (i <= cartitems);

            textBox1.Text = ""+total;

This is where we need to use the cartitems variable as a limiter. So we have to retrieve the information on the cart listView by using these codes, listView2.Items[i].SubItems[1].Text & listView2.Items[i].SubItems[2].Text. This allows us to get the price per piece and the number of pieces respectively. So we convert that into a double type variable since large quantities will produce larger products, Convert.ToDouble. And add that to the total cost per item kind and after several iterations would give us the total cost of the items on cart. So show that on the textbox for the total value and you’re good to go.

As for finalization of the transactions, we have the BUY button. Double click on that and add this fairly easy to understand code snippet


MessageBox.Show("Thank You for Buying", "");

            listView2.Items.Clear();

            textBox1.Text = "0";

            cartitems = -1;

Then click on the EXIT button and add this one so we can go ahead and get back to the control form.


  Form control = new Form2();

            control.Show();

            this.Hide();

Now move on to the next, Form4 which is our INVENTORY MANAGEMENT form. Uncomment the calling function of Form4 on Form2.

Create a new Windows Forms and add the following objects:

-one listView

-5 buttons(ADD, UPDATE, DELETE EXIT, REFRESH)

Start by pasting these same codes in their respective places


//this is placed below using System.Windows.Forms; so that we can use the //OleDb functions.

using System.Data.OleDb;

//These are placed inside this code’s brackets public partial class Form3 : Form and //are what you’ll be using to connect to the database

private OleDbConnection mycon;

private OleDbDataReader dr=null;

private OleDbCommand cmd=null;

//Lastly, you have to define the connection the application will use so put define //mycon inside public Form3()

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb");

And add the loaddbase() function as well so that we could load the database as the Form Loads.


private void loaddbase()

        {

            listView1.Items.Clear();

            string cmdstr = "SELECT * FROM products";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

            string[] product_info = new string[7];

            while (dr.Read())

            {

                product_info[1] = (dr["product_id"].ToString());

                product_info[2] = (dr["product_name"].ToString());

                product_info[3] = (dr["product_srp"].ToString());

                product_info[4] = (dr["product_cost"].ToString());

                 product_info[5] = (dr["product_stock"].ToString());

                this.listView1.Items.Add(new ListViewItem(new string[] { product_info[1], product_info[2], product_info[3], product_info[4], product_info[5] }));

            }

            dr.Close();

        }

And also paste these on the Form4 Load event


mycon.Open();

            loaddbase();

            mycon.Close();

Now that would allow us to see all the information needed inside the database as the form loads. Unfortunately though, we couldn’t use the same loaddbase() function on the ADD and UPDATE function since we will be calling a dynamic form to type in new information and retrieve information. So we have a refresh button instead where you can put the same code as in the Form Load event.

To ADD a new entry to the database we will call a new form where we can type in the information needed. This would also be used to allow us to edit the information currently inside the database to update an existing entry.

So first add these codes on the ADD button:


Form infobox = new Form5("add","","","","","");

            infobox.Show();

Then create a new form having Five labels(CODE, NAME, SRP, COST, STOCK) and textboxes as well as two buttons(OK, EXIT).

As you can see on the ADD button we have a few strings inside the Form5 method. We have to define this on Form5 itself so that we can use it. So define these variables on Form5:


string functioncall, codedisplay, namedisplay, srpdisplay, costdisplay, stockdisplay;

        string product;//we will go over this on the UPDATE button on Form4

//then define these string function, string code, string name, string srp, string cost, //string stock inside public Form5()

public Form5(string function, string code, string name, string srp, string cost, string stock)

This allows to accept the information passed to Form5 through these codes:


     codedisplay = code;

            namedisplay = name;

            srpdisplay = srp;

            costdisplay = cost;

            stockdisplay = stock;

            functioncall = function;

As this is the form where we will be finalizing the add and update functions, we also have to define the OleDb properties here.


//this is placed below using System.Windows.Forms; so that we can use the //OleDb functions.

using System.Data.OleDb;

//These are placed inside this code’s brackets public partial class Form3 : Form and //are what you’ll be using to connect to the database

private OleDbConnection mycon;

private OleDbDataReader dr=null;

private OleDbCommand cmd=null;

//Lastly, you have to define the connection the application will use so put define //mycon inside public Form3()

mycon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb");

The form will recognize what SQL statement it will call using the functioncall variable. Define these functions inside the OK button.


if (functioncall == "add")

            {

                add();

                this.Hide();

            }

            if (functioncall == "update")

            {

                update();

                this.Hide();

            }

So what we will be doing here is fairly simple. Before the form would be able to add a new entry into the database, it first has to scan if there is an existing entry with the same primary key, namely product_id since no two entries can have the same primary key. It would then stop the form from adding terminate the function. On the other hand it would add a new entry using the INSERT statement.

Define the add() function:


 private void add()

        {

//SELECTS THE ID INPUT IN THE TEXTBOX AND CHECKS IF THERE IS AN EXISTING ENTRY

            mycon.Open();

            string cmdstr="SELECT * FROM products WHERE product_id ='"+textBox1.Text+"'";

            cmd = new OleDbCommand(cmdstr, mycon);

            dr = cmd.ExecuteReader();

if (dr.HasRows == true)//IF THERE IS A NEW ENTRY

            {

                MessageBox.Show("ENTRY EXISTS!", "ERROR");

                dr.Close();

                mycon.Close();

            }

            else if (dr.HasRows == false)//IF THERE IS NO EXISTING ENTRY

            {

                string cmdstr2 = "INSERT INTO products([product_id],[product_name],[product_srp],[product_cost],[product_stock]) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + Convert.ToDouble(textBox3.Text) + "','" + Convert.ToDouble(textBox4.Text) + "','" + Convert.ToInt32(textBox5.Text) + "')";

                cmd = new OleDbCommand(cmdstr2, mycon);

                cmd.ExecuteNonQuery();

                dr.Close();

                mycon.Close();

                MessageBox.Show("PRODUCT ADDED!","CONFIRMATION");

            }

        }

Then we will prepare the update() function for the UPDATE button on Form4.


private void update()

        {

            mycon.Open();

            string cmdstr = "UPDATE products SET product_id = '" + textBox1.Text + "', product_name='" + textBox2.Text + "', product_srp='" + Convert.ToDouble(textBox3.Text) + "',product_cost='" + Convert.ToDouble(textBox4.Text) + "', product_stock='" + Convert.ToDouble(textBox5.Text) + "' WHERE product_id = '" + product + "'";

            cmd = new OleDbCommand(cmdstr, mycon);

            cmd.ExecuteNonQuery();

            mycon.Close();

        }

Next allow the user to return to Form4 for further database modification. EXIT button:


this.Hide();

Next on the UPDATE button, we also have to check if there is an existing entry and retrieve the information on the database so that we pass it on to Form5 and edit the information.


mycon.Open();

            string[] product_info = new string[7];

            string[] select = new string[2];

            foreach (ListViewItem item in listView1.SelectedItems)

            {

                select[1] = item.Text;

                string cmdstr = "SELECT * FROM products WHERE product_id = '" + select[1] + "'";

                cmd = new OleDbCommand(cmdstr, mycon);

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    product_info[1] = (dr["product_id"].ToString());

                    product_info[2] = (dr["product_name"].ToString());

                    product_info[3] = (dr["product_srp"].ToString());

                    product_info[4] = (dr["product_cost"].ToString());

                    product_info[5] = (dr["product_stock"].ToString());

                }

            }

            dr.Close();

            mycon.Close();

            Form infobox = new Form5("update",product_info[1],product_info[2],product_info[3],product_info[4],product_info[5]);

            infobox.Show();

            button2.Enabled = false;

            button3.Enabled = false;

Now take note that we have a product variable on the update() function on Form5. This is the string we will use to locate the entry that we have to edit. It would also allow us to edit even the product_id. So define the contents of the variable on the Form Load event


product = textBox1.Text;

So if the product exists and the information are then passed onto Form5, we can view the information and make the changes as needed. As the OK button is clicked, the functioncall variable will tell the application to call the update() function and UPDATE the database.

Lastly, we can then define the DELETE button on Form4. Paste this simple code snippet on the DELETE button.


            mycon.Open();

            string cmdstr = "DELETE * FROM products WHERE product_id = '" + listView1.SelectedItems[0].Text + "'";

            cmd = new OleDbCommand(cmdstr, mycon);

            cmd.ExecuteNonQuery();

            mycon.Close();

            MessageBox.Show("PRODUCT DELETED!", "CONFIRMATION");

The method listView1.SelectedItems[0].Text allows us to use the id of the selected entry on the listView so that the application will know which entry to DELETE.

Now define the EXIT button and you can switch back to the CONTROL form.


Form control = new Form2();

            control.Show();

            this.Hide();

This is a basic inventory system that allows the user to manage the inventory. Add a few restrictions here and there and you will be able to use this application for any business or enterprise.


George