public List <IDbEntity> GetAllTables(DbSchema schema, IDbConnection conn = null)
{
using (var con = conn ?? GetConnection())
{
return(con.Query <DbEntity>(SqlStatements.GetAllTables(_dbType), new { DbName = con.Database, Schema = schema.SchemaName }).ToList <IDbEntity>());
}
}
public async Task Should_behave_the_same_if_no_tenant_and_database_provided()
{
TenantDatabaseProviderMock.Setup(p => p.GetDatabaseName(Schema, "TestEntities")).Returns((string)null !);
await ActDbContext.TestEntities.ToListAsync();
SqlStatements.Last().Should().Contain($"FROM [{Schema}].[TestEntities]");
}
protected override void UnsafeClear()
{
base.UnsafeClear();
SqlStatements.Clear();
Parameters.Clear();
}
public void When_cloning_Then_copies_stuff()
{
var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress")
.From("Users u");
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
u.ID, u.Name, u.EmailAddress
FROM
Users u");
var clone = statement.Clone();
clone.From("Teams t")
.Columns("u.IsCool")
.Where("u.Name = @Query")
.OrderBy("u.Name", false);
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
u.ID, u.Name, u.EmailAddress
FROM
Users u");
clone.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
u.ID, u.Name, u.EmailAddress, u.IsCool
FROM
Users u,
Teams t
WHERE
u.Name = @Query
ORDER BY
u.Name DESC");
}
public void When_cloning_with_all_properties_Then_copies_stuff()
{
var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress")
.From("Users u")
.Where("u.IsCool IS NULL")
.GroupBy("u.ID")
.OrderBy("u.Name")
.Limit(1).Offset(2);
const string resultStatement = @"SELECT
u.ID, u.Name, u.EmailAddress
FROM
Users u
WHERE
u.IsCool IS NULL
GROUP BY
u.ID
ORDER BY
u.Name ASC
LIMIT 1 OFFSET 2";
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(resultStatement);
var clone = statement.Clone();
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(resultStatement);
clone.ToSql().Should().BeEquivalentToIgnoringNewLines(resultStatement);
}
public void When_inserting_from_select_Then_builds_properly()
{
var select = SqlStatements.Select("Name", "EmailAddress")
.From("Users")
.InnerJoin("Teams", "Users.TeamID = Teams.ID")
.Where("Teams.IsOld = FALSE");
var statement = new InsertStatement(select)
.Into("Users");
var sql = statement.ToSql();
Assert.That(
sql,
SqlCompareConstraint.EqualTo(@"INSERT INTO Users
(
Name,
EmailAddress
)
SELECT
Name, EmailAddress
FROM
Users
INNER JOIN Teams ON Users.TeamID = Teams.ID
WHERE
Teams.IsOld = FALSE"));
}
public List <DbColumn> GetAllColumns(IDbEntity entity, IDbConnection conn = null)
{
using (var con = conn ?? GetConnection())
{
return(con.Query <DbColumn>(SqlStatements.GetAllColumns(_dbType), new { DbName = con.Database, Schema = entity.TableSchema, entity.TableName }).ToList());
}
}
public override void Drop(IStructureSchema structureSchema)
{
Ensure.That(structureSchema, "structureSchema").IsNotNull();
var modelInfo = GetModelTablesInfo(structureSchema);
var sqlDropTableFormat = SqlStatements.GetSql("DropTable");
using (var cmd = CreateCommand(string.Empty, new DacParameter(DbSchemaInfo.Parameters.EntityNameParamPrefix, structureSchema.Name)))
{
DropIndexesTables(cmd, modelInfo.Statuses.IndexesTableStatuses, modelInfo.Names.IndexesTableNames);
if (modelInfo.Statuses.UniquesTableExists)
{
cmd.CommandText = sqlDropTableFormat.Inject(structureSchema.GetUniquesTableName());
cmd.ExecuteNonQuery();
}
if (modelInfo.Statuses.SpatialTableExists)
{
cmd.CommandText = sqlDropTableFormat.Inject(structureSchema.GetStructureTableName());
cmd.ExecuteNonQuery();
}
if (modelInfo.Statuses.StructureTableExists)
{
cmd.CommandText = sqlDropTableFormat.Inject(structureSchema.GetStructureTableName());
cmd.ExecuteNonQuery();
}
cmd.CommandText = SqlStatements.GetSql("DeleteStructureFromSisoDbIdentitiesTable");
cmd.ExecuteNonQuery();
}
}
public void When_selecting_with_no_table_by_Then_doesnt_output_from_clause()
{
var statement = SqlStatements.Select("(SELECT COUNT(*) FROM Users) AS UserCount");
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
(SELECT COUNT(*) FROM Users) AS UserCount");
}
public void When_cloning_with_all_properties_Then_copies_stuff()
{
var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress")
.From("Users u")
.Where("u.IsCool IS NULL")
.GroupBy("u.ID")
.OrderBy("u.Name")
.Limit(1).Offset(2);
const string resultStatement = @"SELECT
u.ID, u.Name, u.EmailAddress
FROM
Users u
WHERE
u.IsCool IS NULL
GROUP BY
u.ID
ORDER BY
u.Name ASC
LIMIT 1 OFFSET 2";
Assert.That(
statement.ToSql(),
SqlCompareConstraint.EqualTo(resultStatement));
var clone = statement.Clone();
Assert.That(
statement.ToSql(),
SqlCompareConstraint.EqualTo(resultStatement));
Assert.That(
clone.ToSql(),
SqlCompareConstraint.EqualTo(resultStatement));
}
protected override void UnsafeLog <TState>(
LogLevel logLevel, EventId eventId, string message, TState state, Exception exception)
{
if ((eventId.Id == RelationalEventId.CommandExecuted.Id ||
eventId.Id == RelationalEventId.CommandError.Id ||
eventId.Id == RelationalEventId.CommandExecuting.Id))
{
if (_shouldLogCommands)
{
base.UnsafeLog(logLevel, eventId, message, state, exception);
}
if (!IsRecordingSuspended &&
message != null &&
eventId.Id != RelationalEventId.CommandExecuting.Id)
{
var structure = (IReadOnlyList <KeyValuePair <string, object> >)state;
var parameters = structure.Where(i => i.Key == "parameters").Select(i => (string)i.Value).First();
var commandText = structure.Where(i => i.Key == "commandText").Select(i => (string)i.Value).First();
if (!string.IsNullOrWhiteSpace(parameters))
{
Parameters.Add(parameters);
parameters = parameters.Replace(", ", _eol) + _eol + _eol;
}
SqlStatements.Add(parameters + commandText);
}
}
else
{
base.UnsafeLog(logLevel, eventId, message, state, exception);
}
}
public override IEnumerable <string> GetJsonByIds(IEnumerable <IStructureId> ids, IStructureSchema structureSchema)
{
Ensure.That(structureSchema, "structureSchema").IsNotNull();
var sqlFormat = SqlStatements.GetSql("GetJsonByIds").Inject(structureSchema.GetStructureTableName(), "{0}");
using (var cmd = CreateCommand(string.Empty))
{
foreach (var batchedIds in ids.Batch <IStructureId, IDacParameter>(MaxBatchedIdsSize, (id, batchCount) => new DacParameter(string.Concat("id", batchCount), id.Value)))
{
cmd.Parameters.Clear();
Driver.AddCommandParametersTo(cmd, batchedIds);
var paramsString = string.Join(",", batchedIds.Select(p => p.Name));
cmd.CommandText = sqlFormat.Inject(paramsString);
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
reader.Close();
}
}
}
}
public void When_inserting_rows_Then_builds_properly()
{
var statement = SqlStatements.Insert().Columns(new[] { "Name", "EmailAddress" })
.Into("Users")
.Rows(3);
var sql = statement.ToSql();
sql.Should().BeEquivalentToIgnoringNewLines(@"INSERT INTO Users
(
Name,
EmailAddress
)
VALUES
(
@Name0,
@EmailAddress0
),
(
@Name1,
@EmailAddress1
),
(
@Name2,
@EmailAddress2
)");
}
public void When_inserting_rows_Then_builds_properly()
{
var statement = SqlStatements.Insert().Columns(new[] { "Name", "EmailAddress" })
.Into("Users")
.Rows(3);
var sql = statement.ToSql();
Assert.That(
sql,
SqlCompareConstraint.EqualTo(@"INSERT INTO Users
(
Name,
EmailAddress
)
VALUES
(
@Name0,
@EmailAddress0
),
(
@Name1,
@EmailAddress1
),
(
@Name2,
@EmailAddress2
)"));
}
static void Main1(string[] args)
{
Person p = new Person();
p.FirstName = "georg";
p.LastName = "schinnerl";
p.Id = 123;
SqlStatements sql = new SqlStatements();
string s = sql.Select(p);
Console.WriteLine("sql select for person : " + s);
Location l = new Location(34.12, 45.5, "WIen", "Niederösterreich");
string s1 = sql.Select(l);
Console.WriteLine("sql select for Location : " + s1);
Console.WriteLine("sql insert for Location : " + sql.Insert(l));
List <string> namespaces = new List <string>();
namespaces.Add("DaoThingi.DomainObjects");
namespaces.Add("DaoThingi.TestDI");
GrgContext grgContext = new GrgContext(namespaces);
Console.WriteLine("\n\n\n\nCOntext Data");
grgContext.ListBeans();
grgContext.ListInjectables();
grgContext.ListAutowire();
try
{
Car c = new Car("BMW", 2000);
c.Store();
}
catch (NullReferenceException e)
{
Console.WriteLine($"NUllreference Exception while calling c.Store() which was expected! continuing {e.Message}");
}
Console.WriteLine($"\n\n\n-------------------------------------------------------------------------------------------------");
Console.WriteLine($"Get a car object with DI of a an IStorage implementation");
Car c1 = (Car)grgContext.GetBean("Car");
c1.Name = "Mercedes";
c1.HorsePower = 12;
Console.WriteLine("car c1 = " + c1.ToString());
// Console.WriteLine($"calling c1.Store() -> this should work, because we've injected an object :-)");
c1.Store();
Console.WriteLine($"success c1.Store() - yeah!!! :-)");
Console.WriteLine($"-------------------------------------------------------------------------------------------------\n\n\n");
Console.ReadKey();
}
public async Task <T> GetById(params object[] keys)
{
using (var conn = ConnectionManager.GetConnection())
{
var query = SqlStatements.GetSelectByKeysStatement <T>(keys);
return(await conn.Connection.QueryFirstOrDefaultAsync <T>(query.BuildStatement(), query.Parameters));
}
}
public void ConvertToSqlTest()
{
Assert.AreEqual("True", SqlStatements.ConvertToSql(ColTypes.Boolean, true));
Assert.AreEqual(@"timestamp '2017-01-01 00:00:00'", SqlStatements.ConvertToSql(ColTypes.Date, new DateTime(2017, 1, 1)));
Assert.AreEqual("9", SqlStatements.ConvertToSql(ColTypes.Numeric, 9));
Assert.AreEqual(@"'ab'", SqlStatements.ConvertToSql(ColTypes.Text, "a'b"));
Assert.AreEqual("NULL", SqlStatements.ConvertToSql(ColTypes.Text, null));
}
public void When_selecting_with_no_table_by_Then_doesnt_output_from_clause()
{
var statement = SqlStatements.Select("(SELECT COUNT(*) FROM Users) AS UserCount");
Assert.That(
statement.ToSql(),
SqlCompareConstraint.EqualTo(@"SELECT
(SELECT COUNT(*) FROM Users) AS UserCount"));
}
/// <summary>
/// 创建数据库和表
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="sqlStatements">SQL 语句</param>
protected virtual void EnsureDatabaseAndTableCreated(IDbConnection conn,
SqlStatements sqlStatements)
{
if (!string.IsNullOrWhiteSpace(sqlStatements.CreateDatabaseSql))
{
conn.Execute(sqlStatements.CreateDatabaseSql);
}
conn.Execute(sqlStatements.CreateTableSql);
}
public void Clear()
{
lock (_sync) // Guard against tests with explicit concurrency
{
SqlStatements.Clear();
LogBuilder.Clear();
Parameters.Clear();
_cancellationTokenSource = null;
}
}
public void BuildInsertTest()
{
DefTblCustomers def = new DefTblCustomers();
Dictionary <ColDef, object> map = new Dictionary <ColDef, object>();
map.Add(def.Cid, 1);
map.Add(def.Company, "test");
String sql = SqlStatements.BuildInsert(def.TblName, map);
Assert.AreEqual(String.Format("INSERT INTO {0}({1},{2}) VALUES(1,'test');", def.TblName, def.Cid.Name, def.Company.Name), sql);
}
public void When_selecting_with_full_join_Then_works()
{
var statement = SqlStatements.Select("u.ID, t.ID")
.From("Users u")
.FullJoin(new TableClause("Teams t"), "u.TeamID = t.ID");
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
u.ID, t.ID
FROM
Users u
FULL JOIN Teams t ON u.TeamID = t.ID");
}
public void BuildUpdateTest()
{
DefTblCustomers def = new DefTblCustomers();
Dictionary <ColDef, object> map = new Dictionary <ColDef, object>();
map.Add(def.Cid, TestCustomer.cid);
map.Add(def.Company, TestCustomer.company);
map.Add(def.Address, TestCustomer.address);
String sql = SqlStatements.BuildUpdate(def.TblName, map, new Tuple <ColDef, object>(def.Cid, TestCustomer.cid));
Assert.AreEqual(String.Format("UPDATE {0} SET {1} = '{2}',{3} = '{4}' WHERE {5} = {6};", def.TblName, def.Company.Name, TestCustomer.company, def.Address.Name, TestCustomer.address, def.Cid.Name, TestCustomer.cid), sql);
}
public override long CheckOutAndGetNextIdentity(string entityName, int numOfIds)
{
Ensure.That(entityName, "entityName").IsNotNullOrWhiteSpace();
var nextId = ExecuteScalar <long>(SqlStatements.GetSql("Sys_Identities_GetNext"), new DacParameter(DbSchemaInfo.Parameters.EntityNameParamPrefix, entityName));
ExecuteNonQuery(SqlStatements.GetSql("Sys_Identities_Increase"),
new DacParameter(DbSchemaInfo.Parameters.EntityNameParamPrefix, entityName),
new DacParameter("numOfIds", numOfIds));
return(nextId);
}
public async Task Should_use_database_name_in_views()
{
_tenant = "1";
var database = ArrangeDbContext.Database.GetDbConnection().Database;
TenantDatabaseProviderMock.Setup(p => p.GetDatabaseName(Schema, "TestView"))
.Returns(database);
await ActDbContext.TestView.ToListAsync();
SqlStatements.Last().Should().Contain($"FROM [{database}].[{Schema}].[TestView]");
}
protected override void OnRenameStructureTable(string oldTableName, string newTableName)
{
var dropPkContraintSqlFormat = SqlStatements.GetSql("DropPkConstraint");
var addPkConstraintSqlFormat = SqlStatements.GetSql("AddPkConstraintAgainstStructureId");
ExecuteNonQuery(dropPkContraintSqlFormat.Inject(oldTableName));
ExecuteNonQuery("sp_rename @objname=@objname, @newname=@newname, @objtype=@objtype",
new DacParameter("objname", oldTableName),
new DacParameter("newname", newTableName),
new DacParameter("objtype", "OBJECT"));
ExecuteNonQuery(addPkConstraintSqlFormat.Inject(newTableName));
}
public async Task Should_behave_the_same_if_database_name_is_specified_explicitly()
{
_tenant = "1";
var database = ArrangeDbContext.Database.GetDbConnection().Database;
TenantDatabaseProviderMock.Setup(p => p.GetDatabaseName(Schema, "TestEntities"))
.Returns(database);
await ActDbContext.TestEntities.ToListAsync();
SqlStatements.Last().Should().Contain($"FROM [{database}].[{Schema}].[TestEntities]");
}
public void When_selecting_with_group_by_Then_works()
{
var statement = SqlStatements.Select("u.ID, COUNT(*)")
.From("Users u")
.GroupBy("u.ID");
statement.ToSql().Should().BeEquivalentToIgnoringNewLines(@"SELECT
u.ID, COUNT(*)
FROM
Users u
GROUP BY
u.ID");
}
protected override IDbQuery CreateSqlQuery(IQuery query)
{
var sqlExpression = SqlExpressionBuilder.Process(query);
var formatter = CreateSqlQueryFormatter(query, sqlExpression);
var parameters = GenerateParameters(query, sqlExpression);
if (query.HasNoDependencies())
{
return(new DbQuery(formatter.Format(SqlStatements.GetSql("QueryWithoutDependencies")), parameters, query.IsCacheable));
}
return(new DbQuery(formatter.Format(SqlStatements.GetSql("Query")), parameters, query.IsCacheable));
}
public IDataReader ExecuteQueryWithSqls(SqlStatements queries)
{
string Function_Name = "ExecuteQuery";
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Entered");
string query;
DatabaseSession session = m_sessionFactory.GetDatabaseSession();
bool bLocalSession = false;
IDataReader dr = null;
if (session != null)
{
try
{
if (!session.IsConnectionOpen())
{
session.OpenConnection();
bLocalSession = true;
}
IDbCommand cmd = session.CreateCommand();
if (session.GetDBType() == DBType.Oracle)
{
query = queries.OracleSql;
}
else
{
query = queries.MySqlStr;
}
LogHelper.Debug(CLASS_NAME, Function_Name, string.Format("Query = {0}", query));
cmd.CommandText = query;
dr = cmd.ExecuteReader();
// LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
// return dr;
}
catch (Exception localException)
{
LogHelper.Error(CLASS_NAME, Function_Name, localException.ToString());
//todo
// throw exception
}
}
/*if (bLocalSession)
* {
* session.CloseConnection();
* }*/
LogHelper.Trace(CLASS_NAME, Function_Name, "Function_Exited");
return(dr);
}
