目录
简要说明
有时候项目中，在部署过程中，单独用工具去创建数据库和表，会消耗很多人力和时间，也不利于后期程序迭代去增加数据表，
那可以在程序启动的时候，去判断数据库和表是否存在，如果不存在则创建
包括后续表需要增加字段的话，也可以用语句去新增字段-可自行研究下，此篇文章没展示
下面的静态类方法可以直接复制使用，数据库链接的字段就要替换成你自己的数据即可，表结构数据也是
代码实现
/// <summary>
/// 数据库初始化
/// </summary>
public static class DBCreate
{
static string server = "127.0.0.1"; // 替换为你的服务器地址
static string user = "root"; // 替换为你的用户名
static string password = "123qwe"; // 替换为你的密码
static string databaseName = "pz240606c"; // 替换为你的数据库名称
static string T_Alarm = @"CREATE TABLE `pz240606c`.`T_Alarm` (
`ID` varchar(32) NOT NULL,
`AlarmTime` datetime NOT NULL,
`AlarmCode` varchar(32) NULL,
`AlarmType` varchar(255) NULL,
`AlarmContent` varchar(2048) NULL,
`HandleTime` datetime NOT NULL,
`IsUpLoad` int NULL,
`CreateTime` datetime NOT NULL,
PRIMARY KEY (`ID` DESC)
);";
static string T_BoxNG = @"CREATE TABLE `pz240606c`.`T_BoxNG` (
`ID` varchar(32) NOT NULL,
`Time` datetime NOT NULL,
`Type` int NULL,
`NGNum` int NULL,
`QRCode` varchar(1024) NULL,
`CreateTime` datetime NOT NULL,
`Remark` varchar(2048) NULL DEFAULT NULL,
`WorkNum` varchar(255) NULL,
`IsTest` int NULL,
PRIMARY KEY (`ID`)
);";
static string T_LittleBox = @"CREATE TABLE `pz240606c`.`T_LittleBox` (
`ID` varchar(32) NOT NULL,
`PackingQRCode` varchar(255) NOT NULL,
`QRCode` varchar(255) NOT NULL,
`BoxCode` varchar(255) NULL,
`BoxName` varchar(255) NULL,
`BoxSpec` varchar(255) NULL,
`BoxColor` varchar(255) NULL,
`Time` datetime NOT NULL,
`CreateTime` datetime NOT NULL,
`WorkNum` varchar(255) NULL,
`IsUpload` int NULL,
`IsTest` int NULL,
PRIMARY KEY (`ID`)
);";
static string T_Packing = @"CREATE TABLE `pz240606c`.`T_Packing` (
`ID` varchar(32) NOT NULL,
`PackingCode` varchar(255) NULL,
`PackingName` varchar(255) NULL,
`PackingSpec` varchar(255) NULL,
`PackingColor` varchar(255) NULL,
`QRCode` varchar(255) NOT NULL,
`Time` datetime NOT NULL,
`CreateTime` datetime NOT NULL,
`WorkNum` varchar(255) NULL,
`IsUpload` int NULL,
`IsTest` int NULL,
PRIMARY KEY (`ID`)
);";
static string T_TimeSummary = @"CREATE TABLE `pz240606c`.`T_TimeSummary` (
`ID` varchar(32) NOT NULL,
`Type` int NOT NULL,
`BeginTime` datetime NOT NULL,
`EndTime` datetime NOT NULL,
`CreateTime` datetime NOT NULL,
`Remark` varchar(2048) NULL DEFAULT NULL,
`WorkNum` varchar(255) NULL,
PRIMARY KEY (`ID`)
) COMMENT = '时间汇总表';";
static Dictionary<string, string> tables = new Dictionary<string, string>()
{
{ "T_Alarm",DBCreate.T_Alarm},
{ "T_BoxNG",DBCreate.T_BoxNG},
{ "T_LittleBox",DBCreate.T_LittleBox},
{ "T_Packing",DBCreate.T_Packing},
{ "T_TimeSummary",DBCreate.T_TimeSummary },
};
/// <summary>
/// 初始化数据库
/// </summary>
public static BaseResult CreateDB()
{
try
{
using (var connection = new MySqlConnection($"Server={server};User Id={user};Password={password};Port=3306;"))
{
try
{
connection.Open();
LogOperate.Start("Connected to MariaDB server.");
// 检查数据库是否存在
if (!DatabaseExists(connection, databaseName))
{
CreateDatabase(connection, databaseName);
LogOperate.Start($"Database '{databaseName}' created.");
}
else
{
LogOperate.Start($"Database '{databaseName}' already exists.");
}
// 切换到目标数据库
connection.ChangeDatabase(databaseName);
foreach (var tb in tables)
{
// 检查表是否存在
if (!TableExists(connection, tb.Key))
{
CreateTable(connection, tb.Value);
LogOperate.Start($"Table '{tb.Key}' created.");
}
else
{
LogOperate.Start($"Table '{tb.Key}' already exists.");
}
}
}
catch (Exception ex)
{
LogOperate.Start($"An error occurred: {ex.Message}");
return new BaseResult(false, "初始化数据库失败，" + ex.Message);
}
}
return BaseResult.Successed;
}
catch (Exception ex)
{
return new BaseResult(false, "初始化数据库失败，" + ex.Message);
}
}
/// <summary>
/// 检查数据库是否存在
/// </summary>
/// <param name="connection"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
static bool DatabaseExists(MySqlConnection connection, string databaseName)
{
string query = $"SHOW DATABASES LIKE '{databaseName}'";
using (var command = new MySqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
return reader.HasRows;
}
}
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="connection"></param>
/// <param name="databaseName"></param>
static void CreateDatabase(MySqlConnection connection, string databaseName)
{
string query = $"CREATE DATABASE IF NOT EXISTS {databaseName}";
using (var command = new MySqlCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
/// <summary>
/// 检查表是否存在
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <returns></returns>
static bool TableExists(MySqlConnection connection, string tableName)
{
string query = $"SHOW TABLES LIKE '{tableName}'";
using (var command = new MySqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
return reader.HasRows;
}
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="connection"></param>
/// <param name="sql"></param>
static void CreateTable(MySqlConnection connection, string sql)
{
string query = sql;
using (var command = new MySqlCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
}