C# makes a simple supermarket counter checkout system

Introduction

This program is used in the design of Windows programming courses, and the knowledge involved mainly includes: C# form application development, database connection and conventional addition, deletion, modification and query, the use of SqlConnection and SqlDataAdapter, the use of dataGridView, control linkage and file read and write operations .

Program main interface

Background inventory management interface

Historical consumption records

Export of consumption records

development environment

Windows10, Visual Studio2019, Microsoft SQL Server

Project file structure

Form1: Program main window

Form2: background inventory management interface

Form3: Inquiry of historical consumption records

Database1.mdf: manually added database file

Properties: project configuration, mainly used to configure the database connection string

development notes

Create a new project and select Windows Forms Application (.Net Framework) C#

Each form contains a design interface and a code file. The design interface is used for form layout. After the layout is completed, double-click the control to enter the click response function of the control, and write the event response code in the function

Add a database file: Right-click on the solution, select Add -> New Item, and select "Service-Based Database"

Add 3 tables to the database: Cart, History, Product, which respectively store the products in the shopping cart, historical consumption records, and basic information of the products. The definitions of the tables are as follows: (The creation of tables can be operated through the graphical interface)

CREATE TABLE [dbo].[Cart] (
    [Name]  NVARCHAR (50) NOT NULL,
    [Price] MONEY         NULL,
    [Num]   INT           NULL,
    [Total] MONEY         NULL,
    PRIMARY KEY CLUSTERED ([Name] ASC)
);
CREATE TABLE [dbo].[History] (
    [Id]      INT            IDENTITY (1, 1) NOT NULL,
    [Content] NVARCHAR (MAX) NULL,
    [Total]   MONEY          NULL,
    [Time]    DATETIME       NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Product] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (50)  NULL,
    [Price]       MONEY          NOT NULL,
    [Stock]       INT            NOT NULL,
    [Description] NVARCHAR (MAX) NULL,
    [Class]       NVARCHAR (20)  NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CHECK ([Price]>=(0)),
    CHECK ([Stock]>=(0))
);

The following is an example of loading the product name of the product information table to the list control, which is helpful to understand the basic usage of SqlConnection, SqlDataAdapter and DataTable

//Load all item names into listbox1
SqlConnection con = new SqlConnection(Properties.Settings.Default.Database1ConnectionString);
SqlDataAdapter da1 = new SqlDataAdapter("select Name from Product", con);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
listBox1.DataSource = dt1;
listBox1.DisplayMember = "Name";
listBox1.ValueMember = "Name";

core code

Main window loading function:

a. Load all categories of products into the drop-down box

b. Load all product names into the list list

c. Load the shopping cart content into the datagridview

private void Form1_Load(object sender, EventArgs e)
{
    //Load all categories of products into the drop-down box
    con = new SqlConnection(Properties.Settings.Default.Database1ConnectionString);
    SqlDataAdapter da = new SqlDataAdapter("select distinct Class from Product", con);
    DataTable dt = new DataTable();
    da.Fill(dt);
    DataRow dr = dt.NewRow();
    dr[0] = "all";
    dt.Rows.InsertAt(dr, 0);
    comboBox1.DataSource = dt;
    comboBox1.DisplayMember = "Class";
    comboBox1.ValueMember = "Class";

    //Load all item names into listbox1
    SqlDataAdapter da1 = new SqlDataAdapter("select Name from Product", con);
    DataTable dt1 = new DataTable();
    da1.Fill(dt1);
    listBox1.DataSource = dt1;
    listBox1.DisplayMember = "Name";
    listBox1.ValueMember = "Name";

    //Load cart content into datagridview1
    SqlDataAdapter da2 = new SqlDataAdapter("select * from Cart",con);
    DataTable dt2 = new DataTable();
    da2.Fill(dt2);
    dataGridView1.DataSource = dt2;
}

The drop-down box is linked with the list list. When a product category in the drop-down box is selected, the list list will automatically display the products belonging to this category

//Load the corresponding product to listbox1 according to the category selected in the drop-down box
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlDataAdapter da = new SqlDataAdapter();
    if (comboBox1.SelectedValue.ToString() == "all")//If the selected category is "All", load all products
    {
        da.SelectCommand = new SqlCommand("select Name from Product", con);
    }
    else//Otherwise, load the products of the specified category
    {
        da.SelectCommand = new SqlCommand("select Name from Product where Class=N'" + comboBox1.SelectedValue.ToString() + "'", con);
    }
    DataTable dt = new DataTable();
    da.Fill(dt);
    //bind data source
    listBox1.DataSource = dt;
    listBox1.DisplayMember = "Name";
    listBox1.ValueMember = "Name";
}

Click the Add button to add the selected items to the shopping cart on the right. The addition process requires the following things to be done:

a. First check whether the product to be added has enough stock

b. If the inventory is sufficient, it can be added to the shopping cart. There are two cases. The shopping cart already has the product (find the product and only increase the quantity) and has not added the product (find the relevant attributes from the product Product, add a record and insert it into the shopping cart) Cart)

c. Change the inventory of the product

d. Calculate the total amount and display it in the consumption column

private void button1_Click(object sender, EventArgs e)
{
    //If the added quantity is 0, do nothing
    if (numericUpDown1.Value == 0)
    {
        toolStripStatusLabel1.Text = "Finish";
        return;
    }

    //First check to see if there is enough stock for the item to be added
    SqlCommand cmd = new SqlCommand("select Stock from Product where Name=N'" + listBox1.SelectedValue.ToString() + "'", con);
    con.Open();
    int stock = Convert.ToInt32(cmd.ExecuteScalar());
    con.Close();
    if(stock< Convert.ToInt32(numericUpDown1.Value))//Insufficient stock if the stock is less than the amount to be added
    {
        MessageBox.Show("This item is out of stock!","hint");
        toolStripStatusLabel1.Text = "Finish";
        return;
    }

    //If the inventory is sufficient, you can add it to the shopping cart Cart, that is, perform an insert operation. There are two cases, the shopping cart already has the product (find the product and only increase the quantity) and the product has not been added (find the relevant attributes from the product Product, add a Record is inserted into the shopping cart Cart)
    SqlDataAdapter da = new SqlDataAdapter("select * from Cart", con);
    SqlCommandBuilder scb = new SqlCommandBuilder(da);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dt.PrimaryKey = new DataColumn[] { dt.Columns["Name"] };//Set the primary key, in order to find the specified product below
    DataRow row = dt.Rows.Find(listBox1.SelectedValue.ToString());
    if (row == null)//No records found, indicating that this product is added for the first time
    {
        cmd.CommandText = "select Price from Product where Name=N'" + listBox1.SelectedValue.ToString() + "'";
        con.Open();
        string price = cmd.ExecuteScalar().ToString();
        con.Close();
        row = dt.NewRow();
        row.BeginEdit();
        row[0] = listBox1.SelectedValue.ToString();
        row[1] = Convert.ToDouble(price);
        row[2] = Convert.ToInt32(numericUpDown1.Value);
        row[3] = Convert.ToDouble(price) * Convert.ToInt32(numericUpDown1.Value);
        row.EndEdit();
        dt.Rows.InsertAt(row, 0);
    }
    else//If you find it, you only need to increase the Num value of the record
    {
        row.BeginEdit();
        row[2] = Convert.ToInt32(row[2]) +Convert.ToInt32(numericUpDown1.Value);
        row[3] = Convert.ToDouble(row[1]) * Convert.ToInt32(row[2]);
        row.EndEdit();
    }
    dataGridView1.DataSource = dt;
    da.Update(dt);//Submit records to the shopping cart Cart

    //Subtract Inventory from Product
    con.Open();
    cmd.CommandText = "update Product set Stock-="+ numericUpDown1.Value + " where Name=N'" + listBox1.SelectedValue.ToString() + "'";
    cmd.ExecuteNonQuery();

    //Calculate the total amount and fill it in the consumption column (red)
    cmd.CommandText = "select sum(Total) from Cart";
    label2.Text = cmd.ExecuteScalar().ToString();
    con.Close();
}

When entering a number in the actual payment text box, change the change value synchronously

private void textBox1_TextChanged(object sender, EventArgs e)
{
    try
    {
        double cost = Convert.ToDouble(label2.Text);
        double pay = Convert.ToDouble(textBox1.Text);
        double ret = pay - cost;
        label6.Text = ret.ToString();
    }
    catch (FormatException)//If the amount entered is found to be illegal, do nothing, that is, do not change the value of the change
    {
        return;
    }
}

When clicking to complete the payment, it is necessary to judge whether the amount entered is sufficient, or whether there is any product added; if the payment is successful, the content in the shopping cart will be deleted, and the control will be restored to the initial value

private void button2_Click(object sender, EventArgs e)
{
    //In the first case, click to pay without shopping
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "select count(Name) from Cart";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
    {
        con.Close();
        MessageBox.Show("You have not selected an item yet!","hint");
        toolStripStatusLabel1.Text = "Finish";
        return;
    }
    con.Close();

    //In the second case, the amount entered is less than the consumption amount, or illegal data is entered
    double ret = Convert.ToDouble(label6.Text);
    if (ret < 0)
    {
        MessageBox.Show("You are not paying enough!", "hint");
        toolStripStatusLabel1.Text = "Finish";
        return;
    }
    try
    {
        double pay = Convert.ToDouble(textBox1.Text);
    }
    catch (FormatException)
    {
        MessageBox.Show("You are not paying enough!", "hint");
        toolStripStatusLabel1.Text = "Finish";
        return;
    }

    //Write the record to the shopping history History
    con.Open();
    cmd.CommandText = "select Name,Num from Cart";
    SqlDataReader sdr = cmd.ExecuteReader();
    string content = "";//Read the record to this string as the Content attribute value of the shopping record, in the form of (Nutrition Express x2, Orange x3...)
    while (sdr.Read())
    {
        content += "," + sdr.GetValue(0).ToString() + "x" + sdr.GetValue(1).ToString();
    }
    sdr.Close();
    content = content.Substring(1);//Remove the first character ","
    cmd.CommandText = "insert into History(Content,Total,Time) values(N'"+content+"',"+Convert.ToDouble(label2.Text)+",'"+DateTime.Now.ToLocalTime()+"')";
    cmd.ExecuteNonQuery();
    con.Close();

    //Empty the contents of the cart
    cmd.CommandText = "delete from Cart";
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();

    //reset datagridview1
    SqlDataAdapter da = new SqlDataAdapter("select * from Cart", con);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dataGridView1.DataSource = dt;

    //Reset the value of the following controls
    numericUpDown1.Value = 1;
    label2.Text = "0";
    label6.Text = "0";
    textBox1.Text = "";

    //Tips for Shopping Success
    MessageBox.Show("Welcome to visit us next time!", "hint");
}

Select one or more rows in the shopping cart, click the delete button to trigger the delete event, and remove the selected items from the shopping cart

private void button3_Click(object sender, EventArgs e)
{
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    //Iterate over each row selected
    for (int i = 0; i < dataGridView1.SelectedRows.Count; i++)
    {
        string name = dataGridView1.SelectedRows[i].Cells[0].Value.ToString();
        int num = Convert.ToInt32(dataGridView1.SelectedRows[i].Cells[2].Value);//Get the Name value of the current row
        cmd.CommandText = "delete from Cart where Name=N'" + name + "';update Product set Stock+="+num+ " where Name=N'" + name + "';";
        cmd.ExecuteNonQuery();//execute delete
    }

    //Let the datagridview redisplay the content of the deleted shopping cart Cart
    SqlDataAdapter da = new SqlDataAdapter("select * from Cart", con);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dataGridView1.DataSource = dt;

    //Recalculate the total spend
    cmd.CommandText = "select sum(Total) from Cart";
    label2.Text = cmd.ExecuteScalar().ToString();
    con.Close();
}

The above only explained the code of the main window, and the codes of the remaining two windows are similar, and the implementation ideas are all commented in the code

For complete code see:

https://github.com/FonlinGH/SimpleCheckoutSystem

Tags: Database C#

Posted by Raphael diSanto on Sat, 25 Mar 2023 00:31:54 +0530