public static string GenerateColumnTypeSqlQuery(ITableInfo tableInfo, List <string> columnNames)
{
var columnNameTable = new ColumnListDataTable();
foreach (var columnName in columnNames)
{
columnNameTable.Rows.Add(columnName);
}
var getColumnTypesQueryParams = new SqlParameter[]
{
new SqlParameter()
{
ParameterName = SqlParameterStore.columnsTableParam, SqlDbType = SqlDbType.Structured,
Value = columnNameTable, TypeName = ColumnListDataTable.dbTypeName
},
new SqlParameter(SqlParameterStore.dbNameParam, tableInfo.DbName),
new SqlParameter(SqlParameterStore.schemaNameParam, tableInfo.SchemaName),
new SqlParameter(SqlParameterStore.tableNameParam, tableInfo.TableName)
};
var resultTable = new DataTable();
resultTable = SqlHelper.ExecuteQuery(tableInfo.DbConnectionString, getColumnTypesQueryParams, Resources.Get_Column_Types);
var resultStr = resultTable.Rows[0][0] as string;
if (resultStr == null)
{
throw new Exception("The result query returned a null value.");
}
return(resultStr);
}
public static bool Scramble(ITableInfo tableInfo)
{
var constantColumnsAndValuesTable = new ColumnAndValueListDataTable();
foreach (var columnValuePair in tableInfo.ConstantColumnsAndValues)
{
constantColumnsAndValuesTable.Rows.Add(columnValuePair.Key, columnValuePair.Value);
}
var scrambledColumnsTable = new ColumnListDataTable();
foreach (var column in tableInfo.ScrambledColumns)
{
scrambledColumnsTable.Rows.Add(column);
}
var getScramblingScriptParams = new List <SqlParameter>
{
new SqlParameter()
{
ParameterName = SqlParameterStore.constColumnsAndValuesTableParam, SqlDbType = SqlDbType.Structured,
Value = constantColumnsAndValuesTable, TypeName = ColumnAndValueListDataTable.dbTypeName
},
new SqlParameter()
{
ParameterName = SqlParameterStore.scrambledColumnsTableParam, SqlDbType = SqlDbType.Structured,
Value = scrambledColumnsTable, TypeName = ColumnListDataTable.dbTypeName
},
new SqlParameter(SqlParameterStore.sqlToGetConstantTypesParam, tableInfo.SqlToGetConstantColumnTypes),
new SqlParameter(SqlParameterStore.sqlToGetScrambledTypesParam, tableInfo.SqlToGetScrambledColumnTypes),
new SqlParameter(SqlParameterStore.dbNameParam, tableInfo.DbName),
new SqlParameter(SqlParameterStore.schemaNameParam, tableInfo.SchemaName),
new SqlParameter(SqlParameterStore.tableNameParam, tableInfo.TableName),
new SqlParameter(SqlParameterStore.whereParam, tableInfo.WhereClause)
};
try
{
IndexManager.TurnIndexesOnOff(tableInfo, false);
SqlHelper.ExecuteNonQuery(tableInfo.DbConnectionString, getScramblingScriptParams, Resources.Scramble);
IndexManager.TurnIndexesOnOff(tableInfo, true);
LogSuccess(tableInfo);
return(true);
}
catch (Exception ex)
{
logger.Error(ex, $"An error happened while scrambling the table {tableInfo.FullTableName}: {ex.Message}");
return(false);
}
}
private static void CheckParams(ITableInfo tableInfo, List <string> constantColumns, List <string> scrambledColumns, string paramCheckerScript)
{
var constantColumnNamesTable = new ColumnListDataTable();
foreach (var columnName in constantColumns)
{
constantColumnNamesTable.Rows.Add(columnName);
}
var scrambledColumnNamesTable = new ColumnListDataTable();
foreach (var columnName in scrambledColumns)
{
scrambledColumnNamesTable.Rows.Add(columnName);
}
var checkingQueryParams = new List <SqlParameter>
{
new SqlParameter()
{
ParameterName = SqlParameterStore.constColumnsTableParam,
SqlDbType = SqlDbType.Structured, Value = constantColumnNamesTable, TypeName = ColumnListDataTable.dbTypeName
},
new SqlParameter()
{
ParameterName = SqlParameterStore.scrambledColumnsTableParam,
SqlDbType = SqlDbType.Structured, Value = scrambledColumnNamesTable, TypeName = ColumnListDataTable.dbTypeName
},
new SqlParameter(SqlParameterStore.dbNameParam, tableInfo.DbName),
new SqlParameter(SqlParameterStore.schemaNameParam, tableInfo.SchemaName),
new SqlParameter(SqlParameterStore.tableNameParam, tableInfo.TableName)
};
SqlHelper.ExecuteNonQuery(tableInfo.DbConnectionString, checkingQueryParams, paramCheckerScript);
}
