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;
}
