public static void QueryWhereIn(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query()
.WhereIn(p => p.ProductName2, new string[] { "Konbu", "Chang", "Tunnbröd", "Geitost" });
Console.WriteLine(result.Sql());
Console.WriteLine();
IEnumerable <AzProducts> azProducts = dbConnection.Query <AzProducts>(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
}
}
/// <summary>
/// 分页
/// </summary>
public void DoItTopAndPage()
{
Console.WriteLine();
var repository = MsSqlRepoFactory.Create <ToDo>();
var results1 = repository.Query()
.Top(20);
Console.WriteLine(results1.Sql());
var results2 = repository.Query()
.Page(10, 3);
Console.WriteLine(results2.Sql());
}
public static void QueryWhere()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query()
.Where(p => p.ProductName2.Contains("t") && p.ProductID < 100)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
IEnumerable <AzProducts> azProducts = dbConnection.Query <AzProducts>(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
}
}
public void DoParam()
{
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query().Where(c => c.Id == 6).Go().FirstOrDefault();
ToDo toDo = new ToDo();
toDo.Task = "Atk";
var resultinsert = repository.Insert().For(results);//.With(c => c.Task, "nkk");
Console.WriteLine(resultinsert.ParamSql());
var v = resultinsert.ParamSqlWithEntity();
Console.WriteLine(v.paramsql);
}
public static void LeftOuterJoin()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query()
.LeftOuterJoin <AzSuppliers>()
.On <AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
IEnumerable <AzProducts> azProducts = dbConnection.Query <AzProducts>(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName2}\t{item.Supplier}");
}
}
public static void DoUpdateEntityReturnParam()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository
.Update()
.Set(p => p.ProductName2, "testvalue123")
.Set(p => p.CategoryID, 5)
.Where(p => p.ProductID == 84);
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
var ret = resultUpdate.ParamSqlWithEntity();
// AzProducts products = new AzProducts() { ProductID = 84, ProductName2 = "testvalue100", CategoryID = 7 };
int result = dbConnection.Execute(ret.paramsql, ret.entity);
Console.WriteLine($"{result}");
}
public static void DoInsertSelectorParam(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultinsert = repository
.Insert()
.With(c => c.ProductName2, "testvalue");
if (go)
{
var rest = resultinsert.Go();
}
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// INSERT [dbo].[Products]([ProductName])
// VALUES(@ProductName2);
// SELECT [ProductName] as ProductName2,[ProductID]
// FROM [dbo].[Products]
// WHERE [ProductID] = SCOPE_IDENTITY();
}
public static void DoUpdateEntityParam(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Update().For(azProducts);
if (go)
{
var rest = resultUpdate.Go();
}
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;
}
/// <summary>
/// Join 演示
/// </summary>
public static void DoJoin()
{
var repoCustomers = MsSqlRepoFactory.Create <Orders>();
var cust = repoCustomers.Query().Select(c => c.OrderID, c => c.CompanyName, c => c.FirstName, c => c.LastName, c => c.OrderDate)
.InnerJoin <Customers>()
.On <Customers>((r, l) => r.CustomerID == l.CustomerID, l => l.CompanyName)
.InnerJoin <Employees>()
.On <Employees>((k, q) => k.EmployeeID == q.EmployeeID, q => q.FirstName, q => q.LastName)
.Top(10);
Console.WriteLine(cust.Sql());
foreach (var item in cust.Go())
{
Console.WriteLine($"{item.OrderID}\t{item.CompanyName}\t{item.FirstName}\t{item.LastName}\t{item.OrderDate};");
}
}
public void DoIt()
{
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query().Select(e => e.Id, e => e.Task)
.Where(c => c.Id == 9)
.Where(c => c.Id > 12)
.And(c => c.Id > 90);
Console.WriteLine(results.Sql());
//foreach (var item in results2)
//{
// Console.WriteLine($"{item.Id}\t {item.Task} ");
//}
}
public static void DoUpdateEntity(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Update().For(azProducts);
if (go)
{
var rest = resultUpdate.Go();
}
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 StoreProcedureSimpleNoDTO()
{
var repository = MsSqlRepoFactory.Create();
var paramDef = new ParameterDefinition[]
{
new ParameterDefinition
{
Name = "OrderID",
Value = "10248"
}
};
IDataReader dataReader = repository.StatementExecutor.ExecuteStoredProcedure("CustOrdersDetail", paramDef);
while (dataReader.Read())
{
Console.WriteLine($"{dataReader["ProductName"]}\t{dataReader["Quantity"]}\t{dataReader["ExtendedPrice"]}");
}
}
public static void QueryUnion()
{
var repository = MsSqlRepoFactory.Create <AzCustomers>();
// 此语句不会参与数据查询，只是作为Union的包裹
// 如果此语句本身也是数据查询，请增加到new List<UnionSql>中
var result = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName);
var result01 = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName)
.Where(c => c.CustomerID == "ANATR");
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();
IEnumerable <AzCustomers> azCustomers = dbConnection.Query <AzCustomers>(resultAllSql);
foreach (var item in azCustomers)
{
Console.WriteLine($"{item.CustomerID}\t{item.CompanyName}");
}
}
public static void TestMsSql()
{
string ConnectionString = "Data Source=(Local);Initial Catalog=Northwind;User ID=test;Password=test";
var connectionProvider = new MsSQLP.ConnectionStringConnectionProvider(ConnectionString);
MsSqlRepoFactory.UseConnectionProvider(connectionProvider);
MsSqlRepoFactory.UseStatementExecutor(new DapperStatementExecutor(connectionProvider));
MsSqlRepoFactory.UseDataReaderEntityMapper(new DapperEntityMapper());
var repository = MsSqlRepoFactory.Create <Customers>();
var result = repository.Query().Select(e => e.CustomerID, e => e.CompanyName, e => e.Address).Top(10);
Console.WriteLine(result.Sql());
var rs = result.Go();
foreach (var r in rs)
{
Console.WriteLine($"{r.CustomerID}\t {r.CompanyName}\t\t\t {r.Address}");
}
}
public static void DoUpdateEntityParam()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository
.Update()
.ParamSet(p => p.ProductName2, p => p.CategoryID)
.Where(p => p.ProductID == p.ProductID);
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
AzProducts products = new AzProducts()
{
ProductID = 84, ProductName2 = "testvalue100", CategoryID = 7
};
int result = dbConnection.Execute(resultUpdate.ParamSql(), products);
Console.WriteLine($"{result}");
}
public static void DoInsertEntityParam(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue"
};
var resultinsert = repository
.Insert()
.For(azProducts);
if (go)
{
var rest = resultinsert.Go();
}
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 QueryAvg()
{
var repository = MsSqlRepoFactory.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();
IEnumerable <AzOrder_Details> azOrder_Details = dbConnection.Query <AzOrder_Details>(result.Sql());
foreach (var item in azOrder_Details)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName}\t{item.QuantityAvg}\t{item.UnitPrice}");
}
}
public static void DoInsertEntityParam()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProduct = new AzProducts {
ProductName2 = "testvalue"
};
var resultinsert = repository
.Insert();
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// 需返回自增字段，所以用Query
IEnumerable <AzProducts> azProducts = dbConnection.Query <AzProducts>(resultinsert.ParamSql(), azProduct);
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
}
}
public static void LeftOuterJoin()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query()
.LeftOuterJoin <AzSuppliers>()
.On <AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT [dbo].[Products].[ProductID]
// , [dbo].[Products].[ProductName] as [ProductName2]
// , [dbo].[Products].[SupplierID]
// , [dbo].[Products].[CategoryID]
// , [dbo].[Products].[QuantityPerUnit]
// , [dbo].[Products].[UnitPrice]
// , [dbo].[Products].[UnitsInStock]
// , [dbo].[Products].[UnitsOnOrder]
// , [dbo].[Products].[ReorderLevel]
// , [dbo].[Products].[Discontinued]
// , [dbo].[Suppliers].[CompanyName] as [Supplier]
// FROM [dbo].[Products]
// LEFT OUTER JOIN [dbo].[Suppliers]
// ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID];
}
public static void StoreProcedureSimpleWithDTO(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzCustOrdersDetail>();
var paramDef = new ParameterDefinition[]
{
new ParameterDefinition
{
Name = "OrderID",
Value = "10248"
}
};
var result = repository.ExecuteQueryProcedure().WithParameters(paramDef);
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductName}\t{item.Quantity}\t{item.ExtendedPrice}");
}
}
}
public static void DoInnerJoin()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1")
.InnerJoin <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]
// INNER JOIN [dbo].[Suppliers] AS [a2]
// ON [a1].[SupplierID] = [a2].[SupplierID];
}
public static void QueryUnion(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzCustomers>();
// 此语句不会参与数据查询，只是作为Union的包裹
// 如果此语句本身也是数据查询，请增加到new List<UnionSql>中
var result = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName);
var result01 = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName)
.Where(c => c.CustomerID == "ANATR");
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();
if (go)
{
var resultAll = result.Union(new List <UnionSql> {
UnionSql.New(result01, UnionType.Union),
UnionSql.New(result02, UnionType.Union),
UnionSql.New(result03, UnionType.Union),
});
foreach (var item in resultAll)
{
Console.WriteLine($"{item.CustomerID}\t{item.CompanyName}");
}
}
// SELECT [_this_is_union].[CustomerID]
// , [_this_is_union].[CompanyName]
// FROM ( SELECT [dbo].[Customers].[CustomerID]
// , [dbo].[Customers].[CompanyName]
// FROM [dbo].[Customers]
// WHERE (([dbo].[Customers].[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
}