public void T07_Db2Db_SqlServer() { string tblName = $"T03_Db2Db_{Guid.NewGuid().ToString().Substring(0, 8)}"; var connstr = Environment.GetEnvironmentVariable("SQLCONNSTRING"); // skip if no db config found Skip.If(string.IsNullOrWhiteSpace(connstr), "No database connection info found, is the environment variable 'SQLCONNSTRING' set?"); using (var utility = new Utility()) { try { utility.PrepareTestInstanceDir(); utility.PrepareSqlServerDb(tblName, connstr); // debug: get list of tables /* * var testCmd = utility.TestDbConnection.CreateCommand(); * testCmd.CommandText = "SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'"; * var tbl = new DataTable(); * using (var rd = testCmd.ExecuteReader()) * { * tbl.Load(rd); * } * string tablesStr = Utility.DumpDataTable(tbl); * Console.Write($"========== List of tables: {tablesStr} =========="); */ var config = new MaskConfig() { DataSource = DataSource.Database, DataDestination = DataDestination.Database, SqlSelect = $"SELECT * FROM {utility.TestTableName}", SqlCommand = $"UPDATE {utility.TestTableName} SET Name=@Name, Bankaccount=@Bankaccount, Address=@Address WHERE ID = @ID", OutputFile = Path.Combine(utility.TestInstanceDir, "T01_Db2Csv.csv") }; config.SetConnection(utility.TestDbConnection); config.FieldMasks.Add(new FieldMask() { FieldName = "ID", MaskType = MaskType.None }); config.FieldMasks.Add(new FieldMask() { FieldName = "Name", MaskType = MaskType.None }); config.FieldMasks.Add(new FieldMask() { FieldName = "BankAccount", MaskType = MaskType.Asterisk }); config.FieldMasks.Add(new FieldMask() { FieldName = "Address", MaskType = MaskType.List }); config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement() { Selector = "country=CH", ReplacementList = "Bahnhofstrasse 41,Hauptstrasse 8,Berggasse 4" }); config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement() { Selector = "country=GR", ReplacementList = "Evangelistrias 22,Thessalias 47,Eparhiaki Odos Lefkogion 6" }); config.FieldMasks[3].ListOfPossibleReplacements.Add(new Replacement() { Selector = "", ReplacementList = "Main Street 9,Fifth Avenue 104,Ranch rd. 1" }); var masker = new DataMasking(config); masker.Mask(); } catch (System.Data.SqlClient.SqlException ex) { // error 40 - could not open connection to sql server Skip.If(ex.Message.Contains("40"), $"Database seems not to respond, check if your SQL Server is running. {ex.Message}"); } } }
public void T08_Mask_MaskType_Query_Test() { // ===== Arrange string csvContent = "ID;Name;City;Country\r\n1;Roche;Basel;CH\r\n2;ABB;Baden;CH\r\n3;BMW;München;DE\r\n4;Barilla;Parma;IT\r\n5;FAGE;Athens;GR"; string csvFilename = this.utility.CreateFileInTestInstanceDir(csvContent, "csv"); var config = new MaskConfig() { InputFile = csvFilename, OutputFile = csvFilename.Replace(".csv", "_out.csv") }; config.SetConnection(utility.TestDbConnection); config.FieldMasks.Add(new FieldMask() { FieldName = "ID", MaskType = MaskType.None }); config.FieldMasks.Add(new FieldMask() { FieldName = "Name", MaskType = MaskType.Asterisk }); config.FieldMasks.Add(new FieldMask() { FieldName = "City", MaskType = MaskType.Query }); config.FieldMasks.Add(new FieldMask() { FieldName = "Country", MaskType = MaskType.None }); config.FieldMasks[2].QueryReplacement = new QueryReplacement() { Query = "SELECT city, country FROM cities", GroupDbField = "country", ValueDbField = "city", SelectorField = "Country" }; // prepare lookup table var cmdTbl = utility.TestDbConnection.CreateCommand(); cmdTbl.CommandText = @"CREATE TABLE cities AS SELECT 'Geneva' AS city, 'CH' as country UNION ALL SELECT 'Bern' AS city, 'CH' as country UNION ALL SELECT 'Thun' AS city, 'CH' as country UNION ALL SELECT 'Köln' AS city, 'DE' as country UNION ALL SELECT 'Berlin' AS city, 'DE' as country UNION ALL SELECT 'Hamburg' AS city, 'DE' as country UNION ALL SELECT 'Roma' AS city, 'IT' as country UNION ALL SELECT 'Venezia' AS city, 'IT' as country UNION ALL SELECT 'Milano' AS city, 'IT' as country UNION ALL SELECT 'Rethimno' AS city, 'GR' as country UNION ALL SELECT 'Trikala' AS city, 'GR' as country UNION ALL SELECT 'Patra' AS city, 'GR' as country"; cmdTbl.ExecuteNonQuery(); utility.TestTablesToDrop = "cities"; // ===== Act var masker = new DataMasking(config); masker.Mask(); // ===== Assert Assert.True(File.Exists(config.OutputFile)); var contents = new List <string>(File.ReadLines(config.OutputFile)); Assert.Equal(6, contents.Count); for (int i = 1; i < contents.Count; i++) { var items = contents[i].Split(';'); string id = items[0]; string city = items[2]; string country = items[3]; // id Assert.Equal(i.ToString(), id); // city switch (country) { case "CH": Assert.Contains <string>(city, new string[] { "Geneva", "Bern", "Thun" }); break; case "DE": Assert.Contains <string>(city, new string[] { "Köln", "Berlin", "Hamburg" }); break; case "IT": Assert.Contains <string>(city, new string[] { "Roma", "Venezia", "Milano" }); break; case "GR": Assert.Contains <string>(city, new string[] { "Rethimno", "Trikala", "Patra" }); break; default: throw new NotSupportedException($"Unexpected country {country} found"); } } }