Exemplo n.º 1
0
        /// <summary>
        /// Gets the requested object out of the database.
        /// </summary>
        /// <param name="rep"></param>
        /// <param name="key">The key to </param>
        /// <param name="flds">List of fields to get out of the database. Leave empty to retrieve all. Names are case sensitive.</param>
        /// <returns></returns>
        public static async Task <IEnumerable <T> > GetAsync <T>(this IBaRepository rep, object key, params string[] flds) where T : BaTableObject, new()
        {
            var obj    = new T();
            var selCmd = new SelectCmdBuilder(obj.TableName);

            // Add specified fields to the select command.
            foreach (var fldName in flds)
            {
                var fld = obj.Fields[fldName];
                if (fld != null)
                {
                    selCmd.Fields.Add(fld.Name);
                }
            }

            var propBag = key.ToPropertyBag();

            foreach (var prop in propBag)
            {
                selCmd.Criteria.Add($"{prop.Key} = @{prop.Key}");
                selCmd.Parameters.AddWithValue(prop.Key, prop.Value);
            }

            var cmd = selCmd.Build();

            return(await rep.DB.GetObjectsAsync <T>(cmd).ConfigureAwait(false));
        }
Exemplo n.º 2
0
        public void SimpleSelectTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            var cmd = bldr.Build();

            AreEqual("SELECT *\n\tFROM Person p\n", cmd.CommandText);
        }
Exemplo n.º 3
0
        public void SingleFieldTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Fields.Add("p.Name");

            var cmd = bldr.Build();

            AreEqual("SELECT p.Name\n\tFROM Person p\n", cmd.CommandText);
        }
Exemplo n.º 4
0
        public void MultipleFieldTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Fields.Add("p.Name");
            bldr.Fields.Add("p.PersonTypeID");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT p.Name, p.PersonTypeID
	FROM Person p
";

            AreEqual(expected, cmd.CommandText);
        }
Exemplo n.º 5
0
        public void SingleOrderByTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.OrderBy.Add("p.Name");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT *
	FROM Person p
	ORDER BY p.Name
";

            AreEqual(expected, cmd.CommandText);
        }
Exemplo n.º 6
0
        public void SingleCriteriaTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Criteria.Add("p.Name = 'Jill'");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT *
	FROM Person p
	WHERE p.Name = 'Jill'
";

            AreEqual(expected, cmd.CommandText);
        }
Exemplo n.º 7
0
        public void SingleJoinTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Joins.Add("JOIN Address a ON (p.AddressID = a.AddressID)");
            bldr.Fields.Add("*");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT *
	FROM Person p
		JOIN Address a ON (p.AddressID = a.AddressID)
";

            AreEqual(expected, cmd.CommandText);
        }
Exemplo n.º 8
0
        public void SingleParameterTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Criteria.Add("p.Name = @Name");
            var param = bldr.Parameters.AddWithValue("Name", "Jill");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT *
	FROM Person p
	WHERE p.Name = @Name
";

            AreEqual(expected, cmd.CommandText);
            Assert.AreEqual(1, cmd.Parameters.Count);
            Assert.AreSame(param, cmd.Parameters[0]);
        }
Exemplo n.º 9
0
        public void ComplexSelectTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Fields.Add("p.ID");
            bldr.Fields.Add("p.Name");
            bldr.Fields.Add("pt.PersonTypeID");
            bldr.Fields.Add("pt.PersonTypeName");
            bldr.Fields.Add("a.AddressID");
            bldr.Fields.Add("a.City");

            bldr.Joins.Add("JOIN Address a ON (p.AddressID = a.AddressID)");
            bldr.Joins.Add("JOIN PersonType pt ON (p.PersonTypeID = pt.PersonTypeID)");

            bldr.Criteria.Add("p.Name = @Name");
            bldr.Criteria.Add("p.PersonTypeID = @PersonTypeID");
            var param = bldr.Parameters.AddValues(new { Name = "Jill", PersonTypeID = 1 });

            bldr.OrderBy.Add("p.Name");
            bldr.OrderBy.Add("a.City");

            var cmd      = bldr.Build();
            var expected =
                @"SELECT p.ID,
		p.Name,
		pt.PersonTypeID,
		pt.PersonTypeName,
		a.AddressID,
		a.City
	FROM Person p
		JOIN Address a ON (p.AddressID = a.AddressID)
		JOIN PersonType pt ON (p.PersonTypeID = pt.PersonTypeID)
	WHERE p.Name = @Name
		AND p.PersonTypeID = @PersonTypeID
	ORDER BY p.Name, a.City
";

            AreEqual(expected, cmd.CommandText);
            Assert.AreEqual(2, cmd.Parameters.Count);
        }
Exemplo n.º 10
0
        public void MultipleParameterTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.Criteria.Add("p.Name = @Name");
            bldr.Criteria.Add("p.PersonTypeID = @PersonTypeID");
            var param = bldr.Parameters.AddValues(new { Name = "Jill", PersonTypeID = 1 });

            var cmd      = bldr.Build();
            var expected =
                @"SELECT *
	FROM Person p
	WHERE p.Name = @Name
		AND p.PersonTypeID = @PersonTypeID
";

            AreEqual(expected, cmd.CommandText);
            Assert.AreEqual(2, cmd.Parameters.Count);
            Assert.AreEqual("Name", cmd.Parameters[0].ParameterName);
            Assert.AreEqual("Jill", cmd.Parameters[0].Value);
            Assert.AreEqual("PersonTypeID", cmd.Parameters[1].ParameterName);
            Assert.AreEqual(1, cmd.Parameters[1].Value);
        }
Exemplo n.º 11
0
        public void PagedTest()
        {
            var bldr = new SelectCmdBuilder("Person p");

            bldr.OrderBy.Add("p.Name");

            var cmd      = bldr.Build(0, 10);
            var expected =
                @"WITH qry AS
(
SELECT ROW_NUMBER() OVER(ORDER BY p.Name) AS RowNbr,
		COUNT(*) OVER() AS TotalRows,
		*
	FROM Person p
)
SELECT *
	FROM qry
	WHERE RowNbr BETWEEN 0 AND 10
	ORDER BY RowNbr
";

            AreEqual(expected, cmd.CommandText);
        }