LL17-honghai/C-Windows-1/BarcodeInfo.cs

using ADOX;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices;
using System.Runtime.Remoting.Channels;
using System.Windows.Forms;

namespace C_Windows_1
{
    internal class BarcodeInfo
    {
        public class BarcodeRecord
        {

            public string 测试时间 { get; set; }
            public string 条形码 { get; set; }
            public string 工单 { get; set; }
            public string 产品名称 { get; set; }
            public string 产品型号 { get; set; }
            public string 判定结果 { get; set; }
            public string 开始重测 { get; set; }
            public string 测试人 { get; set; }
        }

        /// <summary>
        /// 记录条码
        /// </summary>
        /// <param name="createTime"></param>
        /// <param name="barcode"></param>
        /// <param name="result"></param>
        /// <param name="user"></param>
        public static void InsertBarcodeData(string createTime, string barcode, string workOrder, string productName, string productModel, string result, string user, string reTest = "NO")
        {
            string filepath = System.Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb";
            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb;";
            OleDbConnection con = new OleDbConnection(constr);
            try
            {
                if (File.Exists(filepath) == false)//判断所选路径是否有文件
                {
                    string filepath2 = System.Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\";
                    Directory.CreateDirectory(filepath2);//新建文件夹
                    Catalog Product = new Catalog();
                    Product.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb;Jet OLEDB:Engine Type=5;");
                    con.Open();
                    string sql = "CREATE TABLE BarcodeInfo([id] int identity(1,1),[CreateTime] VarChar(100),[Barcode] VarChar(150),[WorkOrder] VarChar(100),[ProductName] VarChar(100),[ProductModel] VarChar(100),[Result] VarChar(8),[User] VarChar(50),[ReTest] VarChar(8))";
                    OleDbCommand cmd = new OleDbCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

                //con.Open();
                //string sql2 = "INSERT INTO BarcodeInfo( CreateTime, Barcode, Result,User,ReTest) VALUES ('" + createTime + "', '" + barcode + "', '" + result + "', '" + user + "',  '" + reTest + "')";
                //OleDbCommand cmd2 = new OleDbCommand(sql2, con);
                //cmd2.ExecuteNonQuery();
                //con.Close();
                con.Open();
                OleDbCommand command = new OleDbCommand("INSERT INTO [BarcodeInfo]( [CreateTime], [Barcode],[WorkOrder],[ProductName], [ProductModel],[Result],[User],[ReTest]) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", con);
                OleDbParameterCollection paramCollection = command.Parameters;
                paramCollection.Add(new OleDbParameter("CreateTime", createTime));
                paramCollection.Add(new OleDbParameter("Barcode", barcode));
                paramCollection.Add(new OleDbParameter("WorkOrder", workOrder));
                paramCollection.Add(new OleDbParameter("ProductName", productName));
                paramCollection.Add(new OleDbParameter("ProductModel", productModel));
                paramCollection.Add(new OleDbParameter("Result", result));
                paramCollection.Add(new OleDbParameter("User", user));
                paramCollection.Add(new OleDbParameter("ReTest", reTest));
                command.ExecuteNonQuery();
                con.Close();

            }
            catch (Exception ex)
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查询条码行数
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public static int QueryBarcodeCount(string barcode)
        {
            string filepath = Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb";
            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath;

            try
            {
                if (!File.Exists(filepath))
                {
                    return 0;
                }
                else
                {
                    using (OleDbConnection con = new OleDbConnection(constr))
                    {
                        con.Open();

                        // 删除5天前数据
                        string deleteSql = "DELETE FROM [BarcodeInfo] WHERE DateDiff('d', [CreateTime], Now()) > 3";
                        OleDbCommand deleteCommand = new OleDbCommand(deleteSql, con);
                        deleteCommand.ExecuteNonQuery();

                        // 查询条形码数量
                        string countSql = "SELECT COUNT(*) FROM [BarcodeInfo] WHERE [Barcode] = @Barcode";
                        OleDbCommand countCommand = new OleDbCommand(countSql, con);
                        countCommand.Parameters.AddWithValue("@Barcode", barcode);

                        int count = (int)countCommand.ExecuteScalar();

                        return count;
                    }
                }
            }
            catch (Exception ex)
            {
                // 具体异常处理
                return -1;
            }
        }

        public static DataTable DataTableExcute(string cmdstr)
        {
            string filepath = Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb";
            if (!File.Exists(filepath)) return null;
            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb;";
            OleDbConnection con = new OleDbConnection(constr);
            OleDbCommand cmd = new OleDbCommand(cmdstr, con);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
            DataTable datatable = new DataTable();
            DataSet dataset = new DataSet();
            try
            {
                con.Open();
                dataAdapter.Fill(dataset);
                if (dataset.Tables[0].Rows.Count > 0)
                {
                    datatable = dataset.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                con.Close();
                cmd.Dispose();
                dataAdapter.Dispose();
            }
            return datatable;
        }

        public static List<BarcodeRecord> SelectBarcode()
        {
            //try
            //{
            //定义一个集合，用于存放对象
            List<BarcodeRecord> Record = new List<BarcodeRecord>();
            string filepath = Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb";
            if (File.Exists(filepath) == true)//判断所选路径是否有文件
            {
                string constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Environment.CurrentDirectory + "\\Config\\BarcodeRecord\\BarcodeInfo.mdb;";
                OleDbConnection con = new OleDbConnection(constr);
                con.Open();
                string sql2 = "SELECT * FROM BarcodeInfo";
                OleDbCommand cmd2 = new OleDbCommand(sql2, con);
                OleDbDataReader information = cmd2.ExecuteReader();
                //下移游标，读取一行，如果没有数据了则返回false
                while (information.Read())
                {
                    BarcodeRecord rec = new BarcodeRecord();
                    rec.测试时间 = Convert.ToString(information["CreateTime"]);
                    rec.条形码 = Convert.ToString(information["Barcode"]);
                    rec.判定结果 = Convert.ToString(information["Result"]);
                    rec.开始重测 = Convert.ToString(information["ReTest"]);
                    rec.测试人 = Convert.ToString(information["User"]);
                    Record.Add(rec);
                }
                //Warning.wa.DataGridView1.DataSource = Record;
                information.Close();
                con.Close();
            }
            return Record;
            //    return "OK";
            //}
            //catch (Exception ex)
            //{
            //    return ex.Message;
            //}
        }
    }
}