public void TestCreateSQL_WithJoin_SecondLevel_TwoBranches() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source branch1 = new Source("JoinBranch1", "MY_BRANCH_1"); Source branch2 = new Source("JoinBranch2", "MY_BRANCH_2"); Source.Join join = CreateAndAddJoin(source, joinSource); Source.Join branchJoin1 = CreateAndAddJoin(joinSource, branch1); Source.Join branchJoin2 = CreateAndAddJoin(joinSource, branch2); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, branch1, branch2)); //-------------Test Result ---------------------- Source.Join.JoinField joinField = join.JoinFields[0]; Source.Join.JoinField joinFieldBranch1 = branchJoin1.JoinFields[0]; Source.Join.JoinField joinFieldBranch2 = branchJoin2.JoinFields[0]; string expectedSql = string.Format("((({0} a1 JOIN {1} a2 ON a1.{4} = a2.{5}) JOIN {2} a3 ON a2.{6} = a3.{7}) JOIN {3} a4 ON a2.{8} = a4.{9})", sourceDB.EntityName, joinSource.EntityName, branch1.EntityName, branch2.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName, joinFieldBranch1.FromField.FieldName, joinFieldBranch1.ToField.FieldName, joinFieldBranch2.FromField.FieldName, joinFieldBranch2.ToField.FieldName); Assert.AreEqual(expectedSql, sql); }
public void ResetSourceDB() { SourceDB sourceDB = new SourceDB(); sourceDB.RunSQLScript("Reset"); Console.WriteLine("Resetted SourceDB"); }
public void TestCreateSQL_WithJoin_NoFields() { //-------------Setup Test Pack ------------------ string tableName = "MY_SOURCE"; Source source = new Source("MySource", tableName); string joinTableName = "MY_JOINED_TABLE"; Source joinSource = new Source("JoinSource", joinTableName); Source.Join join = new Source.Join(source, joinSource); source.Joins.Add(join); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- Exception exception = null; try { sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); } catch (Exception ex) { exception = ex; } //-------------Test Result ---------------------- Assert.IsNotNull(exception, "An error was expected when creating SQL with joins that have no fields"); Assert.IsInstanceOf(typeof(HabaneroDeveloperException), exception); string expectedMessage = string.Format("SQL cannot be created for the source '{0}' because it has a join to '{1}' without join fields", sourceDB.Name, join.ToSource.Name); StringAssert.Contains(expectedMessage, exception.Message); }
public void TestCreateSQL_WithJoin_TwoFields() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = CreateAndAddJoin(source, joinSource); QueryField fromField = new QueryField("FromField2", "FROM_FIELD2", source); QueryField toField = new QueryField("ToField2", "TO_FIELD2", joinSource); join.JoinFields.Add(new Source.Join.JoinField(fromField, toField)); Source.Join.JoinField joinField1 = join.JoinFields[0]; Source.Join.JoinField joinField2 = join.JoinFields[1]; SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); //-------------Test Result ---------------------- string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3} AND a1.{4} = a2.{5})", source.EntityName, joinSource.EntityName, joinField1.FromField.FieldName, joinField1.ToField.FieldName, joinField2.FromField.FieldName, joinField2.ToField.FieldName); Assert.AreEqual(expectedSql, sql); }
public int selectID(string sourceName) { sourceDB = new SourceDB(); source = new Source(); source.setName(sourceName); return(sourceDB.selectSourceId(source)); }
public void TestCreateSQL_WithInheritanceJoin_TwoLevels() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source joinSource2 = new Source("JoinSource2", "MY_JOINED_TABLE_2"); Source.Join join = CreateAndAddInheritanceJoin(source, joinSource); Source.Join join2 = CreateAndAddInheritanceJoin(joinSource, joinSource2); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, joinSource2)); //-------------Test Result ---------------------- Source.Join.JoinField joinField = join.JoinFields[0]; Source.Join.JoinField joinField2 = join2.JoinFields[0]; string expectedSql = string.Format("(({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3}) JOIN {4} a3 ON a2.{5} = a3.{6})", sourceDB.EntityName, joinSource.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName, joinSource2.EntityName, joinField2.FromField.FieldName, joinField2.ToField.FieldName); Assert.AreEqual(expectedSql, sql); }
public string selectSourceName(int ID) { source = new Source(); sourceDB = new SourceDB(); source.setID(ID); sourceDB.selectSourceName(source); return(source.getName()); }
private void BindSource() { List <Source> srcList = SourceDB.GetAllSource(); ddlSource.DataSource = srcList; ddlSource.DataTextField = "NAME"; ddlSource.DataValueField = "ID"; ddlSource.DataBind(); }
public string getSourcePhone3(string sourceName) { source = new Source(); sourceDB = new SourceDB(); int ID = this.selectID(sourceName); source.setID(ID); sourceDB.selectPhone3(source); return(source.getPhone3()); }
public string getSourceEmployee1(string sourceName) { source = new Source(); sourceDB = new SourceDB(); int ID = this.selectID(sourceName); source.setID(ID); sourceDB.selectEmployeeName1(source); return(source.getEmployeeName1()); }
public string getSourceAddress(string sourceName) { source = new Source(); sourceDB = new SourceDB(); int ID = this.selectID(sourceName); source.setID(ID); sourceDB.selectSourceAdress(source); return(source.getAddress()); }
public void TestCreateSQL_Simple() { //-------------Setup Test Pack ------------------ string tableName = "MY_SOURCE"; Source source = new Source("MySource", tableName); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source)); //-------------Test Result ---------------------- Assert.AreEqual(tableName + " a1", sql); }
public void TestCreateSQL_Simple_WithDelimiter() { //-------------Setup Test Pack ------------------ const string tableName = "MY_SOURCE"; Source source = new Source("MySource", tableName); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(new SqlFormatter("[", "]", "TOP", ""), CreateAliases(source)); //-------------Test Result ---------------------- Assert.AreEqual(string.Format("[{0}] a1", tableName), sql); }
public void fillComboboxSourceName(ComboBox combobox) { combobox.Items.Clear(); connection = new DBConnection(); sourceDB = new SourceDB(); SqlDataReader reader = sourceDB.fillComboboxSourceName(); while (reader.Read()) { combobox.Items.Add(reader["sourceName"]); } connection.close(); }
public void TestCreateSourceDB() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); //-------------Execute test --------------------- SourceDB sourceDB = new SourceDB(source); //-------------Test Result ---------------------- Assert.AreEqual(source.Name, sourceDB.Name); Assert.AreEqual(source.EntityName, sourceDB.EntityName); Assert.AreEqual(source.Joins.Count, sourceDB.Joins.Count); }
public void RecordDataMigrateJob(PerformContext context) { context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob Running ..."); // 从SourceDB查询出符合条件的FinaSettlementlist var finaSettlement10s = SourceDB.Queryable <Models.SourceDB.FinaSettlement>().Where(it => SqlFunc.Between(it.CreateDate, DateTime.Now.AddHours(-1), DateTime.Now)).ToList(); context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 查询SourceDB.AreaList 个数为" + finaSettlement10s.Count.ToString()); if (finaSettlement10s.Count == 0) { context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} 更新个数为" + finaSettlement10s.Count.ToString() + ",停止更新!"); } else { var progressBar = context.WriteProgressBar(); var insertConnt = 0; foreach (Models.SourceDB.FinaSettlement item in finaSettlement10s.WithProgress(progressBar)) { Models.TargetDB.FinaSettlement targetItem = new Models.TargetDB.FinaSettlement(); targetItem.ID = item.ID; targetItem.SerialNumber = item.SerialNumber; targetItem.ClerkID = item.ClerkID; targetItem.ClerkNum = item.ClerkNum; targetItem.Date = item.CreateDate; targetItem.Company = item.Company; targetItem.Reckoner = item.Reckoner; targetItem.Money = item.Money; targetItem.ClearingForm = item.ClearingForm; targetItem.OpeningBank = item.OpeningBank; targetItem.Account = item.Account; targetItem.PrintCount = item.PrintCount; targetItem.PaymentWay = item.PaymentWay; targetItem.Verifier = item.Verifier; targetItem.Status = item.Status; targetItem.Creater = item.Creater; targetItem.CreateDate = item.CreateDate; targetItem.UpdateDate = item.UpdateDate; targetItem.Remark = item.Remark; targetItem.FundContent = "0.0"; // 为什么一个一个插入都不行? int insert = TargetDB.Insertable(targetItem).ExecuteCommand(); insertConnt += insert; } context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} RecordDataMigrateJob 插入TargetDB.AreaList 个数为" + insertConnt); } context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} TrySqlSugarJob Running Over ..."); }
/// <summary> /// DataMigrateJob 数据迁移job /// </summary> /// <param name="context"></param> //[RecurringJob("*/1 * * * *")] //[System.ComponentModel.DisplayName("BaseDataMigrateJob")] //[Queue("jobs")] public void BaseDataMigrateJob(PerformContext context) { context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob Running ..."); var areaLists = SourceDB.Queryable <AreaList>().ToList(); context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 查询SourceDB.AreaList 个数为" + areaLists.Count.ToString()); var deleteCount = TargetDB.Deleteable <AreaList>().ExecuteCommand(); context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 删除TargetDB.AreaList 个数为" + deleteCount); var insertConnt = TargetDB.Insertable(areaLists.ToArray()).ExecuteCommand(); context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} DataMigrateJob 插入TargetDB.AreaList 个数为" + insertConnt); context.WriteLine($"{DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")} TrySqlSugarJob Running Over ..."); }
public void updateSource(string previousName, string name, string address, string EmployeeName1, string EmployeeName2, string phone1, string phone2, string phone3) { source = new Source(); sourceDB = new SourceDB(); int ID = this.selectID(previousName); source.setID(ID); source.setName(name); source.setEmployeeName1(EmployeeName1); source.setEmployeeName2(EmployeeName2); source.setAddress(address); source.setPhone1(phone1); source.setPhone2(phone2); source.setPhone3(phone3); sourceDB.update(source); }
public void TestCreateSQL_LeftJoin() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = CreateAndAddJoin(source, joinSource); join.JoinType = Source.JoinType.LeftJoin; SourceDB leftJoinSourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = leftJoinSourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); //-------------Test Result ---------------------- StringAssert.Contains("LEFT JOIN", sql); }
public void ProcessBatchFromSourceDBToResultDB(int amount) { int leftToDo = amount; int batch = 50; SourceDB sourceDB = new SourceDB(); int imageFilesInSourceDB = sourceDB.GetImageFilesCount(); ResultDB resultDB = new ResultDB(); int imageFilesInResultDB = resultDB.GetImageFilesCount(); int diffResultDBandSourceDB = imageFilesInSourceDB - imageFilesInResultDB; if (diffResultDBandSourceDB == 0) { Console.WriteLine("No new images in sourceDB to process"); return; } while (leftToDo > 0) { if (leftToDo <= batch) { batch = leftToDo; } if (leftToDo > diffResultDBandSourceDB) { batch = diffResultDBandSourceDB; } if (batch == 0) { break; } CopyJpgFiles(ETLcontroller.sourceDBDataFolder, ETLcontroller.stageDBDataUnporssedFolder, batch); InsertDataIntoStageDBFromUnprocessed(); ExtractMetadataIntoStageDB(); ImageRegonitionPrediction(); SortFilesInStageDBData(); MoveFilesFromStageDBDataToResultDBData(); LoadDataFromStageDBtoResultDB(); ResetStageDB(); leftToDo -= 50; } }
public void TestCreateSourceDB_WithJoins() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = new Source.Join(source, joinSource); source.Joins.Add(join); //-------------Execute test --------------------- SourceDB sourceDB = new SourceDB(source); //-------------Test Result ---------------------- Assert.AreEqual(source.Name, sourceDB.Name); Assert.AreEqual(source.EntityName, sourceDB.EntityName); Assert.AreEqual(source.Joins, sourceDB.Joins); }
public void TestCreateSQL_WithJoin() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = CreateAndAddJoin(source, joinSource); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); //-------------Test Result ---------------------- Source.Join.JoinField joinField = join.JoinFields[0]; string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3})", source.EntityName, joinSource.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName); Assert.AreEqual(expectedSql, sql); }
public void TestCreateSQL_WithJoin_WithDelimiter() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = CreateAndAddJoin(source, joinSource); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- SqlFormatter myFormatter = new SqlFormatter("[", "]", "TOP", ""); string sql = sourceDB.CreateSQL(myFormatter, CreateAliases(source, joinSource)); //-------------Test Result ---------------------- Source.Join.JoinField joinField = join.JoinFields[0]; string expectedSql = string.Format("([{0}] a1 JOIN [{1}] a2 ON a1.[{2}] = a2.[{3}])", source.EntityName, joinSource.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName); Assert.AreEqual(expectedSql, sql); }
public void CopyJpgFiles(string sourceFilePath, string destFilePath, int amount) { FileMover fileMover = new FileMover(); StreamReader sr = new StreamReader(StageDB.stageDBDataLog); string line = sr.ReadLine(); int lastImageFileIndex = 0; if (line != null) { lastImageFileIndex = Int32.Parse(line); } sr.Close(); for (int i = 1; i <= amount; i++) { SourceDB sourceDB = new SourceDB(); ImageFile imageFile = sourceDB.GetImageFile(lastImageFileIndex + i); fileMover.CopyImageFile(imageFile, stageDBDataUnporssedFolder); } if (lastImageFileIndex == 0) { lastImageFileIndex = amount; } else { lastImageFileIndex += amount; } fileMover.UpdateTxtFile(lastImageFileIndex.ToString(), StageDB.stageDBDataLog); //StageDB.stageDBDataLog //fileMover.CopyJpgFiles(sourceFilePath, destFilePath, amount); }
public Boolean insertSource(string name, string address, string EmployeeName1, string EmployeeName2, string phone1, string phone2, string phone3) { source = new Source(); sourceDB = new SourceDB(); source.setName(name); source.setEmployeeName1(EmployeeName1); source.setEmployeeName2(EmployeeName2); source.setAddress(address); source.setPhone1(phone1); source.setPhone2(phone2); source.setPhone3(phone3); check = sourceDB.checkSource(source); if (check == true) { return(false); } else { sourceDB.insert(source); return(true); } }
public void Test_CreateSQL_ShouldUseAliasesInJoins() { //---------------Set up test pack------------------- var mysource = new Source("mysource"); QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource); Source joinedTableSource = new Source("myjoinedtosource"); QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource); mysource.Joins.Add(new Source.Join(mysource, joinedTableSource)); mysource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource)); SourceDB sourceDB = new SourceDB(mysource); SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT"); IDictionary <string, string> aliases = new Dictionary <string, string>() { { mysource.ToString(), "a1" }, { joinedTableSource.ToString(), "a2" } }; //---------------Execute Test ---------------------- string sql = sourceDB.CreateSQL(sqlFormatter, aliases); //---------------Test Result ----------------------- StringAssert.AreEqualIgnoringCase( "([mysource] a1 JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield])", sql); }
private void AppendFrom(StringBuilder builder) { SourceDB source = new SourceDB(_selectQuery.Source); builder.AppendFormat(" FROM {0}", source.CreateSQL(_sqlFormatter, Aliases)); }
public void InsertDataIntoSourceDB() { SourceDB sourceDB = new SourceDB(); sourceDB.InsertAllImageFilesFromFolder(sourceDBDataFolder); }