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: