public static void QueryWhere(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1")
.Where(p => p.ProductName2.Contains("test") && p.ProductID == @p.@ProductID && p.ProductName2 == @p.ProductName2, alias: "a1");
Console.WriteLine(result.Sql());
Console.WriteLine();
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductID}\t{item.Supplier}");
}
}
// 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]
// FROM [dbo].[Products]
// WHERE ((([dbo].[Products].[ProductName] LIKE '%' + 'test' + '%') and ([dbo].[Products].[ProductID] = 100)));
}
public void DoItUnion()
{
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query().Select(e => e.Id, e => e.Task);
var results5 = repository.Query().Select(e => e.Id, e => e.Task)
.Where(c => c.Id > 0 && c.Id < 7);
var results6 = repository.Query()
.Select(e => e.Id, e => e.Task)
.Where(c => c.Id > 10 && c.Id < 15);
var results2 = results.Union(new List <UnionSql> {
UnionSql.New(results5, UnionType.Union),
UnionSql.New(results6, UnionType.Union)
});
var results3 = results.UnionSql(new List <UnionSql> {
UnionSql.New(results5, UnionType.Union),
UnionSql.New(results6, UnionType.Union)
});
Console.WriteLine(results3);
foreach (var item in results2)
{
Console.WriteLine($"{item.Id}\t {item.Task} ");
}
}
public static void QueryCount(bool go = false)
{
// 已知问题，当使用平均、合计时，short 类型,合转换成 int 所以类型需要是 int
var repository = MsSqlRepoFactory.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();
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName}\t{item.Quantity}\t{item.UnitPrice}");
}
}
// 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 void DoTransactionIt()
{
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query().Where(c => c.Id < 6);
foreach (var item in results.Go())
{
Console.WriteLine($"{item.Id}\t {item.Task} ");
}
using (var tranc = repository.GetConnectionProvider.BeginTransaction())
{
repository.Update().Set(c => c.Task, "A01").Where(c => c.Id == 1).Go(); // A1
repository.Update().Set(c => c.Task, "B01").Where(c => c.Id == 2).Go(); // B2
tranc.Rollback();
}
foreach (var item in results.Go())
{
Console.WriteLine($"{item.Id}\t {item.Task} ");
}
Console.WriteLine(results.Sql());
}
public static void DoDeleteBatch()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == p.ProductID);
List <AzProducts> azProductList = new List <AzProducts>
{
new AzProducts {
ProductID = 88
},
new AzProducts {
ProductID = 89
},
new AzProducts {
ProductID = 90
},
new AzProducts {
ProductID = 91
},
};
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
int result = dbConnection.Execute(resultUpdate.Sql(), azProductList);
Console.WriteLine($"{result}");
}
/// <summary>
/// 1.test query
/// 2.
/// </summary>
/// <param name="go"></param>
public static void QueryOnly(bool go = false)
{
// [Column("ProductName")]
// public string ProductName2 { get; set; }
// Shoud Show [dbo].[Products].[ProductName] as [ProductName2]
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query();
Console.WriteLine(result.Sql());
Console.WriteLine();
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductID}\t{item.Supplier}");
}
}
// 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]
// FROM [dbo].[Products];
}
/// <summary>
/// Update
/// </summary>
public void DoItUpdata()
{
ToDo toDo = new ToDo()
{
Id = 10, Task = "B3", IsCompleted = true, CreatedDate = DateTime.Now
};
Console.WriteLine();
var repository = MsSqlRepoFactory.Create <ToDo>();
var results1 = repository.Update().For(toDo);
Console.WriteLine(results1.Sql());
Console.WriteLine();
var results2 = repository.Update()
.Set(e => e.Remark, "this remark")
.Set(e => e.Task, "H7")
.Set(e => e.CreatedDate, DateTime.Now)
.Where(e => e.Id == 10);
Console.WriteLine(results2.Sql());
}
public static void QueryWhereIn(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query()
.WhereIn(p => p.ProductName2, new string[] { "test1", "test2" });
Console.WriteLine(result.Sql());
Console.WriteLine();
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductID}\t{item.Supplier}");
}
}
// 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]
// FROM [dbo].[Products]
// WHERE ([dbo].[Products].[ProductName] IN ('test1', 'test2'));
}
/// <summary>
/// 带括号条件
/// </summary>
public void DoItNested()
{
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query()
.Where(c => c.Id > 0 && c.Id == 3)
.NestedAnd(c => c.Id == 2)
.Or(c => c.Remark.Contains("a"))
.EndNesting()
.NestedOr(c => c.IsCompleted == true)
.And(c => c.Id == 5)
.EndNesting()
.OrderBy(e => e.Id);
Console.WriteLine(results.Sql());
//foreach (var item in results.Go())
//{
// Console.WriteLine($"{item.Id},{item.Task} ");
//}
// SELECT[dbo].[ToDo].[Remark]
//, [dbo].[ToDo].[CreatedDate]
//, [dbo].[ToDo].[IsCompleted]
//, [dbo].[ToDo].[Task]
//, [dbo].[ToDo].[Id]
// FROM[dbo].[ToDo]
// WHERE([dbo].[ToDo].[Id] > 0
//AND ([dbo].[ToDo].[Id] = 2
// OR[dbo].[ToDo].[Id] = 2)
//OR([dbo].[ToDo].[IsCompleted] = 1
//AND[dbo].[ToDo].[Id] = 5))
//ORDER BY[dbo].[ToDo].[Id] ASC;
}
public static void QueryAvg(bool go = false)
{
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();
if (go)
{
var resultgo = result.Go();
foreach (var item in resultgo)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName}\t{item.QuantityAvg}\t{item.UnitPrice}");
}
}
// 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 SelectTest()
{
var repository = MsSqlRepoFactory.Create <AzOrder_Details>();
var result = repository.Query();
if (1 == 1)
{
result = result.Select(e => e.ProductID, e => e.Quantity, e => e.ProductName);
}
if (2 == 2)
{
result = result.Where(e => e.ProductID > 1);
}
if (3 == 3)
{
result = result.Or(e => e.ProductID == 2);
}
if (4 == 4)
{
result = result.InnerJoin <AzProducts>()
.On <AzProducts>((l, r) => l.ProductID == r.ProductID, r => r.ProductName2);
}
Console.WriteLine(result.Sql());
Console.WriteLine();
}
public static void DoDeleteTransaction()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == p.ProductID);
List <AzProducts> azProductList = new List <AzProducts>
{
new AzProducts {
ProductID = 92
},
new AzProducts {
ProductID = 93
},
new AzProducts {
ProductID = 94
},
new AzProducts {
ProductID = 91
},
};
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
using (var transaction = dbConnection.BeginTransaction())
{
dbConnection.Execute(resultUpdate.Sql(), azProductList, transaction: transaction);
transaction.Rollback();
}
}
public static void Config()
{
string ConnectionString = "Data Source=(Local);Initial Catalog=Northwind;User ID=test;Password=test";
var connectionProvider = new ConnectionStringConnectionProvider(ConnectionString);
MsSqlRepoFactory.UseConnectionProvider(connectionProvider);
}
public static void DoInsertEntityParamBatch()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
List <AzProducts> azProductList = new List <AzProducts> {
new AzProducts {
ProductName2 = "testvalue1", CategoryID = 1, UnitPrice = 123
},
new AzProducts {
ProductName2 = "testvalue2", CategoryID = 1, UnitPrice = 123
},
new AzProducts {
ProductName2 = "testvalue3", CategoryID = 1, UnitPrice = 123
},
new AzProducts {
ProductName2 = "testvalue4", CategoryID = 1, UnitPrice = 123
},
new AzProducts {
ProductName2 = "testvalue5", CategoryID = 1, UnitPrice = 123
},
new AzProducts {
ProductName2 = "testvalue6", CategoryID = 1, UnitPrice = 123
},
};
var resultinsert = repository
.Insert().ParamWith(c => c.ProductName2, c => c.UnitPrice, c => c.CategoryID);
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// 需返回自增字段，所以用Query
dbConnection.Execute(resultinsert.ParamSql(), azProductList);
}
/// <summary>
/// Join
/// </summary>
public void DoItJoin()
{
Console.WriteLine();
var repository = MsSqlRepoFactory.Create <ToDo>();
var results1 = repository.Query()
.InnerJoin <TaskRemark>()
// 增加附加条件，如果主选择有此属性，则查询本句中所设置
.On <TaskRemark>((r, l) => r.Task == l.Task, l => l.Remark);
Console.WriteLine(results1.Sql());
Console.WriteLine();
var results2 = repository.Query()
.LeftOuterJoin <TaskRemark>()
.On <TaskRemark>((r, l) => r.Task == l.Task, l => l.Remark);
Console.WriteLine(results2.Sql());
Console.WriteLine();
var results3 = repository.Query()
.RightOuterJoin <TaskRemark>()
.On <TaskRemark>((r, l) => r.Task == l.Task, l => l.Remark);
Console.WriteLine(results3.Sql());
}
public static void DoProcedure()
{
var repoHist = MsSqlRepoFactory.Create <CustOrderHist>();
var paramDefs = new ParameterDefinition[]
{
new ParameterDefinition
{
Name = "CustomerID",
Value = "ALFKI"
}
};
var paramDefs2 = new ParameterDefinition[]
{
new ParameterDefinition
{
Name = "CustomerID",
Value = "ALFKI"
},
new ParameterDefinition
{
Name = "Cust",
Value = "Cust",
Direction = ParameterDirection.InputOutput,
Size = 100
},
new ParameterDefinition
{
Name = "Cust2",
Value = "Cust2",
Direction = ParameterDirection.Input,
Size = 23
}
};
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
var json1 = javaScriptSerializer.Serialize(paramDefs2);
Console.WriteLine(json1);
var obj = javaScriptSerializer.Deserialize <ParameterDefinition[]>(json1);
var hist = repoHist.ExecuteQueryProcedure().WithName("CustOrderHist").WithParameters(paramDefs).Go();
foreach (var item in hist)
{
Console.WriteLine($"{item.ProductName}\t{item.Total};");
}
}
/// <summary>
/// NoLocks
/// </summary>
public void DoItNoLocks()
{
Console.WriteLine();
var repository = MsSqlRepoFactory.Create <ToDo>();
var results = repository.Query()
.NoLocks();
Console.WriteLine(results.Sql());
}
/// <summary>
/// init
/// 创建初始方法，初始一个工厂类。
/// </summary>
static void Init()
{
// Set Connection String
string ConnectionString = "Data Source=(Local);Initial Catalog=Northwind;User ID=test;Password=test";
var connectionProvider = new ConnectionStringConnectionProvider(ConnectionString);
MsSqlRepoFactory.UseConnectionProvider(connectionProvider);
// this Demo is POJO ,So Using SimpleWritablePropertyMatcher()。
// 本例中，使用的是简单类，所以用SimpleWritablePropertyMatcher()来操作属性。
MsSqlRepoFactory.UseWritablePropertyMatcher(new SimpleWritablePropertyMatcher());
}
public static void DoDelete()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == 90);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
int result = dbConnection.Execute(resultUpdate.Sql());
Console.WriteLine($"{result}");
}
/// <summary>
/// 显示TaskRemark表数据
/// </summary>
public void DoItTaskRemark()
{
var repository = MsSqlRepoFactory.Create <TaskRemark>();
var results = repository.Query()
.Select(e => e.Id, e => e.Task, e => e.Remark);
Console.WriteLine(results.Sql());
foreach (var item in results.Go())
{
Console.WriteLine($"{item.Id},{item.Task},{item.Remark} ");
}
}
public static void DoDeleteEntity(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 81
};
var resultUpdate = repository.Delete().For(azProducts);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
int result = dbConnection.Execute(resultUpdate.Sql());
Console.WriteLine($"{result}");
}
public static void QueryOnly()
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var result = repository.Query().Top(10);
Console.WriteLine(result.Sql());
IEnumerable <AzProducts> azProducts = dbConnection.Query <AzProducts>(result.Sql());
foreach (var item in azProducts)
{
Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
}
}
public static void DoDelete(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == 82);
if (go)
{
var rest = resultUpdate.Go();
}
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products] WHERE [ProductID] = 82;
}
public static void DoDeleteWhere(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == 82).And(p => p.ProductName2 == "test2");
if (go)
{
var rest = resultUpdate.Go();
}
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products]
// WHERE ([dbo].[Products].[ProductID] = 82 AND [dbo].[Products].[ProductName] = 'test2');
}
public static void DoUpdateSelectorParam(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Update().Set(c => c.ProductName2, "testvalue").Where(p => p.ProductID == 82);
if (go)
{
var rest = resultUpdate.Go();
}
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
// UPDATE [dbo].[Products]
// SET ProductName = @ProductName2
// WHERE ([dbo].[Products].[ProductID] = 82);
}
public static void DoDeleteEntity(bool go = false)
{
var repository = MsSqlRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Delete().For(azProducts);
if (go)
{
var rest = resultUpdate.Go();
}
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products] WHERE [ProductID] = 82;
}
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());
}
private static void Main(string[] args)
{
var connectionProvider = new AppConfigFirstConnectionProvider();
MsSqlRepoFactory.UseConnectionProvider(connectionProvider);
MsSqlRepoFactory.UseStatementTransactionExecutor();
var gettingStarted = new GettingStarted();
// gettingStarted.DoIt();
// gettingStarted.DoItJoin();
gettingStarted.DoItUnion();
// gettingStarted.DoItUpdata();
//gettingStarted.DoItNested();
//gettingStarted.DoItTaskRemark();
Console.ReadLine();
}
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);
}
