/// <summary>
/// Devuelve el último Id insertado en la base de datos. El campo en la base de datos debe llamarse Id.
/// </summary>
/// <returns></returns>
public virtual DataRow Last()
{
string sql = "SELECT * FROM " + this.tablename + " Order by ";
if (this.idFormat.Count == 1)
{
sql += this.idFormat [0];
}
else
{
for (int i = 0; i < idFormat.Count; i++)
{
if (i == idFormat.Count - 1)
{
sql += this.idFormat [i];
}
else
{
sql += this.idFormat [i] + ", ";
}
}
}
sql += " desc limit 1";
return(ExecuteQuery(sql).Rows[0]);
}
/// <summary>
/// Continues the.
/// </summary>
/// <returns>An IQuery.</returns>
public static IEnumerable <ISql> Continue(this IEnumerable <ISql> sql, ISql desSql)
{
return(new List <ISql>(sql)
{
desSql
});
}
private void FillTable(ISql sql, TablePrescription tablePrescription)
{
_Owner._Logger.LogInformation(Messages.LOG_INF_STARTED_1, nameof(FillTable), tablePrescription.TableDescription.Name);
var columns = tablePrescription.ColumnPrescriptions.Select(_ => _.ColumnDescription.Name).ToArray();
var tableName = tablePrescription.TableName();
using var recordGenerator = _Owner._RecordGeneratorFactory.Create(tablePrescription, tablePrescription.Rows);
using var connection = sql.CreateConnection();
connection.Open();
using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)
{
BatchSize = BULKCOPY_BATCHSIZE,
BulkCopyTimeout = 0,
DestinationTableName = tableName,
EnableStreaming = true,
};
// skipping DF1, DF2.
for (var i = 0; i < recordGenerator.FieldCount; i++)
{
_ = bulkCopy.ColumnMappings.Add(i, i + 2);
}
bulkCopy.WriteToServer(recordGenerator);
_Owner._Logger.LogInformation(Messages.LOG_INF_FINISHED_1, nameof(FillTable), tablePrescription.TableDescription.Name);
}
private void setFailConn()
{
ISql conn = fooCon;
FactorySql factory = new FactorySql();
factory.SetManager(conn);
}
/// <summary>
/// Generate the data.
/// </summary>
/// <remarks>
/// 1. Add columns @DF1 and @DF2 to the table.
/// @DF1 is a primary key.
/// @DF2 contains the same values as @DF1, but scrambled.
/// 2. For each primary key relationship, update the fk by binding @DF2 of the parent table to the @DF1 of the referenced table.
/// 3. Drop the primary keys.
/// </remarks>
public ISql Generate()
{
ISql sql = null;
_Owner._Logger.LogInformation(Messages.LOG_INF_STARTED, nameof(Generate));
try
{
sql = _Owner._SqlFactory.CreateTemporaryDatabase(DEFAULT_DATABASENAME);
var tablePrescriptions = _Owner._Project.Prescriptor.TablePrescriptions
.AsParallel()
.WithDegreeOfParallelism(2);
var tableDescriptions = tablePrescriptions.Select(t => t.TableDescription);
var foreignKeyDescriptions = tableDescriptions.SelectMany(tableDescription => tableDescription.ForeignKeyDescriptions);
tablePrescriptions.ForAll(_ => CreateTable(sql, _));
tablePrescriptions.ForAll(_ => FillTable(sql, _));
tableDescriptions.ForAll(_ => FillAuxiliaryColumns(sql, _));
foreignKeyDescriptions.ForAll(_ => UpdateForeignKeys(sql, _));
DropAuxiliaryColumns(sql);
return(sql);
}
catch (Exception exception)
{
_Owner._Logger.LogError(exception, Messages.LOG_ERR_FAILED, nameof(Generate));
sql?.Dispose();
throw;
}
finally
{
_Owner._Logger.LogInformation(Messages.LOG_INF_FINISHED, nameof(Generate));
}
}
/// <summary>
/// Initializes a new instance of the <see cref="IfSql"/> class.
/// </summary>
/// <param name="thenSql">The then sql.</param>
/// <param name="conditionSql">The condition sql.</param>
/// <param name="elseSql">The else sql.</param>
/// <param name="database">The database.</param>
public IfSql(ISql thenSql, ISql conditionSql, ISql elseSql, string database = "")
{
ThenSql = thenSql;
ConditionSql = conditionSql;
ElseSql = elseSql;
Database = database;
}
/// <summary>
/// Builds the sql result.
/// </summary>
/// <param name="sql">The sql.</param>
/// <returns>An ISqlResult.</returns>
public ISqlResult BuildSqlResult(ISql sql)
{
return(sql switch
{
IQuery query => BuildQuery(query),
IfSql ifSql => BuilderIfSql(ifSql),
StringSql stringSql => BuilderStringSql(stringSql),
ExistSql existSql => BuilderExist(existSql),
CountDatabaseSql countDatabaseSql => BuildCountForDatabase(countDatabaseSql),
DeleteDatabaseSql deleteDatabaseSql => BuildDeleteDatabase(deleteDatabaseSql),
AllTableNamesSql allTableNamesSql => BuildAllTableName(allTableNamesSql),
CreateDatabaseSql createDatabaseSql => BuildCreateDatabase(createDatabaseSql),
AllDatabaseSql allDatabaseSql => BuildAllDatabase(allDatabaseSql),
CreateTableSql createTableSql => BuildCreateTable(createTableSql),
CountTableSql countTableSql => BuildTableCount(countTableSql),
UpdateTableNameSql updateTableNameSql => BuildUpdateTableName(updateTableNameSql),
DeleteTableSql deleteTableSql => BuildDeleteTable(deleteTableSql),
ColumsNamesSql columsNamesSql => BuildColumnNames(columsNamesSql),
CountColumsSql countColumsSql => BuildColumnsCount(countColumsSql),
RenameColumnSql renameColumnSql => BuildRenameColumnsName(renameColumnSql),
UpdateColumnsSql updateColumnsSql => BuildUpdateColumns(updateColumnsSql),
AddColumnsSql addColumnsSql => BuildAddColumns(addColumnsSql),
DeleteColumsSql deleteColumsSql => BuildDeleteColumn(deleteColumsSql),
CreateIndexSql createIndexSql => BuildCreateIndex(createIndexSql),
DeleteIndexSql deleteIndexSql => BuildDeleteIndex(deleteIndexSql),
CountIndexSql countIndexSql => BuildIndexCount(countIndexSql),
RenameIndexSql renameIndexSql => RenameIndexSql(renameIndexSql),
DeleteSql deleteSql => BuildDeleteSql(deleteSql),
InsertSql insertSql => BuildInsertSql(insertSql),
UpdateSql updateSql => BuildUpdateSql(updateSql),
_ => new ParmsSqlResult()
});
/// <summary>
/// 获取SQL语句
/// </summary>
public static string GetSql(ISql isql, string key, params object[] paramValues)
{
if (_Ssbs.ContainsKey(key) == false)
{
throw new Exception(string.Format("SQL语句key异常.找不到key:{0}", key));
}
return(_Ssbs[key].ToString(isql, paramValues));
}
private static void FillAuxiliaryColumns(ISql sql, TableDescription tableDescription)
{
using var connection = sql.CreateConnection();
connection.Open();
var tableName = tableDescription.TableName();
var command = "WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS ROWNUMBER, [@DF1] FROM {0}) UPDATE T2 SET T2.[@DF2] = T1.[@DF1] FROM {0} T2, CTE T1 WHERE T1.ROWNUMBER = T2.[@DF1]".FormatInvariant(tableName);
sql.NonQuery(connection, command);
}
private void DoSetSql(ISql sql)
{
if (sql.SqlString != null)
{
this.SqlString = sql.SqlString;
}
if (sql.Params != null)
{
this.Params = sql.Params;
}
}
public static bool AppendIfNotNull(this ISql sql, string txt, Parameter para)
{
var p = para.Value;
if (p != null && (!string.IsNullOrWhiteSpace(p as string) || !(p is string)))
{
sql.Text += " " + txt;
sql.Parameters.Add(para);
return(true);
}
return(false);
}
protected TestBase()
{
_configuration = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("testsettings.json")
.Build();
var redisConnection = new RedisConnection(_configuration["CacheConfig:RedisServer"]).Multiplexer.GetDatabase();
var serializer = new JsonNetSerializer();
_cache = new Redis(redisConnection, serializer, Convert.ToInt32(_configuration["CacheConfig:CacheExpiryTime"]));
_sql = new Sql(_cache, new SqlConnection(_configuration["DataConfig:XelloDbServer"]));
}
private void connect()
{
if (sql == null)
{
sql = FactorySql.Create(sqlprovider);
}
if (password != string.Empty && !passEstablished)
{
sqlConnectionString += password;
passEstablished = true;
}
conn = sql.Connection(sqlConnectionString);
conn.Open();
}
private static void UpdateForeignKeys(ISql sql, ForeignKeyDescription foreignKeyDescription)
{
var parentTableName = foreignKeyDescription.Parent.TableName();
var referencedTableName = foreignKeyDescription.Referenced.TableName();
var columnRelationshipDescriptions = foreignKeyDescription.ColumnRelationshipDescriptions;
var referencedColumnNames = columnRelationshipDescriptions.Select(_ => _.Referenced.Name);
var parentColumnNames = columnRelationshipDescriptions.Select(_ => _.Parent.Name);
var updatePairs = parentColumnNames.Zip(referencedColumnNames, (p, r) => $"T1.{p} = T2.{r}");
var sb = new StringBuilder("UPDATE T1 SET ")
.AppendJoin(", ", updatePairs)
.AppendFormatInvariant(" FROM {0} T1, {1} T2 WHERE T2.[@DF1] = T1.[@DF2];", parentTableName, referencedTableName)
.ToString();
sql.NonQuery(sb);
}
private static void DropAuxiliaryColumns(ISql sql)
{
using var connection = sql.CreateConnection();
connection.Open();
var server = new Server(new ServerConnection(connection));
foreach (Table table in server.Databases[DEFAULT_DATABASENAME].Tables)
{
var indexes = table.Indexes.Cast <Index>();
var columns = table.Columns.Cast <Column>();
indexes.Single().Drop();
columns.Single(c => c.Name == "@DF1").Drop();
columns.Single(c => c.Name == "@DF2").Drop();
}
}
/// <summary>
/// 输出SQL语句
/// 1.各sql参数对应实例化后拼起来就构成SqlString
/// </summary>
/// <param name="paramValues">第一个参数值必须是入口参数类对象,如果入口参数类为null,第一个参数填null.标准参数编号从1开始到9</param>
/// <returns></returns>
public string ToString(ISql isql, params object[] paramValues)
{
StringBuilder sql = new StringBuilder();
object paramValue = null;
// 1.标准参数(mSqlParams[i].ParamSeq > 0),参数值按顺序
// 2.属性参数(mSqlParams[i].ParamSeq == 0),参数值按属性名称
// 3.末尾参数(mSqlParams[i].ParamSeq == -1),无参数
// 4.条件参数处理
// 如果不是空参数值，需要将参数值替换参数sql字段的参数占位符{0}
// 如果是空参数，则不作处理
for (int i = 0; i < mSqlParams.Count; i++)
{
if (mSqlParams[i].ParamSeq != -1)
{
if (mSqlParams[i].ParamSeq == 0)
{
paramValue = ReflectUtil.GetPropertyValue(paramValues[0], mSqlParams[i].ParamName);
}
else //if (mSqlParams[i].ParamSeq > 0)
{
paramValue = paramValues[mSqlParams[i].ParamSeq];
}
if (mSqlParams[i].IsConditionParam == true)
{
if (DbUtil.isnull(paramValue) == false)
{
sql.AppendFormat(mSqlParams[i].ParamSql, isql.ToString(paramValue));
}
}
else
{
sql.Append(mSqlParams[i].ParamSql).Append(isql.ToString(paramValue));
}
}
else
{
sql.Append(mSqlParams[i].ParamSql);
}
}
// 排序
sql.Append(mOrderBy);
return(sql.ToString());
}
/// <summary>
/// Conecta a la base de datos mediante una única conexión.
/// </summary>
/// <param name="connStringName">Nombre de la connectionString que está definida en el archivo de configuración de la aplicación.</param>
static public void Connect(string connStringName)
{
if (uniqueconn != null)
{
string sqlConnString = ConfigurationManager.ConnectionStrings [connStringName].ConnectionString;
string sqlProvider = ConfigurationManager.ConnectionStrings [connStringName].ProviderName;
ISql consql = FactorySql.Create(sqlProvider);
if (password != string.Empty)
{
sqlConnString += password;
}
uniqueconn = consql.Connection(sqlConnString);
uniqueconn.Open();
}
}
/// <summary>
/// Prepares the instance ready for use.
/// </summary>
public bool Load()
{
//Find the database type
var fmt = "mysql".ToLower (); //TODO: properties file
_database = GetFormat (fmt);
if (null == _database)
{
Tools.WriteLine ("Cannot find database format: " + fmt);
return false;
}
//Get a connection
if (!_database.OpenConnection (_host, _port))
{
Tools.WriteLine ("Failed to open a connection to the permission database");
return false;
}
return true;
}
private void InitBD()
{
string connString = ConfigurationManager.ConnectionStrings["GebatDataConnectionString"].ConnectionString;
connString += "Password=root";
string provider = ConfigurationManager.ConnectionStrings ["GebatDataConnectionString"].ProviderName;
ISql manager = FactorySql.Create(provider);
FileInfo file = new FileInfo(scriptfilename);
StreamReader lector = file.OpenText();
string script = lector.ReadToEnd();
lector.Close();
DbConnection conn = manager.Connection(connString);
conn.Open();
DbCommand comando = manager.Command(script, conn);
comando.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// Prepares the instance ready for use.
/// </summary>
public bool Load()
{
//Find the database type
var fmt = "mysql".ToLower(); //TODO: properties file
_database = GetFormat(fmt);
if (null == _database)
{
Tools.WriteLine("Cannot find database format: " + fmt);
return(false);
}
//Get a connection
if (!_database.OpenConnection(_host, _port))
{
Tools.WriteLine("Failed to open a connection to the permission database");
return(false);
}
return(true);
}
private static void WriteInsertions(Stream stream, ISql sql, TableDescription[] tableDescriptions)
{
using var connection = sql.CreateConnection();
var server = new Server(new ServerConnection(connection));
var database = server.Databases[DEFAULT_DATABASENAME];
foreach (var tableDescription in tableDescriptions)
{
bool Match(Table table) => table.Schema == tableDescription.Schema &&
table.Name == tableDescription.Name;
var table = database
.Tables
.Cast <Table>()
.First(Match);
var tableName = tableDescription.TableName();
stream.WriteLine("SET IDENTITY_INSERT {0} ON;".FormatInvariant(tableName));
WriteInsertions(stream, table);
stream.WriteLine("SET IDENTITY_INSERT {0} OFF;".FormatInvariant(tableName));
}
}
public void ChangeResults(string partyAbbreviation, int votes, ISql sql)
{
int totalVotes = 0;
foreach (ElectionResult result in results)
{
if (result.Party.Abbreviation == partyAbbreviation)
{
result.Votes = votes;
}
totalVotes = totalVotes + result.Votes;
}
foreach (ElectionResult result in results)
{
result.Percentage = ((double)result.Votes / (double)totalVotes);
result.Percentage = Math.Round(result.Percentage, 4, MidpointRounding.AwayFromZero);
result.Percentage = result.Percentage * 100;
result.Seats = (Int32)(Math.Round((((double)result.Votes / (double)totalVotes) * Seats), 0, MidpointRounding.AwayFromZero));
sql.UpdateResult(result);
}
}
static public bool AttemptConnection(string connStringName)
{
try
{
string trysqlConnectionString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
string trysqlprovider = ConfigurationManager.ConnectionStrings [connStringName].ProviderName;
ISql tryisql = FactorySql.Create(trysqlprovider);
if (password != string.Empty)
{
trysqlConnectionString += password;
}
DbConnection tryconn = tryisql.Connection(trysqlConnectionString);
tryconn.Open();
tryconn.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public HarpSynchronizer(ISql sql, StringBuilder trace)
{
this.sql = sql;
this.trace = trace;
}
public SysButtonRepository(ISql sql)
: base(sql)
{
}
public OpStraitStudentInfoQuery(ISql TheISql)
{
SetISql = TheISql;
BindDBName = "TjMedical";
}
public void SetManager(ISql mgr)
{
manager = mgr;
}
public void ResetManager()
{
manager = null;
}
/// <summary>
/// Devuelve el último Id insertado en la base de datos. El campo en la base de datos debe llamarse Id.
/// </summary>
/// <returns></returns>
public virtual DataRow Last()
{
string sql = "SELECT * FROM " + this.tablename + " Order by ";
if (this.idFormat.Count == 1)
{
sql += this.idFormat [0];
}
else
{
for (int i = 0; i < idFormat.Count; i++)
{
if (i == idFormat.Count - 1)
{
sql += this.idFormat [i];
}
else
{
sql += this.idFormat [i] + ", ";
}
}
}
sql += " desc limit 1";
return ExecuteQuery(sql).Rows[0];
}
public SysMenuRepository(ISql sql)
: base(sql)
{
}
public OpLoginQuery(String DBName, ISql TheISql)
{
SetISql = TheISql;
BindDBName = DBName;
}
public OpStudentMngExec(ISql TheISql)
{
SetISql = TheISql;
BindDBName = "TjMedical";
}
public GlobalSettingRepository(ISql sql) : base(sql)
{
_sql = sql;
}
/// <summary>
/// Initializes a new instance of the <see cref="ExistSql"/> class.
/// </summary>
/// <param name="isNot">If true, is not.</param>
/// <param name="subSql">The sub sql.</param>
/// <param name="database">The database.</param>
public ExistSql(ISql subSql, bool isNot = false, string database = "")
{
IsNot = isNot;
SubSql = subSql;
Database = database;
}
private void connect()
{
if (sql == null)
{
sql = FactorySql.Create (sqlprovider);
}
if (password != string.Empty && !passEstablished)
{
sqlConnectionString += password;
passEstablished = true;
}
conn = sql.Connection (sqlConnectionString);
conn.Open();
}