Page 1 of 1

C# Inventory program using SQL Compact

#1 optix212  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 529
  • Joined: 10-October 09

Posted 18 April 2013 - 09:09 AM

Inventory Program in C#

Hello all, I am here today to teach you how to make a simple inventory program using C# and utilizing the built-in SQL Server Compact 4.0. This tutorial expects that you have at least a little knowledge of the C# language, and you know how a database works (i.e. you know the difference between a table, column, row, and cell). With that being said, you should be able to compile and run this program without any flaws even if you are new to programming. You just might not understand a few concepts used, or may not understand a few of the classes used. In which case, I encourage you to post any questions you may have. I will be updating this tutorial pretty regularly, so be sure to check back.

What you should know:
• What a variable is
• How to make a new WinForms project
• What an array is
• How to drag/drop tools from the toolbox.

What you will learn:
• How to create a SQL Server Compact edition database within the Visual Studio IDE.
• How to create a DataGridView with bound data to it.
• How to add Tables to a database, and columns to that table.
• How to create a search bar for searching through a database.

Contents:
1. Creating a SQL Server Compact Database
2. Creating a search bar for our database

Creating a SQL Server Compact Database

Okay, so first thing is first. We are going to create a database to hold all of our data. This database will contain the Table named Inventory, and that table will contain the Columns for holding all of our data. If you don’t see Data Sources on the left hand side of the IDE, go ahead and click View > Other Windows > Data Sources. Click the “Add new Data Source” icon in the top left, and it will bring up a new window asking you what type of data source you would like to create. Make sure “Database” is clicked, and then click next. This will bring up a screen to choose your database model. Make sure “Dataset” is clicked, and click the next button again. On this screen, it will ask you to choose your database connection. Since we haven’t made a database yet, we will have to click the “New Connection” button. This will bring up a new screen asking you to fill out our new connections information. Leave everything in the form default except for name. Name it “Inventory” and click the “Create” button. This will bring up a new screen to name and create your database. Leave everything on this page default, and click the OK button. It will ask you if you are sure you want to create a database without password protection. Click yes, and it will create your database for you. Click OK on the next page, and it will now show your new database in the selection box. Click the next button, and a message box will pop up asking if you would like to copy the new database file to your project. Click Yes, and it will take you to a new screen asking how you would like to save the connection string. Leave everything as default, and click next. Finally, click “Finish” and it will create a new, empty dataset for you.

Next, we will have to add data to our database. To do this, you will need to bring up the Database Explorer by clicking View > Other Windows > Database Explorer. Once you have brought this up, you will see your new database. Click the dropdown arrow on your database, right click on “Tables”, and click “Create New Table”. Name the Table “Inventory”, and fill out the columns according to the information below:

Name: Part Number
Type: nvarchar

Name: Date Added
Type: datetime

Name: Condition
Type: nvarchar

Name: Location
Type: nvarchar

Name: In Stock
Type: bit

If this seems confusing to you, try and follow the picture below:



Once done, click the “OK” button. Go back to your DataSources panel, and click the “Configure data source with wizard” icon. This will bring up the Data Source Configuration Wizard. All you have to do here is check the “Tables” checkbox, and click “Finish”. This will load the Table into your Inventory dataset. Then, all you have to do to add the database to your form is drag the Inventory table from your Data Sources menu, and onto your form. The table in which you are now looking at is called the DataGridView. On the DataGrid, click the menu arrow on the top right of it, and click the “Dock in parent container” button. This will fill your form with the DataGrid. Another notable feature that was added to your form when you dragged the table to it is the Context Menu Strip that is right above the DataGrid. We will talk more about this later, when we make our search button. Set the DataGrid’s Multiselect property to False, and its ReadOnly property to True. The reason we set the ReadOnly property to true is because people could accidentally go in and change the value of one of the cells without knowing. This will prevent that. But then, how will we let the user input data into the DataGrid? Click the “Events” icon in the properties window (shaped like a lightning bolt), and double click the “CellDoubleClick” event to bring up “Form1.cs”. It should have already added the CellDoubleClick event for you. Finally, we get to code! Change the double click event so that it looks like this:

private void inventoryDataGridView_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            inventoryDataGridView.ReadOnly = false;   
        }



This means that once a cell has been double clicked, it will change the DataGrids ReadOnly property to False.

Congratulations! You have just created your first database with its very own DataGridView on a Windows Forms Application. Wasn’t so hard, was it? But now it’s time for the fun part… If a real company were to use this, the datagrid would get filled up pretty fast, and it may take a while to find the part you are looking for. Looks like it’s time to implement a search bar!

Creating a Search Bar for your database

From the Context Menu Strip, click the dropdown menu at the far right of the icons, and create a new button. Click on the button to bring up the properties window for that button and change its DisplayStyle property to “Text”, change its Text property to “Go”, and change its Alignment property to “Right”. Then create a Textbox within the Context Menu Strip and change its Text property to “Search…”, and change its Alignment property to “Right” so that it appears next to the Go button. Next double click the Textbox to bring up a click event in the “Form1.cs” document, and change it to read as follows:

private void tsSearchBar_Click(object sender, EventArgs e)
        {
            tsSearchBar.Text = String.Empty;
        }



Next, double click the Go button to bring the up the click event for it. Change the code so that it reads as follows:

private void tsGoButton_Click(object sender, EventArgs e)
        {
            string searchValue = tsSearchBar.Text;

            inventoryDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            try
            {
                foreach (DataGridViewRow row in inventoryDataGridView.Rows)
                {
                    if (row.Cells[0].Value.ToString().Equals(searchValue))
                    {
                        row.Selected = true;
                        dataGridView1.FirstDisplayedScrollingRowIndex = inventoryDataGridView.SelectedRows[0].Index;
                        break;
                    }
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }



What this code does is creates a local variable of type string called searchValue that takes the Text from the textbox we created. Then it changes the SelectionMode of our DataGrid to FullRowSelect. This means that instead of selecting one single cell, it will select a whole row for you. The next thing that it does is creates a foreach loop that checks each DataGridViewRow that we named “row” in inventoryDataGridView.Rows. The next thing that it does is checks if the selected row is equal to our searchValue. If so, then it selects the row for you. After it’s selected, it scrolls down so that the selection is within the screen. Once these conditions have been met, it breaks the loop. If there is any error thrown while the loop is attempting to find our part, then the catch(Exception exc) method will show us the error message within a MessageBox.

This should about wrap up the tutorial. If you have any questions, please feel free to ask on this thread, or send me a PM. Thank you, and keep programming!

Is This A Good Question/Topic? 2
  • +

Page 1 of 1