namespace IStation.DAL
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public class ScheduleConclusion_bak
|
{
|
public SqlSugarClient Connection
|
{
|
get { return ConfigHelper.GetSqlSugarClient(); }
|
}
|
|
private readonly string _tableNamePrefix = "ScheduleConclusion_Cl_";
|
|
private string GetTableName(string runFlag)
|
{
|
return $"{_tableNamePrefix}{runFlag}";
|
}
|
|
#region Table
|
|
/// <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 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.GetInt(sql) > 0;
|
return bol;
|
}
|
}
|
|
/// <summary>
|
/// 数据库创建表
|
/// </summary>
|
public bool CreateTable(string runFlag)
|
{
|
using (SqlSugarClient db = Connection)
|
{
|
var tableName = GetTableName(runFlag);
|
var sql_exist = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
if (db.Ado.GetInt(sql_exist) > 0)
|
return true;
|
var sql_create_table = $"CREATE TABLE {tableName} (\r\n ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n ScheduleCombineID BIGINT,\r\n RunFlag VARCHAR (255),\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n Head REAL,\r\n Flow REAL,\r\n Power REAL,\r\n WP REAL,\r\n UWP REAL\r\n);";
|
var bol = db.Ado.ExecuteCommand(sql_create_table) > 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;
|
}
|
|
|
#endregion
|
|
#region BulkInserts
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts(string runFlag, List<Entity.ScheduleConclusion> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = Connection)
|
{
|
return db.Fastest<Entity.ScheduleConclusion>().AS(tableName).BulkCopy(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public async Task<bool> BulkInserts_Create_Async(string runFlag, List<Entity.ScheduleConclusion> 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} (\r\n ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n ScheduleCombineID BIGINT,\r\n RunFlag VARCHAR (255),\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n Head REAL,\r\n Flow REAL,\r\n Power REAL,\r\n WP REAL,\r\n UWP REAL\r\n);";
|
if (db.Ado.ExecuteCommand(sql_create_table) < 1)
|
{
|
return false;
|
}
|
}
|
return await db.Fastest<Entity.ScheduleConclusion>().AS(tableName).BulkCopyAsync(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_SplitTable(List<Entity.ScheduleConclusion> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
using (SqlSugarClient db = Connection)
|
{
|
///自己来制定定义的规则
|
db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService = new Entity.ScheduleConclusionSubTableService();
|
db.CodeFirst
|
.SplitTables()//标识分表
|
.InitTables<Entity.ScheduleConclusion>(); //程序启动时加这一行,如果一张表没有会初始化一张
|
|
//大数据写入+表不存在会建表
|
//自动找表大数据写入
|
return db.Fastest<Entity.ScheduleConclusion>().SplitTable().BulkCopy(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_Create(string runFlag, List<Entity.ScheduleConclusion> 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} (\r\n ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n ScheduleCombineID BIGINT,\r\n RunFlag VARCHAR (255),\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n Head REAL,\r\n Flow REAL,\r\n Power REAL,\r\n WP REAL,\r\n UWP REAL\r\n);";
|
db.Ado.ExecuteCommand(sql_create_table);
|
}
|
//大数据写入
|
return db.Fastest<Entity.ScheduleConclusion>().AS(tableName).BulkCopy(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 大批量插入
|
/// </summary>
|
public bool BulkInserts_NativeSql(string runFlag, List<Entity.ScheduleConclusion> 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 ScheduleCombineID BIGINT,\r\n RunFlag VARCHAR (255),\r\n Pump1 REAL,\r\n Pump2 REAL,\r\n Pump3 REAL,\r\n Head REAL,\r\n Flow REAL,\r\n Power REAL,\r\n WP REAL,\r\n UWP REAL\r\n);\r\n";
|
command.CommandText = sql_create_table;
|
command.ExecuteNonQuery();
|
}
|
|
for (int i = 0; i < list.Count; i++) //[---使用事务---]执行INSERT命令
|
{
|
var sql_insert = $"INSERT INTO {tableName}(ID, ScheduleCombineID, RunFlag, Pump1, Pump2, Pump3, Head, Flow, Power, WP, UWP) " +
|
$"VALUES ('{list[i].ID}','{list[i].ScheduleCombineID}','{list[i].RunFlag}','{list[i].Pump1}','{list[i].Pump2}','{list[i].Pump3}','{list[i].Head}','{list[i].Flow}','{list[i].Power}','{list[i].WP}','{list[i].UWP}')";
|
command.CommandText = sql_insert;
|
command.ExecuteNonQuery();
|
}
|
command.ExecuteScalar();
|
transaction.Commit();//提交事务
|
connection.Close();//关闭连接
|
return true;
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
/// 查询
|
/// </summary>
|
public List<Entity.ScheduleConclusion> GetList(string runFlag, double targetHead)
|
{
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
if (db.Ado.GetInt(sql) < 1)
|
{
|
return default;
|
}
|
var list = db.Queryable<Entity.ScheduleConclusion>().AS(tableName)
|
.Where(x => x.Head == targetHead)
|
.OrderBy(x => x.Power)
|
.ToList();
|
return list;
|
}
|
|
}
|
|
/// <summary>
|
/// 查询
|
/// </summary>
|
public List<Entity.ScheduleConclusion> GetList(string runFlag, double targetFlow, double targetHead, int takeCount)
|
{
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = Connection)
|
{
|
var sql = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
if (db.Ado.GetInt(sql) < 1)
|
{
|
return default;
|
}
|
var list = db.Queryable<Entity.ScheduleConclusion>().AS(tableName)
|
.Where(x => x.Head >= targetHead && x.Head <= targetHead * 1.01 && x.Flow > targetFlow)
|
.OrderBy(x => x.Power)
|
.Take(takeCount)
|
.ToList();
|
return list;
|
}
|
|
}
|
|
}
|
}
|
