DbHelper
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
namespace MySQLDemo
{
public static class DbHelper
{
// 数据表如果已经被导出，则在其表名的尾部增加该标识
private const string StrSuffix = @"_exported";
// 数据表的名称的前缀
private const string StrPrefix = @"tab_";
// 根据用该值格式化后的时间字符串，判断是否需要创建新的数据表
private const string StrDate = @"yyyyMMddHHmm";
// 数据库名称
private const string StrDatabase = @"test0";
// 在数据表中插入时间数据的格式
private const string StrDatetime = @"yyyy-MM-dd HH:mm:ss";
/// <summary>
/// 获取数据库操作级别的连接字符串
/// </summary>
/// <returns>连接字符串</returns>
private static string GetDatabaseConnectString()
{
var sb = new MySqlConnectionStringBuilder
{
Server = "127.0.0.1",
Port = 3306,
UserID = "root",
Password = "root",
SslMode = MySqlSslMode.None,
CharacterSet = "utf8"
};
return sb.ConnectionString;
}
/// <summary>
/// 获取表操作级别的连接字符串
/// </summary>
/// <returns>连接字符串</returns>
private static string GetTableConnectString()
{
var sb = new MySqlConnectionStringBuilder
{
Server = "127.0.0.1",
Port = 3306,
Database = StrDatabase,
UserID = "root",
Password = "root",
SslMode = MySqlSslMode.None,
CharacterSet = "utf8"
};
return sb.ConnectionString;
}
/// <summary>
/// 判断给定名称的数据库是否此存在
/// </summary>
/// <returns>存在返回true，否则返回false</returns>
public static bool DatabaseIsExisted()
{
using (var conn = new MySqlConnection(GetDatabaseConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name='{StrDatabase}'";
var res = cmd.ExecuteScalar();
return !(Convert.IsDBNull(res) || (long) res <= 0);
}
}
}
/// <summary>
/// 创建指定名称的数据库
/// </summary>
/// <returns>创建成功返回true，否则返回false</returns>
public static bool CreateDatabase()
{
using (var conn = new MySqlConnection(GetDatabaseConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"CREATE DATABASE {StrDatabase}";
var res = cmd.ExecuteNonQuery();
return res > 0;
}
}
}
/// <summary>
/// 判断指定名称的表在数据库中是否存在
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <returns>返回结果状态，true 存在，false 不存在</returns>
private static bool IsTableExist(string tableName)
{
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT COUNT(*) FROM information_schema.TABLES " +
$"WHERE TABLE_SCHEMA = '{StrDatabase}' " +
$"AND TABLE_TYPE = 'BASE TABLE' " +
$"AND TABLE_NAME ='{tableName}'";
var res = cmd.ExecuteScalar();
return (long) res > 0;
}
}
}
/// <summary>
/// 创建指定名称的数据表
/// </summary>
/// <param name="name">数据表名称</param>
/// <returns>返回结果状态，true 创建成功，false 创建失败</returns>
private static bool CreateTable(string name)
{
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"CREATE TABLE {name} ( " +
$"Id bigint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY," +
$"Time varchar(50) NOT NULL," +
$"Station varchar(255) NOT NULL)";
var res = cmd.ExecuteNonQuery();
return res == 0;
}
}
}
/// <summary>
/// 删除数据库中指定名称的数据表
/// </summary>
/// <param name="dateTime">删除时间</param>
/// <returns>返回结果状态，true 删除成功，false 删除失败</returns>
public static bool DropTable(DateTime dateTime)
{
var strDate = dateTime.ToString(StrDate);
// 使用LINQ语句，获取需要进行查询的表格
var nameList = GetTableNames().Where(item => long.Parse(item.Substring(4, StrDate.Length)) <= long.Parse(strDate)
&& item.EndsWith(StrSuffix)).Select(item => item).ToList();
if (nameList.Count <= 0)
return false;
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
foreach(var item in nameList)
{
cmd.CommandText = $"DROP TABLE {item}";
cmd.ExecuteNonQuery();
}
}
}
return false;
}
/// <summary>
/// 获取指定数据库中所有用户定义的表的名称
/// </summary>
/// <returns>表名列表</returns>
private static IEnumerable<string> GetTableNames()
{
var nameList = new List<string>();
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT TABLE_NAME FROM information_schema.TABLES " +
$"WHERE TABLE_SCHEMA = '{StrDatabase}' " +
$"AND TABLE_TYPE = 'BASE TABLE' " +
$"AND TABLE_NAME LIKE '{StrPrefix}%' " +
$"ORDER BY TABLE_NAME";
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
nameList.Add(reader.GetString(0));
}
reader.Close();
}
}
return nameList;
}
/// <summary>
/// 向数据库中插入数据
/// 根据指定的时间判断是否创建新的数据表
/// </summary>
/// <param name="time">时间</param>
/// <param name="station">站点</param>
/// <returns>返回结果状态，true 插入成功，false 插入失败</returns>
public static bool InsertData(DateTime time, string station)
{
var tableName = StrPrefix + DateTime.Now.ToString(StrDate);
var isExisted = IsTableExist(tableName);
if (!isExisted)
{
var isSuccess = CreateTable(tableName);
if (!isSuccess)
{
return false;
}
}
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand($"INSERT INTO {tableName} (Time, Station) VALUES ('{time.ToString(StrDatetime)}','{station}')", conn))
{
var res = cmd.ExecuteNonQuery();
return res > 0;
}
}
}
/// <summary>
/// 查询给定开始时间和终止时间之间的数据，并保存在DataTable中
/// </summary>
/// <param name="dateTimeStx">开始时间</param>
/// <param name="dateTimeEtx">结束时间</param>
/// <param name="dataTable">输出数据表</param>
/// <returns>返回结果状态，true 查询成功，false 查询失败</returns>
public static bool MultiTableQueryWithDateTime(DateTime dateTimeStx, DateTime dateTimeEtx, out DataTable dataTable)
{
/*
* 根据开始时间与结束时间可以确定在哪张或哪几张表格中进行查询
*/
dataTable = new DataTable();
var strDateStx = dateTimeStx.ToString(StrDate);
var strDateEtx = dateTimeEtx.ToString(StrDate);
// 使用LINQ语句，获取需要进行查询的表格
var nameList = GetTableNames().Where(item => !item.EndsWith(StrSuffix)
&& long.Parse(item.Substring(4, StrDate.Length)) >= long.Parse(strDateStx)
&& long.Parse(item.Substring(4, StrDate.Length)) <= long.Parse(strDateEtx)).Select(item=>item).ToList();
if (nameList.Count <= 0)
return false;
var queryString = string.Empty;
var strDateTimeStx = dateTimeStx.ToString(StrDatetime);
var strDateTimeEtx = dateTimeEtx.ToString(StrDatetime);
for (var i = 0; i < nameList.Count; i++)
{
if (i != 0)
{
queryString += " UNION ";
}
queryString += $"SELECT Id,Time,Station FROM {nameList[i]} WHERE Time BETWEEN '{strDateTimeStx}' AND '{strDateTimeEtx}'";
}
if (queryString == string.Empty)
{
return false;
}
queryString += " ORDER BY Station ASC, Time ASC";
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryString;
using (var adapter = new MySqlDataAdapter(cmd))
{
// 复制表内容
var num = adapter.Fill(dataTable);
if (num < 0)
return false;
// 增加索引编号
for (var i = 1; i <= dataTable.Rows.Count; i++)
{
dataTable.Rows[i - 1]["Id"] = i;
}
}
}
}
return true;
}
/// <summary>
/// 将指定之间以前数据导出到CSV文件
/// </summary>
/// <param name="dateTime">导出时间</param>
/// <param name="strFileName">导出文件名</param>
/// <returns>返回结果状态，true 导出成功，false 导出失败</returns>
public static bool ExportDataToCsv(DateTime dateTime, string strFileName)
{
var strDate = dateTime.ToString(StrDate);
// 使用LINQ语句，获取需要进行查询的表格
var nameList = GetTableNames().Where(item => long.Parse(item.Substring(4, StrDate.Length)) <= long.Parse(strDate)
&& !item.EndsWith(StrSuffix)).Select(item => item).ToList();
if (nameList.Count <= 0)
return false;
var queryString = string.Empty;
for (var i = 0; i < nameList.Count; i++)
{
if (i != 0)
{
queryString += " UNION ";
}
queryString += $"SELECT Id,Time,Station FROM {nameList[i]}";
}
if (queryString == string.Empty)
{
return false;
}
using (var conn = new MySqlConnection(GetTableConnectString()))
{
conn.Open();
using (var cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryString;
using (var adapter = new MySqlDataAdapter(cmd))
{
var dataTable = new DataTable();
var num = adapter.Fill(dataTable);
if (num < 0)
return false;
// 增加索引编号
for (var i = 1; i <= dataTable.Rows.Count; i++)
{
dataTable.Rows[i - 1]["Id"] = i;
}
CsvHelper.Dt2Csv(dataTable, strFileName);
}
foreach(var item in nameList)
{
cmd.CommandText = $"ALTER TABLE {item} RENAME TO {item + StrSuffix}";
cmd.ExecuteNonQuery();
}
}
}
return true;
}
}
}CsvHelper
using System.Data;
using System.IO;
using System.Text;
namespace MySQLDemo
{
public static class CsvHelper
{
/// <summary>
/// 导出报表为Csv
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="strFilePath">物理路径</param>
public static bool Dt2Csv(DataTable dt, string strFilePath)
{
try
{
var writer = new StreamWriter(strFilePath, false, Encoding.UTF8);
// 添加字段
var strBufferLine = "索引,时间,工位";
writer.WriteLine(strBufferLine);
for (var i = 0; i < dt.Rows.Count; i++)
{
strBufferLine = $"{dt.Rows[i][0]},{dt.Rows[i][1]},{dt.Rows[i][2]}";
writer.WriteLine(strBufferLine);
}
writer.Close();
return true;
}
catch
{
return false;
}
}
}
}Program
using System;
using System.Data;
using System.Threading;
using System.Timers;
namespace MySQLDemo
{
internal static class Program
{
// 用于线程同步
private static readonly object DataLocker = new object();
public static void Main(string[] args)
{
// 如果数据库不存在，则创建数据库
if (!DbHelper.DatabaseIsExisted())
{
DbHelper.CreateDatabase();
}
// 每十秒中查询一次数据
var queryTimer = new System.Timers.Timer(10 * 1000) {AutoReset = true};
queryTimer.Elapsed += QueryTimerOnElapsed;
// 每四秒中插入一次数据
var insertTimer = new System.Timers.Timer(4 * 1000) {AutoReset = true};
insertTimer.Elapsed += WriteTimerOnElapsed;
// 每一分钟导出一次数据
var exportTimer = new System.Timers.Timer(1 * 60 * 1000) { AutoReset = true };
exportTimer.Elapsed += ExportTimer_Elapsed;
queryTimer.Start();
insertTimer.Start();
exportTimer.Start();
while (true)
{
if (Console.ReadKey().Key != ConsoleKey.Q)
continue;
queryTimer.Stop();
insertTimer.Stop();
exportTimer.Stop();
Thread.Sleep(2 * 60 * 1000);
break;
}
}
private static void ExportTimer_Elapsed(object sender, ElapsedEventArgs e)
{
try
{
lock (DataLocker)
{
var dateTime = DateTime.Now.AddMinutes(-5);
// 导出五分钟之前的所有数据
DbHelper.ExportDataToCsv(dateTime, dateTime.ToString("HHmm")+".csv");
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
private static void QueryTimerOnElapsed(object sender, ElapsedEventArgs e)
{
try
{
lock (DataLocker)
{
// 查询前三分钟到前一分钟的所有数据
DbHelper.MultiTableQueryWithDateTime(DateTime.Now.AddMinutes(-3), DateTime.Now.AddMinutes(-1), out var table);
if(table.Rows.Count <= 0)
return;
Console.WriteLine("==============================================================================");
foreach (DataRow row in table.Rows)
{
Console.WriteLine($"\t {row[0]}\t{row[1]}\t{row[2]}");
}
Console.WriteLine("==============================================================================");
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
private static void WriteTimerOnElapsed(object sender, ElapsedEventArgs e)
{
try
{
lock (DataLocker)
{
DbHelper.InsertData(DateTime.Now, DateTime.Now.ToString("yyyy MM dd HHmmss"));
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
