Exemplo n.º 1
0
        static void Main(string[] args)
        {
            Console.Title = "Expression2SqlTest";

            ExpressionToSql <UserInfo> userInfoSql = new ExpressionToSql <UserInfo>(new MySQLSqlParser());

            Printf(
                userInfoSql.Select().Where(u => u.Id != 1),
                "Instance class"
                );


            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == "Jones"),
                "SQLServer static class"
                );
            Printf(
                ExpressionToSqlMySQL.Select <UserInfo>().
                Where(u => u.Name == "Tom"),
                "MySQL static class"
                );
            Printf(
                ExpressionToSqlSQLite.Select <UserInfo>().
                Where(u => u.Name == "Venus"),
                "SQLite static class"
                );
            Printf(
                ExpressionToSqlOracle.Select <UserInfo>().
                Where(u => u.Name == "Lucy"),
                "Oracle static class"
                );



            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(),
                "Query single table all fields"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id),
                "Query single table single field"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => new { u.Id, u.Name }),
                "Query single table specified field"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => new { UserId = u.Id, u.Email, UserName = u.Name }),
                "Query single table specified field, take field alias"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == "Alice"),
                "Query single table with multiple conditions"
                );

            string parameterValue = "Jack";

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == parameterValue),
                "Query single table with multiple conditions"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.Like("b")),
                "Query single table with 'like' conditions"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.LikeLeft("b")),
                "Query single table with 'like left' conditions"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.LikeRight("b")),
                "Query single table with 'like right' conditions"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Id.In(1, 2, 3, 4, 5)),
                "Query single table with 'in' conditions, mode 1"
                );

            int[] aryId = { 1, 2, 3 };
            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Id.In(aryId)),
                "Query single table with 'in' conditions, mode 2"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Name.In(new string[] { "a", "b" })),
                "Query single table with 'in' conditions, mode 3"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(
                    u => u.Name == "b" &&
                    u.Id > 2 &&
                    u.Name != null &&
                    u.Id > int.MinValue &&
                    u.Id < int.MaxValue &&
                    u.Id.In(1, 2, 3) &&
                    u.Name.Like("a") &&
                    u.Name.LikeLeft("b") &&
                    u.Name.LikeRight("c") ||
                    u.Id == null
                    ),
                "Query single table with multiple conditions"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(
                    u => u.Name == "b" &&
                    (u.Id == 100 || u.Id != null) &&
                    (u.Id == 50 || u.Id == null)
                    ),
                "Query single table with multiple conditions, parentheses priority"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                Join <Account>((u, a) => u.Id == a.UserId),
                "Multi-table join query"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                InnerJoin <Account>((u, a) => u.Id == a.UserId),
                "Multi-table innerJoin query"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                LeftJoin <Account>((u, a) => u.Id == a.UserId),
                "Multi-table leftJoin query"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                RightJoin <Account>((u, a) => u.Id == a.UserId),
                "Multi-table rightJoin query"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                FullJoin <Account>((u, a) => u.Id == a.UserId),
                "Multi-table fullJoin query"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account, Student, Class, City, Country>((a, b, c, d, e, f) =>
                                                                                                   new { a.Id, b.Name, StudentName = c.Name, ClassName = d.Name, e.CityName, CountryName = f.Name }).
                Join <Account>((u, a) => u.Id == a.UserId).
                LeftJoin <Account, Student>((a, s) => a.Id == s.AccountId).
                RightJoin <Student, Class>((s, c) => s.Id == c.UserId).
                InnerJoin <Class, City>((c, d) => c.CityId == d.Id).
                FullJoin <City, Country>((c, d) => c.CountryId == d.Id).
                Where(u => u.Id != null),
                "More complex associated query table, the same column name alias"
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id).
                GroupBy(u => u.Name),
                "GroupBy"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                OrderBy(u => u.Id),
                "OrderBy"
                );

            Printf(
                ExpressionToSqlSQLServer.Max <UserInfo>(u => u.Id),
                "Returns a column of the maximum value, null value is not included in the calculation"
                );

            Printf(
                ExpressionToSqlSQLServer.Min <UserInfo>(u => u.Id),
                "Returns a column of the minimum value, null value is not included in the calculation"
                );

            Printf(
                ExpressionToSqlSQLServer.Avg <UserInfo>(u => u.Id),
                "Returns the numerical average of the column, null value is not included in the calculation"
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(),
                "Returns the record number in the table."
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id),
                "Returns the value of the specified column number(null can't be counted)"
                );

            Printf(
                ExpressionToSqlSQLServer.Sum <UserInfo>(u => u.Id),
                "Returns the total number of numeric column(total amount)"
                );

            Printf(
                ExpressionToSqlSQLServer.Insert <UserInfo>(() => new { Name = "Paul", Sex = 1, Email = "*****@*****.**" }),
                "Insert a record"
                );

            Printf(
                ExpressionToSqlSQLServer.Delete <UserInfo>(),
                "A full table delete"
                );

            Printf(
                ExpressionToSqlSQLServer.Delete <UserInfo>().
                Where(u => u.Id == null),
                "According to the condition delete specified table record"
                );

            Printf(
                ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "Marilyn", Sex = 1, Email = "*****@*****.**" }),
                "A full table updates"
                );

            Printf(
                ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "Susan", Sex = 1, Email = "*****@*****.**" }).
                Where(u => u.Id == 1),
                "According to the condition update specified table record"
                );

            //to be continued...
        }
Exemplo n.º 2
0
        static void Main(string[] args)
        {
            Console.Title = "Expression2SqlTest";

            ExpressionToSql <UserInfo> userInfoSql = new ExpressionToSql <UserInfo>(new MySQLSqlParser());

            Printf(
                userInfoSql.Select().Where(u => u.Id != 1),
                "查询单表,带where条件,实例类"
                );


            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == "张三"),
                "SQLServer静态类"
                );
            Printf(
                ExpressionToSqlMySQL.Select <UserInfo>().
                Where(u => u.Name == "张三"),
                "MySQL静态类"
                );
            Printf(
                ExpressionToSqlSQLite.Select <UserInfo>().
                Where(u => u.Name == "张三"),
                "SQLite静态类"
                );
            Printf(
                ExpressionToSqlOracle.Select <UserInfo>().
                Where(u => u.Name == "张三"),
                "Oracle静态类"
                );



            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(),
                "查询单表所有字段"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id),
                "查询单表单个字段"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => new { u.Id, u.Name }),
                "查询单表指定字段"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => new { UserId = u.Id, u.Email, UserName = u.Name }),
                "查询单表指定字段,取字段别名"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == "张三"),
                "查询单表,带where条件,实参赋值"
                );

            string parameterValue = "李四";

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                Where(u => u.Name == parameterValue),
                "查询单表,带where条件,形参赋值"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.Like("b")),
                "查询单表,带where Like条件"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.LikeLeft("b")),
                "查询单表,带where LikeLeft条件"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(u => u.Name.LikeRight("b")),
                "查询单表,带where LikeRight条件"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Id.In(1, 2, 3, 4, 5)),
                "查询单表,带where in条件,写法一"
                );

            int[] aryId = { 1, 2, 3 };
            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Id.In(aryId)),
                "查询单表,带where in条件,写法二"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Name).
                Where(u => u.Name.In(new string[] { "a", "b" })),
                "查询单表,带where in条件,写法三"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(
                    u => u.Name == "b" &&
                    u.Id > 2 &&
                    u.Name != null &&
                    u.Id > int.MinValue &&
                    u.Id < int.MaxValue &&
                    u.Id.In(1, 2, 3) &&
                    u.Name.Like("a") &&
                    u.Name.LikeLeft("b") &&
                    u.Name.LikeRight("c") ||
                    u.Id == null
                    ),
                "查询单表,带多个where条件"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>(u => u.Id).
                Where(
                    u => u.Name == "b" &&
                    (u.Id == 100 || u.Id != null) &&
                    (u.Id == 50 || u.Id == null)
                    ),
                "查询单表,带多个where条件,括号优先级"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                Join <Account>((u, a) => u.Id == a.UserId),
                "多表Join关联查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                InnerJoin <Account>((u, a) => u.Id == a.UserId),
                "多表InnerJoin关联查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                LeftJoin <Account>((u, a) => u.Id == a.UserId),
                "多表LeftJoin关联查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                RightJoin <Account>((u, a) => u.Id == a.UserId),
                "多表RightJoin关联查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }).
                FullJoin <Account>((u, a) => u.Id == a.UserId),
                "多表FullJoin关联查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo, Account, Student, Class, City, Country>((a, b, c, d, e, f) =>
                                                                                                   new { a.Id, b.Name, StudentName = c.Name, ClassName = d.Name, e.CityName, CountryName = f.Name }).
                Join <Account>((u, a) => u.Id == a.UserId).
                LeftJoin <Account, Student>((a, s) => a.Id == s.AccountId).
                RightJoin <Student, Class>((s, c) => s.Id == c.UserId).
                InnerJoin <Class, City>((c, d) => c.CityId == d.Id).
                FullJoin <City, Country>((c, d) => c.CountryId == d.Id).
                Where(u => u.Id != null),
                "多表复杂关联查询,多表相同列名取别名"
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id).
                GroupBy(u => u.Name),
                "GroupBy分组查询"
                );

            Printf(
                ExpressionToSqlSQLServer.Select <UserInfo>().
                OrderBy(u => u.Id),
                "OrderBy排序"
                );

            Printf(
                ExpressionToSqlSQLServer.Max <UserInfo>(u => u.Id),
                "返回一列中的最大值。NULL 值不包括在计算中。"
                );

            Printf(
                ExpressionToSqlSQLServer.Min <UserInfo>(u => u.Id),
                "返回一列中的最小值。NULL 值不包括在计算中。"
                );

            Printf(
                ExpressionToSqlSQLServer.Avg <UserInfo>(u => u.Id),
                "返回数值列的平均值。NULL 值不包括在计算中。"
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(),
                "返回表中的记录数"
                );

            Printf(
                ExpressionToSqlSQLServer.Count <UserInfo>(u => u.Id),
                "返回指定列的值的数目(NULL 不计入)"
                );

            Printf(
                ExpressionToSqlSQLServer.Sum <UserInfo>(u => u.Id),
                "返回数值列的总数(总额)。"
                );

            Printf(
                ExpressionToSqlSQLServer.Insert <UserInfo>(() => new { Name = "张三", Sex = 1, Email = "*****@*****.**" }),
                "插入一条记录"
                );

            Printf(
                ExpressionToSqlSQLServer.Delete <UserInfo>(),
                "全表删除"
                );

            Printf(
                ExpressionToSqlSQLServer.Delete <UserInfo>().
                Where(u => u.Id == null),
                "根据where条件删除指定表记录"
                );

            Printf(
                ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }),
                "全表更新"
                );

            Printf(
                ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = "", Sex = 1, Email = "*****@*****.**" }).
                Where(u => u.Id == 1),
                "根据where条件更新指定表记录"
                );

            //to be continued...
        }