public void ExportTable(Table table, string path, ConnectionString connectionString) { try { if (File.Exists(path)) File.Delete(path); StringBuilder sb = new StringBuilder(); sb.Append("SET NOCOUNT ON\r\n\r\n"); table = _dataGenerationService.GenerateDataForTable(connectionString, table, true); string sql = _sqlGenerationService.GenerateUpdateSqlForTable(table, true); sb.Append(sql); sb.Append("SET NOCOUNT OFF\r\n\r\n"); using (StreamWriter writer = new StreamWriter(path)) { writer.Write(sb.ToString()); } } catch (Exception ex) { Logging.LogException(ex); throw; } }
public List<DataTable> GetPreviewDataForTable(Table table, ConnectionString conntectionString) { List<DataTable> previewData = new List<DataTable>(); DataTable originalData = _databaseProvider.GetPreviewData(conntectionString, table); previewData.Add(originalData); return previewData; }
public ObfuscationWorker(ISqlGenerationService sqlGenerationService, IDataGenerationService dataGenerationService, IDatabaseInteractionService databaseInteractionService, IEventAggregator eventAggregator, ConnectionString connectionString, Table table) { _sqlGenerationService = sqlGenerationService; _dataGenerationService = dataGenerationService; _databaseInteractionService = databaseInteractionService; _eventAggregator = eventAggregator; _table = table; _connectionString = connectionString; }
public ObfuscationResult ObfuscateTable(ConnectionString connectionString, Table table) { ObfuscationResult result = new ObfuscationResult(); result.DatabaseName = connectionString.DatabaseName; result.StartTimeStamp = DateTime.Now; if (table.AreAnyGeneratorsActive) { Table newTable = _dataGenerationService.GenerateDataForTable(connectionString, table, true); string sql = _sqlGenerationService.GenerateUpdateSqlForTable(newTable, false); if (String.IsNullOrEmpty(sql) == false) { _eventAggregator.SendMessage<StatusUpdateEvent>(new StatusUpdateEvent(string.Format("Processing SQL query for table: {0}", table.FullTableName))); int rowsProcessed = _databaseInteractionService.ProcessSql(connectionString, sql); result.TablesProcessed.Add(table.FullTableName, rowsProcessed); } } result.FinsihedTimeStamp = DateTime.Now; return result; }
public List<Table> GetSchemaInformation(string connectionInfo) { List<Table> tables = new List<Table>(); OleDbConnection con = new OleDbConnection(connectionInfo); con.Open(); // Get all tables in the Database DataTable tableData = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in tableData.Rows) { Table t = new Table(); t.Schema = row[1].ToString(); t.Name = row[2].ToString(); // Get all columns in the Table DataTable columns = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, row[2], null }); foreach (DataRow row1 in columns.Rows) { Column c = new Column(); c.Name = row1[3].ToString(); c.DataType = (OleDbType)row1["DATA_TYPE"]; c.IsNullable = (bool)row1["IS_NULLABLE"]; if (row1["CHARACTER_MAXIMUM_LENGTH"] != null && !String.IsNullOrEmpty(row1["CHARACTER_MAXIMUM_LENGTH"].ToString())) c.MaxLength = int.Parse(row1["CHARACTER_MAXIMUM_LENGTH"].ToString()); t.Columns.Add(c); } // Get all the Primary Key Columns for the Table DataTable primaryKeys = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, row[1], row[2] }); foreach (DataRow row2 in primaryKeys.Rows) { t.Columns.Where(x => x.Name == row2[3].ToString()).First().IsPrimaryKey = true; //t.Columns.Where(x => x.Name == row2[3].ToString()).First().PrimaryKeys = GetPrimaryKeysForTable(connectionInfo, t); } t.RecordCount = GetRecordCount(connectionInfo, t); tables.Add(t); } // Get all the Forign keys and table contraints foreach (Table t in tables) { DataTable forignKeys = con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, t.Schema, t.Name }); foreach (DataRow row in forignKeys.Rows) { var table = tables.Where(x => x.Name == row[8].ToString()).First(); // TODO: I think this needs schema + table name table.Columns.Where(x => x.Name == row[9].ToString()).First().IsForignKey = true; } DataTable constraints = con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints_By_Table, new object[] { null, t.Schema, t.Name }); foreach (DataRow row in constraints.Rows) { var table = tables.Where(x => x.Name == row["TABLE_NAME"].ToString()).First(); // TODO: I think this needs schema + table name List<string> columnNames = GetTableNamesFromSql(row["CHECK_CLAUSE"].ToString()); foreach (string column in columnNames) { Column c = table.Columns.Where(x => x.Name.Equals(column, StringComparison.OrdinalIgnoreCase)).FirstOrDefault(); if (c != null) c.IsPartOfConstraint = true; } } } con.Close(); return tables; }
public double GetRecordCount(string connectionInfo, Table table) { double recordCount = 0; Column primaryKey = table.GetPrimaryKeyColumn(); OleDbConnection con = new OleDbConnection(connectionInfo); OleDbCommand command = con.CreateCommand(); command.CommandText = string.Format("SELECT COUNT(*) FROM [{0}].[{1}]", table.Schema, table.Name); command.CommandTimeout = 0; try { con.Open(); object returnValue = command.ExecuteScalar(); if (returnValue != null && returnValue.ToString().Length > 0) recordCount = double.Parse(returnValue.ToString()); } finally { con.Close(); } return recordCount; }
public List<string> GetPrimaryKeysForTable(string connectionInfo, Table table) { List<string> ids = new List<string>(); Column primaryKey = table.GetPrimaryKeyColumn(); OleDbConnection con = new OleDbConnection(connectionInfo); OleDbCommand command = con.CreateCommand(); command.CommandText = string.Format("SELECT [{0}] FROM [{1}]", primaryKey.Name, table.FullTableName); command.CommandTimeout = 0; try { con.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { ids.Add(reader[0].ToString()); } } finally { con.Close(); } return ids; }
public DataTable GetPreviewData(ConnectionString connectionString, Table table) { DataTable returnData = new DataTable(); OleDbConnection con = new OleDbConnection(connectionString.GetConnectionString()); OleDbCommand command = con.CreateCommand(); command.CommandText = string.Format("SELECT TOP 1000 * FROM {0}", table.FullTableName); try { con.Open(); IDataReader reader = command.ExecuteReader(); returnData.Load(reader); } finally { con.Close(); } return returnData; }
public HashSet<string> GetDataForColumn(string connectionInfo, Table table, Column column) { HashSet<string> data = new HashSet<string>(); OleDbConnection con = new OleDbConnection(connectionInfo); OleDbCommand command = con.CreateCommand(); command.CommandText = string.Format("SELECT [{0}] FROM [{1}]", column.Name, table.FullTableName); command.CommandTimeout = 0; try { con.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { data.Add(reader[0].ToString().Trim().ToUpper()); } } finally { con.Close(); } return data; }
public string GenerateUpdateSqlForTable(Table table, bool isUserRun) { if (table.HandlerType != TableHandlerTypes.None) { _eventAggregator.SendMessage<StatusUpdateEvent>(new StatusUpdateEvent(string.Format("Generating SQL for table: {0}", table.FullTableName))); StringBuilder sql1 = new StringBuilder(); if (table.HandlerType == TableHandlerTypes.Drop) { sql1.Append(string.Format("IF OBJECT_ID('{0}') IS NOT NULL \r\n", table.FullTableName)); sql1.Append(string.Format(" DROP TABLE {0} \r\n\r\n", table.FullTableName)); if (isUserRun) sql1.Append("GO\r\n\r\n"); } else { sql1.Append(string.Format("IF OBJECT_ID('{0}') IS NOT NULL \r\n", table.FullTableName)); sql1.Append(string.Format(" DELETE FROM {0} \r\n\r\n", table.FullTableName)); if (isUserRun) sql1.Append("GO\r\n\r\n"); } return sql1.ToString(); } if (table.AreAnyGeneratorsActive == false) return String.Empty; if (table.RecordCount <= 0) return String.Empty; StringBuilder sql = new StringBuilder(); Column primaryKey = table.GetPrimaryKeyColumn(); List<Column> activeColumns = table.Columns.Where(x => x.GeneratorType != null && x.GeneratorType != SystemConstants.DefaultGuid).ToList(); List<Column> activeColumnsNonClear = activeColumns.Where(x => x.GeneratorType != SystemConstants.ClearGeneratorGuid).ToList(); List<Column> activeColumnsOnlyClear = activeColumns.Where(x => x.GeneratorType == SystemConstants.ClearGeneratorGuid).ToList(); string tableNameOnly = table.Schema + table.Name; string tableName = tableNameOnly + "ObfuscateData"; if (activeColumnsNonClear.Count > 0) { if (isUserRun) sql.Append(string.Format("\r\nPRINT '########## [' + CONVERT(VARCHAR, GETDATE()) + ']: Starting setting up temp Obfuscation data for {0}' \r\nGO\r\n\r\n", table.FullTableName)); // Create generated data temp table structre sql.Append(string.Format("CREATE TABLE #{0} \r\n ( \r\n", tableName)); sql.Append(" Id bigint \r\n"); foreach (Column c in activeColumnsNonClear) { sql.Append(string.Format(" ,[{0}] VARCHAR(MAX) \r\n", c.Name)); } sql.Append(") \r\n"); sql.Append(" \r\n"); // Populate the generated data temp table sql.Append(string.Format("INSERT INTO #{0} \r\n", tableName)); sql.Append(" (Id, "); for (int i = 0; i < activeColumnsNonClear.Count(); i++) { if (i == 0) sql.Append(string.Format("[{0}]", activeColumnsNonClear[i].Name)); else sql.Append(string.Format(", [{0}]", activeColumnsNonClear[i].Name)); } sql.Append(") \r\n"); for (int i = 0; i < table.RecordCount; i++) { sql.Append(" SELECT"); for (int j = 0; j < activeColumnsNonClear.Count(); j++) { try { if (j == 0) sql.Append(string.Format(" {0}", i)); sql.Append(string.Format(", '{0}'", activeColumnsNonClear[j].Data[i].Trim())); } catch (Exception ex) { Logging.LogError(string.Format("Error trying to add data for table {0}, column {1}, data index {2}, total count {3}", table.FullTableName, activeColumnsNonClear[j].Name, i, activeColumnsNonClear[j].Data.Count)); Logging.LogException(ex); throw; } } if (i != table.RecordCount - 1) sql.Append("\r\n UNION ALL \r\n"); } if (isUserRun) sql.Append(string.Format("\r\nPRINT '########## [' + CONVERT(VARCHAR, GETDATE()) + ']: Finished setting up temp Obfuscation data for {0}' \r\nGO\r\n\r\n", table.FullTableName)); sql.Append(" \r\n"); sql.Append(" \r\n"); } if (isUserRun) sql.Append(string.Format("\r\nPRINT '########## [' + CONVERT(VARCHAR, GETDATE()) + ']: Starting Obfuscating table: {0}' \r\nGO\r\n\r\n", table.FullTableName)); sql.Append(string.Format("DECLARE @{0}RecId BIGINT \r\n", tableNameOnly)); sql.Append(string.Format("DECLARE @{0}RowNum BIGINT \r\n", tableNameOnly)); sql.Append(string.Format("SELECT TOP 1 @{0}RecId={1} FROM [{2}].[{3}] ORDER BY {1} ASC \r\n", tableNameOnly, primaryKey.Name, table.Schema, table.Name)); sql.Append(string.Format("SET @{0}RowNum = 0 \r\n", tableNameOnly)); sql.Append(string.Format("WHILE @{0}RowNum < {1} \r\n", tableNameOnly, table.RecordCount)); sql.Append("BEGIN \r\n"); sql.Append(string.Format(" UPDATE [{0}].[{1}] \r\n", table.Schema, table.Name)); for (int i = 0; i < activeColumns.Count(); i++) { if (i == 0) if (activeColumns[i].GeneratorType == SystemConstants.ClearGeneratorGuid) { if (activeColumns[i].GeneratorData != null && activeColumns[i].GeneratorData.Count > 0 && (bool)activeColumns[i].GeneratorData[0]) sql.Append(string.Format(" SET [{0}] = NULL \r\n", activeColumns[i].Name)); else sql.Append(string.Format(" SET [{0}] = '' \r\n", activeColumns[i].Name)); } else sql.Append(string.Format(" SET [{0}] = (SELECT {0} FROM #{1} WHERE Id = @{2}RowNum) \r\n", activeColumns[i].Name, tableName, tableNameOnly)); else if (activeColumns[i].GeneratorType == SystemConstants.ClearGeneratorGuid) { if (activeColumns[i].GeneratorData != null && activeColumns[i].GeneratorData.Count > 0 && (bool)activeColumns[i].GeneratorData[0]) sql.Append(string.Format(" ,[{0}] = NULL \r\n", activeColumns[i].Name)); else sql.Append(string.Format(" ,[{0}] = '' \r\n", activeColumns[i].Name)); } else sql.Append(string.Format(" ,[{0}] = (SELECT {0} FROM #{1} WHERE Id = @{2}RowNum) \r\n", activeColumns[i].Name, tableName, tableNameOnly)); } sql.Append(string.Format(" WHERE [{0}] = @{1}RecId \r\n", primaryKey.Name, tableNameOnly)); sql.Append(string.Format(" SET @{0}RowNum = @{0}RowNum + 1 \r\n", tableNameOnly)); sql.Append(string.Format(" SELECT TOP 1 @{0}RecId={1} FROM [{2}].[{3}] WHERE {1} > @{0}RecId ORDER BY {1} ASC \r\n", tableNameOnly, primaryKey.Name, table.Schema, table.Name)); sql.Append("END \r\n"); if (isUserRun) sql.Append("GO \r\n"); sql.Append("\r\n"); if (activeColumnsNonClear.Count > 0) { sql.Append(string.Format("DROP TABLE #{0}\r\n", tableName)); if (isUserRun) sql.Append("GO \r\n"); sql.Append("\r\n"); } if (isUserRun) sql.Append(string.Format("\r\nPRINT '########## [' + CONVERT(VARCHAR, GETDATE()) + ']: Finished Obfuscating table: {0}' \r\nGO\r\n\r\n", table.FullTableName)); return sql.ToString(); }
public double GetRecrodCountForTable(ConnectionString connectionString, Table table) { return _databaseProvider.GetRecordCount(connectionString.GetConnectionString(), table); }
public HashSet<string> GetDataForColumn(ConnectionString connectionString, Table table, Column column) { return _databaseProvider.GetDataForColumn(connectionString.GetConnectionString(), table, column); }
public void ExportTestTable(Table table, string path, ConnectionString connectionString) { try { if (File.Exists(path)) File.Delete(path); StringBuilder sb = new StringBuilder(); sb.Append("BEGIN TRANSACTION\r\n\r\n"); sb.Append("SET NOCOUNT ON\r\n\r\n"); if (table.RecordCount > 1000) table.RecordCount = 1000; table = _dataGenerationService.GenerateDataForTable(connectionString, table, false); string sql = _sqlGenerationService.GenerateUpdateSqlForTable(table, true); sb.Append(sql); sb.Append("SET NOCOUNT OFF\r\n\r\n"); sb.Append("\r\n\r\nROLLBACK TRANSACTION\r\n\r\n"); using (StreamWriter writer = new StreamWriter(path)) { writer.Write(sql); } } catch (Exception ex) { Logging.LogException(ex); throw; } }
protected void OnSelectedTableChanged(object sender, RoutedPropertyChangedEventArgs <object> e) { WaveTech.Dafuscator.Model.Table t = e.NewValue as WaveTech.Dafuscator.Model.Table; SelectedTable = t; }
public Table GenerateDataForTable(ConnectionString connectionString, Table table, bool updateRecordCount) { if (table.AreAnyGeneratorsActive && table.HandlerType == TableHandlerTypes.None) { _eventAggregator.SendMessage<StatusUpdateEvent>(new StatusUpdateEvent(string.Format("Generating Data for table: {0}", table.FullTableName))); for (int i = 0; i < table.Columns.Count; i++) { if (table.Columns[i].GeneratorType.HasValue && (table.Columns[i].GeneratorType.Value.Equals(SystemConstants.DefaultGuid) == false)) { List<object> generationData = new List<object>(); double recordCount = 0; if (updateRecordCount) { try { recordCount = _databaseInteractionService.GetRecrodCountForTable(connectionString, table); } catch { } } if (recordCount <= 0) recordCount = table.RecordCount; else table.RecordCount = recordCount; generationData.Add(recordCount); foreach (object o in table.Columns[i].GeneratorData) { if (o != null) generationData.Add(o); } HashSet<string> existingColumnData = new HashSet<string>(); try { existingColumnData = _databaseInteractionService.GetDataForColumn(connectionString, table, table.Columns[i]); } catch { } table.Columns[i] = GenerateDataForColumn(table.Columns[i], generationData.ToArray(), existingColumnData); } } } return table; }