public DbContext(SqlType SqlType, string strConnectionString) { this.SqlType = SqlType; this.ConnectionString = strConnectionString; switch (this.SqlType) { case SqlType.SqlServer: ExpressionToSql = new ExpressionToSqlSQLServer(this); break; case SqlType.Oracle: ExpressionToSql = new ExpressionToSqlOracle(this); break; case SqlType.MySql: ExpressionToSql = new ExpressionToSqlMySQL(this); break; case SqlType.SQLite: ExpressionToSql = new ExpressionToSqlSQLite(this); break; case SqlType.SqlServerCe: case SqlType.PostgreSql: case SqlType.Db2: case SqlType.Accesss: throw new Exception("暂不支持此类型数据库"); } }
static void Main(string[] args) { MappingSpeedTest.SpeedTest(); Console.Title = "Expression2SqlTest"; DbContext context = new DbContext(SqlType.SqlServer, ConnectionString); IExpressionToSql ExpressionToSqlSQLServer = context.ExpressionToSql; //IExpressionToSql ExpressionToSqlSQLServer = new ExpressionToSqlSQLServer(); //IExpressionToSql ExpressionToSqlMySQL = new ExpressionToSqlMySQL(); //IExpressionToSql ExpressionToSqlSQLite = new ExpressionToSqlSQLite(); //IExpressionToSql ExpressionToSqlOracle = new ExpressionToSqlOracle(); //查询的时候,给字段取别名 Printf(ExpressionToSqlSQLServer.Select <UserInfo>(x => new { UserId = x.Id, UserName = x.Name, x.Email }), "SQLServer static class" ); var name2 = "新用户"; Printf( ExpressionToSqlSQLServer.Select <UserInfo>(). Where(u => u.Name == name2), "SQLServer static class" ); Printf( ExpressionToSqlSQLServer.Select <UserInfo, Account>((u, a) => new { u.Id, a.Name }, true). LeftJoin <Account>((u, a) => u.Id == a.UserId, true), "Multi-table join query" ); ExpressionToSql <UserInfo> userInfoSql = new ExpressionToSql <UserInfo>(SqlType.MySql, ""); 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 }). InnerJoin <Account>((u, a) => u.Id == a.UserId).FullJoin <Account>((u, c) => u.Id == c.UserId), "测试同表" ); 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>(). Take(1), "OrderBy" ); Printf( ExpressionToSqlSQLServer.Select <UserInfo>(). OrderBy(u => u.Id), "OrderBy" ); Printf( ExpressionToSqlSQLServer.Select <UserInfo>(). OrderByDesc(u => u.Id).ThenByDesc(u => u.Name), "OrderByDesc" ); 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)" ); string strName = "Paul"; Printf( ExpressionToSqlSQLServer.Insert <UserInfo>(() => new { Name = strName, 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" ); string name = GetName("Susan"); Printf( ExpressionToSqlSQLServer.Update <UserInfo>(() => new { Name = name, Sex = 1, Email = "*****@*****.**" }). Where(u => u.Id == 1), "According to the condition update specified table record" ); //to be continued... Console.ReadKey(); }