public static void QueryCount( ) { // 已知问题,当使用平均、合计时,short 类型,合转换成 int 所以类型需要是 int var repository = NormalRepoFactory.Create <AzOrder_Details>(); var result = repository.Query().Select(p => p.ProductID, p => p.ProductName) .Count(c => c.OrderID, c => c.OrderCount) .GroupBy(c => c.ProductID) .InnerJoin <AzProducts>() .On <AzProducts>((r, l) => r.ProductID == l.ProductID, l => l.ProductName2) .GroupBy <AzProducts>(p => p.ProductName2) .Top(10); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT TOP (10) [dbo].[Order Details].[ProductID] // , [dbo].[Products].[ProductName] // , SUM([dbo].[Order Details].[Quantity]) AS [Quantity] // FROM [dbo].[Order Details] // INNER JOIN [dbo].[Products] // ON [dbo].[Order Details].[ProductID] = [dbo].[Products].[ProductID] // GROUP BY [dbo].[Order Details].[ProductID] // , [dbo].[Products].[ProductName] // HAVING AVG([dbo].[Order Details].[Quantity]) > 10; }
public static void QueryAvg( ) { var repository = NormalRepoFactory.Create <AzOrder_Details>(); var result = repository.Query().Select(p => p.ProductID, p => p.ProductName) .Avg(c => c.Quantity, c => c.QuantityAvg) .Avg(c => c.UnitPrice) .GroupBy(c => c.ProductID) .InnerJoin <AzProducts>() .On <AzProducts>((r, l) => r.ProductID == l.ProductID, l => l.ProductName2) .GroupBy <AzProducts>(p => p.ProductName2) .Top(10); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT TOP (10) [dbo].[Order Details].[ProductID] // , [dbo].[Products].[ProductName] // , AVG([dbo].[Order Details].[Quantity]) AS [QuantityAvg] // , AVG([dbo].[Order Details].[UnitPrice]) AS [UnitPrice] // FROM [dbo].[Order Details] // INNER JOIN [dbo].[Products] // ON [dbo].[Order Details].[ProductID] = [dbo].[Products].[ProductID] // GROUP BY [dbo].[Order Details].[ProductID] // , [dbo].[Products].[ProductName]; }
public static void QueryAvg() { var repository = NormalRepoFactory.Create <AzOrder_Details>(); var result = repository.Query().From("a1").Select(p => p.ProductID, alias: "a1", additionalSelectors: p => p.ProductName) .Avg(c => c.Quantity, c => c.QuantityAvg, "a1") .Avg(c => c.UnitPrice, "a1") .GroupBy(c => c.ProductID, "a1") .InnerJoin <AzProducts>("a2") .On <AzProducts>((r, l) => r.ProductID == l.ProductID, "a1", "a2", l => l.ProductName2) .GroupBy <AzProducts>(p => p.ProductName2, "a2") .Top(10); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT TOP (10) [a1].[ProductID] // , [a1].[ProductName] // , AVG([a1].[Quantity]) AS [QuantityAvg] // , AVG([a1].[UnitPrice]) AS [UnitPrice] // FROM [dbo].[Order Details] AS [a1] // INNER JOIN [dbo].[Products] AS [a2] // ON [a1].[ProductID] = [a2].[ProductID] // GROUP BY [a1].[ProductID] // , [a2].[ProductName]; }
public static void DoDelete() { var repository = NormalRepoFactory.Create <AzProducts>(); var resultUpdate = repository.Delete().Where(p => p.ProductID == 82); Console.WriteLine(resultUpdate.Sql()); Console.WriteLine(); // DELETE [dbo].[Products] WHERE [ProductID] = 82; }
public static void DoUpdateSelectorParam() { var repository = NormalRepoFactory.Create <AzProducts>(); var resultUpdate = repository.Update().Set(c => c.ProductName2, "testvalue").Where(p => p.ProductID == 82); Console.WriteLine(resultUpdate.ParamSql()); Console.WriteLine(); // UPDATE [dbo].[Products] // SET ProductName = @ProductName2 // WHERE ([dbo].[Products].[ProductID] = 82); }
public static void DoDeleteWhere() { var repository = NormalRepoFactory.Create <AzProducts>(); var resultUpdate = repository.Delete().Where(p => p.ProductID == 82).And(p => p.ProductName2 == "test2"); Console.WriteLine(resultUpdate.Sql()); Console.WriteLine(); // DELETE [dbo].[Products] // WHERE ([dbo].[Products].[ProductID] = 82 AND [dbo].[Products].[ProductName] = 'test2'); }
public static void DoDeleteEntity() { var repository = NormalRepoFactory.Create <AzProducts>(); AzProducts azProducts = new AzProducts { ProductName2 = "testvalue", ProductID = 82 }; var resultUpdate = repository.Delete().For(azProducts); Console.WriteLine(resultUpdate.Sql()); Console.WriteLine(); // DELETE [dbo].[Products] WHERE [ProductID] = 82; }
public static void QueryUnion() { var repository = NormalRepoFactory.Create <AzCustomers>(); // 此语句不会参与数据查询,只是作为Union的包裹 // 如果此语句本身也是数据查询,请增加到new List<UnionSql>中 var result = repository.Query() .Select(c => c.CustomerID, c => c.CompanyName); var result01 = repository.Query().From("a1") .Select(c => c.CustomerID, "a1", c => c.CompanyName) .Where(c => c.CustomerID == "ANATR", "a1"); var result02 = repository.Query() .Select(c => c.CustomerID, c => c.CompanyName) .Where(c => c.CustomerID == "FRANK"); var result03 = repository.Query() .Select(c => c.CustomerID, c => c.CompanyName) .Where(c => c.CustomerID == "TRADH"); var resultAllSql = result.UnionSql(new List <UnionSql> { UnionSql.New(result01, UnionType.Union), UnionSql.New(result02, UnionType.Union), UnionSql.New(result03, UnionType.Union), }); Console.WriteLine(resultAllSql); Console.WriteLine(); // SELECT [_this_is_union].[CustomerID] // , [_this_is_union].[CompanyName] // FROM ( SELECT [a1].[CustomerID] // , [a1].[CompanyName] // FROM [dbo].[Customers] AS [a1] // WHERE (([a1].[CustomerID] = 'ANATR')) // UNION // SELECT [dbo].[Customers].[CustomerID] // , [dbo].[Customers].[CompanyName] // FROM [dbo].[Customers] // WHERE (([dbo].[Customers].[CustomerID] = 'FRANK')) // UNION // SELECT [dbo].[Customers].[CustomerID] // , [dbo].[Customers].[CompanyName] // FROM [dbo].[Customers] // WHERE (([dbo].[Customers].[CustomerID] = 'TRADH')) ) // AS _this_is_union }
public static void DoUpdateEntityParam() { var repository = NormalRepoFactory.Create <AzProducts>(); AzProducts azProducts = new AzProducts { ProductName2 = "testvalue", ProductID = 82 }; var resultUpdate = repository.Update().For(azProducts); Console.WriteLine(resultUpdate.ParamSql()); Console.WriteLine(); // UPDATE [dbo].[Products] // SET ProductName = @ProductName2, SupplierID = @SupplierID, CategoryID = @CategoryID, QuantityPerUnit = @QuantityPerUnit, // UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel, Discontinued = @Discontinued // WHERE ProductID = @ProductID; }
public static void DoUpdateEntity() { var repository = NormalRepoFactory.Create <AzProducts>(); AzProducts azProducts = new AzProducts { ProductName2 = "testvalue", ProductID = 82 }; var resultUpdate = repository.Update().For(azProducts); Console.WriteLine(resultUpdate.Sql()); Console.WriteLine(); // UPDATE [dbo].[Products] // SET [ProductName] = 'testvalue', [SupplierID] = NULL, [CategoryID] = NULL, [QuantityPerUnit] = NULL, [UnitPrice] = NULL, // [UnitsInStock] = NULL, [UnitsOnOrder] = NULL, [ReorderLevel] = NULL, [Discontinued] = 0 // WHERE [ProductID] = 82; }
public static void DoInsertSelectorParam( ) { var repository = NormalRepoFactory.Create <AzProducts>(); var resultinsert = repository .Insert() .With(c => c.ProductName2, "testvalue"); Console.WriteLine(resultinsert.ParamSql()); Console.WriteLine(); // INSERT [dbo].[Products]([ProductName]) // VALUES(@ProductName2); // SELECT [ProductName] as ProductName2,[ProductID] // FROM [dbo].[Products] // WHERE [ProductID] = SCOPE_IDENTITY(); }
static void Main(string[] args) { var repository = NormalRepoFactory.Create <Customers>(); var result = repository.Query() .Select(e => e.CustomerID, e => e.CompanyName, e => e.Address, e => e.EmployeeID) .Top(10) .Where(e => e.CustomerID == "ok") .And(e => e.Country == "china") .Where(e => e.Fax == "xxx") .InnerJoin <Employees>() .On <Employees>((r, l) => r.CustomerID == l.FirstName, l => l.EmployeeID); Console.WriteLine(result.Sql()); // var h= result.Go(); Console.ReadLine(); }
public static void DoInsertEntityParam( ) { var repository = NormalRepoFactory.Create <AzProducts>(); AzProducts azProducts = new AzProducts { ProductName2 = "testvalue" }; var resultinsert = repository .Insert() .For(azProducts); Console.WriteLine(resultinsert.ParamSql()); Console.WriteLine(); // INSERT [dbo].[Products]([ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) // VALUES(@ProductName2,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued); // SELECT [ProductID],[ProductName] as ProductName2,[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] // FROM [dbo].[Products] // WHERE [ProductID] = SCOPE_IDENTITY(); }
public static void QueryWhereIn() { var repository = NormalRepoFactory.Create <AzProducts>(); var result = repository.Query().From("a1") .WhereIn(p => p.ProductName2, new string[] { "test1", "test2" }, "a1"); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT [a1].[ProductID] // , [a1].[ProductName] as [ProductName2] // , [a1].[SupplierID] // , [a1].[CategoryID] // , [a1].[QuantityPerUnit] // , [a1].[UnitPrice] // , [a1].[UnitsInStock] // , [a1].[UnitsOnOrder] // , [a1].[ReorderLevel] // , [a1].[Discontinued] // FROM [dbo].[Products] AS [a1] // WHERE ([a1].[ProductName] IN ('test1', 'test2')); }
public static void QueryWhere() { var repository = NormalRepoFactory.Create <AzProducts>(); var result = repository.Query().From("a1") .Where(p => p.ProductName2.Contains("test") && p.ProductID == 12 && p.ProductName2 == "test", alias: "a1"); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT [a1].[ProductID] // , [a1].[ProductName] as [ProductName2] // , [a1].[SupplierID] // , [a1].[CategoryID] // , [a1].[QuantityPerUnit] // , [a1].[UnitPrice] // , [a1].[UnitsInStock] // , [a1].[UnitsOnOrder] // , [a1].[ReorderLevel] // , [a1].[Discontinued] // FROM [dbo].[Products] AS [a1] // WHERE (((([a1].[ProductName] LIKE '%test%') and ([a1].[ProductID] = 12)) and ([a1].[ProductName] ='test'))); }
/// <summary> /// 1.test query /// 2. /// </summary> /// <param name="go"></param> public static void QueryOnly() { // [Column("ProductName")] // public string ProductName2 { get; set; } // Shoud Show [dbo].[Products].[ProductName] as [ProductName2] var repository = NormalRepoFactory.Create <AzProducts>(); var result = repository.Query().From("a1").Top(10); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT TOP (10) [a1].[ProductID] // , [a1].[ProductName] as [ProductName2] // , [a1].[SupplierID] // , [a1].[CategoryID] // , [a1].[QuantityPerUnit] // , [a1].[UnitPrice] // , [a1].[UnitsInStock] // , [a1].[UnitsOnOrder] // , [a1].[ReorderLevel] // , [a1].[Discontinued] // FROM [dbo].[Products] AS [a1]; }
public static void LeftOuterJoin() { var repository = NormalRepoFactory.Create <AzProducts>(); var result = repository.Query().From("a1") .LeftOuterJoin <AzSuppliers>("a2") .On <AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, "a1", "a2", r => r.CompanyName); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT [a1].[ProductID] // , [a1].[ProductName] as [ProductName2] // , [a1].[SupplierID] // , [a1].[CategoryID] // , [a1].[QuantityPerUnit] // , [a1].[UnitPrice] // , [a1].[UnitsInStock] // , [a1].[UnitsOnOrder] // , [a1].[ReorderLevel] // , [a1].[Discontinued] // , [a1].[CompanyName] as [Supplier] // FROM [dbo].[Products] AS [a1] // LEFT OUTER JOIN [dbo].[Suppliers] AS [a2] // ON [a1].[SupplierID] = [a2].[SupplierID]; }
public static void QuerySum() { // 已知问题,当使用平均、合计时,short 类型,合转换成 int 所以类型需要是 int var repository = NormalRepoFactory.Create <AzOrder_Details>(); var result = repository.Query().From("a1").Select(p => p.ProductID, "a1", p => p.ProductName) .Sum(c => c.Quantity, "a1") .GroupBy(c => c.ProductID, "a1") .InnerJoin <AzProducts>("a2") .On <AzProducts>((r, l) => r.ProductID == l.ProductID, "a1", "a2", l => l.ProductName2) .GroupBy <AzProducts>(p => p.ProductName2, "a2") .Top(10); Console.WriteLine(result.Sql()); Console.WriteLine(); // SELECT TOP (10) [a1].[ProductID] // , [a1].[ProductName] // , SUM([a1].[Quantity]) AS [Quantity] // FROM [dbo].[Order Details] AS [a1] // INNER JOIN [dbo].[Products] AS [a2] // ON [a1].[ProductID] = [a2].[ProductID] // GROUP BY [a1].[ProductID] // , [a2].[ProductName]; }