public static class Performance
{
public static void SetUp()
{
QueryMaker.DbScheme = "dbo";
}
public static void FullSelectForCustomerTime()
{
int cnt = 1000000;
string sql = null;
var stopwatch = System.Diagnostics.Stopwatch.StartNew();
for (int i = 0; i < cnt; i++)
{
sql = QueryMaker.New()
.SELECT()
.Col("Id", "Id")
.Col("Name", "Name")
.Col("Description", "Desc")
.Col("Address", "Addr")
.Col("Zip", "Zip")
.Col("Balance", "Balance")
.Col("Registered", "Reg")
.FROM().Tab("Customer")
.WHERE("Zip = @zip")
.ORDERBY("Name", SortAs.Asc)
.RawSql();
}
stopwatch.Stop();
//var example = "SELECT\n\tId AS Id\n\t, Name AS Name" + "\n\t, Description AS Desc\n\t, Address AS Addr\n\t, Zip AS Zip" + "\n\t, Balance AS Balance\n\t, Registered AS Reg" + "\nFROM\n\t[dbo].[Customer]" + "\nWHERE\n\tZip = @zip\nORDER BY Name ASC;";
System.Diagnostics.Trace.WriteLine(stopwatch.Elapsed.TotalMilliseconds.ToString("Total 0.00 ms"));
System.Diagnostics.Trace.WriteLine( ((double) (stopwatch.Elapsed.TotalMilliseconds*1000)/cnt).ToString("0.00 us per one query"));
}
}
初始化语句构建器
publicstaticclass InitSqlMaker
{
publicstaticvoidInitSqlMakerO()
{
var current = QueryMaker.Current;
Console.WriteLine("current ISqlMaker:" + (current is ISqlMaker));
var maker = QueryMaker.New();
Console.WriteLine("maker ISqlMaker:" + (maker is ISqlMaker));
Console.WriteLine("maker ISqlFirst:" + (maker is ISqlFirst));
Console.WriteLine("maker.Equals(current):" + (maker.Equals(current)));
}
publicstaticvoidMakeEmptyQuery()
{
var newQuery = QueryMaker.Current;
try
{
newQuery.RawSql();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
Select语句构建
public class SelectSqlMaker
{
private string _dbScheme;
public void SetUp()
{
_dbScheme = "dbo"; // 默认MSSQL 架构
}
public void InitSelectDistinctTest()
{
var maker = QueryMaker.New(_dbScheme) .SelectDistinct("Name, Description, Address");
var sql = maker.RawSql();
// var example = "SELECT DISTINCT\n\tName\n\t, Description\n\t, Address;";
}
public void SelectUnionTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.UNION();
var sql = maker.RawSql();
var example = "SELECT\nUNION\nSELECT;";
maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id")
.UNION(IsALL: true)
.Col("Id");
sql = maker.RawSql();
example = "SELECT\n\tId\nUNION ALL\nSELECT\n\tId;";
}
public void InitSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Name, t.Description, t.Address AS adr");
var sql = maker.RawSql();
//var example = "SELECT\n\tName\n\t, t.Description\n\t, t.Address AS adr;";
}
public void AddColSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Id")
.Col("Name")
.Col("Description", "Desc")
.Col("t.Address", "Addr");
var sql = maker.RawSql();
//var example = "SELECT\n\tId\n\t, Name\n\t, Description AS Desc\n\t, t.Address AS Addr;";
}
public void FromSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT("Id")
.Col("Name")
.Col("Description", "Desc")
.Col("t.Address", "Addr")
.FROM("table, dbo.table, [dbo].[Customer] cst");
var sql = maker.RawSql();
//var example = "SELECT\n\tId\n\t, Name\n\t, Description AS Desc\n\t, t.Address AS Addr\nFROM" + "\n\ttable\n\t, dbo.table\n\t, [dbo].[Customer] cst;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void EmptySchemeSelectTest()
{
var maker = QueryMaker.New()
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[Customer];";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void AddTableSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Table")
.Tab("Table", "Tab")
.Tab("Table", "Tab", "tbl");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Table]\n\t, [dbo].[Table] AS Tab\n\t, [tbl].[Table] AS Tab;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nWHERE\n\tZip = @zip AND Id >= @id;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereAndSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.WHERE("Zip = @zip")
.WhereAnd("Id >= @id");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nWHERE\n\tZip = @zip\n\tAND Id >= @id;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void WhereOrSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.WHERE("Zip = @zip")
.WhereOr("Id >= @id");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nWHERE\n\tZip = @zip\n\tOR Id >= @id;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void JoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.JOIN("Address", "addr");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nINNER JOIN [dbo].[Address] AS addr;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void LeftJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.LeftJoin("Address", "addr");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nLEFT JOIN [dbo].[Address] AS addr;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void RightJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.RightJoin("Address", "addr");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nRIGHT JOIN [dbo].[Address] AS addr;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void FullJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.FullJoin("Address", "addr");
var sql = maker.RawSql();
Console.WriteLine(sql);
Console.WriteLine("-------------------------------------------------------");
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nFULL JOIN [dbo].[Address] AS addr;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OnJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.JOIN("Address", "addr")
.ON("cst.Id = addr.Id");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer] AS cst" + "\nINNER JOIN [dbo].[Address] AS addr" + "\n\tON cst.Id = addr.Id;";
}
public void OnAndJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.JOIN("Address", "addr")
.ON("cst.Id = addr.Id")
.OnAnd("cst.Col = addr.Col");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer] AS cst"
+ "\nINNER JOIN [dbo].[Address] AS addr"
+ "\n\tON cst.Id = addr.Id"
+ "\n\tAND cst.Col = addr.Col;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OnOrJoinSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.JOIN("Address", "addr")
.ON("cst.Id = addr.Id")
.OnOr("cst.Col = addr.Col");
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM"
+ "\n\t[dbo].[Customer] AS cst"
+ "\nINNER JOIN [dbo].[Address] AS addr"
+ "\n\tON cst.Id = addr.Id"
+ "\n\tOR cst.Col = addr.Col;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OrderBySelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.ORDERBY("Id", SortAs.Desc);
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer] AS cst" + "\nORDER BY Id DESC;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void OrderThenSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.ORDERBY("Id", SortAs.Desc)
.OrderThen("Zip", SortAs.Asc);
var sql = maker.RawSql();
var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer] AS cst" + "\nORDER BY Id DESC, Zip ASC;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void GroupBySelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.GROUPBY("Id");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer] AS cst" + "\nGROUP BY Id;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void GroupThenSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer", "cst")
.GROUPBY("Id")
.GroupThen("Zip");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer] AS cst" + "\nGROUP BY Id, Zip;";
//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
public void HavingSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.GROUPBY("Zip")
.HAVING("COUNT(Id) >= @id");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nGROUP BY Zip" + "\nHAVING COUNT(Id) >= @id;";
}
public void HavingAndSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.GROUPBY("Zip")
.HAVING("COUNT(Id) >= @id")
.HavingAnd("MIN(Zip) = @zip");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nGROUP BY Zip" + "\nHAVING COUNT(Id) >= @id\n\tAND MIN(Zip) = @zip;";
}
public void HavingOrSelectTest()
{
var maker = QueryMaker.New(_dbScheme)
.SELECT()
.Col("Id", "Id")
.FROM()
.Tab("Customer")
.GROUPBY("Zip")
.HAVING("COUNT(Id) >= @id")
.HavingOr("MIN(Zip) = @zip");
var sql = maker.RawSql();
//var example = "SELECT\n\tId AS Id\nFROM" + "\n\t[dbo].[Customer]\nGROUP BY Zip" + "\nHAVING COUNT(Id) >= @id\n\tOR MIN(Zip) = @zip;";
}
}
Insert语句构建
public static class InsertSqlMaker
{
private static string _dbScheme;
public static void SetUp()
{
_dbScheme = "dbo"; // 默认MSSQL 架构
}
public static void InitInsert()
{
var maker = QueryMaker.New(_dbScheme).INSERT("Customer");
var sql = maker.RawSql();
//var example = "INSERT INTO [dbo].[Customer]";
}
public static void AddColumnTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address");
var sql = maker.RawSql();
//var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t)";
}
public static void ValuesTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address")
.VALUES("@name, @description, @address");
var sql = maker.RawSql();
//var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t)\n\tVALUES (\n\t\t@name\n\t\t, @description\n\t\t, @address\n\t);";
}
public static void AddValueTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address")
.Col("Zip")
.VALUES("@name, @description, @address").Param("zip");
var sql = maker.RawSql();
//var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t\t, [Zip]\n\t)\n\tVALUES (\n\t\t@name\n\t\t, @description\n\t\t, @address\n\t\t, @zip\n\t);";
}
public static void JustAddValueTest()
{
var maker = QueryMaker.New(_dbScheme)
.INSERT("Customer")
.Col("Name")
.Col("Description")
.Col("Address")
.Col("Zip")
.VALUES()
.Param("@name")
.Param("description")
.Param("@address")
.Param("zip");
var sql = maker.RawSql();
//var example = "INSERT INTO [dbo].[Customer] (\n\t\t[Name]\n\t\t, [Description]\n\t\t, [Address]\n\t\t, [Zip]\n\t)\n\tVALUES (\n\t\t@name\n\t\t, @description\n\t\t, @address\n\t\t, @zip\n\t);";
}}
Delete语句构建
publicstaticclass DeleteSqlMaker
{
publicstaticvoidSetUp()
{
QueryMaker.DbScheme = "dbo";
}
publicstaticvoidInitDeleteTest()
{
var maker = QueryMaker.New().DELETE("Customer");
var sql = maker.RawSql();
var example = "DELETE FROM [dbo].[Customer];";
}
publicstaticstringWhereDeleteTest()
{
var maker = QueryMaker.New().DELETE("Customer").WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
//var example = "DELETE FROM [dbo].[Customer]" + "\nWHERE Zip = @zip AND Id >= @id;";
Console.WriteLine(sql);
Console.WriteLine("-------------------------------------------------------");
return sql;
}
publicstaticstringWhereAndDeleteTest()
{
var maker = QueryMaker.New().DELETE("Customer").WHERE("Zip = @zip").WhereAnd("Id >= @id");
var sql = maker.RawSql();
//var example = "DELETE FROM [dbo].[Customer]" + "\nWHERE Zip = @zip\n\tAND Id >= @id;";
Console.WriteLine(sql);
return sql;
}
publicstaticstringWhereOrDeleteTest()
{
var maker = QueryMaker.New()
.DELETE("Customer")
.WHERE("Zip = @zip")
.WhereOr("Id >= @id");
var sql = maker.RawSql();
//var example = "DELETE FROM [dbo].[Customer]" + "\nWHERE Zip = @zip\n\tOR Id >= @id;";return sql;
}
}
Update语句构建
publicclass UpdateSqlMaker
{
publicvoidSetUp()
{
QueryMaker.DbScheme = "dbo";
}
publicvoidInitUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer];";//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
publicvoidSetUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id, Name = @name");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name;";//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
publicvoidValueSetUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET()
.Val("Id", "id")
.Val("Name", "name");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name;";
}
publicvoidWhereUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id")
.Val("Name", "name")
.WHERE("Zip = @zip AND Id >= @id");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name" + "\nWHERE Zip = @zip AND Id >= @id;";
}
publicvoidWhereAndUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id")
.Val("Name", "name")
.WHERE("Zip = @zip")
.WhereAnd("Id >= @id");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name" + "\nWHERE Zip = @zip\n\tAND Id >= @id;";//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
publicvoidWhereOrUpdateTest()
{
var maker = QueryMaker.New()
.UPDATE("Customer")
.SET("Id = @id")
.Val("Name", "name")
.WHERE("Zip = @zip")
.WhereOr("Id >= @id");
var sql = maker.RawSql();
//var example = "UPDATE [dbo].[Customer]\nSET\n\tId = @id\n\t, Name = @name" + "\nWHERE Zip = @zip\n\tOR Id >= @id;";//Assert.That(sql, Is.EqualTo(example).IgnoreCase);
}
}