Exemplo n.º 1
0
        public void TestCreateCommandPopulatesCommandParameters()
        {
            var conn  = new MockConnection();
            var query = new DbQueryBuilder("dbo.TableName", conn);

            query.Parameters.Add(new DbParameterValue()
            {
                ParameterName = "@Param1",
                DbType        = System.Data.DbType.Int32,
                Size          = 4,
                Value         = 123
            });

            var stmt = query.Render();

            Assert.AreEqual(1, stmt.Parameters.Count);
            Assert.AreEqual(System.Data.DbType.Int32, stmt.Parameters.First().DbType);
            Assert.AreEqual(4, stmt.Parameters.First().Size);
            Assert.AreEqual(123, stmt.Parameters.First().Value);

            var cmd = stmt.CreateCommand(conn);

            Assert.AreEqual(conn, cmd.Connection);
            Assert.AreEqual(1, cmd.Parameters.Count);
            Assert.AreEqual(System.Data.DbType.Int32, ((IDbDataParameter)cmd.Parameters[0]).DbType);
            Assert.AreEqual(4, ((IDbDataParameter)cmd.Parameters[0]).Size);
            Assert.AreEqual(123, ((IDbDataParameter)cmd.Parameters[0]).Value);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Loads Count of Records in the specifyed Table
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>Count</returns>
        public ulong LoadCount(string tableName)
        {
            string query  = new DbQueryBuilder(this).BuildSelectCount(tableName);
            object scalar = ExecuteScalar(query);

            return(Convert.ToUInt64(scalar));
        }
Exemplo n.º 3
0
        /// <summary>
        /// Sets the database query builder in the specified script variable set.
        /// </summary>
        /// <param name="scriptVariableSet">The script variable set to consider.</param>
        /// <param name="queryBuilder">The query builder to consider.</param>
        /// <returns></returns>
        public static IScriptVariableSet SetDbBuilder(
            this IScriptVariableSet scriptVariableSet,
            DbQueryBuilder queryBuilder)
        {
            scriptVariableSet?.SetValue(__DbBuilder, queryBuilder);

            return(scriptVariableSet);
        }
Exemplo n.º 4
0
        private static void GetSalesOrderDetails(Db db, DbQueryBuilder queryBuilder, SalesOrderDetail model)
        {
            SalesOrderDetail d;

            queryBuilder.From(db.SalesOrderDetail, out d)
            .AutoSelect()
            .OrderBy(d.SalesOrderDetailID);
        }
Exemplo n.º 5
0
        /// <summary>
        /// Creates database query for specified model.
        /// </summary>
        /// <typeparam name="T">Type of the model.</typeparam>
        /// <param name="_">The specified model.</param>
        /// <param name="initializer">The model initializer.</param>
        /// <param name="buildQuery">The query builder.</param>
        /// <returns>The created database query.</returns>
        public DbQuery <T> CreateQuery <T>(T _, Action <T> initializer, Action <DbQueryBuilder, T> buildQuery)
            where T : Model, new()
        {
            buildQuery.VerifyNotNull(nameof(buildQuery));

            var model = _ == null ? new T() : (_.DataSource == null ? _ : _.MakeCopy(false));

            model.Initialize(initializer);
            var builder = new DbQueryBuilder(model);

            buildQuery(builder, model);
            return(PerformCreateQuery(model, builder.BuildQueryStatement(null)));
        }
Exemplo n.º 6
0
        public void TestForkCreatesShallowClone()
        {
            var conn  = new MockConnection();
            var query = new DbQueryBuilder("dbo.TableName", conn);

            query.OrderBy.Add(new SqlOrderColumn()
            {
                ColumnName = "Foo"
            });
            query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("Blah")));
            query.AddFilter(new SqlNullFilter(query.Root, "Foo", false));
            query.SelectColumns.Add(new SqlColumn(query.Root, "Foo"));
            query.PagingOptions = new SqlPagingOptions()
            {
                Offset = 10,
                Limit  = 100
            };
            query.Parameters.Add(new DbParameterValue()
            {
                ParameterName = "@Param1",
                DbType        = System.Data.DbType.Int32,
                Size          = 4,
                Value         = 123
            });
            query.Distinct       = true;
            query.CommandTimeout = 123;

            var fork = query.Fork();

            query.OrderBy.Clear();
            query.AddFilter(new SqlNullFilter(query.Root, "Bar", false));
            query.SelectColumns.Clear();
            query.PagingOptions = null;
            query.Parameters.Clear();
            query.CommandTimeout = 999;
            query.Distinct       = false;
            query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("blah2")));

            Assert.AreEqual(1, fork.OrderBy.Count);
            Assert.AreEqual(1, fork.SelectColumns.Count);
            Assert.AreEqual(1, fork.Parameters.Count);
            Assert.AreEqual(10, fork.PagingOptions.Value.Offset);
            Assert.AreEqual(100, fork.PagingOptions.Value.Limit);
            Assert.AreEqual(true, fork.Distinct);
            Assert.AreEqual(123, fork.CommandTimeout);
        }
Exemplo n.º 7
0
        public void TestQueryBuilderRenderBatch()
        {
            var conn  = new MockConnection();
            var query = new DbQueryBuilder("SELECT * FROM dbo.TableName WHERE Foo=@Foo", conn);

            query.Parameters.Add(new DbParameterValue()
            {
                ParameterName = "@Foo",
                DbType        = System.Data.DbType.Int32,
                Size          = 4,
                Value         = 123
            });

            var stmt = new ISqlStatementElement[] { query }.RenderBatch();

            Assert.AreEqual(1, stmt.Parameters.Count);
            Assert.AreEqual("SELECT * FROM (SELECT * FROM dbo.TableName WHERE Foo=@Foo) AS [t0];\r\n", stmt.CommandText);
        }
Exemplo n.º 8
0
        public void TestQueryBuilderAsSqlStatementRendersParametersToArgs()
        {
            var conn  = new MockConnection();
            var query = new DbQueryBuilder("SELECT * FROM dbo.TableName WHERE Foo=@Foo", conn);


            query.Parameters.Add(new DbParameterValue()
            {
                ParameterName = "@Foo",
                DbType        = System.Data.DbType.Int32,
                Size          = 4,
                Value         = 123
            });

            var args   = new SqlBuildArguments();
            var writer = new SqlTextWriter();

            ((ISqlStatementElement)query).Render(writer, args);

            Assert.AreEqual(1, args.Parameters.Count);
            Assert.AreEqual("SELECT * FROM (SELECT * FROM dbo.TableName WHERE Foo=@Foo) AS [t0]", writer.ToString());
        }
Exemplo n.º 9
0
        /// <summary>
        /// Load records using association table
        /// </summary>
        /// <typeparam name="TTargetType">Type we want to Load</typeparam>
        /// <typeparam name="TAssociationType">Type which contains associations</typeparam>
        /// <param name="data">Data object</param>
        /// <returns>Array of Requested Objects</returns>
        /// <example>
        /// <code>
        /// Task []tasks = DbGateway.Instance.LoadAssociated{Task, TasksAssignment}(TestData.User);
        ///
        /// Types Definitions:
        ///
        ///     [DbRecord]
        ///        public class TasksAssignment
        ///        {
        ///            [DbForeignKeyField(typeof(Task))]
        ///            public ulong TaskId;
        ///
        ///            [DbForeignKeyField(typeof(User))]
        ///            public ulong UserId;
        ///
        ///            ...
        ///       }
        ///
        ///     [DbRecord]
        ///     public class Task
        ///     {
        ///         [DbPrimaryKeyField]
        ///         public ulong Id;
        ///
        ///         ...
        ///     }
        ///
        ///     [DbRecord]
        ///     public class User
        ///     {
        ///         [DbPrimaryKeyField]
        ///         public ulong Id;
        ///
        ///         ...
        ///     }
        /// </code>
        /// </example>
        public TTargetType[] LoadAssociated <TTargetType, TAssociationType>(object data) where TTargetType : new()
        {
            Type primaryType = data.GetType();

            DbIdentityRecordInfo targetRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TTargetType)) as DbIdentityRecordInfo;

            if (targetRecordInfo == null)
            {
                throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records");
            }

            DbRecordInfo associationRecordInfo = DbAttributesManager.GetRecordInfo(typeof(TAssociationType));

            DbIdentityRecordInfo sourceRecordInfo = DbAttributesManager.GetRecordInfo(primaryType) as DbIdentityRecordInfo;

            if (sourceRecordInfo == null)
            {
                throw new NdbNotIdentityException("Only DbIdentityRecord objects can have related records");
            }

            object primaryKey = sourceRecordInfo.PrimaryKey.GetValue(data);

            DbQueryBuilder queryBuilder = new DbQueryBuilder(Accessor);
            string         select       = queryBuilder.BuildSelect(targetRecordInfo);
            // below is a self documented query? :)
            string sql = string.Format(
                @"{5} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey"
//                @"SELECT * FROM {0} INNER JOIN {1} ON {0}.{3}={1}.{2} AND {1}.{4}=@PrimaryKey"
                , targetRecordInfo.TableName
                , associationRecordInfo.TableName
                , associationRecordInfo.ForeignKeys[typeof(TTargetType)].Name
                , targetRecordInfo.PrimaryKey.Name
                , associationRecordInfo.ForeignKeys[primaryType].Name
                , select
                );

            return(loadRecords <TTargetType>(targetRecordInfo, sql, "PrimaryKey", primaryKey));
        }