using System.Threading.Tasks;
|
using System.Xml.Linq;
|
|
namespace IStation.DAL
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public class ScheduleCombine_bak
|
{
|
public SqlSugarClient Connection
|
{
|
get { return ConfigHelper.GetSqlSugarClient(); }
|
}
|
|
private readonly string _tableNamePrefix = "ScheduleCombine_Cb_";
|
|
private string GetTableName(string runFlag)
|
{
|
return $"{_tableNamePrefix}{runFlag}";
|
}
|
|
private readonly string _createTable = "(\r\n ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n RunFlag VARCHAR (255),\r\n RunCount INTEGER,\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n CurveQH VARCHAR (255),\r\n CurveQP VARCHAR (255),\r\n MaxFlow REAL,\r\n MinFlow REAL,\r\n MaxHead REAL,\r\n MinHead REAL,\r\n AnaStatus BIT\r\n);";
|
|
/// <summary>
|
/// 获取全部表名
|
/// </summary>
|
public List<string> GetAllTableName()
|
{
|
var tables = new List<string>();
|
using (var connection = new SQLiteConnection(ConfigHelper.ConnectionString))
|
{
|
connection.Open();
|
var sql = $"select name from sqlite_master where type='table' and name like '%{_tableNamePrefix}%';";
|
using (var command = new SQLiteCommand(sql, connection))
|
{
|
using (var reader = command.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
var tableName = reader["name"].ToString();
|
tables.Add(tableName);
|
}
|
}
|
}
|
}
|
return tables;
|
}
|
|
|
/// <summary>
|
/// 通过表名获取所有未分析组合
|
/// </summary>
|
public List<Entity.ScheduleCombine> GetAllUnanalyzedAnaByTableName(string tableName)
|
{
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"select * from {tableName} where AnaStatus=False";
|
var list = db.Ado.SqlQuery<Entity.ScheduleCombine>(sql);
|
return list;
|
}
|
}
|
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_SplitTable(List<Entity.ScheduleCombine> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
using (SqlSugarClient db = Connection)
|
{
|
///自己来制定定义的规则
|
db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService = new Entity.ScheduleCombineSubTableService();
|
db.CodeFirst
|
.SplitTables()//标识分表
|
.InitTables<Entity.ScheduleCombine>(); //程序启动时加这一行,如果一张表没有会初始化一张
|
|
//大数据写入+表不存在会建表
|
//自动找表大数据写入
|
return db.Fastest<Entity.ScheduleCombine>().SplitTable().BulkCopy(list) > 0;
|
}
|
}
|
|
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public async Task<bool> BulkInserts_Create_Async(string runFlag, List<Entity.ScheduleCombine> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = Connection)
|
{
|
var exist_sql = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}' ;";
|
if (db.Ado.GetInt(exist_sql) < 1)
|
{
|
var sql_create_table = $"CREATE TABLE {tableName} {_createTable}";
|
if (db.Ado.ExecuteCommand(sql_create_table) < 1)
|
{
|
return false;
|
}
|
}
|
return await db.Fastest<Entity.ScheduleCombine>().AS(tableName).BulkCopyAsync(list) > 0;
|
}
|
}
|
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_Create(string runFlag, List<Entity.ScheduleCombine> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = Connection)
|
{
|
var exist_sql = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
if (db.Ado.ExecuteCommand(exist_sql) < 1)
|
{
|
var sql_create_table = $"CREATE TABLE {tableName} {_createTable}";
|
db.Ado.ExecuteCommand(sql_create_table);
|
}
|
//大数据写入
|
return db.Fastest<Entity.ScheduleCombine>().AS(tableName).BulkCopy(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_NativeSql(string runFlag, List<Entity.ScheduleCombine> list)
|
{
|
var tableName = GetTableName(runFlag);
|
var connect = ConfigHelper.ConnectionString;
|
SQLiteConnection connection = new SQLiteConnection(connect);//连接对象初始化
|
connection.Open();//打开连接
|
|
SQLiteCommand command = new SQLiteCommand(connection);//命令对象初始化
|
command.CommandText = $"VACUUM;";//执行VACUUM命令收缩数据库
|
command.ExecuteNonQuery();
|
|
SQLiteTransaction transaction = connection.BeginTransaction();//开始事务
|
var sql_exist_table = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
command.CommandText = sql_exist_table;
|
if (command.ExecuteNonQuery() < 1)//没有表就创建表
|
{
|
var sql_create_table = $"CREATE TABLE {tableName} (\r\n ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n RunFlag VARCHAR (255),\r\n RunCount INTEGER,\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n CurveQH VARCHAR (255),\r\n CurveQP VARCHAR (255),\r\n AnaStatus BIT\r\n);";
|
command.CommandText = sql_create_table;
|
command.ExecuteNonQuery();
|
}
|
|
var group = list.GroupBy(x => x.ID).Where(x => x.Count() > 1).Count();
|
|
for (int i = 0; i < list.Count; i++) //[---使用事务---]执行INSERT命令
|
{
|
var item = list[i];
|
var sql_insert = $"INSERT INTO {tableName}(ID, RunFlag, RunCount, Pump1, Pump2, Pump3, CurveQH, CurveQP, AnaStatus) " +
|
$"VALUES ('{item.ID}','{item.RunFlag}','{item.RunCount}','{item.Pump1}','{item.Pump2}','{item.Pump3}','{item.CurveQH}','{item.CurveQP}','{item.AnaStatus}')";
|
command.CommandText = sql_insert;
|
command.ExecuteNonQuery();
|
}
|
// var result = command.ExecuteScalar();
|
transaction.Commit();//提交事务
|
connection.Close();//关闭连接
|
return true;
|
}
|
|
|
/// <summary>
|
/// 设置已分析状态
|
/// </summary>
|
public bool SetAnaStatus(string tableName, long id)
|
{
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"update {tableName} set AnaStatus=True where ID={id}";
|
var bol = db.Ado.ExecuteCommand(sql) > 0;
|
return bol;
|
}
|
}
|
|
|
/// <summary>
|
/// 设置已分析状态
|
/// </summary>
|
public bool SetAnaStatus(string tableName, List<long> ids)
|
{
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"update {tableName} set AnaStatus=True where ID in(@ids)";
|
var obj = new { ids = ids.ToArray() };
|
var bol = db.Ado.ExecuteCommand(sql, obj) > 0;
|
return bol;
|
}
|
}
|
|
|
/// <summary>
|
/// 判断数据库是否存在表
|
/// </summary>
|
public bool ExistTable(string runFlag)
|
{
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"select count(*) from sqlite_master where type = 'table' and name = '{GetTableName(runFlag)}';";
|
var bol = db.Ado.ExecuteCommand(sql) > 0;
|
return bol;
|
}
|
}
|
|
/// <summary>
|
/// 获取全部表名
|
/// </summary>
|
public bool DeleteAllTable()
|
{
|
var tables = GetAllTableName();
|
if (tables == null || !tables.Any())
|
return false;
|
|
using (SqlSugarClient db = Connection)
|
{
|
db.BeginTran();
|
|
foreach (var table in tables)
|
{
|
var sql = $"drop table {table};";
|
db.Ado.ExecuteCommand(sql);
|
}
|
db.CommitTran();
|
}
|
return true;
|
}
|
|
|
}
|
}
|
