using SqlSugar;
|
using System.Collections.Generic;
|
using System.Linq;
|
|
namespace IStation.DAL
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public partial class AnalysisParameter
|
{
|
/// <summary>
|
///
|
/// </summary>
|
public ConnectionConfig ConnectionConfig
|
{
|
get
|
{
|
var dbName = "analysis.db";
|
var filePath = $"{FileHelper.GetDataFolder()}\\{dbName}";
|
var config = new ConnectionConfig
|
{
|
|
DbType = SqlSugar.DbType.Sqlite,//数据库类型
|
ConnectionString = $"DataSource={filePath}",
|
IsAutoCloseConnection = true,//是否自动关闭
|
MoreSettings = new ConnMoreSettings()
|
{
|
//PgSqlIsAutoToLower = false //数据库存在大写字段的 ，需要把这个设为false ，并且实体和字段名称要一样
|
},
|
AopEvents = new AopEvents
|
{
|
OnLogExecuting = (sql, p) =>
|
{
|
var sqlString = UtilMethods.GetNativeSql(sql, p);
|
}
|
},
|
ConfigureExternalServices = new ConfigureExternalServices()
|
{
|
EntityService = (property, column) =>
|
{
|
//除主键外都可空
|
if (!column.IsPrimarykey)
|
{
|
column.IsNullable = true;
|
}
|
}
|
}
|
};
|
return config;
|
}
|
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
private string TableNamePrefix
|
{
|
get { return "AnalysisParameter_"; }
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="runFlag"></param>
|
/// <returns></returns>
|
public string GetTableName(string runFlag)
|
{
|
return $"{this.TableNamePrefix}{runFlag}";
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="tableName"></param>
|
/// <returns></returns>
|
private string GetCreateTableSql(string tableName)
|
{
|
var sql = $"CREATE TABLE {tableName} (ID BIGINT NOT NULL\r\n PRIMARY KEY,\r\n PumpID BIGINT,\r\n Hz REAL,\r\n Head REAL,\r\n PressureDiff REAL,\r\n Flow REAL,\r\n Power REAL,\r\n WP REAL,\r\n UWP REAL\r\n);";
|
return sql;
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="tableName"></param>
|
/// <returns></returns>
|
private string GetExistTableSql(string tableName)
|
{
|
var sql = $"select count(*) from sqlite_master where type = 'table' and name = '{tableName}';";
|
return sql;
|
}
|
|
|
/// <summary>
|
/// 获取全部表名
|
/// </summary>
|
public List<string> GetAllTableName()
|
{
|
using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
|
{
|
var list = new List<string>();
|
var sql = $"select name from sqlite_master where type='table' and name like '%{this.TableNamePrefix}%';";
|
using (var reader = db.Ado.GetDataReader(sql))
|
{
|
while (reader.Read())
|
{
|
var tableName = reader["name"].ToString();
|
list.Add(tableName);
|
}
|
}
|
return list;
|
}
|
}
|
|
/// <summary>
|
/// 获取全部表
|
/// </summary>
|
public Dictionary<string, List<Dto.AnalysisParameter>> GetAllTable()
|
{
|
|
|
var tableNames = GetAllTableName();
|
if (tableNames == null || !tableNames.Any())
|
return default;
|
|
var dict = new Dictionary<string, List<Dto.AnalysisParameter>>();
|
using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
|
{
|
foreach (var tableName in tableNames)
|
{
|
var sql = $"select * from {tableName}";
|
var list = db.Queryable<Dto.AnalysisParameter>().AS(tableName).ToList();
|
if (list == null || !list.Any())
|
continue;
|
dict.Add(tableName, list);
|
}
|
return dict;
|
}
|
}
|
|
|
/// <summary>
|
/// 大批量插入(表不存在就新建)
|
/// </summary>
|
public bool BulkInsertsEx(string runFlag, List<Dto.AnalysisParameter> list)
|
{
|
if (list == null || list.Count < 1)
|
return default;
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
|
{
|
var exist_sql = GetExistTableSql(tableName);
|
if (db.Ado.GetInt(exist_sql) < 1)
|
{
|
var sql_create_table = GetCreateTableSql(tableName);
|
db.Ado.ExecuteCommand(sql_create_table);
|
}
|
//大数据写入
|
return db.Fastest<Dto.AnalysisParameter>().AS(tableName).BulkCopy(list) > 0;
|
}
|
}
|
|
/// <summary>
|
/// 判断数据库是否存在表
|
/// </summary>
|
public bool ExistTable(string runFlag)
|
{
|
using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
|
{
|
var tableName = GetTableName(runFlag);
|
var sql = GetExistTableSql(tableName);
|
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 = new SqlSugarClient(ConnectionConfig))
|
{
|
db.BeginTran();
|
foreach (var table in tables)
|
{
|
var sql = $"drop table {table};";
|
db.Ado.ExecuteCommand(sql);
|
}
|
db.CommitTran();
|
}
|
return true;
|
}
|
|
|
/// <summary>
|
/// 查询
|
/// </summary>
|
public List<Dto.AnalysisParameter> GetList(string runFlag, double targetHead)
|
{
|
var tableName = GetTableName(runFlag);
|
using (SqlSugarClient db = new SqlSugarClient(ConnectionConfig))
|
{
|
var sql = GetExistTableSql(tableName);
|
if (db.Ado.GetInt(sql) < 1)
|
{
|
return default;
|
}
|
var list = db.Queryable<Dto.AnalysisParameter>().AS(tableName)
|
.Where(x => x.Head == targetHead)
|
.OrderBy(x => x.Power)
|
.ToList();
|
return list;
|
}
|
}
|
}
|
}
|
