C# Sql.Append方法代码示例

        public void Can_Get_A_Page_Of_Keys()
        {
            var sql = new Sql();
            sql.Append("SELECT *").Append("FROM [merchInvoice]");

            var page = _db.Page<KeyDto>(1, 10, sql);

            Assert.NotNull(page);
            Assert.AreEqual(15, page.TotalItems);
            Assert.AreEqual(2, page.TotalPages);
        }

        public PagedPeopleResult GetPaged(int itemsPerPage, int pageNumber, string sortColumn,
            string sortOrder, string searchTerm)
        {
            var items = new List<People>();
            var db = DatabaseContext.Database;

            var currentType = typeof(People);

            var query = new Sql().Select("*").From("demo_people");

            if (!string.IsNullOrEmpty(searchTerm))
            {
                int c = 0;
                foreach (var property in currentType.GetProperties())
                {
                    string before = "WHERE";
                    if (c > 0)
                    {
                        before = "OR";
                    }

                    var columnAttri =
                           property.GetCustomAttributes(typeof(ColumnAttribute), false);

                    var columnName = property.Name;
                    if (columnAttri.Any())
                    {
                        columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    }

                    query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
                    c++;
                }
            }
            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);
            else
            {
                query.OrderBy("id asc");
            }

            var p = db.Page<People>(pageNumber, itemsPerPage, query);
            var result = new PagedPeopleResult
            {
                TotalPages = p.TotalPages,
                TotalItems = p.TotalItems,
                ItemsPerPage = p.ItemsPerPage,
                CurrentPage = p.CurrentPage,
                People = p.Items.ToList()
            };
            return result;
        }

        public UIOMaticPagedResult GetPaged(string typeName, int itemsPerPage, int pageNumber, string sortColumn,
            string sortOrder, string searchTerm)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));

            var db = (Database)DatabaseContext.Database;
            if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            var query = new Sql().Select("*").From(tableName.Value);

            EventHandler<QueryEventArgs> tmp = BuildingQuery;
            if (tmp != null)
                tmp(this, new QueryEventArgs(tableName.Value, query));

            if (!string.IsNullOrEmpty(searchTerm))
            {
                int c = 0;
                foreach (var property in currentType.GetProperties())
                {
                    //if (property.PropertyType == typeof (string))
                    //{
                        string before = "WHERE";
                        if (c > 0)
                            before = "OR";

                        var columnAttri =
                           property.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                        var columnName = property.Name;
                        if (columnAttri.Any())
                            columnName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;

                        query.Append(before + " [" + columnName + "] like @0", "%" + searchTerm + "%");
                        c++;

                    //}
                }
            }
            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);
            else
            {
                var primaryKeyColum = "id";

                var primKeyAttri = currentType.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyAttribute));
                if (primKeyAttri.Any())
                    primaryKeyColum = ((PrimaryKeyAttribute)primKeyAttri.First()).Value;

                foreach (var property in currentType.GetProperties())
                {
                    var keyAttri = property.GetCustomAttributes().Where(x => x.GetType() == typeof(PrimaryKeyColumnAttribute));
                    if (keyAttri.Any())
                        primaryKeyColum = property.Name;
                }

                query.OrderBy(primaryKeyColum + " asc");
            }

            EventHandler<QueryEventArgs> temp = BuildedQuery;
            if (temp != null)
                temp(this, new QueryEventArgs(tableName.Value,query));

            var p = db.Page<dynamic>(pageNumber, itemsPerPage, query);
            var result = new UIOMaticPagedResult
            {
                CurrentPage = p.CurrentPage,
                ItemsPerPage = p.ItemsPerPage,
                TotalItems = p.TotalItems,
                TotalPages = p.TotalPages
            };
            var items  = new List<object>();

            foreach (dynamic item in p.Items)
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);


                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;

                    if(values.ContainsKey(propName))
                        prop.SetValue(obj, values[propName]);
                }

//.........这里部分代码省略.........

        public IEnumerable<object> GetFiltered(string typeName, string filterColumn, string filterValue, string sortColumn, string sortOrder)
        {
            var currentType = Type.GetType(typeName);
            var tableName = (TableNameAttribute)Attribute.GetCustomAttribute(currentType, typeof(TableNameAttribute));
            var uioMaticAttri = (UIOMaticAttribute)Attribute.GetCustomAttribute(currentType, typeof(UIOMaticAttribute));

            var db = (Database)DatabaseContext.Database;
            if (!string.IsNullOrEmpty(uioMaticAttri.ConnectionStringName))
                db = new Database(uioMaticAttri.ConnectionStringName);

            var query = new Sql().Select("*").From(tableName.Value);

            query.Append("where" + "[" + filterColumn + "] = @0", filterValue);

            if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortOrder))
                query.OrderBy(sortColumn + " " + sortOrder);

            foreach (dynamic item in db.Fetch<dynamic>(query))
            {
                // get settable public properties of the type
                var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                    .Where(x => x.GetSetMethod() != null);

                // create an instance of the type
                var obj = Activator.CreateInstance(currentType);


                // set property values using reflection
                var values = (IDictionary<string, object>)item;
                foreach (var prop in props)
                {
                    var columnAttri =
                           prop.GetCustomAttributes().Where(x => x.GetType() == typeof(ColumnAttribute));

                    var propName = prop.Name;
                    if (columnAttri.Any())
                        propName = ((ColumnAttribute)columnAttri.FirstOrDefault()).Name;
                    if (values.ContainsKey(propName))
                        prop.SetValue(obj, values[propName]);
                }

                yield return obj;
            }
        }

        public void Append_GivenConsecutiveOrderBys_ShouldBeValid()
        {
            _sql = new Sql()
                .Append("SELECT * FROM blah");

            _sql.Append("ORDER BY x");
            _sql.Append("ORDER BY y");

            _sql.SQL.ShouldBe("SELECT * FROM blah\nORDER BY x\n, y");
        }

        public void Append_GivenConsecutiveWheres_ShouldBeValid()
        {
            _sql = new Sql()
                .Append("SELECT * FROM blah");

            _sql.Append("WHERE x");
            _sql.Append("WHERE y");

            _sql.SQL.ShouldBe("SELECT * FROM blah\nWHERE x\nAND y");
        }

        public void Append_GivenSqLInstance_ShouldBeValid()
        {
            _sql = new Sql("l0 @0", "a0");
            var sql1 = new Sql("l1 @0", "a1");
            var sql2 = new Sql("l2 @0", "a2");

            _sql.Append(sql1).ShouldBe(_sql);
            _sql.Append(sql2).ShouldBe(_sql);

            _sql.SQL.ShouldBe("l0 @0\nl1 @1\nl2 @2");
            _sql.Arguments.Length.ShouldBe(3);
            _sql.Arguments[0].ShouldBe("a0");
            _sql.Arguments[1].ShouldBe("a1");
            _sql.Arguments[2].ShouldBe("a2");
        }

 public StoredProcedureBuilder(Sql sql, string procedureName)
 {
     string initalSql = string.Format(PROCEDURE_FORMAT, procedureName);
     _sql = sql.Append(initalSql);
 }

        public void Append_GivenConsecutiveSetsAndWheres_ShouldBeValid()
        {
            _sql = new Sql()
                .Append("UPDATE blah");

            _sql.Append("SET a = 1");
            _sql.Append("SET b = 2");
            _sql.Append("WHERE x");
            _sql.Append("WHERE y");

            _sql.SQL.ShouldBe("UPDATE blah\nSET a = 1\n, b = 2\nWHERE x\nAND y");
        }

        public void Append_GivenConsecutiveSets_ShouldBeValid()
        {
            _sql = new Sql()
                .Append("UPDATE blah");

            _sql.Append("SET a = 1");
            _sql.Append("SET b = 2");

            _sql.SQL.ShouldBe("UPDATE blah\nSET a = 1\n, b = 2");
        }