Exemplo n.º 1
0
        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;
            }
        }
Exemplo n.º 2
0
        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;
        }
Exemplo n.º 3
0
        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;
        }
Exemplo n.º 4
0
        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;
        }
Exemplo n.º 5
0
        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;
        }
Exemplo n.º 6
0
        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;
        }
Exemplo n.º 7
0
        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;
        }
Exemplo n.º 8
0
        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;
        }
Exemplo n.º 9
0
        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;
        }
Exemplo n.º 10
0
        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);
 }
Exemplo n.º 13
0
        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;
            }
        }
Exemplo n.º 14
0
 protected void OnSelectedTableChanged(object sender, RoutedPropertyChangedEventArgs <object> e)
 {
     WaveTech.Dafuscator.Model.Table t = e.NewValue as WaveTech.Dafuscator.Model.Table;
     SelectedTable = t;
 }
Exemplo n.º 15
0
        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;
        }