using LB_SmartVisionCommon;
|
using MySql.Data.MySqlClient;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace LB_SmartVision.SQL
|
{
|
public class DatabaseRecordProductDataHelper
|
{
|
private static string connectionString = "Server=localhost;Database=product_db;Uid=root;Pwd=root;";
|
private static object databaseRecordProductDataHelperObject = new object();
|
public static MySqlConnection GetConnection()
|
{
|
return new MySqlConnection(connectionString);
|
}
|
// 创建数据库和表
|
public static void InitializeDatabase()
|
{
|
lock (databaseRecordProductDataHelperObject)
|
{
|
try
|
{
|
using (var connection = GetConnection())
|
{
|
connection.Open();
|
// 创建数据库
|
string createDbSql = @"CREATE DATABASE IF NOT EXISTS product_db;";
|
using (var cmd = new MySqlCommand(createDbSql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
// 使用数据库
|
string useDbSql = @"USE product_db;";
|
using (var cmd = new MySqlCommand(useDbSql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
// 创建表（增加索引以提高查询性能）
|
string createTableSql = @"
|
CREATE TABLE IF NOT EXISTS RecordProductData (
|
Id INT AUTO_INCREMENT PRIMARY KEY,
|
ProductName VARCHAR(255) NOT NULL,
|
ProductSN VARCHAR(255) NOT NULL,
|
InspectionOperator VARCHAR(255),
|
NGType VARCHAR(255),
|
NGSize VARCHAR(255),
|
DetectionTime VARCHAR(255),
|
CameraInspection VARCHAR(255),
|
CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
|
INDEX idx_created_date (CreatedDate),
|
INDEX idx_product_number (ProductSN),
|
INDEX idx_operator (InspectionOperator)
|
);";
|
using (var cmd = new MySqlCommand(createTableSql, connection))
|
{
|
cmd.ExecuteNonQuery();
|
AsyncLogHelper.Info("数据库和表创建成功！");
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"数据库初始化失败: {ex.Message}");
|
throw;
|
}
|
}
|
}
|
}
|
}
|
