private static async Task TestSchemaCompare()
{
Stopwatch sw = new Stopwatch();
var dbA = new Database();
var dbB = new Database();
sw.Start();
int numLoops = 1;
for (int i = 0; i < numLoops; i++)
{
await RegisterDatabasesAsync(dbA, dbB);
//RegisterDatabases(dbA, dbB);
}
sw.Stop();
Console.WriteLine($"Took {sw.ElapsedMilliseconds / numLoops}ms to register DB's");
sw.Restart();
var differences = dbA.CompareWith(dbB);
sw.Stop();
Console.WriteLine($"Took {sw.ElapsedMilliseconds}ms to compare DB's");
var scriptBuilder = new SqlScriptBuilder();
sw.Restart();
var scriptBlocks = scriptBuilder.GenerateScript(differences, ScriptDirection.FromBToA, Options.Default);
StringBuilder script = new StringBuilder();
foreach (var block in scriptBlocks)
{
var sqlString = block.ToString().TrimEnd();
script.AppendLine(sqlString);
if (!sqlString.EndsWith("GO"))
{
script.AppendLine("GO");
}
script.AppendLine();
}
sw.Stop();
Console.WriteLine($"Took {sw.ElapsedMilliseconds}ms to build update script");
File.WriteAllText(OutputDir, script.ToString());
Console.WriteLine("Finished");
Console.ReadKey();
}
/// <summary>
/// 利用[ROW_NUMBER() over]分页，生成sql语句
/// </summary>
/// <param name="tableName">表名称『eg:Orders』</param>
/// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1』</param>
/// <param name="orderType">升序降序『1：desc;其他:asc』</param>
/// <param name="pSize">每页页数『需大于零』</param>
/// <param name="pIndex">页数『从壹开始算』</param>
/// <returns>生成分页sql脚本</returns>
public static string JoinPageSQLByRowNumber(string tableName, string columns, string orderColumn, string sqlWhere, OrderType orderType, int pSize, int pIndex)
{
int _pageStart = pSize * (pIndex - 1) + 1;
int _pageEnd = pSize * pIndex + 1;
string _sql = string.Format("select * from (select (ROW_NUMBER() over(order by {2} {3})) as ROWNUMBER,{1} from {0})as tp where ROWNUMBER >= {4} and ROWNUMBER< {5} ",
tableName,
columns,
orderColumn,
orderType == OrderType.Desc ? "desc" : "asc",
_pageStart,
_pageEnd);
_sql = SqlScriptBuilder.JoinQueryWhereSql(_sql, sqlWhere);
_sql = SqlScriptBuilder.JoinQueryTotalSql(_sql, tableName);
return(_sql);
}
/// <summary>
/// 利用[ROW_NUMBER() over]分页，生成sql语句
/// </summary>
/// <param name="tableName">表名称『eg:Orders』</param>
/// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1』</param>
/// <param name="orderType">升序降序『1：desc;其他:asc』</param>
/// <param name="pSize">每页页数『需大于零』</param>
/// <param name="pIndex">页数『从壹开始算』</param>
/// <returns>生成分页sql脚本</returns>
public static string JoinPageSQLByRowNumber(string tableName, string columns, string orderColumn, string sqlWhere, OrderType orderType, int pSize, int pIndex)
{
int _pageStart = pSize * (pIndex - 1) + 1;
int _pageEnd = pSize * pIndex + 1;
string _sql = string.Format(@"select {1} from (SELECT A.*, ROWNUM RN FROM (SELECT * FROM {0} order by {2} {3}) A ) WHERE RN BETWEEN {4} AND {5} "
, tableName
, columns
, orderColumn
, orderType == OrderType.Desc ? "desc" : "asc"
, _pageStart
, _pageEnd);
_sql = SqlScriptBuilder.JoinQueryWhereSql(_sql, sqlWhere);
_sql = SqlScriptBuilder.JoinQueryTotalSql(_sql, tableName);
return(_sql);
}
public override void EndCollectData()
{
_locked = true;
foreach (StorageCommand command in _list)
{
string script;
switch (command.CommandType)
{
case StorageCommandType.Insert:
script = _builders[command.EntityType].CreateInsertStatement(command.Entities);
break;
case StorageCommandType.Update:
script = _builders[command.EntityType].CreateUpdateStatement(command.Entities);
break;
default:
script = _builders[command.EntityType].CreateDeleteStatement(command.Entities);
break;
}
_script += script + "\r\n\r\n";
}
string clearMtm = "";
string addMtm = "";
foreach (PropertyDescription pd in _mtmRelationships.Keys)
{
clearMtm += SqlScriptBuilder.CreateMtmDelete(pd, _ps, _mtmRelationships[pd]) + "\r\n";
addMtm += SqlScriptBuilder.CreateMtmInsert(pd, _ps, _mtmRelationships[pd]) + "\r\n";
}
_script = clearMtm + "\r\n\r\n" + _script + "\r\n\r\n" + addMtm;
string guid = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
_script = "DECLARE @status int\r\nDECLARE @msg VARCHAR(255)\r\n" +
"BEGIN TRAN savetran" + guid + "\r\n\r\n" +
"BEGIN TRY\r\n\r\n" +
_script +
"SELECT @status = 1\r\nCOMMIT TRAN savetran" + guid + "\r\n" +
"END TRY\r\n" +
"BEGIN CATCH\r\nROLLBACK TRAN savetran" + guid + "\r\n" +
"SELECT @status = 0\r\n" +
"SET @Msg =ERROR_MESSAGE()\r\nRAISERROR(@Msg,16,1)\r\nEND CATCH\r\nSELECT 'SUCCESS'";
}
/// <summary>
/// 利用[Top NotIn]分页，生成sql语句
/// </summary>
/// <param name="tableName">表名称『eg:Orders』</param>
/// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1』</param>
/// <param name="orderType">升序降序『1：desc;其他:asc』</param>
/// <param name="pSize">每页页数『需大于零』</param>
/// <param name="pIndex">页数『从壹开始算』</param>
/// <returns>生成分页sql脚本</returns>
public static string TablePageSQLByTopNotIn(string tableName, string columns, string orderColumn, string sqlWhere, OrderType orderType, int pSize, int pIndex)
{
/*
* eg:
* 1=>SELECT orderID FROM Orders ORDER BY orderID
* 2=>SELECT TOP 20 orderID FROM Orders ORDER BY orderID //查询前一页数据
* 3=> SELECT TOP 10 * FROM Orders WHERE (orderID NOT IN (SELECT TOP 20 orderID FROM Orders ORDER BY orderID)) ORDER BY orderID //在所有数据中，截去掉上一页数据(not in)，然后select top 10 即当前页数据
*/
string _sql = string.Format("SELECT TOP {4} {1} FROM {0} WHERE ({2} NOT IN (SELECT TOP {5} {2} FROM {0} ORDER BY {2} {3})) ORDER BY {2} {3}",
tableName,
columns,
orderColumn,
orderType == OrderType.Desc ? "desc" : "asc",
pSize,
(pIndex - 1) * pSize);
_sql = SqlScriptBuilder.JoinQueryWhereSql(_sql, sqlWhere);
_sql = SqlScriptBuilder.JoinQueryTotalSql(_sql, tableName);
return(_sql);
}
/// <summary>
/// 利用[Top Max]分页，生成sql语句
/// </summary>
/// <param name="tableName">表名称『eg:Orders』</param>
/// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
/// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
/// <param name="sqlWhere">筛选条件『eg:Order=1』</param>
/// <param name="orderType">升序降序『1：desc;其他:asc』</param>
/// <param name="pSize">每页页数『需大于零』</param>
/// <param name="pIndex">页数『从壹开始算』</param>
/// <returns>生成分页sql脚本</returns>
public static string TablePageSQLByTopMax(string tableName, string columns, string orderColumn, string sqlWhere, OrderType orderType, int pSize, int pIndex)
{
/*
* eg:
* 1=>select top 30 orderID from Orders order by orderID asc
* 2=>(select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T) //查询前一页数据
* 3=> select top 15 OrderID,OrderDate,ShipName,ShipCountry from Orders where orderID>
* ISNULL((select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T),0)
* order by orderID asc
*/
string _sql = string.Format("select top {4} {1} from {0} where {2}> ISNULL((select max ({2}) from (select top {5} {2} from {0} order by {2} {3}) as T),0) order by {2} {3}",
tableName,
columns,
orderColumn,
orderType == OrderType.Desc ? "desc" : "asc",
pSize,
(pIndex - 1) * pSize);
_sql = SqlScriptBuilder.JoinQueryWhereSql(_sql, sqlWhere);
_sql = SqlScriptBuilder.JoinQueryTotalSql(_sql, tableName);
return(_sql);
}
