示例#1
0
 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>());
     }
 }
示例#2
0
        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();
            }
示例#4
0
        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");
        }
示例#5
0
        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);
        }
示例#6
0
        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"));
        }
示例#7
0
 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());
     }
 }
示例#8
0
        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();
            }
        }
示例#9
0
        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");
        }
示例#10
0
        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);
                }
            }
示例#12
0
        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
    )");
        }
示例#14
0
        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
    )"));
        }
示例#15
0
        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();
        }
示例#16
0
 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));
     }
 }
示例#17
0
 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));
 }
示例#18
0
        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;
                }
            }
示例#21
0
        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);
        }
示例#22
0
        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");
        }
示例#23
0
        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);
        }
示例#24
0
        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);
        }
示例#25
0
        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]");
        }
示例#26
0
        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));
        }
示例#27
0
        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]");
        }
示例#28
0
        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");
        }
示例#29
0
        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));
        }
示例#30
0
        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);
        }