CCDCount/TestWork.DLL/SqlDataClass/BatchMessSqliteDataClass.cs

using CCDCount.MODEL.AuditTrailModel;
using CCDCount.MODEL.SqlDataModel;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

namespace CCDCount.DLL.SqlDataClass
{
    public class BatchMessSqliteDataClass
    {
        private string DatabaseFile;
        private readonly string _connectionString;
        private object locker = new object();

        public BatchMessSqliteDataClass(string dbPath)
        {
            DatabaseFile = dbPath;
            _connectionString = $"Data Source={DatabaseFile};Version=3;" +
                "Journal Mode=WAL;" +           // 使用WAL模式提高并发性
                "Synchronous=Normal;" +         // 平衡性能和安全性
                "Cache Size=10000;" +           // 增加缓存大小
                "Pooling=true;" +               // 启用连接池
                "Max Pool Size=100;";           // 设置最大连接池大小
            string directoryPath = Path.GetDirectoryName(DatabaseFile);
            if (!Directory.Exists(directoryPath))
            {
                // 创建文件夹
                Directory.CreateDirectory(directoryPath);
            }
            InitializeDatabase();
        }

        // 初始化数据库和表
        private void InitializeDatabase()
        {
            if (!File.Exists(DatabaseFile))
            {
                SQLiteConnection.CreateFile(DatabaseFile);
            }

            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                string createTableSql = @"
                    CREATE TABLE IF NOT EXISTS BatchMessage (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        BatchNunber TEXT NOT NULL,
                        MaterialCylinderVibrationTableSpeed REAL NOT NULL,
                        FilterVibrationTableSpeed REAL NOT NULL,
                        CountVibrationTableSpeed REAL NOT NULL,
                        BottingCount INTEGER NOT NULL,
                        BottingSpeed INTEGER NOT NULL,
                        RecordTime TEXT NOT NULL
                )";
                new SQLiteCommand(createTableSql, conn).ExecuteNonQuery();
            }
        }

        // 插入数据
        public void InsertBatchMessage(BatchRecordModel Message)
        {
            lock (locker)
            {
                using (var conn = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
                {
                    conn.Open();
                    string sql = "INSERT INTO BatchMessage" +
                        " (BatchNunber, MaterialCylinderVibrationTableSpeed, FilterVibrationTableSpeed,CountVibrationTableSpeed,BottingCount,BottingSpeed,RecordTime)" +
                        " VALUES (@BatchNunber, @MaterialCylinderVibrationTableSpeed, @FilterVibrationTableSpeed,@CountVibrationTableSpeed, @BottingCount, @BottingSpeed,@RecordTime)";
                    var cmd = new SQLiteCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@BatchNunber", Message.BatchNunber);
                    cmd.Parameters.AddWithValue("@MaterialCylinderVibrationTableSpeed", Message.MaterialCylinderVibrationTableSpeed);
                    cmd.Parameters.AddWithValue("@FilterVibrationTableSpeed", Message.FilterVibrationTableSpeed);
                    cmd.Parameters.AddWithValue("@CountVibrationTableSpeed", Message.CountVibrationTableSpeed);
                    cmd.Parameters.AddWithValue("@BottingCount", Message.BottingCount);
                    cmd.Parameters.AddWithValue("@BottingSpeed", Message.BottingSpeed);
                    cmd.Parameters.AddWithValue("@RecordTime", Message.RecordTime.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 查询所有数据
        public List<BatchRecordModel> GetAllBatchMessage()
        {
            var BatchS = new List<BatchRecordModel>();
            using (var conn = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                conn.Open();
                string sql = "SELECT * FROM BatchMessage";
                var cmd = new SQLiteCommand(sql, conn);
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        BatchS.Add(new BatchRecordModel
                        {
                            BatchNunber = reader["BatchNunber"].ToString(),
                            MaterialCylinderVibrationTableSpeed = Convert.ToUInt16(reader["MaterialCylinderVibrationTableSpeed"]),
                            FilterVibrationTableSpeed = Convert.ToUInt16(reader["FilterVibrationTableSpeed"]),
                            CountVibrationTableSpeed = Convert.ToUInt16(reader["CountVibrationTableSpeed"]),
                            BottingCount = Convert.ToInt32(reader["BottingCount"]),
                            BottingSpeed = Convert.ToUInt16(reader["BottingSpeed"]),
                            RecordTime = DateTime.Parse(reader["RecordTime"].ToString())
                        });
                    }
                }
            }
            return BatchS;
        }
    }
}