public RunQuerySql ( List |
||
result | List |
|
cmd | string | |
Résultat | void |
private List <SQLQuerry> GetOracleQuerries(string sqlDialect, List <string> dbNames) { List <SQLQuerry> ret = new List <SQLQuerry>(); this.ProgressInfo.CreateProgress(); int iiDbCounter = 0; foreach (var dbName in dbNames) { this.ProgressInfo.SetProgressDone((double)100 * ++iiDbCounter / dbNames.Count, dbName); try { // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); List <string> sqls = this.GetSQLCommands(sqlDialect, "queries", replaces); // vem prvni select, procedury spojime dle cisla radku List <SQLResult> firstBlock = new List <SQLResult>(); DBExecutor.RunSql(firstBlock, sqls.FirstOrDefault()); StringBuilder wholeCode = new StringBuilder(512 * 1024); string queryName = string.Empty; string querySchema = string.Empty; string queryDatabase = string.Empty; string queryGroup = string.Empty; int counter = 0; int query_counter = 0; foreach (var item in firstBlock) { if (item.Column5.Equals("1")) // dump previous wholeCode { if (counter > 0) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = wholeCode.ToString(), name = queryName, groupName = queryGroup, database = queryDatabase, schema = querySchema }; ret.Add(querryItem); this.Log("Query done " + query_counter); query_counter++; } wholeCode.Length = 0; wholeCode.Append(item.Column0); queryName = item.Column1; queryGroup = item.Column2; querySchema = item.Column4; queryDatabase = item.Column3; } else { wholeCode.Append(item.Column0); queryName = item.Column1; querySchema = item.Column4; queryDatabase = item.Column3; queryGroup = item.Column2; } counter++; } sqls.RemoveAt(0); List <SQLResult> secondBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(secondBlock, sqls.FirstOrDefault()); foreach (var item in secondBlock) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = "CREATE OR REPLACE FORCE VIEW " + item.Column2 + " AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(querryItem); } sqls.RemoveAt(0); List <SQLResult> thirdBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(thirdBlock, sqls.FirstOrDefault()); foreach (var item in thirdBlock) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = "CREATE MATERIALIZED VIEW " + item.Column2 + " AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(querryItem); } } catch (Exception) { throw; } } this.ProgressInfo.RemoveProgress(); return(ret); }
private List <SQLQuery> GetOracleQuerries(string sqlDialect, List <string> dbNames, SQLDatabaseModel databaseModel) { List <SQLQuery> ret = new List <SQLQuery>(); bool firstSqlCommands = true; foreach (var dbName in dbNames) { //this.ProgressInfo.SetProgressDone((double)100* ++iiDbCounter / dbNames.Count, dbName); // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.QUERIES, firstSqlCommands, replaces); firstSqlCommands = false; try { // vem prvni select, procedury spojime dle cisla radku List <SQLResult> firstBlock = new List <SQLResult>(); DBExecutor.RunSql(firstBlock, sqls.FirstOrDefault()); StringBuilder wholeCode = new StringBuilder(512 * 1024); string queryName = string.Empty; string querySchema = string.Empty; string queryDatabase = string.Empty; string queryGroup = string.Empty; int counter = 0; int query_counter = 0; foreach (var item in firstBlock) { if (firstBlock.Count != 0) { this.ProgressInfo.SetProgressPercent(15 + 30 * (counter / firstBlock.Count), "Collecting queries."); } if (item.Column5.Equals("1")) // dump previous wholeCode { if (counter > 0) { SQLQuery queryItem = new SQLQuery() { sourceCode = wholeCode.ToString(), name = queryName, groupName = queryGroup, database = queryDatabase, schema = querySchema }; ret.Add(queryItem); Logger.Log("Query done " + query_counter); query_counter++; } wholeCode.Length = 0; wholeCode.Append(item.Column0); queryName = item.Column1; queryGroup = item.Column2; querySchema = item.Column4; queryDatabase = item.Column3; } else { wholeCode.Append(item.Column0); queryName = item.Column1; querySchema = item.Column4; queryDatabase = item.Column3; queryGroup = item.Column2; } counter++; } sqls.RemoveAt(0); List <SQLResult> secondBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(secondBlock, sqls.FirstOrDefault()); Logger.Log("Processing second block."); foreach (var item in secondBlock) { List <String> colArr = GetColumnsFromDbModel(databaseModel, item.Column1, item.Column4, item.Column3); if (colArr == null) { Logger.Log(String.Format("Skipping table {0}, columns not found in database model.", item.Column2)); continue; } for (int i = 0; i < colArr.Count; i++) { colArr[i] = String.Format("\"{0}\"", colArr[i]); } string columns = String.Join(",", colArr); SQLQuery queryItem = new SQLQuery() { sourceCode = "CREATE OR REPLACE FORCE VIEW " + item.Column2 + " (" + columns + ") AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); List <SQLResult> thirdBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(thirdBlock, sqls.FirstOrDefault()); Logger.Log("Processing third block."); foreach (var item in thirdBlock) { List <String> colArr = GetColumnsFromDbModel(databaseModel, item.Column1, item.Column4, item.Column3); if (colArr == null) { Logger.Log(String.Format("Skipping table {0}, columns not found in database model.", item.Column2)); continue; } for (int i = 0; i < colArr.Count; i++) { colArr[i] = String.Format("\"{0}\"", colArr[i]); } string columns = String.Join(",", colArr); SQLQuery queryItem = new SQLQuery() { sourceCode = "CREATE MATERIALIZED VIEW " + item.Column2 + " (" + columns + ") AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); // There could be custom code left while (sqls.Count != 0) { String sql = sqls.FirstOrDefault(); List <SQLResult> customBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(customBlock, sql); foreach (var item in customBlock) { SQLQuery queryItem = new SQLQuery() { sourceCode = item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); } } catch (Oracle.ManagedDataAccess.Client.OracleException oe) { Logger.Log("Last executed SQL dump:\n" + sqls.FirstOrDefault()); throw oe; } catch (Exception) { throw; } } return(ret); }