需求分析:
生产过程中会有多个机柜,每个机柜里会有多个条码,为了快速的将其中的重复条码筛选出来,以便于业务人员使用。条码数据摘取自其他系统或用户提供的Excel。
程序使用开发语言使用C#,数据库选择SqlLite3,开发工具使用VS2017或以上。
程序目录结构:
程序操作界面如下图:
程序代码如下:
DBHelper类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Data;
namespace CodeCheckTool
{
public class DBHelper
{
static string fileName = "codechecktool.db";
static string connectionString = $"Data Source={fileName};Version=3;";
SQLiteConnection connection = new SQLiteConnection(connectionString);
public void CreateDB()
{
if (!File.Exists(fileName))
{
connection.Open();
string sql = "CREATE TABLE TB_CodeList (ID varchar(36) PRIMARY KEY,Code nvarchar(max),GuiHao nvarchar(max));";
// 创建表等操作
using (var command = new SQLiteCommand(sql,connection))
{
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool ExcuteNonQuery(string sql)
{
int success = 0;
connection.Open();
// 创建表等操作
using (var command = new SQLiteCommand(sql,connection))
{
success=command.ExecuteNonQuery();
}
connection.Close();
return success > 0;
}
/// <summary>
/// 查询数据并返回table
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable ExcuteQuery(string sql)
{
connection.Open();
DataTable dataTable = new DataTable();
using (var command = new SQLiteCommand(sql, connection))
{
using (var reader = command.ExecuteReader())
{
dataTable.Load(reader);
}
}
connection.Close();
return dataTable;
}
}
}
窗体程序代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CodeCheckTool
{
public partial class Form1 : Form
{
DBHelper dbHelper = new DBHelper();
public Form1()
{
InitializeComponent();
CheckForIllegalCrossThreadCalls = false;
}
private void Form1_Load(object sender, EventArgs e)
{
dbHelper.CreateDB();
comboBox1.SelectedIndex = 0;
}
private void btn_insert_data_Click(object sender, EventArgs e)
{
if (textBox1.Text.Trim().Length == 0)
{
MessageBox.Show("请输入条码");
return;
}
if (txt_guihao.Text.Trim().Length == 0)
{
MessageBox.Show("请输入柜号");
return;
}
Thread thread = new Thread(new ThreadStart(ImportData));
thread.Start();
}
private void ImportData()
{
int i = 0;
int j = 0;
foreach (string item in textBox1.Lines)
{
string sql = string.Format("select * from TB_CodeList where Code='{0}' and GuiHao='{1}'", item, txt_guihao.Text.Trim());
DataTable dt = dbHelper.ExcuteQuery(sql);
if (dt != null && dt.Rows.Count > 0)
{
textBox2.AppendText(string.Format("条码【{0}】在柜号【{1}】中\r\n", item, txt_guihao.Text.Trim()));
}
else
{
string insert_sql = string.Format("insert into TB_CodeList(ID,Code,GuiHao) values('{0}','{1}','{2}')", Guid.NewGuid().ToString().Replace("-", ""), item, txt_guihao.Text.Trim());
bool isIn = dbHelper.ExcuteNonQuery(insert_sql);
if (isIn)
{
textBox3.AppendText(string.Format("条码【{0}】不在柜号【{1}】中\r\n", item, txt_guihao.Text.Trim()));
i += 1;
}
}
j += 1;
}
lable_info.Text = string.Format("共计【{0}】条,本次导入【{1}】条",j, i);
}
private void btn_clear_Click(object sender, EventArgs e)
{
Clear();
}
private void Clear()
{
string value = "";
textBox1.Text = value;
textBox2.Text = value;
textBox3.Text = value;
txt_guihao.Text = value;
lable_info.Text = value;
}
/// <summary>
/// 删除输入的柜数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
string sql = string.Format("delete from TB_CodeList where GuiHao={0}",txt_guihao.Text.Trim());
DialogResult result = MessageBox.Show(string.Format("确定要删除该柜号【{0}】的数据吗?", txt_guihao.Text.Trim()), "确认对话框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result==DialogResult.OK)
{
bool success = dbHelper.ExcuteNonQuery(sql);
if (success)
{
MessageBox.Show("删除成功!");
Clear();
}
else
{
MessageBox.Show("删除失败!");
}
}
}
/// <summary>
/// 检测重复数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
string sql = "select Code,COUNT(Code) as CodeCount from TB_CodeList group by Code HAVING count(Code)>1 ";
DataTable dt = dbHelper.ExcuteQuery(sql);
dataGridView1.Refresh();
dataGridView1.DataSource = dt;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns["Code"].HeaderText = "条码";
dataGridView1.Columns["CodeCount"].HeaderText = "数量";
if (dataGridView1.Columns.Count == dt.Columns.Count)
{
AddCheckBoxColumn();
}
}
private void AddCheckBoxColumn()
{
if (dataGridView1.Columns[0] is DataGridViewCheckBoxColumn)
{
// 复选框列已存在,不需要再次添加
return;
}
DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
checkBoxColumn.ValueType = typeof(bool);
checkBoxColumn.Name = "checkBox";
checkBoxColumn.HeaderText = "选择";
dataGridView1.Columns.Insert(0,checkBoxColumn);
}
/// <summary>
/// 查询柜信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
SearchGuiInfo();
}
private void SearchGuiInfo()
{
string sql = string.Format(@"select GuiHao Code,COUNT(GuiHao) as CodeCount from TB_CodeList where {0}
group by GuiHao order by COUNT(GuiHao) ", textBox4.Text.Length > 0 ? string.Format(" GuiHao='{0}' ", textBox4.Text.Trim()) : " 1=1 ");
DataTable dt = dbHelper.ExcuteQuery(sql);
dataGridView1.Refresh();
dataGridView1.DataSource = dt;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns["Code"].HeaderText = "柜号";
dataGridView1.Columns["CodeCount"].HeaderText = "数量";
if (dataGridView1.Columns.Count == dt.Columns.Count)
{
AddCheckBoxColumn();
}
}
/// <summary>
/// 删除柜
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
List<string> list = new List<string>();
DialogResult result = MessageBox.Show(string.Format("确定要删除选中的数据吗?", txt_guihao.Text.Trim()), "确认对话框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
int i = 0;
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
if (Convert.ToBoolean(row.Cells[0].Value)) // 假设复选框列是第一列
{
string cellVal = row.Cells[1].Value.ToString();
string sql = string.Format("delete from TB_CodeList where GuiHao='{0}'", cellVal);
dbHelper.ExcuteNonQuery(sql);
i++;
}
}
if (i > 0)
{
MessageBox.Show("操作成功!");
//删除成功后查询
SearchGuiInfo();
}
else
{
MessageBox.Show("操作失败");
}
}
}
/// <summary>
/// 条码查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
SearchCodeInfo();
}
private void SearchCodeInfo()
{
string sql = string.Format("select GuiHao Code,Code CodeCount from TB_CodeList where 1=1 ");
if (comboBox1.SelectedItem.ToString() == "条码" && textBox4.Text.Trim().Length > 0)
{
sql += string.Format(" and Code='{0}'", textBox4.Text.Trim());
}
if (comboBox1.SelectedItem.ToString() == "柜号" && textBox4.Text.Trim().Length > 0)
{
sql += string.Format(" and GuiHao='{0}'", textBox4.Text.Trim());
}
DataTable dt = dbHelper.ExcuteQuery(sql);
dataGridView1.Refresh();
dataGridView1.DataSource = dt;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns["CodeCount"].HeaderText = "条码";
dataGridView1.Columns["Code"].HeaderText = "柜号";
if (dataGridView1.Columns.Count == dt.Columns.Count)
{
AddCheckBoxColumn();
}
}
/// <summary>
/// 删除条码
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
List<string> list = new List<string>();
DialogResult result = MessageBox.Show(string.Format("确定要删除选中的数据吗?", txt_guihao.Text.Trim()), "确认对话框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
int i = 0;
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
if (Convert.ToBoolean(row.Cells[0].Value)) // 假设复选框列是第一列
{
string cellVal = row.Cells[2].Value.ToString();
string sql = string.Format("delete from TB_CodeList where Code='{0}'", cellVal);
dbHelper.ExcuteNonQuery(sql);
i++;
}
}
if (i > 0)
{
MessageBox.Show("操作成功!");
//删除成功后查询
SearchCodeInfo();
}
else
{
MessageBox.Show("操作失败!");
}
}
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
private void btn_stop_Click(object sender, EventArgs e)
{
MessageBox.Show("暂未实现!");
}
}
}