条码重复检查工具，基于C#和SqlLite3

需求分析：

       生产过程中会有多个机柜，每个机柜里会有多个条码，为了快速的将其中的重复条码筛选出来，以便于业务人员使用。条码数据摘取自其他系统或用户提供的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("暂未实现!");
        }
    }
}